Crear gráfico de flecha de varianza en Excel

En Excel, el gráfico de flecha de varianza o diferencia es un tipo de gráfico de columnas agrupadas o de barras con 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 de ventas en comparación con el año anterior, puede usar flechas verdes y rojas para mostrar el cambio porcentual como se muestra en la captura de pantalla a continuación. En este artículo, hablaré sobre cómo crear este tipo complejo de gráfico en Excel.

• Crear gráfico de flecha de varianza o diferencia en Excel
• Crear gráfico de flecha de varianza o diferencia en Excel con una función práctica
• Descargar archivo de ejemplo de Gráfico de Flecha de Varianza o Diferencia
• Video: Crear gráfico de flecha de varianza o diferencia en Excel

Crear gráfico de flecha de varianza o diferencia en Excel

Para crear un gráfico de flecha de varianza que compare el cambio porcentual de 2020 basado en 2019, primero debe insertar algunas columnas auxiliares para calcular los datos básicos 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, escriba la siguiente fórmula y luego arrastre la fórmula hasta la celda D10, vea la captura de pantalla:

2. En la celda E2, ingrese la siguiente fórmula y luego copie la fórmula hasta la celda E10, vea la captura de pantalla:

3. Continúe escribiendo la siguiente fórmula en la celda F2 y copie la fórmula hasta la celda F10, vea la captura de pantalla:

4. Luego, en la celda G2, ingrese la siguiente fórmula y arrástrela hasta la celda G10 para completar la fórmula, vea la captura de pantalla:

5. Luego, ingrese la siguiente fórmula en la celda H2 y arrastre la fórmula hasta la celda H10, vea la captura de pantalla:

6. Luego, continúe ingresando la siguiente fórmula en la celda I2 y luego arrastre el controlador de relleno hacia abajo hasta la celda I10, vea la captura de pantalla:

Segundo, cree el gráfico de flecha de varianza basado en los datos de las columnas auxiliares

7. Después de crear los datos auxiliares, seleccione los datos en las columnas A, B, C, D, E y luego haga clic en Insertar > Insertar gráfico de columnas o barras > Columnas agrupadas, vea la captura de pantalla:

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

9. Luego, haga clic para seleccionar el gráfico y luego elija Seleccionar datos desde el menú contextual, vea la captura de pantalla:

10. En el cuadro de diálogo Seleccionar origen de datos emergente, en el cuadro de lista Entradas de leyenda (Series), seleccione la serie Max Izquierda y luego haga clic en el botón de flecha hacia arriba para mover esta serie de datos al inicio, vea la demostración a continuación:

11. Luego, haga clic en el botón Aceptar para cerrar el cuadro de diálogo y luego haga clic derecho en la serie de datos Max Izquierda, haga clic en el botón Elementos del gráfico para expandir el cuadro de lista, en el cuadro de lista, marque Barras de error, vea la captura de pantalla:

12. Continúe haciendo clic derecho en la serie de datos Max Derecha y luego haga clic en el botón Elementos del gráfico para expandir el cuadro de lista, en el cuadro de lista, marque Barras de error, vea la captura de pantalla:

13. Luego, haga clic derecho en las Barras de error Y Max Izquierda y elija Formato de barras de error desde el menú contextual, vea la captura de pantalla:

14. Luego, 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, configure las siguientes operaciones:

• Seleccione la opción Menos en la sección Dirección;
• Luego, seleccione Sin tapa desde la sección Estilo final;
• Y luego, seleccione Personalizado desde la sección Cantidad de error, luego haga clic en el botón Especificar valor, en el cuadro de diálogo Barras de error personalizadas emergente, mantenga en blanco el cuadro de texto Valor de error positivo y seleccione los datos de la columna Flecha hacia arriba en el cuadro de texto Valor de error negativo, y luego haga clic en Aceptar para cerrar el cuadro de diálogo.

15. Mantenga abierto el panel Formato de barras de error y luego haga clic para seleccionar las Barras de error Y Max Derecha del gráfico, bajo la pestaña Opciones de barra de error, en la sección Barra de error vertical, configure las siguientes operaciones:

• Seleccione la opción Menos en la sección Dirección;
• Luego, seleccione Sin tapa desde la sección Estilo final;
• Y luego, seleccione Personalizado desde la sección Cantidad de error, luego haga clic en el botón Especificar valor, en el cuadro de diálogo Barras de error personalizadas emergente, mantenga en blanco el cuadro de texto Valor de error positivo y seleccione los datos de la columna Flecha hacia abajo en el cuadro de texto Valor de error negativo, y luego haga clic en Aceptar para cerrar el cuadro de diálogo.

16. Luego, debe formatear las barras de datos Max Izquierda y Max Derecha para hacerlas invisibles, haga clic derecho en la barra de datos Max Izquierda y elija la opción Formato de serie de datos, en el panel Formato de serie de datos abierto, bajo la pestaña Relleno y línea, seleccione Sin relleno desde la sección Relleno, vea la captura de pantalla:

17. Realice la misma operación para ocultar las barras de datos Max Derecha, vea la captura de pantalla:

18. Después de ocultar las barras de datos Max Izquierda y Max Derecha, ahora haga clic derecho en las Barras de error Y Max Izquierda y luego elija Formato de barras de error desde el menú contextual, 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 sólida y luego especifique un color que le guste para las flechas hacia arriba desde la lista desplegable Color;
• Luego ajuste el ancho de línea a 1,25 pt según sea necesario desde la sección Ancho;
• Luego, seleccione el ícono de flecha desde la lista desplegable Tipo de flecha inicial.

19. Mantenga abierto el panel Formato de barras de error, luego haga clic para seleccionar las Barras de error Y Max 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 sólida y luego especifique otro color que le guste para las flechas hacia abajo desde la lista desplegable Color;
• Luego ajuste el ancho de línea a 1,25 pt según sea necesario desde la sección Ancho;
• Luego, seleccione el ícono de flecha desde la lista desplegable Tipo de flecha final.

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

21. Luego, haga clic derecho en la barra de datos Max Izquierda y elija Formato de serie de datos desde el menú contextual, en el panel Formato de serie de datos abierto, bajo la pestaña Opciones de serie, seleccione la opción Eje secundario desde la sección Trazar serie en, luego haga clic para seleccionar la barra de datos Max Derecha, realice la misma operación como se muestra en la demostración a continuación:

22. Luego, haga clic derecho en cualquier barra del gráfico y elija Formato de serie de datos, en el panel Formato de serie de datos, bajo la pestaña Opciones de serie, ingrese 0% y 100% en los cuadros Superposición de series y Ancho de espacio respectivamente.

Luego, en el panel Formato de serie de datos, continúe seleccionando Serie “Año 2019” o Serie “Año 2020” desde la lista desplegable Opciones de serie, e ingrese 0% y 100% en los cuadros Superposición de series y Ancho de espacio respectivamente también. Vea la demostración a continuación:

23. En este paso, puede agregar las etiquetas de datos para el gráfico, haga clic para seleccionar la serie de datos Max Izquierda y luego haga clic en Elementos del gráfico, en el cuadro de lista expandido de Elementos del gráfico, marque la opción Etiquetas de datos, vea la captura de pantalla:

24. Luego, haga clic derecho en las etiquetas de datos y elija Formato de etiquetas de datos, vea la captura de pantalla:

25. En el panel Formato de etiquetas de datos abierto, bajo la pestaña Opciones de etiqueta, marque la opción Valor de celdas y en el cuadro de diálogo Rango de etiquetas de datos emergente, seleccione las celdas de datos de la columna Etiqueta de datos hacia arriba, luego haga clic en el botón Aceptar, vea la captura de pantalla:

26. Todavía en el panel Formato de etiquetas de datos, desmarque las opciones Valor y Mostrar líneas guía, y obtendrá el siguiente gráfico, vea la captura de pantalla:

27. Luego, continúe agregando las etiquetas de datos hacia abajo, haga clic derecho en la barra de datos Max Derecha y haga clic en Elementos del gráfico para expandir el cuadro de lista, en el cuadro de lista de Elementos del gráfico, marque la opción Etiquetas de datos, vea la captura de pantalla:

28. Luego, haga clic derecho en las nuevas etiquetas de datos agregadas y elija Formato de etiquetas de datos, en el panel Formato de etiquetas de datos abierto, bajo la pestaña Opciones de etiqueta, marque la opción Valor de celdas y en el cuadro de diálogo Rango de etiquetas de datos emergente, seleccione las celdas de datos de la columna Etiqueta de datos hacia abajo, luego haga clic en el botón Aceptar, vea la captura de pantalla:

29. Todavía en el panel Formato de etiquetas de datos, desmarque las opciones Valor y Mostrar líneas guía, y las etiquetas de datos de cambio porcentual se han agregado al gráfico, vea la captura de pantalla:

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

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

Descargar archivo de ejemplo de Gráfico de Flecha de Varianza o Diferencia

Video: Crear gráfico de flecha de varianza o diferencia en Excel