Crear un velocímetro con Excel (Gráficas)

Hacía bastante tiempo que quería documentar y actualizar el procedimiento para realizar una gráfica de velocímetro que en los cuadros de mando siempre es interesante adjuntar para reforzar la información que nos aportan los datos de las tablas.

El objetivo será montar una gráfica parecida a la siguiente imagen, donde en función de unos parámetros la aguja nos indicará un valor que tendrá diferentes interpretaciones en función de los parámetros a estudiar.

El ejemplo o práctica lo voy a basar en un estudio del Teorema de Pareto, de tal manera que según el teoría de pareto, una distribución normal de las ventas haría que el 80% de nuestras ventas debería estar repartida entre un 20% de nuestros clientes (Clientes A). Así la gráfica velocímetro, indicará el % de clientes A que tenemos en la empresa respecto al total que será un 100%.

Así pues, comencemos:

Datos a estudiar: Tenemos la siguiente tabla y como hemos comentado, vamos a seleccionar los datos que reflejan el porcentaje de clientes A

También necesitaremos tener claro el tipo de velocímetro que queremos hacer, para lo cual debemos establecer unos parámetros, que marcarán las segmentaciones y el radio de acción del velocímetro. En nuestro caso, vamos a tomar como dato inicial 0% y dato final 100%, para lo cual con medio arco de circunferencia bastaría, si bien podríamos utilizar tantos segmentos como queramos de una circunferencia. También utilizaremos 4 divisiones, que marcarán 0, 25%, 50%, 75% y 100%.

Finalmente os pongo una imagen de la distribución de las tablas y ubicación en la hoja para que podais seguir las fórmulas. Vereis que hay algunas fórmulas y más datos que explicaré más adelante.

Para la construcción de la aguja que indicará la graduación del velocímetro utilizaremos un gráfico de dispersión, pero previamente debemos calcular cuantos grados mide cada punto porcentual, que será una división entre el valor que le pasamos (debe estar en tanto por ciento) y los parámetros del velocímetro.

Intentaré explicarlo algo más claro utilizando nuestro ejemplo.

• Los clientes A representan un 21% del total de clientes.
• En nuestra gráfica el valor máximo son 100%, que se representa como 1.
• De tal manera que 0,21/1 nos da 0,21 que debemos convertirlo en radianes (por eso multiplicamos por pi) para poder representarlo en una circunferencia.
• Para poder ubicar esos radianes nos basamos en trigonometría y mediante el coseno y el seno podemos obtener la posición del punto (X,Y) que indicarán esos radianes.
• Finalmente añadiremos un punto (0,0) para poder unir el valor de los grados, y así obtener una recta o radio que será nuestra aguja.

Bien, ahora que hemos visto la teoría pasemos a la práctica.

Lo primero que haremos será crear la gráfica de anillos. Marcaremos la columna de segmento de la tabla de parámetros de velocímetro (Rango B9:B14) e insertaremos una gráfica de anillos de tal manera que nos quede algo parecido a esto.

Una vez tenemos el gráfico realizaremos las siguientes tareas:

• Seleccionar “Formato de serie de datos” y en “Opciones de Serie”, girar el gráfico 270º. con la idea que los segmentos pequeños queden en la parte superior.
• Seleccioanr el area más grande (el que ocupara la mitad del círculo) y en formato de punto de datos hemos de hacer que no se vea, para lo cual en “Relleno” poner “Sin relleno”, y si hubiera borde, en “Color del borde” marcar “Sin línea”
• Para añadir los porcentaje ir a “Seleccionar origen de datos”, y en “Etiquetas del eje horizontal” marcar la columna de títulos de la tabla de “Parámetros Velocimetro” sin incluir el título de la columna. Posteriormente en la gráfica marcar la serie de datos y “Añadir las etiquetas de datos”.
• Cómo no son los datos lo que queremos sino los títulos, seleccionaremos los datos y con botón derecho iremos a “Dar formato Etiqueta de datos”. Allí marcaremos el nombre de la categoría y desmarcaremos el check de valor. Finalmente ubicaremos(con el ratón) cada porcentaje en la posición que queramos que ocupe.
• Por último borramos la leyenda y el título del gráfico lo modificamos con el texto “Indicador de Clientes A”

El resultado será algo parecido a esto:

Vamos con la parte más laboriosa, que no es otra que añadir la aguja del indicador y hacer que funcione.

La primera complicación es que hemos de mezclar dos tipos de gráficos que utilizan origenes de datos de diferente tipo, ya que los anillos unicamente necesitan un valor por coordenada, y en cambio los gráficos de dispersión necesitan dos (X,Y).

Comencemos:

• Sobre el gráfico existente, marcar “Seleccionar Origen de Datos” y agregar una nueva serie de datos. En nombre de la serie seleccionaremos la celda A18 (Puntos) y en Valores de la serie, borraremos el contenido y marcaremos las celdas B20 y C20.
• Lo que queremos hacer es añadir una nueva serie de datos, que nos creará un gráfico de arco nuevo, y posteriormente cambiar este gráfico a “Gráfico de dispersión” y seleccionar los datos correctos, ya que necesitamos dos coordenadas, el punto (0,0) y el punto que indicará el valor de la aguja.
• Una vez hemos añadido la nueva serie de datos quedará esta imagen:

• El paso a realizar, es seleccionar sólo el anillo exterior, que es la nueva serie de datos, y modificar el tipo de gráfico a X Y (Dispersión).
• Sobre el gráfico “Seleccionar origen de datos” y allí marcar editar para modificar los valores de este tipo de gráfico.
• En nombre de la serie seleccionar la celda “A18” Puntos, y seleccionar en Valores X de la serie marcar la columna X (sín títulos) y en Valores Y la columna Y

• Los siguientes pasos serán modificar los ejes de coordenadas, para hacer coincidir los dos gráficos.
• Seleccionamos el Eje Vertical (Y), y entramos en “Dar formato a Eje”.
  • En opciones del Eje, en valor mínimo seleccionamos “Fija” y ponemos -1, en valor máximo seleccionamos “Fija” y ponemos 1.
  • En Etiquetas del eje seleccionamos “ninguno”
  • Y en “El eje horizontal cruza” seleccionamos “Valor del Eje” cruza en 0,0.
• Hacemos lo mismo para el Eje Horizontal (Y)
• Marcamos las lineas de división Horizontales y Verticales, y las eliminamos y tendremos ya algo bastante definitivo. Y También quitamos la línea del Eje vertical.

• Finalmente debemos dar formato a la aguja de nuestro indicador, que será el vector que nos unirá los puntos amarillos.
• Seleccionamos uno de los puntos amarillos, y seleccionamos “Formato de serie de Datos”.
  • En color de línea, marcamos “línea sólida” y le cambiamos el color de tal manera que sea diferentes a los utilizados.
  • Ahora podemos eliminar los marcadores (cuadrados amarillos), para lo cual en “opciones de marcador” seleccionamos ninguno.
  • En estilo de línea, ampliamos el ancho a 3 puntos, y modificamos la configuración de de flechas, de forma que los extremos sean unos círculos no muy grandes.

El resultado final debería ser algo similar a esta gráfica:

Adjunto algunos gráficos definitivos, para ver artes finales y diferentes opciones de acabados.

• Los datos y parámetros para realizar este gráfico serían los siguientes

Finalmente, me gustaría agradecer a L.Quezada su post de gráficos velocímetro I y II