Cómo crear un gráfico dinámico de Excel y mantener la coherencia de las actualizaciones.
Para los propósitos de esta discusión, un gráfico dinámico de Excel es un gráfico que se actualiza automáticamente cada vez que se agregan nuevas filas de datos. Lo opuesto es un gráfico estático, que no incorporará ninguna fila de datos nueva agregada al rango existente.
Para ser claros, cuando Excel crea un gráfico basado en un rango de celdas, si los valores de las celdas dentro de ese rango cambian, ambos tipos de gráficos se actualizarán en consecuencia. Sin embargo, estamos hablando de lo que sucede cuando agregamos nuevas filas de datos bajo el rango existente utilizado por el gráfico. En otras palabras, si agregamos nuevas filas de datos, un gráfico dinámico actualizará dinámicamente su rango e incorporará los nuevos valores de las filas. Un gráfico estático no lo hará.
El problema que estamos resolviendo
Para nuestro conjunto de datos, incluiremos carreras que requieran habilidades de Excel y documentaremos sus salarios nacionales promedio. Digamos que almacenamos estos datos en un rango normal, como este:
Seleccionamos cualquier celda en ese rango y usamos el comando Insertar Gráfico . Excel crea un gráfico básico como este:
Más adelante queremos sumar una carrera más. Entonces lo escribimos en una nueva fila debajo del rango, así:
Cuando miramos nuestro gráfico, nada cambió:
En otras palabras, nuestra nueva fila de datos fue excluida. Preferimos crear un gráfico que actualice dinámicamente su rango para incluir dichas filas nuevas.
Video
Las dos formas de crear un gráfico dinámico en Excel
En última instancia, el objetivo es decirle a Excel que la fuente de datos es un rango dinámico; De esa manera, el gráfico se actualizará automáticamente para reflejar cualquier adición (y cambio) realizada a los datos de origen.
Veremos dos formas de lograr esto: usando una tabla o una fórmula sofisticada. Usar una tabla es la opción más sencilla y funciona en la mayoría de situaciones, ¡así que comenzamos con eso!
Crear un gráfico dinámico usando una tabla
En lugar de almacenar nuestros datos de origen en un rango normal, queremos almacenarlos en una tabla. ¿Como hacemos eso? Fácil. Simplemente seleccionamos cualquier celda dentro del rango normal y usamos el comando Insertar Tabla .
Excel convierte el rango normal en una tabla y aplica algún formato predeterminado:
Ahora bien, lo mejor de las tablas es que se expanden automáticamente. Entonces, después de seleccionar cualquier celda de la tabla, podemos insertar nuestro gráfico con el comando Insertar Gráfico . Nuevamente, tenemos nuestro cuadro básico:
Pero ahora, cuando escribimos un nuevo valor inmediatamente debajo de la Tabla, la Tabla se expande automáticamente para incluirlo:
Esta vez… nuestro gráfico incluye automáticamente la nueva fila… bam:
Cada vez que agreguemos nuevas filas a la tabla, ¡nuestro gráfico dinámico se actualizará automáticamente!
Crear un gráfico dinámico usando una fórmula
El problema de utilizar el método de la tabla es que no todas las versiones de Excel tienen las mismas capacidades. Crear un gráfico dinámico en Excel es un poco más complicado con una fórmula, pero aún así es muy posible.
La fórmula utilizará las funciones COMPENSACIÓN y CONTAR, por lo que si buscas practicar un poco con ellas, ¡un gráfico dinámico es un excelente punto de partida!
Este ejemplo utilizará los mismos datos que antes, pero almacenados en un rango normal:
1. Primero, cree un rango con nombre navegando a Fórmulas en la cinta y seleccionando Administrador de nombres .
2. Después de que aparezca el cuadro de diálogo Administrador de nombres, seleccione Nuevo .
3. Aparecerá el cuadro de diálogo Nuevo nombre. Ahora ingrese Salary_Range en el campo Nombre . Luego, copie/pegue (o escriba) la siguiente fórmula en el cuadro Se refiere a (reemplace Hoja1 con el nombre de su hoja de trabajo, $B$2 para su primera celda numérica y $B:$B para su columna de números) .
=DESPLAZAMIENTO('Hoja1'!$B$2,,,CONTARA('Hoja1'!$B:$B)-1)
4. Presione Aceptar .
5. Seleccione el comando Nuevo una vez más.
6. Esta vez, ingrese Career_Range en el campo Nombre e inserte la siguiente fórmula en la sección a la que se refiere (use el nombre de su hoja y la letra de la columna que hace referencia a la columna de la etiqueta):
=DESPLAZAMIENTO('Hoja1'!$A$2,,,CONTARA('Hoja1'!$A:$A)-1)
7. Presione Aceptar.
Una vez configurados estos nombres, actualizarán dinámicamente los rangos a los que hacen referencia. En un momento, las usaremos en nuestro cuadro, pero primero hablemos de las fórmulas con más detalle.
¿Qué está pasando en la fórmula?
Al crear su gráfico, es útil saber por qué funcionan las fórmulas anteriores. La función DESPLAZAMIENTO se utiliza en esta fórmula para crear un rango que cambia automáticamente de altura dependiendo del número de celdas no vacías (proporcionadas por COUNTA).
Tenga en cuenta que, por este motivo, la fórmula COMPENSACIÓN no funcionará correctamente si su gráfico tiene celdas vacías.
El primer argumento representado por la función COMPENSACIÓN es la celda $B$2, que es una referencia absoluta al punto inicial del rango salarial. Es decir, la primera celda numérica.
Usamos COUNTA para el argumento de altura. Lo apuntamos a la columna que incluye los valores que estamos contando. Si nuestra primera fila de la columna incluye una etiqueta de encabezado, como Carrera o Salario promedio, la excluimos del rango del gráfico ingresando -1 para reiniciar uno del recuento.
Ahora, utiliza sus rangos con nombre para crear el gráfico.
1. Seleccione Insertar en el menú de la cinta. Luego seleccione el gráfico que desea utilizar (este ejemplo utiliza un gráfico de columnas 2D simple).
2. Vaya a la pestaña Diseño de gráfico y elija Seleccionar datos .
3. Aparecerá el cuadro de diálogo Seleccionar fuente de datos. En la sección Entradas de leyenda (Serie) , selecciona el botón Agregar .
4. Escriba la siguiente fórmula en el campo Valores de serie del cuadro y luego presione Aceptar .
='Hoja1'!Rango_salario
5. Regrese al cuadro de diálogo Seleccionar fuente de datos. Verá etiquetas de eje horizontal (categoría) en este cuadro de diálogo. Seleccione el botón Editar .
6. Aparecerá otro cuadro de diálogo llamado Etiquetas de eje. Ingrese la siguiente fórmula en el cuadro Rango de etiquetas del eje y luego presione Aceptar .
='Hoja1'!Carrera_Rango
¡Dale un título a tu gráfico y tu gráfico dinámico estará completo!
Al igual que antes, puedes insertar filas adicionales y el gráfico se actualizará automáticamente.
En la mayoría de las situaciones, crear un gráfico dinámico usando una tabla es más eficiente e igual de efectivo que usar fórmulas y rangos con nombre. Pero siempre es útil practicar y ampliar sus conocimientos de Excel a lo largo del camino.
archivo de muestra
Gráfico dinámico.xlsx Descargar
¿Tiene algún consejo adicional para crear gráficos dinámicos en Excel? ¡Háganos saber en los comentarios!
Deja una respuesta