Mmaresma/ agosto 3, 2016/ Como se hace en Excel

Excel nos permite poner nombres a los rangos de celdas de manera que los podamos identificar adecuadamente al usarlos en nuestras fórmulas, pero esto está limitado a un rango específico.

En este artículo, voy a mostraros como realizarlo para que sea dinámico.

Montar rangos dinámicos con DESREF

Para lograr este objetivo utilizaremos la función DESREF que nos permite crear una referencia a un rango. Esta es la sintaxis de la función:

DESREF(ref; filas; columnas; [alto]; [ancho])

La sintaxis de la función DESREF nos indica que el primer argumento debe ser la celda “inicial” sobre la cual se basará la referencia.

El segundo y tercer argumento de la función nos permiten especificar cuantas filas y columnas nos moveremos de la celda “inicial”.

En este ejemplo no queremos movernos de esa celda, así que estos parámetros serán siempre cero.

El cuarto y quinto argumento son el alto y ancho de la referencia que queremos crear y aquí es donde viene la parte interesante porque queremos decir a Excel que deseamos todas las celdas que tienen un contenido, o que es lo mismo, que contengan algún dato.

Para lograr nuestro cometido debemos utilizar la función CONTARA, la cual nos ayuda a contar las celdas que no están vacías. Para “contar ” las filas que no están vacías en la columna A, utilizo la siguiente función:

=CONTARA(A:A)

Rangos_dinamicos_1

Y para contar las columnas que no están vacías en la fila 11:

=CONTARA(11:11)

Rangos_dinamicos_2

Con los parámetros ya definidos podemos empezar a montar la fórmula que utilizaremos con la función DESREF, de la siguiente manera:

Suponiendo que los datos se encuentran en la Hoja1, que los datos dinámicos van a estar situados en la columna A:

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

NOTA: Que añadamos el nombre de la Hoja a un rango es importante, debido a que si utilizamos el nombre en otra hoja, Excel sabrá dónde ir a buscarla.

Esta fórmula siempre nos devolverá el rango que incluye las celdas que tienen un valor y que son adyacentes (hacía abajo) a la celda A1.

Pero… ¿Como lo añadimos?

Tal y como se explica en el artículo “usar nombres para celdas o rangos“, seleccionamos en la ficha “Fórmulas” la opción “Administrador de Nombres”.

Rangos_dinamicos_3

Al abrirse la ventana, seleccionamos el botón “Nuevo” y nos aparecerá la siguiente ventana:

Rangos_dinamicos_4

En el “Nombre” ponemos algo descriptivo para que nos acordemos de como utilizarlo después, en el ámbito dejamos establecido a nivel “Libro”, en el comentario… pues eso, un comentario para vosotros y donde “hace referencia a:” utilizamos la función completa, es decir:

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

Nota: En esta ocasión, le decimos que solo queremos una columna, la primera, de ahí poner “CONTARA(Hoja1!$1:$1)” para que solo se quede con la primera columna de la fila 1.

Y le damos al botón “Aceptar”. Debería quedarnos así:

Rangos_dinamicos_5

A partir de este momento, ya podemos usar el nombre “MiRangoDinamico” en cualquier función, sabiendo además, que todo lo que se incluya será tenido en cuenta, como por ejemplo en una suma:

Rangos_dinamicos_6

Espero que os haya gustado, 🙂

 

Comentario

Comentario

Share this Post