Obtener y transformar: una alternativa a los informes simples de tabla dinámica

Me encantan las tablas dinámicas y las uso todo el tiempo. Pero, cuando nuestras necesidades son simples, podemos resumir fácilmente los datos con una consulta Obtener y transformar. ¿Por qué? Simplificar nuestros libros de trabajo y mejorar nuestra eficiencia. Déjame demostrarlo.

Objetivo

Antes de profundizar en la mecánica, dejemos claro nuestro objetivo. Digamos que cada mes exportamos datos de nuestro sistema de contabilidad a un archivo csv. Luego, importamos los datos csv a un libro de Excel copiando y pegando. Una vez que las transacciones están en un libro de Excel, creamos un informe de tabla dinámica para resumir las transacciones. Guardamos el libro de trabajo, lo cerramos y seguimos con nuestra vida. Y la vida es buena… hasta el próximo período, cuando podamos volver a realizar estos pasos manuales.

En su lugar, podemos configurar un libro de Excel que extraerá automáticamente las transacciones del archivo csv, ¡ ya resumidas! Y se puede actualizar cada mes sin un solo clic del mouse. Sin duda, eso sería más rápido que la forma manual. ¿Es esto siquiera posible? Sí, todo gracias a las consultas Power Query/Get Transform.

He incluido una breve demostración en video , así como todos los pasos detallados a continuación como referencia.

Demostración en vídeo

Pasos detallados

Cada periodo exportamos transacciones desde algún sistema, quizás sea un sistema de contabilidad, sistema de inventario, AR, AP, ERP, CRM, lo que sea. El caso es que exportamos transacciones como un archivo csv. En este ejemplo, exporté algunas transacciones de mi sistema de contabilidad y se parecen un poco a esto:

Para lograr nuestro objetivo, seguiremos estos pasos:

1. Crear la consulta básica
2. Resumir
3. Fila total
4. Actualizar al abrir

Veámoslos uno por uno.

Nota: Los pasos a continuación se presentan con Excel para Windows 2016. Si está utilizando una versión diferente de Excel, tenga en cuenta que es posible que las funciones presentadas no estén disponibles o que deba descargar e instalar el complemento Power Query.

Crear la consulta básica

Primero necesitamos crear la consulta básica. Para hacer esto, hacemos clic en el ícono de comando Datos Desde texto/CSV del grupo de cinta Obtener y transformar datos .

Después de buscar el archivo csv, Excel proporciona una vista previa rápida, como se muestra a continuación.

En este punto, hacemos clic en el botón Editar para abrir el Editor de consultas, como se muestra a continuación.

Ahora, sólo necesitamos decirle a Excel cómo resumir las transacciones antes de que se introduzcan en Excel.

Resumir

Nuestro objetivo es resumir las transacciones por cuenta y crear una columna para cada departamento. Hay cuatro pasos.

Paso 1: eliminar columnas adicionales. El primer paso es eliminar las columnas innecesarias, especialmente aquellas que tienen valores únicos en cada fila, como las columnas TransID y Date. Necesitamos eliminarlos porque impedirán que Excel resuma las transacciones como desee.

En nuestra consulta, seleccionamos las columnas TransID , AcctID , DeptID y Date manteniendo presionada la tecla Ctrl mientras hacemos clic en la etiqueta de cada columna. Luego, hacemos clic en el comando de cinta Inicio Quitar columnas .

Paso 2: pivotar. Para crear una columna para cada departamento, seleccionamos la columna NombreDepto y luego el comando de cinta Transformar Pivotar . Excel muestra el cuadro de diálogo Columna dinámica donde simplemente identificamos la columna de valores como Importe, como se muestra a continuación.

Paso 3: Pie cruzado. Necesitamos insertar una nueva columna que calcule la suma de todos los valores de departamento para cada fila de la cuenta. Para hacer esto, seleccionamos todas las columnas de departamento (manteniendo presionada la tecla Mayús) y luego hacemos clic en el comando Agregar columna Estándar Agregar . Ahora tenemos una nueva columna llamada Suma, cuyo nombre podemos cambiar si lo deseamos haciendo doble clic en la etiqueta de la columna o usando el ícono de ajustes en el cuadro de lista de pasos aplicados.

Paso 4: cerrar y cargar. Ahora, podemos hacer clic en el botón Inicio Cerrar y cargar para enviar nuestros resultados a Excel, como se muestra a continuación.

De vuelta en Excel, solo tenemos que agregar una fila total.

Fila total

Para agregar una fila total, simplemente seleccionamos la tabla de resultados y luego hacemos clic en la casilla Herramientas de tabla Diseño Fila total .

En este punto, Excel proporciona una fila total y suma la columna final de la tabla. Podemos decirle fácilmente a Excel que sume cada columna seleccionando cada celda en la fila total y seleccionando Suma en el menú desplegable como se muestra a continuación.

Después de hacer esto para cada columna, nuestro informe estará completo, como se muestra a continuación.

Ahora, la tabla de resumen resultante no es tan flexible como una tabla dinámica; por ejemplo, no tiene opciones de desglose, diseño de informe o la capacidad de hacer clic y arrastrar elementos de campo. Entonces, cuando necesitamos más de un informe, querríamos utilizar una tabla dinámica… pero… cuando nuestras necesidades son simples, esta es una buena alternativa.

Para actualizar este informe el próximo período, exportaríamos un nuevo archivo csv y reemplazaríamos el archivo csv del período anterior. Es decir, lo guardaríamos con el nombre nombre de archivo en la misma carpeta. (Nota: si desea que Excel tome todos los archivos de la carpeta, en lugar de tener que usar el mismo nombre, considere usar la opción Desde carpeta, como se explica en esta publicación).

Luego, cuando abrimos este libro de informes de Excel, simplemente hacemos clic derecho en la tabla de resultados y seleccionamos Actualizar. Excel recuperará y resumirá el contenido del archivo csv y actualizará nuestra tabla de resultados. ¡Lindo!

Nota: Si hay cuentas nuevas en el archivo csv, Excel las incluirá automáticamente. Sin embargo, un cambio en los departamentos, como un departamento nuevo o un departamento eliminado, requerirá una edición de la consulta para garantizar que la columna transversal refleje la lista de departamentos actualizada.

Si también quisiéramos eliminar el paso manual de hacer clic derecho y actualizar, podemos decirle a Excel que actualice automáticamente la tabla de resultados cuando abrimos el libro de Excel. Así es cómo.

Actualizar al abrir

Para que Excel actualice automáticamente la tabla de resultados al abrir el libro, haga clic derecho en la tabla de resultados y seleccione Tabla Propiedades de datos externos . Excel mostrará el cuadro de diálogo Propiedades de datos externos como se muestra a continuación.

A continuación, haga clic en el pequeño icono Propiedades de consulta en el lado derecho del campo Nombre para mostrar el cuadro de diálogo Propiedades de consulta, como se muestra a continuación.

Marque la casilla Actualizar datos al abrir el archivo y ¡debería estar listo! La próxima vez que abra el archivo de Excel, sin hacer clic con el mouse, Excel tomará los datos del archivo csv, los resumirá y actualizará la tabla de resultados. ¡Lindo!

Conclusión

Si tienes otros trucos divertidos de Obtener y Transformar, compártelos publicando un comentario a continuación. Y, si desea practicar estos pasos, descargue el archivo csv de datos de muestra y pruébelo.

• datos.csv