Construir con Excel una tabla con las 10 primeras posiciones.

sábado, octubre 21, 2006

Uno de mis lectores me consulta como construir con Excel una tabla donde aparezcan los 10 alumnos de un curso que han conseguido el mejor puntaje. El pedido incluye que la tabla se actualice automáticamente a medida que se vayan ingresando nuevos datos.

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:

37 comments:

Anónimo,  07 noviembre, 2006 03:53  

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.

Jorge L. Dunkelman 07 noviembre, 2006 22:14  

Hola
todo 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".

sueñoprofundo 26 diciembre, 2007 13:34  

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

A 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

sueñoprofundo 26 diciembre, 2007 13:37  

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

A 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

Jorge L. Dunkelman 26 diciembre, 2007 16:23  

Mandame el archivo a jorgedun@gmail com

hug0mx 04 febrero, 2008 04:28  

Buen 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)

Jorge L. Dunkelman 04 febrero, 2008 20:11  

Usando la función JERARQUIA junto CONTAR.SI, para el desempate.

Edgard,  04 mayo, 2008 18:46  

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

Jorge L. Dunkelman 07 mayo, 2008 17:06  

Hola Edgard

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

dman 11 mayo, 2008 08:20  

Antes que nada, gracias por allanarnos el camino del Excel.

Consulta: 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?

Anónimo,  11 mayo, 2008 10:03  

Jorge:

Si 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,

Jorge L. Dunkelman 11 mayo, 2008 17:04  

Con INDICE y COINCIDIR, como está indicado en la nota

Jorge L. Dunkelman 11 mayo, 2008 17:12  

Los tiempos en Excel son números, así que puedes usar la función JERARQUIA.
Esta 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).

Anónimo,  15 mayo, 2008 23:51  

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

Jorge L. Dunkelman 16 mayo, 2008 06:49  

Hola Ezequiel,

podés fijarte en esta nota.
Un modelo más elaborado, que usa macros, podés verlo aquí.

Anónimo,  22 mayo, 2008 18:05  

gracias por considerar mi comentario y por tu ayuda..
esta muy bueno lo que haces..
tratare de ponerlo en practica..

ezequiel...

Anónimo,  16 mayo, 2010 05:37  

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

Jorge L. Dunkelman 19 mayo, 2010 16:56  

Usando JERARQUIA para obtener el número de orden de acuerdo al puntaje. Luego con INDICE los organizás de mayor a menor.

Anónimo,  18 enero, 2011 01:06  

Hola Jorge,

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

Jorge L. Dunkelman 19 enero, 2011 11:58  

Se puede, pero tendrías que construir algunas columnas auxiliares para considerar todas las posibilidades que mencionás.
Podés orientarte con las técnicas que muestro en esta nota.
También podés ver esta nota sobre cómo ordenar con JERARQUIA.

Ignasi D,  20 julio, 2012 14:04  

Muchas gracias por el post.
Me 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

Jorge L. Dunkelman 20 julio, 2012 17:24  

Se puede hacer con formato condicional, pero tendrás que tener una columna con el puntaje anterior del alumno para hacer la comparación.
En 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.

Angel Marcelo 05 marzo, 2013 01:49  

Simplemente asombroso!!!

Anónimo,  13 mayo, 2013 23:34  

Estimado Jorge:

De 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

Jorge Dunkelman 14 mayo, 2013 15:39  

BUSCARV o una combinación de INDICE y COINCIDIR

Pablo Vergara 22 mayo, 2013 20:35  

Hola Jorge, te queria consultar cuales son las formulas que utilizaste para los rangos alumno_posicion y alumno_puntaje. De antemano muchas gracias!

Jorge Dunkelman 22 mayo, 2013 23:32  

Podés descargar el ejemplo (el enlace donde dice "para construir este modelo..."

Federico Seco Moran 30 marzo, 2015 17:44  

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

Jorge Dunkelman 31 marzo, 2015 07:17  

Hola 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.
Como comprenderás la forma de hacerl depende de cómo esté organizado el modelo y explicarlo excede las posibilidades de un comentario.

Mario Escudero 17 octubre, 2015 01:35  

Gracias Jorge. Encontré exactamente lo que necesitaba.

Anónimo,  20 diciembre, 2015 02:29  

Hola; 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.

Jorge Dunkelman 21 diciembre, 2015 07:27  

Fijate en el enlace Ayuda (en la parte superior del blog). Ahí están las instrucciones como enviar consultas.

miraquienes 31 enero, 2017 14:12  

Hola, tengo una duda sobre crear un ranking usando la función Jerarquía.
Só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?

Jorge Dunkelman 01 febrero, 2017 07:52  

No ppuedo ayudarte sin ver tu hoja, pero supongo que hay una fila vacía eb algún lugar de la tabla.

miraquienes 01 febrero, 2017 13:12  

Te puedo mandar un enlace o foto?

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

Jorge Dunkelman 02 febrero, 2017 08:10  

Coo te puse en mi anterior comentario, sin ver tu hoja no puedo darme una idea del problema.

academia Majadahonda 19 julio, 2017 12:26  

Excelente post e información toda la que compartes ya que me va a venir de maravilla de cara al futuro, un saludo!

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP