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)

(Actualización 09/11/2007: la tabla ampliada para 40 equipos se puede descargar aquí)

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

Gráficos tipo velocímetro en Excel - tercera nota

En la nota anterior mostrábamos un ejemplo de gráfico tipo velocímetro para indicar el porcentaje de realización de un proyecto. La escala del velocímetro iba de 0% a 100% y la posición de la aguja estaba dada por el porcentaje cumplido.
Un uso más frecuente para este tipo de gráficos es la representación de índices de performance, KPI (Key Performance Indicators), que tanto se han puesto de moda desde principios de los '90.

En esta nota desarrollaremos un ejemplo de gráfico tipo velocímetro cuya escala empieza y termina en números arbitrarios y utiliza dos agujas.
La técnica es similar a la que mostramos en la nota anterior, y está basada en los métodos mostrados por Jon Peltier en su nota sobre el tema.

Supongamos que en nuestra hipotética empresa publicamos cada tanto un índice que mide la relación entra las ventas y los días de trabajo en un período. Este índice lo comparamos con el índice correspondiente del mismo período del año anterior. Para el caso supongamos esta tabla de datos:

También suponemos que este índice puede variar entre 10 y 90.

El primer paso es construir el fondo del velocímetro (la escala) para lo cual creamos una tabla que contiene los rótulos y los valores. Es importante distinguir entre los rótulos y el valor. El valor determina el tamaño del sector. El primer sector va de 10 a 30, por lo tanto en la celda al lado del rótulo 10 ponemos el valor 20. El segundo sector corre de 30 a 70, por lo tanto el valor correspondiente al rótulo 30 será 40 y así sucesivamente. El valor correspondiente al último rótulo debe ser, necesariamente, la suma de todos los valores anteriores.




Como en el caso anterior, dejamos la columna de rótulos sin encabezamiento para darle a entender a Excel que se trata de rótulos. Seleccionamos el rango y en el asistente de gráficos elegimos el de tipo anillos



Como en el ejemplo de la nota anterior, eliminamos el título y la leyenda, y giramos el gráfico 270 grados



Seleccionamos el sector inferior y cambiamos las definiciones de borde y área en la pestaña "Trama" a "ninguno". Con esto hacemos "desaparecer el sector inferior. También podemos cambiar los colores de los sectores visibles de acuerdo a nuestras preferencias.

Luego abrimos el menú de formato de series de datos y en la pestaña "Rotulos" marcamos la opción "Nombre de la categoría".




Ahora arrastramos los rótulos a su posición, con lo que terminamos la primer parte de nuestra tarea



El próximo paso es construir las agujas, que serán representadas por un gráfico de tipo XY con líneas unidas,

Para esto volvemos a nuestra tabla de índices los que tendremos que convertir a radianes, tal como hicimos en el ejemplo anterior. En este caso construimos esta tabla auxiliar




La fórmula en la celda B7 (que está combinada con C7) convierte el valor del índice del año 2005 a radianes:

=(B4-$B$13)/($B$16-$B$13)*PI()

donde B13 es el primer rótulo de los anillos (que en este caso tratamos como valor) y B16 es el último rótulo de los anillos. Es decir el mínimo y el máximo de nuestra escala.

A partir de este valor, usamos las funciones =SENO() y =COS() para calcular los puntos de las agujas.
El primer valor de X y de Y será siempre 0. El segundo valor de X es calculado por la fórmula

=-COS(B7) (prestar atención al signo "-" delante de COS).

El segundo punto de Y es calculado por la fórmula

=SENO(B7)

Procedemos de la misma manera para el índice del año 2006.

Ahora procedemos a combinar los nuevos valores en el gráfico de anillos.

Primero seleccionamos los valores para el año 2005, el rango B8:C9, y lo copiamos (Ctrl+C).
Seleccionamos el gráfico de anillos y en el menú "Edicion" pulsamos "Pegado Especial". En el diálogo que se abre

seleccionamos "agregar celdas como nueva serie" y "valores (Y) en filas". Marcamos "categorías en la primera columna" y pulsamos Aceptar



Ahora seleccionamos el nuevo anillo (violeta) y en el menú Tipo de Gráfico elegimos XY (Dispersión) unido con líneas.
Hacemos lo mismo con los datos del año 2006, el rango D8:E9

El paso siguiente es arreglar los ejes, para que coincidan con los del gráfico de anillos.
Seleccionamos el eje de las X y en el diálogo de formato del eje, en la pestaña Escala ponemos el mínimo a -1, el máximo a 1 y "eje de valores (Y) cruza en:" a 0.



Hacemos lo mismo con el eje de las Y




Borramos el eje de las Y y los marcadores en el eje de las X (en el diálogo de formato de ejes, en la pestaña Tramas ponemos los valores de las marcas a "ninguno").


Todo lo que nos queda ahora por hacer es dar los formatos deseados.

Por ejemplo, para agregar los valores en el extremo de las agujas usamos la técnica que mostramos en la nota anterior:

1 - Seleccionamos la "aguja" y abrimos el menú de formato de series de datos. En la pestaña "rótulo de datos" seleccionamos "valor X".

2 - El rótulo muestra el valor en radianes, cuando lo que queremos es el valor del índice. Para esto, con el rótulo seleccionado, hacemos clic en la barra de las fórmulas, escribimos "=" (sin las comillas) y hacemos clic a la celda B4, si se trata de la aguja del año 2005, o a la celda C4 si se trata del año 2006. También podemos agregar títulos, leyenda y cuadros de texto, dar fondo al gráfico y demás efectos que creamos convenientes

En la nota en su sitio, Jon Peltier propone un método más sofisticado para agregar marcadores a la escala del velocímetro (al gráfico de anillos). Les recomiendo leer la nota.



Categorías: Graficos_

Technorati Tags: Excel, Graficos en Excel

Gráficos tipo velocímetro en Excel – segunda nota

En la entrada anterior explicamos las técnicas a emplear para crear un gráfico en Excel que simule un velocímetro. Si queremos crear un gráfico aún más parecido a un velocímetro, tendremos que diseñarlo con aguja (manecilla).
En esta nota mostraremos las técnicas y trucos necesarios para lograr este efecto.

Volvamos al ejemplo de la evaluación de avance de un proyecto

Nuestro objetivo es representar estos datos con el siguiente gráfico



Empezaremos por el final. Para crear la escala en el fondo, usamos la técnica que ya vimos en la nota anterior: un gráfico de tipo anillos al que dividimos en dos mitades. En una de las mitades representamos los intervalos de 10% en orden ascendiente de izquierda a derecha. La otra mitad la hacemos "desaparecer".

Los pasos son los siguientes:

1 – Armamos una tabla con una columna para los rótulos y una para los valores de los anillos, cuidándonos de dejar la celda del encabezamiento de los rótulos (A19) en blanco. De esta manera Excel utilizará los datos de la comuna A como rótulos y no como valores de un segundo anillo.




2 – Seleccionamos el rango (A19:B30) con los valores y los rótulos y en el asistente de gráficos elegimos el tipo anillos



Nos aseguramos que en el asistente de gráficos la opción sea "Series en columnas"



3 – En la pestaña "Títulos" borramos el título; en la pestaña "Leyenda" eliminamos la leyenda y en la pestaña "Rótulos de Datos" señalamos la opción "Nombre de la categoría"



Apretamos aceptar y este es el resultado



4 - Seleccionamos la serie de datos con un clic sobre alguna de las porciones del anillo y abrimos el menú de formato de serie de datos



En la pestaña "Tramas" elegimos un color conveniente (en nuestro ejemplo el amarillo claro)



En la pestaña "Opciones" llevamos el valor de la opción "ángulo del primer sector" a 270 grados, el tamaño del agujero del anillo a 20% y quitamos la marca de la opción "Variar colores de los sectores" de manera que todas las porciones tengan el mismo color.



5 – Para hacer desaparecer el anillo inferior, seleccionamos el sector (con un doble clic sobre la porción) y abrimos el menú de formato de puntos de datos.



En el diálogo señalamos para "Borde" y "Área" las opciones "ninguno"



6 – Arrastramos los rótulos a la posición deseada, lo que haremos manualmente. Elegimos los rótulos con un clic y con un segundo vamos eligiendo cada uno de los rótulos y llevándolo a la posición deseada



Presten atención que el rótulo de la porción inferior (100%) aparece a pesar de que no vemos el sector.

Con esto hemos terminado de preparar la escala de nuestro velocímetro. Ahora crearemos la aguja lo que hacemos usando un gráfico de tipo XY (Dispersión) con puntos de datos conectados por líneas.




Uno de los puntos de la aguja será siempre el cero; el otro punto deberá "moverse" a lo largo de una semicircunferencia que coincida con el borde exterior del anillo.
Esto significa que debemos transformar el dato que queremos representar (el porcentaje cumplido del proyecto) a radianes y establecer una fórmula que nos de los valores del punto. Hacemos esto usando las funciones seno [=SENO()] y coseno [COS()] de Excel.

A nuestra tabla de datos del proyecto la agregamos una tabla para la transformación de datos




Los valores de las celdas A7 y B7 serán siempre cero.

El valor de la celda A8 está determinado por la fórmula

=-COS(B4*PI())/B10
Prestar atención al signo "-" al principio de la fórmula.

El valor de la celda B8 está determinado por la fórmula

=SENO(B4*PI())/B10

La función de la celda B10 es darnos control sobre el largo de la aguja. Podemos cambiar el valor en la celda B10 hasta llegar al largo deseado.

Ahora, con el rango A7:B8 seleccionado, elegimos el gráfico de tipo XY con líneas sin marcadores y nos aseguramos que la opción "series en" sea "columnas"

Quitamos las líneas de división y apretamos "Aceptar"



Como ven, tenemos todavía mucho trabajo por delante.

La primer tarea es adaptar los ejes a nuestras necesidades. Elegimos el eje de las X y abrimos el diálogo de formato del eje.

En la pestaña "Escala" fijamos el valor mínimo a -1 y el máximo a 1

Ahora seleccionamos el eje de las Y y apretamos F4 para copiar el formato del eje de las X. Este es el resultado




Ahora borramos el eje de la Y y los rótulos del eje de las X. Este es ahora nuestro gráfico




La línea que vemos une dos puntos: uno determinado por los valores (0,0) y el otro determinado por los valores (0.556, 0.962).

Ahora tenemos que combinar los dos gráficos. Excel permite presentar distintas series de datos en un mismo gráfico con distinto tipos de gráficos. Por lo tanto lo que haremos es copiar la serie de datos que define la aguja al gráfico de anillos y luego cambiar ciertas definiciones para lograr el efecto deseado.

Procedemos de esta manera:

1 – Seleccionamos el rango de los valores XY (A7:B8) y los copiamos (Ctrl+C). Luego seleccionamos el gráfico de anillos y en el menú de Excel apretamos "Edición--Pegado Especial". En el diálogo que se abre señalamos "agregar celdas como nueva serie" , "valores Y en columnas" y "Categorías (rótulos de X) en la primera columna"




El resultado es un tanto desalentador, pero enseguida nos ocuparemos de esto.



2 – Seleccionamos el anillo violeta (la nueva serie que acabamos de agregar) y abrimos el diálogo de tipo de gráfico.



Elegimos el tipo XY con líneas sin marcadores y apretamos "Aceptar"



3 – Ahora arreglaremos los ejes para que la aguja coincida con el gráfico de los anillos. Seleccionamos el eje de las Y y, como mostramos antes, definimos el valor mínimo a -1, el máximo a 1, y en "Eje de valores X cruza en" ponemos 0.



Seleccionamos el eje de las X y apretamos F4 para copiar el formato. Borramos el eje de la Y para lo cual en el menú Gráfico--Opciones de gráfico, en la pestaña Eje quitamos la marca del eje de las Y. Del eje de las X quitamos los rótulos



Como ven, casi hemos terminado.

En el extremo de la aguja aparece el rótulo con el valor del punto (0.556), cuando lo que queremos es que aparezca el valor original 66.7%.

El "truco" es el siguiente: seleccionamos el rótulo del punto (dos clic separados por un pequeño intervalo). Con el rótulo seleccionado creamos una referencia a la celda B4 en la barra de fórmulas (clic a la barra de fórmulas, escribimos "=" y hacemos un clic a la celda B4).

Los últimos toques son formar el fondo del gráfico, el rótulo del valor y la aguja, todo de acuerdo a los gustos personales de cada uno.



En una próxima nota veremos una interesante variante con dos series de datos (dos agujas) propuesta por Jon Peltier en su sitio.

Categorías: Graficos_

Technorati Tags: Excel, Graficos en Excel