Éste es un tema un tanto complejo, pero sin duda muy interesante y útil de Excel.
En un post anterior vimos la posibilidad de nombrar rangos (pulsa aquí, si no lo recuerdas o quieres leerlo). En aquella ocasión lo hicimos de una forma rápida y sencilla, pero ¿qué pasa si una vez que has nombrado el rango incorporas o borras nuevos elementos?. Por ejemplo, supón que tienes una lista desplegable como la del ejemplo que hicimos aquí, que se nutre de un rango previamente nombrado y, en algún momento, necesitas incorporar una nueva ciudad. Parece lógico que la lista puede aumentar e incluso disminuir según las necesidades. Tal y como lo tenemos planteado, la solución pasaría por tener que estar pendiente de estas modificaciones y cambiar el nombre asignándolo al nuevo rango.
Sin embargo, es posible crear lo que denominamos rangos dinámicos. Por medio de estos rangos dinámicos conseguimos que sea el propio programa el que se ocupe de actualizar el número de celdas que pertenecen a un nombre de rango, de forma que si incorporamos un elemento nuevo, o lo borramos, el nombre sigue siendo válido, ya que incorpora de forma automática los cambios.
No es sencillo, aunque es cierto que una vez realizados correctamente, nos evitará trabajo, tener que estar pendientes de actualizar los nombres de rango y algún quebradero de cabeza. Vamos a analizarlo por partes. Para comenzar usaremos dos funciones que son las que detallaremos a continuación. Vamos a verlas por separado primero, de forma independiente y luego la incorporaremos a un ejemplo de rango dinámico, de forma que se aplique al caso concreto:
DESREF: Aunque tiene otras posibilidades, ahora nos interesa saber que nos devuelve un rango de celdas. La sintaxis de la función aplicable al ejemplo es de la forma siguiente:
DESREF(ref;filas;columnas;alto;ancho)
Entre los parénteris debemos escribir los argumentos siguientes:
- ref: es una celda de referencia, por así decir, un punto desde el que partimos.
- filas: hay que indicar un número, será el número de filas que se desplaza hacia abajo o hacia arriba (si el número es negativo) desde la celda indicada en el punto anterior (ref).
- columnas: similar al anterior. Se indica un número, éste es el número de columnas que se desplazará hacia la derecha o izquierda (si el número es negativo) desde la celda indicada en el primer punto (ref).
- alto: también pondremos un número e indica el número de filas a incluir en el rango. Al contrario que los anteriores, no puede ser un número negativo.
- ancho: similar al punto anterior. Escribiremos un número que indica el número de columnas que se incluye en el rango. No puede ser un número negativo.
Veamos un par de ejemplos para intentar aclarar este galimatías. Supongamos que tenemos lo siguiente en una hoja de cálculo:
La formula DESREF(A1;1;0;2;2) devolvería el rango señalado con un cuadro rojo, ya que partiendo de A1, nos desplazamos una fila hacia abajo, 0 columnas (es decir seguimos en la A y tomamos un rango con 2 filas y dos columnas. Para hacer la prueba, escribe el ejemplo y en, por ejemplo A6 escribe la fórmula siguiente:
=SUMA(DESREF(A1;1;0;2;2))
El resultado es 50, que es la suma de lo que contiene el rango que se señala en rojo.
De igual manera podrás comprobar que la fórmula siguiente: DESREF(A1;1;2;3;1), se refiere al rango que está señalado en la imagen en color verde.
CONTARA: Esta función es mucho más sencilla que la anterior, pues lo que hace es contar el número de celdas ocupadas en un rango. Es decir, dice las celdas que hay no vacías. Si en el ejemplo de la imagen ponemos la fórmula =CONTARA(B4:C6), por ejemplo en la celda B6, el resultado obtenido es 2, pues dos son las celdas en el rango B4:C6 que no están vacías.
Por último, aparte de estas dos funciones, necesitamos saber cómo nombrar rangos desde la opción de menú. Para esto pulsaremos en la pestaña Fórmulas y luego busca un botón que se llama Asignar nombre a un rango. Cuando pulses aparece un menú pidiéndote los datos para el nuevo nombre de rango
Imaginemos el siguiente ejemplo:
A la izquierda, en el rango A2:A6 hemos escrito una serie de ciudades que queremos convertir en rango dinámico. Ahora tenemos cinco ciudades, pero si mañana incorporo otra ciudad, queremos que Excel lo tome correctamente de forma automática. A la derecha vemos la ventana correspondiente a la creación de un nombre de rango. En dicha ventana los datos a completar son los siguientes:
Nombre: Es el nombre que le asignamos al rango y será con el que nos referiremos a él. Es aconsejable usar nombres con una cierta coherencia y que nos resulten fáciles de recordar. En el caso de la imagen de ejemplo, sería adecuado por ejemplo llamarle ciudades. Será este nombre el que usemos. Llamamos ciudades al rango A2:A6.
Ámbito: Se refire a en qué partes estará disponible el nombre de rango. Por ejemplo, si ponemos en todo el libro, estará disponible en todas sus hojas, pero si sólo ponemos una de las hojas, en las demás no podríamos usarlo y por lo tanto sólo se podría usar en las fórmulas de la hoja que hemos puesto como ámbito. En nuestro ejemplo vamos a poner que sea libro.
Comentario: Este campo lo puedes usar para hacer algún tipo de aclaración, pero es opcional y puedes dejarlo en blanco si quieres.
Hace referencia a: Es el rango al que le asignamos el nombre. En condiciones normales aquí pondrías A2:A6, pero ese rango sería estático y no cambiaría de forma automática si añades una ciudad a la lista. En nuestro caso, escribiremos una fórmula que contiene las funciones descritas con anterioridad. Para el ejemplo concreto de la imagen, escribimos la siguiente fórmula en el campo:
=DESREF($A$1;1;0;CONTARA($A:$A);1)
¿Qué significa esta fórmula? Significa que el rango a nombrar es el siguiente: tomando como referencia la celda A1, una fila debajo (observa que en la primera ciudad está en A2), no nos movemos de columna (aparece un 0 en columna), y tomamos tantas filas como no estén vacías (aquí usamos la función contara y como argumento le ponemos la columna A).
Para probar el funcionamiento de nuestro nuevo rango, hemos realizado una lista desplegable tal y como se muestra en el post correspondiente (pulsa aquí para verlo) y en el origen de la lista escribimos la fórmula:
=ciudades
Y si añadimos más ciudades, la lista desplegable lo irá reflejando de forma automática.
Los rangos dinámicos tienen muchas aplicaciones, seguro que con un poco de práctica le encontrarás enseguida muchas más aplicaciones. 🙂
Plantillas, Trucos e ideas para usar los programas Office. 