JLD Excel en Castellano - Usar Microsoft Excel eficientemente: Gráficos en Excel

Uno de mis lectores me pregunta como se pueden diseñar gráficos en Excel del tipo "velocímetros". Se trata de esos gráficos que se ven como taquígrafos, por ejemplo como este, tomado del sitio de Jon Peltier

La galería de gráficos de Excel no incluye este tipo de diagramas, pero su realización es posible usando algunos trucos.

En esta nota daremos una explicación de las técnicas para lograr este tipo de gráficos en Excel, en sus formas más básicas. Gráficos con agujas, como el de la imagen, requieren técnicas más elaboradas y ciertos conocimientos de trigonometría. Estos gráficos serán tratados en una futura nota.

Las fuentes de esta nota son el sitio ya mencionado de Jon Peltier y partes del libro de John Walkenbach "Excel Charts".

La forma más sencilla es un gráfico que muestra el porcentaje de lo cumplido en relación al total planeado. Supongamos esta sencilla tabla

Nuestro objetivo es generar este gráfico

Este gráfico es, de hecho, uno de tipo "circular" (pie, en inglés), de tres datos (porciones), donde hemos ocultado la tercer porción que representa la mitad de el gráfico.
Para lograr este efecto, usamos una tabla auxiliar donde dividimos el dato "cumplido" por la mitad y calculamos la mitad del restante. La mitad restante es, necesariamente, la mitad que ocultaremos en el gráfico.

Para lograr todos lo efectos los pasos son los siguientes:

1 – definir de los datos

Las fórmulas son:

en la celda B7: =B1/2
en la celda B8: =B2/2-B7
en la celda B9: =B2/2

2 – girar el gráfico de acuerdo a la necesidad (en este caso 270 grados)

3 - seleccionar el punto 3 de los datos y marcar el valor del área y del borde como "ninguno"

4 – El rótulo que muestra el porcentaje de lo cumplido es un cuadro de texto que hemos ligado a la celda B4

para hacer esto, seleccionamos el cuadro con un clic y en la barra de fórmulas cramos el vínculo a la celda.

Nota: este gráfico presupone que los valores sean siempre positivos.

Un gráfico más elaborado se puede lograr usando la variante "anillos". En este ejemplo, clasificamos los resultamos de un examen, de acuerdo a la nota obtenida, en "reprobado", "regular", "bueno" y "excelente". El resultado de cada examen lo exhibimos en un gráfico como este:

Para lograrlo usamos una técnica similar a la anterior, pero con el gráficos "anillos".
El primer paso consiste en elaborar una tabla auxiliar que nos permita representar los datos:

Las fórmulas son las siguientes

En la columna "Auxiliar" usamos la misma técnica que en el gráfico anterior, dividiendo los valores por la mitad.
En la columna "Valor Límite" establecemos los límites de las calificaciones, también en este caso dividido por la mitad. En nuestro ejemplo, el límite superior de "Reprobado" es 40 puntos, por lo tanto en la celda C4 ponemos un valor de 20, "regular" es hasta 70 puntos, es decir 30 puntos más a partir de "reprobado". Por lo tanto anotamos 15 (la mitad de 30) en la celda C5. Y así sucesivamente.
Con estos datos, más los rótulos de la columna B podemos construir nuestro gráfico.

Sin seleccionar ninguna celda activamos el asistente para gráficos y definimos las series de esta manera:

Serie 1: =Anillos!$A$4:$A$6
Serie 2: =Anillos!$C$4:$C$8, para esta serie definimos también los rótulos de las categorías, =Anillos!$B$4:$B$7

Abrimos el diálogo de Formato de Series de Datos y giramos el gráfico 270 grados

Seleccionamos el punto 5 de la serie 2 (valor límite) y en el diálogo de Formato de punto de datos ponemos los valores de "borde" y "área" en "ninguno"

Hacemos lo mismo con el tercer punto de la serie 1 (Auxiliar)

Cambiamos el área de los puntos de la serie 1 al color blanco y a los puntos de las serie 2 les damos colores apropiados.

Todo lo que nos queda por hacer es agregar dos cuadros de textos, uno ligado a la celda A1, que será el título del gráfico y el otro ligado a la celda B1, que mostrará el puntaje obtenido en el examen.
También la damos un fondo al área del gráfico y voila!