Los gráficos dinámicos que utilizan la función OFFSET de Excel y los nombres de rangos cambian de tamaño cuando se añaden o eliminan datos. A diferencia del uso de Tablas para los gráficos dinámicos, este método es compatible con versiones anteriores de Excel.
Utilice las funciones OFFSET y COUNTA de Excel para crear nombres de rangos dinámicos que se ajusten automáticamente a medida que se añaden o eliminan datos.
Haga clic para descargar el archivo de ejemplo del tutorial sobre el cambio de tamaño dinámico de los gráficos mediante OFFSET y nombres de rangos. El ejemplo incluye datos horizontales y verticales.
El método de OFFSET y nombre de rango requiere más trabajo que los dos clics utilizados para una Tabla, pero le da control sobre todo lo relacionado con los datos del gráfico, como muestra mi libro «Balanced Scorecards and Operational Dashboards with Microsoft Excel». Este sitio, CriticalToSuccess.com, tiene ejemplos de otras formas de utilizar OFFSET, por ejemplo, la creación de un gráfico de desplazamiento, el control de los gráficos con menús desplegables, y más. Busque en CriticalToSuccess.com utilizando el término «OFFSET» o «rango dinámico» para ver ejemplos.
Creación de un gráfico estático
Comience su gráfico dinámico creando primero un gráfico estático como sigue,
1. Haga clic dentro del rango B7:D12 para que Excel sepa dónde se encuentran los datos del gráfico.
2. En la pestaña Insertar, en el grupo Gráficos, seleccione un gráfico simple como un gráfico de columnas, de barras o de líneas.
Mueva este gráfico para que tenga espacio para añadir datos debajo del rango de datos.
Creación de nombres de rangos dinámicos con OFFSET
Diferentes versiones de Excel tienen diferentes métodos para crear y editar fórmulas de nombres de rangos. Las siguientes instrucciones son para Excel 2007, 2010 y 2013.
1. En la ficha Fórmulas, en el grupo Nombre definido, haga clic en Definir nombre. Aparece el cuadro Definir nombre.
Cree nombres de rangos en el cuadro Definir nombre.
2. En el cuadro de texto Nombre, introduzca rngVDate. Las convenciones de nomenclatura son,
rng Nombre del rango
V Orientación vertical de los datos en B7:B16
Fecha El tipo de datos en el rango
3. Escriba un comentario en el cuadro de texto de comentarios que le ayude a usted o a otros a saber para qué sirve este nombre de rango.
4. En el cuadro Refiere a, escriba la fórmula OFFSET que calcula el rango de datos de Fecha.
=OFFSET(wrksht!$B$7,0,0,COUNTA(wrksht!$B$7:$B$16),1)
5. Haga clic en Ok.
Con esta fórmula OFFSET puede introducir fechas adicionales en cualquier lugar del rango B7:B16 siempre y cuando las nuevas fechas estén al final de la antigua lista de fechas sin celdas en blanco entre las fechas.
Ahora, repita el mismo proceso para crear nombres de rangos para los datos de Ventas y Unidades.
Los nombres y fórmulas que añadirás son,
Nombre del rango de datos se refiere a
Ventas rngVSales =OFFSET(wrksht!$C$7,0,0,COUNTA(wrksht!$C$7:$C$16),1)
Unidades rngVUnits =OFFSET(wrksht!$D$7,0,0,COUNTA(wrksht!$D$7:$D$16),1)
Haga clic aquí para saber cómo OFFSET calcula el tamaño del nombre del rango dinámico. Este artículo también le da consejos sobre la comprobación de los nombres de rango dinámico.
Haga clic aquí para aprender el error que nunca quiere cometer con OFFSET. Haga clic para descargar el archivo de ejemplo del tutorial sobre el cambio de tamaño dinámico de los gráficos mediante OFFSET y los nombres de rango. El ejemplo incluye datos horizontales y verticales.
Inserción de un nombre de rango en la fórmula de fecha del gráfico
Ahora puede hacer que sus gráficos sean dinámicos insertando los nombres de rango que ha creado en las fórmulas que utilizan los gráficos para referenciar los datos. No se preocupe. Esto es muy sencillo. De hecho, aprender a hacer esto te da habilidades místicas y el poder de doblar cualquier gráfico a tu voluntad. (También te permite arreglar los gráficos que se estropean.)
Arriba has creado tres nombres de rangos dinámicos que hacen referencia a los datos que utilizará el gráfico,
rngVDate
rngVSales
rngVUnits
Lo siguiente es reemplazar las referencias de celdas utilizadas por el gráfico con los nombres de rangos dinámicos que has creado,
1. Seleccione el gráfico haciendo clic en su borde.
2. En la pestaña Diseño de herramientas de gráfico, en el grupo Datos, haga clic en la herramienta Seleccionar datos para mostrar el cuadro de diálogo Seleccionar fuente de datos.
El cuadro de diálogo Seleccionar fuente de datos le ofrece un control mágico sobre los datos que utiliza un gráfico.
3. Bajo las Etiquetas de eje horizontal (categoría), haga clic en Editar para mostrar el cuadro Etiquetas de eje. Es difícil ver la fórmula en este cuadro de diálogo, así que haga clic en el icono del selector a la derecha de la referencia de la celda. Esto muestra un cuadro de Etiquetas de Eje más amplio en el que puede hacer referencia a la fórmula. CUIDADO antes de editar!
El cuadro de Etiquetas de Eje muestra la referencia del Eje Horizontal.
4. ¡CUIDADO! Antes de intentar editar la fórmula, haz clic dentro de la fórmula y pulsa la tecla Editar, F2, para poder editar la fórmula. Pulsa la tecla Fin para ir al final de la fórmula. ¡Sustituye las referencias de las celdas después del signo ! por el nombre del rango, como se muestra en esta imagen.
¡Tu fórmula debería tener ahora el siguiente aspecto,
=’Redimensionar dinámicamente los gráficos a los datos.xlsx’!rngVDate
El cuadro de Etiquetas de Eje tiene este aspecto después de pulsar F2 y editar la fórmula para incluir el nombre del rango de fechas.
TIP: Si comete un error al editar la fórmula, pulse Ctrl+Z (Deshacer), para volver atrás.
5. Haga clic en Aceptar para volver al cuadro de diálogo Seleccionar fuente de datos y poder añadir los nombres de Ventas y Unidades.
Inserción de un nombre de rango en la fórmula de Ventas y Unidades del gráfico
Ahora, sustituya las fórmulas del eje de Ventas y Unidades por nombres de rango utilizando prácticamente el mismo proceso.
1. En el cuadro de diálogo Seleccionar fuente de datos, haga clic en Ventas y en el botón Editar para que aparezca el cuadro de diálogo Editar serie.
Reemplace la referencia de la celda de Datos de ventas con su nombre de rango.
2. Haga clic en el icono del selector a la derecha de la fórmula Valor de la serie que hace referencia a la serie Ventas.
3. Haga clic dentro de la fórmula, presione F2 para editar y cambie las referencias de celda para los datos de Ventas por el nombre del rango, rngVSales.
El gráfico ahora utiliza el nombre del rango dinámico para referenciar los datos de Ventas.