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