Gráficos de viñetas en Excel.

Recientemente compré una copia del excelente libro Information Dashboard Design de Stephen Few. Primero que nada… ¡guau! Si sus informes incluyen algún tipo de cuadro o gráfico, se debe a usted mismo obtener una copia de este increíble recurso. Stephen desarrolló un gráfico llamado Bullet Graph y, en esta publicación, explicaré los pasos para crear gráficos de viñetas verticales y horizontales en Excel 2013 para Windows.

Objetivo

Antes de comenzar, echamos un vistazo a nuestro objetivo. Nos gustaría crear un gráfico de viñeta único en Excel y nos gustaría poder realizar los mismos pasos para crear gráficos verticales y horizontales. Además, nos gustaría que los pasos fueran fáciles de recordar y bastante rápidos de implementar.

Si no está familiarizado con un gráfico de viñetas, en resumen, proporciona una medida junto con el contexto en un formato compacto que se puede leer de un vistazo. Por ejemplo, consideramos los ingresos del año hasta la fecha. Digamos que los ingresos reales son de 280.000 dólares. Presentar esta cifra por sí sola no es tan significativo como brindarle contexto… debemos informar al usuario si es mala, satisfactoria o buena, y si estamos en el objetivo. Supongamos que el mal desempeño es de $200 000, el desempeño satisfactorio es de $250 000, el buen desempeño es de $300 000 y que el objetivo de ingresos es de $270 000. Toda esta información se presenta en el siguiente gráfico de viñetas.

Ésa es la idea, aunque a un alto nivel. Para obtener más detalles, revise las especificaciones del gráfico de viñetas que están disponibles en el sitio web de Stephen Few en el enlace que se proporciona a continuación.

Dado que vamos a crear esto en Excel, hay, por supuesto, muchas opciones y una búsqueda en Google o YouTube arroja muchas alternativas. La solución que se presenta a continuación es solo una de esas opciones, y si prefiere un enfoque diferente, compártalo publicando un comentario a continuación. ¡Gracias a Jorge Camoes de excelcharts.com por abrirme la mente a este enfoque!

Celdas de entrada

Primero necesitamos un lugar conveniente para almacenar o calcular los valores clave. Los valores clave son los rangos cualitativos malo, satisfactorio y bueno, la medida comparativa objetivo y la medida del desempeño real. Configure un área de entrada del usuario en la hoja de trabajo, como se muestra a continuación.

Datos del gráfico

Ahora es el momento de preparar el rango de la hoja de cálculo que Excel utilizará para crear el gráfico. Le pediremos a Excel que dibuje 5 líneas verticales y ajustaremos el ancho de las líneas según sea necesario. Ignorando el eje horizontal (x) por un momento y pensando solo en el eje vertical (y), cada línea se puede definir mediante los puntos inicial y final, por ejemplo, el rango incorrecto es de 0 a 200, el rango satisfactorio es 200 a 250, y el rango bueno es de 250 a 300. Como puede imaginar, escribir fórmulas de Excel para calcular los puntos inicial y final en función de las celdas de entrada es bastante fácil. La línea de ingresos abarcará de 0 a 280, y la línea del monto objetivo abarcará de 270 a 271.

Ahora que hemos visualizado el eje vertical (y), pensemos en el eje horizontal (x). Como estamos creando líneas, los valores del eje x para todas las líneas serán 1.

Después de escribir algunas fórmulas sencillas, el área de trazado del gráfico se muestra a continuación.

Dado que el área de trazado se crea con fórmulas, los valores se actualizarán automáticamente cuando cambien los valores de la celda de entrada.

Ahora, pasemos a la elaboración del gráfico.

gráfico vertical

Comenzaremos con el gráfico orientado verticalmente que se muestra arriba.

1) Primero , seleccionamos el rango de trazado, excluyendo la fila del encabezado, y luego hacemos clic en el icono de comando Insertar Gráfico Dispersión con líneas suaves. Excel produce el gráfico que se muestra a continuación.

Lo creas o no, con algunos trucos simples de formato, ¡lo tendremos como un gráfico con viñetas en poco tiempo!

2) A continuación , necesitamos identificar correctamente los valores de la serie, por lo que hacemos clic en el icono de comando Herramientas de gráficos Diseño Cambiar fila/columna. El gráfico actualizado se encuentra a continuación.

¡En este punto, Excel está trazando los valores deseados! Todo lo que necesitamos hacer es realizar algunas actualizaciones de formato, es decir, actualizar el ancho y los colores de las líneas, cambiar el título del gráfico y eliminar la leyenda, las líneas de la cuadrícula, el borde del área del gráfico y el eje. horizontal.

3) A continuación , eliminamos las etiquetas, las líneas de cuadrícula y la leyenda del eje X. Estoy usando Excel 2013, por lo que este paso se realiza activando el gráfico, haciendo clic en el ícono Elementos del gráfico (el botón verde más) y luego desmarcando las casillas de verificación Líneas de cuadrícula, Leyenda y Eje horizontal primario. El gráfico actualizado se muestra a continuación.

4) A continuación , para cada línea, debemos especificar el ancho, el color y el tipo de límite. Esto se hace seleccionando cada serie en el gráfico una a la vez y configurando el formato deseado. Por ejemplo, seleccione la serie Actual y luego cambie el Ancho de la línea para que diga 8 puntos, el tipo de Extremo de la línea a Plano y el color de la línea a Negro.

Una vez terminada esa serie, pasa a la siguiente. Dependiendo de los datos, puede resultar difícil hacer clic en cada serie del gráfico, por lo que puede simplemente hacer clic en cualquier serie del gráfico y luego utilizar las teclas de flecha hacia arriba y hacia abajo del teclado para recorrer cada elemento del gráfico. Por ejemplo, haga clic en la serie Real y luego presione la tecla de flecha hacia abajo para seleccionar la serie Objetivo. Configure el formato de la serie Target según sea necesario, por ejemplo, Ancho de línea en 15, Tipo de tapa Plana y color de línea Negro.

Haga clic nuevamente en cualquier serie del gráfico y luego use las teclas de flecha para pasar a la siguiente, como la serie Buena. Formatéelo con algo como un Ancho de 20, Tipo de tapa Plana y color de línea gris claro. Seleccione la siguiente serie, Satisfactorio, y opte por un Ancho de 20, Tipo de tapa Plana y un color de línea gris medio. Seleccione la siguiente serie, Malo, y elija Ancho 20, Tipo de tapa Plana y un color gris claro. El gráfico actualizado se ve bien, como se muestra a continuación.

5) Por último , actualizamos el título del gráfico, eliminamos el borde del gráfico y cambiamos el tamaño del área del gráfico como desee. La versión actualizada se muestra a continuación.

gráfico horizontal

Los mismos pasos utilizados para crear el gráfico de viñetas verticales se pueden utilizar para crear la versión horizontal, pero hay un paso adicional.

Al igual que la versión vertical, seleccionamos el área de trazado excluyendo el encabezado y usamos el ícono de comando Insertar Gráfico Dispersión con líneas suaves. Al igual que arriba, hacemos clic en el ícono Cambiar fila/columna.

Ahora aquí está el paso adicional. Necesitamos cambiar los valores X e Y para cada serie de datos. Para hacer esto, hacemos clic en el ícono Herramientas de gráficos Diseño Seleccionar datos para abrir el cuadro de diálogo Seleccionar fuente de datos como se muestra a continuación.

Luego, seleccionamos cada serie, hacemos clic en el botón Editar y cambiamos las referencias de rango X e Y. Por ejemplo, la serie Bad contiene los valores X e Y que se muestran a continuación.

Simplemente cambiamos las referencias de rango X e Y, ya sea seleccionando el rango correcto en la hoja de trabajo o escribiendo las referencias de fila actualizadas. Tenga en cuenta: presionar F2 mientras se encuentra en los campos de diálogo de valores X o Y puede facilitar la edición de estos valores.

Los valores de diálogos actualizados se muestran a continuación.

Después de cambiar las referencias de rango X e Y para cada serie, su gráfico actualizado debería verse como el siguiente.

A partir de aquí, simplemente use todos los trucos de formato del gráfico vertical, incluida la configuración del ancho de línea, los colores y los tipos de límites. También colocamos el área de trazado y el título del gráfico en las ubicaciones deseadas haciendo clic y arrastrando. El gráfico de viñetas horizontal resultante se muestra a continuación.

Color dinámico basado en el objetivo

Ahora, un pequeño giro divertido. Si tuviéramos varios gráficos de viñetas y quisiéramos que el lector determinara rápidamente si una medida determinada no ha alcanzado el objetivo, entonces podríamos considerar el uso de colores dinámicos. Por ejemplo, un color rojo podría indicar que es necesario tomar medidas y podría llamar la atención sobre la medida dada.

Para lograr esto, en realidad trazaríamos dos líneas de serie para la medida real, en este caso los ingresos. La serie que se utiliza cuando la medida cumple con el objetivo suele ser de color, por ejemplo, negro. La serie utilizada cuando la medida no alcanza el objetivo está coloreada para distinguirla, por ejemplo, roja. Para hacer esto, simplemente agregamos una serie adicional y la coloreamos con el color de resaltado deseado. Para agregar la serie, active el gráfico y use el ícono Herramientas de gráfico Diseño Seleccionar datos y luego Agregar una nueva serie.

Aquí hay una captura de pantalla actualizada que muestra los valores de la serie adicional cuando se cumple el objetivo.

Y así es como se ve cuando no se ha cumplido el objetivo.

Al utilizar las funciones IF() y NA(), actualizamos las fórmulas gráficas para la serie original (objetivo de impacto real) para devolver valores cuando los ingresos son mayores o iguales que el objetivo y para devolver #N/A cuando no lo es . . El error #N/A elimina la serie del gráfico. La fórmula para la nueva serie (Objetivo real incumplido) devuelve valores cuando los ingresos son menores que el objetivo y #N/A en caso contrario. Las fórmulas se proporcionan en el libro de trabajo de muestra.

El gráfico resultante se muestra a continuación.

Esta es una posible implementación de Bullet Graph en Excel. ¡Gracias a Stephen Few y Jorge Camoes!

Y por último… querrás comprar una copia de Information Dashboard Design en Amazon… ¡He incluido un enlace a continuación como referencia!

Recursos adicionales

• Libro de trabajo de muestra: BulletGraphs
• Página web de Stephen Few: Diseño de paneles de información
• Especificaciones de diseño de Bullet Graph: PDF de Bullet Graph
• Sitio web de Stephen Few: ventaja perceptual
• Página del libro de Amazon: Diseño de paneles de información