Združite podatke iz več delovnih listov v en sam delovni list v Excelu

Pred kratkim sem od bralca prejel vprašanje o združevanju več delovnih listov v istem delovnem zvezku v en sam delovni list.

Prosil sem ga, naj uporabi Power Query za združevanje različnih listov, potem pa sem spoznal, da je to za nekoga, ki je nov v Power Queryju, težko.

Zato sem se odločil, da napišem to vadnico in pokažem natančne korake za združevanje več listov v eno samo tabelo z uporabo Power Query.

Spodaj videoposnetek, kjer pokažem, kako združiti podatke z več listov/tabel z uporabo Power Query:

Spodaj so napisana navodila o tem, kako združiti več listov (če imate raje pisno besedilo nad videom).

Opomba: Power Query je mogoče uporabiti kot dodatek v Excelih 2010 in 2013 in je vgrajena funkcija od Excela 2016 naprej. Glede na vašo različico so lahko nekatere slike videti drugače (posnetki slik, uporabljeni v tej vadnici, so iz Excela 2016).

Združite podatke iz več delovnih listov z uporabo Power Query

Pri združevanju podatkov z različnih listov z uporabo Power Query je potrebno, da so podatki v Excelovi tabeli (ali vsaj v imenovanih obsegih). Če podatki niso v Excelovi tabeli, tukaj prikazana metoda ne bi delovala.

Recimo, da imate štiri različne liste - vzhodno, zahodno, severno in južno.

Vsak od teh delovnih listov ima podatke v Excelovi tabeli, struktura tabele pa je skladna (tj. Glave so enake).

Kliknite tukaj, če želite prenesti podatke in nadaljevati.

Tovrstne podatke je izjemno enostavno združiti z Power Query (ki zelo dobro deluje s podatki v Excelovi tabeli).

Da bi ta tehnika delovala najbolje, je bolje imeti imena za Excelove tabele (delajte tudi brez nje, vendar je lažje uporabljati, ko so tabele poimenovane).

Tabelam sem dal naslednja imena: East_Data, West_Data, North_Data in South_Data.

Tu so koraki za združevanje več delovnih listov z Excelovimi tabelami z uporabo Power Query:

1. Pojdite na zavihek Podatki.
2. V skupini Get & Transform Data kliknite možnost 'Get Data'.
3. Pojdite na možnost »Iz drugih virov«.
4. Kliknite možnost »Prazna poizvedba«. S tem se odpre urejevalnik Power Query.
5. V urejevalnik poizvedb vnesite naslednjo formulo v vrstico s formulo: = Excel.Trenutni delovni zvezek(). Upoštevajte, da formule Power Query razlikujejo velike in male črke, zato morate uporabiti natančno formulo, kot je omenjeno (sicer boste dobili napako).
6. Pritisnite tipko Enter. Tako se prikažejo vsa imena tabel v celotnem delovnem zvezku (prikazani bodo tudi imenovani obsegi in/ali povezave, če obstajajo v delovnem zvezku).
7. [Izbirni korak] V tem primeru želim združiti vse tabele. Če želite združiti samo določene tabele programa Excel, lahko kliknete spustno ikono v glavi imena in izberete tiste, ki jih želite združiti. Podobno, če ste poimenovali obsege ali povezave in želite samo združiti tabele, lahko odstranite tudi ta imenovana območja.
8. V celici glave vsebine kliknite puščico z dvojno koničico.
9. Izberite stolpce, ki jih želite združiti. Če želite združiti vse stolpce, preverite (Izberite vse stolpce).
10. Počistite polje »Uporabi izvirno ime stolpca kot predpono«.
11. Kliknite V redu.

Zgornji koraki bi združili podatke iz vseh delovnih listov v eno samo tabelo.

Če natančno pogledate, boste našli zadnji stolpec (skrajno desno) z imeni Excelovih tabel (East_Data, West_Data, North_Data in South_Data). To je identifikator, ki nam pove, kateri zapis je prišel iz katere tabele Excel. Tudi zato sem rekel, da je bolje imeti opisna imena za Excelove tabele.

Tu je nekaj sprememb, ki jih lahko naredite za združene podatke v samem Power Queryju:

1. Povlecite in postavite stolpec Ime na začetek.
2. Odstranite »_Data« iz stolpca z imenom (tako boste v stolpcu z imeni ostali vzhod, zahod, sever in jug). Če želite to narediti, z desno miškino tipko kliknite glavo Name in kliknite Replace Values. V pogovornem oknu Zamenjaj vrednosti zamenjajte _Data s praznim.
3. Spremenite stolpec Podatki tako, da bodo prikazani samo datumi (in ne čas). Če želite to narediti, kliknite glavo stolpca Datum, pojdite na zavihek »Pretvori« in spremenite vrsto podatkov v Datum.
4. Preimenujte poizvedbo v ConsolidatedData.

Zdaj, ko imate združene podatke iz vseh delovnih listov v Power Queryju, jih lahko naložite v Excel - kot novo tabelo na novem delovnem listu.

Storiti to. sledite spodnjim korakom:

1. Kliknite zavihek »Datoteka«.
2. Kliknite Zapri in naloži v.
3. V pogovornem oknu Uvoz podatkov izberite možnosti Tabela in Nov delovni list.
4. Kliknite V redu.

Zgornji koraki bi združili podatke iz vseh delovnih listov in vam dali te združene podatke na novem delovnem listu.

Pri uporabi te metode morate rešiti eno težavo

Če ste uporabili zgornjo metodo za združevanje vseh tabel v delovnem zvezku, se boste verjetno soočili s težavo.

Oglejte si število vrstic združenih podatkov - 1304 (kar je prav).

Če osvežim poizvedbo, se število vrstic spremeni v 2607. Ponovno osveži in spremenilo se bo v 3910.

Tu je problem.

Vsakič, ko osvežite poizvedbo, združenim podatkom doda vse zapise v izvirnih podatkih.

Opomba: S to težavo se boste soočili le, če ste za združevanje uporabili Power Query VSE ODLIČNE MIZE v delovnem zvezku. Če ste izbrali posebne tabele, ki jih želite združiti, se s to težavo ne boste soočili.

Razumemo vzrok te težave in kako jo odpraviti.

Ko osvežite poizvedbo, se vrne nazaj in sledi vsem korakom, ki smo jih naredili za združevanje podatkov.

V koraku, kjer smo uporabili formulo = Excel.CurrentWorkbook (), nam je dal seznam vseh tabel. To je prvič delovalo dobro, saj so bile samo štiri mize.

Ko pa osvežite, je v delovnem zvezku pet tabel - vključno z novo tabelo, ki jo je Power Query vstavil, kjer imamo združene podatke.

Tako vsakič, ko osvežite poizvedbo, poleg štirih tabel Excel, ki jih želimo združiti, v nastale podatke doda tudi obstoječo tabelo poizvedb.

To se imenuje rekurzija.

Tukaj je opisano, kako rešiti to težavo.

Ko v vrstico formule Power Query vnesete = Excel.CurrentWorkbook () in pritisnete enter, dobite seznam Excelovih tabel. Če želite le združiti tabele z delovnega lista, morate nekako filtrirati samo te tabele, ki jih želite združiti, in odstraniti vse ostalo.

Če želite zagotoviti, da imate samo zahtevane tabele, sledite tem korakom:

1. Kliknite spustni meni in miško pomaknite na Besedilni filtri.
2. Kliknite možnost Vsebuje.
3. V pogovornem oknu Filterske vrstice vnesite _Data v polje poleg možnosti 'vsebuje'.
4. Kliknite V redu.

Morda ne boste videli nobenih sprememb v podatkih, vendar boste s tem preprečili, da bi se tabela, ki je nastala, znova dodala, ko je poizvedba osvežena.

Upoštevajte, da smo v zgornjih korakih uporabili »_Podatki”, Da filtrirate, kot smo tako poimenovali tabele. Kaj pa, če vaše tabele niso poimenovane dosledno. Kaj pa, če so vsa imena tabel naključna in nimajo nič skupnega.

Tukaj je način, kako to rešiti - uporabite filter 'ni enako' in vnesite ime poizvedbe (kar bi bilo v našem primeru ConsolidatedData). To bo zagotovilo, da bo vse ostalo enako in nastala tabela poizvedb, ki je bila ustvarjena, filtrirana.

Poleg dejstva, da Power Query olajša celoten postopek združevanja podatkov z različnih listov (ali celo istega lista), je še ena njegova prednost ta, da je dinamičen. Če v katero koli tabelo dodate več zapisov in osvežite Power Query, se vam samodejno prikažejo združeni podatki.

Pomembna opomba: V primeru, uporabljenem v tej vadnici, so bile glave enake. Če so glave drugačne, bo Power Query združil in ustvaril vse stolpce v novi tabeli. Če so podatki za ta stolpec na voljo, bodo prikazani, sicer pa bodo prikazani ničelni.

Morda vam bodo všeč tudi naslednje vaje Power Query:

• Združite podatke iz več delovnih zvezkov v Excelu (z uporabo Power Query).
• Kako odpreti podatke v Excelu z uporabo Power Query (aka Get & Transform)
• Pridobite seznam imen datotek iz map in podmap (z uporabo Power Query)
• Združite tabele v Excelu z uporabo Power Query.
• Kako primerjati dva Excelova lista/datoteke