Construiți tabele din diferite fișiere Excel cu interogare de putere

Formularea problemei

Să ne uităm la o soluție la o frumoasă situații foarte frecvente, care se confruntă mai devreme sau mai târziu, majoritatea utilizatorilor Excel trebuie să colecteze rapid și în mod automat datele de la un număr mare de fișiere într-un tabel de sinteză.

Să presupunem că avem un astfel de dosar, care conține mai multe fișiere cu date din ramuri-orașe:

Numărul de fișiere de rol nu se joacă și se poate schimba în viitor. În fiecare fișier există o foaie numită Vânzări. unde se află masa cu datele:

Numărul de rânduri (comenzi) din tabele, de la sine, este diferit, dar setul de coloane este pretutindeni standard.

Sarcină: colectați datele din toate fișierele dintr-o singură carte și apoi actualizați automat când adăugați sau ștergeți fișiere oraș sau rânduri în tabele. În tabelul final consolidat, puteți să construiți apoi rapoarte, tabele pivot, date de sortare filtru și așa mai departe. Principalul lucru este să puteți colecta.

Ridicarea armelor

Pasul 1. Importați un fișier ca probă

În primul rând, să importăm date dintr-o singură carte ca exemplu, astfel încât Excel "să primească ideea". Pentru a face acest lucru, creați un registru de lucru nou și.

Acesta este un instrument foarte puternic care vă permite să "completati" masa în forma dorită. Chiar și o descriere superficială a tuturor funcțiilor ar avea mai puțin de o sută de pagini, dar, dacă este doar pe scurt, cu această fereastră poți:

• filtrați date inutile, linii goale, linii cu erori
• sortați datele cu una sau mai multe coloane
• scapa de repetare
• pentru a împărți textul îmbinat în coloane (delimitator, număr de caractere etc.)
• puneți textul în ordine (eliminați spațiile suplimentare, corectați registrul etc.)
• în orice mod posibil de a transforma tipurile de date (pentru a converti numere ca text la numere normale și invers)
• a transpune (roti) tabelele și a implementa tabelul bidimensional pe plat
• Adăugați coloane suplimentare în tabel și utilizați formulele și funcțiile în limba M încorporată în Power Query.
• .

De exemplu, să adăugăm la tabelul nostru o coloană cu numele textului pentru luna, astfel încât să fie mai ușor să construim mai târziu rapoarte ale tabelelor pivot. Pentru a face acest lucru, faceți clic dreapta pe titlul coloanei Data și selectați comanda Coloana duplicat. apoi faceți clic dreapta pe antetul coloanei duplicate care apare și selectați comenzile Convert - Month - Month Name.

Ar trebui să se formeze o nouă coloană cu numele textului pentru fiecare lună. Dând dublu clic pe antetul coloanei, îl puteți redenumi din Copiere dată într-o lună mai convenabilă. de exemplu.

Excludeți rândurile cu erori sau linii goale, precum și cu manageri sau clienți inutili, utilizând un filtru simplu:

Și toate transformările efectuate sunt fixate în panoul din dreapta, unde pot fi întotdeauna rulate înapoi (încrucișate) sau modificate parametrii (uneltele):

E ușor și elegant, nu-i așa?

Pasul 2. Convertiți cererea noastră la o funcție

Pentru a repeta ulterior toate transformările de date efectuate pentru fiecare carte importată, trebuie să transformăm interogarea noastră creată într-o funcție, care apoi va fi aplicată, la rândul ei, tuturor fișierelor noastre. Pentru a face acest lucru, de fapt, este foarte simplu.

Acum efectuați cu atenție câteva editări:

Semnificația acestora este simplă: prima linie (filepath) => transformă procedura noastră într-o funcție cu argumentul filepath. și mai jos vom schimba calea fixă ​​la valoarea acestei variabile.

Asta e tot. Dați clic pe Finalizați și vedeți acest lucru:

Nu vă alarmați că datele lipsă - de fapt, totul este în regulă, totul este așa cum ar trebui să arate :) Am stabilit cu succes funcția noastră definită de utilizator, care a adus aminte întreaga date algoritmul și de import, fără a fi legat de un anumit fișier. Rămâne să îi oferiți un nume mai ușor de înțeles (de exemplu, getData) în panoul din dreapta în câmpul Name și puteți apăsa pe Home - Close and Load. Rețineți că codul are o cale de codare greu la fișierul pe care l-am importat pentru exemplu. Veți reveni la fereastra principală Microsoft Excel, dar în partea dreaptă ar trebui să vedeți un panou cu conexiunea creată pentru funcția noastră:

Pasul 3. Colectăm toate fișierele

Acum trebuie să adăugăm la masa noastră încă o coloană cu funcția noastră creată, care "trage" datele din fiecare fișier. Pentru aceasta, accesați fila Adăugați coloana - Adăugați coloana personalizată și introduceți funcția getData în fereastra care apare. specificându-i ca argument un drum complet către fiecare fișier:

După ce faceți clic pe OK, coloana creată ar trebui adăugată în tabelul din dreapta.

Acum, ștergeți toate coloanele inutile (ca în Excel, faceți clic dreapta pe Ștergeți), lăsând doar coloana și coloana adăugate cu numele fișierului, deoarece Acest nume (sau mai degrabă - orașul) va fi util să existe în datele sumare pentru fiecare linie.

Și acum "wow-moment" - faceți clic pe mouse-ul de pe pictogramă cu săgețile din colțul din dreapta sus al coloanei adăugate cu funcția noastră:

Debifați caseta Utilizare originală a coloanei ca prefix și faceți clic pe OK. Și funcția noastră va încărca și procesa datele din fiecare fișier, urmând algoritmul înregistrat și colectând totul în tabelul general:

Pentru frumusețea completă poate elimina în continuare .xlsx extensie din prima coloană cu numele fișierelor - standardul schimbat la „nimic“ (click-dreapta pe antetul coloanei - Înlocuiți) și redenumiți coloana în oraș. Și, de asemenea, pentru a corecta formatul de date în coloană cu data.

Totul! Faceți clic pe Main - Închideți și descărcați (Acasă - Închidere Încărcați). Toate datele colectate prin interogare pentru toate orașele vor fi încărcate în foaia curentă Excel în formatul "tabel inteligent":

Conexiunea creată și funcția noastră de construire nu trebuie să fie salvate separat - acestea sunt salvate împreună cu fișierul curent în mod obișnuit.

În viitor, orice schimbare în directorul (adăugarea, ștergerea orașelor) sau în fișiere (pentru a modifica numărul de rânduri) va fi suficient pentru a faceți clic dreapta direct pe masa sau interogarea în panoul din dreapta și selectați Refresh (Reîmprospătare) - Putere interogare „reconstrui dvs.“ toate datele în câteva secunde.

Link-uri conexe

Îmbunătățit experiența "Combinare binare" atunci când importați din orice folder

Înainte de această versiune, utilizatorii ar putea combina text sau CSV fișiere numai. Combinei nu ar funcționa pentru orice alte formate de fișier acceptate (cum ar fi Excel Workbooks, fișiere JSON, etc.), și nu ar considerare pentru transformările necesare pentru fiecare fișier înainte de combinarea lor într-un singur tabel (cum ar fi eliminarea primul rând cu antetul valorilor).

Acum vă place să vă bucurați și să vă bateți mâinile. Acum nu trebuie să pervertiți prin schimbarea manuală a codului.

Cu toate acestea (în munca mea), atunci când încărcați fișiere de 1C în format .xlsx, Putere interogare încă nu le poate înghiți până pur și simplu nu resalvați fișierul în Excel (fișierul deschis, salvați, închideți).

Diviziunea ta! Tocmai l-am actualizat și am încercat: toate fișierele au fost încărcate dintr-o dată direct fără funcție - cu două clicuri. Pentru a obține. Actualizează mai repede decât scriu articole

Mulțumesc, Vasile! Mi-ai făcut ziua!

Trimiteți-le prietenilor: