Hipervínculos en Excel - referencias absolutas y relativas

El tema de los hipervínculos en Excel sigue siendo uno de los más consultados por mis lectores. Las preguntas más frecuentes tienen que ver con la "dirección".

La dirección es la referencia al archivo, objeto o páginas Web que se abrirá al pulsar el enlace. Técnicamente la referencia es un URL (Uniform Resource Locator).

El URL puede apuntar tanto a una página WEB como a un archivo.

De acuerdo con Office Online los hipervínculos, es decir la referencia URL, pueden ser absolutos o relativos.

Un URL absoluto es aquel que contiene la dirección completa que se compone de cuatro partes: el protocolo (http, ftp, file), la ubicación física (el servidor Web, o el lugar en la red o en la máquina), el path y el nombre del archivo.

Un URL relativo es aquel en el que falta alguna o algunas de las partes mencionadas.

En Excel los hipervínculos son, por defecto, relativos al cuaderno que los contiene.

Como ejemplo supongamos que tenemos un cuaderno con hipervínculos a imágenes de productos. El cuaderno está guardado en la dirección D:\Catalogo. Las imágenes están en el directorio D:\Catalogo\Productos

Abrimos un cuaderno nuevo, agregamos la lista de productos (en nuestro ejemplo los números de catálogo) y creamos el hipervínculo para el primero de la lista

Nótese que la dirección es absoluta. Esto se debe a que aún no hemos guardado el cuaderno. Lo mismo puede apreciarse apuntando al enlace

Después de guardar el cuaderno, la dirección se vuelve relativa

Ahora copiamos D:\Catalogo a un CD (D:\Catalogo incluye, obviamente, la carpeta Productos). De esta manera podremos entregar copias del catálogo a nuestros clientes.

Cuando nuestro cliente abra el cuaderno, los enlaces apuntarán a la ubicación del cuaderno (en nuestro ejemplo, E :\)

La dirección ha cambiado en relación a la ubicación del cuaderno.

Si queremos forzar dirección del hipervínculo podemos hacer lo siguiente:

Abrimos el menú Propiedades del cuaderno
En Excel Clásico: Archivo-Propiedades-Resumen;
En Excel 2007: botón del Office-Preparar-Propiedades-Propiedades Avanzadas
En la ventanilla Base del Hipervínculo ponemos la referencia deseada, de manera.

Rangos dinámicos y funciones volátiles

En los comentarios de la nota anterior surgió el asunto de los rangos dinámicos y las funciones volátiles. A pesar de que estos temas han sido tratados de alguna manera en este blog, haremos una reseña en esta nota para aquellos lectores que no conocen el tema.

Empecemos por lo básico: ¿qué es un rango dinámico? El rango A1:A5 en la fórmula =SUMA(A1:A5) es estático.

Si agregamos una fila dentro del rango, Excel lo ajustará automáticamente convirtiendo la fórmula a =SUMA(A1:A6).

Pero si insertamos la nueva fila entre la fila 5 y 6 de nuestro ejemplo, Excel no ajustará el rango de la fórmula

Para superar este problema podemos convertir el rango A1:A5 de la fórmula en dinámico usando rangos nominados ("nombres"). Seleccionamos la última celda del rango (A5 en nuestro ejemplo) y usamos como referencia en un nombre que llamaremos "ultima_celda" (sin acentos y sin espacios en blanco que nos aceptables en nombres) la celda inmediata superior (A4 en nuestro ejemplo)

Hay que prestar atención a que la referencia es relativa (la dirección de la celda no incluye las "anclas" $)

Ahora podemos escribir nuestra fórmula de esta manera:

Otro tipo de situación se presenta cuando queremos que el rango de una lista se extienda o contraiga de acuerdo a la cantidad de elementos (celdas ocupadas) que la componen. Un ejemplo es cuando creamos una lista de valores para una lista desplegable con validación de datos.

La receta clásica es usar la función DESREF como en este ejemplo

DESREF crea un rango empezando en el "ancla" (la celda A1 en nuestro caso) y usando la función CONTARA para determinar cuántas filas están incluidas en él.

Para crear listas desplegables dependientes usamos la función INDIRECTO.

Aquí comienza la cuestión con las funciones volátiles. ¿Qué es una función volátil?

No todas las fórmulas de una hoja son recalculadas cada vez que introducimos un cambio. Excel determina qué celdas serán afectadas por el cambio y cuáles no y sólo recalcula las primeras.

Sin embargo hay algunas funciones que son recalculadas con cualquier cambio en la hoja. Estas son las funciones volátiles. Por eso se suele recomendar como buena práctica evitar, en la medida de lo posible el uso de estas funciones.

Entre las funciones volátiles de Excel se cuentan, justamente, DESREF e INDIRECTO.

Podemos construir rangos dinámicos sin usar DESREF e INDIRECTO con estas técnicas:

1 – si usamos la versión 2003 de Excel o posteriores, la mejor alternativa en mi opinión es usar Listas (Excel 2003, Tablas en Excel 2007/2010). No nos complicamos la vida con fórmulas complejas que a veces pueden producir errores inadvertidos y le dejamos que todo el trabajo lo haga Excel por detrás de las bambalinas.

2 – Usar una combinación de INDICE y CONTARA:

=Hoja1!$A$1:INDICE(Hoja1!$A:$A;CONTARA(Hoja1!$A:$A))

Finalicemos la nota diciendo que la influencia de las funciones volátiles en una hoja de Excel será significativa sólo cuando se haga uso intensivo de ellas, algo similar a lo que ocurre con las fórmulas matriciales. Por eso, y como en muchos otros aspectos de la vida, las cuestiones no son absolutas. Usar funciones volátiles no es ni buena ni mala práctica; todo es una cuestión de cantidad.

Listas desplegables dependientes – rangos en filas

El tema de crear listas desplegables dependientes ha sido tratado abundantemente en este blog. Ni por casualidad ni por capricho, sino por ser uno de los temas más consultados por mis lectores.

Un lector, cuyo mail he borrado por error y espero que lea esta nota en algún momento, me comentaba que no lograba crear listas dependientes cuando los valores estaban ubicados en un rango horizontal (filas) en lugar de vertical (columnas) tal como muestro en el ejemplo de esta nota.

Como en el caso anterior, el problema reside en el hecho que la función INDIRECTO sólo funciona con texto.

Cuando queremos crear una lista de valores desplegable en Excel, la opción Lista de validación de datos es la alternativa más fácil.

Veamos el caso. Los nombres de los agentes de cada zona de una empresa aparecen en esta hoja en rangos horizontales

Empezamos por crear el nombre que contiene el rango vertical de las zonas

Ahora asignamos la primer celda de la cada celda del la columna C al nombre de cada zona. Es decir

Norte = $C$3
Sur=$C$4
Este=$C$5
Oeste=$C$6

En la celda B9 de la misma hoja ponemos la lista desplegable de las zonas

En la celda B10 creamos la lista desplegable dependiente de los agentes con esta fórmula

=DESREF(INDIRECTO($B$9),0,0,1,CONTARA(INDIRECTO(FILA(INDIRECTO($B$9))&":"&FILA(INDIRECTO($B$9))))-1)

Como ven, combinamos INDIRECTO y FILA para "construir" el texto que representa el rango de los nombres de cada zona, en forma dinámica.
Esta solución tiene un grave problema: si ponemos las listas desplegables en otra hoja, la fórmula no funcionará correctamente. Aquí nos enfrentamos con dos problemas:

1 – tenemos que crear una referencia a la hoja que contiene los valores de las listas (en nuestro ejemplo, la Hoja1)

2 – en Validación de datos no podemos usar referencias a hojas remotas directamente; tenemos que "encapsularlas" en un nombre.

Por estos motivos creamos el nombre "lista_dependientes" que se refiere a esta fórmula:

=DESREF(INDIRECTO(Hoja2!$B$3),0,0,1,CONTARA(INDIRECTO("Hoja1!"&FILA(INDIRECTO(B3))&":"&FILA(INDIRECTO(B3))))-1)

En esta fórmula creamos el texto de la referencia en la función CONTARA poniendo en forma explícita el nombre de la hoja que contiene las listas de valores (Hoja1).

Ahora podemos ser fieles al principio de separar los datos de los reportes y usar la lista dependiente en la Hoja2

El archivo del ejemplo se puede descargar aquí

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.