Cómo crear leyendas de gráficos dinámicos en Excel
Las leyendas de los gráficos nos muestran lo que representa cada Serie en un gráfico. Estas leyendas suelen colocarse en cuadros cerca del gráfico. Sin embargo, las investigaciones muestran que separar las etiquetas del artículo de esta manera requiere más capacidad intelectual de los lectores y resta valor al mensaje clave. Entonces, en esta publicación, veremos cómo crear leyendas de gráficos dinámicos en Excel, que ayudan a los lectores a concentrarse en el mensaje clave.
Tabla de contenido
- ¿Por qué es importante la posición de la leyenda?
- Archivo de ejemplo
- Usando la función NA() en gráficos
- Crear el gráfico
- Cree una leyenda de gráfico dinámico para el pronóstico
- Crear una etiqueta dinámica para el real
- Completa el formato
- Pruébalo
- Conclusión
Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.
Nombre del archivo: 0128 Etiquetas de gráficos dinámicos.zip
Tener acceso
Basándonos en investigaciones de la década de 1920, conocidas como los principios de la Gestalt, sabemos que el cerebro humano encontrará patrones y significados de forma natural en las imágenes.
Uno de los principios clave de la Gestalt se relaciona con la proximidad, según la cual los elementos colocados cerca uno del otro se consideran relacionados. Por lo tanto, podemos ayudar a los lectores de nuestros gráficos colocando etiquetas cerca de los elementos a los que hacen referencia. Mire las dos imágenes a continuación; la primera imagen muestra el cuadro de leyenda estándar de Excel, mientras que la segunda imagen muestra una posición de etiqueta dinámica.
En el segundo gráfico, la etiqueta se coloca cerca de cada línea, por lo que el cerebro acepta la etiqueta de forma natural y no necesita buscar en otra parte.
Otro principio de la Gestalt se relaciona con la similitud. Afirma que es probable que los elementos que parecen similares se consideren relacionados. Por lo tanto, utilizar el mismo color para la etiqueta que para la línea también ayuda a los lectores a comprender fácilmente el gráfico.
Sabemos que a los gráficos a menudo se les agregan datos. Por lo tanto, debemos asegurarnos de que las etiquetas sean dinámicas y que la proximidad de la etiqueta a la línea se mantenga a medida que se agregan datos. No queremos mover la etiqueta manualmente, así que veamos cómo usar la funcionalidad de Excel para lograrlo automáticamente.
Archivo de ejemplo
El archivo de ejemplo contiene los datos que utilizamos a lo largo de esta publicación. Los datos de origen están contenidos en una tabla de Excel (que es la mejor manera de almacenar datos en Excel).
Dado que las tablas tienen referencias estructuradas, debemos ser un poco más inteligentes con nuestras fórmulas.
Usando la función NA() en gráficos
La función NA() no es una que usemos frecuentemente en Excel. Genera el error #N/A. Puede que esto no parezca particularmente útil; sin embargo, NA() tiene un propósito específico cuando se usa dentro de un gráfico.
En Excel, una celda vacía no se representa en un gráfico porque no existe ningún valor. Sin embargo, si usamos una fórmula en una celda, incluso si el resultado de esa fórmula parece ser una celda vacía, no lo es. Excel calcula la celda y representa el valor vacío como cero.
En el ejemplo anterior, los datos de origen del 31 de julio en la columna Etiqueta real parecen estar vacíos, pero la línea se muestra en el gráfico como cero. Esto se debe a que hay una fórmula en la columna Etiqueta real :
=IF([@Actual]"",[@Actual],"")Si no hay ningún valor en la columna Real , la fórmula devuelve una cadena de texto vacía, pero no es lo mismo que una celda vacía. Como resultado, se representa la línea gris en el gráfico anterior.
Para forzar que el gráfico reconozca el valor como vacío, podemos usar la función NA().
En el cuadro anterior, la fórmula en la columna Etiqueta real es:
=IF([@Actual]"",[@Actual],NA())Esta fórmula devuelve el error #N/A, que no se representa en el gráfico (es decir, la línea gris no es visible de julio a diciembre).
Crear el gráfico
Ya son suficientes principios; Comencemos a trabajar en nuestro ejemplo. Primero, necesitamos crear el gráfico.
- Seleccione todas las celdas en la columna Fecha (incluido el encabezado), mantenga presionada la tecla Ctrl , luego seleccione las celdas en las columnas De real a etiqueta de pronóstico (incluidos los encabezados)
- Cree un gráfico de líneas 2D estándar haciendo clic en Insertar gráfico de líneas (menú desplegable) Gráfico de líneas
Eliminemos los elementos que no necesitamos. Seleccione cada elemento y presione Eliminar :
- Titulo del gráfico
- Líneas de cuadrícula principales
- Leyenda
Dependiendo de su situación, puede decidir eliminar otros elementos también.
El gráfico inicial ahora se ve así.
Cree una leyenda de gráfico dinámico para el pronóstico
A continuación, creemos una etiqueta dinámica para la línea de Pronóstico.
El objetivo es crear una celda con #N/A para cada fila de la columna, excepto la última fila.
Estamos utilizando una tabla de Excel, por lo que debemos asegurarnos de que nuestras fórmulas reaccionen a cualquier dato nuevo.
En la primera celda de la columna Pronóstico, ingrese la siguiente fórmula:
=IF([@Date]=MAX([Date]),[@Forecast],NA())Esta fórmula establece:
Si la fecha en la misma fila que la fórmula es igual a la última fecha en la columna Pronóstico, devuelva el valor de la columna Pronóstico; de lo contrario, devuelva #N/A.
Esto garantiza que sólo la última fecha incluya un valor; todas las demás filas devuelven #N/A. Si agregamos nuevos datos a la tabla, la fórmula se copia y mueve automáticamente el total en consecuencia.
Para agregar la etiqueta de datos:
- Seleccione el gráfico y haga clic en la cinta Formato de gráfico en el menú.
- En el menú desplegable, en la parte superior izquierda de la cinta, seleccione la serie Etiqueta de pronóstico
- Haga clic en Diseño de gráfico Agregar etiquetas de datos de elementos de gráfico hacia la derecha
- Haga clic derecho en la etiqueta de datos en el gráfico, seleccione Formatear etiquetas de datos... en el menú
- En el panel Formatear etiquetas de datos , marque Nombre de serie y desmarque Valor .
Esto muestra el nombre de la serie, que actualmente es Etiqueta de pronóstico .
- Haga clic derecho en el gráfico, haga clic en Seleccionar datos
- Se abre el cuadro de diálogo Seleccionar fuente de datos , seleccione Etiqueta de pronóstico en el cuadro Entradas de leyenda (serie) y luego haga clic en Editar .
- En el cuadro de diálogo Editar serie , apunte el nombre de la serie a la celda $C$1 (que es la etiqueta que queremos aplicar).
- Haga clic en Aceptar para cerrar el cuadro Editar serie y luego en Aceptar nuevamente para cerrar el cuadro Seleccionar fuente de datos .
El gráfico ahora tiene la etiqueta correcta para la línea de pronóstico.
Crear una etiqueta dinámica para el real
Ahora, creemos una etiqueta dinámica para la línea Real.
Queremos crear una celda con el error #N/A para cada fila de la columna, excepto la fila con el último valor ingresado.
En la primera celda de la columna Real , ingrese la siguiente fórmula:
=IF([@Date]=MAXIFS([Date],[Actual],""""),[@Actual],NA())MAXIFS([Fecha],[Actual]”,”””) calcula la última fecha donde Actual no está en blanco.
El resto de la fórmula dice:
Si la fecha en la misma fila que la fórmula es igual a la fecha máxima que contiene un valor, devuelva el valor de la columna Real; de lo contrario, devuelva #N/A.
Ahora aplique los mismos pasos anteriores para agregar la etiqueta de datos para la línea Real.
Completa el formato
A partir de aquí, todo es cuestión de formatear:
- Seleccione colores adecuados para cada línea.
- Utilice los mismos colores para cada etiqueta de datos (considere poner la fuente en negrita para que sea más fácil de leer)
- Cambie el ancho del área de trazado para garantizar que las etiquetas encajen
- Finalmente, hagamos que el eje de fechas encaje correctamente:
- Haga clic derecho en el eje de fecha, seleccione Formatear eje… en el menú
- En el panel Formato de eje , expanda la sección Número
- En el campo Código de formato , ingrese mmm para mostrar tres letras para cada mes (como alternativa, ingrese mmmmm para mostrar solo una letra para cada mes, o mmm-yy para mostrar el mes y el año). Luego haga clic en Agregar .
El gráfico final se ve así:
Pruébalo
Cuando agregamos más datos a la columna Real, la etiqueta se mueve en consecuencia.
Además, debido a que hemos usado una Tabla, si agregamos más meses a la Tabla, todas las fórmulas se copian automáticamente.
Conclusión
Crear etiquetas dinámicas requiere un pequeño esfuerzo adicional. Sin embargo, debido a que hemos aplicado los principios de la Gestalt, sabemos que proporciona importantes beneficios al lector, ya que le resulta más fácil ver con qué se relaciona cada línea.
Artículos Relacionados:
- Cómo crear un diagrama de Sankey en Excel
- Crear gráficos de pendiente en Excel
- Guía definitiva: VBA para gráficos en Excel (más de 100 ejemplos)
Deja una respuesta