Tabla dinámica de muchos archivos CSV

En esta publicación, resumiremos datos de varios archivos CSV con una tabla dinámica. Específicamente, usaremos una consulta Obtener y transformar (Power Query) para recuperar y preparar datos de numerosos archivos CSV. Luego, enviaremos los resultados de la consulta al modelo de datos (Power Pivot). Luego, crearemos nuestro informe resumido utilizando una tabla dinámica basada en el modelo de datos. ¡Y será fácil! Y divertido

Objetivo

Antes de llegar demasiado lejos, confirmemos nuestro objetivo. Tenemos 3 archivos CSV exportados desde nuestro sistema de contabilidad, uno para enero, febrero y marzo. Aquí hay un ejemplo:

Tenga en cuenta que el extracto tiene números de cuenta, pero no nombres de cuenta. Y hay números de departamento pero no nombres. Nuestro informe resumido debe mostrar los nombres de las cuentas y los departamentos, como se muestra a continuación:

Creé un video y una narrativa escrita completa con los pasos detallados a continuación.

Ahora, terminemos.

Video

Pasos detallados

Prepararemos nuestro informe siguiendo estos pasos:

• Recuperar con una consulta Obtener y transformar
• Relacionar tablas en el modelo de datos
• Resumir con una tabla dinámica

Revisaremos cada paso uno por uno.

Nota: los pasos siguientes se realizaron con Excel 2016 para Windows y utilizan funciones que no están disponibles en todas las versiones de Excel, a saber, consultas Obtener y transformar (Power Query) y el modelo de datos (Power Pivot). Además, los cuadros de diálogo y navegación pueden diferir de la versión de Excel que esté utilizando en este momento. Visite el sitio web de Microsoft para obtener más información sobre estas funciones, cómo habilitarlas y qué versiones las incluyen.

Recuperar con una consulta Obtener y transformar

Primero, necesitamos extraer los datos de todos los archivos CSV. Para ello seleccionamos el comando Datos Obtener datos Desde archivo Desde carpeta . Navegamos hasta la carpeta que contiene los archivos CSV y hacemos clic en Aceptar. Excel muestra una lista de archivos que se encuentran en la carpeta y, como queremos importarlos todos, simplemente hacemos clic en el botón Combinar y seleccionamos Combinar y cargar en en el cuadro de diálogo que se muestra a continuación.

Nota: si quisiéramos excluir algunos archivos, haríamos clic en Editar y luego aplicaríamos un filtro para eliminar otros tipos de archivos (como PDF).

En el cuadro de diálogo Combinar archivos resultante , simplemente hacemos clic en Aceptar .

Luego, le decimos a Excel que queremos agregar los datos al modelo de datos y crear solo una conexión , como se muestra a continuación.

Haga clic en Aceptar y ahora tendremos los datos de los distintos archivos CSV cargados en el modelo de datos. ¡Lindo!

Relacionar tablas en el modelo de datos

Ahora es el momento de introducir los nombres de las cuentas y los nombres de los departamentos. Tenemos el plan de cuentas almacenado en una tabla de Excel, como se muestra a continuación.

Para agregar esto al modelo de datos, seleccionamos cualquier celda de la tabla y seleccionamos Power Pivot Agregar al modelo de datos .

Nota: si el plan de cuentas está almacenado en otro lugar, como una base de datos o un archivo csv, usaríamos la ventana Power Pivot y usaríamos el comando Obtener datos externos correspondiente.

Luego, repetimos estos pasos para cargar la tabla de departamentos en el modelo de datos.

Para decirle a Excel cómo se relacionan estas tablas, necesitamos definir las relaciones. Lo hacemos haciendo clic en el comando Power Pivot Administrar . En la ventana de Power Pivot, hacemos clic en Inicio Vista de diagrama . Podemos ver nuestras tres tablas y no tienen relaciones definidas, como se muestra a continuación.

Para definir las relaciones, simplemente hacemos clic y arrastramos el campo AcctID desde la tabla DataFiles al campo AcctID relacionado en la tabla Cuentas . Luego, arrastramos y soltamos el campo DeptID de la tabla DataFiles al campo DeptID correspondiente en la tabla Departamentos . Ahora, las tablas están relacionadas, como se muestra a continuación.

Ahora, todo lo que tenemos que hacer es resumir los datos con una tabla dinámica.

Resumir con una tabla dinámica

En Excel, hacemos clic en el comando Insertar Tabla dinámica . Nos aseguramos de que la opción Usar el modelo de datos de este libro esté seleccionada, como se muestra a continuación, y hacemos clic en Aceptar.

Ahora somos libres de crear nuestro informe en cualquier estructura o forma que desee.

Por ejemplo, podemos insertar el campo NombreDepto de la tabla Departamentos en el área de diseño Filas . Luego, inserte el campo AcctName de la tabla Cuentas en el área de diseño Filas . Y finalmente, inserte el campo Cantidad de la tabla Archivos de datos en el área Valores .

Nuestro informe básico está listo y podemos aplicar cualquier cosmético que queramos. Por ejemplo, podríamos usar el comando Herramientas de tabla dinámica Diseño de informe Tabular y Subtotales Mostrar todos los subtotales en la parte inferior del grupo para crear el informe que se muestra a continuación.

¡Y lo logramos! Y mira, todo lo que hicimos fue hacer clic en algunos comandos… no necesitábamos escribir una sola fórmula… ¡guau!

Ah, sí, y la mejor parte es que el próximo mes simplemente podemos agregar el extracto de abril a la carpeta de datos y hacer clic en Datos Actualizar todo para actualizar nuestro informe

Si desea practicar, le proporcioné los archivos de muestra a continuación.

Nota: asegúrese de descomprimirlos antes de comenzar.

• Archivos de muestra: PQ2PP.zip