Funcion JERARQUIA en Excel – Aplicar a parte de una lista.

lunes, noviembre 13, 2006

Uno de mis lectores me propone el siguiente problema: queremos ordenar una lista de arqueros de fútbol de acuerdo al promedio de goles recibidos. Parte de los arqueros en la lista aún no han jugado (suplentes) y por lo tanto no deben ser tenidos en cuenta.
Buscamos una solución dinámica, sin tener que manipular manualmente la lista. Es decir, que todas las tareas sean hechas por fórmulas.

El problema es una variación del modelo que mostré en la nota Construir una tabla en Excel con las primeras 10 posiciones.

Supongamos que esta es nuestra arqueroslista de arqueros





Para calcular los promedio hemos usado una función condicional SI, para que aquellos que aún no han jugado aparezcan con promedio 0. El promedio del Arquero 1 es calculado por la fórmula =SI(B2=0,-1,C2/B2).

Nuestro próximo paso es calcular el orden de rango en orden ascendiente, pero de manera que el primer número de orden sea cero. Para eso usamos la fórmula
=JERARQUIA(D2,$D$2:$D$21,1)-1



He ordenado la lista de acuerdo a los resultados de JERARQUIA para hacer más evidente la técnica que vamos a emplear.

Como pueden ver, y como ya hemos explicado, el primer número de orden que tenemos es 6 (tenemos seis arqueros con el número de orden 0). Para nuestros propósitos queremos mantener los ceros, que identifican a los arqueros que estarán fuera del ranking, pero queremos que fuera de ellos los números de orden comiencen del 1.

Para esto anotamos en la columna F esta fórmula

=SI(E2=0,0,JERARQUIA(E2,$E$2:$E$21,1)-CONTAR.SI($E$2:$E$21,"<0")+CONTAR.SI($D$2:D2,D2)-1)


13-Nov-2006: la fórmula ha sido corregida gracias al aporte de Jaizki (ver comentarios)

Ahora generamos una tabla auxiliar para mostrar sólo aquellos arqueros que hayan jugado, ordenados de acuerdo al promedio de goles recibidos.

En el rango I1:K21 creamos esta tabla



Creamos tres nombres con rangos dinámicos (por si en el futuro queremos agregar más arqueros a la lista)

arqueros =DESREF(Hoja1!$A$2,0,0,CONTARA(Hoja1!$A:$A)-1,1)
jerarquia =DESREF(Hoja1!$F$2,0,0,CONTARA(Hoja1!$F:$F)-1)
promedio =DESREF(Hoja1!$D$2,0,0,CONTARA(Hoja1!$D:$D)-1)

En la celda J2 anotamos esta fórmula =INDICE(arqueros,COINCIDIR(I2,jerarquia,0)) y la copiamos al resto del rango.

En la celda K2 anotamos la fórmula =INDICE(promedio,COINCIDIR(J3,arqueros,0)) y la copiamos al resto del rango

El resultado es



Para ocultar los resultados N/A usamos formato condicional. En la ventanilla de Fórmula del diálogo de formato condicional escribimos



En la pestaña de Tramas, elegimos blanco para el color de la fuente; en la pestaña de borde elegimos la opción "ninguno".

El resultado es



El formato condicional ha vuelto "invisibles" a las líneas de la tabla donde la celda de la columna J da un resultado de error.

De esta manera, sólo usando fórmulas, hemos generado una tabla de ranking dinámica.





Categorías: Funciones&Formulas_,

Technorati Tags:

15 comments:

Jaizki 13 noviembre, 2006 16:46  

Me parece que podría haber un problema con el orden si algún portero hubiera jugado pero no hubiera encajado ningún gol. Para solucionarlo, supongo que sería suficiente con unas ligeras modificaciones en las fórmulas:

=SI(B2=0,-1,C2/B2)

=SI(E2=0,0,JERARQUIA(E2,$E$2:$E$21,1)-CONTAR.SI($E$2:$E$21,"<0")+CONTAR.SI($D$2:D2,D2)-1)

Jorge L. Dunkelman 13 noviembre, 2006 17:55  

Hola Jaizki, gracias por tu certera observación y ya estoy corrigiendo la entrada.
Hay que corregir un poco tu fórmula que debe ser
=SI(E2=0,0,JERARQUIA(E2,$E$2:$E$21,1)-CONTAR.SI($D$2:$D$21,"<0")+CONTAR.SI($E$2:E2,E2)-1)

es decir el primer CONTAR.SI debe hacerse al rango D2:D21 y el segundo al rango E2:E21

Debo escribir 100 veces: no publicar entradas a las 12 de la noche, no publicar entradas a las 12 de la noche...

Jaizki 14 noviembre, 2006 01:54  

Es un placer poder aportar algo a tu magnífico blog.

Anónimo,  28 mayo, 2009 18:36  

Jorge buen día, tengo un problema que varias veces se me ha presentado, si dos elementos de la lista que quiero jerarquizar tienen el mismo valor, lo que hace la formula es darles el mismo número. Por ejemplo si la clase x tiene el mismo número de alumnos que la clase y, a ambas les asigna el mismo número, es decir ambas quedan con el "ranking" No 3 y me deja el No 2 o el 4, y en coincidir genera el error de #N/A, pues al buscar el 2 o el 4 no lo encuentra.
Gracias por la ayuda que me pueda dar.
Jair

Villy,  26 noviembre, 2009 18:10  

Hola. Ante todo enhorabuena por tu blog. Estoy empezando a trabajar con Excel y veo que hay un montón de posibilidades que todavía desconozco. En concreto, me interesa el ejemplo de este ejercicio, en relación a la asignación de posiciones. A mi parecer, genera un problema:
+ Los arqueros 10 y 14 tienen el mismo promedio (0,50). El 10 ha jugado 2 partidos y el 14, 4. Parecería justo que el 14 tuviera un mejor puesto en la clasificación ya que ha jugado más arriesgando más por lo tanto. Sin embargo en tu lista el 14 tiene la 5ª posición y el 10 la 4ª.
+ Para evitar esto, ¿podría haber una fórmula dinámica que ordenara las posiciones de tal manera que cuando hubiera un empate en el promedio tuviera en cuenta el número de partidos jugados y asignara un mejor puesto al que más partidos hubiera jugado? ¿Y, si tuvieran el mismo promedio y el mismo número de partidos jugados, les asignara el mismo puesto?
Esperando tu respuesta.Gracias de antemano. Villy.

Jorge L. Dunkelman 27 noviembre, 2009 09:33  

Hola Villy, bienvenido al blog!
Muy bueno tu punto. La forma de corregir esto es modificar la fórmula que calcula el promedio agregando un factor para dar peso a los partidos jugados. La fórmula es:

=SI(B2=0;-1;C2/B2-(B2/10000))

Esto hará que en caso de "empate" el promedio del que haya jugado más partidos sea menor.

No hay necesidad de modificar el resto de las fórmulas.

Villy,  27 noviembre, 2009 13:31  

Muchas gracias Jorge. Perdona mi atrevimiento. Te planteo ahora otro problema. Quisiera que en caso de que los dos arqueros hayan jugado los mismos partidos y tengan el mismo promedio, tuviera otra forma de jerarquizarlos. Por ello introduzco una nueva columna que indica el número de balones que han ido entre palos durante el partido. (Se entiende que a más balones entre palos más dificultades ha tenido el portero, luego ha sido mejor su trabajo). De este modo quisiera asignarles el puesto teniendo en cuenta: 1. el promedio y en caso de empate 2. el número de partidos jugados y en caso de nuevo empate: 3. que tuviera en cuenta la lista de los balones que han ido entre palos y pusiera mejor promedio al que más balones tuviera asignándole un puesto mejor. Y sólo en caso de que tuvieran todo igual se les tendría que asignar el mismo puesto.
Muchas gracias de antemano. Villy.

Jorge L. Dunkelman 27 noviembre, 2009 13:44  

Villy,
la idea es la misma que en el caso anterior. La columna "Promedio" la calculamos con el "puntaje" que esta formado por: promedio menos partidos jugados/10000 - balones entre palos/100000. Como ves a "balones entre palos" le estoy dando un peso distinto (lo divido por 100000 en lugar de 10000) de manera que influya menos en el resultado que "partidos jugados".

Anónimo,  09 junio, 2011 18:05  

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

Jorge L. Dunkelman 10 junio, 2011 10:32  

La única forma que se me ocurre usando la función JERARQUIA es crear rangos definidos (nombres) que se refieran a las ventas de cada país (por ejemplo, el rango definido "Argentina" se refiere a las celdas que contienen las ventas de ese país).
Luego, suponiendo que los países se encuentran la columna A y ls ventas en la columna C, la fórmula sería

=JERARQUIA(C2,INDIRECTO(A2))

Si no estás familiarizado con el uso de la función INDIRECTO, podés fijarte en esta nota.

Pero creo que tu pregunta tendría que ser cómo determinar el principal vendedor de cada país lo que puede hacerse con otras herramientas, no necesariamente con la función JERARQUIA.

Mariana,  10 enero, 2014 22:39  

Hola!

Tengo una pregunta y espero me puedan ayudar ya que me ahorraría mucho tiempo en la elaboración de mi reporte y no he encontrado aún la manera de hacerlo.

Necesito jerarquizar ciertas marcas, de acuerdo a lo que venden en cada tienda, no puedo usar la fórmula de jerarquizar ya que me toma en cuenta los valores de todas las tiendas, lo que podría hacer es ir recorriendo los rangos para que sólo me tome en cuenta las tiendas que correspondan pero eso me llevaría mucho tiempo ya q tengo unas mil tiendas. Quisiera saber si puedo complementar la función jerarquizar con otra que haga que, al cambiar la tienda, se reinicie el conteo. Mi tabla dinámica tiene los datos de tienda, marca y venta $, y quiero que me ponga el lugar de venta mas o menos asi:

TIENDA MARCA VENTA LUGAR

2051 Fud $1800 1
Nestle $1000 2
McCormick $985 3
Danone $400 4
2052 Nestle $2500 1
Danone $1900 2
Colgate $950 3
Nescafe $500 4
2053 Nestle $3500 1
Danone $1000 2
Colgate $850 3


Es decir, alguna fórmula o algo que le pueda meter a la tabla dinámica, que me distinga el cambio de tienda y reinicie el conteo.... espero que si se haya entendido y que me puedan ayudar, mil gracias!!!

Jorge Dunkelman 12 enero, 2014 03:58  

Hola Mariana, si te entendí, estás usando una tabla dinámica para mostrar las ventas en cada tienda. En ese caso lo más sencillo es que ordenes la tabla en forma descendente de acuerdo al campo de los valores (Excel conserva la definición en cada selección de tienda).
Ahora si quieres mostrar el rango (1 para el producto más vendido, 2 para el siguiente, etc.), todo depende de la versión de Excel que estés usando. En Excel 2013 podés aplicar "Mostrar Valores como..--Clasificar de Mayor a Menor". En versiones anteriores esta funcionalildad no existe.

Fran 25 febrero, 2015 16:15  

Hola Jorge,

Tengo una pregunta en el caso que los arqueros no fueran únicos, sino que se repitieran. Es decir, si de la fila 1 a la 7 fueran datos del "Arquero1"; de la fila 8 a las 15 fueran datos del "Arquero 2" y de la fila 16 a la 21 fueran datos del "Arquero 3", podemos ordenar los mejores tiempos de cada arquero?

Saludos y mil gracias!
Fran

Jorge Dunkelman 26 febrero, 2015 06:56  

Fran,
en ese caso te sugiero usar una tabla dinámica poniendo los arqueros en el área de las filas y usar la función MAX para totalizar los datos.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP