Aprendiendo la función Jerarquía.eqv

La función JERARQUIA.EQV (o JERARQUIA en versiones de Excel 2010 o anteriores) se utiliza para obtener el ranking que tiene un determinado valor en relación al resto de los datos. 

Por ejemplo, puedes precisar establecer un ranking para

  • Identificar los vendedores que más vendieron

  • Los goleadores de un torneo

  • Películas más vistas, etc

En este video te explico cómo armar un ranking en Excel que se actualice automáticamente con la función JERARQUÍA.EQV y cómo hacer cuando en el ranking hay empates:

 

 

Explicación

Imagina que tenemos una lista de vendedores y la cantidad de producto que vendieron.

Queremos realizar un ranking que ordene los datos desde el vendedor que más vendió al que menos

jerarquia.eqv jerarquia

Opción # 1 Comando Ordenar y Filtrar

Podríamos pensar en aplicar el comando Ordenar y Filtrar para así organizar las unidades de mayor a menor, sin embargo, en este registro de ventas constantemente se van modificando los datos, por tanto, el solo uso del comando no sería muy efectivo porque no se actualizará automáticamente.
 

Opción #2 Función JERARQUIA.EQV

Frente a esto, necesitamos aplicar una función que conforme actualicemos los datos, el orden también lo haga, y la función que cumple con estas características es JERARQUÍA.EQV.

 

= JERARQUÍA.EQV(número;referencia;[orden])

 

  • Número (obligatorio): Es el número cuya jerarquía (posición) deseamos conocer.

  • Referencia (obligatorio): La matriz o la referencia a una lista que contiene el conjunto de valores que deseamos analizar (los valores no numéricos se omiten).

  • Orden (opcional): Establece el orden que se aplicará a los números. 

    • Si se deja vacío o se ingresa 0, se establece un orden descendente

    • Si se ingresa un número distinto a cero, se establece un orden ascendente.

 

Agreguemos una nueva columna, la denominamos “Posición” y en ella ingresamos la función 

=JERARQUÍA.EQV(B4;$B$4”$B$35;0)

 

  • En el argumento “número” seleccionamos el primer registro de la columna “Unidades”, vale decir, el primer número del cual conoceremos su posición (celda B4). 

  • En “referencia” seleccionamos el rango B4:B35 compuesto por todas las unidades vendidas (lista de números), y lo dejamos con referencias absolutas para poder copiar la función hacia abajo. 

  • Finalmente, en el argumento “[orden]” señalamos el número 0 puesto que necesitamos que el orden que se aplique a la cantidad de unidades sea de manera descendente, es decir, que le asigne la posición más alta al número más alto.

jerarquia.eqv

 

Copiamos hacia abajo y vemos que la función asignó a cada cantidad el número de posición que ocupa dentro de la lista seleccionada.

Ahora, para armar el ranking, solo resta traer los nombres de los vendedores a la columna G en función de la posición que figura en la columna F, y para ello acudimos a la función BUSCARV:

 

=BUSCARV(F4;$A$4:$C$35;3;0)

 

 

jerarquia.eqv

Y listo, tenemos nuestro Ranking de Vendedores creado para identificar quienes vendieron más.

Ahora si, si realizamos un cambio: por ejemplo si Silvia pasó de vender 110 a 180, ahora figurará que está en el puesto #1 de ventas.

jerarquia.eqv

CASO Empate: Ranking con valores repetidos 

¿Pero qué pasaría si los números de la lista se repitieran? 

Por ejemplo en este caso donde en una liga de fútbol hay varios equipos con el mismo puntaje la función asignará la misma posición a los equipos que tienen igual puntaje y por ende  las posiciones no serán secuenciales, por ejemplo, la posición 2 no figura, sino que del 1 salta al 3.

JERARQUIA.EQV EMPATES

Frente a estos casos debemos añadirle un factor de corrección a la función JERARQUÍA.EQV

Agregamos una nueva columna al lado de la “Jerarquía” y en ella utilizaremos la función CONTAR.SI, la cual por definición cuenta aquellas celdas que cumplen con determinado criterio que definimos:

=CONTAR.SI($D$3:D3;D3)


Mediante esta función lo que hacemos es contar cuántas veces aparecen los puntajes conforme vamos agregando nuevos datos, es por ello que en el argumento “rango” establecemos una referencia expansiva, para que así CONTAR.SI devuelva un recuento de ocurrencias en ejecución, en lugar de un recuento total para cada valor.

Así podemos observar que “Las Palmas” tiene el mismo puntaje que “Eibar”, por tanto, se le asigna el número 2 porque aparece por segunda vez el puntaje 11. Lo mismo pasa con los equipos “Málaga”, “Deportivo” y “Real Betis” que se les asigna el número 1, 2 y 3 respectivamente.

JERARQUIA.EQV EMPATES
 

Ahora, modificaremos la función CONTAR.SI para que solo cuente los valores cuando comiencen a repetirse, y para a ello le restaremos el valor 1:
=CONTAR.SI($D$3:D3;D3)-1

Esto hará que la cuenta de los puntajes quede en cero cuando sea la primera aparición y cuando comiencen a repetirse, les irá asignando un 1, 2 o 3 según la cantidad de veces que aparezca.
 

 

 

JERARQUIA.EQV

Luego, este factor de corrección se lo añadimos a la función JERARQUÍA.EQV, a fin de que cuando el puntaje se repita, se le sume la cantidad de veces que aparece dicho puntaje:
JERARQUIA.EQV

Ahora cada equipo tiene una posición única.

Por consiguiente, ya podemos armar y ordenar de manera descendente nuestra tabla de posiciones mediante BUSCARV:

=BUSCARV(I3;$B$3:$D$22;2;0)JERARQUIA.EQV

 

Y de esta manera, resolvimos el caso de empates.