Consolidación de Excel (Tabla de contenido)
- Introducción a la consolidación en Excel
- ¿Cómo consolidar datos en varias hojas de trabajo?
Consolidar datos en Excel
La consolidación en general significa la recolección o reunión de algunas cosas comunes en un solo lugar. Podría ser cualquier tipo de datos o cualquier cosa como información o número. La consolidación en Excel se refiere a una función que combina información o datos de diferentes libros / hojas de trabajo en una sola hoja / libro de trabajo. En otras palabras, esta función toma datos de diferentes hojas de trabajo para combinarlos en una sola hoja de trabajo que se puede actualizar fácilmente.
Por ejemplo, si tiene cuatro hojas de trabajo. Cada hoja tiene gastos por un cuarto. Es posible que necesite una sola hoja con los gastos de los cuatro trimestres. Esto es cuando entra en juego la función de consolidación. Cuando los datos en las hojas de trabajo están en la misma posición, lo que significa bajo las mismas etiquetas y en el mismo orden. Usamos "Consolidación por posición". Y cuando los datos en las hojas de trabajo no están bajo las mismas etiquetas ni en el mismo orden. Luego, utilizamos "Consolidación por categoría". Es como crear una tabla dinámica.
¿Cómo consolidar datos en varias hojas de trabajo?
Comprendamos cómo consolidar datos en varias hojas de trabajo con algunos ejemplos.
Ejemplo # 1 - Consolidar datos en el mismo libro de trabajo
Digamos que tenemos una hoja de trabajo en la que tenemos datos de ventas con cuatro hojas de trabajo marcadas según su región como Norte, Sur, Este y Oeste. Ahora nos gustaría consolidar los datos en un solo lugar en lugar de una hoja dentro del mismo libro de trabajo. Hay una quinta hoja llamada archivo consolidado.
Este ejemplo mostrará las ventas consolidadas para todas las regiones. Aquí están los archivos de datos de muestra. Aquí puede ver los diferentes nombres de hojas de trabajo y el último archivo consolidado en el que tenemos que trabajar.
Esta es nuestra plantilla en la hoja de "archivo consolidado" y ahora comenzaremos a consolidar los datos de las hojas de trabajo.
Ahora haremos clic en la celda B3.
Donde queremos que la función "Consolidar" inserte los datos de otras hojas. Como podemos ver arriba, la celda B3 está seleccionada y ahora pasaremos a la pestaña Datos en la Cinta de Excel e iremos a Consolidar.
Una vez que hagamos clic en Consolidar, aparecerá la siguiente ventana:
Lo primero que vemos es el menú desplegable "Función" que muestra muchas funciones aritméticas como suma, conteo, máximo, promedio, etc. Como queremos una suma de ventas, seleccionaremos "Suma" en el menú desplegable. Ahora, iremos a la pestaña de referencia para agregar una referencia a nuestros datos de diferentes hojas de trabajo. Luego iremos a nuestra primera hoja que contiene los datos de ventas de North. Solo tenemos que seleccionar datos de ventas y no encabezados y filas. Esto se muestra a continuación.
La referencia se muestra en el cuadro "Referencia" como este.
Haga clic en "Agregar" y la referencia se agregará en el cuadro "Todas las referencias".
De la misma manera, ahora agregaremos una referencia de todas las otras hojas como Norte, Sur, Este y Oeste. Una vez que hayamos terminado de agregar las referencias, haga clic en "Aceptar".
Ahora hemos consolidado los datos de ventas para los ejecutivos mensualmente, a nivel nacional.
Ejemplo # 2 - Consolidar las ventas anuales de productos inteligentes
En esto, tenemos ventas trimestrales para los productos A, B, C y D y queremos una venta anual consolidada en términos de productos.
Aquí tenemos datos en cuatro hojas, la primera hoja con datos de ventas para Q1, luego tiene datos para Q2 seguidos de datos para Q3 y Q4. Ahora iremos a la pestaña de referencia para agregar referencias a nuestros datos de diferentes hojas de trabajo. Iremos a nuestra primera hoja que contiene los datos de ventas para el primer trimestre. Seleccionaremos los datos como se muestra a continuación.
Ahora, iremos a la pestaña Datos y haremos clic en Consolidar y aparecerá la siguiente ventana.
Haremos clic en "Agregar" y la referencia se agregará en el cuadro "Todas las referencias".
Haremos clic en "Agregar" y la referencia se agregará en el cuadro "Todas las referencias".
Haremos clic en "Agregar" y la referencia se agregará en el cuadro "Todas las referencias".
A continuación se muestra nuestra plantilla para la hoja de datos consolidada. Ahora seleccionaremos la celda B2 para obtener los datos de ventas totales de otras hojas.
Seleccionaremos la función "Suma" del menú desplegable. Del mismo modo, agregaremos referencias de las hojas Q2, Q3 y Q4. Aparecerá así. Todas las referencias de todas las hojas ahora se agregan. Haga clic en Aceptar"
Ahora tenemos nuestros datos de ventas anuales consolidadas con los totales de suma de cada producto.
En el caso, si queremos insertar la tabla consolidada en una hoja de trabajo en blanco en lugar de hacer una tabla de plantilla. Podemos hacer eso también. Tomando nuestros datos de muestra anteriores, haremos la consolidación a continuación.
No tenemos una plantilla para la tabla de consolidación y queremos obtener datos consolidados en una hoja de trabajo en blanco con etiquetas de fila y columna. Agregaremos una nueva hoja de trabajo, en nuestro caso es "Archivo consolidado".
Ahora, como antes, iremos a la pestaña Datos Ahora seleccionaremos la referencia de nuestras hojas de datos. Comenzaremos esto con la hoja "Norte" y luego procederemos con las hojas "Sur", "Este" y "Oeste". Seleccionaremos los datos como se muestra a continuación, que incluye etiquetas de fila y columna. Luego agregaremos la referencia en el cuadro "Todas las referencias": Ahora agregue todas las referencias de la misma manera de todas las hojas de datos. Ahora verificaremos tanto la “Fila superior” como la “Columna izquierda” y luego presionaremos OK. Ahora vemos datos de ventas consolidados con etiquetas de fila y columna. Si desea actualizar los datos consolidados cuando se actualiza la hoja individual, haga clic en el cuadro "Crear enlaces para crear datos". Si desea actualizar los datos manualmente, no marque la casilla y haga clic en Aceptar. Las celdas de la tabla consolidada ahora contienen la fórmula de suma como se muestra a continuación. Esto lo hace dinámico en la naturaleza. Esta es una tabla de consolidación dinámica. Si tenemos datos en diferentes libros y queremos consolidarlos en un solo libro. Podemos hacer esto también. Esto nos da una gran flexibilidad y facilidad. Tenemos datos de ventas trimestrales de varios productos, como A, B, C y D, en diferentes libros de trabajo, como se muestra a continuación. Una vez que tengamos todos los libros abiertos, iremos a un nuevo libro y haremos clic en la celda B3. Agregue las referencias de todas las hojas de trabajo a continuación. Hemos consolidado los datos de todos los libros de trabajo en un nuevo libro de trabajo. Además, cualquier cambio en los datos de cualquier libro de trabajo también se actualizará en el nuevo libro de datos consolidado. Así que hemos aprendido cómo usar la función de consolidación en Excel con la ayuda de ejemplos. Es una función muy útil para fusionar o recopilar datos en una hoja de diferentes hojas de trabajo o libros de trabajo. Esta es una guía para la consolidación en Excel. Aquí discutimos cómo consolidar datos en varias hojas de trabajo junto con ejemplos prácticos y una plantilla de Excel descargable. También puede consultar nuestros otros artículos relacionados:
Ejemplo # 3 - Consolidando datos de diferentes libros de trabajo
Cosas para recordar sobre la consolidación en Excel
Artículos recomendados