contingut
Formulació del problema
Vegem una solució bonica per a una de les situacions molt estàndard a què s'enfronten la majoria dels usuaris d'Excel tard o d'hora: necessiteu recopilar dades de forma ràpida i automàtica d'un gran nombre de fitxers en una taula final.
Suposem que tenim la carpeta següent, que conté diversos fitxers amb dades de ciutats sucursals:
El nombre de fitxers no importa i pot canviar en el futur. Cada fitxer té un full anomenat Saleson es troba la taula de dades:
El nombre de files (ordres) a les taules, per descomptat, és diferent, però el conjunt de columnes és estàndard a tot arreu.
Tasca: recopilar dades de tots els fitxers en un sol llibre amb una actualització automàtica posterior en afegir o suprimir fitxers de ciutat o files a les taules. D'acord amb la taula consolidada final, llavors serà possible construir qualsevol informe, taules dinàmiques, dades de filtre-ordenació, etc. El més important és poder recollir.
Seleccionem les armes
Per a la solució, necessitem la darrera versió d'Excel 2016 (la funcionalitat necessària ja està integrada per defecte) o versions anteriors d'Excel 2010-2013 amb el complement gratuït instal·lat Power Consulta de Microsoft (descarregueu-lo aquí). Power Query és una eina súper flexible i molt potent per carregar dades a Excel des del món exterior, després eliminar-les i processar-les. Power Query admet gairebé totes les fonts de dades existents, des de fitxers de text fins a SQL i fins i tot Facebook 🙂
Si no teniu Excel 2013 o 2016, no podeu llegir més (és broma). En versions anteriors d'Excel, aquesta tasca només es pot fer programant una macro en Visual Basic (que és molt difícil per als principiants) o mitjançant una còpia manual monòtona (que triga molt de temps i genera errors).
Pas 1. Importa un fitxer com a mostra
En primer lloc, importem les dades d'un llibre com a exemple, de manera que Excel "agafi la idea". Per fer-ho, creeu un nou llibre de treball en blanc i...
- si teniu Excel 2016, obriu la pestanya dades i després Crea una consulta - Des del fitxer - Des del llibre (Dades - Nova consulta - Del fitxer - Des d'Excel)
- si teniu Excel 2010-2013 amb el complement Power Query instal·lat, obriu la pestanya Power Consulta i seleccioneu-hi Del fitxer: del llibre (Des del fitxer — Des d'Excel)
Aleshores, a la finestra que s'obre, aneu a la nostra carpeta amb informes i seleccioneu qualsevol dels fitxers de ciutat (no importa quin, perquè tots són típics). Al cap d'un parell de segons, hauria d'aparèixer la finestra del Navegador, on cal seleccionar el full que necessitem (Ventes) a la part esquerra, i el seu contingut es mostrarà a la part dreta:
Si feu clic al botó de l'extrem inferior dret d'aquesta finestra descarregar (Càrrega), llavors la taula s'importarà immediatament al full en la seva forma original. Per a un sol fitxer, això és bo, però hem de carregar molts d'aquests fitxers, així que anirem una mica diferent i farem clic al botó Correcció (Edita). Després d'això, l'editor de consultes de Power Query s'hauria de mostrar en una finestra independent amb les nostres dades del llibre:
Aquesta és una eina molt potent que us permet "acabar" la taula a la vista que necessitem. Fins i tot una descripció superficial de totes les seves funcions trigaria unes cent pàgines, però, si és molt breu, amb aquesta finestra podeu:
- filtreu dades innecessàries, línies buides, línies amb errors
- ordenar les dades per una o més columnes
- desfer-se de la repetició
- divideix el text enganxós per columnes (per delimitadors, nombre de caràcters, etc.)
- ordenar el text (eliminar espais addicionals, corregir majúscules, etc.)
- convertir els tipus de dades de totes les maneres possibles (convertir números com el text en números normals i viceversa)
- transposar (girar) taules i ampliar les taules creuades bidimensionals en taules planes
- afegir columnes addicionals a la taula i utilitzar-hi fórmules i funcions mitjançant el llenguatge M integrat a Power Query.
- ...
Per exemple, afegim una columna amb el nom de text del mes a la nostra taula, de manera que més endavant sigui més fàcil crear informes de taula dinàmica. Per fer-ho, feu clic amb el botó dret a l'encapçalament de la columna datai seleccioneu l'ordre Columna duplicada (Columna duplicada), i després feu clic amb el botó dret a la capçalera de la columna duplicada que apareix i seleccioneu Ordres Transformació - Mes - Nom del mes:
S'ha de formar una nova columna amb els noms de text del mes per a cada fila. Si feu doble clic a l'encapçalament d'una columna, podeu canviar-ne el nom Data de còpia a un més còmode mes, per exemple.
Si en algunes columnes el programa no va reconèixer correctament el tipus de dades, podeu ajudar-lo fent clic a la icona de format a la part esquerra de cada columna:
Podeu excloure línies amb errors o línies buides, així com gestors o clients innecessaris, mitjançant un filtre senzill:
A més, totes les transformacions realitzades es fixen al tauler dret, on sempre es poden revertir (creuar) o canviar els seus paràmetres (engranatge):
Lleuger i elegant, no?
Pas 2. Transformem la nostra petició en una funció
Per poder repetir posteriorment totes les transformacions de dades fetes per a cada llibre importat, hem de convertir la nostra sol·licitud creada en una funció, que s'aplicarà, al seu torn, a tots els nostres fitxers. Fer-ho és realment molt senzill.
A l'Editor de consultes, aneu a la pestanya Visualització i feu clic al botó Editor avançat (Veure — Editor avançat). S'hauria d'obrir una finestra on totes les nostres accions anteriors s'escriuran en forma de codi en llenguatge M. Tingueu en compte que el camí del fitxer que hem importat per a l'exemple està codificat en dur al codi:
Ara fem un parell d'ajustaments:
El seu significat és senzill: la primera línia (camí del fitxer) => converteix el nostre procediment en una funció amb un argument ruta del fitxer, i a continuació canviem el camí fix al valor d'aquesta variable.
Tots. Fer clic a Finish i hauria de veure això:
No tingueu por que les dades hagin desaparegut; de fet, tot està bé, tot hauria de semblar així 🙂 Hem creat correctament la nostra funció personalitzada, on es recorda tot l'algorisme per importar i processar dades sense estar lligat a un fitxer específic. . Queda per donar-li un nom més entenedor (per exemple getData) al panell de la dreta del camp Nom i pots collir Inici — Tanca i baixa (Inici — Tancar i carregar). Tingueu en compte que el camí del fitxer que hem importat per a l'exemple està codificat en dur al codi. Tornaràs a la finestra principal de Microsoft Excel, però a la dreta hauria d'aparèixer un panell amb la connexió creada a la nostra funció:
Pas 3. Recollida de tots els fitxers
Tot el més difícil queda enrere, queda el més agradable i fàcil. Aneu a la pestanya Dades – Crea una consulta – Des del fitxer – Des de la carpeta (Dades — Nova consulta — Des del fitxer — Des de la carpeta) o, si teniu Excel 2010-2013, de manera similar a la pestanya Power Consulta. A la finestra que apareix, especifiqueu la carpeta on es troben tots els nostres fitxers de ciutat d'origen i feu clic OK. El següent pas hauria d'obrir una finestra on es mostraran tots els fitxers d'Excel que es troben en aquesta carpeta (i les seves subcarpetes) i els detalls de cadascun d'ells:
feu clic Canviar (Edita) i tornem a entrar a la finestra familiar de l'editor de consultes.
Ara hem d'afegir una altra columna a la nostra taula amb la nostra funció creada, que "treu" les dades de cada fitxer. Per fer-ho, aneu a la pestanya Afegeix una columna: columna personalitzada (Afegeix una columna — Afegeix una columna personalitzada) i a la finestra que apareix, introduïu la nostra funció getData, especificant-hi com a argument el camí complet a cada fitxer:
Després de fer clic OK la columna creada s'hauria d'afegir a la nostra taula de la dreta.
Ara suprimim totes les columnes innecessàries (com a Excel, fent servir el botó dret del ratolí - Remove), deixant només la columna afegida i la columna amb el nom del fitxer, perquè aquest nom (més precisament, la ciutat) serà útil per tenir en el total de dades de cada fila.
I ara el "moment genial": feu clic a la icona amb les seves pròpies fletxes a la cantonada superior dreta de la columna afegida amb la nostra funció:
… desmarqueu Utilitzeu el nom de la columna original com a prefix (Utilitzeu el nom de la columna original com a prefix)and clic OK. I la nostra funció carregarà i processarà les dades de cada fitxer, seguint l'algorisme enregistrat i recopilant-ho tot en una taula comuna:
Per obtenir una bellesa completa, també podeu eliminar les extensions .xlsx de la primera columna amb els noms de fitxers, mitjançant la substitució estàndard per "res" (feu clic amb el botó dret a la capçalera de la columna - Suplent) i canviar el nom d'aquesta columna a ciutat. I també corregeix el format de les dades a la columna amb la data.
Tot! Fer clic a Inici - Tancar i carregar (Inici — Tancar i carregar). Totes les dades recollides per la consulta per a totes les ciutats es penjaran al full d'Excel actual en el format "taula intel·ligent":
La connexió creada i la nostra funció de muntatge no s'han de desar per separat de cap manera: es guarden juntament amb el fitxer actual de la manera habitual.
En el futur, amb qualsevol canvi a la carpeta (afegir o eliminar ciutats) o als fitxers (canviar el nombre de línies), n'hi haurà prou amb fer clic amb el botó dret directament a la taula o a la consulta del panell dret i seleccionar el comandament Actualitza i desa (Actualització) - Power Query tornarà a "reconstruir" totes les dades en pocs segons.
PS
Esmena. Després de les actualitzacions de gener de 2017, Power Query va aprendre a recopilar llibres d'Excel per si mateix, és a dir, ja no cal fer una funció separada; això passa automàticament. Així, el segon pas d'aquest article ja no és necessari i tot el procés es fa notablement més senzill:
- Escollir Crea sol·licitud - Des del fitxer - Des de la carpeta - Selecciona la carpeta - D'acord
- Quan aparegui la llista de fitxers, premeu Canviar
- A la finestra de l'Editor de consultes, amplieu la columna Binària amb una doble fletxa i seleccioneu el nom del full que voleu agafar de cada fitxer.
I això és tot! Cançó!
- Redisseny de la taula creuada en una de plana adequada per construir taules dinàmiques
- Creació d'un gràfic de bombolles animat a Power View
- Macro per muntar fulls de diferents fitxers Excel en un sol