Determinar posición (ranking) por grupos

En la nota sobre la función JERARQUIA aparece este comentario

¿Es posible aplicar la función jerarquía a una columna de manera que se reinicie el ranking si el valor de otra columna cambia? Por ejemplo, tengo 5 vendedores en 4 países y quiero saber el principal vendedor en cada país.

Esta consulta me llevó a reflexionar sobre dos temas:

1 - ¿cómo aplicar la función JERARQUIA en este caso?
2 – ¿Por qué aplicar la función JERARQUIA para este caso?

Muy a menudo me encuentro con consultas de este tipo, donde el lector presupone que la solución pasa por usar una función o un método determinado. En lugar de preguntar "¿cómo determino el mejor vendedor de cada país?" la consulta se transforma en "¿cómo uso JERARQUIA para solucionar este problema?

El medio (el uso de la función JERARQUIA) se transforma en el objetivo (encontrar el mejor vendedor de cada país).

Supongo que esta forma de enfrentar la solución de problemas está relacionada con la tendencia natural, en mi opinión, de aferrarnos a lo conocido y nuestra aversión o temor a lo desconocido. Seguramente hay otros factores, pero no lo trataré en esta nota. Por supuesto, mis lectores están invitados a opinar sobre el tema.

Y ahora llegó el momento de empezar a trabajar. ¿Cómo solucionamos el problema? Empecemos por mostrar nuestros datos

Solución con JERARQUIA



Para usar la función JERARQUIA por grupos (vendedores de Argentina, vendedores de Colombia, etc.) empezamos por definir rangos con nombres

Cada nombre se refiere al rango de valores de ventas correspondiente al país a que se refiere. Nótese que estos rangos no son dinámicos, por lo que se agregamos valores, tendremos que editar el nombre para redefinir el rango.

Agregamos una columna a la tabla, Ranking, donde ponemos esta fórmula

=JERARQUIA(C2,INDIRECTO(A2))

Hemos agregado además un formato condicional para resaltar el nombre y las ventas del vendedor que recibe la posición 1

Solución con INDICE, COINCIDIR y MAX en forma matricial

La ventaja de esta solución es que nos permite definir rangos dinámicos, librándonos de la necesidad de editar los rangos definidos cada vez que agregamos o quitamos datos de la tabla.

Otra ventaja es que implementamos uno de los principios importantes de buenas prácticas en Excel: la separación de la base de datos de los cálculos y los informes.

Como en el caso anterior, empezamos por definir rangos en nombres (otra buena práctica en Excel)
En este caso definimos rangos dinámicos

pais =indice!$A$2:INDICE(indice!$A:$A,CONTARA(indice!$A:$A))
vendedor =indice!$B$2:INDICE(indice!$B:$B,CONTARA(indice!$B:$B))
ventas =indice!$C$2:INDICE(indice!$C:$C,CONTARA(indice!$C:$C))

Creamos una tabla separada para mostrar los resultados por país donde ponemos esta fórmula matricial (fórmulas que introducimos apretando simultáneamente Ctrl+Mayúsculas+Enter)

=INDICE(vendedor,COINCIDIR(MAX((pais=E2)*(ventas)),ventas,0))

Solución con tablas dinámicas

Empezamos por crear una tabla dinámica con los campos País y Vendedor en el área de filas y Ventas en el área de datos. Luego ordenamos el campo Vendedor según las Ventas

El próximo paso es crear un campo calculado, Ranking (o cualquier otro nombre) con la fórmula "=1"

Seleccionamos el campo que acabamos de agregar y en el menú de configuración del campo mostramos los valores como "Total en" usando como campo de base "Vendedor"

El resultado es

Como puede apreciarse, no se trata de una verdadera solución ya que nos basta con ordenar los valores tal como hicimos en el primer paso. Obviamente, quien aparece en el primer lugar de cada país es el que más vendió. Esta solución puede ser útil si queremos extraer el vendedor que más vendió (o el segundo o el tercer, etc.) usando funciones que se refieran a la tabla dinámica, como IMPORTARDATOSDINAMICOS.

En este enlace muestro otra forma de aplicar JERARQUIA, es decir señalar el ranking, en una tabla dinámica.