consolidación de datos y tablas dinámicas

CONSOLIDACIÓN DE DATOS Y TABLAS DINÁMICAS

Es muy efectiva para “agrupar” la información que se encuentra distribuida en varias hojas pero no es la única opción pues contamos con la alternativa que nos da la poderosa herramienta de Tabla dinámica solo que en este caso tiene una particularidad importante.

Para insertar una Tabla dinámica se parte de uno y solo un rango de celdas (desde que los estemos haciendo con datos de Excel), es decir, la configuración “básica” de las tablas dinámicas parte de los datos de una sola hoja o de un solo rango de celdas pero al hablar de Consolidación de datos, tal como hemos visto en las entregas pasadas, la información se consolida siempre y cuando la tengamos distribuida en más de un rango o más de una hoja así que en principio, no pareciera posible que las tablas dinámicas sirvieran para realizar este operación.

TABLAS DINÁMICAS: Los informes de tablas dinámicas organizan y resumen los datos, permitiendo comparar y analizar la información mostrando diferentes vistas y permitiendo una mejor presentación y facilidad de interpretación de los datos. Además, permiten la creación de Gráficos dinámicos. 

Nota: Reciben el nombre de Dinámicas porque permiten manipular los datos de diferentes formas. 

1. Para insertar una tabla dinámica se debe seleccionar cualquier dato de la base de datos y hacer clic en la cinta Insertar, hacer clic en el icono Tabla dinámica y elegir la opción Tabla dinámica.

 2. Automáticamente Excel selecciona toda la base de datos. Si esta selección es correcta, se hace clic en Aceptar. De lo contrario, se debe seleccionar manualmente el rango de celdas y hacer clic en aceptar.

 3. Elegir dónde se desea crear el informe, en una nueva hoja de cálculo o en la hoja de cálculo donde se encuentra la Base de Datos. 

Consolidar datos en Excel

Imagina que después de recopilar la información sobre las ventas de nuestros productos en diferentes países tenemos un libro de Excel donde cada hoja tiene la información de un mes en específico.

Necesito consolidar la información en una sola hoja para poder crear mi reporte, pero ¿cómo puedo hacerlo de una manera sencilla? Para consolidar datos en Excel tenemos el comando Consolidar que nos ayuda a obtener el resultado deseado de una manera fácil. Supongamos que la información de las 3 hojas de Excel es la siguiente.

Comando Consolidar en Excel

Para consolidar los datos crearé una nueva hoja y haré clic en el botón Consolidar que se encuentra en la ficha Datos dentro del grupo Herramientas de datos.

Se mostrará el cuadro de diálogo Consolidar en donde se deberá especificar cada uno de los rangos de las diferentes hojas. Puedes hacer clic en el botón de selección de referencia para seleccionar los rangos fácilmente.

Frecuentemente necesitamos consolidar datos en Excel pero la técnica que utilicemos depende mucho del tipo de consolidación a realizar así como la manera en como se encuentre las fuentes de datos.

En esta ocasión mostraré la técnica de consolidación de datos en Excel con tablas dinámicas. Este método no solamente consolidará los datos sino que tendrás un reporte flexible al mismo tiempo sin mayor esfuerzo.

Consolidar datos en Excel

Para este ejemplo, tengo tres hojas de Excel. Cada una de las hojas tiene las ventas de los tres vendedores en la empresa en los primeros 2 meses del año.

El reporte que necesito realizar es sobre las ventas del mes de enero y febrero. Para iniciar debo abrir el Asistente para tablas y gráficos dinámicos con el atajo de teclado ALT+T+B.

Seleccionamos la opción Rangos de consolidación múltiples y también la opción Tabla dinámica. Al pulsar el botón siguiente deberé seleccionar la opción Crear un solo campo de página.

En el siguiente paso deberás agregar los rangos de cada una de las tablas en las diferentes hojas de Excel:

Finalmente hago clic en Siguiente y en el último paso selecciono la opción Hoja de cálculo nueva que significa que Excel creará una nueva hoja en mi libro donde pondrá el resultado. A continuación puedes ver los datos consolidados:

Puedes dar el detalle final a esta tabla dinámica aplicando un formato de número a las celdas.

Filtro de tabla dinámica

Adicionalmente podrás observar que el filtro de la tabla dinámica tiene las opciones Elemento1, Elemento2 y Elemento3.

Cada opción corresponde a una de las hojas que hemos consolidado, de manera que si selecciono la opción Elemento1 observaré en la tabla dinámica los resultados de venta de la Hoja1 que corresponden a Hugo:

Hemos visto la técnica para consolidar datos en Excel con tablas dinámicas. Existen otras técnicas que he descrito en artículos anteriores y que puedes encontrar en los vínculos de abajo.