Hola! Bueno, como vieron en la entrada de "Número a letras con excel", vamos a poner primero una breve explicación de como utilizar esta planilla, y el link de descarga. Y más abajo, para los aventureros, una breve explicación de fórmulas y funcionamiento para los que quieran hacer modificaciones.
Hay 2 hojas de cálculo que no ocultamos ni bloqueamos. Pero para el usuario, la llamada "AUX" no debe ser tocada. Solo a partir de la fila 37 en adelante, tienen los 30 partidos y hay que llenar los campos amarillos correspondientes al resultado, arriba se irán modificando las tablas "Posiciones" y "Promedios". Están cargados hasta la fecha 17 inclusive.
El link para la descarga:
DESCARGAR Fixture Torneo 2015 Excel
Y si te gustó, podés ayudarnos compartiendo la planilla en facebook, twitter, etc. Podés preguntar lo que quieras, y si ves que alguien pregunta, podés ayudarnos respondiendo.
Si esta entrada gusta, si tiene aceptación y visitas, tenemos previsto subir todos los torneos apertura y clausura que consigamos (del 2000 al 2014 ya conseguimos datos). En si, es casi lo mismo que esto, con 2 hojas de cálculo (una para cada torneo), otra hoja para la tabla general y los promedios, y otra Auxiliar. Y si sigue la aceptación, vamos a explicar y subir Copas Libertadores, Mundiales, Copas América y todo lo que el tiempo y los datos conseguidos nos permitan. Así que estate atento a nuestra sección FUTBOL.
Sigamos con los aventureros. Esto intentará ser un tutorial para el armado de la planilla, para que la puedas adaptar a tu torneo de amigos, country, o lo que quieras (otras categorías, fixture de otros países, etc).
El nivel de excel es intermedio: vamos a dar una explicación genérica, estaría bueno que manejes fórmulas del tipo SI, SUMAR.SI, BUSCARV y similares.
Paciencia a que carguen las imágenes. Hacé click en cualquier imagen para agrandarla!
Empezamos:
1 - Tenemos 2 hojas de cálculo: "Torneo" y "Aux". En "Torneo", vamos a darle formato parecido a lo siguiente:
2 - Comenzamos en auxiliar con lo siguiente:
Como vemos, a B2 y a B3 le damos nombre: "Torneo1" y "Anio" respectivamente. Esto es un "detalle", pero como ya lo teníamos configurado para los torneos "Apertura" y "Clausura" (que pronto subiremos), queda lindo para referenciar los torneos en los lugares en los que tengamos que poner su nombre (como en las cabeceras de las tablas, por ejemplo).
3 - Como acostumbramos en la mayoría de nuestras planillas, todo lo que está en amarillo es lo que editamos "a mano" (lo que no tiene fórmulas). Así que a partir de B5, vamos a poner todas las fechas. La columna B sirve para poner el número de fecha con la fecha seguido de los equipos de local. En la E, ponemos los equipos visitantes. La C va a tener los goles de los equipos de B, y la D, los goles de los equipos de E. Luego:
* En la columna F y por fórmulas, aparecerá si el partido fue ganado por el local o lo que es lo mismo, perdido por el visitante (con un 1).
* En la columna G y por fórmulas, aparecerá si el partido fue ganado por el visitante o lo que es lo mismo, perdido por el local (con un 1).
* En la H, si fue empate.
Es obvio que va a haber un solo 1 y dos 0: el partido no puede haber sido ganado por el local y por el visitante, o ganado por el local y empate, o ganado por el visitante y empate.
4 - Ahora vamos a "llevar" a la Hoja de Cálculos "Torneo" todos los partidos.
En la siguiente imagen, en B37 de "Torneo" ponemos "=Aux!B5", que no es más que traer el número de la fecha con la fecha del primer partido:
Acá otro ejemplo de lo mismo: en este caso, llevamos (referenciamos) el primer equipo local (aldosivi) de la fecha 2 (que está en AUX!B22) que es aldosivi a la hoja "Torneo":
5 - Ahora, vamos a la inversa: llevamos los resultados puestos por el usuario en "Torneo" a la Hoja "Auxiliar". Para ello vamos a usar un "SI" con un "O" dentro (para ver como funcionan en detalle, entrá a la sección "Funciones" del Blog).
En la siguiente imagen:
=SI(O(Torneo!D38="";Torneo!F38="");"";Torneo!D38)
quiere decir que si el usuario no ingresó datos en los 2 resultados del primer partido de la fecha 1 (pueden ver que estos están en la Hoja "Torneo" en las celdas D38 y F38), acá no ponga nada. Sino, es decir, si el usuario cargó el resultado de Argentinos-Atl.Rafaela, entonces que traída lo que dice en D38 de Torneo (los goles de Argentinos):
=SI(O(Torneo!D38="";Torneo!F38="");"";Torneo!F38)
Dejamos 2 ejemplos más de otro partido:
Esto quiere decir que para cada fecha hay que ir trabajando todo esto. Tenemos la parte más "laboriosa" completa: escribir todo el fixture (importante: cada equipo debe escribirse EXACTAMENTE igual en todas las fechas, ya que se usarán como índice), pasarlo a Torneo, y programar por fórmula el hecho de "traer" los resultados cargados.
6 - Vamos a programar las columnas F, G y H.
En F6, hay que poner un 1 si ganó Argentinos (o perdió At.Rafaela, que es lo mismo), o un 0 en caso de que esto no ocurra:
=SI(O(C6="";D6="");"";SI(C6>D6;1;0))
Claro que primero, volvemos a repetir el "O" para saber si los resultados están vacíos (para evitar el 0 en caso de que los campos estén vacíos) y si estos no están vacíos, solo ponemos un SI preguntando si los goles de C6 (Argentinos) son más que los de D6 (Rafaela). Si esto se cumple, que ponga un 1. Sino, un 0.
=SI(O(C6="";D6="");"";SI(C6<D6;1;0))
=SI(O(C6="";D6="");"";SI(C6=D6;1;0))
Si se completaron los resultados y ambos hicieron la misma cantidad de goles, empate, entonces un 1. Sino, un 0:
7 - Vamos a empezar a armar la tabla en la Auxiliar AUX: la columna I y la J la guardamos para después. En K, vamos a poner los 30 equipos. No importa el orden, pero los vamos a referenciar de la manera que vemos a continuación, para que una vez que carguemos los partidos, se completen solos los 30 equipos.
Sabemos que en la primera fecha ya tenemos los 30 equipos. Entonces, en K6 vamos a poner
=B6
En K7
=B7
Así hasta B20. Ya en K21, ponemos
=E6
En K22
=E7
y así hasta tener los 30 equipos.
Dos imágenes para que se entienda un poco esto:
8 - Vamos a empezar con lo entretenido. La columna L la dejamos para los puntos, la M para los Partidos Jugados (ambas las tratamos luego).
Vamos a la N que es la de los partidos ganados:
En N6 dice:
=SUMAR.SI($B$6:$B$484;K6;$F$6:$F$484)+SUMAR.SI($E$6:$E$484;K6;$G$6:$G$484)
* Como vimos, los signos $ son para "trabar" el rango "B6:B84", el "F6:F484", el "E6:E484" y el "G6:G484".
Parece complicado pero es sencillo: En B tenemos todos los equipos locales, en E tenemos los visitantes, en F tenemos los partidos ganados por los locales, y en G los ganados por los visitantes. Por lo tanto, si necesitamos los GANADOS, vamos a sumar en todas las veces que en B aparezca lo que dice K (Argentinos), la columna F (que va a tener con un 1 los ganados de local). Por tanto, la suma esta dará solamente los partidos que Argentinos ganó de local. Nos falta los que ganó de visitante. Para eso el segundo SUMAR.SI de la fórmula: Si en E aparece Argentinos Juniors, sumame los 1 que aparezcan en G.
La imagen con esto:
=SUMAR.SI($B$6:$B$484;K6;$H$6:$H$484)+SUMAR.SI($E$6:$E$484;K6;$H$6:$H$484)
repasando: sumamos los empatados de local (el primer SUMAR.SI) y los empatados de visitante (el segundo SUMAR.SI), solo que acá no tenemos una columna para empatados de local y otra de visitante, sería redundante, empatar es empatar. Por lo tanto, el rango H6:H484 es el mismo para ambas funciones, solo cambia DONDE buscar a Argentinos.
La imagen:
=SUMAR.SI($B$6:$B$484;K6;$G$6:$G$484)+SUMAR.SI($E$6:$E$484;K6;$F$6:$F$484)
Le decimos a Excel que sume todos los Argentinos que encuentre en B (local), que hayan PERDIDO de local (G), más los Argentinos que encuentre en E (visitante) que tengan un 1 en F (Perdidos por el visitante).
Vamos a ver como extraer los goles.
9 - En la columna "Q" va a tener los goles a favor de cada equipo, y en la "R" los goles en contra.
En Q6 dice:
=SUMAR.SI($B$6:$B$484;K6;$C$6:$C$484)+SUMAR.SI($E$6:$E$484;K6;$D$6:$D$484)
Es masomenos lo mismo que venimos viendo: para saber TODOS los goles a favor del equipo que está en K6 (argentinos), buscamos en B todos los "Argentinos" que encontremos, y cada vez que encontremos uno, vamos sumando los goles de C. Pero, al igual que antes, esto solo nos da los goles de Argentinos de local. Entonces, creamos otro SUMAR.SI para sumar los goles de Argentinos de visitante (cada vez que encuentre un "Argentinos" en E, que vaya sumando los goles de D).
La imagen:
=SUMAR.SI($B$6:$B$484;K6;$D$6:$D$484)+SUMAR.SI($E$6:$E$484;K6;$C$6:$C$484)
Lo mismo que arriba, solo que para los goles en contra, cada vez que encuentre "Argentinos", vaya sumando los goles que le convirtieron de local (Argentinos en B y los goles del rival, en D), y los que le convirtieron jugando de visitante (Argentinos en E y los goles del rival estarán en C).
La imagen:
10 - Vamos a los cálculos con lo que acabamos de traer:
En S, ponemos la Diferencia de Goles: Goles a Favor MENOS goles en contra. En S6 iría
=Q6-R6
Los partidos jugados, son los GANADOS más los PERDIDOS más los empatados. En M6:
=SUMA(N6:P6)
=N6*3+O6*1
11 - En I (de I6 a I35) vamos a poner los números del 1 al 30 como vimos en las imágenes anteriores. Esto va a ser de mucha utilidad como vamos a ver.
Vamos a crear un coeficiente de orden. Quizá esto sea lo más difícil de entender, pero una vez que lo tengamos entendido, va a ser de mucha utilidad, no solo para esto.
Vamos a darle el primer puesto al que tenga más puntos. Si hay igualdad, vamos a tener en cuenta la diferencia de goles. Si aún así tenemos igualdad, tenemos en cuenta la cantidad de goles a favor.
En J6 tenemos lo siguiente:
=+L6*10000+Q6-R6+Q6/10+I6/10000
Vamos a darle unos espacios y colores para la explicación:
= +L6*10000 +Q6-R6 +Q6/10 +I6/10000
Lo que está en amarillo, son los puntos. Lo multiplicamos por 10.000 para darle una jerarquía alta al coeficiente: por más que haya un equipo con mucha cantidad de goles, los puntos tiene que ser lo que prevalezca en este coeficiente, lo más importante. Luego, y sin multiplicar, viene lo celeste que nos da la diferencia de goles (los goles a favor menos los goles en contra). En caso de igualdad entre estos dos, ponemos un decimal de los goles a favor dividido 10 (rosa) para desempatar esta situación.
Vamos a ver un ejemplo de esto: El EQUIPO A y el EQUIPO B tienen la misma cantidad de puntos.
EQUIPO A tiene 10 goles a favor y 1 en contra, este número es (10-1+10/10), o sea, 10.
EQUIPO B tiene 9 goles a favor y 0 en contra, este número es (9-0+9/10), o sea 9,9.
Este ejemplo sirve para lo que nos lo propusimos: ambos tienen 9 goles como DG, pero EQUIPO A tendrá más coeficiente que B porque tiene 1 gol a favor más.
Lo último, el rojo, es algo que no va a influir y solo se pone para evitar errores: que ningún equipo sea exactamente igual. En la columna I figuran esos números que pusimos del 1 al 30. Si eso lo dividimos por 10.000, va a dar un decimal muuy chico, que solo sirve para evitar duplicados y errores a la hora de ordenar los coeficientes de mayor a menor.
Ya tenemos los coeficientes de orden. Y es importante saber por qué lo pusimos en J. No es caprichoso. Esto se hace porque, a la hora de ordenar, vamos a usar "BuscarV" y ese índice que utilizaremos para rastrear el equipo con el índice más alto, con el que le sigue, etc. será ese coeficiente que figura en J. Vamos al punto 12: a ordenar todo esto.
12 - Acá vamos a usar K.ESIMO.MAYOR.
Te dejamos el link a la entrada para que lo veas por si no lo tenés claro:
K.ESIMO.MAYOR y K.ESIMO.MENOR
Básicamente, de una lista (un rango), esta fórmula nos va a decir cuál es el mayor (el 1), cuál le sigue (el 2), cuál es el 3, etc etc.
Vamos a usar la columna T para poner todos los coeficientes del punto 11 (los que están el la columna J) ordenados de mayor a menor.
Por eso, en T6, ponemos:
=K.ESIMO.MAYOR($J$6:$J$35;I6)
En lugar de poner el número 1, citamos I6 que ya tiene el 1. Cuándo copiemos y peguemos para abajo, en K7 dirá I7 (2), en K8 I8 (3), etc.
2 imágenes para que vean esto:
13 - Ahora si, ya tenemos todos los coeficientes ordenados de mayor a menor. Vamos a armar la preciada tabla. De U en adelante, vamos a tener lo que muestra la siguiente imagen:
=BUSCARV(T6;$J$6:$K$35;2;FALSO)
Y ahora si, empezamos a usar el nombre del equipo para buscar el resto de los datos de la tabla. Dejamos las imágenes para que lo vean:
14 - Vamos, con la misma temática, a la tabla del promedio. Esto se calcula con la cantidad de puntos logrados en una serie de campeonatos, dividido la cantidad de puntos logrados en ese mismo período. Bien, como no arrancamos de cero, tenemos que tener ese dato. La tabla la creamos a partir de la fila 37, como se ve en la siguiente imagen:
Las dos columnas en amarillo, la L y la M, como dijimos al principio, son para llenar a mano. Este es el dato que necesitamos completar: la cantidad de partidos jugados y la cantidad de puntos de cada equipo, antes de empezar el campeonato. Los equipos los llenamos haciendo referencia a la tabla de arriba. Es decir, en K38 ponemos "=K6", en K39 "=K7", etc.
Vamos a centrarnos en la columna N, más específicamente en N38. Ahí tenemos que poner los puntos al inicio, MAS los puntos de este torneo:
=+L38+BUSCARV(K38;$K$6:$M$35;2;FALSO)
En L38 está los puntos al inicio (lo amarillo, lo que llenamos a mano). A eso, le sumamos el valor que entregue BUSCARV (que es buscar los puntos de Argentinos en la tabla de arriba):
=+M38+BUSCARV(K38;$K$6:$M$35;3;FALSO)
Y ahora, en S, calculamos el promedio, que es Puntos / Partidos Jugados:
=SI(O38=0;0;N38/O38)
Ponemos el SI para evitar el error de dividir por cero, solo a ese efecto:
15 - Al igual que el caso de la tabla, nos reservamos J para sacar un coeficiente. Pero a este coeficiente, solo le vamos a agregar el I/10000 para evitar coeficientes iguales como vimos arriba:
=S38+I38/100000
16 - Y útimo paso! Vamos a la Hoja "Torneo", y empezamos a referenciar en cada tabla, lo que obtuvimos con todos los pasos. Dejamos las imágenes de ejemplo, creemos que ya a esta altura no vas a tener problemas con esto:
Hacé clic para ver de que forma podés colaborar con nosotros:
Hasta la próxima!
No hay comentarios:
Publicar un comentario