viernes, 10 de enero de 2014

MUNDIAL DE FUTBOL EXCEL 2007 Parte (II) Formulas


BRASIL 2014 

En esta segunda parte vamos a ver qué formulas son necesarias para que las hojas funcionen y muestren en la hoja 1 las estadísticas de la tablas clasificatorias y en la hoja 2 para las fases eliminatorias.

En la hoja 1 hay que copiar todas las tablas y copiarlas unas columnas mas a la derecha, en estas tablas que vamos a crear será donde insertaremos las formulas. Estas tablas son las que realmente recogen los datos que se van insertando y si accidentalmente se borraría algún dato de las tablas clasificatorias originales estas volverán a pegar las formulas de nuevo.



Si no se quiere realizar de esta manera también pueden insertar las formulas directamente en las tablas originales aunque no lo recomiendo, las formulas son bastante complejas y es recomendable tenerlas a salvo de cualquier usuario o incluso de nosotros mismos, es fácil liarla y difícil encontrar el fallo si se llega a producir.

Esta es una de las partes más trabajadas de la hoja, si esto no funciona todo lo demás no tiene sentido, así que vamos a ir paso a paso con cada formula y cada casillero.

FORMULAS

Voy a explicar brevemente lo que hace cada formula y como hay que repetirla en las demás celdas, algunas formulas podrán copiarse con el controlador de relleno pero otras hay que copiarlas y modificarlas manualmente. Solo mostraré el Grupo A, los demás grupos se crean exactamente como el primero. 
Vamos a ello:

Partidos Jugados:


{=SUMA(SI($D$5:$D$66=$H7;1;0)*SI(ESNUMERO($C$5:$C$66);1;0))+SUMA(SI($E$5:$E$66=$H7;1;0)*SI(ESNUMERO($F$5:$F$66);1;0))}

Esta fórmula busca en la tabla del Grupo A, si coincide con el equipo de la tabla clasificatoria y hay un número en la casilla de goles, suma cada celda que tenga número.
Esta fórmula es matricial, así que para que quede cerrada con las llaves {} se tendrá que pulsar Crtl+Shift+Enter. Para copiarla en las demás columnas hay que repetir la operación manualmente y pulsar Crtl+Shift+Enter en cada celda para que quede fijada la matriz.

Partidos Ganados:


{=SUMA(SI($D$5:$D$66=$H7;1;0)*SI($C$5:$C$66>$F$5:$F$66;1;0))+SUMA(SI($E$5:$E$66=$H7;1;0)*SI($F$5:$F$66>$C$5:$C$66;1;0))}

Esta fórmula busca en la tabla del Grupo A, si coincide el equipo y el resultado es mayor que el de su oponente suma un punto.
Esta fórmula es matricial, así que para que quede cerrada con las llaves {} se tendrá que pulsar Crtl+Shift+Enter. Para copiarla en las demás columnas hay que repetir la operación manualmente y pulsar Crtl+Shift+Enter en cada celda para que quede fijada la matriz.

Partidos empatados:


=AA7-AB7-AD7

Esta fórmula resta a los partidos jugados los partidos ganados y los partidos perdidos.
Puede usarse el controlador de relleno.

Partidos Perdidos:


{=SUMA(SI($D$5:$D$66=$H7;1;0)*SI($C$5:$C$66<$F$5:$F$66;1;0))+SUMA(SI($E$5:$E$66=$H7;1;0)*SI($F$5:$F$66<$C$5:$C$66;1;0))}

Esta fórmula busca en la tabla del Grupo A, si coincide el equipo y el resultado es menor que el de su oponente suma un punto.
Esta fórmula es matricial, así que para que quede cerrada con las llaves {} se tendrá que pulsar Crtl+Shift+Enter. Para copiarla en las demás columnas hay que repetir la operación manualmente y pulsar Crtl+Shift+Enter en cada celda para que quede fijada la matriz.

Goles a favor:


=SUMAR.SI($D$5:$D$66;$H7;$C$5:$C$66)+SUMAR.SI($E$5:$E$66;$H7;$F$5:$F$66)

Esta fórmula busca en la tabla del Grupo A, si coincide el equipo suma los goles.
Puede usarse el controlador de relleno.

Goles en contra:


=SUMAR.SI($D$5:$D$66;$H7;$F$5:$F$66)+SUMAR.SI($E$5:$E$66;$H7;$C$5:$C$66)

Esta fórmula busca en la tabla del Grupo A, si coincide el equipo suma los goles de su oponente.
Puede usarse el controlador de relleno.

Diferencia de goles:


=AE7-AF7

Resta a los goles a favor los goles en contra.
Puede usarse el controlador de relleno.

Puntos:


=(AB7*3)+AC7

Multiplica por tres los partidos ganados y suma el número de empatados.
Puede usarse el controlador de relleno.

Una vez preparada la tabla solo queda repetirlo con las siete tablas restantes. Aquí es donde cada uno tiene ingeniárselas para que la operación no sea demasiado tediosa y por supuesto ir cambiando cada referencia por grupo. Aparte del problema añadido de que muchas de las formulas son matriciales y hay que fijarlas una por una.

La hoja 1 queda finalizada y por supuesto operativa, solamente con esto ya debería funcionar si se insertaran goles en el casillero de equipos.

La hoja 2 es mucho más sencilla. Solamente hay que hacer referencia a los dos primeros clasificados de cada grupo y a partir de ahí referenciar a los equipos que van ganando cada eliminatoria.

Las fórmulas serán para que aparezca el equipo en la celda correspondiente, ya que los goles igual que en la hoja 1, hay que insertarlos a mano.

Las formulas quedarían de esta manera:

Octavos: ='1ª Fase'!H7

Cuartos: =SI(Y(D3="";D5="");"";SI(D3<D5;C5;C3))

Semifinales: =SI(Y(G4="";G8="");"";SI(G4<G8;F8;F4))

3º y 4º puesto: =SI(Y(I6="";I14="");"";SI(I6<I14;H6;H14))

Final: =SI(Y(I6="";I14="");"";SI(I6<I14;H14;H6))

Campeón: =SI(Y(K10="";M10="");"";SI(K10<M10;N10;J10))

Estas fórmulas son condicionales, pueden resumirse en: si no hay nada en las celdas anteriores, no escribas nada, pero si hay algo escribe el nombre del equipo con el resultado mayor.

Con estas fórmulas queda finalizada la segunda parte del tutorial.

Parte 1 Diseño
Parte 3 Macros