Crear gráfico de varianza con flechas en Excel

En Excel, el gráfico de varianza o diferencia con flechas es un tipo de gráfico de columnas agrupadas —o gráfico de barras— que incluye flechas hacia arriba o hacia abajo para indicar el porcentaje de aumento o disminución entre dos conjuntos de datos.

Por ejemplo, si desea mostrar la variación en las ventas respecto al año anterior, puede utilizar flechas verdes y rojas para representar el cambio porcentual, tal como se ilustra en la siguiente captura de pantalla. En este artículo le explicaré cómo crear este tipo de gráfico avanzado en Excel.

• Crear un gráfico de varianza o diferencia con flechas en Excel
• Crear un gráfico de varianza o diferencia con flechas en Excel con una función práctica
• Descargar archivo de ejemplo de gráfico de varianza o diferencia con flechas
• Vídeo: Crear un gráfico de varianza o diferencia con flechas en Excel

Crear un gráfico de varianza o diferencia con flechas en Excel

Para crear un gráfico de varianza con flechas que compare el cambio porcentual del año 2020 respecto al año 2019, primero debe insertar algunas columnas auxiliares para calcular los datos básicos, tal como se muestra en la siguiente captura de pantalla:

Primero, cree los datos de las columnas auxiliares

1. En la celda D2, junto a los datos originales, introduzca la siguiente fórmula y arrástrela hasta la celda D10; consulte la captura de pantalla:

2. En la celda E2, introduce la siguiente fórmula y cópiala hasta la celda E10; consulta la captura de pantalla:

3.Escriba la siguiente fórmula en la celda F2 y cópiela hasta la celda F10; consulte la captura de pantalla:

4. A continuación, introduzca la siguiente fórmula en la celda G2 y arrástrela hasta la celda G10 para rellenarla; consulte la captura de pantalla:

5. A continuación, introduzca la siguiente fórmula en la celda H2 y arrástrela hasta la celda H10; consulte la captura de pantalla:

6. A continuación, introduzca la siguiente fórmula en la celda I2 y arrastre el controlador de relleno hasta la celda I10; consulte la captura de pantalla:

Segundo, cree el gráfico de varianza con flechas a partir de los datos de las columnas auxiliares

7. Tras crear los datos auxiliares, seleccione los datos de las columnas A, B, C, D y E y, a continuación, haga clic en Insertar > Insertar columna o gráfico de barras > Columnas agrupadas; consulte la captura de pantalla:

8. Se insertará un gráfico de columnas agrupadas, como se muestra en la siguiente captura de pantalla:

9. A continuación, haga clic para seleccionar el gráfico y elija Seleccionar Datos en el menú contextual; consulte la captura de pantalla:

10. En el cuadro de diálogo emergente Seleccionar origen de datos, en el cuadro de lista Rango de nombres de la serie, seleccione la serie Máx. izquierda y haga clic en el botón de flecha hacia arriba para mover esta serie de datos a la parte superior; consulte la siguiente demostración:

11. A continuación, haga clic en el botón Aceptar para cerrar el cuadro de diálogo. Luego, haga clic con el botón derecho en la serie de datos Máx. izquierda, pulse el botón Elementos del gráfico para expandir la lista desplegable y active la casilla Barras de error. Consulte la captura de pantalla:

12. Siga haciendo clic con el botón derecho en la serie de datos Máx. derecha y, a continuación, pulse el botón Elementos del gráfico para expandir la lista desplegable y marque la casilla Barras de error; consulte la captura de pantalla:

13. A continuación, haga clic con el botón derecho en las barras de error Y Máx. izquierda y elija la opción Formato de barras de error en el menú contextual; consulte la captura de pantalla:

14. A continuación, en el panel Formato de barras de error abierto, bajo la pestaña Opciones de barra de error, en la sección Barra de error vertical, realice las siguientes operaciones:

• Seleccione la opción Menosen la sección Dirección;
• A continuación, seleccione Sin extremoen la sección Estilo del extremo;
• A continuación, seleccione Personalizado en la sección Cantidad de error y haga clic en el botón Especificar valor. En el cuadro de diálogo emergente Barras de error personalizadas, deje en blanco el cuadro de texto Valor de error positivo y seleccione los datos de la columna Flecha arriba en el cuadro de texto Valor de error negativo. Por último, haga clic en Aceptar para cerrar el cuadro de diálogo.

15. Mantenga abierto el panel Formato de barras de error y, a continuación, haga clic para seleccionar las barras de error Y Máx. derecha en el gráfico. En la pestaña Opciones de barra de error, dentro de la sección Barra de error vertical, realice las siguientes operaciones:

• Seleccione la opción Menosen la sección Dirección;
• A continuación, seleccione Sin extremoen la sección Estilo del extremo;
• A continuación, seleccione Personalizado en la sección Cantidad de error y haga clic en el botón Especificar valor. En el cuadro de diálogo emergente Barras de error personalizadas, deje en blanco el cuadro de texto Valor de error positivo y seleccione los datos de la columna Flecha abajo en el cuadro de texto Valor de error negativo. Por último, haga clic en Aceptar para cerrar el cuadro de diálogo.

16. A continuación, debe hacer invisibles las barras de las series de datos «Máx. izquierda» y «Máx. derecha». Haga clic con el botón derecho en la barra de datos «Máx. izquierda» y seleccione Formato de serie de datos. En el panel Formato de serie de datos que aparece, vaya a la pestaña Relleno y línea, y en la sección Relleno, elija Sin relleno; consulte la captura de pantalla:

17. Realice la misma operación para ocultar las barras de datos de Máx. derecha; consulte la captura de pantalla:

18. Tras ocultar las barras de datos Máx. izquierda y Máx. derecha, haz clic con el botón derecho en las barras de error Y máx. izquierda y, a continuación, elige Formato de barras de error en el menú contextual. En el panel Formato de barras de error, bajo la pestaña Relleno y línea, realiza las siguientes operaciones:

• Seleccione la opción Línea continuay especifique después un color que le guste para las flechas hacia arriba en el menú desplegable Color;
• A continuación, ajuste el grosor de la línea a 1,25 ptsegún sus necesidades en la sección Grosor;
• Después, seleccione el icono Flecha en la lista desplegable del tipo Flecha inicial.

19. Mantenga el panel Formato de barras de error aún abierto y, a continuación, haga clic para seleccionar las barras de error Y máx. derecha. En el panel Formato de barras de error, bajo la pestaña Relleno y línea, realice las siguientes operaciones:

• Seleccione la opción Línea continuay especifique después otro color que le guste para las flechas hacia abajo en el menú desplegable Color;
• A continuación, ajuste el grosor de la línea a 1,25 ptsegún sus necesidades en la sección Grosor;
• Después, seleccione el icono Flecha en la lista desplegable del tipo Flecha final.

20. Y obtendrá el gráfico que se muestra en la siguiente captura de pantalla:

21. A continuación, haz clic con el botón derecho en la barra de datos Máx. izquierda y elige Formato de serie de datos en el menú contextual. En el panel Formato de serie de datos que se abre, bajo la pestaña Opciones de serie, selecciona la opción Eje secundario en la sección Trazar serie en. Luego, haz clic para seleccionar la barra de datos Máx. derecha y realiza la misma operación que se muestra en la siguiente demostración:

22. A continuación, haz clic con el botón derecho en cualquier barra del gráfico y selecciona Formato de serie de datos. En el panel Formato de serie de datos, bajo la pestaña Opciones de serie, introduce 0 % y 100 % en los cuadros Superposición de series y Anchura del intervalo, respectivamente.

A continuación, en el panel Formato de serie de datos, seleccione Serie “Año 2019” o Serie “Año 2020” en la lista desplegable Opciones de serie e introduzca 0 % y 100 % en los cuadros Superposición de series y Anchura del intervalo, respectivamente. Vea la siguiente demostración:

23. En este paso, puede agregar las Etiquetas de Datos al gráfico. Haga clic para seleccionar la serie de datos «Máx. izquierda» y, a continuación, haga clic en Elementos del gráfico. En el cuadro de lista expandido Elementos del gráfico, active la opción Etiquetas de Datos. Vea la captura de pantalla:

24. A continuación, haz clic con el botón derecho en las Etiquetas de datos y elige Formato de etiquetas de datos. Consulta la captura de pantalla:

25. En el panel Formato de Etiquetas de Datos abierto, bajo la pestaña Opciones de etiqueta, active la opción Valor de celdas. En el cuadro de diálogo Rango de Etiquetas de Datos que aparece, seleccione las celdas de datos de la columna Etiqueta de datos superior y, a continuación, haga clic en el botón Aceptar. Vea la captura de pantalla:

26. Todavía en el panel Formato de Etiquetas de Datos, desactive las opciones Valor y Mostrar líneas guía. Así obtendrá el siguiente gráfico. Vea la captura de pantalla:

27. A continuación, siga añadiendo las etiquetas de datos inferiores. Haga clic con el botón derecho en la barra de datos «Máx.» derecha y seleccione Elementos del gráfico para expandir el cuadro de lista. En el cuadro de lista Elementos del gráfico, active la opción Etiquetas de datos. Vea la captura de pantalla:

28. A continuación, haz clic con el botón derecho en las nuevas etiquetas de datos añadidas y selecciona Formato de Etiquetas de Datos. En el panel Formato de Etiquetas de Datos que se abre, ve a la pestaña Opciones de etiqueta y activa la opción Valor de celdas. En el cuadro de diálogo Rango de Etiquetas de Datos que aparece, selecciona las celdas de la columna de etiquetas de datos inferior y, a continuación, haz clic en el botón Aceptar. Consulta la captura de pantalla:

29. Todavía en el panel Formato de etiquetas de datos, desactive las opciones Valor y Mostrar líneas guía. Así se habrán añadido las etiquetas de datos de variación porcentual al gráfico. Vea la captura de pantalla:

30. Por último, elimine del gráfico los elementos innecesarios según sus necesidades, como las líneas de cuadrícula, los títulos del gráfico y el eje secundario, y obtendrá el gráfico completo que se muestra en la siguiente captura de pantalla:

Crear un gráfico de varianza o diferencia con flechas en Excel con una función práctica

Descargar archivo de ejemplo de gráfico de varianza o diferencia con flechas

Vídeo: Crear un gráfico de varianza o diferencia con flechas en Excel