- Descargar el libro de trabajo
- Descargar el libro con datos externos
- Suscribirse al canal de YouTube de ExcelTotal
Transcripción del video
Cuando tenemos diferentes libros u hojas de Excel con datos que necesitamos consolidar en un solo rango, podemos usar algunas herramientas de Excel para simplificar esta tarea.
Para nuestro primer ejemplo, debemos cumplir un requisito, y es que los datos deberán tener las mismas columnas y filas de manera que puedan ser integrados adecuadamente por la fórmula que vamos a crear.
En este libro tenemos las hojas Tienda-1, Tienda-2 y Tienda-3 que son los reportes de venta de 10 productos en los primeros 3 meses del año.
Toda nuestra información está en diferentes hojas, y vamos a usar una fórmula para integrar los datos en la hoja Suma.
Ya que nos interesa sumar los datos de cada hoja, usaremos la función SUMA y la primera fórmula la vamos a colocar en la celda B2 ya que esa es la ubicación del producto 1 para el mes de enero en todas nuestras hojas.
Puedes notar que en todas las hojas tenemos esa información en la misma celda B2, así que, regresaré a la hoja Suma y activaré la celda B2 para ingresar la fórmula.
Ingreso el símbolo igual y en seguida la función SUMA y con el ratón voy a seleccionar la celda B2 de la hoja Tienda-1 y aquí es donde viene la parte importante de este ejemplo.
Voy a pulsar la tecla Shift y en seguida haré clic en la pestaña de la hoja Tienda-3 y quiero que observes que se han seleccionado las 3 hojas de las tiendas y en nuestra fórmula tenemos una referencia como Tienda-1, dos puntos, Tienda-3 lo cual le pide a la función SUMA que haga la suma de la celda B2 desde la hoja Tienda-1 y hasta la hoja Tienda-3.
Voy a cerrar el paréntesis de la función SUMA y al pulsar Enter obtendremos como resultado el número 246.
Hagamos una comprobación rápida de este resultado.
En la hoja Tienda-1 tenemos el valor 67 y en la hoja Tienda-2 tenemos el valor 90, que al sumarlos nos dará el valor 157.
Finalmente, en la hoja Tienda-3 tenemos el número 89 que sumado a 157 nos dará 246 que es precisamente el valor que nos ha devuelto la función SUMA que acabamos de crear en la hoja Suma.
Para obtener el resto de los valores simplemente arrastraré la fórmula hacia abajo, hasta la fila 11 de la hoja, y además la copiaré 2 columnas a la derecha para considerar los meses febrero y marzo que tenemos en los datos.
La referencia que acabamos de crear es conocida en Excel como Referencia 3D y nos ayuda a incluir en nuestros cálculos la misma celda de diferentes hojas.
Dado el comportamiento de las referencias 3D, es necesario tener los datos en la misma ubicación o de lo contrario no podríamos usar la fórmula.
Hasta ahora he usado los datos de diferentes hojas en el mismo libro, pero también podríamos incluir datos de un libro externo.
Por ejemplo, tengo abierto otro libro de Excel llamado Mas Datos y que tiene 2 hojas, la hoja Tienda-4 y la hoja Tienda-5.
Son datos de los mismos productos y meses, pero que se encuentran en otro libro.
Si queremos incluirlos en los cálculos de la hoja Suma debemos modificar la fórmula de la siguiente manera.
Editaré la fórmula de la celda B2 agregando una coma al final de la función lo cual me permitirá indicar un rango adicional de suma y en seguida iré al libro Mas Datos y haré clic en la celda B2 de la hoja Tienda-4.
Al igual que lo hicimos hace unos instantes, pulsaré la tecla Shift y haré clic en la hoja Tienda-5 lo cual creará la referencia 3D para los datos que se encuentran en el libro Mas Datos.
Puedes notar que la diferencia con la primera referencia 3D es que se ha incluido el nombre del libro Mas Datos punto XLSX encerrado entre corchetes.
Antes de concluir, voy a remover los símbolos de moneda de la referencia a la celda B2 porque quiero que sea relativa, ya que posteriormente copiaremos esta fórmula a las celdas adyacentes.
Al pulsar le tecla Entrar obtendremos la suma que incluyen los datos de las hojas de este libro y del libro externo.
Para finalizar copiaré la fórmula hacia abajo y también hacia la derecha para tener la suma de los datos de las 5 hojas. Tres de ellas están en el mismo libro y 2 hojas se encuentran en un libro externo.
De esta manera podemos usar las referencias 3D para consolidar los datos en Excel.
Sin embargo, tal como lo mencioné anteriormente, la desventaja de este método es que necesitamos tener los datos en la misma ubicación y en caso de que no podamos cumplir con esa condición entonces tendremos que recurrir al comando Consolidar que se encuentra en la pestaña Datos y dentro del grupo Herramientas de datos.
Con el comando Consolidar podremos hacer la consolidación aún cuando los datos estén en una ubicación diferente y para demostrarlo usaremos la hoja Tienda-3B que tiene los mismos datos de la hoja Tienda-3 pero los he movido dos columnas a la derecha y cuatro filas hacia abajo.
Iré a la hoja Consolidar y activaré la celda A1 que es donde quiero que inicien mis datos consolidados.
En seguida iré a la pestaña Datos y dentro del grupo Herramientas de datos, haré clic en el botón Consolidar y se abrirá el cuadro de diálogo consolidar.
La primera lista desplegable nos permite elegir la función que será usada al momento de hacer la consolidación de los datos y en este momento tenemos activa la función Suma.
Puedes ver que la lista tiene otras opciones como el recuento o el promedio, los valores máximo y mínimo, el producto, etcétera.
La segunda opción es el recuadro Referencia que nos permitirá indicar cada uno de los rangos que vamos a consolidar.
Puedes escribir directamente la referencia o hacer clic en el botón de selección de rango. Yo haré clic en este botón e iré a la hoja Tienda-1 y seleccionaré el rango A1:D11 que contiene los datos y de nuevo pulsaré el botón de selección de rango para regresar al cuadro de diálogo y para finalizar pulsaré el botón Agregar.
Puedes notar que el recuadro Todas las referencias ya muestra el rango de la hoja Tienda-1 que acabamos de agregar.
Para agregar los datos de la hoja Tienda-2, haré clic en el botón de selección de rango y en seguida iré a la hoja Tienda-2 y seleccionaré el rango A1:D11.
Pulso el botón de selección de rango y de nuevo el botón Agregar para que se agreguen los datos de la hoja Tienda-2.
Este proceso lo tenemos que repetir para cada uno de los rangos, así que haré clic en el botón de selección de rango y en la hoja Tienda-3B y en seguida seleccionaré el rango C5:F15 y regresaré al cuadro de diálogo para agregar este nuevo rango de datos.
Ya tenemos los datos de este libro, y ahora me faltan las hojas del libro externo, así que de nuevo haré clic en el botón de selección de rango y en seguida activaré el libro Más Datos y elegiré el rango A1:D11 de la hoja Tienda-4 y regresaré al cuadro de diálogo y terminaré pulsando el botón Agregar.
Voy a repetir esta acción para los datos de la última hoja, hago clic en el botón de selección de rango y activo la hoja Tienda-5 y selecciono el rango A1:D11 y finalmente regreso al cuadro de diálogo para pulsar el botón Agregar.
Con esto hemos terminado de agregar todos los rangos que queremos consolidar y solo nos queda indicar algunas opciones para el comando consolidar y que son los rótulos.
En este caso nuestros datos tienen encabezados de columna y también tienen datos en la primera columna con los nombres de productos y por lo tanto le indicaré al comando Consolidar que se usen rótulos en la fila superior y en la columna izquierda.
La opción crear vínculos la dejaremos para el próximo ejemplo, así que haré clic en el botón Aceptar y Excel hará la consolidación de datos.
Observa que tenemos los mismos 10 productos y las 3 columnas de los datos originales.
Si activamos la hoja Suma, donde ya habíamos hecho la consolidación con una fórmula, podrás comprobar que los resultados son idénticos, con la diferencia que el comando Consolidar nos ha permitido incluir los datos de la hoja Tienda-3B los cuales están en una ubicación diferente al resto de los datos.
Ahora voy a mostrarte el objetivo de la opción “Crear vínculos con los datos de origen” que dejamos inactiva en este último ejemplo, pero antes debo mostrarte el comportamiento de los datos consolidados cuando actualizamos los datos de origen.
Observa que, en este momento, la celda B2 tiene el valor 359 que es la suma de todos los valores para el producto 1 en el mes de enero considerando las hojas de este libro y del libro externo.
A continuación, activaré la hoja Tienda-1 y en la celda B2 reemplazaré el número 67 por el número mil y al activar de nuevo la hoja Suma, podrás notar que ahora tenemos el valor 1292 en la celda B2 porque ya está considerando la actualización que acabamos de realizar.
Sin embargo, al activar la hoja Consolidar, la celda B2 aún tiene el valor 359 que era el valor anterior, es decir, no se ha actualizado al momento de modificar los datos de origen.
Cuando usamos el comando Consolidar y necesitamos que los datos queden vinculados con los datos de origen, debemos recordar activar la opción “Crear vínculos con los datos de origen”.
Hagamos ese ejemplo, y para eso activaré la celda G15 para hacer de nuevo la consolidación de datos. En un instante te explicaré porqué he activado la celda G15.
Ahora pulsaré el botón Consolidar de la pestaña Datos y puedes notar que Excel recuerda los rangos que acabamos de usar anteriormente.
Si por alguna razón a ti no te aparecen las referencias, tendrás que hacer todo el proceso para agregarlas de nuevo.
Yo activaré la caja de selección “Crear vínculos con los datos de origen” y en seguida el botón Aceptar.
La razón por la cual hice la nueva consolidación en la celda G1 fue porque, al haber pedido que se vincularan los datos de origen, Excel ha agrupado las filas de la hoja y también ha usado una columna adicional, y por lo tanto no quería impactar el rango de datos que consolidamos anteriormente en la parte superior izquierda de la hoja.
Observa que, al hacer clic en cualquiera de los grupos de filas creados por el comando Consolidar, se mostrarán los valores de cada una de las hojas que dan forma al total consolidado.
En el primer grupo puedes notar el valor mil que modificamos previamente en la hoja Tienda-1.
Para comprobar que los datos están vinculados, activaré la hoja Tienda-1 y colocaré el valor 2 mil en lugar de mil y al regresar a la hoja Consolidar veremos reflejado el número 2 mil para la primera hoja y por lo tanto el dato consolidado ahora se muestra como 2292.
Para finalizar con la revisión del comando Consolidar, voy a mostrarte un ejemplo un tanto diferente al anterior pero que nos ayudará a conocer todo el potencial de este comando.
En la hoja Tienda-A tengo datos similares a los ejemplos anteriores pero la diferencia es que en esta ocasión tengo productos repetidos, por ejemplo, puedes ver que existen dos filas para el producto 4 y tres filas para el producto 11.
En esta hoja tenemos los datos para el mes de enero y febrero.
En seguida activaré la hoja Tienda-B y de igual manera puedes ver productos repetidos como el producto 9 o el producto 11 y, a diferencia de la hoja anterior, aquí tenemos datos para el mes de febrero y marzo.
La consolidación que vamos a hacer nos devolverá una lista de productos únicos y además tres columnas para los meses enero, febrero y marzo.
Hagamos dicha consolidación.
Activaré la hoja Consolidar-2 y activaré la celda A1 y en la pestaña Datos pulsaré el comando Consolidar.
Para la primera referencia seleccionaré los datos de la hoja Tienda-A y regresaré al cuadro de diálogo para pulsar el botón Agregar.
De nuevo pulsaré el botón de selección de rango y seleccionaré los datos de la hoja Tienda-B para agregarlo a la lista de referencias.
Finalmente activaré las casillas fila superior y columna izquierda y pulsaré el botón Aceptar.
Como resultado, Excel nos devolverá la lista de productos únicos y las columnas para los meses enero, febrero y marzo.
Puedes comprobar que la columna enero proviene de la hoja Tienda-A y la columna marzo proviene de la hoja Tienda-B, pero la columna febrero consolida los datos de ambas hojas.
De esta manera hemos consolidado los datos removiendo los productos repetidos y combinando los datos de todas las columnas.
Como último consejo, después de haber hecho una consolidación de este tipo, tendremos celdas vacías para aquellos datos que no tienen información en determinadas columnas.
Podemos colocar el número cero en todas ellas de una manera sencilla, solo seleccionamos el rango de datos y en la pestaña Inicio, pulsamos el botón Buscar y seleccionar y elegimos la opción Ir a Especial y dentro de este cuadro de diálogo elegiré la opción Celdas en blanco y pulsaré el botón Aceptar.
En la Barra de fórmulas ingresaré el número cero y pulsaré el atajo Ctrl + Enter para insertar el mismo valor en todas las celdas seleccionadas.
Espero que estos ejemplos hayan sido de utilidad para comprender estas dos opciones de consolidación de datos en Excel que hemos revisado: las referencias 3D y el comando Consolidar.