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

En Excel, insertar una línea horizontal o vertical en un gráfico suele ser una tarea habitual; dicha línea puede representar un valor objetivo o un promedio. Este tipo de línea facilita la comparación entre el valor actual y el valor de referencia, permitiéndole identificar de inmediato qué datos se sitúan por encima o por debajo de dicho umbral, tal como se muestra en las siguientes capturas de pantalla. En este artículo le explicaré cómo crear un gráfico de columnas o de 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 o de objetivo horizontalmente
• Añadir una línea de objetivo con Valor diferente en un Gráfico de columnas
• Crear Gráfico de columnas con línea de promedio dinámica mediante el uso de Botón de Opción
• Crear Gráfico de barras con línea de promedio o de objetivo verticalmente
• Añadir una línea de objetivo con Valor diferente en un Gráfico de barras
• Añadir una línea de objetivo/promedio a una columna o Gráfico de barras con una práctica función
• 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 línea de promedio u objetivo horizontalmente, siga estos pasos:

1. Si quieres crear un gráfico de columnas con una línea de promedio, primero debes calcular el valor medio de la lista de datos. Para ello, introduce la siguiente fórmula en una celda vacía y arrastra el controlador de autorrelleno para copiarla en las demás celdas, tal como se muestra en la captura de pantalla:

2. A continuación, seleccione el rango de datos que incluya los valores promedio y haga clic en Insertar > Insertar columna o gráfico de barras > Columnas agrupadas, tal como se muestra en la captura de pantalla:

3. Se insertará un gráfico de columnas en la hoja de cálculo. Puede eliminar los elementos innecesarios, como el título del gráfico o las líneas de cuadrícula, según sus necesidades, tal como se muestra en las capturas de pantalla:

4. A continuación, haga clic con el botón derecho en la serie de datos del promedio (barras naranjas) y seleccione Cambiar tipo de serie / Tipo de gráfico en el menú contextual, tal como se muestra en la captura de pantalla:

5. En el cuadro de diálogo Cambiar Tipo de Gráfico, en el cuadro de lista Elija el tipo de gráfico y el eje para su serie de datos, seleccione Gráfico de líneas en la lista desplegable Tipo de Gráfico para la serie de datos Promedio, tal como se muestra en la captura de pantalla:

6. A continuación, haga clic en el botón Aceptar y obtendrá el gráfico que se muestra en la siguiente captura de pantalla:

7. A continuación, puede añadir la etiqueta de datos a esta línea de promedio. Haga doble clic para seleccionar el punto de datos situado más a la derecha de la línea, tal como se muestra en la captura de pantalla:

8. A continuación, haga clic en Elementos del gráfico, marque la casilla Etiquetas de datos y se insertarán las etiquetas de datos en la línea de promedio, tal como se muestra en la captura de pantalla:

Añadir una línea de objetivo con Valor diferente en un Gráfico de columnas

En ocasiones, es posible que desee comparar el valor actual con distintos valores objetivo para cada fila. En ese caso, el siguiente tipo de gráfico puede resultarle especialmente útil para visualizar los valores objetivo con mayor claridad.

Para crear este tipo de gráfico, proceda de la siguiente manera:

1. Primero, seleccione el rango de datos y haga clic en Insertar > Insertar columna o gráfico de barras > Columnas agrupadas para insertar el gráfico.

2. A continuación, haz clic con el botón derecho en la serie de datos del objetivo (barras naranjas) y selecciona Cambiar tipo de serie / Tipo de gráfico en el menú contextual. En el cuadro de diálogo Cambiar tipo de gráfico, elige Gráfico de líneas para la serie de datos del objetivo en la lista desplegable Tipo de gráfico, tal como se muestra en la captura de pantalla:

3. A continuación, haga clic en el botón Aceptar y, luego, haga doble clic en la línea de objetivo y seleccione Formato de serie de datos, tal como se muestra en la captura de pantalla:

4. En el panel Formato de serie de datos abierto, en la pestaña Relleno y línea, seleccione Sin línea en la sección Línea, tal como se muestra en la captura de pantalla:

5. Todavía en la pestaña Relleno y línea, vaya a la sección Marcador, seleccione Incorporado en la sección Opciones de marcador, elija la barra horizontal en la lista desplegable Tipo y, por último, establezca un tamaño que se corresponda con el ancho de sus barras en el cuadro Tamaño, tal como se muestra en la captura de pantalla:

6. A continuación, establezca el color de las líneas de objetivo según su preferencia en la sección Relleno, tal como se muestra en la captura de pantalla:

7. Por último, puede eliminar elementos innecesarios, como el título del gráfico o las líneas de cuadrícula, según sus necesidades.

Crear Gráfico de columnas con línea de promedio dinámica mediante el uso de Botón de Opción

Si dispone de una tabla extensa con las ventas del producto a lo largo de varios años y desea mostrar la línea de promedio de cada año en un gráfico, puede utilizar un gráfico con línea de promedio haciendo clic en el botón de opción, tal como se muestra en la siguiente demostración. Al seleccionar el año correspondiente, el gráfico con la línea de promedio se actualizará dinámicamente.

Para crear este gráfico dinámico, primero debe insertar varios botones de opción y, a continuación, crear una tabla de datos auxiliar que sirva de base al gráfico. Por último, inserte un gráfico vinculado a los botones de opción. Siga estos pasos:

Primero, inserte varios Botón de Opción

1. Haga clic en Desarrollador > Insertar > Botón de opción (control de formulario) y, a continuación, dibuje cuatro botones de opción y edite el texto según sus necesidades, tal como se muestra en las capturas de pantalla:

2. A continuación, haz clic con el botón derecho en el primer Botón de Opción y selecciona Formato de control en el menú contextual, tal como se muestra en la captura de pantalla:

3. En el cuadro de diálogo Formato de objeto abierto, en la pestaña Control, seleccione una celda para vincular el Botón de Opción desde el cuadro de texto Celda y haga clic en Aceptar para cerrarlo. Vea la captura de pantalla:

En segundo lugar, inserte una tabla auxiliar de datos para crear el gráfico basado en

4. Copie los nombres de los productos de la tabla original a un nuevo rango. A continuación, aplique la fórmula siguiente en una celda vacía junto a los datos copiados y arrastre el controlador de relleno para obtener el resultado. Vea la captura de pantalla:

5. Ahora, al hacer clic en uno de los botones de opción, se mostrará automáticamente el año correspondiente de los datos, tal como se muestra en la siguiente demostración:

6. A continuación, calcule los valores promedio con la fórmula siguiente. Vea la captura de pantalla:

En tercer lugar, cree el gráfico basado en la nueva tabla auxiliar de datos

A continuación, seleccione los nuevos datos creados y aplique los pasos 2 a 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ámica. Vea la demostración siguiente:

Crear un Gráfico de barras con línea de promedio o de objetivo verticalmente

Esta sección explica cómo crear un gráfico de barras vertical con promedio u objetivo. Siga estos pasos:

1. En primer lugar, cree una tabla auxiliar de datos: introduzca X e Y en dos celdas y, a continuación, calcule los valores promedio en las celdas D2 y D3. Vea la captura de pantalla:

2. A continuación, introduzca los números 0 y 1 en las celdas E2 y E3 para el valor Y. Vea la captura de pantalla:

3. A continuación, seleccione el rango de datos original y haga clic en Insertar > Insertar columna o gráfico de barras > Barras agrupadas. Vea la captura de pantalla:

4. Se ha insertado un gráfico de barras en la hoja. Haz clic con el botón derecho en la barra y selecciona Seleccionar datos en el menú contextual. Consulta la captura de pantalla:

5. En el cuadro de diálogo emergente Origen de datos, haga clic en el botón Agregar. Vea la captura de pantalla:

6. A continuación, en el cuadro de diálogo Editar serie, introduzca un nombre para la serie en el cuadro de texto Nombre de la Serie y seleccione las celdas con los valores X (D2:D3 en este ejemplo) en el cuadro de texto Valores de la serie. Vea la captura de pantalla:

7. Haga clic en Aceptar > Aceptar para cerrar los cuadros de diálogo. La nueva serie de datos ya se ha añadido a su gráfico de barras (aparecerán dos barras naranjas). Haga clic con el botón derecho en una barra naranja y seleccione Cambiar tipo de gráfico de serie. Vea la captura de pantalla:

8. En el cuadro de diálogo emergente Cambiar tipo de gráfico de Tipo de Gráfico, seleccione Dispersión con líneas suaves en la lista desplegable Tipo de Gráfico para la serie de datos Promedio. Vea la captura de pantalla:

9. A continuación, haga clic en Aceptar para cerrar el cuadro de diálogo. La nueva serie de datos ya aparece como un punto de datos a lo largo del eje Y principal. Haga clic con el botón derecho en la barra y elija Seleccionar Datos en el menú contextual. Vea la captura de pantalla:

10. En el cuadro de diálogo Origen de datos, en la lista Rango de nombres de la serie, haga clic en la opción Promedio y, a continuación, pulse el botón Editar. Vea la captura de pantalla:

11. A continuación, aparecerá el cuadro emergente Editar serie. En el cuadro Valor de la Serie en el Eje X, seleccione las dos celdas X (D2:D3) y, en el cuadro Valor de la Serie en el Eje Y, 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 verá que se ha añadido una línea vertical de promedio al gráfico. Vea la captura de pantalla:

13. A continuación, haz clic con el botón derecho en el eje vertical secundario (el eje de la derecha) y selecciona Formato de eje en el menú contextual. Consulta la captura de pantalla:

14. En el panel Formato de eje abierto, en la pestaña Opciones de eje, dentro de la sección Opciones de eje, escriba 1,0 en el cuadro Límite máximo y obtendrá el gráfico que se muestra en la siguiente captura de pantalla:

15. Por último, elimine el eje vertical secundario y las líneas de cuadrícula no deseadas según sus necesidades. ¡Ya tiene un gráfico de barras con una línea vertical de promedio! Vea la captura de pantalla:

Añadir una línea de objetivo con Valor diferente en un Gráfico de barras

Para añadir la línea de objetivo con Valor diferente en un Gráfico de barras, tal como se muestra en las siguientes capturas de pantalla, siga los pasos siguientes:

1. Seleccione el rango de datos y, a continuación, haga clic en Insertar > Insertar columna o gráfico de barras > Barras agrupadas para insertar un gráfico de barras. Vea la captura de pantalla:

2. A continuación, haga clic con el botón derecho en la serie de datos de objetivo (las barras naranjas) y seleccione Formato de serie de datos en el menú contextual. Vea la captura de pantalla:

3. En el panel Formato de serie de datos abierto, en 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, vaya a la pestaña Relleno y línea y seleccione Sin relleno en la sección Relleno. Vea la captura de pantalla:

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

• Especifique un color que necesite desde el Colordesplegable;
• Establezca el desenfoque en 0 pt;
• Luego, ajuste el ángulo a 0°;
• Por último, ajuste la distancia a 2 pt según sus necesidades.

6. Ahora vuelva a la pestaña Opciones de serie y seleccione Eje secundario en la sección Opciones de serie. Cambie también el ancho del espacio a 90 %, según necesite. Vea la captura de pantalla:

7. Por último, elimine los elementos no deseados, como el mosaico del gráfico, el eje X superior y las líneas de cuadrícula, según lo necesite, y obtendrá un gráfico de barras con distintas líneas objetivo, tal como se muestra en la siguiente captura de pantalla:

Añada una línea de objetivo o media a un gráfico de columnas o a un Gráfico de barras con una práctica función

Descargar archivo de ejemplo de gráfico con línea de promedio/objetivo