Contenido
Formulación del problema
Veamos una hermosa solución para una de las situaciones más comunes que la mayoría de los usuarios de Excel enfrentan tarde o temprano: necesita recopilar datos de forma rápida y automática de una gran cantidad de archivos en una tabla final.
Supongamos que tenemos la siguiente carpeta, que contiene varios archivos con datos de ciudades sucursales:
El número de archivos no importa y puede cambiar en el futuro. Cada archivo tiene una hoja llamada Ventasdonde se encuentra la tabla de datos:
El número de filas (órdenes) en las tablas, por supuesto, es diferente, pero el conjunto de columnas es estándar en todas partes.
Tarea: recopilar datos de todos los archivos en un libro con la actualización automática posterior al agregar o eliminar archivos de ciudades o filas en las tablas. De acuerdo con la tabla consolidada final, entonces será posible crear informes, tablas dinámicas, filtrar datos, etc. Lo principal es poder recopilar.
Seleccionamos armas
Para la solución, necesitamos la última versión de Excel 2016 (la funcionalidad necesaria ya está integrada de forma predeterminada) o versiones anteriores de Excel 2010-2013 con el complemento gratuito instalado Power Query de Microsoft (descárgalo aquí). Power Query es una herramienta súper flexible y súper poderosa para cargar datos en Excel desde el mundo exterior, luego extraerlos y procesarlos. Power Query es compatible con casi todas las fuentes de datos existentes, desde archivos de texto hasta SQL e incluso Facebook 🙂
Si no tiene Excel 2013 o 2016, entonces no puede seguir leyendo (es broma). En versiones anteriores de Excel, esta tarea solo se puede realizar programando una macro en Visual Basic (que es muy difícil para los principiantes) o mediante una copia manual monótona (que lleva mucho tiempo y genera errores).
Paso 1. Importe un archivo como muestra
Primero, importemos datos de un libro de trabajo como ejemplo, para que Excel "capte la idea". Para hacer esto, cree un nuevo libro en blanco y...
- si tiene Excel 2016, abra la pestaña Datos y luego Crear consulta – Desde archivo – Desde libro (Datos — Nueva consulta - Desde archivo — Desde Excel)
- si tiene Excel 2010-2013 con el complemento Power Query instalado, abra la pestaña Power Query y seleccione en él Del archivo – Del libro (Desde archivo — Desde Excel)
Luego, en la ventana que se abre, vaya a nuestra carpeta con informes y seleccione cualquiera de los archivos de la ciudad (no importa cuál, porque todos son típicos). Después de un par de segundos, debería aparecer la ventana Navegador, donde debe seleccionar la hoja que necesitamos (Ventas) en el lado izquierdo, y su contenido se mostrará en el lado derecho:
Si hace clic en el botón en la esquina inferior derecha de esta ventana Descargar (Carga), la tabla se importará inmediatamente a la hoja en su forma original. Para un solo archivo, esto es bueno, pero necesitamos cargar muchos de esos archivos, por lo que iremos un poco diferente y haremos clic en el botón Corrección (Edit). Después de eso, el editor de consultas de Power Query debería mostrarse en una ventana separada con nuestros datos del libro:
Esta es una herramienta muy poderosa que le permite "terminar" la tabla a la vista que necesitamos. Incluso una descripción superficial de todas sus funciones tomaría alrededor de cien páginas, pero, aunque sea muy brevemente, usando esta ventana puede:
- filtrar datos innecesarios, líneas vacías, líneas con errores
- ordenar datos por una o más columnas
- deshacerse de la repetición
- dividir el texto adhesivo por columnas (por delimitadores, número de caracteres, etc.)
- poner el texto en orden (eliminar espacios adicionales, mayúsculas y minúsculas correctas, etc.)
- convertir tipos de datos de todas las formas posibles (convertir números como texto en números normales y viceversa)
- transponer (girar) tablas y expandir tablas cruzadas bidimensionales en tablas planas
- agregue columnas adicionales a la tabla y use fórmulas y funciones en ellas usando el lenguaje M integrado en Power Query.
- ...
Por ejemplo, agreguemos una columna con el nombre de texto del mes a nuestra tabla, para que luego sea más fácil crear informes de tablas dinámicas. Para hacer esto, haga clic derecho en el encabezado de la columna datosy seleccione el comando Columna duplicada (columna duplicada)y luego haga clic derecho en el encabezado de la columna duplicada que aparece y seleccione Comandos Transformar – Mes – Nombre del mes:
Se debe formar una nueva columna con los nombres de texto del mes para cada fila. Al hacer doble clic en el encabezado de una columna, puede cambiarle el nombre de Fecha de copia a una mas comoda Mes, p.ej.
Si en algunas columnas el programa no reconoció correctamente el tipo de datos, puede ayudarlo haciendo clic en el ícono de formato en el lado izquierdo de cada columna:
Puede excluir líneas con errores o líneas vacías, así como gerentes o clientes innecesarios, utilizando un filtro simple:
Además, todas las transformaciones realizadas se fijan en el panel derecho, donde siempre se pueden revertir (cruzar) o cambiar sus parámetros (engranaje):
Ligero y elegante, ¿no?
Paso 2. Transformemos nuestra solicitud en una función
Para poder repetir posteriormente todas las transformaciones de datos realizadas para cada libro importado, necesitamos convertir nuestra solicitud creada en una función, que luego se aplicará, a su vez, a todos nuestros archivos. Hacer esto es realmente muy simple.
En el Editor de consultas, vaya a la pestaña Ver y haga clic en el botón Editor avanzado (Ver — Editor avanzado). Debería abrirse una ventana donde todas nuestras acciones anteriores estarán escritas en forma de código en el lenguaje M. Tenga en cuenta que la ruta al archivo que importamos para el ejemplo está codificada en el código:
Ahora hagamos un par de ajustes:
Su significado es simple: la primera línea (ruta de archivo) => convierte nuestro procedimiento en una función con un argumento ruta de archivo, y debajo cambiamos la ruta fija al valor de esta variable.
Todos. Haga clic en Acabado y debería ver esto:
No tenga miedo de que los datos hayan desaparecido; de hecho, todo está bien, todo debería verse así 🙂 Hemos creado con éxito nuestra función personalizada, donde se recuerda todo el algoritmo para importar y procesar datos sin estar vinculado a un archivo específico . Queda por darle un nombre más comprensible (por ejemplo obtener datos) en el panel de la derecha en el campo Nombre y puedes cosechar Inicio — Cerrar y descargar (Inicio — Cerrar y Cargar). Tenga en cuenta que la ruta al archivo que importamos para el ejemplo está codificada en el código. Volverá a la ventana principal de Microsoft Excel, pero a la derecha debería aparecer un panel con la conexión creada a nuestra función:
Paso 3. Recopilación de todos los archivos
Todo lo más difícil ha quedado atrás, queda lo agradable y lo fácil. Ir a la pestaña Datos – Crear consulta – Desde archivo – Desde carpeta (Datos — Nueva consulta — Desde archivo — Desde carpeta) o, si tiene Excel 2010-2013, de manera similar a la pestaña Power Query. En la ventana que aparece, especifique la carpeta donde se encuentran todos nuestros archivos de ciudades de origen y haga clic en OK. El siguiente paso debería abrir una ventana donde se enumerarán todos los archivos de Excel que se encuentran en esta carpeta (y sus subcarpetas) y los detalles de cada uno de ellos:
Haga Clic en Cambios (Edit) y de nuevo entramos en la ventana familiar del editor de consultas.
Ahora necesitamos agregar otra columna a nuestra tabla con nuestra función creada, que "extraerá" los datos de cada archivo. Para hacer esto, vaya a la pestaña Agregar columna: columna personalizada (Agregar columna — Agregar columna personalizada) y en la ventana que aparece, ingrese nuestra función obtener datos, especificando como argumento la ruta completa a cada archivo:
Después de hacer clic en OK la columna creada debe agregarse a nuestra tabla a la derecha.
Ahora eliminemos todas las columnas innecesarias (como en Excel, usando el botón derecho del mouse – Eliminar), dejando solo la columna agregada y la columna con el nombre del archivo, porque este nombre (más precisamente, la ciudad) será útil para tener en el total de datos para cada fila.
Y ahora el "momento sorpresa": haga clic en el icono con sus propias flechas en la esquina superior derecha de la columna agregada con nuestra función:
… desmarcar Utilice el nombre de la columna original como prefijo (Utilice el nombre de la columna original como prefijo)y haga clic OK. Y nuestra función cargará y procesará los datos de cada archivo, siguiendo el algoritmo registrado y recopilando todo en una tabla común:
Para una belleza completa, también puede eliminar las extensiones .xlsx de la primera columna con nombres de archivo, mediante el reemplazo estándar con "nada" (haga clic con el botón derecho en el encabezado de la columna: Suplente) y cambie el nombre de esta columna a Ciudad. Y también corrija el formato de datos en la columna con la fecha.
¡Todos! Haga clic en Inicio – Cerrar y Cargar (Inicio — Cerrar y cargar). Todos los datos recopilados por la consulta para todas las ciudades se cargarán en la hoja de Excel actual en el formato de "tabla inteligente":
La conexión creada y nuestra función de ensamblaje no necesitan guardarse por separado de ninguna manera; se guardan junto con el archivo actual de la forma habitual.
En el futuro, con cualquier cambio en la carpeta (agregar o quitar ciudades) o en los archivos (cambiar el número de líneas), bastará con hacer clic derecho directamente en la tabla o en la consulta en el panel derecho y seleccionar la dominio Actualizar y guardar (Actualizar) – Power Query “reconstruirá” todos los datos nuevamente en unos segundos.
PS
Enmienda. Después de las actualizaciones de enero de 2017, Power Query aprendió a recopilar libros de Excel por sí mismo, es decir, ya no es necesario crear una función separada: sucede automáticamente. Por lo tanto, el segundo paso de este artículo ya no es necesario y todo el proceso se simplifica notablemente:
- Elige Crear solicitud – Desde archivo – Desde carpeta – Seleccionar carpeta – Aceptar
- Después de que aparezca la lista de archivos, presione Cambios
- En la ventana del Editor de consultas, expanda la columna Binario con una flecha doble y seleccione el nombre de la hoja que se tomará de cada archivo
¡Y eso es todo! ¡Canción!
- Rediseño de la tabla de referencias cruzadas en una plana adecuada para construir tablas dinámicas
- Creación de un gráfico de burbujas animado en Power View
- Macro para ensamblar hojas de diferentes archivos de Excel en uno