Nos toca ahora resolver, 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 prueba de Excel del examen de la Oferta de Empleo Público 2016, AUX-LI 16.
Vamos a ver apartado por apartado. Empecemos:
DADA LA HOJA EXCEL QUE CONTIENE UN CONTROL DE INVENTARIO, REALIZAR LAS SIGUIENTES ACTIVIDADES:
1.-Rellenar la columna INVENTARIO1 calculando para cada línea la suma de las celdas TRAMO1, TRAMO2, a este resultado restarle la celda TRAMO3 y finalmente dividir este resultado por la celda TOTAL.
La solución de este primer apartado es una fórmula sencilla que puedes observar en la imagen siguiente. Ten especial cuidado en el uso de los paréntesis:
2.-Calcular para cada línea el INVENTARIO2 según la siguiente fórmula:
INVENTARIO2=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 pecualiaridad del uso de referencias absolutas (en la celda D4).
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 (D7 en nuestra fórmula) el número de fila de dicha referencia (7) se irá incrementando de uno en uno (si arrastramos hacia abajo) 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 INVENTARIO1 e INVENTARIO2 con #.###,0% y #.##0,00 “cli” respectivamente.
Para realizar este apartado debes acceder al Formato de celdas y crear un formato personalizado para cada una de las columnas. Recuerda que cuando quieres que aparezca texto al lado de un valor númerico, éste tendrá que ir entre comillas (caso del segundo formato pedido):
4.-Alinear las columnas INVENTARIO1 e INVENTARIO2 a la derecha con sangría 1.
Selecciona ambas columnas y accede al Formato de celdas – Pestaña Alineación:
5.-Rellenar la columna INVENTARIO3 calculando para cada línea la multiplicación de la celda TRAMO2 por la resta de las celdas TRAMO1 menos TRAMO3, en el caso de que esta multiplicación sea mayor que 0 y 10 para el caso contrario.
La resolución de este apartado requiere el uso de la función lógica SI.
En la imagen adjunta puedes verla con el asistente, pero te recomiendo que las escribas directamente en la celda, ya que en el examen real estarán desactivados los atajos de teclado y las ayudas (lo que imagino conllevará la desactivación de los asistentes también).
6.-Dar formato a la columna INVENTARIO3 con valores numéricos con tres decimales y con separador de miles. Alinear la columna INVENARIO3 a la derecha.
En este caso debes establecer un formato de celda numérico y una alineación con las características indicadas.
7.-Completar la columna INVENTARIO4 con la concatenación del texto “Indicador: “, más los dos primeros caracteres de la PROVINCIA en minúsculas, más los tres últimos caracteres del CÓDIGO. (Detrás de los “:” hay un espacio).
En este apartado tendremos que realizar el anidamiento de varias funciones de texto. En el siguiente video puedes ver la resolución del mismo.
8.-Alinear la columna INVENTARIO4 con el valor derecha y con sangría 3.
9.-Calcular la columna INVENTARIO5 según las siguientes condicones:
Si TRAMO1 es Impar y mayor o igual a TRAMO2
INVENTARIO5=TRAMO2
En otro caso
INVENTARIO5=TOTAL-TRAMO2+127
Para calcular la columna INVENTARIO5 una de las cosas que tendremos que comprobar es si el valor contenido en cada celda de TRAMO1 es impar o no. Esto podremos conocerlo con la función RESIDUO, que me devuelve el resto de una división. Un número es impar si el resto de dividirlo entre 2 es distinto de cero:
RESIDUO(E7;2)<>0
Teniendo en cuenta también el resto de condiciones indicadas en el enunciado del apartado, la fórmula quedaría así:
10.-Completar la celda DATO1 (D28) con el mínimo común múltiplo de los tres primeros valores de la columna TRAMO2.
11.-Para la celda DATO1 (D28) establecer el formato #.###
12.-Completa la celda DATO2 (D30) con la media acotada de las columnas TRAMO1, TRAMO2 y TRAMO3, con un porcentaje de exclusión del 2%.
13.-Aplicar a la celda DATO2 (D30) el mismo formato que las celdas de la columna TOTAL.
14.-Copiar la columna POBLACIÓN, celdas B7:B26, y pegarlas a partir de la celda B40 ordenando alfabéticamente.
15.-Copiar las columnas TRAMO1 y TRAMO3, celdas E7:E26 y G7:G26, y pegarlas respectivamente a partir de las celdas F40 y G40. Ordenar ambas columnas utilizando como primer criterio los valores de TRAMO3, de mayor a menor, y como segundo criterio los de TRAMO1 de menor a mayor.
16.-Completar la celda ACTUALIZADO A (D3) para que muestre la fecha 12 de febrero de 2017 más 36 días naturales. Dar formato a la celda ACTUALIZADO A (D3) para que se muestre como Año.Día.Mes, con cuatro cifras para el año, una o dos cifras para el día y dos cifras para el mes. La separación entre cada parte ha de realizarse con un punto.
17.-Completar la celda DATO3 (D32) con el valor de la mediana del conjunto de valores de las columnas TOTAL y TRAMO2.
18.-Completar la celda DATO4 (D34) con la cuenta del número de filas que complan conjuntamente los criterios de TRAMO2 menor que 660 y TRAMO3 mayor que 78.
Como las celdas de TRAMO2 deben cumplir ambos criterios para tenerlas en cuenta a la hora de contar, debemos utilizar la función CONTAR.SI.CONJUNTO:
19.-Completar la celda DATO5 (D36) con el resto resultante de dividir el máximo de los valores de la columna TRAMO2 entre la curtosis de la columna TOTAL.
20.-Completar la celda DATO6 (D38) con la suma de los valores de la columna TRAMO3 siempre que en la celda correspondiente a la misma fila de la columna POBLACION el texto consignado termine con la letra O (tanto mayúscula como minúscula).
Nos pide que calculemos la suma de unos valores si se cumple una condición determinada: función SUMAR.SI
A continuación puedes ver el resultado obtenido en las diferentes columnas y celdas solicitadas:
si deseas recibir tutoriales como este Suscribete