Recent am primit o întrebare de la un cititor despre combinarea mai multor foi de lucru în același registru de lucru într-o singură foaie de lucru.
I-am cerut să folosească Power Query pentru a combina diferite foi, dar apoi mi-am dat seama că pentru cineva nou în Power Query, a face acest lucru poate fi dificil.
Așa că am decis să scriu acest tutorial și să arăt pașii exacți pentru a combina mai multe foi într-un singur tabel folosind Power Query.
Mai jos un videoclip în care arăt cum să combin datele din mai multe foi / tabele folosind Power Query:
Mai jos sunt instrucțiuni scrise despre cum să combinați mai multe foi (în cazul în care preferați text scris în locul videoclipului).
Notă: Power Query poate fi utilizat ca supliment în Excel 2010 și 2013 și este o caracteristică încorporată începând cu Excel 2016. Pe baza versiunii dvs., unele imagini pot arăta diferit (capturile de imagine utilizate în acest tutorial provin din Excel 2016).
Combinați date din mai multe foi de lucru folosind Power Query
Atunci când combinați date din diferite foi folosind Power Query, este necesar să aveți datele într-un tabel Excel (sau cel puțin în intervale denumite). Dacă datele nu se află într-un tabel Excel, metoda prezentată aici nu va funcționa.
Să presupunem că aveți patru foi diferite - Est, Vest, Nord și Sud.
Fiecare dintre aceste foi de lucru conține datele într-un tabel Excel, iar structura tabelului este consecventă (adică antetele sunt aceleași).
Faceți clic aici pentru a descărca datele și a continua.
Acest tip de date este extrem de ușor de combinat folosind Power Query (care funcționează foarte bine cu datele din tabelul Excel).
Pentru ca această tehnică să funcționeze cel mai bine, este mai bine să aveți nume pentru tabelele Excel (funcționați și fără aceasta, dar este mai ușor de utilizat atunci când tabelele sunt denumite).
Am dat tabelelor următoarele nume: East_Data, West_Data, North_Data și South_Data.
Iată pașii pentru a combina mai multe foi de lucru cu tabele Excel folosind Power Query:
- Accesați fila Date.
- În grupul Obțineți și transformați date, faceți clic pe opțiunea „Obțineți date”.
- Accesați opțiunea „Din alte surse”.
- Faceți clic pe opțiunea „Interogare necompletată”. Aceasta va deschide editorul Power Query.
- În editorul de interogări, tastați următoarea formulă în bara de formule: = Excel.CurrentWorkbook(). Rețineți că formulele Power Query sunt sensibile la majuscule și minuscule, deci trebuie să utilizați formula exactă așa cum s-a menționat (altfel veți primi o eroare).
- Apăsați tasta Enter. Aceasta vă va arăta toate numele tabelelor din întregul registru de lucru (vă va arăta, de asemenea, intervalele și / sau conexiunile denumite în cazul în care există în registrul de lucru).
- [Pas opțional] În acest exemplu, vreau să combin toate tabelele. Dacă doriți să combinați numai anumite tabele Excel, puteți face clic pe pictograma drop-down din antetul numelui și selectați-le pe cele pe care doriți să le combinați. În mod similar, dacă ați numit intervale sau conexiuni și doriți doar să combinați tabele, puteți elimina și acele intervale denumite.
- În celula antetului Conținut, faceți clic pe săgeata cu două puncte.
- Selectați coloanele pe care doriți să le combinați. Dacă doriți să combinați toate coloanele, asigurați-vă că (Selectați toate coloanele) este bifat.
- Debifați opțiunea „Utilizați numele coloanei originale ca prefix”.
- Faceți clic pe OK.
Pașii de mai sus ar combina datele din toate foile de lucru într-un singur tabel.
Dacă priviți cu atenție, veți găsi ultima coloană (cea mai dreaptă) cu numele tabelelor Excel (East_Data, West_Data, North_Data și South_Data). Acesta este un identificator care ne spune ce înregistrare a venit din care tabel Excel. Acesta este și motivul pentru care am spus că este mai bine să aveți nume descriptive pentru tabelele Excel.
Iată câteva modificări pe care le puteți face datelor combinate în Power Query în sine:
- Trageți și plasați coloana Nume la început.
- Eliminați „_Data” din coloana cu nume (astfel încât să rămâneți cu Est, Vest, Nord și Sud în coloana cu nume). Pentru a face acest lucru, faceți clic dreapta pe antetul Name și faceți clic pe Înlocuire valori. În caseta de dialog Înlocuire valori, înlocuiți _Date cu un gol.
- Schimbați coloana Date pentru a afișa doar datele (și nu ora). Pentru aceasta, faceți clic pe antetul coloanei Date, accesați fila „Transformare” și schimbați tipul de date la Data.
- Redenumiți interogarea în ConsolidatedData.
Acum că aveți datele combinate din toate foile de lucru din Power Query, le puteți încărca în Excel - ca un nou tabel într-o nouă foaie de lucru.
Pentru a face acest lucru. urmați pașii de mai jos:
- Faceți clic pe fila „Fișier”.
- Faceți clic pe Închidere și Încărcare în.
- În caseta de dialog Import date, selectați Opțiuni tabel și Foaie de lucru nouă.
- Faceți clic pe Ok.
Pașii de mai sus ar combina datele din toate foile de lucru și ar oferi aceste date combinate într-o nouă foaie de lucru.
O problemă pe care trebuie să o rezolvați atunci când utilizați această metodă
În cazul în care ați utilizat metoda de mai sus pentru a combina toate tabelele din registrul de lucru, este posibil să vă confruntați cu o problemă.
Vedeți numărul de rânduri ale datelor combinate - 1304 (care este corect).
Acum, dacă reîmprospătez interogarea, numărul de rânduri se schimbă la 2607. Reîmprospătează din nou și se va schimba la 3910.
Iată problema.
De fiecare dată când reîmprospătați interogarea, aceasta adaugă toate înregistrările din datele originale la datele combinate.
Notă: veți întâmpina această problemă numai dacă ați utilizat Power Query pentru a combina TOATE MESELE EXCEL în registrul de lucru. În cazul în care ați selectat anumite tabele pentru a fi combinate, nu veți întâmpina această problemă.Să înțelegem cauza acestei probleme și cum să o corectăm.
Când reîmprospătați o interogare, aceasta revine și urmează toți pașii pe care i-am făcut pentru a combina datele.
În etapa în care am folosit formula = Excel.CurrentWorkbook (), ne-a oferit o listă cu toate tabelele. Acest lucru a funcționat bine prima dată, deoarece erau doar patru mese.
Dar când reîmprospătați, există cinci tabele în registrul de lucru - inclusiv noul tabel introdus de Power Query în care avem datele combinate.
Deci, de fiecare dată când reîmprospătați interogarea, în afară de cele patru tabele Excel pe care dorim să le combinăm, se adaugă și tabelul de interogare existent la datele rezultate.
Aceasta se numește recursivitate.
Iată cum să rezolvați această problemă.
După ce introduceți = Excel.CurrentWorkbook () în bara de formulare Power Query și apăsați Enter, veți obține o listă de tabele Excel. Pentru a vă asigura că puteți combina numai tabelele din foaia de lucru, trebuie să filtrați cumva doar aceste tabele pe care doriți să le combinați și să eliminați orice altceva.
Iată pașii pentru a vă asigura că aveți doar tabelele necesare:
- Faceți clic pe meniul derulant și plasați cursorul pe Filtre text.
- Faceți clic pe opțiunea Conține.
- În caseta de dialog Filtrare rânduri, introduceți _Date în câmpul de lângă opțiunea „conține”.
- Faceți clic pe OK.
Este posibil să nu vedeți nicio modificare în date, dar acest lucru va împiedica adăugarea din nou a tabelului rezultat atunci când interogarea este actualizată.
Rețineți că în pașii de mai sus am folosit „_Date”Pentru a filtra pe măsură ce am numit tabelele în acest fel. Dar dacă tabelele dvs. nu sunt numite în mod consecvent. Ce se întâmplă dacă toate numele tabelelor sunt aleatorii și nu au nimic în comun.
Iată modalitatea de a rezolva acest lucru - utilizați filtrul „nu este egal” și introduceți numele interogării (care ar fi ConsolidatedData în exemplul nostru). Acest lucru se va asigura că totul rămâne la fel și că tabelul de interogare rezultat care este creat este filtrat.
În afară de faptul că Power Query face acest întreg proces de combinare a datelor din diferite foi (sau chiar din aceeași foaie) destul de ușor, un alt avantaj al utilizării acesteia îl face dinamic. Dacă adăugați mai multe înregistrări la oricare dintre tabele și reîmprospătați Power Query, acesta vă va oferi automat datele combinate.Notă importantă: În exemplul folosit în acest tutorial, anteturile erau aceleași. În cazul în care antetele sunt diferite, Power Query va combina și va crea toate coloanele din noul tabel. Dacă datele sunt disponibile pentru acea coloană, acestea vor fi afișate, altfel vor apărea nule.
Vă pot plăcea, de asemenea, următoarele tutoriale de interogare Power:
- Combinați date din mai multe registre de lucru în Excel (utilizând Power Query).
- Cum să Unpivot date în Excel folosind Power Query (aka Get & Transform)
- Obțineți o listă de nume de fișiere din dosare și sub-foldere (utilizând Power Query)
- Combinați tabelele în Excel folosind Power Query.
- Cum se compară două foi / fișiere Excel
