PREPARADORES DE OPOSICIONES PARA LA ENSEÑANZA

P.G.A. 1

www.preparadores.eu

P.G.A. CONTENIDO: Ejercicios de Excel Ejercicio 1- Reparación Este ejercicio no presenta ninguna dificultad, únicamente sirve para reforzar la idea de que las hojas de cálculo no utilizan las fechas ni las horas, lo hacen con números que las representan. El importe, por tanto, será el resultado de multiplicar el precio de la hora de trabajo (30 €) por la diferencia entre la hora de salida y la de entrada, pero con una corrección resultante de una proporción del tipo: 1 Tiempo empleado

=

24 x

Porque en Excel las 24:00 horas del día se representan por la unidad, siendo cualquier fracción del día una fracción de la unidad. Por ejemplo: 12:00 horas = 0,5; 13:25 horas = 0,5590; 21:15 horas = 0,8854. Es importante no olvidar que la referencia a la celda C1 debe contener el símbolo $ para convertirla en absoluta.

Por tanto, la fórmula de la celda F4 será: =$C$1*E4*24 Ejercicio 2 - Parking Una variación del ejercicio anterior sería calcular el precio a pagar por los usuarios de un parking según el tiempo que han permanecido en él.

PREPARADORES DE OPOSICIONES PARA LA ENSEÑANZA

C/ Sagasta nº 20 1º dcha • 28004 Madrid Tel.:

91 308 00 32

PREPARADORES DE OPOSICIONES PARA LA ENSEÑANZA

P.G.A. 2

www.preparadores.eu

Para la solución únicamente hay que tener en cuenta que Excel considera las fechas y las horas como números, por tanto basta con sumar el día y la hora de salida y restarles la suma del día y la hora de entrada.

Las columnas del día de entrada y salida tendrán cualquier formato de fecha. Las columnas de hora de entrada y salida tendrán cualquier formato de hora. La columna del tiempo de estancia se tendrá que utilizar el formato de hora tipo 37:30:55.

PREPARADORES DE OPOSICIONES PARA LA ENSEÑANZA

C/ Sagasta nº 20 1º dcha • 28004 Madrid Tel.:

91 308 00 32

PREPARADORES DE OPOSICIONES PARA LA ENSEÑANZA

P.G.A. 3

www.preparadores.eu

Ejercicio 3 – Horas trabajadas Calcula las horas minutos y segundos totales que han realizado los trabajadores de una empresa durante la semana

En este ejercicio seguimos con el refuerzo de las fechas, pero ya intervienen más funciones de Excel relacionadas con las fechas y horas. En la columna horas, únicamente haremos la diferencia entre la hora de salida y la de entrada =C5-B5. El resultado de esta celda lo usaremos para que Excel nos indique cuantas horas =HORA(D5), minutos =MINUTO(D5) y segundos =SEGUNDO(D5) se ha trabajado. Hasta ahora no hay nada especial, porque la columna D ya nos indicaba los resultados en horas, minutos y segundos. El problema consiste en sumar, puesto que en la suma, cada sesenta minutos se añade una hora y cada sesenta segundos se añade un minuto. De manera que las 32 horas de la celda E11 no dan problemas, pero los 207 minutos de la celda F11 y los 83 segundos de la celda G11 hay que convertirlos. Para ello, PREPARADORES DE OPOSICIONES PARA LA ENSEÑANZA

C/ Sagasta nº 20 1º dcha • 28004 Madrid Tel.:

91 308 00 32

PREPARADORES DE OPOSICIONES PARA LA ENSEÑANZA

P.G.A. 4

www.preparadores.eu

dividiremos entre 60 y usaremos las funciones matemáticas Cociente() y Residuo(). En las filas16 a 20 se han incluido una serie de funciones de la categoría Fecha y hora que nos permiten extraer de una fecha distintos valores como el día, el mes, el año, el día de la semana (entre 1 y 7, de lunes a domingo) o el número que hace la semana en el total del año.

Ejercicio 4 - Trienios Continuamos con el refuerzo de las fechas, pero ya intervienen dos funciones de Excel, Entero() y Hoy() anidadas. En la columna años, utilizaremos la función FRAC.AÑO que calcula la fracción de año que representa el número de días enteros entre la fecha inicial y la fecha final. Su sintaxis es: =FRAC.AÑO(fecha_inicial;fecha_final;base) La base determina en qué tipo de base deben contarse los días según la tabla: Base

Base para contar días

0 u omitido

US (NASD) 30/360

1

Real/real

2

Real/360

3

Real/365

4

Europea 30/360

Utilizamos la función Entero() para que, en el resultado de restar la fecha del sistema (es lo que indica la función Hoy()) a la fecha de ingreso en la empresa, no se tenga en cuenta la parte fraccionaria (los decimales), puesto que los años se cuentan de uno en uno, por enteros (no cumplimos años hasta el día del cumpleaños).

Para el importe trienios el resultado es más sencillo, basta dividir los años por 3. También aquí se utiliza la función Entero() por el mismo motivo (el trabajador tiene 0, 1, 2, etc. trienios, nunca 1,5 ó 1,8).

PREPARADORES DE OPOSICIONES PARA LA ENSEÑANZA

C/ Sagasta nº 20 1º dcha • 28004 Madrid Tel.:

91 308 00 32

PREPARADORES DE OPOSICIONES PARA LA ENSEÑANZA

P.G.A. 5

www.preparadores.eu

En el cálculo del importe de los trienios no es necesaria una función. Se multiplica el importe en euros por el número de trienios. Es importante no olvidar que la referencia a la celda B1 debe contener el símbolo $ para convertirla en absoluta.

Ejercicio 5. Letra NIF Con este ejercicio vamos a calcular la letra del número de identificación fiscal. Para ello necesitamos saber que se obtiene después de dividir el número del DNI entre 23, como resto resultará un número comprendido entre 0 y 22, al que se le asigna una letra según la siguiente tabla: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 T R W A G M Y F P D X B N J Z S Q V H L C K E Solución 1 Utilizaremos las funciones Residuo y Buscar. A B C D E F G H I J K L M N O P Q R S T U V W 1 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 2 T R W A G M Y F P D X B N J Z S Q V H L C K E 3 4 DNI Letra 5 26123456 =BUSCAR(RESIDUO(C5;23);A1:W1;A2:W2) 6 7 Solución 2 Otra posible solución es utilizando la función de texto EXTRAE. La función quedaría así:

PREPARADORES DE OPOSICIONES PARA LA ENSEÑANZA

C/ Sagasta nº 20 1º dcha • 28004 Madrid Tel.:

91 308 00 32

PREPARADORES DE OPOSICIONES PARA LA ENSEÑANZA

P.G.A. 6

www.preparadores.eu

=EXTRAE("TRWAGMYFPDXBNJZSQVHLCKE";RESIDUO(A2;23)+1;1) Ejercicio 6 – Stock

Este ejercicio sirve para introducir una de las funciones de Excel más utilizadas, la función lógica SI. Se trata de que en la columna Cantidad Servida, Excel indique la cantidad servida al cliente dependiendo de que exista suficiente cantidad en el almacén. A

B

C

D

F

G

Stock Cantidad servida inicial

E

Pendiente de servir

Stock final

1

Código

Descripción

Cantidad pedida

2

000

Plumas

56

125

=SI(D2>C2;C2;D2)

=C2-E2

=D2-E2

3

001

Carpetas

189

150

=SI(D3>C3;C3;D3)

=C3-E3

=D3-E3

4

002

Portaminas

75

100

=SI(D4>C4;C4;D4)

=C4-E4

=D4-E4

5

003

Rotuladores

35

20

=SI(D5>C5;C5;D5)

=C5-E5

=D5-E5

6

004

Tijeras

85

25

=SI(D6>C6;C6;D6)

=C6-E6

=D6-E6

7

005

Grapadoras

234

500

=SI(D7>C7;C7;D7)

=C7-E7

=D7-E7

En el caso de que la cantidad en almacén sea superior a la cantidad pedida por el cliente, se servirá lo efectivamente pedido. Si la cantidad el almacén es menor, serviremos únicamente lo que hay en existencia, quedando el resto pendiente de servir. La columna Pendiente de servir muestra (no es necesaria una función SI, aunque podría utilizarse) la diferencia entre lo pedido y lo realmente servido. La columna Stock final indica el saldo de existencias, El Stock inicial menos la cantidad servida. Ejercicio 7 – Funciones para “limpiar” celdas Cuando se presentan determinadas tablas es útil que permanezcan todas las celdas en blanco hasta que no se comience la introducción de datos. Veamos un ejemplo:

PREPARADORES DE OPOSICIONES PARA LA ENSEÑANZA

C/ Sagasta nº 20 1º dcha • 28004 Madrid Tel.:

91 308 00 32

PREPARADORES DE OPOSICIONES PARA LA ENSEÑANZA

P.G.A. 7

www.preparadores.eu

En esta tabla, los resultados de las columnas C, D y E irán apareciendo conforme escribamos los números en las columnas A y B. Para ello utilizaremos las funciones SI.ERROR(), ESBLANCO() y la función O(). En la celda C10: =SI(O(ESBLANCO(A10);ESBLANCO(B10));"";SI.ERROR(A10/B10;"Indeterminado")) En la celda D10: =SI(O(ESBLANCO(A10);ESBLANCO(B10));"";SI.ERROR(COCIENTE(A10;B10);"Indeterm inado")) En la celda E10: =SI(O(ESBLANCO(A10);ESBLANCO(B10));"";SI.ERROR(RESIDUO(A10;B10);"Indetermi nado")) Ejercicio 8 – Calificaciones Se trata de conseguir que cuando nosotros escribamos la nota numérica del alumno, Excel indique su correspondencia en letra en la columna siguiente según estas equivalencias: de 1 hasta 5, Insuficiente; 5, suficiente; 6, Bien; 7 y 8, Notable; 9 y 10, Sobresaliente. Para notas mayores de 10 o menores de 1, debe indicar: Nota no válida.

Solución 1 En este ejercicio se lleva al máximo el concepto de funciones anidadas (se pueden anidar hasta 64 niveles de funciones) utilizando solamente la función lógica SI. Vamos a trabajar en la celda C2 para posteriormente copiar la función hacia abajo. La PREPARADORES DE OPOSICIONES PARA LA ENSEÑANZA

C/ Sagasta nº 20 1º dcha • 28004 Madrid Tel.:

91 308 00 32

PREPARADORES DE OPOSICIONES PARA LA ENSEÑANZA

P.G.A. 8

www.preparadores.eu

función (funciones anidadas) es la siguiente: =SI(B2