Gráficos en Excel – Crear gráficos tipo velocímetro - 2da. 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.