Crear rangos dinámicos en Excel

En esta oportunidad explicaremos cómo crear rangos dinámicos en Excel, ya sea un único rango o múltiples rangos dinámicos en una sola hoja.

Rangos dinámicos en Excel.

Un rango dinámico es un rango que cambia según se amplíe o se reduzca, ya porque se agregan datos o porque se eliminan.

Recordemos que las operaciones en Excel se hacen sobre rangos, como por ejemplo sumar la columna A desde la fila 1 hasta la fila 100, así que tenemos el rango A1:A100, que es un rango fijo.

Pero si agregamos o eliminamos filas ese rango cambia, por lo que debemos actualizar la formula para adaptarla a ese nuevo rango, cambio que no es necesarios si trabajamos con rangos dinámicos que se ajustan automáticamente en la medida que se incluyen o eliminan datos.

Nombres de rango.

Otro aspecto relevante y que va de la mano con los rangos dinámicos, son los nombres de rango, los que reemplazan la referencia a las filas y columnas que conforman el rango.

Supongamos que tenemos una lista de empleados en el rango A1:A20, pero si eliminamos o agregamos más empleados, tenemos que ajustar manualmente se rengo para que se ajuste a la nueva cantidad de datos.

Supongamos que debemos la siguiente fórmula para buscar empleados:

=BUSCARV("Pedro";A1:B20;2)

Debemos buscar a Pedro en el rango de empleados que hay de A1:B20. ¿Pero qué pasa si agregamos 10 empleados?, pues tenderemos que adaptar la fórmula así:

=BUSCARV("Pedro";A1:B30;2)

Y cada vez que se agreguen o eliminen empleados hay que cambiar el rango indicado de la fórmula.

Esto lo evitamos si en lugar de colocar el rango en la formula, le asignamos un nombre de rango a las celdas que contienen los datos de interés, y le podemos colocar como nombre Empleados, y de esa forma nos queda así:

=BUSCARV("Pedro";Empleados;2)

Ahora hacemos que ese rango sea dinámico como a continuación se explica.

Crear nombres para rangos dinámicos.

Para crear un rango dinámico, formulamos las referencias y con ello definimos un nombre para nuestro rango dinámico.

Para ello, en la barra de herramientas buscamos la opción “Fórmulas y luego “Asignar un nombre al rango”.

Le damos el nombre a nuestro rango y seleccionamos la hoja del libro a la que pertenece ese rango.

Seguidamente introducimos la fórmula que definirá nuestro rango dinámico.

Si queremos un rango dinámico infinito hacia abajo y hacia la derecha, esto es que se puedan agregar tantas filas como columnas, la fórmula a introducir será:

=DESREF($A$1;0;0;CONTARA($A:$A);CONTARA($1:$1))

Con ello le estamos diciendo a Excel que defina el rango considerando todas las celdas con valores que estén por debajo de la celda A1 y desde la columna A hacia la derecha.

Pero seguramente necesitamos un rango dinámico  limitado, como por ejemplo que ocupe únicamente la columna A desde la fila 2. En ese caso la  fórmula será:

=DESREF($A$1;1;0;CONTARA($A:$A))

Para la columna B la fórmula será:

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

Si queremos un rango dinámico que ocupe las columnas  E y F, que vaya de a fila 12 a la 100,  la fórmula será:

=DESREF($E$12;0;0; CONTARA($E1:$E$100 ); 2)

Ejemplo de rangos dinámicos.

Hemos desarrollado un pequeño ejemplo con rangos dinámicos en Excel con fines ilustrativos. Descargar anexo