Cómo crear una hoja de resumen que vincule datos de varias hojas de cálculo.

1. Análisis de la construcción del cuaderno de trabajo. Es muy importante analizar cuidadosamente cómo están estructurados los datos antes de empezar a trabajar.
2. Comprender cada componente de una referencia. Cada parte de una referencia tendrá un papel que desempeñar.
3. Construya la función INDIRECTA. El uso de la función INDIRECTO es fundamental para realizar la conexión entre las diferentes partes de una referencia.

Tomemos como ejemplo un libro de trabajo que contiene las hojas de horas de 5 empleados (una hoja por empleado). Un primer punto muy importante a tener en cuenta es que todas las hojas de trabajo tienen la misma estructura.

• La información del lunes está en la línea 4, el martes en la línea 5, ....
• Todos los días están en la columna A.
• Las horas trabajadas siempre están en la columna B.

Ahora la pregunta es cómo podemos completar la hoja de resumen con las otras cinco hojas. ¿Cómo podemos vincular la hoja de resumen con varias hojas de cálculo?

Paso 1: escriba el primer enlace

Vamos a escribir el primer enlace entre la hoja resumen y la hoja del empleado Pierre del día lunes.

1. En la celda B3, ingrese el signo =
2. Luego seleccione la celda que contiene el tiempo de Pierre (celda B4)

=¡Pedro!B4

Paso 2: Analicemos la fórmula.

La fórmula se divide en 4 categorías.

1. El nombre de la hoja (Pierre)
2. El separador entre el nombre de la hoja y la referencia de la celda (el signo de exclamación)
3. La referencia de la columna (B)
4. La referencia de línea (4)

Paso 3: escriba conexiones de los lunes para otros empleados

Luego, repetiremos la misma operación para el resto de empleados.

Y usando la técnica para mostrar fórmulas en celdas, Tenemos el siguiente resultado

COMENTARIO IMPORTANTE. Entre todas estas fórmulas, lo único que cambia es el nombre de la hoja de cálculo. Todo lo demás es igual (!B4). Sin embargo, el nombre de los empleados está presente en las celdas B2, C2, D2, E2 y F2. Entonces la idea es

• Construir una referencia a otras hojas de trabajo usando las celdas B2, C2, ...

Paso 4: utiliza la función INDIRECTA

La Función INDIRECTA le permite interpretar una referencia escrita en forma de cadenas de caracteres. Así que ya vamos a escribir una de las referencias como argumento para la función INDIRECTA.

=INDIRECTO("Pierre!B4")

Paso 5: construye la referencia con una celda

¡AQUÍ ES DONDE TODO ESTÁ EN JUEGO! Transformaremos la fórmula anterior para usar los valores de la línea 2 como referencia.

Así que vamos

1. Eliminar el nombre de la hoja de trabajo de la referencia
2. Luego, colóquese FUERA de la cadena de caracteres.
3. Seleccione la celda B2
4. Une las 2 partes con el símbolo &

=INDIRECTO(B2&"!B4")

PERFECTO ! La función INDIRECTA devuelve el resultado de la celda B2 de la hoja de trabajo de piedra. Si la escritura de la referencia no es perfecta, la función devolverá un error.

A continuación, copiaremos la fórmula. Cuando copias la fórmula solo para el lunes, Vemos el cambio de referencia de la columna (es perfecto). Pero luego, cuando vamos a copiar para los demás días de la semana, siempre debes permanecer en la línea 2. Por eso tenemos pon un dolar justo antes de la referencia de línea.

Paso 6: ¿Qué pasa con los otros días?

En esta etapa, sólo se utiliza como variable el nombre de la hoja de trabajo. Pero también debemos transformar el número de línea a regresar para no quedarnos siempre en la celda B4.

Lo que debes hacer es transformar el 4 con una fórmula que devolverá 5, 6, 7, 8 para los demás. Para hacer esto, usaremos la función LINE().

=INDIRECTO(B$2&"!B"&LINEA()+1)

Escribiendo LIGNE()+1 Se explica por la diferencia de líneas entre la hoja "Resumen" y las hojas de los empleados.

Artículos relacionados

• Función INDIRECTA en Excel
• Fusionar varias hojas de Excel en una
• Consejo: copie una hoja a otro libro de trabajo
• Los 10 mejores consejos para las hojas de cálculo

Simplifique el trabajo con Power Query

Otra forma de hacerlo sería consolidar todas las hojas de trabajo en una sola. Este trabajo se puede realizar fácilmente con Power Query como se explica en el vídeo. Por lo tanto, el trabajo de análisis puede simplificarse de construir con una tabla dinámica por ejemplo.