Análisis de datos de producción con CUARTIL, Formato Condicional y K.ESIMO

Supongamos que tenemos el listado de las unidades producidas por nuestras 5 zonas a lo largo del año y queremos analizar cuáles han sido las, por ejemplo, 5 de mayor y menor producción. Además queremos tener la posibilidad de saber qué zonas y en que meses han producido por encima de un determinado cuartil. En concreto nos gustaría que se destacaran con un color diferente dentro de la tabla en función del cuartil indicado.

Lo primero que hacemos, como casi siempre, es dar nombre a la tabla que contiene la información:

1. Seleccionamos el rango B4:F15 y hacemos un clic en el cuadro de nombres (a la izquierda de la barra de fórmulas) y escribimos el nombre produccion y pulsamos enter.

2. Nos situamos en la celda A26 y escribimos hacia abajo la lista de posiciones que queremos analizar. En nuestro ejemplo serán 5.
3. Nos situamos en la celda B26 y escribimos la fórmula:
=K.ESIMO.MAYOR(produccion;$A26) Utilizamos referencias mixtas en A26 porque copiaremos esta fórmula a la derecha y simplemente cambiaremos después el nombre de la función.

4. Copiamos la celda B26 en C26 y sustituimos K.ESIMO.MAYOR por K.ESIMO.MENOR (obviamente es suficiente con cambiar la palabra MAYOR por MENOR)
5. Seleccionamos el rango B26:C26 y hacemos doble clic en la parte inferior derecha de la selección para que el copiado inteligente haga su trabajo...

De esta manera ya tenemos las 5 mayores y las 5 menores producciones del año de manera automática.
Pero además de lo calculado queremos hacer el cálculo de los distintos cuartiles (valor bajo el cual se encuentra una determinada proporción de los valores analizados). Para ello utilizaremos la función CUARTIL. Excel nos permite analizar el cuartil 0, 1, 2, 3 ó 4 que se corresponden a:


0....valor mínimo

1....percentil 25 ó primer cuartil

2....percentil 50 ó segundo cuartil

3....percentil 75 ó tercer cuartil

4....percentil 100 ó cuarto cuartil


Para que la entrada de datos sea más intuitiva vamos a poner una lista desplegable con las distintas opciones en la celda B18 que nos presente los porcentajes a los que hace referencia cada cuartil en vez del número de cuartil en si.


6. Montamos la tabla que puede ver en la imagen en el rango E19:F23 y le damos el nombre cuartiles (la seleccionamos; hacemos clic en el cuadro de nombres; escribimos la palabra cuartiles y pulsamos enter)

7. Nos situamos en la celda B18 y vamos al menú Datos/Validación de datos y seleccionamos Permitir/Lista. En Origen introducimos el rango E19:E23 , que serán los elementos que aparecerán en nuestra lista desplegable.

8. Nos ponemos en B19 y escribimos la siguiente fórmula:

=CUARTIL(produccion;(BUSCARV(B18;cuartiles;2;Falso))

Hemos utilizado la función BUSCARV anidada dentro de la función CUARTIL para transformar los elementos de nuestra lista desplegable en argumentos válidos para dicha función (el segundo argumento de la función CUARTIL sólo permite los valores 0, 1, 2, 3 ó 4)

En la figura de nuestro ejemplo hemos seleccionado el tercer cuartil (percentil 75). Dicha cantidad nos muestra el valor que deja, en este caso, el 75% de todos los valores de la producción por debajo. En nuestro ejemplo 2.418 unidades. Si lo que nos interesa ahora es que en la tabla se sombreen las producciones que están por encima de este valor (del tercer cuartil) haremos lo siguiente:

9. Seleccionamos la tabla de datos de producción, es decir, el rango B4:F15 y vamos al menú Formato/Formato condicional.

10. Seleccionamos valor de la celda - mayor o igual que y en el último recuadro le indicamos la celda B19. Pulsamos el botón Formato y seleccionamos el formato que queremos que aparezca (en nuestro caso trama gris). Aceptamos.

De esta manera las producciones de nuestra tabla que superen el cuartil que le indiquemos en la celda B18 se sombrearán en gris en nuestra tabla.