Análisis de datos avanzado: consolidación de datos

Es posible que se haya encontrado con diferentes situaciones en las que tenga que presentar datos consolidados. La fuente de los datos puede ser de un lugar o de varios lugares. Otro desafío podría ser que otras personas actualicen los datos de vez en cuando.

Necesita saber cómo puede configurar una hoja de trabajo de resumen que consolide los datos de las fuentes que configure, cuando lo desee. En Excel, puede realizar esta tarea fácilmente en unos pocos pasos con elData Tool – Consolidate.

Preparación de datos para la consolidación

Antes de comenzar a consolidar los datos, asegúrese de que haya coherencia entre las fuentes de datos. Esto significa que los datos se organizan de la siguiente manera:

  • Cada rango de datos está en una hoja de trabajo separada.

  • Cada rango de datos está en formato de lista, con etiquetas en la primera fila.

  • Además, puede tener etiquetas para las categorías, si corresponde, en la primera columna.

  • Todos los rangos de datos tienen el mismo diseño.

  • Todos los rangos de datos contienen hechos similares.

  • No hay filas o columnas en blanco dentro de cada rango.

En caso de que las fuentes de datos sean externas, asegúrese de utilizar un diseño predefinido en forma de plantilla de Excel.

Suponga que tiene los datos de ventas de varios productos básicos de cada una de las regiones: este, norte, sur y oeste. Es posible que deba consolidar estos datos y presentar un resumen de ventas de producto de vez en cuando. La preparación incluye lo siguiente:

  • Una hoja de trabajo por región, es decir, cuatro hojas de trabajo con los nombres Este, Norte, Sur y Oeste. Estos pueden estar en el mismo libro de trabajo o en libros de trabajo diferentes.

  • Cada hoja de trabajo tiene el mismo diseño, que representa los detalles del producto, el número de unidades y la cantidad.

  • Necesita consolidar el producto de datos. Por lo tanto, asegúrese de que la columna con la etiqueta Producto sea la primera columna y contenga las etiquetas del Producto.

Consolidar datos en el mismo libro de trabajo

Si tiene todos los datos, que tiene que consolidar, en el mismo libro de trabajo, proceda de la siguiente manera:

Step 1 - Asegúrese de que los datos de cada región estén en una hoja de trabajo separada.

Step 2 - Agregue una nueva hoja de trabajo y asígnele el nombre Resumen.

Step 3 - Haga clic en la hoja de trabajo Resumen.

Step 4 - Haga clic en la celda donde desea colocar el resumen de resultados.

Step 5 - Haga clic en el DATA pestaña de la cinta.

Step 6 - Haga clic en el Consolidate botón en el Data Tools grupo.

los Consolidate aparece el cuadro de diálogo.

Step 7 - Seleccionar Sum de la lista desplegable debajo Function.

Step 8 - Seleccione los datos de cada hoja de trabajo de la siguiente manera.

  • Haga clic en el icono en el cuadro debajo de Referencia.
  • Seleccione la hoja de trabajo - Este.
  • Seleccione el rango de datos.
  • Nuevamente, haga clic en el icono en el cuadro debajo de Referencia.

El rango seleccionado aparece en el cuadro Referencia -

Step 9 - Haga clic en el Addbotón a la derecha del cuadro. El rango de datos seleccionado aparece en el cuadro debajoAll References.

Step 10- Repita los pasos del 1 al 5 para el resto de las hojas de trabajo de datos: norte, sur y oeste. El cuadro de diálogo Consolidar tiene el siguiente aspecto.

Puede ver que los rangos de datos aparecen en la hoja de trabajo en orden alfabético, en el cuadro debajo All references.

Step 11 - Marque las casillas Top row y Left column debajo Use labels in. Haga clic en Aceptar.

Sus datos se resumen en términos de productos para las regiones: este, norte, sur y oeste.

Puede repetir los pasos indicados anteriormente para actualizar los resultados de su resumen manualmente, siempre que los necesite.

Consolidar datos automáticamente

Suponga que desea que su hoja de resumen se actualice automáticamente, siempre que haya cambios en los datos. Para lograr esto, necesita tener enlaces a los datos de origen.

Step 1 - Marque la casilla - Create links to source data en el cuadro de diálogo Consolidar y haga clic en Aceptar.

Los resultados de su resumen aparecen con un esquema de la siguiente manera:

Observará que se inserta una nueva columna a la derecha de la columna denominada Producto.

Step 2- Haga clic en el signo + en el contorno de la fila que contiene el valor del Producto denominado Jabón. Puede ver que la nueva columna contiene el valor consolidado para cada conjunto de valores de producto, por región.

Consolidar datos de diferentes libros de trabajo

En el ejemplo anterior, todos los datos que necesita resumir están en el mismo libro de trabajo. Sin embargo, es probable que los datos se mantengan por separado para cada región y se actualicen por región. En tal caso, puede consolidar los datos de la siguiente manera:

Step 1 - Abra los libros de trabajo que contienen los datos, por ejemplo, libros de trabajo: East-Sales, North-Sales, South-Sales y West-Sales.

Step 2 - Abra un nuevo libro de trabajo.

Step 3 - En una nueva hoja de trabajo, haga clic en la celda donde desea que aparezca el resumen.

Step 4 - Haga clic en la pestaña DATOS en la cinta.

Step 5 - Haga clic en Consolidar en el cuadro Herramientas de datos.

UNA Consolidateaparece el cuadro de diálogo. En el cuadro de diálogo Consolidar:

  • Seleccione Suma de la lista desplegable en el cuadro debajo de Función.
  • Haga clic en el icono en el cuadro debajo Reference.
  • Seleccione el libro de trabajo: East-Sales.xlsx.
  • Seleccione el rango de datos.
  • Nuevamente, haga clic en el icono en el cuadro debajo de Referencia.
  • Haga clic en el Add botón a la derecha.

El cuadro de diálogo Consolidar tiene el siguiente aspecto:

  • Haga clic en el icono a la derecha del cuadro debajo de Referencias.
  • Seleccione el libro de trabajo: North-Sales.xlsx.
  • Seleccione el rango de datos.
  • Nuevamente, haga clic en el icono a la derecha del cuadro debajo de Referencias.
  • Haga clic en Agregar.

Step 6 - Repita los pasos 1 a 6 para agregar los rangos de datos de los libros de trabajo: South-Sales.xlsx y West-Sales.xlsx.

Step 7 - Debajo Use labels in, marque las siguientes casillas.

  • Fila superior.
  • Columna izquierda.

Step 8 - Marque la casilla Create links to source data.

Su cuadro de diálogo Consolidar tiene el siguiente aspecto:

Sus datos se resumen en su libro de trabajo.