JLD Excel en Castellano - Usar Microsoft Excel eficientemente
Blog de sugerencias y ayuda para usar Microsoft Excel en español eficientemente. Esta bitácora contiene explicaciones y ejemplos de funciones y fórmulas Excel, gráficos y automatización de hojas de cálculo con Vba.
http://jldexcelsp.blogspot.com/2006/...eonato-de.html sábado, septiembre 23, 2006 Tabla de Posiciones para campeonato de fútbol con MS Excel
Después de publicar la tabla de posiciones en mi nota sobre como
construir una tabla de posiciones para un campeonato de fútbol con Excel he recibido algunos pedidos de agregar un método de manera que al ingresar los resultados de los partidos la tabla se vaya actualizando automáticamente.
La tabla que muestro en esta nota, y que pueden
descargar aquí, permite construir una tabla de posiciones para un campeonato de fútbol en el que participan un mínimo de seis equipos y un máximo de veinte.
(Actualización 07/11/2006: dado problemas con Excel 2003, he reemplazado el archivo)
Las reglas para determinar las posiciones son:
1 – 3 puntos por partido ganado, 1 por empate y 0 por derrotas.
2 – en caso de igualdad de puntos el primer criterio es diferencia de goles; en caso de persistir la igualdad se define por mayor cantidad de goles a favor.
Primero daré las instrucciones para el uso del modelo y luego, para los que se interesen, una explicación detallada de cómo funciona.
El cuaderno consta de tres hojas: Tabla de posiciones, Resultados y Parámetros.
El primer paso a dar es ir a la hoja Parámetros
y fijar las reglas de puntaje (rango B2:B4), fijar la cantidad de equipos que participarán en el torneo (lista desplegable en la celda B6) e introducir los nombres de los equipos (reemplazando "Equipo 1", "Equipo 2", en la columna D).
El segundo paso es abrir la hoja Resultados
e introducir los resultados de los partidos jugados hasta el momento. Importante: los resultados deben introducirse de acuerdo al patrón "goles equipos local-goles equipo visitante". Por ejemplo "3-2" para una victoria local o "2-3" para una victoria del equipo visitante. Los resultados son introducidos como texto.
La tabla en la hoja Tabla de Posiciones se irá actualizando automáticamente a medida que se anoten los resultados.
Por ejemplo, en la primera fecha del torneo Equipo 1 venció a Equipo 2 por 2 goles a 1; Equipo 3 perdió frente a Equipo 4 por 4 goles a 2. En la hoja resultados anotamos
La tabla de posiciones se actualizará a:
He ocultado las columnas que muestran las estadísticas como local y visitante de cada equipo, por claridad. La versión completa de la tabla es
Hasta aquí todo lo que hay que saber para usar el modelo.
Ahora daré una explicación detallada del modelo.
El cuaderno contiene cinco hojas, las tres ya mencionadas y dos ocultas: Posiciones Auxiliar y Resultados Auxiliar. He definido la propiedad "Visible" de estas dos hojas como xlSheetVeryHidden y no por lo tanto se las puede volver visibles con el menú "Formato--Hoja--Mostrar". Una explicación de cómo volver estas hojas visibles se puede leer en mi nota sobre
como ocultar hojas en Excel.
La hoja Resultados Auxiliar contiene una matriz de 20 filas por 20 columnas y está ligada a la hoja Resultados por medio de la
macrofunción EVALUAR. Este tipo de funciones (funciones macro Excel 4) funcionan en la versiones posteriores a Excel 4 cuando están incluidas en nombres. En nuestro caso hemos definido el nombre "resultado" con la fórmula
=SI(ESBLANCO(Resultados!D3),"",EVALUAR(Resultados! D3))
Lo que hace =EVALUAR(Resultados!D3) es transformar la cadena de texto del resultado en el resultado de la operación (en nuestro caso, 2-1 = 1).
Esto nos permite establecer quien es el ganador, el local o el visitante, o si hubo empate. La regla es sencilla: si el resultado de EVALUAR(Resultados!##) es positivo, el ganador ha sido el local; si EVALUAR da negativo, el ganador ha sido el visitante; si el resultado es 0, se trata de un empate. Esto nos permitirá luego contar cuantos partidos ganados, perdido y empatados ha tenido cada equipo como local y como visitante.
La función de la parte =SI(ESBLANCO(Resultados!D3),""… es evitar resultados #VALOR!, ya que EVALUAR no puede evaluar un celda vacía.
La hoja Posiciones Auxiliar es el "motor" del modelo. Aquí realizamos todos los cálculos que nos permiten establecerla posición de cada equipo en la tabla y sus estadísticas.
La tabla cuenta con un rango para las estadísticas de los equipos como locales, otra para las estadísticas de los equipos como visitantes y la suma de las columnas respectivas de ambos rangos, lo que nos da el total para cada equipo.
La fórmula en la columna A calcula la posición de cada equipo con la
función JERARQUIA, basada en el resultado de la fórmula de la celda correspondiente en la columna AA. Más adelante explicaremos estas fórmulas.
Las celdas en la columna C contienen la fórmula =CONTARA(Resultados!C3:H3), que de acuerdo a la cantidad de resultados anotados en la hoja Resultados calcula la cantidad de partidos jugados como local.
Las celdas en la columna D contienen la fórmula
=CONTAR.SI('Resultados Auxiliar'!B3:G3,">0")
para calcular la cantidad de partidos ganados como local, contamos cuantos números positivos hay en la fila correspondiente del equipo en la hoja Resultados Auxiliar.
La cantidad de partidos empatados en la columna E se obtiene con la misma fórmula, cambiando la condición ">0" por "=0".
Los partidos perdidos se pueden calcular cambiando ahora la condición a "menor que cero" o simplemente como diferencia entre los ganados y los empatados.
Para calcular los goles a favor como local, sumamos los números a la izquierda del guión en los resultados anotados en la fila del equipo en la hoja Resultados. Para esto usamos la
fórmula matricial
={SUMA(SI(ESNUMERO(--IZQUIERDA(Resultados!C3:H3,ENCONTRAR
("-",Resultados!C3:H3)-1)),--IZQUIERDA(Resultados!C3:H3,ENCONTRAR
("-",Resultados!C3:H3)-1)))}
Los goles en contra como local son calculados, de manera similar, con la fórmula matricial
={SUMA(SI(ESNUMERO(--DERECHA(Resultados!C3:H3,(LARGO(Resultados!C3:H3)-
ENCONTRAR("-",Resultados!C3:H3)))),--DERECHA(Resultados!C3:H3,
(LARGO(Resultados!C3:H3)-ENCONTRAR("-",Resultados!C3:H3)))))}
La diferencia de goles en la columna I es calculada por la obvia fórmula =G3-H3.
Finalmente, el puntaje es calculado por la fórmula
=D3*Partido_ganado+E3*Partido_empatado+F3*Partido_ perdido
donde Partido_ganado, Partido_empatado y Partido_perdido son nombres que contienen las celdas de la hoja Parámetros donde definimos las reglas de puntaje.
El rango de las estadísticas como visitante (columnas K:R) contiene fórmulas distintas, ya que nos basamos en las columnas de la matriz en la hoja Resultados y no en las filas.
La cantidad de partidos jugados como visitante es calculada en la columna K por la fórmula:
=CONTARA(INDICE(Resultados!$A$3:$V$22,,FILA()))
Los partidos ganados como visitante los calcula en la columna L la fórmula
=CONTAR.SI(INDICE('Resultados Auxiliar'!$B$3:$U$22,,FILA()-2),"<0") es ="JERARQUIA(AA3,$AA$3:$AA$22)" es ="SI(S3=" s3="0,-2000000" rmula ="BUSCARV($A3,Posiciones_Auxiliar,COLUMNA(),0)" rango ="'Posiciones" class="category">Categorías: Funciones&Formulas_
Technorati Tags:
Excel Si la información de este blog te resultó útil y quieres apoyar su continuidad, puedes hacer una donación usando el botón aquà abajo. Gracias! 