Construir una tabla de posiciones usando las funciones JERARQUIA, SUMAPRODUCTO, INDICE y COINCIDIR

viernes, junio 02, 2006

En mi entrada anterior sobre ranking de listas con más de un criterio mostré como construir una tabla de posiciones para un campeonato de fútbol. Para determinar la posición de cada equipo tomamos en cuenta la cantidad de puntos obtenidos en caso de equipos con el mismo puntaje, la cantidad de goles a favor. De persistir el empate la posición se determina por la mejor diferencia de goles.

Este es un
ejemplo simplificado de una tabla de posiciones, donde sólo tomamos en cuenta los puntos obtenido y la diferencia de goles.
A diferencia del ejemplo de la nota anterior, aquí no hay necesidad de ordenar la tabla con Datos--Ordenar. La tabla de posiciones se ordena automáticamente (con fórmulas INDICE---COINCIDIR).

Este modelo esta formado por dos tablas:

La tabla de posiciones





La tabla de cálculos auxiliares.





Todos los cálculos para de terminar las posiciones se hacen en la tabla auxiliar. En la tabla de posiciones usamos una fórmula que combina las funciones INDICE y JERARQUIA para ordenar la tabla.

En la tabla de cálculos auxiliares usamos las siguientes fórmulas:

En al celda Q7: =O7-P7, para calculas la diferencia de goles

En la celda R7: =L7*3+M7, para calcular el puntaje (victoria = 3 puntos; empate = 1 punto)

En la celda S7: =JERARQUIA(R7,$R$7:$R$11), para calcular la posición relativa de cada equipo. Cuando dos equipos tienen la misma cantidad de puntos, ambos reciben el mismo número de de orden.

En la celda T7: =SUMAPRODUCTO(($R$7:$R$11=R7)*(Q7<$Q$7:$Q$11)) para generar un ranking entre los equipos de igual cantidad de puntos, de acuerdo a la diferencia de goles. Esta fórmula calcula un número de orden interno sólo para los equipos que tienen el mismo puntaje (el mismo número de orden con la función JERARQUIA). Si esto no se cumple, da como resultado cero.

En la celda U7: =S7+T7, para calcular la posición final del equipo.



Categorías: Funciones&Formulas_



Technorati Tags:

COINCIDIR

59 comments:

JC,  12 septiembre, 2006 19:26  

ME PUEDES ENVIAR UNA TABLA DE POSICIONES PARA UNA ESCUELA DE FUTBOL EN CHILE GRACIAS

Jorge L. Dunkelman 12 septiembre, 2006 23:16  

Hola JC
todo lo que tienes que hacer es descargar el ejemplo en esta nota (pulsando el enlace) y adaptarlo a tus necesidades.
Un modelo más elaborado puedes encontrar en esta nota. También aquí puedes descargar el ejemplo.
Si necesitas adaptar el modelo y te resulta complicado, ponte en contacto conmigo por mail.

jc,  13 septiembre, 2006 18:21  

Jorge gracias por responder y ayudarme, pero como puedo hacer para colocar las fechas de partidos y la tabla se vaya actualizando automaticamente
ejemplo

blanco 1 v/s azul 3
puntos pj pg pe pp gf gc dif
blanco 1 1 0 0 1 1 3 -2
azul 3 1 1 0 0 3 1 2

Jorge para que quede asi como lo podre hacer

gracias por tu ayuda

Anónimo,  13 septiembre, 2006 21:46  

maestro, gracias por responder, pero me puedes ayudar con una tabla real, donde colque los marcadores y se vaya acumulando sola ejemplo :

A 1 v/s B 0
Equipos PJ Pg Pe PP
A 1 1 0 0
B 1 0 0 1

pero que solo coloque el marcador y sea automatico

es para una escuela de futbol de chile.

gracias por tu ayuda.

Jorge L. Dunkelman 14 septiembre, 2006 00:20  

Les voy que tener que pedir un poco de paciencia ya que en estos días dispongo de poco tiempo.
En unos días publicaré algo al respecto.
Una pregunta, la escuela de fútbol de Chile es gratuita?

Anónimo,  15 septiembre, 2006 23:02  

sip es para niños d escasos recursos.

Jorge L. Dunkelman 19 septiembre, 2006 23:15  

He subido una tabla de posiciones que se va actualizando automaticamente a medida que se anotan los resultados (pulsar el enlace para descargar).
Funciona sobre la base de partidos ida y vuelta (local - visitante), 3 puntos por partido ganado, 1 punto por empate. En caso de igualdad de puntos la posicion depende de la diferencia de goles. Si el empate persiste, por goles a favor.
Los resultados hay que anotarlos en la hoja "Resultados" en formato "goles local - goles visitante". Por ejemplo "3-2", sin las comillas, por supuesto.
Pruebenlo y avisenme si hay defectos o errores.

Anónimo,  15 abril, 2007 00:35  

hola amigo jorge, googleando encontre tu blog esta muy bueno, mi correo es el siguiente m_s_r_m@hotmail.com, estuve tratando de bajar los archivos para la tabla de posiciones para un campeonato de futbol, pero los links parecen ya no funcionar, si pudieras enviarme los archivos a mi correo, ademas si sabes como hacer lo mismo en access o algo como php contra una base de datos mysql para poner en el web te lo agradeceria si no, me conformo con los archivos excell de la tabla de posiciones para un campeonato de futbol, desde ya muchas gracias

Jorge L. Dunkelman 15 abril, 2007 20:40  

Hola, puedes descargar una tabla de posiciones más elaborada que aparece en esta nota

Además, he corregido el enlace del archivo de esta nota

Alien-Games 29 junio, 2007 18:50  

te cuento jorge que cuando baje este archivo y puse todos los datos en 0 me sale errores #n/a y solo queda el primer lugar a que se debe eso y como lo puedo arreglar si no es mucha molestia

Jorge L. Dunkelman 29 junio, 2007 21:48  

Hola, tienes razón. De tan sencilla, la tabla no tomaba en cuenta esa situacion. He modificado la tabla, que puedes descargar apretando el enlace en la nota.

Gracias por la observación y por leer el blog.

Alien-Games 30 junio, 2007 13:17  

perdon jorge que sea tan odioso pero te cuento que ahora que pongo resultados en la tabla auxiliar los equipos de la tabla de posiciones no ordena correctamente y uno de los equipos se repite dos veces.

muchas gracias por tu comprension

Jorge L. Dunkelman 30 junio, 2007 15:36  

Debo confesar que después de corregir el archivo no lo he revisado a fondo. Sin embargo, parecía funcionar bien.
Volveré a revisarlo. Mientras tanto, por favor mandame el archivo que estás usando (jorgedun@gmail.com).
Gracias

Jorge L. Dunkelman 30 junio, 2007 15:58  

OK, he revisado y veo que hay un problema cuando dos equipos empatan en todos los aspectos, puntos y diferencia de goles.
En esos casos, el equipo que aparece primero en la lista aparece dos veces.
He modificado nuevamente el modelo, para crear un desempate en esos casos. Sobre la técnica para jhacerlo pueden leer mi nota Función JERARQUIA (RANK) en Excel – Nota adicional
El modelo corregido se puede descargar en el enlace de esta nota.

Alien-Games 01 julio, 2007 18:22  

jorge a que se debe que la tabla repita a los equipos.


nota.- te mande al correo el archivo

Jorge L. Dunkelman 01 julio, 2007 22:13  

Hola,
se debe a que me confundí de archivo y subí uno equivocado. Acabo de subir el archivo corregido y lo podés bajar apretando el enlace (también te lo mandé por mail)
Gracias por la paciencia

Daniel,  14 julio, 2007 02:23  

Gracias amigo por la tabla me faltaba como ordenar la tabla automaticamente, gracias, ya hice la tabla... visita el blog de mi equipo Estudiantes de Santiago del Estero: www.estuky.blogspot.com
Daniel

Jorge L. Dunkelman 14 julio, 2007 07:10  

Muy lindo el blog, te felicito.

Luis 17 octubre, 2007 03:15  

Jorge muchas gracias por tu respuesta. Simplemente tengo una duda, supongamos que en la primera rueda el equipo A (local) juega con el equipo B (visitante)y el resultado es 3-2 a favor del local, eso hace que en la tabla de resultados coloque 3-2. En la segunda rueda juega el equipo B (local) con el equipo A (visitante)y sale por ejemplo 4-2 a favor del visitante. Yo tengo ocupada la celda con el anterior resultado (3-2) de la primer rueda, donde coloco el 4-2?
Jorge muchas gracias por todo.

Jose Maria

Jorge L. Dunkelman 17 octubre, 2007 17:56  

Hola José María (o Luis?)

tu comentario anterior apareció en mi nota sobre el modelo para un campeonato de fútbol y se refiere a ese modelo y no al que aparece en esta nota.
Refiérete a esa nota, por favor.

alberto4ever 11 noviembre, 2008 05:31  

hola jorge, tengo un problema, quiero que me pongas una formula q olvidé, se trata de una lista de rankin de jugadores, en la cual ellos van subiendo y bajando posiciones. Bueno yo la hago en 2 listas, y comparo la primera con la 2da. un ejm y asi me entiendes
1ra tabla
1 juan 203
2 jose 189
3 roberto 182
2da tabla
1 roberto 217
2 jose 209
3 juan 205
es facil darse cuenta que roberto subio 2 posiciones, sinembargo la lista q yo tengo es muy grandes y en realidad son necesarias las formulas. ojala puedan ayudarme. Salu2

Jorge L. Dunkelman 13 noviembre, 2008 23:09  

Si quieres saber cuantos puestos han subido o bajado cada jugador, puedes usar la función JERARQUÍA para obtener el ranking del jugador en cada tabla y luego restar entre lo resultados.

Anónimo,  07 mayo, 2009 09:29  

Buenos dias
La tabla se ve bastante util, pero quisiera poder aprender a utilizarla, me podria ayudar a hacerlo Señor Jorge...
un saludo
andres morales (colombia)
anfemo246@yahoo.es

Jorge L. Dunkelman 07 mayo, 2009 19:21  

Andrés
está todo explicado en la nota. Además puedes descargar el archivo con el ejemplo.

nachojc 19 mayo, 2009 02:25  

Hola, ya gracias a ti e creado toda la tabla y me da los resultados que quiero en cuanto a suma y resta de puntos por partidos ganados, empatados, perdidos y goles a favor y encontra.
A hora lo que no se como hacer es que los acomode segun el total de puntos (no importa la diferencia de goles, estos no quiero que intervengan en ese ranking de posiciones.), osea en posiciones normales 1,2,3,4,5,6, etc.solo por los puntages de partidos ganados perdidos y empatados.
Muchas Gracias , Nacho.

Jorge L. Dunkelman 19 mayo, 2009 20:56  

Nacho
nodificando la tabla auxiliar de manera que sólo calcule el puntaje sin tomar en cuenta los goles a favor y en contra, es decir, sin las columnas S, T y U.

Anónimo,  25 junio, 2009 22:10  

Jorge: en primer lugar felicitaciones por el Blog!
Tengo un inconveniente ya que tratando de adaptar la Tabla de posiciones al Basket hay ciertas diferencias en cuanto al puntaje que no he podido resolver y me gustaría su comentario y/o ayuda.
En Basket no hay empate, el gana obtiene 2 ptos. y el que pierde 1. Pero cuando un equipo no se presenta a un partido entonces se le da por perdido 20 a 0, pero no debe sumar puntos para el perdedor. Entonces pueden haber encuentros suspendidos 20 a 0, o 0 a 20 de acuerdo a la localía. Cómo puedo corregir este inconveniente para que la Tabla funcione también para Basket?
Muchas gracias y Felicitaciones nuevamente. Carlos

Jorge L. Dunkelman 25 junio, 2009 23:04  

Habría que hacer varios cambios y la explicación excede el objetivo de un comentario. Si encuentro un poco de tiempo en la semana, tal vez publique un modelo adaptado al basquet.
Gracias por las felicitaciones.

Mario 16 julio, 2009 15:12  

Gracias Jorge por todo lo que nos enseñas. Estoy intentando aplicar tus enseñanzas a un torneo de tenis donde se juega sólo a un set. Los jugadores se distribuyen en distintos grupos de cuatro jugadores y pasan a la segunda ronda los dos primeros. ¿Cómo podría solucionar el problema qye se presenta cuando dos jugadores quedan empatados en partidos ganados, juegos a favor y en contra y el criterio que define es el enfrentamiento entre ellos?

Jorge L. Dunkelman 16 julio, 2009 21:59  

Mario, me parece que los más práctico es corregir el resultado manualmente. Por supuesto, se puede crear una columna auxiliar con el criterio correspondiente, pero se me ocurre que será bastante complicado.

Mario 20 julio, 2009 17:33  

Gracias Jorge. Siguiendo tu consejo lo dejaré para modificarlo manualmente en el caso de que se diera ese caso de empate. Dado que me gustaría tratar de avanzar en la solución, si me pudieras orientar en las funciones que tendría que usar para contemplar el desempate teniendo en cuenta el resultado entre dos participantes, me sería muy útil. Gracias nuevamente.

Jorge L. Dunkelman 22 julio, 2009 18:31  

En la nota muestro como calcular el desempate en base a la diferencia de goles. ¿Cuál sería el criterio en tu modelo?

Anónimo,  26 julio, 2009 03:21  

Hola Jorge! Te quería consultar nuevamente si tuviste algún momento para ver cómo se puede adaptar la tabla al Basket.
Excelente el Blog, lo estoy recomendando a todos mis amigos y conocidos.
Gracias por tu atención y disculpas por las molestias. Carlos

Jorge L. Dunkelman 26 julio, 2009 21:08  

Realmente andoexcaso de tiempo. También me han pedido adaptar la tabla para un tormeo de volleyball. Espero poder ocuparme del tema en un futuro cercano.

Julian 19 agosto, 2009 01:49  

LA VERDAD SOY NUEVO EN ESTE TEMA, ORGANIZO TORNEOS DE VIDEO JUEGOS PERO NO TENGO IDEA DE VISUAL NI DE PROGRAMAR, TENGO UN ARCHIVO BUENO QUE ME PASARON PERO NO SE ACTUALIZAN LAS TABLAS Y NO ES MUY DINAMICO, QUIEN PUEDE AYUDARME CON ESTE TEMA PORFAVOR.

LOS TORNEOS LOS ORGANIZO DEPENDIENDO DE LA CANTIDAD DE PARTICIPANTES POR LO QUE TENGO MUCHAS DUDAS SOBRE COMO HACER EL VISUAL O EL EXCEL YA QUE COMO REALMENTE FUNCIONA SI LO TENGO CLARO ESPERO AYUDA DE USTEDES GRACAS

Jorge L. Dunkelman 19 agosto, 2009 18:58  

Julián

podés descargar la tabla de posiciones que publiqué en esta nota.

Alejandro 03 diciembre, 2009 08:31  

hola amigo como estas? queria saber como puedo hacer para actualizar automaticamente los datos de la tabla de posiciones solo ingresando los resultados de los partidos.. ya que el link http://www.filelodge.com/files/room13/327240/SpFiles/Tabla_Posiciones.xls esta roto..

estoy armando el fixture para el mundial 2010 de sudafrica, desde ya muchas gracias

Jorge L. Dunkelman 03 diciembre, 2009 14:05  

Alejandro,
en las próximas horas estaré corrigiendo el link (el servicio de FileLodge dejó de funcionar sin previo aviso).

Anónimo,  27 diciembre, 2009 03:31  

Hola!

excelente tu blog!

la parte de la tabla auxiliar la entendí, pero no se como usar las funciones indice y coincidir de la manera correcta en la tabla de posiciones para que quede ordenado automaticamente.

me fije donde explicas el funcionamiento de indice y coincidir... pero no logro entenderlo del todo...

como veo... el link para descargar el ejemplo no esta funcionando.

espero puedas arreglarlo, para entenderlo un poco mejor.

o puedas explicar desde aqui lo que tendria que ir en tabla de posiciones.

saludos y desde ya muchas gracias!

Jorge L. Dunkelman 27 diciembre, 2009 06:46  

Hola,
acabo de mover el enlace, Prueba descargaro ahora.
Gracias por los conceptos y buen año nueva.

Daniel,  07 enero, 2010 23:58  

Estimado jorge, soy de uruguay y estamos organizando un campeonato de seven rugby infantil. Son 21 equipos, y el criterio de clasificaciòn es en este orden: 1)Puntos a favor 2)Try a favor (cada try son 5 puntos y si convierte es decir patea a los palos y la pasa por arriba de la H son 2 tantos màs,3)Diferencias de Try,4)Tantos a favor 5)Diferencia de tantos.Me baje la tabla funciona expectacular pero me faltan columnas para poder agregar los otros criterios de clasificaciòn.
Lo importante serìa enteder como se realiza el agregado de otras dos columnas y que formulas se deberìan usar. mi mail es indarte@internet.com.uy saludos y felicitaciones

Jorge L. Dunkelman 08 enero, 2010 07:30  

Daniel,
los cálculo se hacen en la tabla auxiliar. La tabla de posiciones los refleja ordenándolos de acuerdo al puntaje. Podés ver las fórmulas en el archivo.
No me queda claro lo de tries a favor y tantos a favor. Un equipo con 3 tries no convertidos tiene 9 tantos. Otro equipo con 2 tries convertidos tiene 10 tantos. ¿Quien ocupa el primer puesto?

Anónimo,  22 enero, 2010 07:44  

Como podria yo tener al lado de cada equipo una flecha que me indique si subió o bajó de la posicion anterior. O que me indique cuantas posiciones perdió o ganó de su último partido a la fecha.

Anónimo,  24 enero, 2010 14:29  

buenos días jorge.

agradecería enormemente si me puedes enviar un fixturi para hacer la rpogramación de un campeonato con 12 equipos con un sistema de juego de todos contra todos

gracias

leonardo neita
lneitar@yahoo.es

Jorge L. Dunkelman 24 enero, 2010 16:55  

Puedes descargar esta tabla que permite llevar torneos de hasta 40 equipos.

Anónimo,  15 marzo, 2010 16:47  

Buenos días, Jorge
¿Es posible tener una plantilla para realizar sorteos automáticos de todo un campeonato de fútbol o, por lo menos, saber hacer para 14, 15 o 16 equipos?
Muchas gracias
Angel Ordóñez
Chile

Jorge L. Dunkelman 15 marzo, 2010 18:18  

Hola Ángel,
si se puede y es una de las tareas que vengo proponiéndome hacer hace bastante tiempo.
Mientras tanto creo que si haces una búsqueda en la Internet podrás encontrar varios modelos sobre el tema.

lucas mm,  14 abril, 2010 06:11  

hola tengo un problema , con armado de fixture todos contra todos pero como hago para que jueguen todos una fecha de local y otra visitante?????????son 12 equipos un abrazo

Jorge L. Dunkelman 14 abril, 2010 07:21  

¿Podrías explicar ujn poco más cuál es el problema?

Anónimo,  23 abril, 2010 00:15  

Hola, me podrias decir si la funcion de ordenar (mayor a menor o viceversa) puede funcionar automaticamente? Me explico: En una tabla de el grupo del mundial pongo el resultado y de forma automatica se actualizan los puntos, goles, diferencia goles etc. Imaginemos que Camerun le adelanta a Holanda en un dia. Si pongo los resultados y Holanda se queda con 5 puntos y Camerun con 6, hay alguna forma de que camerun sea primera automaticamente (usando la funcion ordenador encontrada en "datos"). gracias.

Jorge L. Dunkelman 23 abril, 2010 07:15  

La única forma sería programando un evento. Pero no hace falta ya que la tabla que muestro en esta nota se ordena automáticamente de acuerdo al puntaje de cada equipo.

Anónimo,  18 mayo, 2010 09:46  

Jorge, primero felicitarte por tuus conocimientos y tu dispocisión a enseñar. Muchisimas gracias. Estoy haciendo el fixture del mundial donde los grupos se definen por puntaje, luego diferencia de goles y en tercer lugar los goles a favor. Use tu tabla pero solo se ordenaba por los dos primeros criterios. Intente agregarle el de Goles a Favor y no me resulto. Como lo debo hacer?
De antemano muchas gracias.
Matias

Jorge L. Dunkelman 19 mayo, 2010 17:13  

Tendrías que moodificar la forma en questamos calculando el puntaje para el ranking. A cada factor (resutados, diferencia de goles y goles a favor) le tenemos que dar un peso distinto. Luego calculamos el puntaje "ponderado" y en base este la psoición del equipo en la tabla.
Por ejemplo, el puntaje para la posición se compone de:
resultados X 10000
diferencia de goles X 100
goles a favor X 1
Un equipo que gano dos partidos y perdió dos, tiene 8 goles a favor y 4 en contra tendría
6X10000 + 4X100 +8 = 60408
Otro equipo con los mismos resultados pero 6 goles a favor y 2 en contra tendría:
6X10000 + 4X100 +6 = 60406
Al usar JERARQUIA para ordenarlos, este último tendría el lugar 2.

Anónimo,  19 mayo, 2010 23:47  

Muchisimas gracias pro la solución y nuevamente felicitaciones por tu página.
Saludos
Matias

Jorge Fernandez,  30 julio, 2010 04:19  

Primero te felicito por esta pagina. Aca viene la consulta, utilice la tabla con todas sus formulas, pero quisiera saber si hay una manera de que me ordene, por puntos y "luego por partido jugado" y despues si por dif. de goles y goles a favor.
Ej
j g e p gf gc df pts
equipo1 2 2 0 0 7 4 3 6
equipo2 3 2 0 1 7 4 3 6
equ.... etc

Gracias

Jorge L. Dunkelman 30 julio, 2010 07:35  

Jorge,
fijate en la técnica que muestro en esta nota.

Jose Pardo Iriondo 20 marzo, 2011 20:52  

bUENA STARDES, DESDE uRUGUAY TE ESCRIBO Y TE AGRADEZCO POR LA INVALORABLE COLABORACION CON ESTAS TABLAS, REALMENTE ME HAS SIMPLIFICADO EL TRABAJO YA QUE TENGO DOS LIGAS DE HANDBALL, Y ESTO ME AYUDA BASTANTE, ESO SI HABRA ALGUNA FORMA DE HACER LO MISMO PERO CON GOLEADORES, PORQUE YO TENDRIA UNAS 120 JUGADORAS POR LIGA Y NO SE COMO COPIAR LOS 40 RENGLONES Y CONVERTIRLOS EN 120, SI ME AYUDAS SERIA INCREIBLE
SALUDOS
JOSE BRUNO

Jorge L. Dunkelman 20 marzo, 2011 23:12  

Hola José

comunicate conmigo por mail privado (fijate en el enlace Ayuda, en la parte superior del mail).

Publicar un comentario

Seguidores

Google+ Followers

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP