Buenas,
Habitualmente, cuando preparo informes, me planteo incluir rankings de valores para series de datos y saber facilmente quienes estan en el top.
Asi que vamos a ello, como siempre, partimos de un listado de valores. En este caso, tenemos 25 delegaciones de venta repartidas en 8 áreas geográficas distintas y las ventas conseguidas por cada delegación en un periodo determinado.
La manera mas sencilla es utilizar la función K.ESIMO.MAYOR la cual nos devuelve el valor correspondiente a la jerarquia que le digamos, es decir, el valor 1 será el mas alto, el 2 el segundo más alto, y así sucesivamente….
Creamos una tabla con la primera columna (columna H) valores del 1 al 5, y la rellenamos con la formula K.ESIMO.MAYOR($D$2:$D$26;H2) donde H2 es el numero 1. Arrastramos hasta el 5 y nos extrae los 5 mayores valores de la tabla. Si estos valores fueran únicos, ya sabemos extraer la delegación y el área de ventas anidando las funciones INDICE y COINCIDIR.
El problema viene cuando existen dos valores de venta iguales, y por tanto no podemos traer dos datos así. Si os parece os dejo la solución a este problema al final, que será utilizando la función JERARQUIA.EQV para obtener un ranking de los valores.
Otro ranking que podemos sacar, es el de aquellas delegaciones correspondientes a un sólo área. Para hacer esto más util, vamos a utilizar validación de datos (como vimos en el post sobre Grafico dinamico con desplegables para introducir en la celda H9 un desplegable con las áreas de negocio. Después, hacemos una sencilla tabla como podeis ver a continuacion:
Para que los datos se actualicen automaticamente en la tabla, mostrando las tres delegaciones con mayor venta del área seleccionada (o menos, si no hay tantas) hay que formular las tres columnas de abajo:
- Columna Numero de raking (H); la formula a introducir en H11 es: SI(H10<CONTAR.SI($B$2:$B$26;$H$9);H10+1;»»). La arrastramos hasta H13 y con esto conseguiremos que sólo muestre los numeros del 1 al 3 si existen datos para esa área.
- Columna Ventas: Utilizamos una formula matricial (por tanto pulsaremos ctr+shift+enter al introducir la formula) que es como sigue: =SI.ERROR(K.ESIMO.MAYOR(SI($B$2:$B$26=$H$9;$D$2:$D$26);H11);»»). Con el SI.ERRROR la formula solo rellena aquellos datos que debe (en base a lo que haya en la columna H) y el K.ESIMO.MAYOR lo utilizamos con un condicional dentro (acotando esta busqueda al área de referencia)
- Columna degelacion. Aqui hemos creado antes una columna auxiliar entre Area y Ventas concatenando ambos valores y utilizandola para buscar valores con la fomula: SI.ERROR(INDICE($A$2:$A$26;COINCIDIR($H$9&I11;$C$2:$C$26;0));»»)
Con esto ahora si cambiamos el desplegable los valores se actualizarán solos. Un truco muy chulo para incluir en dashboards.
En el excel de trabajo, que os dejo Rankings_valores, también he incluido un ranking de las áreas en conjunto y un ranking en funcion de un determinado volumen de ventas, es decir, que muestre aquellas delegaciones que superan el umbral de ventas indicado.
Lo que hacemos para obtener un rango es es utilizar la función JERARQUIA.EQV para realizar rankings de valores, introduciendo en E2 la formula siguiente: JERARQUIA.EQV(D2;$D$2:$D$26) la cual nos devolverá un número del 1 al 25 (porque tenemos 25 valores) indicando la posición que ocupa el valor analizado en el listado. Quedaría algo así:
Sin embargo, no nos soluciona nada porque seguimos teniendo dos valores 16, asique vamos a utilizar una formula matricial para añadir al valor obtenido un número si el valor analizado ya se ha encontrado con anterioridad, de manera que en este caso concreto el primer 16 se mantendrá pero el segundo será un 17. Os pongo la formula, la cual es matricial (por tanto una vez introducida hay que pulsar ctrl + shift + enter para que surja efecto) que introducimos en F2, utilizando otra columna: JERARQUIA.EQV(D2;$D$2:$D$26)+CONTAR.SI($D$2:D2;D2)-1 si la introducís correctamente podréis ver que se colocan corchetes { } al principio y al final respectivamente. Esta formula como veis va incluyendo más rango en el CONTAR.SI segun vamos arrastrando la formula para ir incluyendo mas valores, de manera que cuando llegue a la fila 16, el valor solo se encuentra una vez, y por tanto no añade ningun valor (mantiene el 16) pero al llegar a la fila 18, encuentra dos veces el valor y por tanto añade 1 al ranking, obteniendo por tanto el 17.
De esta manera, si queremos buscar un valor del ranking, podemos traerlo como ya sabemos con INDICE y COINCIDIR utilizando esta columna de referencia.
Se que ha quedado muy largo el post pero estoy seguro que es muy util.
