Nedavno sam od čitatelja dobio pitanje o kombiniranju više radnih listova u istoj radnoj knjizi u jedan radni list.
Zamolio sam ga da upotrijebi Power Query za kombiniranje različitih listova, no tada sam shvatio da za nekoga tko je tek počeo raditi s Power Queryjem, to može biti teško.
Stoga sam odlučio napisati ovaj vodič i pokazati točne korake za kombiniranje više listova u jednu tablicu pomoću programa Power Query.
Ispod videozapisa u kojem pokazujem kako kombinirati podatke s više listova/tablica pomoću programa Power Query:
Dolje su napisane upute o tome kako kombinirati više listova (u slučaju da više volite pisani tekst od videozapisa).
Napomena: Power Query se može koristiti kao dodatak u programima Excel 2010 i 2013, a ugrađena je značajka od programa Excel 2016 nadalje. Na temelju vaše inačice, neke slike mogu izgledati drugačije (snimke slika korištene u ovom vodiču su iz programa Excel 2016).
Kombinirajte podatke s više radnih listova pomoću programa Power Query
Kada kombinirate podatke s različitih listova pomoću programa Power Query, potrebno je da podaci budu u Excel tablici (ili barem u imenovanim rasponima). Ako podaci nisu u Excelovoj tablici, metoda prikazana ovdje neće funkcionirati.
Pretpostavimo da imate četiri različita lista - istok, zapad, sjever i jug.
Svaki od ovih radnih listova ima podatke u Excel tablici, a struktura tablice je dosljedna (tj. Zaglavlja su ista).
Kliknite ovdje za preuzimanje podataka i slijedite ih.
Ova vrsta podataka iznimno se lako kombinira pomoću programa Power Query (koji jako dobro funkcionira s podacima u Excel tablici).
Da bi ova tehnika najbolje funkcionirala, bolje je imati nazive za svoje Excel tablice (raditi i bez nje, ali je lakše koristiti kad su tablice imenovane).
Tablicama sam dao sljedeće nazive: Istočni_podatci, zapadni_podatci, sjeverni_podatci i južni_podaci.
Evo koraka za kombiniranje više radnih listova s Excel tablicama pomoću programa Power Query:
- Idite na karticu Podaci.
- U grupi Get & Transform Data kliknite opciju ‘Get Data’.
- Idite na opciju "Iz drugih izvora".
- Kliknite opciju "Prazan upit". Ovo će otvoriti uređivač Power Query.
- U uređivač upita upišite sljedeću formulu u traku s formulama: = Excel.Trenutna radna knjiga(). Napominjemo da formule Power Query razlikuju velika i mala slova, pa morate koristiti točnu formulu kao što je spomenuto (inače ćete dobiti pogrešku).
- Pritisnite tipku Enter. Ovo će vam pokazati sve nazive tablica u cijeloj radnoj knjizi (također će vam pokazati imenovane raspone i/ili veze u slučaju da postoje u radnoj knjizi).
- [Izborni korak] U ovom primjeru želim kombinirati sve tablice. Ako želite kombinirati samo određene Excel tablice, tada možete kliknuti padajuću ikonu u zaglavlju naziva i odabrati one koje želite kombinirati. Slično, ako ste imenovali raspone ili veze, a želite samo kombinirati tablice, možete ukloniti i te imenovane raspone.
- U ćeliji zaglavlja sadržaja kliknite dvostranu strelicu.
- Odaberite stupce koje želite kombinirati. Ako želite kombinirati sve stupce, provjerite je li označeno (Odaberi sve stupce).
- Poništite opciju "Koristi izvorni naziv stupca kao prefiks".
- Pritisnite U redu.
Gore navedeni koraci kombinirali bi podatke sa svih radnih listova u jednu tablicu.
Ako pomno pogledate, pronaći ćete da zadnji stupac (krajnji desni) ima naziv Excelovih tablica (East_Data, West_Data, North_Data i South_Data). Ovo je identifikator koji nam govori koji je zapis došao iz koje Excel tablice. To je i razlog zašto sam rekao da je bolje imati opisne nazive za Excel tablice.
Evo nekoliko izmjena koje možete učiniti na kombiniranim podacima u samom Power Queryju:
- Povucite i postavite stupac Naziv na početak.
- Uklonite “_Data” iz stupca s imenom (tako da u stupcu s imenom ostajete Istok, Zapad, Sjever i Jug). Da biste to učinili, desnom tipkom miša kliknite zaglavlje Name i kliknite Zamijeni vrijednosti. U dijaloškom okviru Zamijeni vrijednosti zamijenite _Data praznim mjestom.
- Promijenite stupac Podaci tako da prikazuje samo datume (a ne i vrijeme). Da biste to učinili, kliknite zaglavlje stupca Datum, idite na karticu "Pretvori" i promijenite vrstu podataka u Datum.
- Preimenujte upit u ConsolidatedData.
Sada kada imate kombinirane podatke sa svih radnih listova u programu Power Query, možete ih učitati u Excel - kao novu tablicu u novom radnom listu.
Uraditi ovo. slijedite korake u nastavku:
- Kliknite karticu "Datoteka".
- Pritisnite Zatvori i Učitaj u.
- U dijaloškom okviru Uvoz podataka odaberite opcije Tablica i Novi radni list.
- Pritisnite U redu.
Gore navedeni koraci kombinirali bi podatke sa svih radnih listova i dali vam te kombinirane podatke na novom radnom listu.
Jedan problem koji morate riješiti kada koristite ovu metodu
U slučaju da ste koristili gornju metodu za kombiniranje svih tablica u radnoj knjizi, vjerojatno ćete se suočiti s problemom.
Pogledajte broj redaka kombiniranih podataka - 1304 (što je točno).
Sada, ako osvježim upit, broj redaka mijenja se na 2607. Ponovno osvježi i promijenit će se na 3910.
Ovdje je problem.
Svaki put kad osvježite upit, on kombiniranim podacima dodaje sve zapise u izvornim podacima.
Napomena: S ovim ćete se problemom suočiti samo ako ste za kombiniranje koristili Power Query SVI IZVRSNI STOLOVI u radnoj bilježnici. U slučaju da ste odabrali određene tablice za kombiniranje, nećete se suočiti s ovim problemom.Shvatimo uzrok ovog problema i kako to ispraviti.
Kad osvježite upit, on se vraća unatrag i slijedi sve korake koje smo poduzeli za kombiniranje podataka.
U koraku u kojem smo koristili formulu = Excel.CurrentWorkbook (), dao nam je popis svih tablica. Ovo je prvi put dobro funkcioniralo jer su postojala samo četiri stola.
Ali kad osvježite, u radnoj knjizi ima pet tablica - uključujući novu tablicu koju je Power Query umetnuo tamo gdje imamo kombinirane podatke.
Stoga svaki put kad osvježite upit, osim četiri Excel tablice koje želimo kombinirati, dobivenim podacima dodaje se i postojeća tablica upita.
To se naziva rekurzija.
Evo kako riješiti ovaj problem.
Nakon što umetnete = Excel.CurrentWorkbook () u formular Power Query i pritisnete enter, dobit ćete popis Excel tablica. Da biste bili sigurni da ćete kombinirati samo tablice s radnog lista, morate nekako filtrirati samo ove tablice koje želite kombinirati i ukloniti sve ostalo.
Evo koraka kako biste bili sigurni da imate samo potrebne tablice:
- Kliknite padajući izbornik i zadržite pokazivač miša na Tekstualni filtri.
- Kliknite na opciju Sadrži.
- U dijaloški okvir Filter Rows unesite _Data u polje pored opcije "contains".
- Pritisnite U redu.
Možda nećete vidjeti nikakve promjene u podacima, ali to će spriječiti ponovno dodavanje rezultirajuće tablice kada se upit osvježi.
Imajte na umu da smo u gornjim koracima koristili „_Podaci”Za filtriranje kako smo na taj način imenovali tablice. Ali što ako se vaše tablice ne imenuju dosljedno. Što ako su svi nazivi tablica nasumični i nemaju ništa zajedničko.
Evo načina da to riješite - upotrijebite filtar ‘nije jednako’ i unesite naziv upita (što bi u našem primjeru bilo ConsolidatedData). To će osigurati da sve ostane isto, a dobivena tablica upita koja se stvori filtrira.
Osim što Power Query čini cijeli ovaj proces kombiniranja podataka s različitih listova (ili čak istih listova) prilično lakim, još jedna prednost korištenja je ta što ga čini dinamičnim. Ako bilo kojoj tablici dodate više zapisa i osvježite Power Query, automatski će vam se dati kombinirani podaci.Važna napomena: U primjeru korištenom u ovom vodiču zaglavlja su bila ista. U slučaju da su zaglavlja različita, Power Query će kombinirati i stvoriti sve stupce u novoj tablici. Ako su podaci dostupni za taj stupac, bit će prikazani, inače će pokazati null.
Možda će vam se svidjeti i sljedeći udžbenici Power Query:
- Kombinirajte podatke iz više radnih knjiga u Excelu (pomoću programa Power Query).
- Kako otkinuti podatke u Excelu pomoću programa Power Query (poznat i kao Get & Transform)
- Nabavite popis imena datoteka iz mapa i podmapa (pomoću programa Power Query)
- Spajanje tablica u Excelu pomoću programa Power Query.
- Kako usporediti dva Excel lista/datoteke
