CONSOLIDACIÓN DE DATOS Y TABLAS DINÁMICAS EN EXCEL

¿QUE ES CONSOLIDACIÓN DE DATOS EN EXCEL?

Consolidar datos consiste en combinar los valores de varios rangos de datos en uno solo. Por ejemplo, si tienes una hoja de cálculo de cifras de gastos para cada una de sus oficinas regionales, puedes utilizar una consolidación para reunir estas cifras en una hoja de cálculo de gastos para toda la organización; o si tienes las ventas de cada uno de los vendedores puedes agruparlas en una sola hoja para calcular el total de ventas de toda la compañía.

COMO CONSOLIDAR DATOS EN EXCEL

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.

Una vez especificada la referencia se debe pulsar el botón Agregar. De la misma manera se deben agregar cada una de las hojas hasta tener todas las referencias enlistadas. Finalmente debo marcar los cuadros de selección de rótulos en la Fila superior y en Columna Izquierda de manera que los datos sean presentados adecuadamente.

Agregar todas las referencias a consolidar

Al pulsar el botón Aceptar, Excel realizará la consolidación de los datos y los colocará en la nueva hoja de Excel.

De esta manera la información que antes estaba en diferentes hojas de Excel ha sido consolidada correctamente en una sola hoja

¿QUE SON LAS TABLAS DINÁMICAS EN EXCEL?

Las tablas dinámicas en Excel son un tipo de tablaque nos permiten decidir con facilidad los campos que aparecerán como columnas, como filas y como valores de la tabla y podemos hacer modificaciones a dicha definición en el momento que lo deseemos.

COMO HACER TABLAS DINÁMICAS EN EXCEL

Las tablas dinámicas en Excel reciben su nombre por su capacidad de cambiar dinámicamente la información agrupada con tan solo rotar las columnas o filas de la tabla. En esta ocasión veremos un ejemplo claro de cómo crearlas.

Crear una tabla dinámica en Excel

Haz clic sobre cualquier celda de la tabla de datos que se desea considerar en la nueva tabla dinámica.

Tabla de datos para crear una tabla dinámica

Ahora selecciona el comando Tabla dinámica que se encuentra dentro del grupo Tablas de la ficha Insertar.

Se mostrará el cuadro de diálogo Crear tabla dinámica. Si es necesario podrás ajustar el rango de datos que se considerará en la tabla dinámica.

En este mismo cuadro de diálogo se puede elegir si se desea colocar la tabla dinámica en una nueva hoja de Excel o en una ya existente. Haz clic en el botón Aceptar y se creará la nueva tabla dinámica.

Excel agregará en la parte izquierda del libro la tabla dinámica y en la parte derecha la lista de campos. Esta lista de campos está dividida en dos secciones, primero la lista de todos los campos de los cuales podremos elegir y por debajo una zona a donde arrastraremos los campos que darán forma al reporte ya sea como columna, fila, valor o como un filtro.

Para completar la tabla dinámica debemos arrastrar los campos al área correspondiente. Siguiendo el ejemplo propuesto del artículo anterior, colocaré como columna el campo Producto y como fila al campo Ciudad. Finalmente como valores colocaré el campo Ventas.

De manera predeterminada Excel aplica la función SUMA a los valores y la tabla dinámica que resulta después de hacer esta configuración es la siguiente:

Utilizando una tabla dinámica fue posible crear un reporte de una manera fácil y sin la necesidad de utilizar fórmulas. Pronto veremos cómo se pueden elaborar tablas dinámicas más complejas que permitirán realizar un análisis profundo de la información.

CONSOLIDACIÓN DE DATOS Y TABLAS DINÁMICAS

La herramienta Consolidación de datos como ya hemos visto en las entregas anteriores, 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.

para realizar consolidación de datos con tablas dinámicas, ya vimos que no se puede aplicar el método tradicional para insertarla; debemos abrir el asistente para configuración de Tablas dinámicas la cual solo encontraremos disponible desde la barra de herramientas de acceso rápido y para ello, vamos a Opciones de Excel, Barra de herramientas de acceso rápido, luego en Todos los comandos buscamos el asistente para configuración de tablas y gráficos dinámicos:

Damos Agregar y Aceptar a esta ventana de opciones. Abrimos la ventana de configuración de tablas y gráficos dinámicos que se encuentra ahora en la barra de herramientas de acceso rápido:

La primera opción es indicar donde se encuentran los datos para construir la tabla dinámica y para ello seleccionamos Rangos de consolidación múltiples:

Clic en el botón Siguiente para indicar que el número de campos de página y luego clic en el botón Siguiente para seleccionar los rangos de celdas que necesitamos consolidar: