viernes, junio 02, 2006

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

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

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

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

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

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

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

    ResponderBorrar
  6. sip es para niños d escasos recursos.

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

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

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

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

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

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

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

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

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


    nota.- te mande al correo el archivo

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    ResponderBorrar
  35. Julián

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    ResponderBorrar
  54. Jorge Fernandez30 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

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

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

    ResponderBorrar
  57. Hola José

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

    ResponderBorrar