Header Ads Widget

Ticker

6/recent/ticker-posts

Cómo hacer referencias cruzadas de celdas entre hojas de cálculo de Microsoft Excel

 logotipo de Excel

En Microsoft Excel, es una tarea común hacer referencia a celdas en otras hojas de trabajo o incluso en diferentes archivos de Excel. Al principio, esto puede parecer un poco abrumador y confuso, pero una vez que comprende cómo funciona, no es tan difícil.

En este artículo, veremos cómo hacer referencia a otra hoja en el mismo archivo de Excel y cómo hacer referencia a un archivo de Excel diferente. También cubriremos cosas como cómo hacer referencia a un rango de celdas en una función, cómo simplificar las cosas con nombres definidos y cómo usar BUSCARV para referencias dinámicas.

Cómo hacer referencia a otra hoja en el mismo archivo de Excel

Una referencia de celda básica se escribe como la letra de la columna seguida del número de la fila.

Entonces, la referencia de celda B3 se refiere a la celda en la intersección de la columna B y la fila 3.

Cuando se hace referencia a celdas en otras hojas, esta referencia de celda está precedida por el nombre de la otra hoja. Por ejemplo, a continuación se muestra una referencia a la celda B3 en el nombre de una hoja "Enero".

= Enero! B3

El signo de exclamación (!) Separa el nombre de la hoja de la dirección de la celda.

Si el nombre de la hoja contiene espacios, debe encerrar el nombre con comillas simples en la referencia.

= 'Ventas de enero'! B3

Para crear estas referencias, puede escribirlas directamente en la celda. Sin embargo, es más fácil y confiable dejar que Excel escriba la referencia por usted.

Escriba un signo igual (=) en una celda, haga clic en la pestaña Hoja y luego haga clic en la celda que desea hacer una referencia cruzada.

Al hacer esto, Excel escribe la referencia por usted en la barra de fórmulas.

Referencia de hoja en fórmula

Presione Entrar para completar la fórmula.

Cómo hacer referencia a otro archivo de Excel

Puede hacer referencia a celdas de otro libro utilizando el mismo método. Solo asegúrese de tener abierto el otro archivo de Excel antes de comenzar a escribir la fórmula.

Escriba un signo igual (=), cambie al otro archivo y luego haga clic en la celda de ese archivo al que desea hacer referencia. Presione Entrar cuando haya terminado.

La referencia cruzada completa contiene el otro nombre del libro de trabajo entre corchetes, seguido del nombre de la hoja y el número de celda.

= [Chicago.xlsx] ¡Enero! B3

Si el nombre del archivo o de la hoja contiene espacios, deberá encerrar la referencia del archivo (incluidos los corchetes) entre comillas simples.

= '[New York.xlsx] Enero'! B3

Fórmula que hace referencia a otro libro

En este ejemplo, puede ver signos de dólar ($) entre la dirección de la celda. Esta es una referencia de celda absoluta ( Obtenga más información sobre las referencias de celda absolutas ).

Al hacer referencia a celdas y rangos en diferentes archivos de Excel, las referencias se hacen absolutas de forma predeterminada. Puede cambiar esto a una referencia relativa si es necesario.

Si observa la fórmula cuando se cierra el libro de trabajo al que se hace referencia, contendrá la ruta completa a ese archivo.

Ruta de archivo completa del libro de trabajo en la fórmula

Aunque crear referencias a otros libros de trabajo es sencillo, son más susceptibles a problemas. Los usuarios que crean o renombran carpetas y mueven archivos pueden romper estas referencias y provocar errores.

Mantener los datos en un libro de trabajo, si es posible, es más confiable.

Cómo hacer una referencia cruzada de un rango de celdas en una función

Hacer referencia a una sola celda es bastante útil. Pero es posible que desee escribir una función (como SUMA) que haga referencia a un rango de celdas en otra hoja de trabajo o libro de trabajo.

Inicie la función como de costumbre y luego haga clic en la hoja y el rango de celdas, de la misma manera que lo hizo en los ejemplos anteriores.

En el siguiente ejemplo, una función SUMA suma los valores del rango B2: B6 en una hoja de trabajo denominada Ventas.

= SUMA (Ventas! B2: B6)

Referencia cruzada de hoja en función de suma

Cómo utilizar nombres definidos para referencias cruzadas simples

En Excel, puede asignar un nombre a una celda o rango de celdas. Esto es más significativo que una celda o una dirección de rango cuando los mira hacia atrás. Si usa muchas referencias en su hoja de cálculo, nombrar esas referencias puede hacer que sea mucho más fácil ver lo que ha hecho.

Aún mejor, este nombre es único para todas las hojas de trabajo en ese archivo de Excel.

Por ejemplo, podríamos nombrar una celda 'ChicagoTotal' y luego la referencia cruzada leería:

= ChicagoTotal

Esta es una alternativa más significativa a una referencia estándar como esta:

= Ventas! B2

Es fácil crear un nombre definido. Comience seleccionando la celda o rango de celdas que desea nombrar.

Haga clic en el cuadro de nombre en la esquina superior izquierda, escriba el nombre que desea asignar y luego presione Entrar.

Definiendo un nombre en Excel

Al crear nombres definidos, no puede utilizar espacios. Por lo tanto, en este ejemplo, las palabras se unieron en el nombre y se separaron con una letra mayúscula. También puede separar palabras con caracteres como un guión (-) o un guión bajo (_).

Excel también tiene un Administrador de nombres que facilita el monitoreo de estos nombres en el futuro. Haga clic en Fórmulas> Administrador de nombres. En la ventana Administrador de nombres, puede ver una lista de todos los nombres definidos en el libro de trabajo, dónde están y qué valores almacenan actualmente.

Administrador de nombres para administrar los nombres definidos

Luego puede usar los botones en la parte superior para editar y eliminar estos nombres definidos.

Cómo dar formato a datos como una tabla

Al trabajar con una lista extensa de datos relacionados, el uso de la función Formato como tabla de Excel puede simplificar la forma en que hace referencia a los datos en ella.

Tome la siguiente tabla simple.

Pequeña lista de datos de ventas de productos

Esto podría tener el formato de una tabla.

Haga clic en una celda de la lista, cambie a la pestaña "Inicio", haga clic en el botón "Formatear como tabla" y luego seleccione un estilo.

Dar formato a un rango como una tabla

Confirme que el rango de celdas sea correcto y que su tabla tenga encabezados.

Confirme el rango que se utilizará para la mesa.

A continuación, puede asignar un nombre significativo a su tabla desde la pestaña "Diseño".

Asignar un nombre a su tabla de Excel

Luego, si necesitáramos sumar las ventas de Chicago, podríamos referirnos a la tabla por su nombre (de cualquier hoja), seguido de un corchete ([) para ver una lista de las columnas de la tabla.

Usar referencias estructuradas en fórmulas

Seleccione la columna haciendo doble clic en ella en la lista e ingrese un corchete de cierre. La fórmula resultante se vería así:

= SUM (Ventas [Chicago])

Puede ver cómo las tablas pueden facilitar la referenciación de datos para funciones de agregación como SUMA y PROMEDIO que las referencias de hoja estándar.

Esta tabla es pequeña para fines de demostración. Cuanto más grande sea la tabla y más hojas tenga en un libro de trabajo, más beneficios verá.

Cómo utilizar la función BUSCARV para referencias dinámicas

Las referencias utilizadas en los ejemplos hasta ahora se han fijado todas a una celda o rango de celdas específico. Eso es genial y, a menudo, es suficiente para sus necesidades.

Sin embargo, ¿qué sucede si la celda a la que hace referencia tiene el potencial de cambiar cuando se insertan nuevas filas o si alguien ordena la lista?

En esos escenarios, no puede garantizar que el valor que desea seguirá estando en la misma celda a la que hizo referencia inicialmente.

Una alternativa en estos escenarios es usar una función de búsqueda dentro de Excel para buscar el valor en una lista. Esto lo hace más duradero frente a cambios en la hoja.

En el siguiente ejemplo, usamos la función BUSCARV para buscar un empleado en otra hoja por su ID de empleado y luego devolver su fecha de inicio.

A continuación se muestra la lista de empleados de ejemplo.

Lista de empleados

La función BUSCARV busca la primera columna de una tabla y luego devuelve información de una columna específica a la derecha.

La siguiente función BUSCARV busca el ID de empleado ingresado en la celda A2 en la lista que se muestra arriba y devuelve la fecha unida de la columna 4 (cuarta columna de la tabla).

= BUSCARV (A2, ¡Empleados! A: E, 4, FALSO)

La función BUSCARV

A continuación se muestra una ilustración de cómo esta fórmula busca en la lista y devuelve la información correcta.

La función BUSCARV y cómo funciona

Lo mejor de esta VLOOKUP con respecto a los ejemplos anteriores es que se encontrará al empleado incluso si la lista cambia en orden.

Nota:  BUSCARV es una fórmula increíblemente útil, y solo hemos arañado la superficie de su valor en este artículo. Puede obtener más información sobre cómo utilizar BUSCARV en nuestro artículo sobre el tema.


En este artículo, hemos analizado varias formas de realizar referencias cruzadas entre hojas de cálculo de Excel y libros de trabajo. Elija el enfoque que funcione para su tarea en cuestión y con el que se sienta cómodo trabajando.

Publicar un comentario

0 Comentarios