Lunes, 18 de Junio de 2018

¡Sigue el Mundial Rusia 2018 en Excel!

Llega el Mundial de fútbol y todos queremos seguirle la pista a nuestra selección, partido tras partido. Y todo eso lo podemos hacer con Excel.

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

Este rango contiene todos los partidos del grupo A. En las columnas [D] y [E] se deben escribir los goles convertidos en cada partido. Las fórmulas de la primera columna detectan si el partido ya ha sido jugado.

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

Las fórmulas de la columna [B] nos dicen si el partido fue ganado por el equipo del lado izquierdo de la tabla. Fórmulas similares en la columna [G] nos dicen si el partido fue ganado por el equipo del lado derecho.

Esta función pone un 0 para los partidos aún no jugados; es decir, donde está vacía la celda de los goles del primer equipo. Podríamos hacer lo mismo usando como referencia la celda de los goles del segundo equipo.

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 tabla procesa los datos del resumen de partidos. Las fórmulas de la columna [J] cuentan los partidos jugados por cada equipo. Los datos mostrados corresponden a los resultados hipotéticos de la tabla de partidos de la izquierda.

Los partidos jugados se reconocen porque aparece un número 1 en la columna [A] en la fila correspondiente a cada partido. Por ejemplo, para el primer equipo de la tabla se escribe la siguiente fórmula en [J2]: =SUMAR.SI(C:C;I2;A:A)+SUMAR.SI(F:F;I2;A:A).

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

Las fórmulas de la columna [K] cuentan los partidos ganados por cada equipo.

Los partidos ganados se reconocen porque aparece un número 1 en la columna [B] o en la [G], donde se escribieron las fórmulas que identifican los partidos ganados. Esto se logra con una suma similar a la anterior. Por ejemplo, en [K2] escribimos:

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

Las fórmulas de la columna [O] cuentan los goles convertidos por cada equipo. Fórmulas similares cuentan en la columna [P] los goles recibidos.

Los goles a favor se obtienen de las columnas [D] o [E], del lado del equipo correspondiente. En [O2] escribimos =SUMAR.SI(C:C;I2;D:D)+SUMAR.SI(F:F;I2;E:E).

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.

Las fórmulas de la columna [Q] calculan el “peso”. Los dos equipos con más peso pasan a la segunda ronda.

En [Q2] escribimos =N2*10000+(O2-P2)*100+O2. Esta fórmula combina la cantidad de puntos (columna [N]), la diferencia de goles ([O] menos [P]) y los goles a favor (columna [O]), aplicándoles factores de ponderación que hacen que, por ejemplo, la diferencia de goles solamente cobre importancia peso cuando coincida la cantidad de puntos.

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

La fórmula de la celda [I7] encuentra al segundo equipo con más peso. Este equipo será el segundo clasificado del grupo y pasará también a la segunda ronda.

Determinar el equipo que sale segundo es parecido pero, en vez de buscar el peso máximo, se debe encontrar el valor de peso que le sigue. Esto último se hace con la función K.ESIMO.MAYOR con su segundo argumento igual 2. Entonces escribimos en [I7] la expresión =INDICE(I2:I5;COINCIDIR(K.ESIMO.MAYOR(Q2:Q5;2);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].

Los partidos de octavos de final se arman tomando el ganador o el escolta de cada grupo de las tablas de posiciones de la fase de grupos.

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.

Los partidos de cuartos de final se arman tomando el ganador del partido de octavos correspondiente.

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.

La fórmula de la celda [S25] determina el ganador del partido final. O sea, el campeón del torneo.

Fórmulas similares arman los restantes partidos de esta fase y definen los equipos que pasan a la ronda siguiente, hasta llegar al partido final y definir al campeón. ¡Buena suerte!

 

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

 

Galería de Imágenes

Últimos lanzamientos Ver más