Función JERARQUIA (RANK) en Excel – Nota adicional

jueves, abril 06, 2006

En una entrada anterior he mencionado como usar la función JERARQUIA (RANK en la versión inglesa). Allí dimos la siguiente definición:


JERARQUIA (RANK): Devuelve la jerarquía de un número en una lista de números. La
jerarquía de un número es su tamaño en comparación con otros valores de la
lista. (Si ordenara la lista, la jerarquía del número sería su posición).


Pero a veces hay situaciones en las cuales dos miembros de la lista reciben el mismo ranking, como en este caso




Como podemos ver hay "empate" entre las celdas C5 y C6. Ambas reciben el ranking 14, y el 15 no existe.

Si queremos que en caso de "empate" el ranking se establezca por orden de aparición, podemos utilizar la siguiente fórmula:

=JERARQUIA(B4,sales)+CONTAR.SI($C$4:C4,C4)-1

Si copiamos esta fórmula en todo el rango de la columna D, veremos este resultado




En nuestra fórmula, la función CONTAR.SI (COUNTIF) evalúa cuantas veces aparece el número de ranking de la celda en el rango. Si aparece sólo una vez, el resultado de CONTAR.SI será 0 y el resultado será idéntico al de la fórmula original. Si el número de ranking ya figura en el rango, el resultado de la fórmula será incrementado en 1 por CONTAR.SI


El cuaderno con el ejemplo se puede descargar aquí


Categorías: Funciones&Formulas_,


Technorati Tags: ,

47 comments:

Toño,  25 octubre, 2006 21:43  

como le puedo hacer para que las celdas con el musmo valor tengan la misma jerarquia pero el valor siguiente tenga la siguiente jerarquia. ES decir que no se brinque ninguna jerarquia.

Anónimo,  23 agosto, 2008 18:01  

Gracias de antemano,

Necesitaba saber como podia realizar una jerarquia condicionada por otra columna. Me explico, en el ejemplo de los agentes con las ventas, y si se repitiera el nombre del agente y tuviera diferentes ventas (el mismo agente). ¿Podria tener el ranking por agente?

Salu2, JRM

Jorge L. Dunkelman 01 septiembre, 2008 17:39  

No me queda claro qué es lo que quieres hacer. Puedes mandarme un archivo con un ejemplo?

Anónimo,  05 noviembre, 2008 23:55  

buen día Jorge, excelente tu blog y admirable la forma en que explicas las cosas.
Consulta como puedo efectuar un ranking o jerarquía condicionada con filtros de una columna, es decir, que las celdas queden parametricas sólo con el filtro deseado y sólo dentro del rango visible (que me excluya las no filtradas)

saludos
rené

Jorge L. Dunkelman 08 noviembre, 2008 16:56  

No creo que se pueda hacer con una única fórmula. La única función que toma en cuenta celdas visibles es SUBTOTALES y ésta no funciona con jerarquía.
Una solución sería escribir una función definida por el usuario (UDF, macro). Otra posibilidad sería extraer la lista filtrada a otro rango en la hoja (con Filtro Avanzado) y allí aplicar la función JERARQUIA.

Anónimo,  11 diciembre, 2008 17:23  

No se si tendra alguna falencia que todavia no detecte.
pero lo que hice es los siguiente:
al lado del valor a considerar, le agregue una serie de numeros. 0.000001 0.0000002 0.000003 y lo sume al resultado.
si aplico la jerarquia al resultado sumado con los 000000
nunca tendre 2 valores iguales y tampoco influira lo suficiente como para que el ultimo numero de la lista de 800 empleados por ejemplo, sea superior al numero uno ya que a lo sumo tendria la jerarquia 800,0000800 mientras que el numero uno de la lista sera el (por ejemplo) 66,000000001
Espero haber sido claro y que esto le pueda servir a alguien.
Matias Parenti

Elizabeth,  16 enero, 2009 23:52  

Como puedo programar el rank para que me varié de fila y columna de acuerdo a la que yo quiera, me explico cuando yo grabo una macro el código me aparece es este ActiveCell.FormulaR1C1 = "=RANK(RC[-1],RC[-1]:R33C7,0" como lo puedo modificar para que r=a la fila que yo quiero, c= a la columna que yo quiero. gracias espero me puedan ayudar

Jorge L. Dunkelman 17 enero, 2009 20:22  

No está claro por qué quieres o tienes que usar macros, pero lo que tienes que hacer es definir variables para los rangos. Como supongo que no tienes mucha experiencia con macros, te sugiero que uses fórmulas con rangos dinámicos.

Anónimo,  01 agosto, 2009 02:12  

mmmmm
muchos numero y no entiendo mucho en fin
trate de hacerlo conporcentaje spero hay varios que se repiten
y me fije que eso solo sucedia cuando hay valores iguales, pero este no es el caso
como lo pued o arreglar?
quier o hacer un ranking con pórcentajes
el porcentaje mas alto es el primer lugar y el mas bajo el ultimo
pero se repiten
ayuda por favor ^ ^

Jorge L. Dunkelman 01 agosto, 2009 07:03  

Te sugiero que descargues el archivo con el ejemplo.
No hay ninguna diferencia en el calculo del ranking en una lista de porcentajes o de números entereos.

Anónimo,  27 agosto, 2009 20:10  

=JERARQUIA(B4,sales)+CONTAR.SI($C$4:C4,C4)-1
en esta formula, SALES que celda o que es lo que referencia?, o es una funcion mas del excel?
avisame por favor, gracias
si es posible enviarme un correo, estare muy agradecido
jnick025@hotmail.com

Jorge L. Dunkelman 28 agosto, 2009 18:14  

No, "sales" (ventas en inglés) es un nombre. Si no estás familiarizado con el uso de nombres en Excel, te sugiero que leas esta nota.

Anónimo,  31 agosto, 2009 20:21  

gracias, ya pude desifrar la cuestion y tambien ya funciono la formula, ahora tengo otro problema.
Resulta que tengo que llenar una hoja, en la cual lleva informacion de fletes, pero al final del turno tengo que registrar manualmente todos los fletes del dia, orden de trabajo, fecha, tipo de camion, etc...
habra una forma de que pueda agregar un boton a la pagina principal que lleno, para asi solo darle click ese boton(command botton) y que me guarde la informacion en otra hoja de excel ?... muchas gracias, saludos

Jorge L. Dunkelman 31 agosto, 2009 21:36  

Por supuesto. Puedes agregar un botón en la hoja y ligarlo a una macro que haga la tarea.

Anónimo,  02 septiembre, 2009 16:45  

Buenos dias, creo que no me explique muy bien, revise el enlace pero aun asi no me saco de dudas, agradeceria mucho si pudieras ayudarme de alguna forma con tus conocimientos. tengo una hoja de excel(sheet1), en donde registros todos los datos de un flete(nombre del chofer, placas del camion y todo lo referente a un embarque)el cual imprimo y al final del dia tengo que registralos en otro libro de excel manualmente y eso me quita tiempo, habra alguna forma de agregarle un boton(command button ) a la hoja que lleno al principio que diga registrar y que al picarle ese boton me guarde automaticamente toda la informacion en el otro libro donde llevo el registro ?, de esta forma ya no tendria que hacerllo manualmente al final del dia. espero me puedas ayudar, muchas gracias. saludos coordiales

Jorge L. Dunkelman 02 septiembre, 2009 18:18  

El asunto no es poner el botón sino escribir la macro que haga la tarea. Te sugiero que sigas en contacto conmigo por mail (el asunto de tu consulta no está relacionado con el tema de la nota y no es de interés general).

Anónimo,  02 septiembre, 2009 20:57  

ok muchas gracias , mira mi correo es este: jnick025@hotmail.com
no pude encontrar su correo para poderle escribir.

saludos

Jorge L. Dunkelman 03 septiembre, 2009 12:20  

El correo es jorgedun@gmail.com

AGM 07 octubre, 2009 01:28  

Muchas gracias por tu contribución. Esta función me sirvió mucho, solo quiero agregar q para que se ejecute correctamente hay q oprimir shift+ctrl+enter, porque es una función matricial, de no hacerlo así no se producirá ningún resultado.

Jorge L. Dunkelman 07 octubre, 2009 04:38  

No, la fórmula no es matricial. Puedes ver como funciona descargando el archivo con el ejemplo.

jcferpa 14 noviembre, 2009 00:53  

¿Qué pasa si quiero "Jerarquizar" de acuerdo a dos más criterios? He visto que ha sugerido en ocasiones anteriores no colocar la información en el propio comentario, así que le he enviado la consulta con el archivo adjunto a su correo.

Gracias!

Jorge L. Dunkelman 14 noviembre, 2009 11:19  

Tendrías que crear un campo auxiliar que combine los campos que usas como criterio para jerarquizar y luego calcular la posición de acuerdo a este campo.

Anónimo,  13 abril, 2010 19:52  

Estimado y como se puede hacer cuando esta repetido mas de dos veces

Mariano 16 mayo, 2010 18:33  

Como hago para que se acomoden descendentemente segun su rankink o jerarquia??
tipo para armar un tabla que se ordene automaticamente cuando yo cargue los datos segun el ranking!!!

Jorge L. Dunkelman 19 mayo, 2010 16:59  

La función JERARQUIA tiene un tercer argumento para indicar el orden (0 = descendente, cualquier otro número = ascendente). Una vez que tenés el número de orden usás esos valores en la función INDICE para obtener el nombre del primero, del egundo, etc.

Anónimo,  17 septiembre, 2010 14:06  

Os pido ayuda. Tengo un fichero con una serie de personas y una valoración de cada una de ellas, he utilizado la función =K.ESIMO.MAYOR, para que me haga el ranking de todos y luego he puesto al lado en otra celda la función buscarv para que me ponga el nombre y apellidos de cada uno de ellos. El problema que tengo es que cuando son las mismas valoraciones, me repite el nombre de la primera persona del mismo valor. ¿Como podría solventarlo, para que aunque tenga el mismo valor, me de el nombre del siguiente con ese mismo valor?. Muchísimas gracias por adelantado y felicitaros por el foro que tenéis, porque sirve de mucha ayuda.
Gracias de nuevo y saludos.

Anónimo,  05 junio, 2012 23:24  

Hola, tengo este ejemplo que no lo puedo definir. Bajé antes estos mismo ejemplos y los pude adaptar. Ahora no, tendrá algo que ver que uso una nueva versión de Excel?

A B C D E F
Ptos Pos Pos.s/empate
2 1 Luis 12 10 10
3 2 Maxi 24 4 4
4 3 Pablo 19 7 7
5 4 Lucas 28 3 3
6 5 Cristian 19 7 7

Uso la función jerarquía para definir la posición:

=JERARQUIA(D2;Ptos) y así hasta el final.

Hastá acá todo bien. El tema es que cuando quiero "desempatar" entre Pablo y Cristian la fórmula no lo hace. Uso

=SI(B4="";"";JERARQUIA(D4;Puntaje)+CONTAR.SI($C$2:$C$6;C4)-1)

y

=SI(B4="";"";JERARQUIA(D6;Puntaje)+CONTAR.SI($C$2:$C$6;C6)-1) respectivamente para los casos empatados.

Qué estoy haciendo mal?
Gracias!

Eduardo desde Entre Ríos.

Jorge L. Dunkelman 08 junio, 2012 18:25  

Tendrías que enviarme el archivo para que pueda entender lo que estás haciendo. Además, ¿cuál es la idea con la expresión B4="";"" en la fórmula?

Anónimo,  27 junio, 2012 06:37  

Tengo 10 alumnos con 10 notas , obtenido el promedio dentro de celdas por alumno, en otra columna quiero que de cada uno de ellos se me indique el orden de merito desde el promedio más alto, que corresponderá a 1 y asi sucesivamente, Tener en cuenta que podrian existir casos de igualdad de promedios. pero al haber igualdad se utilizara para el desempate la mejor calificacion en lenguaje, y si continua en historia.

Jorge L. Dunkelman 06 julio, 2012 17:05  

Tendrías que usar una columna auxiliar dande distintos pesos a cada materia/disciplina. De esta manera tendrías un promedio ponderado que usarías para calcular el orden de mérito con JERARQUIA.

Anónimo,  27 mayo, 2013 11:51  

¿Cómo realizar una jerarquia condicionada por otra columna (A) donde hay grupos de datos?

Necesito hacer una especie de "jerarquia.si" de los valores de la columna B (1,7,9,3,4,10,…etc) en funcion de los registros de la columna A (que esta dividida en grupos "sbcn", "sctg", "shlv", etc,…).

Esto es, que me jerarquice de todos los registros que tengan en la columan A "sbcn" por un lado, los de "sctg" por otro y asi sucesivamente.

Es hacer una jerarquia de datos por tramos (que no son iguales entre sí, unos tienen 4 valores, otros 8, otros 15, etc) y que están en la columna (A).

Jorge Dunkelman 28 mayo, 2013 20:39  

No me queda claro si querés ordenar los datos por tramos o calcular el orden de jerarquía, con la función JERARQUIA, por tramos.

julioman1979 09 septiembre, 2013 21:38  

Hola Jorge buenas tardes.
Quiero descargar tu programa para un campeonato que voy a iniciar pero no he podido.
Me podrias colaborar? mi correo es ceandura@hotmail.com

Jorge Dunkelman 10 septiembre, 2013 07:08  

Te sugiero descargar el modelo que presento en esta nota

Anónimo,  28 octubre, 2013 01:27  

Buenas tardes, utilice la formula de jerarquia pero me sigue dando lugares repetidos,

Jorge Dunkelman 28 octubre, 2013 18:20  

Te sugiero que descargues el archivo con el ejemplo (acabo de corregir el enlace) y te fijes allí cómo funciona.

Anónimo,  26 febrero, 2014 09:30  

¿y si hay 2 o mas primeros lugares....?; por ejemplo digamos que tenemos un agente 20 con la misma cantidad que el agente 12 (95438), el agente 12 y el 20 tendrian el primer lugar pero no mostrará el segundo , se saltaria a mostrar 3 lugar a los del segundo y así suscesivamente, como se corregiria?

Anónimo,  03 abril, 2014 17:52  

Buenas tardes tengo dos listas, una ventas de enero y otros ventas de febrero. Como se en que puesto quedo en febrero en comparación con enero

Jorge Dunkelman 03 abril, 2014 20:26  

¿Cómo están organizados los datos? ¿Dónde están los datos de cada mes?

Anónimo,  16 julio, 2014 18:43  

muy interesante, pero si tenemos dos criterios para ordenar como se realizaría la formula, por ejemplo: A tiene 3 puntos. y 2 goles diferencia, B tiene 3 puntos y 3 goles diferencia, entonces si aplicamos la formula solo en función a los puntos no habría problema, pero como hacerlo tomando en cuenta los goles de diferencia que tiene? porque tendría que asignarle a B el numero 1 por tener mas goles y a A el numero 2, pero como hacer la formula? gracias

Jorge Dunkelman 16 julio, 2014 21:07  

En el blog hay varias notas que tratan sobre el tema, por ejemplo esta

Guillermo Hernández Lee 30 junio, 2015 22:29  

Hola:

Hice la fórmula tal cual para un ranking de alumnos pero ahora tengo un problema, antes de usar "CONTAR.SI" tenía dos empates en segundo lugar y de ahí se salta hasta el cuarto, ahora con la función "CONTAR.SI" como lo explicas en la hoja si se hace el desempate, pero uno de los segundos lugares lo hace tercero, siendo que lo que quiero es que me los deje como empate y que me indique el tercer lugar y así sucesivamente.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP