En este tutorial resolveremos, paso a paso, la parte de Excel de las Pruebas Selectivas para el ingreso Libre en el Cuerpo General Auxiliar de la Administración del Estado. Concretamente realizaremos la exámen Excel Oposición Oferta de Empleo Público 2015, AUX-LI 15.
Vamos a ver apartado por apartado. Empecemos:
DADA LA HOJA EXCEL QUE CONTIENE UN CONTROL DE INVENTARIO DE UN ALMACÉN, REALIZAR LAS SIGUIENTES ACTIVIDADES:
1. Rellenar la columna PORCENTAJE DE MAYORES DE 65 calculando para cada línea la división de la celda TRAMO3 entre la celda TOTAL.
La fórmula a utilizar en este enunciado es una fórmula sencilla consistente en una división:
=G8/D8
2. Calcular para cada línea la POBLACIÓN EN 2016 según la siguiente fórmula:
POBLACIÓN EN 2016 = TOTAL + (TOTAL * CRECIMIENTO ESTIMADO)
(El CRECIMIENTO ESTIMADO es el que contiene la celda D4).
Al igual que el apartado anterior, es una fórmula sencilla, con la singularidad del uso de referencias absolutas (en la celda D4).
=D8+(D8*$D$4)
Debemos fijar dicha celda ya que es un valor que no va a variar de un registro al siguiente; en todos los casos habrá que multiplicar por el valor contenido en dicha celda. Recuerda que cuando arrastramos una fórmula con referencias relativas (D8 en nuestra fórmula) el número de fila de dicha referencia (8) se irá incrementando de uno en uno (si arrastramos hacia abajo) o decrementando (si arrastramos la fórmula hacia arriba) y el indicador de columna (D) variárá hacia la siguiente letra si arrastramos hacia la derecha (pasaría a ser E) o hacia la izquierda (en cuyo caso cambiaría a C).
3. Dar formato a las columnas PORCENTAJE DE MAYORES DE 65 y POBLACIÓN EN 2016 con #.##0,00% y #.##0,00 “hab” respectivamente.
Para aplicar el formato solicitado debemos acceder al cuadro de diálogo de Formato de celdas. Con la columna seleccionada podemos:
– hacer clic con el botón derecho y escoger Formato de celdas
– o a través del inicializador de cuadros de diálogo del grupo Número (ficha Inicio), por ejemplo.
Una vez en la ventana indicada, en la ficha Número escojo la categoría Personalizada:
Formato para PORCENTAJE DE MAYORES DE 65:
Formato de POBLACION EN 2016:
Recuerda que para mostrar texto acompañando a una cantidad debemos incluir éste entre comillas dobles (” “).
4. Alinear las columnas PORCENTAJE DE MAYORES DE 65 y POBLACIÓN EN 2016 a la derecha con sangría 1.
Este apartado lo haremos también desde la ventana Formato de celdas, pero esta vez desde la pestaña Alineación:
5. Completar la celda DATOS1(D30) con el promedio de los valores calculados en la columna TOTAL.
La resolución de esta celda la haremos con la función básica PROMEDIO:
=PROMEDIO(D8:D27)
6. Para dicha celda D30 establecer el formato #.###,0
Para aplicar este formato repetiremos los pasos realizados en el apartado 3 (Formato de celdas – Pestaña Número – Categoría Personalizada).
7. Rellenar la columna RATIO POBLACIÓN ACTIVA calculando para cada línea la división de la celda TRAMO2 entre la suma de las celdas TRAMO1 y TRAMO3 en el caso de que esta división sea mayor que 1 y 1 para el caso contrario.
En este caso utlizaremos la función lógica SI, ya que el valor que debe mostrarse en la cada celda de la columna dependerá de si se cumple una condición o no:
8. Dar formato a la columna RATIO POBLACIÓN ACTIVA con valores numéricos con dos decimales y con separador de miles.
9. Alinear la columna RATIO POBLACIÓN ACTIVA al centro.
10. Completar la columna GRUPO con la concatenación de los dos primeros caracteres de la PROVINCIA (que se encuentra en la columna C) y de los tres últimos caracteres del CODIGO (que se encuentra en la columna A), todo en minúsculas.
(Puedes consultar el video que aparece en la resolución del ejercicio de Excel de AUX-LI 16, en el apartado 7 del mismo, ya que es una fórmula similar a ésta; podrás ver cómo se van anidando las diferentes funciones)
11. Alinear la columna GRUPO con el valor distribuido y con sangría 2.
La alineación valor Distribuido distribuye el texto uniformemente en el ancho de la celda; es decir, reparte el texto de forma que quede el mismo espacio por la derecha que por la izquierda. La sangría indicada será la misma por la derecha que por la izquierda.
12. Completar la celda DATOS2 (D32) con el máximo valor de las columnas TRAMO1, TRAMO2 y TRAMO3.
Como ambas columnas están contiguas, podemos seleccionar el rango que comprende las tres columnas, en vez de seleccionar columna por columna:
=MAX(E8:G27)
13. Aplicar a la celda DATOS2 (D32) el mismo formato que las celdas de la columna TOTAL.
Para realizar este apartado se requiere el uso de la herramienta Copiar formato.
– Seleccionamos una celda de la columna TOTAL.
– Pulsamos el botón Copiar formato…
– y hacemos clic en la celda destino, en este caso D32.
Cuando utilizamos esta herramienta, el formato copiado incluye el tamaño y tipo de la fuente, colores, bordes y alineación.
(Puedes consultar el video que aparece en el tutorial Resolución de la parte de Excel del examen de AUX-LI 2016, en el apartado 13, en el que se solicitaba la realización de la misma acción).
14. Copiar la columna POBLACIÓN desde la celda B8 hasta la B27, pegar a partir de la celda B42 y ordenar alfabéticamente. Copiar las columnas TRAMO1 y TRAMO2 desde la celda E8 hasta la F27, pegar a partir de la celda F42 y ordenar ambas columnas utilizando como primer criterio los TRAMO1, de mayor a menor, y como segundo criterio los TRAMO2 de menor a mayor.
En este apartado se trata de copiar, pegar y ordenar. La primera será una ordenación sencilla. En la segunda se trata de ordenar dos columnas al mismo tiempo, primero por una de ellas y luego por la otra. En el siguiente video puedes ver el proceso:
15.Completar la celda ACTUALIZADO A (D3) para que muestre la fecha 1 de enero de 2015 menos 50 días naturales. Dar formato a la celda ACTUALIZADO A (D3) para se muestre como Día//Mes//Año, con cuatro cifras para el año, dos cifras para el mes y una o dos cifras para el día (según si el día tiene decenas o no). La separación entre cada parte ha de realizarse con dos barras.
La fórmula a utilizar en este apartado es la siguiente:
=”01/01/2015″-50
El formato personalizado que debemos aplicarle es el siguiente:
16. Completar la celda DATOS3 (D34) con el valor de la moda de la columna TRAMO1.
=MODA.UNO(E8:E27)
(La función MODA.UNO devuelve el valor que más se repite en el rango especificado. Si no hubiera ningún valor repetido, devolvería el valor de error #N/A)
17. Completar la celda DATOS4 (D36) con la cuenta del numero de filas que cumplan conjuntamente los criterios de TRAMO1 menor que 860 y TRAMO2 mayor que 750.
Para calcular esta celda haremos uso de la fución CONTAR.SI.CONJUNTO, ya que ha de cumplirse más de una condición.
=CONTAR.SI.CONJUNTO(E8:E27;”<860″;F8:F27;”>750″)
18. Completar la celda DATOS5 (D38) con el resto resultante de dividir la mediana de la columna TOTAL entre el mínimo de los valores de la columna TRAMO2.
El resto resultante de una división lo obtendremos con la función RESIDUO. Tiene dos argumentos: dividendo (que en este caso será la mediana de la columna TOTAL) y divisor (que será el mínimo de la columna TRAMO2):
=RESIDUO(MEDIANA(D8:D27);MIN(F8:F27))
19. Completar la celda DATOS6 (D40) con la suma de los valores de la columna TOTAL siempre que en la celda correspondiente a la misma fila de la columna POBLACION el texto consignado comience por la letra “S”.
El criterio que considera para realizar la suma es que la población empiece por S. Esa condición se especifica así: S*, ya que el caracter comodín * esquivale a cero, uno o más caracteres. De esta forma le estoy diciendo que compruebe que la primera letra es una S y que el resto me da igual.
Nota: los caracteres comodín * e ? solo funciones para filtros y para algunas funciones, como por ejemplo SUMAR.SI, CONTAR.SI, …
=SUMAR.SI(B8:B27;”S*”;D8:D27)
20. Calcular la columna ESTIMACION según las siguientes condiciones:
Si TRAMO1 es mayor o igual a TRAMO2
ESTIMACION = 0
Si TRAMO1 es menor que TRAMO2
ESTIMACION = TRAMO1 – TRAMO2 + 250
Para resolver este último apartado utilizamos la función lógica SI:
=SI(E8>=F8;0;E8-F8+250)
En las imagenes siguientes puedes ver el resultado final del ejercicio:
