Cómo crear Rangos Dinámicos para Gráficos en Excel con DESREF y TRIMRANGE

Crear gráficos en Excel que se actualicen automáticamente puede hacer la diferencia entre reportes estáticos y verdaderamente interactivos. En este post, aprenderás cómo utilizar fórmulas para crear rangos dinámicos para gráficos de manera eficiente. Hasta ahora, la función DESREF ha sido clave para lograr esta actualización automática, pero la llegada de TRIMRANGE y el nuevo operador de rango .:. lo simplifican de manera considerable.

La NUEVA forma de crear Rangos Dinámicos para GRÁFICOS en Excel (DESREF ó TRIMRANGE)

Suscríbete para más tutoriales

Descarga el archivo para practicar

Escribe tu correo electrónico para recibir gratis el archivo para practicar.

*Al enviar mi correo electrónico, acepto recibir noticias y ofertas. Puedo darme de baja en cualquier momento.

1. Diferencias entre Gráficos Normales y Gráficos Dinámicos

En Excel, podemos elegir entre gráficos normales y gráficos dinámicos. Los gráficos dinámicos dependen de tablas dinámicas, mientras que los gráficos normales requieren de un rango de celdas específico. Sin embargo, los gráficos normales no se actualizan automáticamente al agregar nuevos datos a menos que el rango esté diseñado para ser dinámico. Aquí entra en juego la fórmula DESREF junto a CONTARA, pero pronto será más sencillo gracias a TRIMRANGE.

2. Acceso a la Función TRIMRANGE mediante el Programa Insider

TRIMRANGE es una función actualmente en vista previa, disponible solo para usuarios del programa Insider de Microsoft. Si quieres unirte para probar las últimas funciones:

• Abre Excel, busca “Insider” en la barra de búsqueda y selecciona la opción para registrarte.
• Sigue las instrucciones para unirte al canal beta y así obtener acceso anticipado a funciones como TRIMRANGE y el operador de rango .:.

3. Insertar un Gráfico Básico de Columnas

Comencemos insertando un gráfico de columnas estándar. Selecciona un rango de datos, ve a la pestaña Insertar y elige un gráfico de columnas. Este gráfico dependerá de un rango fijo que no se actualiza automáticamente al agregar nuevos datos. Para solucionar esto, aplicaremos un rango dinámico.

4. Creación de un Rango Dinámico con DESREF y CONTARA

Para hacer que el gráfico se actualice automáticamente, puedes crear un rango dinámico usando DESREF y CONTARA:

=DESREF(A2, 0, 0, CONTARA(A:A) - 1, 1)

Esta fórmula toma A2 como referencia y utiliza CONTARA para contar los valores en la columna A, creando un rango que crece a medida que se agregan nuevos datos.

5. Asignar Nombres Definidos para Usar en el Gráfico

Para usar este rango dinámico en un gráfico, asignaremos nombres definidos en Excel:

• Ve a Fórmulas > Administrador de Nombres, y crea un nombre como “Productos” o “Ventas”.
• Usa la fórmula DESREF para definir el rango y asigna estos nombres a las categorías o series de datos en tu gráfico.

6. Usar TRIMRANGE para Crear un Rango Dinámico

La nueva función TRIMRANGE es una alternativa que hace el proceso más sencillo:

Copy code=TRIMRANGE(A2:A26)

TRIMRANGE ajusta automáticamente el rango seleccionado para que incluya solo las celdas con datos, ignorando celdas vacías. Esta es una forma más rápida y simple de crear rangos dinámicos, sin la necesidad de combinar varias funciones.

7. El Nuevo Operador de Rango ...

Además de TRIMRANGE, Excel ha introducido un nuevo operador de rango que permite definir rangos dinámicos fácilmente:

=A2.:.A26

Este operador también ajusta el rango para incluir únicamente celdas con datos, funcionando de manera similar a TRIMRANGE pero sin una fórmula.

8. Crear Nombres Definidos con TRIMRANGE y el Operador de Rango

Finalmente, asigna nombres definidos a los rangos creados con TRIMRANGE o el operador de rango. Estos nombres definidos pueden ser usados directamente en gráficos, sin la necesidad de DESREF ni CONTARA.

9. Agregar Rangos Dinámicos a los Gráficos

Con tus nombres definidos listos, puedes usarlos en un gráfico:

• Ve a Seleccionar datos > Agregar serie y usa el nombre definido, como “Ventas” o “Productos”.
• Esto permite que el gráfico se actualice automáticamente a medida que los datos cambian.

Certifícate en Excel

Tienes experiencia en Excel y quieres mejorar tus oportunidades laborales? Obtén la Certificación Excel Expert avalada por Microsoft.