jueves, abril 06, 2006

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

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 comentarios:

  1. 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.

    ResponderBorrar
  2. 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

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

    ResponderBorrar
  4. 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é

    ResponderBorrar
  5. 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.

    ResponderBorrar
  6. 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

    ResponderBorrar
  7. 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

    ResponderBorrar
  8. 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.

    ResponderBorrar
  9. 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 ^ ^

    ResponderBorrar
  10. 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.

    ResponderBorrar
  11. =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

    ResponderBorrar
  12. 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.

    ResponderBorrar
  13. 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

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

    ResponderBorrar
  15. 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

    ResponderBorrar
  16. 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).

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

    saludos

    ResponderBorrar
  18. 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.

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

    ResponderBorrar
  20. ¿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!

    ResponderBorrar
  21. 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.

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

    ResponderBorrar
  23. 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!!!

    ResponderBorrar
  24. 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.

    ResponderBorrar
  25. 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.

    ResponderBorrar
  26. 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.

    ResponderBorrar
  27. 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?

    ResponderBorrar
  28. 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.

    ResponderBorrar
  29. 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.

    ResponderBorrar
  30. ¿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).

    ResponderBorrar
  31. 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.

    ResponderBorrar
  32. 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

    ResponderBorrar
  33. Te sugiero descargar el modelo que presento en esta nota

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

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

    ResponderBorrar
  36. ¿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?

    ResponderBorrar
  37. 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

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

    ResponderBorrar
  39. 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

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

    ResponderBorrar
  41. 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.

    ResponderBorrar

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