Un rango de Excel es un conjunto de celdas adyacentes al cual podemos hacer referencia en nuestras fórmulas. Sin embargo, cuando nuestros datos aumentan o disminuyen, es necesario editar las fórmulas para introducir el nuevo rango. La solución a este inconveniente son los rangos dinámicos ya que hacen que nuestras fórmulas se adapten automáticamente a la cantidad de datos en la hoja.
¿Qué es un rango dinámico en Excel?
Cuando hablamos de rangos dinámicos en Excel nos referimos a una técnica especial que nos permitirá hacer referencia a un conjunto de celdas que se ajustará automáticamente al insertar o borrar datos por lo que siempre tendremos el cálculo adecuado sin necesidad de editar nuestras fórmulas.
Esta técnica implica el uso de dos funciones de Excel: DESREF y CONTARA. La función DESREF nos permite crear una referencia a un rango de celdas indicando la cantidad de filas y columnas a partir de una celda específica. La función CONTARA cuenta el número de celdas no vacías de un rango. Para entender mejor estas funciones haremos algunos ejemplos.
La función CONTARA
La función CONTARA nos devuelve la cantidad de celdas no vacías contenidas en un rango. Por ejemplo, en la siguiente imagen puedes observar que he introducido la función CONTARA con el rango A1:C5 como su argumento.
El rango A1:C5 está formado por 15 celdas, sin embargo la función CONTARA nos ayuda a saber que de todas esas celdas solamente 12 contienen un valor. Esta funcionalidad nos será de gran ayuda al momento de crear rangos dinámicos en Excel ya que utilizaremos esta función para contar las celdas que tienen valores dentro de una fila o de una columna. Por ejemplo, la siguiente fórmula nos ayuda a saber la cantidad de filas con datos dentro de la columna A:
=CONTARA($A:$A)
Observa el resultado de aplicar esta fórmula sobre los mismos datos del ejemplo anterior:
De esta manera podemos saber que nuestro rango tiene 4 filas con datos. Por otro lado, si queremos conocer las columnas de nuestro rango podemos utilizar la siguiente fórmula:
=CONTARA($1:$1)
Esta fórmula contará las celdas no vacías dentro de la fila 1 de nuestra hoja y al utilizarla con los datos de ejemplo tendremos el siguiente resultado.
En seguida combinaremos la función CONTARA con la función DESREF para crear rangos dinámicos en Excel.
Rangos dinámicos con DESREF
La función DESREF nos permite crear una referencia con tan solo indicar la celda donde comenzará el rango y posteriormente el número de filas y columnas que conforman dicho rango. Para nuestros datos de ejemplo, la celda inicial del rango se encuentra en la celda A1 por lo que comenzamos nuestra fórmula de la siguiente manera:
=DESREF($A$1,
El segundo y tercer argumento de la función DESREF nos sirven para indicar algún movimiento a partir de la celda inicial pero no son necesarios en la creación de rangos dinámicos así que los podemos indicar siempre como cero.
=DESREF($A$1, 0, 0,
Los argumentos importantes en la creación de rangos dinámicos son el cuarto y el quinto de la función DESREF ya que nos permiten indicar la cantidad de filas y columnas que deseamos incluir a partir de la celda inicial. El número de filas lo podemos obtener con la función CONTARA de la siguiente manera:
=DESREF($A$1, 0, 0, CONTARA($A:$A),
Y para obtener el número de columnas volvemos a utilizar la función CONTARA de la siguiente manera:
=DESREF($A$1, 0, 0, CONTARA($A:$A), CONTARA($1:$1))
Si introducimos esta fórmula por sí sola en una celda obtendrás un error #¡VALOR! ya que nuestra fórmula no devuelve un valor sino una referencia. Pero si colocamos la fórmula anterior dentro de la función SUMA le estaremos pidiendo a Excel que sume todos los valores del rango devuelto por la función DESREF.
=SUMA(DESREF($A$1, 0, 0, CONTARA($A:$A), CONTARA($1:$1)))
Al momento de ingresar esta fórmula obtendremos la suma de los valores en todo el rango:
Pero lo mejor de esta fórmula es que sumará automáticamente los datos adicionales que introduzcamos. Por ejemplo, en la fila 5 colocaré los valores 2, 4 y 6 y la suma se actualizará automáticamente:
Podrás introducir más datos hacia abajo y la suma siempre se actualizará automáticamente. De igual manera podrás insertar nuevas columnas de datos y de igual manera serán consideradas en el cálculo sin tener que modificar la fórmula.
Rango dinámico nombrado
Una manera de mejorar la administración de los rangos dinámicos en Excel es asignando un nombre a la referencia devuelta por la función DESREF. Para crear un nombre debemos ir a la ficha Fórmulas y pulsar el botón Asignar nombre que se encuentra dentro del grupo Nombres definidos.
Como resultado se mostrará un cuadro de diálogo que nos permitirá crear un nuevo Nombre y como referencia colocaremos la misma función DESREF creada en nuestro ejemplo anterior.
Al pulsar el botón Aceptar se creará el nombre MiRangoDinamico que hará referencia al mismo rango dinámico del ejemplo anterior. La ventaja de utilizar este método será que ya no tendremos que ingresar la función DESREF cada vez que queramos utilizar nuestro rango dinámico sino que será suficiente con introducir el nombre recién creado.
Para futuras modificaciones del rango dinámico recién nombrado podrás utilizar el Administrador de nombres de Excel que te permitirá modificar o eliminar dicho nombre.
Otro ejemplo de rangos dinámicos
El rango dinámico creado en el ejemplo anterior considera la adición de nuevas filas y de nuevas columnas pero hay ocasiones en las que será suficiente con vigilar solamente la adición de nuevas filas. En ese caso nuestra fórmula se simplifica ya que solo tendremos que utilizar la función CONTARA una sola vez.
En el siguiente ejemplo tenemos una lista de personas con sus respectivas edades y nos interesa tener una fórmula que obtenga el promedio de dichas edades conforme se agregan nuevos datos.
Ya que las edades que deseamos considerar en el cálculo se encuentran en la columna B, podemos utilizar la función DESREF de la siguiente manera:
=DESREF($B$1, 0, 0, CONTARA($B:$B))
Observa que en este caso he omitido el quinto argumento de la función DESREF porque no me interesa considerar nuevas columnas sino solamente nuevas filas y eso es precisamente lo que hace el cuarto argumento donde he colocado la función CONTARA. Ahora podemos crear un nuevo nombre utilizando la fórmula anterior.
Ya con el nombre creado podremos utilizarlo en la fórmula de la celda E1 para obtener el promedio de las edades de la siguiente manera:
Podemos aumentar la lista de edades y la fórmula calculará automáticamente el promedio considerando los nuevos datos ingresados.
Rangos dinámicos con encabezados
Hasta ahora nuestros ejemplos han sido con rangos de celdas que no tienen encabezados, pero es muy común que los datos en Excel tengan títulos en la primera fila por lo que tendremos que modificar un poco la manera de crear nuestro rango dinámico. En la siguiente imagen puedes ver los mismos datos del ejemplo anterior pero en esta ocasión la fila 1 tiene encabezados para cada columna.
La fórmula que utilizaremos para definir nuestro rango dinámico tendrá algunas modificaciones y será la siguiente:
=DESREF($B$2, 0, 0, CONTARA($B:$B)-1)
En primer lugar nuestro rango dinámico comenzará en la celda B2 y además haremos una resta del valor 1 al cuarto argumento de la función DESREF. Esto se debe a que la función CONTARA devolverá el valor 9 como la cantidad de celdas con datos en la columna B, y eso incluirá a la celda B1, pero necesitamos descartar el encabezado de la columna y por eso hacemos la resta. Si definimos el nombre Edades_2 utilizando esta fórmula, entonces obtendré el resultado adecuado al calcular el promedio.
Con un poco de práctica estarás listo para crear tus propios rangos dinámicos en Excel y sacar el máximo provecho de esta técnica que seguramente traerá grandes beneficios al momento de trabajar con tus datos. Descarga el libro de trabajo utilizado en esta lección y comienza a dominar el uso de las funciones DESREF y CONTARA para la creación de este tipo de rangos.