viernes, junio 10, 2011

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.

5 comentarios:

  1. Gracias por compartir siempre información tan valiosa y útil.

    ResponderBorrar
  2. Buenas tardes, no encontré algún campo para mandar mi duda, por lo que lo hago aquí. Quisiera que me ayudaran en lo siguiente: He creado un Fixture para el Mundial Sub-17 2011,y todo va bien, excepto que de los 24 equipos, pasan los 2 primeros lugares de cada grupo (6 grupos), y los 4 mejores terceros. Mi pregunta es, como hago para saber cuales son los 4 mejores terceros, y me los coloque automáticamente en las llaves que hice para los octavos de final. Espero puedan aclarar mi duda

    ResponderBorrar
  3. Jerome,
    para aclarar tu duda tendría que escribir un post completo. Te sugiero que hagas una búsqueda en la Internet (fixture mundial 2010, por ejemplo). Encontrarás que hay una gran cantidad de modelos que se pueden descargar y que podrás adaptar a tus necesidades.

    ResponderBorrar
  4. Luis,
    Felicitarte por le blog y la informacion que brindas,Podrias aclarar paso a paso la Solución con INDICE, COINCIDIR y MAX en forma matricial; soy nuevo en esto por tal razon
    intente duplicar la solucion dada y los resultados son otros de antemano gracias.

    ResponderBorrar
  5. Luis,

    si la duda está en el uso de la funciones, te sugiero que leas la ayuda en línea de Excel (en el asistente de funciones hay un enlace "ayuda sobre está función").
    En esta nota puedes encontrar información básica sobre funciones matriciales.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.