Cómo hacer referencia a otro libro sin abrirlo: ¡5 PELIGROS!

En esta publicación, analizamos el uso de Excel para hacer referencia a otro libro sin abrirlo. Si bien esto puede parecer una gran idea, debemos tener mucho cuidado. Al utilizar esta técnica muchas cosas pueden salir mal sin que nos demos cuenta.

Exploremos esta área. Lea hasta el final y asegúrese de conocer todos los peligros.

Tabla de contenido

• Alternativa para hacer referencia a otro libro sin abrir
• Cómo hacer referencia a otro libro de trabajo
• Copiar y pegar enlaces
• Vinculación de celdas
• Escribir una fórmula
• Peculiaridades de la vinculación de celdas
• Referenciación absoluta y relativa
• Libros de trabajo guardados en OneDrive o SharePoint
• matrices dinámicas
• Archivos protegidos con contraseña
• rangos con nombre
• Actualizando enlaces
• Actualizaciones automáticas
• Abriendo el archivo
• Ventana Editar enlaces
• Actualización de una sola celda
• Recálculo
• Cálculos con celdas vinculadas
• ¿Los peligros de los libros vinculados?
• Los datos vinculados cambian de ubicación
• Los subenlaces no están actualizados.
• Datos de la última versión guardada
• Excel almacena en caché los datos externos
• ¿Cuándo está bien vincular archivos?
• Conclusión

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre de archivo: 0118 Referencia a otro libro de trabajo.zip

Tener acceso

En todos los ejemplos de la publicación, utilizamos dos libros de trabajo, Closed.xlsx y Open.xlsx .

Alternativa para hacer referencia a otro libro sin abrir

Antes de lanzarnos al tema principal de cómo hacer referencia a otro libro sin abrirlo, me gustaría hablarles de un método alternativo.

Más adelante en esta publicación, hay una lista de posibles dificultades y peligros al hacer referencia a otro libro de trabajo. En mi opinión, una mejor opción es utilizar Power Query.

Power Query es una herramienta que puede extraer datos de un libro de Excel en lugar de vincularlos a celdas individuales.

En la mayoría de las circunstancias, Power Query logra un resultado similar al de la vinculación de celdas, pero evita muchos de los problemas. Para obtener más información sobre Power Query, haga clic aquí: Introducción a Power Query .

Sin embargo, si desea seguir haciendo referencia a otro libro de trabajo a la manera anterior, sigamos adelante.

Cómo hacer referencia a otro libro de trabajo

Hay muchas formas de configurar vínculos a otros libros. En esta sección, exploraremos tres métodos y las diferencias entre usar Excel Desktop y Excel Online.

Copiar y pegar enlaces

El primer método para crear vínculos a libros externos implica copiar y pegar.

1. Abra los libros de trabajo Open.xlsx y Closed.xlsx .
2. En Closed.xlsx , copie las celdas requeridas con Home Copy (o Ctrl +C )
3. En Open.xlsx , seleccione la celda requerida. Luego, pegue los enlaces en las celdas haciendo clic en Inicio Pegar (menú desplegable) Pegar enlace (o Alt, H, V, N )
4. La barra de fórmulas muestra la referencia de celda:
   =[Closed.xlsx]Hoja1!B4

Cuando cerramos el libro Closed.xlsx , la fórmula cambia a una referencia de archivo completa:
='C:UsersmarksDocuments[Closed.xlsx]Sheet1′!B4

Este primer método es sencillo pero requiere que ambos libros estén abiertos para crear los enlaces inicialmente.

Vinculación de celdas

Vincular una celda a otro archivo es como vincular una celda en el mismo libro.

1. Abra los libros de trabajo Open.xlsx y Closed.xlsx .
2. En el libro de trabajo Open.xlsx , seleccione la celda requerida y escriba el símbolo igual ( = )
3. Haga clic en una celda del libro Closed.xlsx . La barra de fórmulas se verá así:
   =[Closed.xlsx]Hoja1!$B$4
4. Presione regresar para aceptar la fórmula.

Al cerrar el libro Closed.xlsx , la fórmula se convierte en una referencia de archivo completa:
='C:UsersmarksDocuments[Closed.xlsx]Sheet1′!$B$4

En Excel Online, cada ventana del navegador es una instancia independiente de la aplicación Excel. Como resultado, no es posible vincular celdas entre libros utilizando este enfoque.

Al igual que el primer método, este también requiere que ambos libros estén abiertos inicialmente.

Escribir una fórmula

Con este tercer método, escribimos una fórmula directamente en el libro de trabajo Open.xlsx y no necesitamos que el libro de trabajo Closed.xlsx esté abierto.

1. En el libro de trabajo Open.xlsx , seleccione la celda requerida.
2. Ingrese una fórmula que tenga la siguiente sintaxis:
   ='filePath[fileName]sheetName'!cellRef
3. Usando nuestros archivos de ejemplo, la fórmula sería:
   ='C:UsersmarksDocuments[Closed.xlsx]Sheet1′!B4

Si no está seguro del nombre de la hoja de trabajo, ingrese cualquier cosa como nombre de la hoja.

• Si hay una hoja en el libro, Excel la usará.
• Si hay varias hojas, Excel muestra una lista para seleccionar.

En la captura de pantalla siguiente, se ingresó Desconocido como nombre de la hoja de trabajo; Excel ha proporcionado una lista de nombres de hojas válidos para seleccionar.

Peculiaridades de la vinculación de celdas

Hay algunas peculiaridades interesantes que debe tener en cuenta:

Referenciación absoluta y relativa

Se aplican las técnicas de referencia estándar.

Por tanto, si estamos trabajando con la siguiente fórmula:
='C:UsersmarksDocuments[Closed.xlsx]Hoja1′!B4

Copiar la fórmula una celda hacia abajo y una celda hacia la derecha cambia la referencia a:
='C:UsersmarksDocuments[Closed.xlsx]Sheet1′!C5

Sin embargo, si la fórmula original tiene referencia absoluta, no importa dónde copiemos la fórmula; siempre hará referencia a la misma celda.
='C:Documentos de marcas de usuario[Cerrado.xlsx]Hoja1′!$B$4

Libros de trabajo guardados en OneDrive o SharePoint

Si el libro Closed.xlsx se guarda en OneDrive o SharePoint, la ruta completa del archivo hará referencia a la ubicación en línea:
='https://exceloffthegrid-my.sharepoint.com/personal/mark/Documents/Documents/[Closed.xlsx] Hoja1′!B4

Incluso si el archivo existe localmente, siempre vuelve a la ruta del archivo en línea.

matrices dinámicas

Las referencias de celdas vinculadas utilizan el motor de matriz dinámica. Por lo tanto, si proporcionamos un rango de celdas, los valores se extenderán a las filas y columnas siguientes.
='C:Documentos de marcas de usuario[Cerrado.xlsx]Hoja1′!B4:E11

Archivos protegidos con contraseña

Si se vincula a un archivo protegido con contraseña, Excel requiere una contraseña para actualizar los vínculos.

rangos con nombre

Es posible vincular a rangos con nombre en lugar de rangos de celdas.

Los siguientes enlaces a un rango con nombre llamado myNameRange :
='C:UsersmarksDocuments[Closed.xlsx]Sheet1′!myNamedRange

Actualizando enlaces

Habiendo creado un enlace, la siguiente pregunta es cómo actualizarlo. Como ocurre con la mayoría de las cosas en Excel, existen varias formas.

Actualizaciones automáticas

Si los libros Open.xlsx y Closed.xlsx están abiertos simultáneamente, los valores se actualizan automáticamente cuando se vuelve a calcular.

Abriendo el archivo

El siguiente mensaje se mostrará al abrir un archivo de Excel que contenga enlaces a otros libros.

Al hacer clic en Actualizar se actualizarán todos los vínculos del libro de trabajo, mientras que No actualizar no recuperará los valores vinculados.

Ventana Editar enlaces

Para actualizar cualquier enlace existente:

• En la cinta, haga clic en Enlaces de edición de datos .
• Se abre el cuadro de diálogo Editar enlaces . Esta ventana también se puede encontrar en Información del archivo Editar enlaces a archivos .
• Seleccione cada enlace y haga clic en Actualizar valores .

También utilizamos el cuadro de diálogo Editar enlaces para cambiar cualquier enlace existente.

Actualización de una sola celda

Se puede actualizar una sola celda entrando y saliendo del modo de edición. Algunos métodos para lograr esto son:

• Haga doble clic en una celda para ingresar al modo de edición, luego haga clic en otra celda para salir.
• Presione F2 para ingresar al modo de edición y presione Enter para salir.
• Haga clic en una celda, haga clic dentro de la barra de fórmulas para ingresar al modo de edición y haga clic para salir.

Recálculo

El vínculo a un libro cerrado se actualiza cuando la celda que contiene el vínculo requiere un nuevo cálculo. La cadena de cálculo en Excel es un concepto complicado. Generalmente, una celda solo se vuelve a calcular si incluye una referencia a una celda o celda anterior que ha cambiado.

El siguiente enlace nunca se actualizará mediante un recálculo estándar ya que no tiene celdas anteriores en el mismo libro.
='C:Documentos de marcas de usuario[Cerrado.xlsx]Hoja1′!A1

Sin embargo, el siguiente enlace volverá a calcular cada vez que cambie la celda B1 (o una celda anterior a B1).
=IF(B1=1,='C:UsersmarksDocuments[Closed.xlsx]Hoja1′!A1,”No hacer nada”)

Cálculos con celdas vinculadas

En la sección anterior, vimos una función SI con una celda vinculada incluida dentro del argumento verdadero. Pero podemos ir más allá y usar un archivo vinculado dentro de otras fórmulas y funciones.

A continuación se muestra la función BUSCAR XL con los argumentos lookup_array y return_array que hacen referencia a celdas en otro libro.

=BUSCARXL(D3,
'C:Documentos de marcas de usuario[Cerrado.xlsx]Hoja1'!$B$4:$B$11,
'C:Documentos de marcas de usuario[Cerrado.xlsx]Hoja1'!$E$4:$E$11)

Pruébelo usted mismo; BUSCARX se comporta igual que de costumbre; no importa si las celdas están en un libro cerrado o abierto.

Muchas otras funciones que manejan matrices también funcionan con referencias de libros externos. Sin embargo, las funciones que requieren un rango, como SUMIFS, no funcionan con referencias de celdas externas.

¿Los peligros de los libros vinculados?

Como se mencionó en la introducción, existen algunos problemas importantes al hacer referencia a otro libro de trabajo.

He escuchado a mucha gente decir: “Mira, me vinculé a este libro para no tener que actualizar una celda. ¿No es genial?”. Pero, desafortunadamente, el entusiasmo pronto muere cuando se dan cuenta de los riesgos.

Estas son las trampas en las que caen muchos usuarios de Excel.

Los datos vinculados cambian de ubicación

Si se hace referencia a una celda dentro de su propio libro, Excel realiza un seguimiento de lo que sucede con esa celda. Por ejemplo, si la celda A1 se arrastra a la celda B1, cualquier celda que haga referencia a A1 se actualizará automáticamente a B1. ¡Perfecto!

Pero esto no es lo que sucede cuando hacemos referencia a otro libro de Excel.

Si un libro está cerrado, no tiene idea de cuándo se mueve una celda de otro libro. Por ejemplo:

• Cuando Open.xlsx está cerrado
• En Closed.xlsx , la celda A1 se arrastra a la celda B1
• Open.xlsx continúa vinculándose a la celda A1 y no cambiará. No tiene idea de que A1 ahora es B1.
• Open.xlsx ahora se vincula a la celda incorrecta – ¡Ups!

El riesgo se puede mitigar ligeramente mediante el uso de rangos con nombre. Sin embargo, si se trata del archivo de un colega, ¿qué le impide eliminar los rangos con nombre o cambiar el nombre de las hojas de trabajo, simplemente porque así lo desea (¡los compañeros de trabajo son así de amables!)

Por lo tanto, vincular a cualquier archivo que no controle estrictamente es muy peligroso.

Los subenlaces no están actualizados.

Cuando algunos usuarios de Excel descubren la capacidad de vincularse a otros libros, se vuelven un poco locos; uniendo todo lo que está a la vista. No pasa mucho tiempo antes de que tengan una telaraña de enlaces entre múltiples libros. Sin embargo, no se dan cuenta de que cuando se actualiza un enlace, no actualiza ningún subenlace contenido en el archivo vinculado.

Veamos un ejemplo. Sugerimos que

• Open.xlsx contiene un enlace a Closed.xlsx .
• Closed.xlsx contiene un enlace a AnotherClosed.xlsx .
• Al abrir Open.xlsx por sí solo, aparece el cuadro de mensaje Actualizar enlaces.
• Al hacer clic en Actualizar, los valores de Closed.xlsx se trasladarán a Open.xlsx .
• No pasa nada con los enlaces a AnotherClosed.xlsx . Esto se debe a que estos enlaces no están actualizados. Por lo tanto, es posible que los valores finales en Open.xlsx no sean correctos.
• Para que todos los enlaces se actualicen correctamente, todos los archivos deben estar abiertos simultáneamente.

Datos de la última versión guardada

Los valores solo se recuperan de la última versión guardada de un libro.

Veamos un ejemplo. Sugerimos que:

• Su colega abre Closed.xlsx y cambia algunos valores pero deja el libro abierto.
• Estás trabajando en Open.xlsx y has actualizado los enlaces.
• Si Closed.xlsx se guarda localmente, los valores vinculados disponibles permanecerán como los antiguos hasta que se guarde Closed.xlsx .
• Si Closed.xlsx se guarda en OneDrive o SharePoint, los nuevos valores estarán disponibles tan pronto como el archivo se sincronice con el servidor.

Esto no es un problema en sí mismo. Sin embargo, no hay ningún mensaje de advertencia que indique que Closed.xlsx esté abierto actualmente, por lo que puede no ser obvio si el archivo está actualizado o no.

Excel almacena en caché los datos externos

Un libro de Excel es un archivo comprimido construido de una manera específica para ser leído por la aplicación Excel. Cuando se crea un enlace, Open.xlsx guarda una copia de los datos de Closed.xlsx .

Analizaremos el formato de archivo Excel más adelante en la publicación, pero por ahora, demostremos que Open.xlsx almacena en caché los datos:

• Cree una BUSCARX (o BUSCARV si lo prefiere) entre los libros de trabajo Open.xlsx y Closed.xlsx .
• Guarde ambos archivos y cierre el libro Closed.xlsx .
• Cambie el nombre de Closed.xlsx a Closed_Backup.xlsx

Las fórmulas en Open.xlsx no cambian; el enlace ya no es válido porque el archivo Closed.xlsx no existe.

Pregunta: ¿Qué sucede si cambiamos el valor de búsqueda?

Respuesta: Debería causar un error… ¡¿verdad?!! ¡No! Se devuelve un valor

Mmm…. Eso no debería funcionar, pero funciona. ¡¡¡Esto significa que puedes seguir recuperando valores de archivos que ya no existen!!! Esto se debe a que Excel mantiene una copia en caché de todos los datos vinculados.

Mientras trabaja con Excel, es posible que vea este mensaje:

“No podemos actualizar algunos de los vínculos de su libro de trabajo en este momento.
Puedes continuar sin actualizar sus valores o editar los enlaces que creas que son incorrectos”.

Si hace clic en Continuar , los cálculos devuelven valores del archivo fuente almacenado en caché.

Si Excel almacena en caché datos externos de esta manera, significa:

1. Es posible que esté utilizando datos antiguos sin siquiera saberlo: ¡ eso es un gran riesgo!
2. Cualquier usuario que reciba el archivo podría acceder al caché y ver datos confidenciales que no sabía que estaban allí. ¡¡ Ese podría ser un riesgo mayor!!

¿Dónde están los datos almacenados en caché?

Entonces, ¿dónde está este caché de datos al que sigo haciendo referencia?

1. Crear enlaces entre Closed.xlsx y Open.xlsx
2. Guardar y cerrar Open.xlsx
3. Cambie el nombre de Open.xlsx a Open.zip
4. Abra el archivo zip y busque el archivo xlexternalLinksexternalLink1.xml .
5. El archivo se verá así:
   

En la captura de pantalla anterior, los valores de la hoja de trabajo se resaltaron. Se incluyen todos los datos externos del libro de trabajo Closed.xlsx original. Por lo tanto, fácilmente podríamos enviar información accidentalmente e inflar el tamaño de los archivos.

Para detener el almacenamiento en caché de datos de Excel de esta manera, haga clic en Opciones de archivo para abrir el cuadro de diálogo Opciones de Excel. Luego, desmarque guardar valores de enlaces externos en la sección Avanzado .

¿Cuándo está bien vincular archivos?

Como has podido comprobar, vincular archivos no es tan sencillo como podrías pensar. Excel no siempre se comporta como quisiéramos. Entonces, ¿cuándo está bien vincular archivos? Utilizo las siguientes reglas:

• Debo 'controlar' todos los archivos involucrados
• No debe haber ningún subenlace
• Se deben utilizar rangos con nombre, no referencias de celda estándar
• Si comparte el archivo, no debe contener datos confidenciales.

Estas reglas evitan la mayoría de los problemas potenciales.

Como se señaló al principio; Ahora que tenemos Power Query como herramienta, vale la pena preguntar si esa es una mejor solución para su escenario particular. Vaya aquí para obtener más información sobre Power Query: Introducción a Power Query .

Conclusión

Vincular archivos no es para pusilánimes. Lo que parece una solución sencilla, abre todo un mundo de complejidad. Si bien es fácil, debe asegurarse de que los datos sean correctos y de no compartir información inesperadamente.

---