18 junio, 2018
Fase de grupos
Comencemos con la fase de grupos. Para cada grupo necesitamos armar un rango con seis filas (una por cada partido) y las siguientes columnas:
* Dos columnas para los nombres de los equipos que se enfrentan.
* Dos columnas intermedias con los goles convertidos para cada equipo.
Por ejemplo, el partido inaugural es Rusia vs. Arabia Saudita. Entonces preparamos los siguientes datos:
* Escribimos Rusia en la celda [C2].
* Escribimos Arabia Saudita en la celda [F2].
* Escribimos los goles convertidos por Rusia en la celda [D2].
* Escribimos los goles convertidos por Arabia Saudita en la celda [E2].
Además, se usarán dos columnas adicionales para saber quién ganó:
* En la celda [B2] escribimos la función =SI(D2>E2;1;0).
* En la celda [G2] escribimos la función =SI(E2>D2;1;0).
Estas fórmulas ponen un número 1 del lado del equipo que ganó y un 0 del lado del que no ganó. Esto significa que, si hubo empate, habrá un 0 de ambos lados (porque en un empate ningún equipo gana). Además, necesitamos una celda adicional para distinguir el empate del resultado de un partido aún no jugado: en la celda [A2] escribimos la función =SI(ESBLANCO(D2);0;1).
Estas fórmulas las tenemos que repetir a lo largo de los seis partidos y hacer siete rangos más, para el resto de los grupos. Conviene probar estas fórmulas poniendo resultados ficticios para los partidos, y verificar que todas funcionan correctamente.
La tabla de posiciones
La tabla de posiciones del grupo se armará en un segundo rango. Por ejemplo, a la derecha de la lista de partidos, y dejando una columna en blanco como separación, copiamos los nombres de los cuatro equipos en [I2:I5]. En las siete columnas siguientes habrá fórmulas para calcular partidos jugados, ganados, empatados y todo lo necesario para calcular las posiciones.
Esta fórmula suma los unos que aparecen en la columna [A] cuando el nombre del primer equipo [I2] aparece en alguno de los dos lados de la lista de partidos: en la columna [C] o en la [F].
=SUMAR.SI(C:C;I2;B:B)+SUMAR.SI(F:F;I2;G:G).
Esta fórmula suma los unos que aparecen en las columnas [B] y [G] cuando el nombre del primer equipo [I2] aparece en alguno de los dos lados de la lista de partidos: en la columna [C] o en la [F].
Los partidos perdidos se reconocen esencialmente con la misma fórmula, pero se busca el número 1 “del otro lado” de la tabla; es decir, cuando este número indica que ganó el otro equipo. En [M2] escribimos:
=SUMAR.SI(C:C;I2;G:G)+SUMAR.SI(F:F;I2;B:B).
Esta fórmula trabaja como la anterior, pero tiene invertidas las referencias a los nombres de los equipos.
Los partidos empatados se calculan restando ganados y perdidos del total de partidos En [L2] escribimos: =J2-(K2+M2).
Los puntos se calculan fácilmente multiplicando la cantidad de partidos ganados por 3 y sumando la cantidad de partidos empatados. En [N2] escribimos =3*K2+L2.
Esta fórmula suma los goles de cada lado de la tabla cuando el nombre del equipo aparece de ese mismo lado.
Los goles en contra se obtienen de manera similar pero buscando el nombre del equipo “del otro lado”. En [P2] escribimos =SUMAR.SI(C:C;I2;E:E)+SUMAR.SI(F:F;I2;D:D).
Esta fórmula es igual a la anterior pero tomando los goles del lado del otro equipo.
El “peso”
Aquí viene algo importante. Para determinar las posiciones hay que tener en cuenta los puntos obtenidos por cada equipo. A igual cantidad de posiciones, se tiene en cuenta la diferencia de goles y, si también coincide la diferencia de goles, va primero el que más goles convirtió. Para tener en cuenta todo esto podemos definir una variable especial, el “peso”, que combina todas estas variables en un único valor.
Todas estas fórmulas deben extenderse hacia abajo, para los cuatro equipos del grupo.
La clasificación
Según las reglas del torneo, pasarán a la segunda fase los dos equipos con más peso. Determinar, por ejemplo, el ganador del grupo, implica hacer todo esto:
* Calcular el peso máximo. Esto se hace con la función MAX.
* Buscar ese máximo en la columna de peso. Esto se hace con la función COINCIDIR.
* Buscar el nombre del equipo que ocupa la misma posición que el peso máximo. Esto se hace con la función INDICE.
Para hacer todo esto en una única fórmula escribimos, en [I6] la expresión =INDICE(I2:I5;COINCIDIR(MAX(Q2:Q5);Q2:Q5;0)).
Antes de seguir adelante, conviene poner resultados de prueba para todos los partidos del grupo y verificar que todo se calcula correctamente.
En este punto tenemos todos los cálculos para el primer grupo: un rango para el resultado de los partidos, otro para la tabla de posiciones y dos celdas auxiliares que determinan quiénes pasan a la segunda ronda. Estas fórmulas deben repetirse siete veces, una para cada uno de los grupos restantes.
Segunda fase
Una vez completados todos los rangos y los resultados de todos los partidos de la fase de grupos, la hoja nos dará todos los clasificados:
* Primero del grupo A en [I6].
* Segundo del grupo A en [I7].
* Primero del grupo B en [I14].
* Segundo del grupo A en [I15].
Y así sucesivamente, repitiéndose los resultados cada ocho filas hasta obtener los clasificados del grupo H en [I62] y en [I53].
En el primer partido de octavos de final se enfrentan el primero del grupo A con el segundo del B. Estos nombres se toman de las celdas [I6] e [I15]. Entonces preparamos un rango para resolver este partido:
* En [S2] escribimos =I6.
* En [V2] escribimos =I15.
* En [T2] escribimos los goles convertidos por el primer equipo.
* En [U2] escribimos los goles convertidos por el segundo equipo.
Tenemos que preparar rangos similares para los restantes siete partidos de los octavos de final según el siguiente cronograma:
* El primero del grupo C se enfrentará con el segundo del grupo D (partido 2).
* El primero del grupo B se enfrentará con el segundo del grupo A (partido 3).
* El primero del grupo D se enfrentará con el segundo del grupo C (partido 4).
* El primero del grupo E se enfrentará con el segundo del grupo F (partido 5).
* El primero del grupo G se enfrentará con el segundo del grupo H (partido 6).
* El primero del grupo F se enfrentará con el segundo del grupo E (partido 7).
* El primero del grupo H se enfrentará con el segundo del grupo G (partido 8).
Los ganadores de estos partidos pasarán a los cuartos de final. Por ejemplo, en el primer partido de cuartos el ganador del partido 1 se enfrentará con el ganador del partido 2. Si suponemos que este partido se escribe en la fila [12], obtenemos el primer equipo escribiendo, en [S12], la fórmula =SI(T2>U2;S2;V2). Es decir, comparamos la cantidad de goles convertidos por el equipo de la izquierda con los del de la derecha y nos quedamos con el que haya hecho más goles. Si bien esta fórmula falla en el caso de empate, en esta fase no puede haber empates porque se resuelven con tiempo suplementario o penales. El equipo rival (ganador del partido 2 de octavos) lo obtenemos escribiendo en [V12] la fórmula =SI(T3>U3;S3;V3). Como antes, en [T12] y [U12] se escriben los goles convertidos por cada equipo para definir quién pasa a semifinales.
Claudio Sánchez
Fanático número uno de Excel. Mes a mes, Claudio se encarga de aclarar todas las dudas sobre esta herramienta de Office.
clasanchez@redusers.com
Esta nota fue publicada en revista USERS 326
Conocé nuestras publicaciones y suscribite para leer otras notas como esta
