Crear gráfico con línea de promedio / objetivo en Excel

En Excel, puede ser una tarea común insertar una línea horizontal o vertical a un gráfico; la línea horizontal o vertical puede hacer referencia a algún valor objetivo o promedio. Este tipo de línea facilita la comparación de los valores reales frente al valor objetivo o promedio, y con la ayuda de esta línea, puedes ver fácilmente dónde los valores están por encima o por debajo de este valor de referencia, como se muestra en las capturas de pantalla a continuación. En este artículo, hablaré sobre cómo crear el gráfico de columnas o barras con una línea de promedio u objetivo en Excel.

Gráfico de columnas con línea horizontal	Gráfico de barras con línea vertical

• Crear gráfico de columnas con línea de promedio u objetivo horizontalmente
• Agregar una línea de objetivo con diferentes valores en un gráfico de columnas
• Crear gráfico de columnas con línea de promedio dinámicamente usando botones de opción
• Crear gráfico de barras con línea de promedio u objetivo verticalmente
• Agregar una línea de objetivo con diferentes valores en un gráfico de barras
• Agregar una línea de objetivo/promedio a un gráfico de columnas o barras con una función práctica
• Descargar archivo de ejemplo de Gráfico con Línea de Promedio/Objetivo

Crear gráfico de columnas con línea de promedio u objetivo horizontalmente

Para crear un gráfico de columnas con una línea de promedio u objetivo horizontalmente, siga los siguientes pasos:

1. Si desea crear un gráfico de columnas con una línea de promedio, primero debe calcular el valor promedio de la lista de datos. Por favor, ingrese la siguiente fórmula en una celda en blanco y luego arrastre el controlador de autocompletar para copiar la fórmula en otras celdas, vea la captura de pantalla:

2. Luego, seleccione el rango de datos que incluye los valores promedio, y haga clic en Insertar > Insertar Gráfico de Columnas o Barras > Columna Agrupada, vea la captura de pantalla:

3. Y se insertará un gráfico de columnas en la hoja de trabajo, puede eliminar los elementos innecesarios, como el título del gráfico, líneas de cuadrícula según sea necesario, vea las capturas de pantalla:

4. Luego, por favor, haga clic derecho en la serie de datos promedio (barras naranjas) y elija Cambiar Tipo de Gráfico de Serie desde el menú contextual, vea la captura de pantalla:

5. En el cuadro de diálogo Cambiar Tipo de Gráfico, bajo la lista de selección Elija el tipo de gráfico y eje para su serie de datos, seleccione Gráfico de Líneas desde la lista desplegable Tipo de Gráfico para la serie de datos Promedio, vea la captura de pantalla:

6. Y luego, haga clic en el botón Aceptar, obtendrá el gráfico como se muestra en la siguiente captura de pantalla:

7. A continuación, puede agregar la etiqueta de datos para esta línea promedio, haga doble clic para seleccionar el punto de datos más a la derecha de la línea, vea la captura de pantalla:

8. Y luego, haga clic en Elementos del Gráfico, marque la casilla Etiquetas de Datos, y se habrá insertado la etiqueta de datos para la línea promedio, vea la captura de pantalla:

Agregar una línea de objetivo con diferentes valores en un gráfico de columnas

A veces, es posible que desee comparar los valores reales con los valores objetivo que son diferentes para cada fila; en este caso, para visualizar los valores objetivo de manera más clara, el siguiente tipo de gráfico puede ayudarlo.

Para crear este tipo de gráfico, por favor, haga lo siguiente:

1. Primero, seleccione el rango de datos y luego haga clic en Insertar > Insertar Gráfico de Columnas o Barras > Columna Agrupada para insertar el gráfico.

2. Luego, haga clic derecho en la serie de datos objetivo (barras naranjas) y elija Cambiar Tipo de Gráfico de Serie desde el menú contextual, en el cuadro de diálogo Cambiar Tipo de Gráfico, elija Gráfico de Líneas para la serie de datos objetivo desde la lista desplegable Tipo de Gráfico, vea la captura de pantalla:

3. Luego, haga clic en el botón Aceptar y luego haga doble clic en la línea objetivo, y elija Formato de Serie de Datos, vea la captura de pantalla:

4. En el panel Formato de Serie de Datos abierto, bajo la pestaña Relleno y Línea, seleccione Sin línea desde la sección Línea, vea la captura de pantalla:

5. Todavía en la pestaña Relleno y Línea, cambie a la sección Marcador, seleccione Incorporado desde la sección Opciones de Marcador, y luego seleccione la barra horizontal en el Tipo desplegable, finalmente, establezca un tamaño correspondiente al ancho de sus barras en el cuadro Tamaño, vea la captura de pantalla:

6. Y luego, establezca un color para las líneas objetivo como desee desde la sección Relleno, vea la captura de pantalla:

7. Por último, puede eliminar los elementos no deseados, como el título del gráfico, líneas de cuadrícula según sea necesario.

Crear gráfico de columnas con línea de promedio dinámicamente usando botones de opción

Si tiene una tabla grande con las ventas del producto en varios años, ahora desea mostrar la línea de promedio para cada año en un gráfico. Para manejar esta tarea, puede usar un gráfico con línea de promedio haciendo clic en el botón de opción como se muestra en la demostración a continuación. Al seleccionar el año correspondiente, el gráfico con línea de promedio se mostrará dinámicamente.

Para crear este gráfico dinámico, primero debe insertar varios botones de opción y luego crear una tabla de datos auxiliar en la que se base el gráfico, finalmente insertar un gráfico vinculado al botón de opción. Por favor, siga los siguientes pasos:

Primero, inserte varios botones de opción

1. Haga clic en Desarrollador > Insertar > Botón de Opción (Control de Formulario), y luego dibuje cuatro botones de opción y edite el texto según sea necesario, vea las capturas de pantalla:

2. Luego, haga clic derecho en el primer botón de opción y elija Formato de Control desde el menú contextual, vea la captura de pantalla:

3. En el cuadro de diálogo Formato de Objeto abierto, bajo la pestaña Control, seleccione una celda para vincular el botón de opción desde el cuadro de texto Vincular celda, y haga clic en Aceptar para cerrarlo, vea la captura de pantalla:

Segundo, inserte una tabla de datos auxiliar para crear el gráfico basado en ella

4. Copie los nombres de los productos de la tabla original a un nuevo rango, y luego aplique la siguiente fórmula en una celda en blanco junto a sus nuevos datos copiados, y luego arrastre el controlador de relleno para obtener el resultado, vea la captura de pantalla:

5. Ahora, cuando haga clic en un botón de opción, el año correspondiente de los datos se mostrará automáticamente como se muestra en la demostración a continuación:

6. Y luego, puede calcular los valores promedio utilizando la siguiente fórmula, vea la captura de pantalla:

Tercero, Cree el gráfico basado en la nueva tabla de datos auxiliar

Luego, seleccione los datos recién creados, y luego aplique el paso 2 al paso 8 del método 1 para crear un gráfico de columnas con una línea de promedio, y obtendrá un gráfico con una línea de promedio dinámicamente. Vea la demostración a continuación:

Crear gráfico de barras con línea de promedio u objetivo verticalmente

Esta sección introducirá cómo crear un gráfico de barras con promedio u objetivo verticalmente, por favor, haga lo siguiente:

1. Primero, cree una tabla de datos auxiliar, ingrese X e Y en dos celdas, y luego calcule los valores promedio en la celda D2 y D3, vea la captura de pantalla:

2. Luego, ingrese el número 0 y 1 en la celda E2 y E3 para los valores Y, vea la captura de pantalla:

3. Luego, seleccione el rango de datos original, y luego haga clic en Insertar > Insertar Gráfico de Columnas o Barras > Barra Agrupada, vea la captura de pantalla:

4. Y se ha insertado un gráfico de barras en la hoja, luego, haga clic derecho en la barra, y elija Seleccionar Datos desde el menú contextual, vea la captura de pantalla:

5. En el cuadro emergente Seleccionar Fuente de Datos, haga clic en el botón Agregar, vea la captura de pantalla:

6. Y luego, en el cuadro Editar Serie, ingrese un nombre de serie en el cuadro de texto Nombre de serie, y luego seleccione las celdas con los valores X (D2:D3 en este ejemplo) en el cuadro de texto Valores de serie, vea la captura de pantalla:

7. Haga clic en Aceptar > Aceptar para cerrar los cuadros de diálogo, y la nueva serie de datos ahora se ha agregado a su gráfico de barras (dos barras naranjas), y luego haga clic derecho en la barra naranja y elija Cambiar Tipo de Gráfico de Serie, vea la captura de pantalla:

8. En el cuadro emergente Cambiar Tipo de Gráfico, seleccione Dispersión con Líneas Suaves desde la lista desplegable Tipo de Gráfico para la serie de datos Promedio, vea la captura de pantalla:

9. Luego, haga clic en Aceptar para cerrar el cuadro de diálogo, y la nueva serie de datos se convierte en un punto de datos a lo largo del eje Y primario, luego, haga clic derecho en la barra, y luego elija Seleccionar Datos desde el menú contextual, vea la captura de pantalla:

10. En el cuadro de diálogo Seleccionar Fuente de Datos, en el cuadro de lista Entradas de Leyenda (Series), haga clic en la opción Promedio, y luego haga clic en el botón Editar, vea la captura de pantalla:

11. Luego, aparecerá un cuadro de mensaje Editar Serie, en el cuadro Valores X de la serie, seleccione las dos celdas X (D2:D3), y en el cuadro Valores Y de la serie, seleccione las dos celdas Y (E2:E3), vea la captura de pantalla:

12. Haga clic en Aceptar > Aceptar para cerrar los cuadros de diálogo, y puede ver que se ha agregado una línea de promedio vertical al gráfico, vea la captura de pantalla:

13. Luego, haga clic derecho en el eje vertical secundario (el eje derecho), y elija Formato de Eje desde el menú contextual, vea la captura de pantalla:

14. En el panel Formato de Eje abierto, bajo la pestaña Opciones del Eje, en la sección Opciones del Eje, escriba 1.0 en el cuadro Límite Máximo , y obtendrá el gráfico como se muestra en la siguiente captura de pantalla:

15. Por último, puede eliminar el eje vertical secundario y las líneas de cuadrícula no deseadas según sea necesario, ahora, un gráfico de barras con una línea de promedio vertical está terminado. Vea la captura de pantalla:

Agregar una línea de objetivo con diferentes valores en un gráfico de barras

Para agregar la línea de objetivo con diferentes valores en un gráfico de barras como se muestra en las capturas de pantalla a continuación, siga los siguientes pasos:

1. Seleccione el rango de datos, y luego haga clic en Insertar > Insertar Gráfico de Columnas o Barras > Barra Agrupada para insertar un gráfico de barras, vea la captura de pantalla:

2. Luego, haga clic derecho en la serie de datos objetivo (barras naranjas) y elija Formato de Serie de Datos desde el menú contextual, vea la captura de pantalla:

3. En el panel Formato de Serie de Datos abierto, bajo la pestaña Opciones de Serie, cambie la superposición de series al 100%, vea la captura de pantalla:

4. Todavía en el panel Formato de Serie de Datos, cambie a la pestaña Relleno y Línea, seleccione Sin relleno desde la sección Relleno, vea la captura de pantalla:

5. A continuación, vaya a la pestaña Efectos, bajo la sección Sombra, realice las siguientes operaciones:

• Especifique un color que necesite desde la lista desplegable Color;
• Establezca el desenfoque en 0 pt;
• Luego, ajuste el ángulo a 0°;
• Por último, cambie la distancia a 2pt según sea necesario.

6. Ahora, cambie nuevamente a la pestaña Opciones de Serie, seleccione Eje Secundario desde la sección Opciones de Serie, y cambie el ancho del espacio al 90% según sea necesario. Vea la captura de pantalla:

7. Finalmente, elimine los elementos no deseados, como el título del gráfico, el eje X superior, líneas de cuadrícula según sea necesario, obtendrá el gráfico de barras con diferentes líneas objetivo como se muestra en la siguiente captura de pantalla:

Agregar una línea de objetivo/promedio a un gráfico de columnas o barras con una función práctica

Descargar archivo de ejemplo de Gráfico con Línea de Promedio/Objetivo