Cuando se crea un gráfico en Excel y los datos de origen cambian, es necesario actualizar la fuente de datos del gráfico para asegurarse de que refleja los nuevos datos.
En caso de que trabaje con gráficos que se actualizan con frecuencia, es mejor crear un rango de gráfico dinámico.
Este tutorial cubre:
¿Qué es un rango de gráfico dinámico?
Un rango de gráfico dinámico es un rango de datos que se actualiza automáticamente cuando se cambia la fuente de datos.
Este rango dinámico se utiliza entonces como los datos de origen en un gráfico. A medida que los datos cambian, el rango dinámico se actualiza instantáneamente, lo que conduce a una actualización en el gráfico.
A continuación se muestra un ejemplo de un gráfico que utiliza un rango de gráfico dinámico.
Note que el gráfico se actualiza con los nuevos puntos de datos para mayo y junio tan pronto como los datos en introducido.
¿Cómo crear un rango de gráfico dinámico en Excel?
Hay dos formas de crear un rango de gráfico dinámico en Excel:
- Usando tabla de Excel
- Usando fórmulas
En la mayoría de los casos, usar tabla de Excel es la mejor forma de crear rangos dinámicos en Excel.
Veamos cómo funciona cada uno de estos métodos.
Haga clic aquí para descargar el archivo de ejemplo.
Uso de la tabla de Excel
El uso de la tabla de Excel es la mejor manera de crear rangos dinámicos, ya que se actualiza automáticamente cuando se añade un nuevo punto de datos a la misma.
La característica de la tabla de Excel se introdujo en la versión de Excel 2007 de Windows y si se trata de versiones anteriores a la misma, no podrá utilizarla (consulte la siguiente sección sobre la creación de rangos de gráficos dinámicos mediante fórmulas).
En el ejemplo siguiente, puede ver que en cuanto añado nuevos datos, la Tabla de Excel se expande para incluir estos datos como parte de la tabla (observe que el borde y el formato se expanden para incluirlos en la tabla).
Ahora, necesitamos utilizar esta tabla de Excel mientras creamos los gráficos.
Aquí están los pasos exactos para crear un gráfico de líneas dinámicas utilizando la tabla de Excel:
- Seleccione toda la tabla de Excel.
- Vaya a la pestaña Insertar.
- En el Grupo de gráficos, seleccione el gráfico ‘Línea con marcadores’.
¡Eso es!
Los pasos anteriores insertarían un gráfico de líneas que se actualizaría automáticamente al añadir más datos a la tabla de Excel.
Tenga en cuenta que mientras que al añadir nuevos datos se actualiza automáticamente el gráfico, al eliminar datos no se eliminan completamente los puntos de datos. Por ejemplo, si elimina 2 puntos de datos, el gráfico mostrará un espacio vacío a la derecha. Para corregir esto, arrastre la marca azul en la parte inferior derecha de la tabla de Excel para eliminar los puntos de datos eliminados de la tabla (como se muestra a continuación).
Aunque he tomado el ejemplo de un gráfico de líneas, también puede crear otros tipos de gráficos, como los de columnas/barras, utilizando esta técnica.
Uso de fórmulas de Excel
Como he mencionado, el uso de la tabla de Excel es la mejor manera de crear rangos de gráficos dinámicos.
Sin embargo, si no puede utilizar la tabla de Excel por alguna razón (posiblemente si está utilizando Excel 2003), hay otra forma (ligeramente complicada) de crear rangos de gráficos dinámicos utilizando fórmulas de Excel y rangos con nombre.
Suponga que tiene el conjunto de datos que se muestra a continuación:
Para crear un rango de gráfico dinámico a partir de estos datos, necesitamos:
- Crear dos rangos dinámicos con nombre utilizando la fórmula OFFSET (uno para cada columna ‘Valores’ y ‘Meses’). Añadir/eliminar un punto de datos actualizaría automáticamente estos rangos con nombre.
- Inserte un gráfico que utilice los rangos con nombre como fuente de datos.
Déjeme explicar cada paso en detalle ahora.
Paso 1 – Creación de rangos con nombre dinámicos
A continuación, los pasos para crear rangos con nombre dinámicos:
- Vaya a la pestaña ‘Fórmulas’.¡
- Haga clic en ‘Administrador de nombres’.
- En el cuadro de diálogo Administrador de nombres, especifique el nombre como ChartValues e introduzca la siguiente fórmula en Se refiere a la parte: =OFFSET(Fórmula!$B$2,,,COUNTIF(Formula!$B$2:$B$100,»<>»))
- Haga clic en Aceptar.
- En el cuadro de diálogo Administrador de nombres, haga clic en Nuevo.
- En el cuadro de diálogo Administrador de nombres, especifique el nombre como ChartMonths e introduzca la siguiente fórmula en la parte Refers to: =OFFSET(Formula!$A$2,,,COUNTIF(Formula!$A$2:$A$100,»<>»))
- Haga clic en Aceptar.
- Haga clic en Cerrar.
Los pasos anteriores han creado dos rangos con nombre en el Libro de Trabajo – ChartValue y ChartMonth (estos se refieren a los valores y al rango de meses en el conjunto de datos respectivamente).
Si vas y actualizas la columna de valores añadiendo un punto de datos más, el rango con nombre ChartValue se actualizará automáticamente para mostrar el punto de datos adicional en él.
La magia se hace con la función OFFSET aquí.
En la fórmula del rango con nombre ‘ChartValue’, hemos especificado B2 como punto de referencia. La fórmula OFFSET comienza allí y se extiende para cubrir todas las celdas llenas de la columna.
La misma lógica funciona en la fórmula de rango nombrado ChartMonth también.
Paso 2 – Crear un gráfico utilizando estos rangos nombrados
Ahora todo lo que necesita hacer es insertar un gráfico que utilizará los rangos nombrados como la fuente de datos.
Aquí están los pasos para insertar un gráfico y utilizar rangos de gráficos dinámicos:
- Vaya a la pestaña Insertar.
- Haga clic en ‘Insertar gráfico de líneas o áreas’ e inserte el gráfico ‘Línea con marcadores’. ¡Esto insertará el gráfico en la hoja de trabajo.
- Con el gráfico seleccionado, vaya a la pestaña Diseño.
- Haga clic en Seleccionar datos.
- En el cuadro de diálogo ‘Seleccionar fuente de datos’, haga clic en el botón Añadir en ‘Entradas de leyenda (Series)’.
- En el campo Valor de la serie, introduzca =Fórmula!ChartValues (tenga en cuenta que debe especificar el nombre de la hoja de trabajo antes del rango con nombre para que esto funcione).
- Haga clic en Aceptar.
- Haga clic en el botón Editar en las ‘Etiquetas del eje horizontal (categoría)’.
- En el cuadro de diálogo ‘Etiquetas del eje’, introduzca =Fórmula! ChartMonths
- Haga clic en Aceptar.
¡Ya está! Ahora su gráfico está utilizando un rango dinámico y se actualizará cuando añada/elimine puntos de datos en el gráfico.
Algunas cosas importantes que hay que saber cuando se utilizan rangos con nombre con los gráficos:
- No debe haber ninguna celda en blanco en los datos del gráfico. Si hay un espacio en blanco, el rango con nombre no se referirá al conjunto de datos correcto (ya que el recuento total hará que se refiera a un número menor de celdas).
- Debe seguir la convención de nomenclatura cuando utilice el nombre de la hoja en el origen del gráfico. Por ejemplo, si el nombre de la hoja es una sola palabra, como Fórmula, entonces se puede utilizar =Fórmula! ValorGráfico. Pero si hay más de una palabra, como Fórmula Gráfico, entonces usted necesita usar =’Fórmula Gráfico’! GráficoValor.
También puede gustar los siguientes tutoriales de Excel:
- Cómo crear un gráfico de termómetro en Excel.
- Cómo hacer una curva de campana en Excel.
- Cómo crear un gráfico de pasos en Excel.
- Cómo crear un gráfico de Pareto en Excel.
- Cómo hacer un histograma en Excel
- Cómo añadir barras de error en Excel (horizontales/verticales/personalizadas)