Referencia de Excel a otra hoja o libro (referencia externa)
¿Alguna vez te has encontrado en la situación de necesitar datos de otra hoja o incluso de otro libro de Excel? ¡No te preocupes! En el vasto universo de las hojas de cálculo, aprender a establecer referencias externas puede convertirte en un maestro en la gestión de información. Ya sea que estés trabajando en un gran proyecto, recopilando información de distintas fuentes o simplemente necesites optimizar tus análisis, dominar las referencias externas te permitirá acceder a los datos que necesitas de forma rápida y eficiente. Acompáñanos en este artículo y descubre cómo simplificar tu trabajo con Excel como un verdadero experto. ¡Comencemos!
¿Alguna vez te has preguntado cómo referenciar datos de una hoja de Excel a otra hoja o incluso a otro libro? Si eres usuario de Excel y quieres aprender esta útil técnica, ¡has llegado al lugar indicado! En este artículo te enseñaremos cómo realizar referencias externas en Excel, para que puedas organizar tus datos de manera eficiente y obtener los resultados deseados. Así que no pierdas más tiempo buscando, ¡comencemos!
Este breve tutorial explica los conceptos básicos de una referencia externa en Excel y muestra cómo hacer referencia a otra hoja y libro de trabajo en sus fórmulas.
Al calcular datos en Excel, es posible que a menudo se encuentre en una situación en la que necesite extraer datos de otra hoja de cálculo o incluso de un archivo de Excel diferente. ¿Puedes hacer eso? Por supuesto que puede. Solo necesita crear un vínculo entre las hojas de trabajo (dentro del mismo libro o en diferentes libros) usando lo que se llama una referencia de celda externa o una enlace.
Referencia externa en Excel es una referencia a una celda o rango de celdas fuera de la hoja de trabajo actual. El principal beneficio de utilizar una referencia externa de Excel es que cada vez que cambian las celdas a las que se hace referencia en otra hoja de cálculo, el valor devuelto por la referencia de celda externa se actualiza automáticamente.
Aunque las referencias externas en Excel son muy similares a las referencias de celda, existen algunas diferencias importantes. En este tutorial, comenzaremos con lo básico y mostraremos cómo crear varios tipos de referencias externas con pasos detallados, capturas de pantalla y ejemplos de fórmulas.
Cómo hacer referencia a otra hoja en Excel
Para hacer referencia a una celda o rango de celdas en otra hoja de trabajo en el mismo libro, coloque el nombre de la hoja de trabajo seguido de un signo de exclamación (!) antes de la dirección de la celda.
En otras palabras, en una referencia de Excel a otra hoja de cálculo, se utiliza el siguiente formato:
Referencia a un celda individual:
Nombre_hoja!dirección_celular
Por ejemplo, para hacer referencia a la celda A1 en la Hoja2, escribe Hoja2!A1.
Referencia a un rango de celdas:
Nombre_hoja!Primera_celda:Última_celda
Por ejemplo, para hacer referencia a las celdas A1:A10 en la Hoja2, escriba Hoja2!A1:A10.
Nota. Si el nombre de la hoja de trabajo incluye espacios o caracteres no alfabéticos, debe encerrarlo entre comillas simples. Por ejemplo, una referencia externa a la celda A1 en una hoja de trabajo llamada Hitos del proyecto debe decir lo siguiente: ‘Hitos del proyecto’!A1.
En una fórmula de la vida real, que multiplica el valor de la celda A1 en ‘Hitos del proyecto hoja por 10, una referencia de hoja de Excel se ve así:
=’Project Milestones’!A1*10
Crear una referencia a otra hoja en Excel
Al escribir una fórmula que hace referencia a celdas en otra hoja de trabajo, por supuesto, puede escribir el nombre de esa otra hoja seguido de un signo de exclamación y una referencia de celda manualmente, pero esta sería una forma lenta y propensa a errores.
Una mejor manera es señalar las celdas de otra hoja a las que desea que haga referencia la fórmula y dejar que Excel se encargue de la sintaxis correcta de la referencia de su hoja. Para que Excel inserte una referencia a otra hoja en su fórmula, haga lo siguiente:
Por ejemplo, si tiene una lista de cifras de ventas en la hoja Ventas y desea calcular el Impuesto al Valor Agregado (19%) para cada producto en otra hoja llamada IVAproceda de la siguiente manera:
- Comience a escribir la fórmula =19%* en la celda B2 de la hoja IVA.
- Cambiar a hoja Ventasy haga clic en la celda B2 allí. Excel insertará inmediatamente una referencia externa a esa celda, como se muestra en la siguiente captura de pantalla:
> - Presione Enter para completar la fórmula.
Nota. Al agregar una referencia de Excel a otra hoja usando el método anterior, Microsoft Excel agrega de forma predeterminada una referencia relativa (sin el signo $). Entonces, en el ejemplo anterior, puede simplemente copiar la fórmula a otras celdas en la columna B de la hoja. IVAlas referencias de celda se ajustarán para cada fila y tendrás el IVA de cada producto calculado correctamente.
De manera similar, puedes hacer referencia a rango de celdas en otra hoja. La única diferencia es que selecciona varias celdas en la hoja de trabajo de origen. Por ejemplo, para conocer el total de ventas en las celdas B2:B5 de la hoja Ventasingresarías la siguiente fórmula:
=SUM(Sales!B2:B5)
>
Así es como haces referencia a otra hoja en Excel. Y ahora, veamos cómo puede hacer referencia a celdas de un libro diferente.
Cómo hacer referencia a otro libro en Excel
En las fórmulas de Microsoft Excel, las referencias externas a otro libro se muestran de dos maneras, dependiendo de si el libro de origen está abierto o cerrado.
Referencia externa a un libro abierto
Cuando el libro de origen está abierto, una referencia externa de Excel incluye el nombre del libro entre corchetes (incluida la extensión del archivo), seguido del nombre de la hoja, el signo de exclamación (!) y la celda o rango de celdas a la que se hace referencia. En otras palabras, utiliza el siguiente formato de referencia para una referencia de libro abierto:
[Workbook_name]Nombre_hoja!dirección_celular
Por ejemplo, aquí hay una referencia externa a las celdas B2:B5 en la hoja Ene en el libro de trabajo llamado Ventas.xlsx:
[Sales.xlsx]Jan!B2:B5
Si desea, digamos, calcular la suma de esas celdas, la fórmula con la referencia del libro se vería así:
=SUM([Sales.xlsx]Jan!B2:B5)
Referencia externa a un libro cerrado
Cuando hace referencia a otro libro en Excel, no es necesario que ese otro libro esté abierto. Si el libro de origen está cerrado, debe agregar la ruta completa a su referencia externa.
Por ejemplo, para sumar las celdas B2:B5 en el Ene hoja de Ventas.xlsx libro de trabajo que reside dentro del Informes carpeta en la unidad D, escribe la siguiente fórmula:
=SUM(D:Reports[Sales.xlsx]Jan!B2:B5)
Aquí hay un desglose de las piezas de referencia:
- Ruta de archivo. Apunta a la unidad y al directorio en el que está almacenado su archivo de Excel (D:Informes en este ejemplo).
- Nombre del libro de trabajo. Incluye la extensión del archivo (.xlsx, .xls o .xslm) y siempre está entre corchetes, como [Sales.xlsx] en la fórmula anterior.
- Nombre de la hoja. Esta parte de la referencia externa de Excel incluye el nombre de la hoja seguido de un signo de exclamación donde se encuentran las celdas a las que se hace referencia (¡Ene! en este ejemplo).
- Referencia de celda. Apunta a la celda real o a un rango de celdas al que se hace referencia en su fórmula.
Si creó una referencia a otro libro de trabajo cuando ese libro estaba abierto y luego cerró el libro de origen, la referencia de su libro de trabajo externo se actualizará automáticamente para incluir la ruta completa.
Nota. Si el nombre del libro o el nombre de la hoja, o ambos, incluya espacios o cualquier caracteres no alfabéticos, debe incluir la ruta entre comillas simples. Por ejemplo:
=SUM(‘[Year budget.xlsx]Jan’!B2:B5)
=SUM(‘[Sales.xlsx]Jan sales’!B2:B5)
=SUM(‘D:Reports[Sales.xlsx]Jan sales’!B2:B5)
Hacer una referencia a otro libro en Excel
Como es el caso de la creación de una fórmula de Excel que hace referencia otra hoja, no es necesario escribir manualmente una referencia a un libro diferente. Simplemente cambie al otro libro de trabajo cuando ingrese su fórmula y seleccione una celda o un rango de celdas al que desee hacer referencia. Microsoft Excel se encargará del resto:
>
Notas:
- Al crear una referencia a otro libro seleccionando las celdas que contiene, Excel siempre inserta referencias de celda absolutas. Si tiene la intención de copiar la fórmula recién creada a otras celdas, asegúrese de eliminar el signo de dólar ($) de las referencias de celda para convertirlas en referencias relativas o mixtas, según sus propósitos.
- Si al seleccionar una celda o rango en el libro al que se hace referencia no se crea automáticamente una referencia en la fórmula, lo más probable es que los dos archivos estén abiertos en diferentes instancias de excel. Para comprobar esto, abra el Administrador de tareas y vea cuántas instancias de Microsoft Excel se están ejecutando. Si hay más de una, expanda cada instancia para ver qué archivos están anidados allí. Para solucionar el problema, cierre un archivo (y una instancia) y luego ábralo nuevamente desde el otro archivo.
Referencia a un nombre definido en el mismo libro o en otro
Para hacer que una referencia externa de Excel sea más compacta, puede crear un nombre definido en la hoja de origen y luego hacer referencia a ese nombre desde otra hoja que resida en el mismo libro o en un libro diferente.
Crear un nombre en Excel
Para crear un nombre en Excel, seleccione todas las celdas que desea incluir y luego vaya a la Fórmulas pestaña > Nombres definidos grupo y haga clic en el Definir nombre botón, o presione Ctrl + F3 y haga clic Nuevo.
En el Nuevo nombre cuadro de diálogo, escriba el nombre que desee (recuerde que no se permiten espacios en los nombres de Excel) y verifique si se muestra el rango correcto en el cuadro de diálogo Se refiere a campo.
Por ejemplo, así es como creamos un nombre (Jan_ventas) para las celdas B2:B5 en Ene hoja:
>
Una vez creado el nombre, podrá utilizarlo en sus referencias externas en Excel. El formato de dichas referencias es mucho más simple que el formato de un Excel. referencia de hoja y referencia del libro de trabajo discutido anteriormente, lo que hace que las fórmulas con referencias de nombres sean más fáciles de comprender.
Nota. De forma predeterminada, los nombres de Excel se crean para el nivel del libro de trabajotenga en cuenta el Alcance campo en la captura de pantalla anterior. Pero también puedes hacer una específica. nivel de hoja de trabajo nombre eligiendo la hoja correspondiente de la Alcance la lista desplegable. Para las referencias de Excel, el alcance de un nombre es muy importante porque determina la ubicación dentro de la cual se reconoce el nombre.
Se recomienda que siempre cree nombres a nivel de libro (a menos que tenga un motivo específico para no hacerlo), porque simplifican significativamente la creación de referencias externas de Excel, como se ilustra en los siguientes ejemplos.
Hacer referencia a un nombre en otra hoja del mismo libro
Para hacer referencia a un global nivel de libro de trabajo nombre en el mismo libro, simplemente escriba ese nombre en el argumento de una función:
=Función(nombre)
Por ejemplo, para encontrar la suma de todas las celdas dentro del Jan_ventas nombre que creamos hace un momento, use la siguiente fórmula:
=SUM(Jan_sales)
Para hacer referencia a un local nivel de hoja de trabajo nombre en otra hoja dentro del mismo libro, debe preceder el nombre con el nombre de la hoja seguido de un signo de exclamación:
=Function(Sheet_name!name)
Por ejemplo:
=SUM(Jan!Jan_sales)
Si los nombres de las hojas incluyen espacios o caracteres monoalfabéticos, recuerde encerrarlos entre comillas simples, por ejemplo:
=SUM(‘Jan report’!Jan_Sales)
Hacer referencia a un nombre en otro libro de trabajo
Una referencia a un nivel de libro de trabajo El nombre en un libro de trabajo diferente consta del nombre del libro de trabajo (incluida la extensión) seguido de un signo de exclamación y el nombre definido (rango con nombre):
=Función(nombre_libro!nombre)
Por ejemplo:
=SUM(Sales.xlsx!Jan_sales)
Para hacer referencia a un nivel de hoja de trabajo nombre en otro libro de trabajo, también se debe incluir el nombre de la hoja seguido del signo de exclamación y el nombre del libro de trabajo debe estar entre corchetes. Por ejemplo:
=SUM([Sales.xlsx]Jan!Jan_sales)
Al hacer referencia a un rango con nombre en un libro cerradorecuerda incluir la ruta completa a tu archivo de Excel, por ejemplo:
=SUM(‘C:DocumentsSales.xlsx’!Jan_sales)
Cómo crear una referencia de nombre en Excel
Si ha creado varios nombres diferentes en sus hojas de Excel, no es necesario que los recuerde todos de memoria. Para insertar una referencia de nombre de Excel en una fórmula, realice los siguientes pasos:
- Si te refieres a un nivel de libro de trabajo nombre de otro libro de trabajo, cambie a ese libro de trabajo. Si el nombre reside en otra hoja dentro del mismo libro, omita este paso.
- Si estás haciendo referencia a un nivel de hoja de trabajo nombre, navegue hasta esa hoja específica, ya sea en el libro de trabajo actual o en otro diferente.
>
Ahora que sabe cómo crear una referencia externa en Excel, puede aprovechar esta gran capacidad y utilizar datos de otras hojas de trabajo y libros de trabajo en sus cálculos. ¡Te agradezco por leer y espero verte en nuestro blog la próxima semana!
Usted también podría estar interesado en
Referencia de Excel a otra hoja o libro (referencia externa)
Referencia de Excel a otra hoja o libro (referencia externa)
En Excel, las referencias externas son una herramienta muy útil para organizar y utilizar datos de diferentes hojas de cálculo o libros. A través de la referencia externa, puedes acceder a datos de otras ubicaciones y utilizarlos en tus fórmulas y funciones. A continuación, resolveremos algunas preguntas frecuentes relacionadas con las referencias externas en Excel.
1. ¿Cómo puedo referenciar una celda de otra hoja en Excel?
Para referenciar una celda de otra hoja en Excel, utiliza la siguiente sintaxis: ‘NombreHoja’!Celda. Por ejemplo, si deseas obtener el valor de la celda A1 de la hoja «Hoja2», tu fórmula sería ‘Hoja2’!A1. Ten en cuenta que debes incluir las comillas simples alrededor del nombre de la hoja si contiene espacios o caracteres especiales.
2. ¿Cómo puedo referenciar una celda de otro libro en Excel?
Si deseas referenciar una celda de otro libro en Excel, primero asegúrate de tener ambos libros abiertos. Luego, utiliza la siguiente sintaxis: ‘[Ruta/NombreLibro.xlsx]NombreHoja’!Celda. Por ejemplo, si deseas obtener el valor de la celda B2 de la hoja «Hoja1» en el libro «Libro2.xlsx», tu fórmula sería ‘[Libro2.xlsx]Hoja1’!B2.
3. ¿Puedo utilizar referencias externas en funciones de Excel?
Sí, puedes utilizar referencias externas en muchas funciones de Excel. Por ejemplo, puedes sumar los valores de diferentes hojas de cálculo utilizando la función SUMA. Si deseas sumar los valores de la celda A1 de la hoja «Hoja1» y la celda B1 de la hoja «Hoja2», tu fórmula sería =SUMA(‘Hoja1′!A1,’Hoja2’!B1). Recuerda ajustar la sintaxis de acuerdo a tus necesidades y el tipo de función que desees utilizar.
4. ¿Hay alguna manera de actualizar automáticamente las referencias externas en Excel?
Sí, Excel te ofrece la opción de actualizar automáticamente las referencias externas cada vez que abras el libro. Para hacer esto, ve a la pestaña «Datos» en la barra de navegación y selecciona «Actualizar todo» en el grupo «Conexiones». Esto asegurará que los datos referenciados se mantengan actualizados.
5. ¿Qué sucede si el libro referenciado no está abierto?
Si el libro referenciado no está abierto al momento de utilizar una referencia externa, Excel mostrará un error y no podrá obtener los datos. Asegúrate de tener abierto el libro referenciado antes de utilizar las referencias externas en tus fórmulas o funciones.
Esperamos que estas preguntas frecuentes te hayan ayudado a comprender mejor cómo trabajar con referencias externas en Excel. Para obtener más información detallada y ejemplos adicionales, te recomendamos revisar la documentación oficial de Microsoft o consultar la comunidad de Excel en línea.
[automatic_youtube_gallery type=»search» search=»Referencia de Excel a otra hoja o libro (referencia externa)» limit=»1″]
En Excel, las referencias externas son cruciales para la organización y uso efectivo de datos entre diferentes hojas de cálculo o libros. Aquí tienes un resumen sobre cómo hacer referencias a celdas en hojas de trabajo y libros diferentes, tanto abiertos como cerrados.
1. Referenciar celdas en otra hoja de trabajo
Para referenciar una celda de otra hoja en el mismo libro, puedes usar la siguiente sintaxis:
'NombreHoja'!Celda
Por ejemplo, si quieres referenciar la celda B2 de una hoja llamada «Ventas»:
=Ventas!B2
Si el nombre de la hoja tiene espacios, es necesario ponerlo entre comillas simples:
='Nombre Hoja'!B2
Al finalizar la fórmula, presiona Enter para completarla. Las referencias son relativas, lo que permite copiarlas a otras celdas.
2. Referenciar a un rango de celdas en otra hoja
Para sumar un rango de celdas en otra hoja:
=SUM(Ventas!B2:B5)
3. Referenciar celdas en otro libro de trabajo
A. Libro abierto
Cuando te refieres a un libro que está abierto, la sintaxis es:
[NombreLibro]NombreHoja!Celda
Por ejemplo, para sumar las celdas B2:B5 de una hoja llamada «Ene» en el libro «Ventas.xlsx»:
=SUM([Ventas.xlsx]Ene!B2:B5)
B. Libro cerrado
Si el libro está cerrado, necesitas proporcionar la ruta completa:
=SUM('D:Informes[Ventas.xlsx]Ene'!B2:B5)
4. Referenciar nombres definidos
Puedes crear un nombre definido en Excel para facilitar las referencias. Supongamos que definiste el rango B2:B5 como «VentasJan», puedes usarlo de la siguiente manera:
- En la misma hoja:
=SUM(VentasJan)
- En otra hoja:
=SUM(OtraHoja!VentasJan)
- En otro libro abierto:
=SUM([Ventas.xlsx]Ene!VentasJan)
- En otro libro cerrado:
=SUM('D:Informes[Ventas.xlsx]Ene'!Ventas_Jan)
5. Insertar una referencia de nombre
Para insertar una referencia de nombre:
- Selecciona la celda de destino y comienza con =.
- Presiona F3 para abrir el cuadro de diálogo «Nombres definidos”.
- Selecciona el nombre deseado y haz clic en Aceptar, luego completa la fórmula.
Resumen
Las referencias externas en Excel son una poderosa herramienta para trabajar con datos distribuidos en distintas hojas y libros. Recuerda siempre verificar la sintaxis y estar consciente de si tus libros están abiertos o cerrados, así como el alcance de los nombres definidos para facilitar su uso en las fórmulas.
Me pareció súper útil el artículo, ¡gracias por compartir! Yo solía tener un lío con las referencias externas en Excel, pero después de seguir algunos tips que vi aquí, ahora me resulta mucho más fácil organizar mis datos. ¡A seguir aprendiendo! – tcaenfermeriabd.