Combinați date din mai multe registre de lucru în Excel (utilizând Power Query)

Power Query poate fi de mare ajutor atunci când doriți să combinați mai multe registre de lucru într-un singur registru de lucru.

De exemplu, să presupunem că aveți date despre vânzări pentru diferite regiuni (est, vest, nord și sud). Puteți combina aceste date din diferite registre de lucru într-o singură foaie de lucru folosind Power Query.

Dacă aveți aceste registre de lucru în locații / foldere diferite, este o idee bună să le mutați într-un singur folder (sau să creați o copie și să puneți copia cărții de lucru în același folder).

Deci, pentru început, am patru registre de lucru într-un dosar (așa cum se arată mai jos).

Acum, în acest tutorial, acoper trei scenarii în care puteți combina datele din diferite registre de lucru folosind Power Query:

• Fiecare registru de lucru conține datele într-un tabel Excel și toate numele tabelelor sunt aceleași.
• Fiecare registru de lucru are datele cu același nume de foaie de lucru. Acesta poate fi cazul atunci când există o foaie numită „rezumat” sau „date” în toate registrele de lucru și doriți să le combinați.
• Fiecare registru de lucru are multe foi și tabele și doriți să combinați tabele / foi specifice. Această metodă poate fi utilă și atunci când doriți să combinați tabel / foi care nu au un nume consistent.

Să vedem cum să combinăm datele din aceste registre de lucru în fiecare caz.

Fiecare registru de lucru are datele într-un tabel Excel cu aceeași structură

Tehnica de mai jos ar funcționa atunci când tabelele Excel au fost structurate în același mod (aceleași nume de coloane).

Numărul de rânduri din fiecare tabel poate varia.

Nu vă faceți griji dacă unele tabele Excel au coloane suplimentare. Puteți alege unul dintre tabele ca șablon (sau ca „cheie” așa cum îl numește Power Query), iar Power Query l-ar folosi pentru a combina toate celelalte tabele Excel cu acesta.

În cazul în care există coloane suplimentare în alte tabele, acestea vor fi ignorate și numai cele specificate în șablon / cheie ar fi combinate. De exemplu, dacă tabelul șablon / cheie pe care îl selectați are 5 coloane și unul dintre tabelele din alt registru de lucru are 2 coloane suplimentare, acele coloane suplimentare ar fi ignorate.

Acum am patru registre de lucru într-un folder pe care vreau să îl combin.

Mai jos este un instantaneu al tabelului pe care îl am într-unul din registrele de lucru.

Iată pașii pentru a combina datele din aceste registre de lucru într-un singur registru de lucru (ca un singur tabel).

1. Accesați fila Date.
2. În grupul Obțineți și transformați, faceți clic pe meniul derulant Interogare nouă.
3. Plasați cursorul pe „Din fișier” și faceți clic pe „Din folder”.
4. În caseta de dialog Folder, introduceți calea fișierului folderului care conține fișierele sau faceți clic pe Răsfoire și localizați folderul.
5. Faceți clic pe OK.
6. În caseta de dialog care se deschide, faceți clic pe butonul de combinare.
7. Faceți clic pe „Combinați și încărcați”.
8. În caseta de dialog „Combinați fișiere” care se deschide, selectați Tabelul din panoul din stânga. Rețineți că Power Query vă arată tabelul din primul fișier. Acest fișier ar acționa ca șablon (sau cheie) pentru a combina alte fișiere. Power Query ar căuta acum „Tabelul 1” în alte registre de lucru și l-ar combina cu acesta.
9. Faceți clic pe OK.

Aceasta va încărca rezultatul final (date combinate) în foaia de lucru activă.

Rețineți că, împreună cu datele, Power Query adaugă automat numele registrului de lucru ca prima coloană a datelor combinate. Acest lucru vă ajută să țineți evidența datelor care provin din care registru de lucru.

În cazul în care doriți mai întâi să editați datele înainte de a le încărca în Excel, la pasul 6, selectați „Combinați și editați”. Aceasta va deschide rezultatul final în editorul Power Query, unde puteți edita datele.

Câteva lucruri de știut:

• Dacă selectați un tabel Excel ca șablon (la pasul 7), Power Query va utiliza numele coloanelor din acest tabel pentru a combina datele din alte tabele. Dacă alte tabele au coloane suplimentare, acestea vor fi ignorate. În cazul în care celelalte tabele nu au o coloană, care se află în tabelul dvs. de șabloane, Power Query ar pune doar „nul” pentru aceasta.
• Coloanele nu trebuie să fie în aceeași ordine în care Power Query folosește anteturile de coloană pentru cartografierea coloanelor.
• Deoarece ați selectat Tabelul 1 ca cheie, Power Query va căuta Tabelul 1 în toate registrele de lucru și le va combina pe toate. În cazul în care nu găsește un tabel Excel cu același nume (Tabelul 1 în acest exemplu), Power Query vă va da o eroare.

Adăugarea de fișiere noi în dosar

Acum să luăm un minut și să înțelegem ce am făcut cu pașii de mai sus (care ne-au luat doar câteva secunde).

Am combinat datele din patru registre de lucru diferite într-un singur tabel în câteva secunde, fără a deschide niciunul dintre registrele de lucru.

Dar asta nu este tot.

PUTEREA reală a Power Query este că acum, când adăugați mai multe fișiere în dosar, nu trebuie să repetați niciunul dintre acești pași.

Tot ce trebuie să faceți este să mutați noul registru de lucru în dosar, să reîmprospătați interogarea și acesta va combina automat datele din toate registrele de lucru din acel dosar.

De exemplu, în exemplul de mai sus, dacă adaug un nou registru de lucru - „Mid-West.xlsx” în dosar și reîmprospătați interogarea, îmi va oferi instantaneu noul set de date combinat.

Iată cum reîmprospătați o interogare:

• Faceți clic dreapta pe tabelul Excel pe care l-ați încărcat în foaia de lucru și faceți clic pe Reîmprospătare.
• Faceți clic dreapta pe interogarea din panoul „Interogare registru de lucru” și faceți clic pe Reîmprospătare
• Mergeți la fila Date și faceți clic pe Reîmprospătare.

Fiecare registru de lucru are datele cu același nume de foaie de lucru

În cazul în care nu aveți datele într-un tabel Excel, dar toate numele foilor (din care doriți să combinați datele) sunt aceleași, atunci puteți utiliza metoda prezentată în această secțiune.

Există câteva lucruri la care trebuie să fii precaut atunci când sunt doar date tabulare și nu un tabel Excel.

• Numele foilor de lucru ar trebui să fie aceleași. Acest lucru va ajuta Power Query să treacă prin registrele de lucru și să combine datele din foile de lucru care au același nume în fiecare registru de lucru.
• Power Query este sensibil la majuscule și minuscule. Aceasta înseamnă că o foaie de lucru numită „date” și „Date” sunt considerate diferite. În mod similar, o coloană cu antetul „Magazin” și una cu „magazin” sunt considerate diferite.
• Deși este important să aveți aceleași anteturi de coloană, nu este important să aveți aceeași ordine. Dacă coloana 2 din „East.xlsx” este coloana 4 din „West.xlsx”, Power Query o va potrivi corect prin maparea antetelor.

Acum să vedem cum să combinăm rapid date din diferite registre de lucru în care numele foii de lucru este același.

În acest exemplu, am un folder cu patru fișiere.

În fiecare registru de lucru, am o foaie de lucru cu numele „Date” care conține datele în formatul următor (rețineți că acesta nu este un tabel Excel).

Iată pașii pentru a combina datele din mai multe registre de lucru într-o singură foaie de lucru:

1. Accesați fila Date.
2. În grupul Obțineți și transformați, faceți clic pe meniul derulant Interogare nouă.
3. Plasați cursorul pe „Din fișier” și faceți clic pe „Din folder”.
4. În caseta de dialog Folder, introduceți calea fișierului folderului care conține fișierele sau faceți clic pe Răsfoire și localizați folderul.
5. Faceți clic pe OK.
6. În caseta de dialog care se deschide, faceți clic pe butonul de combinare.
7. Faceți clic pe „Combinați și încărcați”.
8. În caseta de dialog „Combinați fișiere” care se deschide, selectați „Date” în panoul din stânga. Rețineți că Power Query vă arată numele foii de lucru din primul fișier. Acest fișier ar acționa ca cheie / șablon pentru a combina alte fișiere. Power Query va parcurge fiecare registru de lucru, va găsi foaia numită „Date” și va combina toate acestea.
9. Faceți clic pe OK. Acum, Power Query va parcurge fiecare registru de lucru, va căuta foaia de lucru numită „Date” și va combina toate aceste seturi de date.

Aceasta va încărca rezultatul final (date combinate) în foaia de lucru activă.

În cazul în care doriți mai întâi să editați datele înainte de a le încărca în Excel, la pasul 6, selectați „Combinați și editați”. Aceasta va deschide rezultatul final în editorul Power Query, unde puteți edita datele.

Fiecare registru de lucru are datele cu nume de tabele diferite sau nume de foi

Uneori, este posibil să nu obțineți date structurate și consistente (cum ar fi tabele cu același nume sau foaie de lucru cu același nume).

De exemplu, să presupunem că obțineți datele de la cineva care a creat aceste seturi de date, dar a denumit foile de lucru ca date Est, West Data, North Data și South Data.

Sau este posibil ca persoana respectivă să fi creat tabele Excel, dar cu nume diferite.

În astfel de cazuri, puteți utiliza în continuare Power Query, dar trebuie să faceți acest lucru cu câțiva pași suplimentari.

1. Accesați fila Date.
2. În grupul Obțineți și transformați, faceți clic pe meniul derulant Interogare nouă.
3. Plasați cursorul pe „Din fișier” și faceți clic pe „Din folder”.
4. În caseta de dialog Folder, introduceți calea fișierului folderului care conține fișierele sau faceți clic pe Răsfoire și localizați folderul.
5. Faceți clic pe OK.
6. În caseta de dialog care se deschide, faceți clic pe butonul Editare. Aceasta va deschide editorul Power Query, unde veți vedea detaliile tuturor fișierelor din dosar.
7. Țineți apăsată tasta Control și selectați coloanele „Conținut” și „Nume”, faceți clic dreapta și selectați „Eliminați alte coloane”. Aceasta va elimina toate celelalte coloane, cu excepția coloanelor selectate.
8. În panglica Editorului de interogări, faceți clic pe „Adăugați coloană”, apoi faceți clic pe „Coloană personalizată”.
9. În caseta de dialog Adăugare coloană personalizată, denumiți noua coloană ca „Import de date” și utilizați următoarea formulă = Excel.Workbook ([CONTENT]). Rețineți că această formulă este sensibilă la majuscule și minuscule și trebuie să o introduceți exact așa cum am arătat aici.
10. Acum veți vedea o nouă coloană cu tabel scris în ea. Acum, lasă-mă să explic ce s-a întâmplat aici. Ați furnizat Power Query numele registrelor de lucru, iar Power Query a preluat obiecte precum foi de lucru, tabele și intervale denumite din fiecare registru de lucru (care se află acum în celula Tabel). Puteți face clic pe spațiul alb de lângă textul Tabel și veți vedea informațiile în partea de jos. În acest caz, deoarece avem doar un tabel și o foaie de lucru în fiecare registru de lucru, puteți vedea doar două rânduri.
11. Faceți clic pe pictograma săgeată dublă din partea de sus a coloanei „Import date”.
12. În caseta de date a coloanei care se deschide, debifați „Utilizați coloana originală ca prefix”, apoi faceți clic pe OK.
13. Acum veți vedea un tabel extins în care vedeți un rând pentru fiecare obiect din tabel. În acest caz, pentru fiecare registru de lucru, obiectul foaie și obiectul tabel sunt listate separat.
14. În coloana Tip, filtrați lista pentru a afișa doar tabelul.
15. Țineți apăsată tasta de control și selectați coloana Nume și date. Acum, faceți clic dreapta și eliminați toate celelalte coloane.
16. În coloana Date, faceți clic pe pictograma săgeată dublă din partea dreaptă sus a antetului de date.
17. În caseta de date a coloanei care se deschide, faceți clic pe OK. Aceasta va combina datele din toate tabelele și se va afișa în Power Query.
18. Acum puteți efectua orice transformare de care aveți nevoie, apoi accesați fila Acasă și faceți clic pe Închidere și încărcare.

Acum, lasă-mă să încerc să explic rapid ce am făcut aici. Deoarece nu a existat o coerență în numele foilor sau numele tabelelor, am folosit formula = Excel.Workbook pentru a prelua toate obiectele din registrele de lucru din Power Query. Aceste obiecte pot include foi, tabele și intervale denumite. Odată ce am avut toate obiectele din toate fișierele, le-am filtrat pentru a lua în considerare doar tabelele Excel. Apoi am extins datele din tabele și le-am combinat pe toate.

În acest exemplu, am filtrat datele pentru a utiliza numai tabele Excel (la pasul 13). În cazul în care doriți să combinați foi și nu tabele, puteți filtra foile.

Notă - această tehnică vă va oferi datele combinate chiar și atunci când există o nepotrivire în numele coloanelor. De exemplu, dacă în East.xlsx, aveți o coloană care a fost greșit de scris, veți ajunge cu 5 coloane. Power Query va completa datele în coloane dacă le găsește și, dacă nu găsește o coloană, va raporta valoarea ca „nulă”.

În mod similar, dacă aveți câteva coloane suplimentare în oricare dintre foile de lucru ale tabelelor, acestea vor fi incluse în rezultatul final.

Acum, dacă obțineți mai multe registre de lucru din care trebuie să combinați date, pur și simplu copiați-le lipiți în folder și reîmprospătați Power Query