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
lista 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: Excel
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:
ResponderBorrar=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)
Hola Jaizki, gracias por tu certera observación y ya estoy corrigiendo la entrada.
ResponderBorrarHay 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...
Es un placer poder aportar algo a tu magnífico blog.
ResponderBorrarJorge 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.
ResponderBorrarGracias por la ayuda que me pueda dar.
Jair
Hola Jair
ResponderBorrarfijate en esta nota.
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:
ResponderBorrar+ 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.
Hola Villy, bienvenido al blog!
ResponderBorrarMuy 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.
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.
ResponderBorrarMuchas gracias de antemano. Villy.
Villy,
ResponderBorrarla 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".
¿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
ResponderBorrarLa ú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).
ResponderBorrarLuego, 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.
Hola!
ResponderBorrarTengo 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!!!
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).
ResponderBorrarAhora 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.
Hola Jorge,
ResponderBorrarTengo 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
Fran,
ResponderBorraren 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.