Cómo Crear un Gráfico de Rango Dinámico en Excel

Este tutorial demostrará cómo crear un Gráfico de Rango Dinámico en todas las versiones de Excel: 2007, 2010, 2013, 2016 y 2019.

Por defecto, cuando se expande o contrae un conjunto de datos utilizado para trazar un gráfico en Excel, los datos de origen subyacentes también tienen que ser ajustados manualmente.

Sin embargo, al crear Gráficos de rangos dinámicos, puede evitar esta molestia.

Los Gráficos de rangos dinámicos permiten actualizar automáticamente los datos de origen cada vez que se añaden o eliminan valores del rango de datos, lo que ahorra mucho tiempo y esfuerzo.

 

En este tutorial, aprenderá todo lo que necesita saber para liberar el poder de los Gráficos de Rangos de Dinámicos.

Gráficos de Rangos de Dinámicos – Introducción

Considere el siguiente conjunto de datos de ejemplo que analiza las fluctuaciones del margen de beneficio:

 

Básicamente, hay dos maneras de configurar un rango de gráfico dinámico:

1. Convirtiendo el rango de datos en una tabla
2. Utilizando rangos dinámicos con nombre como datos de origen del gráfico.

Ambos métodos tienen sus pros y sus contras, por lo que hablaremos de cada uno de ellos en mayor detalle para ayudarle a determinar cuál le funcionará mejor.

Sin más preámbulos, comencemos.

El Método de la Tabla

Permíteme empezar mostrándote la forma más rápida y sencilla de llevar a cabo la tarea que nos ocupa. Esto es lo que hay que hacer: Convierte el rango de datos en una tabla y ya está: más fácil que pelar guisantes.

De esta forma, todo lo que escribas en las celdas al final de la tabla se incluirá automáticamente en los datos de origen del gráfico.

A continuación te explicamos cómo hacerlo en dos sencillos pasos.

Paso #1: Convertir el Rango de Datos en una Tabla.

Desde el principio, transforme el rango de celdas que contiene los datos del gráfico en una tabla.

1. Resalte todo el rango de datos(A1:B6).
2. Haz clic en la pestaña Insertar.
3. Pulsa el botón “Tabla”.

 

En el cuadro de diálogo Crear tabla, haz lo siguiente:

1. Compruebe que el rango de celdas resaltado coincide con toda la tabla de datos.
2. Si su tabla no contiene ninguna fila de encabezado, desmarque la casilla “La tabla tiene encabezados”.
3. Haga clic en “Aceptar”

 

Como resultado, debería obtener esta tabla:

 

Paso #2: Crear un Gráfico Basado en la Tabla.

Ya se han sentado las bases, por lo que ahora puede crear un gráfico a partir de la tabla.

1. Resalte toda la tabla(A1:B6).
2. Vaya a la pestaña Insertar.
3. Crea cualquier gráfico 2D. A título ilustrativo, vamos a crear un simple gráfico de columnas (Insertar gráfico de columnas o de barras > Columnas agrupadas).

 

Ya está! Para probar la técnica, prueba a añadir nuevos puntos de datos en la parte inferior de la tabla para verlos automáticamente representados en el gráfico. ¿Cuánto más sencillo puede ser?

 

NOTA: Con este método, el conjunto de datos nunca debe contener celdas vacías, ya que arruinaría el gráfico.

El Método de Rango Nombrado Dinámico

Aunque es fácil de aplicar, el Método de Tabla demostrado anteriormente tiene algunas desventajas. Por ejemplo, el gráfico se desordena cada vez que el nuevo conjunto de datos termina siendo más pequeño que la tabla de datos inicial, además, a veces simplemente no desea que el rango de datos se convierta en una tabla.

Optar por rangos con nombre puede requerir un poco más de tiempo y esfuerzo, pero la técnica anula los inconvenientes del método de tabla y, además, hace que el rango dinámico sea mucho más cómodo para trabajar a largo plazo.

Paso #1: Crear los Rangos Dinámicos Nombrados.

Para empezar, configure los rangos con nombre que se utilizarán como fuente de datos para su futuro gráfico.

1. Vaya a la pestaña Fórmulas.
2. Haga clic en “Administrador de nombres”
3. En el cuadro de diálogo Administrador de nombres que aparece, seleccione “Nuevo”

 

En el cuadro de diálogo “Nombre Nuevo”, cree un nuevo rango con nombre:

1. Escriba “Trimestre” junto al campo “Nombre”. Para su comodidad, haga que el nombre del rango dinámico coincida con la celda correspondiente de la fila de encabezamiento de la columna A(A1).
2. En el campo “Ámbito”, selecciona la hoja de cálculo actual. En nuestro caso, es la Hoja1.
3. Introduce la siguiente fórmula en el campo «Se refiere a” : =DESREF(Hoja1!$A$2;0;0;CONTARA(Hoja1!$A:$A)-1;1) 

 

 

En pocas palabras, cada vez que cambias cualquier celda de la hoja de cálculo, la función DESREF devuelve sólo los valores reales de la columna A, dejando fuera la celda de la fila de cabecera(A1), mientras que la función CONTARA recalcula el número de valores de la columna cada vez que se actualiza la hoja de cálculo, haciendo todo el trabajo sucio por ti.

Vamos a desglosar la fórmula con más detalle para ayudarte a entender cómo funciona:

 

NOTA: El nombre de un rango con nombre debe empezar por una letra o un guión bajo y no debe contener espacios.

Del mismo modo, cree otro rango con nombre basado en la columna Margen de beneficios(columna B) con la ayuda de esta fórmula y etiquételo como “Margen_Beneficios”:

=DESREF(Hoja1!$B$2;0;0;CONTARA(Hoja1!$B:$B)-1;1)

 

Repita el mismo proceso si su tabla de datos contiene varias columnas con valores reales. En nuestro caso, como resultado, deberías tener dos rangos con nombre listos para la acción:

 

Paso #2: Crear un Gráfico Vacío.

Ya hemos superado la parte más complicada. Ahora, es el momento de crear un gráfico vacío para que pueda insertar manualmente los rangos dinámicos con nombre en él.

1. Selecciona cualquier celda vacía de la hoja de cálculo actual(Hoja1).
2. Vuelve a la pestaña Insertar.
3. Configura el gráfico 2D que quieras. Para nuestro ejemplo, crearemos un gráfico de columnas(Insertar columna o Gráfico de barras > Columna agrupada).

 

Paso #3: Añadir el Rango o Rangos con Nombre que Contienen los Valores Reales.

Primero, inserte el rango con nombre (Margen_Beneficios) vinculado a los valores reales (columna B) en el gráfico.

Haga clic con el botón derecho del ratón en el gráfico vacío y seleccione “Seleccionar datos” en el menú contextual.

 

En la ventana de diálogo Seleccionar origen de datos, haz clic en “Agregar”

 

En el cuadro Editar serie, cree una nueva serie de datos:

1. En “Nombre de la serie”, resalte la celda de la fila de cabecera correspondiente(B1).
2. En “Valores de la serie”, especifique el rango con nombre que se trazará en el gráfico escribiendo lo siguiente “=Hoja1!Margen_Beneficios” La referencia se compone de dos partes: los nombres de la hoja de cálculo actual (=Hoja1) y el respectivo rango dinámico con nombre (Margen_Beneficios). El signo de exclamación se utiliza para unir las dos variables.
3. Seleccione “Aceptar”

 

Una vez allí, Excel trazará automáticamente los valores:

 

Paso #4: Insertar el Rango con Nombre con las Etiquetas de los Ejes.

Finalmente, reemplace las etiquetas de eje de categoría por defecto con el rango con nombre comprendido en la columna A(Trimestre).

En el cuadro de diálogo Seleccionar origen de datos, en “Etiquetas de eje horizontal (categoría)”, seleccione el botón “Editar”.

 

A continuación, inserte el rango con nombre en el gráfico introduciendo la siguiente referencia en «Rango de etiquetas de eje:»

Hoja1!Trimestre

 

Por último, el gráfico de columnas basado en el rango dinámico del gráfico está listo:

 

Compruébalo: El gráfico se actualiza automáticamente cada vez que añades o eliminas datos en el rango dinámico.