En este tutorial vamos a aprender a trabajar con fórmulas que usan valores que contienen celdas de otros libros.
Descargamos los archivo de trabajo AQUI
Crearemos una libro de excel para controlar matriculas trimestrales en una Casa de la Cultura como la de Torrejón. En la que podemos ver que hay 3 hojas, que corresponderían al primer trimestre. Tendremos pues, 3 libros, uno por cada uno de los trimestres (oct-nov-dic / ene-feb-mar-abr / may-jun-jul), y un cuarto libros que será el resumen anual.
Podríamos crear la hoja de octubre, y usando el botón copiar, pegarla luego en la Hoja2 y Hoja3, y renombrarlas después como Noviembre y Diciembre.
También podemos hacer lo siguiente: primero damos nombre a las 3 hojas haciendo doble clic en las pestañas de las hojas y escribiendo sus nombres. Después nos situamos en la hoja de octubre y, con la tecla Ctrl presionada, hacemos clic en las pestañas de las hojas Noviembre y Diciembre. De este modo, lo que escribamos se escribirá en las tres hojas a la vez.
Una vez creado el libro base, en cada una de las hojas modificaremos la celda E1, poniendo el nombre del mes correspondiente.
Salvaremos el archivo con el nombre TUT-EX_22_1er TRIMESTRE.xlsx
Con la opción Guardar como salvaremos el archivo primero como TUT-EX_22_2º TRIMESTRE.xlsx y TUT-EX_22_3er TRIMESTRE.xlsx
En ellos haremos las modificaciones necesarias como cambiar los nombres de las pestañas de las hojas y en todos los casos ajustar la celda E1 de cada hoja escribiendo el nombre de su mes correspondiente.
Por último prepararemos un libro de una sola hoja que será el resumen anual y que salvaremos como TUT-EX_22_ANUAL.xlsx
Rellenaremos todas las hojas de los libros trimestrales para preparar nuestro ejercicio.
Una vez que tenemos nuestros 3 libros trimestrales rellenos, crearemos un área de trabajo. Un área de trabajo puede servirnos para trabajar con varios libros abiertos al mismo tiempo.
Abrimos al mismo tiempo los archivos TUT-EX_22_1er TRIMESTRE.xlsx, TUT-EX_22_2º TRIMESTRE.xlsx, TUT-EX_22_3er TRIMESTRE.xlsx y TUT-EX_22_ANUAL.xlsx
Distribuiremos los archivos en la pantalla en mosaico.
Vamos a la ficha Vista / ventana / organizar todo y elegimos la opción mosaico. Ahora tenemos los 4 archivos en pantalla y podremos trabajar con ellos. Esta distribución de archivos en mosaico la podemos salvar como un archivo usando el botón Guardar área de trabajo y dándole un nombre. Tendremos un archivo con extensión xlw, que llamaremos casadelacultura.xlw. En adelante, para abrir directamente ese área de trabajo abriremos este archivo.
Abrimos el archivo casadelacultura.xlw y tenemos nuestros 4 archivos de trabajo:
Antes de empezar a preparar nuestro informe anual, veremos cómo trabajan estas fórmulas de Excel cuando recopilan datos almacenados en celdas de diferentes libros y hojas.
Supongamos que estamos en un libro de Excel llamado Destino.xlsx. Y queremos traer un valor alojado en otro libro de Excel llamado Origen.xlsx, en cuya Hoja1 se encuentra el valor alojado en su celda A1.
Parece lógico que lo primero que deberá figurar en la fórmula que traerá el valor será el nombre del archivo del libro Origen.xlsx A continuación en qué hoja de libro está la celda en la que está el valor que queremos traer: Hoja1. Y, por último, el nombre de la celda: A1.
Así Excel irá primero al libro Origen.xlsx, luego a la Hoja1 y, por último, tomará el valor alojado en la celda A1.
La fórmula debería seguir un esquema similar a éste:
=Origen.xlsxHoja1B1
Y Excel escribirá así la fórmula:
=[Origen.xlsx]Hoja1!$A$1
No es necesario que sepamos escribir la fórmula, ya que Excel puede hacerlo por nosotros.
Con los dos archivos abiertos en la celda A1 del archivo Destino.xlsx comentariamos escribiendo = para iniciar la fórmula. A continuación haremos clic en la celda A1 de la Hoja1 del archivo Origen.xlsx, donde está el valor que queremos traer. Por último pulsamos Enter, y Excel nos escribe la fórmula:
=[Origen.xlsx]Hoja1!$A$1
En la que:
- [Origen.xlsx] es el primer parámetro del operando, y representa el nombre del libro.
- Hoja1! Es el segundo parámetro y representa el nombre de la hoja.
- $A$1 Es el tercer parámetro y representa a la celda sobre la que hemos hecho clic y contiene el valor que queremos usar.
Podemos verlo en la siguiente imagen:
Si a nuestra fórmula quisiéramos añadir el nombre Luis, que se encuentra en la celda A1, de la Hoja1 de otro libro de Excel llamado Origen_nombres.xlsx
Nuestra fórmula sería así:
=[Origen.xlsx]Hoja1!$A$1&" "&[Origen_nombres]Hoja1!$A&1
Podemos verlo en la siguiente imagen:
Simplemente habremos de tener abiertos los 3 archivos y pulsar en las celdas correspondientes de los libros Origen.xlsx y origen_nombres.xlsx cuando corresponda en la sintaxis de la fórmula.
De la misma forma habremos de actuar en nuestro resumen anual del ejercicio.
Abrimos de nuevo casadelacultura.xlw y tenemos nuestros 4 archivos de trabajo:
Primero rellenaremos la celda C4 del libro anual.
Nos situamos en la celda C4 de la Hoja1 del libro TUT-EX_22_ANUAL.xlsx y escribimos el signo = para iniciar la fórmula. En el libro TUT-EX_22_1er TRIMESTRE.xlsx hacemos clic en la celda C4 de la hoja Octubre y pulsamos +. Abrimos la hoja Noviembre y hacemos clic en la celda C4 y pulsamos +. Abrimos la hoja Diciembre y hacemos clic en la celda C4 y pulsamos +.
En el libro TUT-EX_22_2º TRIMESTRE.xlsx hacemos clic en la celda C4 de la hoja Enero y luego pulsamos +. Abrimos la celda Febrero y hacemos clic en su celda C4 y pulsamos +. Abrimos la hoja Marzo y hacemos clic en su celda C4 y pulsamos +.
En el libro TUT-EX_22_3er TRIMESTRE.xlsx abrimos la hoja Abril y hacemos clic en su celda C4 y pulsamos +. Abrimos la hoja Mayo y hacemos clic en su celda C4 y pulsamos +. Abrimos la hoja Junio y hacemos clic en su celda C4 y para acabar la fórmula pulsamos Enter.
Tendremos el resultado:
='[TUT-EX_22_2º TRIMESTRE.xlsx]Marzo'!$C$4+'[TUT-EX_22_2º TRIMESTRE.xlsx]Febrero'!$C$4+'[TUT-EX_22_2º TRIMESTRE.xlsx]Marzo'!$C$4+'[TUT-EX_22_3er TRIMESTRE.xlsx]Junio'!$C$4+'[TUT-EX_22_3er TRIMESTRE.xlsx]Mayo'!$C$4+'[TUT-EX_22_3er TRIMESTRE.xlsx]Junio'!$C$4+'[TUT-EX_22_1er TRIMESTRE.xlsx]Diciembre'!$C$4+'[TUT-EX_22_1er TRIMESTRE.xlsx]Noviembre'!$C$4+'[TUT-EX_22_1er TRIMESTRE.xlsx]Diciembre'!$C$4
Para obtener el valor que corresponde a la celda D4 del libro TUT-EX_22_ANUAL.xlsx habremos de repetir el proceso anterior.
Nos situamos en la celda D4 de la Hoja1 del libro TUT-EX_22_ANUAL.xlsx y escribimos el signo = para iniciar la fórmula. En el libro TUT-EX_22_1er TRIMESTRE.xlsx hacemos clic en la celda E4 de la hoja Octubre y pulsamos +. Abrimos la hoja Noviembre y hacemos clic en la celda E4 y pulsamos +. Abrimos la hoja Diciembre y hacemos clic en la celda E4 y pulsamos +.
En el libro TUT-EX_22_2º TRIMESTRE.xlsx hacemos clic en la celda E4 de la hoja Enero y luego pulsamos +. Abrimos la celda Febrero y hacemos clic en su celda E4 y pulsamos +. Abrimos la hoja Marzo y hacemos clic en su celda E4 y pulsamos +.
En el libro TUT-EX_22_3er TRIMESTRE.xlsx abrimos la hoja Abril y hacemos clic en su celda E4 y pulsamos +. Abrimos la hoja Mayo y hacemos clic en su celda E4 y pulsamos +. Abrimos la hoja Junio y hacemos clic en su celda E4 y para acabar la fórmula pulsamos Enter.
Obtendremos la fórmula:
='[TUT-EX_22_1er TRIMESTRE.xlsx]Octubre'!$E$4+'[TUT-EX_22_1er TRIMESTRE.xlsx]Noviembre'!$E$4+'[TUT-EX_22_1er TRIMESTRE.xlsx]Diciembre'!$E$4+'[TUT-EX_22_2º TRIMESTRE.xlsx]Marzo'!$E$4+'[TUT-EX_22_2º TRIMESTRE.xlsx]Febrero'!$E$4+'[TUT-EX_22_2º TRIMESTRE.xlsx]Marzo'!$E$4+'[TUT-EX_22_3er TRIMESTRE.xlsx]Junio'!$E$4+'[TUT-EX_22_3er TRIMESTRE.xlsx]Mayo'!$E$4+'[TUT-EX_22_3er TRIMESTRE.xlsx]Junio'!$E$4
A continuación, con el control de relleno arrastraremos hasta la fila 12, y veremos que en todas las celdas aparece el mismo resultado a que las referencias a las celdas C4 y D4 son absolutas ($C$4 y $E$4).
Para solucionar esto, haremos clic en la barra de fórmulas de la celda C4 del libro TUT-EX_22_ANUAL.xlsx y eliminaremos todos los símbolos de dólar ($) que aparecen en la fórmula y luego hacemos pulsamos la tecla Enter.
Repetimos la operación con la celda D4 del libro TUT-EX_22_ANUAL.xlsx
Si ahora volvemos a arrastrar el rango C4:D4 hasta la fila 12 obtendremos el resultado deseado.
No hay comentarios:
Publicar un comentario