Consolidar datos en Excel

¿Qué es Consolidar datos en Excel?

Mientras trabajamos en Excel, tenemos muchos datos disponibles en diferentes hojas de trabajo y si queremos fusionar o consolidar datos en una sola hoja de trabajo, Excel tiene una función para hacerlo después de abrir todas las hojas de trabajo que tienen datos que deben consolidarse, el botón Consolidar es disponible en la sección de herramientas de datos de la pestaña Datos.

Si no conoce la herramienta Consolidar en Excel, este artículo le ayudará a comprender la dinámica de la herramienta Consolidar en Excel.

En común, cuando recibimos datos de diferentes personas, tenemos muchas hojas de trabajo con las que lidiar. Consolidar todo es una tarea hercúlea, especialmente para los niveladores intermedios. En la primera parte de mi carrera, obtuve datos de una amplia gama de fuentes.

Por ejemplo, estaba acostumbrado a obtener datos de ventas de 4 zonas diferentes de los jefes de zona. Cuatro zonas diferentes incluyen 4 hojas de trabajo diferentes. Al final de los datos, se me pidió que recopilara todo en una hoja de trabajo y creara una base de datos para fines de análisis. Lo estaba haciendo manualmente de la forma tradicional de copiar y pegar.

Pero he pasado algún tiempo aprendiendo cosas nuevas y consolidar herramientas es una de ellas y me ha ayudado mucho a recopilar los datos con un mínimo esfuerzo.

¿Cómo consolidar datos en Excel? (Paso a paso con el ejemplo)

Puede descargar esta plantilla de Excel de consolidación de datos aquí - Plantilla de Excel de consolidación de datos

Tengo datos de ventas de cuatro regiones diferentes en cuatro hojas de trabajo diferentes.

Cada uno contiene el mismo formato y el mismo número de filas y columnas. Necesito consolidar todas las hojas en una sola hoja llamada Resumen.

He creado la misma plantilla que está en las otras 4 hojas.

• Paso 1: Después de crear la plantilla anterior en la hoja Resumen, seleccione la celda B2 donde queremos consolidar las 4 regiones diferentes.

• Paso 2: ahora ve a Datos> Consolidar

• Paso 3: Una vez que haya seleccionado la opción Consolidar, verá la siguiente ventana.

• Paso 4: esta es la ventana que necesitamos para hacer la magia. En primer lugar, vemos la función desplegable de la lista de esta lista desplegable que necesitamos para seleccionar nuestro tipo de consolidación. Aquí debemos seleccionar la función SUM en Excel porque estamos consolidando todos los datos de ventas de la zona y creando un informe de resumen. Así que seleccione SUM en esta opción.
• Paso 5: El siguiente paso es hacer referencia a las 4 hojas de regiones diferentes. Haga clic en el cuadro de referencia y vaya a la primera hoja de toda la hoja de la zona, es decir, Sur. Después de seleccionar el rango, haga clic en el botón Agregar.

• Paso 6: Después de hacer clic en el botón Agregar, agrega las referencias a todas las referencias de celda.

• Paso 7: De manera similar, cree un enlace de Excel para todas las hojas de las demás regiones. Finalmente, se verá así.

• Paso 8: Ahora haga clic en el botón Aceptar, sus datos consolidados están listos sin ningún error.

• Paso 9: Ahora que hemos creado un informe de resumen, este no es un informe dinámico porque no hemos creado un enlace a las otras tablas. Si hay cambios, las tablas permanecen iguales y nuestro informe sale mal en este caso. Necesitamos vincular la tabla a las otras hojas para que el informe sea dinámico.
• Paso 10: En el cuadro de diálogo de consolidación, seleccione la casilla de verificación Crear vínculos a los datos de origen. Haga clic en Aceptar.

Luego completa el proceso.

• Paso 11: Ahora ha creado la función SUM. Hay un icono más (+) en el lado derecho de la hoja de cálculo, haga clic en el icono más (+) para ver la ruptura.

Otras opciones en Consolidate

1. Función: esta es la lista de todas las funciones disponibles en la herramienta Consolidar. Puede usar la función SUM, AVERAGE, COUNT, MAX, MIN en Excel, etc.
2. Referencia: Aquí es donde debemos seleccionar los rangos de diferentes hojas para consolidar los datos en una sola hoja.
3. Examinar: esta es la belleza de la opción consolidar. No solo se consolida desde el mismo libro de trabajo, sino que también puede recuperar los datos de otros libros de trabajo.
4. Agregar y eliminar: Aquí podemos agregar y eliminar todas las referencias.
5. Fila superior: use la fila superior como encabezados de fila
6. Columna izquierda: use el lado izquierdo de los datos como encabezados de columna.
7. Crear vínculos a datos de origen: esto hará que el informe sea dinámico. La opción consolidada crea la fórmula para todas las demás hojas para que el informe sea realmente dinámico.

Cosas para recordar

• En consolidación, los rangos de opciones de todas las hojas de trabajo deben ser los mismos en todas las hojas de trabajo.
• Podemos utilizar cualquier tipo de función en la opción consolidar.