Para construir este modelo tomaremos en consideración dos elementos:
1 – La función JERARQUIA (RANK en la versión inglesa)
2 – Rangos dinámicos con nombres.
Supongamos esta lista de alumnos con sus notas
Para la posición de cada uno de acuerdo al puntaje usamos la función JERARQUIA (RANK en la versión inglesa). En la celda C2 escribimos la fórmula =JERARQUIA(B2,puntaje) y la copiamos al resto de las celdas.
El argumento "puntaje" en la función es un rango dinámico definido en un nombre. La definición es =DESREF(Hoja1!$B$2,0,0,CONTARA(Hoja1!$B:$B)-1,1).
El objetivo del rango dinámico es permitir que la fórmula se vaya adaptando a medida que agregamos o quitamos alumnos de la lista.
Si observamos el resultado de la fórmula veremos que hay un problema
Ana y Enrique, al tener el mismo puntaje reciben el mismo número de posición. Ambos reciben la posición 10 y el próximo en la lista recibirá la posición 12.
Para solucionar este problema creamos un nuevo campo en la columna D, al que llamamos "posición sin empate", con la fórmula
=JERARQUIA(B2,puntaje)+CONTAR.SI($C$2:C2,C2)-1
Al ordenar la tabla en orden ascendente de "puntaje sin empate"
podemos ver las diferencias entre los resultados de ambas fórmulas.
Ahora podemos crear un cuadro que muestre los primeros diez alumnos del curso
En la columna F ponemos números (constantes) de 1 a 10.
En la columna G usamos la fórmula
=INDICE(alumno,COINCIDIR(F2,posicion_sin_empate,0)),
que usa los nombres
Alumno: =DESREF(Hoja1!$A$2,0,0,CONTARA(Hoja1!$A:$A)-1,1)
Posición_sin_empate: =DESREF(Hoja1!$D$2,0,0,CONTARA(Hoja1!$D:$D)-1,1)
En la columna H usamos, similarmente la fórmula
=INDICE(puntaje,COINCIDIR(F2,posicion_sin_empate,0))
El problema con esta tabla es que "deja afuera" a Enrique y a Pablo que tienen el mismo puntaje que Ana.
Lo que queremos lograr es una tabla que muestre todos los alumnos que comparten las primeras diez posiciones. En nuestro ejemplo hay 12 alumnos con los mejores diez puntajes.
Para solucionar este problema modificamos nuestra tabla y sus fórmulas:
1 – agregamos un campo (número de orden) con la fórmula
=SI(INDICE(posicion,COINCIDIR(FILA()-1,posicion_sin_empate,0))>10,"",FILA()-1)
2 – En la columna K usamos la fórmula
=SI(CELDA("contenido",J2)="","",BUSCARV(L2,alumno_posicion,3,0))
3 - En la columna L usamos la fórmula
=SI(CELDA("contenido",J2)="","",INDICE(alumno,COINCIDIR(FILA()-1,posicion_sin_empate,0)))
4 - En la columna M usamos la fórmula
=SI(CELDA("contenido",J2)="","",BUSCARV(L2,alumno_puntaje,2,0))
Todas estas fórmulas usan una función SI para condicionar el resultado. Si el valor en el campo "número de orden" nos es "blanco" las fórmulas darán el resultado buscado; en caso contrario el resultado es "blanco".
El número de líneas de esta tabla debe coincidir con el número de alumnos en el curso.
Estas fórmulas usan función CELDA que es volátil y también funciones de búsqueda INDICE, COINCIDIR y BUSCARV, con búsqueda exacta. En hojas con un gran número de alumnos esto puede causar que la recalculación sea lenta.
Una alternativa para esos casos es utilizar Tablas Dinámicas. Los pasos a dar son los siguientes:
1 – definimos un rango dinámico rango_
td: =DESREF(Hoja1!$A$1,0,0,CONTARA(Hoja1!$A:$A),4)
para que nuestra tabla dinámica se adpate a los cambios en la lista de alumnos
2 – Ponemos el campo Alumnos en el área de filas; en el área de datos ponemos Posición y Puntaje.
3 - Abrimos el menú de configuración de campo
y apretamos el botón Avanzado
4 - en el diálogo de avanzado definimos Opciones de Autoordenar: ascendente; activamos la opción de mostrar los 10 valores inferiores; en ambas ventanillas de "Usar campo" elegimos "posición"
El resultado es
La ventaja de usar tablas dinámicas es que nos exime de escribir fórmulas complicadas y los cálculos son mucho más eficientes en términos de tiempo; la desventaja es que las posibilidades de formato son menores que en tablas normales de Excel.
Categorías: Funciones&Formulas_, Manejo de Datos_
Technorati Tags: Excel
Hola este ejemplo es justo lo que necesitaba te pasaste...., Pero quisiera saber como hacer lo contrario es decir el que tenga menos puntaje que sea el primero, el penultimo el segundo y asi sucesivamente, intente hacerlo con jerarquia pero no sale, gracias por tus aportes la verdad es que son geniales.
ResponderBorrarHola
ResponderBorrartodo lo que tenés que hacer es agregar un tercer argumento en la función JERARQUIA para que el orden sea ascendente.
En el ejemplo de la nota, en la celda C2, en lugar de =JERARQUIA(B2,puntaje), escribi =JERARQUIA(B2,puntaje,1) y copiá la fórmula a todo el rango.
Lo mismo en D2. EL resto de las fórmulas se adaptarán automáticamente.
En cuanto a la tabla dinámica, tenés que abrir el menú de configuración de campo, apretar el botón Avanzado y elegir las opciones "Opción de autoordenar--Ascendente", "Usar campo--Puntaje".
hola buenos dias, me gustaria que me solucionaras una duda: llevo los datos de una liga de amigos, y me gustaria ordenar la clasificacion, la tabla se encuentra en la posicion A622 hasta E650, estando organizada de la siguiente forma
ResponderBorrarA B y C D E
NOMBRE EQUIPO PUNTOS
esa linea de texto esta en la fila 622, me podria decir que hacer para ordenar la clasificacion? si quiere me puede decir como mandarle el archivo, pero soy nuevo aqui y no se como hacerlo. muchas gracias
hola buenos dias, me gustaria que me solucionaras una duda: llevo los datos de una liga de amigos, y me gustaria ordenar la clasificacion, la tabla se encuentra en la posicion A622 hasta E650, estando organizada de la siguiente forma
ResponderBorrarA B y C D E
NOMBRE EQUIPO PUNTOS
esa linea de texto esta en la fila 622, me podria decir que hacer para ordenar la clasificacion? si quiere me puede decir como mandarle el archivo, pero soy nuevo aqui y no se como hacerlo. muchas gracias
Mandame el archivo a jorgedun@gmail com
ResponderBorrarBuen día, me sirven enormemente tus explicaciones, pero yo en mi trabajo necesito que ante empate de jerarquias, el criterio a evaluar para desempatar sea la productividad del trabajador, esta productividad es medible en un número (ej. 8.72)
ResponderBorrarUsando la función JERARQUIA junto CONTAR.SI, para el desempate.
ResponderBorrarGracias Jorge, por compartir tus conocimientos, me sirven tus datos y necesitaba esta ayuda, pero tengo un problema, este es el caso: dos alumnos comparten un mismo lugar 1er lugar, yo considero un empate, pero el segundo lugar seria el tercer alumno, y el cuarto alumno tendria el tercer lugar,como yo hago para que la tabla me muestre con cifras esto, haciendo uso de tu ejemplo carlos seria el segundo, pero tu tabla le pone posición 3, como podria hacerlo en este caso, y otra duda puede el excel poner el orden con 1ero, 2do, 3ero...8vo, en lugar de numerarlo?, espero tu respuesta, y te agradesco de antemano tu ayuda.
ResponderBorrarHola Edgard
ResponderBorrarTushar Meta desarrolló una fórmula para solucionar ese problema.
Aplicada a nuestro ejemplo, donde el resultado de la función JERARQUIA están en el rango C2:C25, la fórmula es
={SUMA(1/SI($C$2:$C$25 < C2,CONTAR.SI($C$2:C25,$C$2:$C$25),9.999999999E+307))+1}
Esta es una fórmula matricial; en lugar de apretar ENTER usamos CTRL+MAYUSCULAS+ENTER
Ponemos la fórmula en la primer celda del rango y luego la copiamos al resto del rango.
Antes que nada, gracias por allanarnos el camino del Excel.
ResponderBorrarConsulta: si los datos se presentan desordenados, ¿cómo ordenarlos, es decir que Cristina aparezca en el 1º lugar, Perla en el 2º y así sucesivamente?
Jorge:
ResponderBorrarSi en lugar de calificaciones fuesen tiempos cronometrados, ¿cuál sería la función adecuada para que excel reconozca las diferencias de tiempos?
Cuando cambio el formato de celda el ejemplo aparecido en mejores_10.xls no funciona, ya que coloca el tiempo del más rápido en último lugar, en especial cuando intento cargarle los tiempos con centésimas de segundo.
Gracias desde ya,
Con INDICE y COINCIDIR, como está indicado en la nota
ResponderBorrarLos tiempos en Excel son números, así que puedes usar la función JERARQUIA.
ResponderBorrarEsta función tiene un paramétro opcional para indicar el orden de posición del valor. Si no indicas ningún valor para el parámetro, la función asigna al menor valor (en tu caso el tiempo más rápido) la última posición. Para que el menor valor reciba la primer posición debes usar la función de esta manera:
=JERARQUIA(número,referencia,1).
hola, la verdad me intereso mucho el comentario y qisiera saber si me podes ayudar. Mi nombre es ezequiel, tengo 19 años y me interesa mucho el uso de excel, por eso estoy empezando a investigar por mi cuenta, o con ayuda de algun tutorial aunque son dificiles de encontrar.
ResponderBorrarEn fin, estoy tratando de hacer un fixture como el de un mundial de futbol. En una hoja se escriben los resultados de los partidos y automaticamente se completan los datos de los equipos en la tabla de la otra hoja (puntos, partidos ganados, diferencia de goles, etc.), quiero saber si se puede lograr que se autoordenen los equipos por puntos (si son iguales, por diferencia de goles) sin la necesidad de hacer una tabla dinamica.
Seguramente se me presenten muchas mas dudas en el camino pero por ahora es eso lo que no me deja continuar.
Espero puedas ayudarme, desde ya muchas gracias.
Hola Ezequiel,
ResponderBorrarpodés fijarte en esta nota.
Un modelo más elaborado, que usa macros, podés verlo aquí.
gracias por considerar mi comentario y por tu ayuda..
ResponderBorraresta muy bueno lo que haces..
tratare de ponerlo en practica..
ezequiel...
Hola jorge es muy bueno tu blog me ha servido de mucho.. Tengo un problema y no lo puedo solucionar, tengo una tabla con: jugadores (columna B), puntaje (columna C) y posicion (columna D). Esta tabla esta en orden de jugadores , yo quiero hacer otra al lado ordenada por posiciones pero no me sale, no puedo... Que formula tendria que usar. Cabe aclarar que mi tabla tiene empates y quiero que los empates queden, pero cuando uso indice y coincidir me pone siempre mismo jugador para los jugadores del mismo puntaje.... desde ya agradezco tu ayuda... SERGIO
ResponderBorrarUsando JERARQUIA para obtener el número de orden de acuerdo al puntaje. Luego con INDICE los organizás de mayor a menor.
ResponderBorrarHola Jorge,
ResponderBorrarHe llegado a tu blog intentando resolver un problema de clasificación de golf usando Excel. Soy un novato en esto de Excel y lo que estoy consiguiendo es mediante copia y adaptación pero no encuentro nada que se parezca a lo que quiero hacer.
Te explico por si puedes ayudarme. Tengo una tabla dinámica de Excel con las siguientes columnas:
Columna A (Jugador): J1, J2, J3, J4, J5
Columna B (Hoyos jugados): 4, 1, 0, 6, 0
Columna C (Par): -1, +1, 0, -1, 0
Tengo que crear una nueva columna D con la clasificación que se establece según los siguientes criterios:
* Los jugadores sin hoyos jugados "no cuentan" para el orden (deberían ir al final de la lista)
* El orden se establece mediante la columna Par sabiendo que menor es mejor (-2 es mejor que +2)
* Si un par es igual, gana quien tenga jugados más hoyos (en el ejemplo, J1 y J4 tienen par -1 pero J4 iría primero por llevar 6 hoyos).
La clasificación debería ser la siguiente:
Columna D (Clasificación): 2, 3, 4, 1, 5
¿Se puede conseguir con una fórmula como las mencionadas en este artículo? He aplicado alguna pero claro, cuenta todos los valores incluidos los ceros y los inserta enmedio ...
Espero que se pueda. Gracias por todo.
Se puede, pero tendrías que construir algunas columnas auxiliares para considerar todas las posibilidades que mencionás.
ResponderBorrarPodés orientarte con las técnicas que muestro en esta nota.
También podés ver esta nota sobre cómo ordenar con JERARQUIA.
Muchas gracias por el post.
ResponderBorrarMe ha servido para crear un ranking de puntos conseguidos a lo largo del mes.
He estado pensando la manera de mejorar la tabla añadiendo una columna que muestre un valor según la posición anterior de cada alumno. Es decir, si el alumno mejora su posición en el ranking desde la última entrada de datos, aparece una flecha hacia arriba, si empeora aparece la flecha hacia abajo, si mantiene su lugar en el ranking, aparece el signo "=".
¿Me podrías ayudar?
Muchas gracias por adelantado.
Ignasi
Se puede hacer con formato condicional, pero tendrás que tener una columna con el puntaje anterior del alumno para hacer la comparación.
ResponderBorrarEn esta nota muestro la técnica para hacerlo con Excel 97-2003; en esa misma nota hay un enlace a otra nota donde muestro como hacerlo con Excel 2007/10.
Simplemente asombroso!!!
ResponderBorrarEstimado Jorge:
ResponderBorrarDe una lista de alumnos, mediante formulario, requiero listar de manera de poner las notas según el apellido que se escribe en el formulario. ¿Qué función es recomendable, por favor?
Atentamente
BUSCARV o una combinación de INDICE y COINCIDIR
ResponderBorrarHola Jorge, te queria consultar cuales son las formulas que utilizaste para los rangos alumno_posicion y alumno_puntaje. De antemano muchas gracias!
ResponderBorrarPodés descargar el ejemplo (el enlace donde dice "para construir este modelo..."
ResponderBorrarbuenas tardes Jorge. Mi pregunta es si existe alguna solución para cuando se genera la clasificacion de equipos de futbol en excel, el empate a puntos entre varios equipos se tomen en cuenta los enfrentamientos directos entre ellos, no solamente reflejando la diferencia entre goles a favor y en contra. Le agradeceria que me lo explicase. Atentamente reciba un saludo.
ResponderBorrarHola Federico, existen soluciones pero no se trata de aplicar simplemente una fórmula. Tendrías que tener una hoja donde registrar los resultados entre los equipos y crear una especie de índice que te permita saber cuál fue el vencedor en el encuentro.
ResponderBorrarComo comprenderás la forma de hacerl depende de cómo esté organizado el modelo y explicarlo excede las posibilidades de un comentario.
Gracias Jorge. Encontré exactamente lo que necesitaba.
ResponderBorrarHola; mi nombre e JOSÉ AGUERO, quiero sacar el 1er, 2do, 3er, 4to y 5to lugar de los registros diarios que recibo de los funcionarios policiales, deseo establecer quien fue mas eficaz por las puntuaciones adquiridas de la tabla de reportes; diariamente recibo las de 200 reportes y al fina de mes deseo saber quienes son los 5 primeros lugares por su trabajo desarrollado. ahorita mismo estoy utilizando la siguiente formula: =INDICE(HOJA2!$C$5:$C$1063;COINCIDIR(K.ESIMO.MAYOR(FRECUENCIA(HOJA2!$C$5:$C$1063;HOJA2!$C$5:$C$1063);1);FRECUENCIA(HOJA2!$C$5:$C$1063;HOJA2!$C$5:$C$1063);0)) para el primer lugar; pero he observado que me da es el primer lugar por cantidad de reportes y no por los Puntos acumulados; y deseo también que la formula sea mas exacta en las posiciones que se desea mostrar sin empates.! Si considera poca información me gustaría enviar un archivo o imagen detallada.
ResponderBorrarFijate en el enlace Ayuda (en la parte superior del blog). Ahí están las instrucciones como enviar consultas.
ResponderBorrarHola, tengo una duda sobre crear un ranking usando la función Jerarquía.
ResponderBorrarSólo me hace la lectura de datos una parte de la tabla, pero no entera. No sé dónde está el error por más vueltas que le doy.
Me podrías ayudar?
No ppuedo ayudarte sin ver tu hoja, pero supongo que hay una fila vacía eb algún lugar de la tabla.
ResponderBorrarTe puedo mandar un enlace o foto?
ResponderBorrarLa fórmula que utilizo es:
=JERARQUIA(E2;$E$2:$E$23;0)+CONTAR.SI($E$2:E2;E2)-1
Así hasta la fila 23. Pero sólo me ordena hasta la fila 15, y de la 16 a la 23 hace otro orden.
Lo curioso que me lo ordena bien, me dice el orden pero no me lo clasifica.
No sé dónde puede estar el fallo...
Coo te puse en mi anterior comentario, sin ver tu hoja no puedo darme una idea del problema.
ResponderBorrarExcelente post e información toda la que compartes ya que me va a venir de maravilla de cara al futuro, un saludo!
ResponderBorrar