Excel: ¿cómo cruzar celdas de referencia?

➥ Ir al inicio del Curso de Excel online gratis

En Microsoft Excel, es una tarea común referirse a celdas en otras hojas de trabajo o incluso en diferentes archivos de Excel. Al principio, esto puede parecer un poco desalentador y confuso, pero una vez que usted entiende 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 hacer las cosas más simples con nombres definidos, y cómo usar VLOOKUP para referencias dinámicas.

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

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

Así que la referencia de celda B3 se refiere a la celda en la intersección de la columna B y la fila 3.

Cuando se refiere a celdas en otras hojas, esta referencia de celda es precedida por el nombre de la otra hoja. Por ejemplo, abajo hay una referencia a la celda B3 en una hoja llamada «Enero».

B3 =Enero!

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 entre 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 más confiable dejar que Excel escriba la referencia por usted.

Escriba un signo igual (=) en una celda, haga clic en la ficha Hoja y, a continuación, haga clic en la celda a la que desea hacer referencia cruzada.

Al hacerlo, Excel escribe la referencia en la barra de fórmulas.

Pulse Intro para completar la fórmula.

Cómo hacer referencia a otro archivo de Excel

Puede referirse a celdas de otro libro de trabajo utilizando el mismo método. Sólo asegúrese de tener el otro archivo de Excel abierto antes de empezar a escribir la fórmula.

Escriba un signo igual (=), cambie al otro archivo y, a continuación, haga clic en la celda del 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 por el 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á incluir la referencia del archivo (incluidos los corchetes) entre comillas simples.

= ‘[Nueva York.xlsx] enero’! B3

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

Cuando se hace referencia a celdas y rangos en diferentes archivos de Excel, las referencias se hacen absolutas por defecto. Si es necesario, puede cambiarlo por una referencia relativa.

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

Aunque la creación de referencias a otros libros de trabajo es sencilla, son más susceptibles a los problemas. Los usuarios que crean o renombran carpetas y mueven archivos pueden romper estas referencias y causar errores.

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

Cómo cruzar referencias 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 SUM suma los valores del rango B2:B6 en una hoja de trabajo llamada Ventas.

=SUM(Sales!B2:B6))

Cómo usar nombres definidos para referencias cruzadas simples

En Excel, puede asignar un nombre a una celda o a un rango de celdas. Esto es más significativo que la dirección de una celda o de un rango cuando se mira hacia atrás. Si usas muchas referencias en tu hoja de cálculo, nombrarlas puede hacer que sea mucho más fácil ver lo que has hecho.

Mejor aún, este nombre es único para todas las hojas de trabajo de ese archivo de Excel.

Por ejemplo, podríamos llamar a una celda’ChicagoTotal’ y luego la referencia cruzada diría:

=ChicagoTotal

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

=Ventas!B2

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

Haga clic en el cuadro Nombre en la esquina superior izquierda, escriba el nombre que desea asignar y, a continuación, presione Entrar.

Al crear nombres definidos, no se pueden utilizar espacios. Por lo tanto, en este ejemplo, las palabras han sido unidas en el nombre y separadas por una letra mayúscula. También puede separar palabras con caracteres como un guión (-) o 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.

A continuación, puede utilizar los botones situados en la parte superior para editar y eliminar estos nombres definidos.

Cómo formatear datos como una tabla

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

Tome la siguiente tabla sencilla.

Esto podría ser formateado como 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, a continuación, seleccione un estilo.

Confirme que el rango de celdas es correcto y que su tabla tiene encabezados.

En la pestaña «Design» puede asignar un nombre significativo a su tabla.

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.

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

=Suma (Ventas[Chicago])

Puede ver cómo las tablas pueden hacer que los datos de referencia para funciones de agregación como SUM y PROMEDIO sean más fáciles que las referencias de hojas estándar.

Esta tabla es pequeña para fines de demostración. Mientras 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 VLOOKUP para referencias dinámicas

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

Sin embargo, ¿qué pasa 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 se puede garantizar que el valor que se desea seguirá estando en la misma celda a la que se hizo referencia inicialmente.

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

En el siguiente ejemplo, utilizamos la función VLOOKUP para buscar a un empleado en otra hoja por su ID de empleado y luego devolverle su fecha de inicio.

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

La función VLOOKUP mira hacia abajo la primera columna de una tabla y luego devuelve información de una columna especificada a la derecha.

La siguiente función VLOOKUP busca el ID de empleado introducido en la celda A2 de la lista mostrada arriba y devuelve la fecha unida de la columna 4 (cuarta columna de la tabla).

=VLOOKUP(A2,Empleados!A:E,4,FALSE)

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

Lo bueno de este VLOOKUP sobre los ejemplos anteriores es que el empleado será encontrado incluso si la lista cambia de orden.