Skombinujte údaje z viacerých zošitov v Exceli (pomocou Power Query)

Power Query môže byť veľkou pomocou, keď chcete skombinovať viacero zošitov do jedného zošita.

Predpokladajme napríklad, že máte údaje o predajoch pre rôzne regióny (východ, západ, sever a juh). Tieto údaje z rôznych zošitov môžete skombinovať do jedného pracovného hárka pomocou Power Query.

Ak máte tieto zošity na rôznych miestach/priečinkoch, je dobré ich všetky presunúť do jedného priečinka (alebo vytvoriť kópiu a vložiť kópiu zošita do rovnakého priečinka).

Na začiatok teda mám štyri pracovné zošity v priečinku (ako je uvedené nižšie).

Teraz v tomto návode pokrývam tri scenáre, v ktorých môžete kombinovať údaje z rôznych zošitov pomocou Power Query:

• Každý zošit obsahuje údaje v tabuľke programu Excel a všetky názvy tabuliek sú rovnaké.
• Každý zošit má údaje s rovnakým názvom pracovného hárka. To môže byť prípad, keď je vo všetkých zošitoch hárok s názvom „súhrn“ alebo „údaje“ a chcete všetky tieto položky skombinovať.
• Každý zošit má veľa hárkov a tabuliek a vy chcete kombinovať konkrétne tabuľky/hárky. Táto metóda môže byť užitočná aj vtedy, ak chcete kombinovať tabuľku/listy, ktoré nemajú jednotný názov.

Pozrime sa, ako v každom prípade skombinovať údaje z týchto zošitov.

Každý zošit má údaje v tabuľke programu Excel s rovnakou štruktúrou

Nasledujúca technika bude fungovať, ak budú vaše tabuľky programu Excel štruktúrované rovnakým spôsobom (rovnaké názvy stĺpcov).

Počet riadkov v každej tabuľke sa môže líšiť.

Ak niektoré tabuľky programu Excel majú ďalšie stĺpce, nie je dôvod sa znepokojovať. Môžete si vybrať jednu z tabuliek ako šablónu (alebo ako „kľúč“, ako ju Power Query nazýva) a Power Query by pomocou nej skombinoval všetky ostatné tabuľky programu Excel.

V prípade, že v ďalších tabuľkách sú ďalšie stĺpce, budú tieto ignorované a skombinujú sa iba tie, ktoré sú uvedené v šablóne/kľúči. Ak má napríklad vybratá tabuľka šablón/kľúčov 5 stĺpcov a jedna z tabuliek v inom zošite má 2 ďalšie stĺpce, tieto ďalšie stĺpce budú ignorované.

Teraz mám štyri pracovné zošity v priečinku, ktorý chcem skombinovať.

Nasleduje snímka tabuľky, ktorú mám v jednom zošitov.

Tu sú kroky na zlúčenie údajov z týchto zošitov do jedného zošita (ako jednej tabuľky).

1. Prejdite na kartu Údaje.
2. V skupine Získať a transformovať kliknite na rozbaľovaciu ponuku Nový dotaz.
3. Umiestnite kurzor na položku „Zo súboru“ a kliknite na položku „Zo priečinka“.
4. V dialógovom okne Priečinok zadajte cestu k súboru priečinka, v ktorom sa nachádzajú súbory, alebo kliknite na položku Prehľadávať a priečinok vyhľadajte.
5. Kliknite na tlačidlo OK.
6. V dialógovom okne, ktoré sa otvorí, kliknite na tlačidlo kombinovať.
7. Kliknite na „Skombinovať a načítať“.
8. V otvorenom dialógovom okne „Skombinovať súbory“ vyberte tabuľku na ľavom paneli. Všimnite si toho, že Power Query vám ukazuje tabuľku z prvého súboru. Tento súbor by slúžil ako šablóna (alebo kľúč) na kombináciu iných súborov. Power Query by teraz vyhľadal „tabuľku 1“ v iných zošitoch a skombinoval ju s týmto.
9. Kliknite na tlačidlo OK.

Načíta sa konečný výsledok (kombinované údaje) do vášho aktívneho pracovného hárka.

Všimnite si toho, že Power Query spolu s údajmi automaticky pridá názov zošita ako prvý stĺpec kombinovaných údajov. Pomáha to sledovať, ktoré údaje pochádzajú z ktorého zošita.

V prípade, že chcete údaje najskôr upraviť pred načítaním do Excelu, v kroku 6 vyberte „Skombinovať a upraviť“. Tým sa otvorí konečný výsledok v editore Power Query, kde môžete údaje upravovať.

Niekoľko vecí, ktoré je potrebné vedieť:

• Ak ako šablónu vyberiete tabuľku programu Excel (v kroku 7), Power Query použije názvy stĺpcov v tejto tabuľke na kombináciu údajov z iných tabuliek. Ak majú ostatné tabuľky ďalšie stĺpce, budú tieto ignorované. V prípade, že tieto ostatné tabuľky nemajú stĺpec, ktorý sa nachádza vo vašej tabuľke šablón, Power Query by pre neho označil „null“.
• Stĺpce nemusia byť v rovnakom poradí, pretože Power Query používa na mapovanie stĺpcov hlavičky stĺpcov.
• Pretože ste vybrali ako kľúč Tabuľku1, Power Query vyhľadá Tabuľku1 vo všetkých zošitoch a všetky tieto skombinuje. V prípade, že nenájde tabuľku programu Excel s rovnakým názvom (v tomto prípade tabuľka 1), Power Query vám poskytne chybu.

Pridanie nových súborov do priečinka

Teraz si urobme minútu a pochopme, čo sme urobili s vyššie uvedenými krokmi (čo nám trvalo iba niekoľko sekúnd).

Údaje zo štyroch rôznych zošitov sme skombinovali do jednej tabuľky v priebehu niekoľkých sekúnd bez toho, aby sme museli otvoriť ktorýkoľvek zošit.

Ale to nie je všetko.

Skutočnou SILOU Power Query je, že keď teraz do priečinka pridáte ďalšie súbory, nemusíte opakovať žiadny z týchto krokov.

Všetko, čo musíte urobiť, je presunúť nový zošit do priečinka, obnoviť dotaz a automaticky skombinuje údaje zo všetkých zošitov v tomto priečinku.

Napríklad vo vyššie uvedenom príklade, ak pridám nový zošit - „Mid-West.xlsx“ do priečinka a obnovením dotazu mi to okamžite poskytne nový kombinovaný súbor údajov.

Dotaz obnovíte takto:

• Kliknite pravým tlačidlom myši na tabuľku programu Excel, ktorú ste nahrali do pracovného hárka, a kliknite na položku Obnoviť.
• Kliknite pravým tlačidlom myši na Dopyt na table „Dopis zošita“ a kliknite na položku Obnoviť
• Prejdite na kartu Údaje a kliknite na položku Obnoviť.

Každý zošit má údaje s rovnakým názvom pracovného hárka

V prípade, že údaje v tabuľke programu Excel nemáte, ale všetky názvy hárkov (z ktorých chcete údaje skombinovať) sú rovnaké, môžete použiť metódu uvedenú v tejto časti.

Existuje niekoľko vecí, pri ktorých musíte byť opatrní, keď ide iba o tabuľkové údaje, a nie o tabuľku programu Excel.

• Názvy pracovných hárkov by mali byť rovnaké. Power Query to pomôže prechádzať vašimi zošitmi a kombinovať údaje z pracovných hárkov, ktoré majú v každom zošite rovnaký názov.
• Power Query rozlišuje veľké a malé písmena. To znamená, že pracovný hárok s názvom „údaje“ a „údaje“ sa považuje za odlišný. Podobne sa považuje za odlišný stĺpec s hlavičkou „Store“ a stĺpec s „store“.
• Aj keď je dôležité mať rovnaké hlavičky stĺpcov, nie je dôležité mať rovnaké poradie. Ak je stĺpec 2 v poli „East.xlsx“ stĺpcom 4 v dokumente „West.xlsx“, Power Query ho správne zladí zmapovaním hlavičiek.

Teraz sa pozrime, ako rýchlo skombinovať údaje z rôznych zošitov, kde je názov pracovného hárka rovnaký.

V tomto prípade mám priečinok so štyrmi súbormi.

V každom zošite mám pracovný list s názvom „Údaje“, ktorý obsahuje údaje v nasledujúcom formáte (všimnite si, že toto nie je tabuľka programu Excel).

Tu sú kroky na kombinovanie údajov z viacerých zošitov do jedného pracovného hárka:

1. Prejdite na kartu Údaje.
2. V skupine Získať a transformovať kliknite na rozbaľovaciu ponuku Nový dotaz.
3. Umiestnite kurzor na položku „Zo súboru“ a kliknite na položku „Zo priečinka“.
4. V dialógovom okne Priečinok zadajte cestu k súboru priečinka, v ktorom sa nachádzajú súbory, alebo kliknite na položku Prehľadávať a priečinok vyhľadajte.
5. Kliknite na tlačidlo OK.
6. V dialógovom okne, ktoré sa otvorí, kliknite na tlačidlo kombinovať.
7. Kliknite na „Skombinovať a načítať“.
8. V otvorenom dialógovom okne „Skombinovať súbory“ vyberte na ľavom paneli položku „Údaje“. Všimnite si toho, že Power Query zobrazuje názov pracovného hárka z prvého súboru. Tento súbor by slúžil ako kľúč/šablóna na kombináciu iných súborov. Power Query prejde každým zošitom, nájde list s názvom „Údaje“ a skombinuje ich všetky.
9. Kliknite na tlačidlo OK. Teraz Power Query prejde každým zošitom, vyhľadá v ňom pracovný hárok s názvom „Údaje“ a potom skombinuje všetky tieto množiny údajov.

Načíta sa konečný výsledok (kombinované údaje) do vášho aktívneho pracovného hárka.

V prípade, že chcete údaje najskôr upraviť pred ich načítaním do Excelu, v kroku 6 vyberte „Skombinovať a upraviť“. Tým sa otvorí konečný výsledok v editore Power Query, kde môžete údaje upravovať.

Každý zošit má údaje s rôznymi názvami tabuliek alebo názvov hárkov

Niekedy nemusí byť k dispozícii štruktúrované a konzistentné údaje (napríklad tabuľky s rovnakým názvom alebo pracovný hárok s rovnakým názvom).

Predpokladajme napríklad, že údaje získavate od niekoho, kto tieto súbory údajov vytvoril, ale pracovné hárky pomenoval ako East Data, West Data, North Data a South Data.

Osoba môže tiež vytvoriť tabuľky programu Excel, ale s inými menami.

V takýchto prípadoch môžete stále používať Power Query, ale musíte to urobiť pomocou niekoľkých ďalších krokov.

1. Prejdite na kartu Údaje.
2. V skupine Získať a transformovať kliknite na rozbaľovaciu ponuku Nový dotaz.
3. Umiestnite kurzor na položku „Zo súboru“ a kliknite na položku „Zo priečinka“.
4. V dialógovom okne Priečinok zadajte cestu k súboru priečinka, v ktorom sa nachádzajú súbory, alebo kliknite na položku Prehľadávať a priečinok vyhľadajte.
5. Kliknite na tlačidlo OK.
6. V dialógovom okne, ktoré sa otvorí, kliknite na tlačidlo Upraviť. Otvorí sa editor Power Query, v ktorom uvidíte podrobnosti o všetkých súboroch v priečinku.
7. Podržte kláves Control a vyberte stĺpce „Obsah“ a „Názov“, kliknite pravým tlačidlom myši a zvoľte „Odstrániť ďalšie stĺpce“. Odstránia sa tým všetky ostatné stĺpce okrem vybratých stĺpcov.
8. Na páse s nástrojmi Editora dotazov kliknite na položku „Pridať stĺpec“ a potom kliknite na položku „Vlastný stĺpček“.
9. V dialógovom okne Pridať vlastný stĺpec pomenujte nový stĺpec ako „Import údajov“ a použite nasledujúci vzorec = Excel.Workbook ([CONTENT]). Upozorňujeme, že v tomto vzorci sa rozlišujú malé a veľké písmená a musíte ho zadať presne tak, ako som tu ukázal.
10. Teraz uvidíte nový stĺpec, v ktorom je napísaná tabuľka. Teraz mi vysvetlite, čo sa tu stalo. Poskytli ste Power Query názvy zošitov a Power Query z každého zošita (ktorý sa odteraz nachádza v bunke Tabuľka) načítal objekty, ako sú pracovné hárky, tabuľky a pomenované rozsahy. Môžete kliknúť na biele miesto vedľa textu Tabuľka a v spodnej časti sa vám zobrazia informácie. V tomto prípade, pretože v každom zošite máme iba jednu tabuľku a jeden pracovný hárok, môžete vidieť iba dva riadky.
11. Kliknite na ikonu dvojitej šípky v hornej časti stĺpca „Import údajov“.
12. V otvorenom poli s údajmi stĺpca zrušte začiarknutie políčka „Použiť pôvodný stĺpec ako predponu“ a potom kliknite na tlačidlo OK.
13. Teraz uvidíte rozbalenú tabuľku, kde vidíte jeden riadok pre každý objekt v tabuľke. V tomto prípade sú pre každý zošit objekt hárka a objekt tabuľky uvedený samostatne.
14. V stĺpci Druh upravte zoznam tak, aby sa zobrazila iba tabuľka.
15. Podržte ovládací kláves a vyberte stĺpec Názov a Údaje. Teraz kliknite pravým tlačidlom myši a odstráňte všetky ostatné stĺpce.
16. V stĺpci Údaje kliknite na ikonu dvojitej šípky v pravom hornom rohu hlavičky údajov.
17. V poli s údajmi stĺpca, ktoré sa otvorí, kliknite na tlačidlo OK. To skombinuje údaje vo všetkých tabuľkách a zobrazí sa v Power Query.
18. Teraz môžete vykonať akúkoľvek potrebnú transformáciu a potom prejsť na kartu Domov a kliknúť na položku Zavrieť a načítať.

Teraz sa pokúsim rýchlo vysvetliť, čo sme tu urobili. Pretože názvy hárkov alebo názvy tabuliek neboli konzistentné, použili sme vzorec = Excel.Workbook na načítanie všetkých objektov zošitov v Power Query. Tieto objekty môžu zahŕňať hárky, tabuľky a pomenované rozsahy. Keď sme mali všetky objekty zo všetkých súborov, filtrovali sme ich, aby sme zohľadnili iba tabuľky programu Excel. Potom sme rozšírili údaje v tabuľkách a spojili ich všetky.

V tomto prípade sme údaje filtrovali tak, aby používali iba tabuľky programu Excel (v kroku 13). V prípade, že chcete kombinovať listy a nie tabuľky, môžete filtrovať listy.

Poznámka - táto technika vám poskytne kombinované údaje, aj keď existuje nesúlad v názvoch stĺpcov. Napríklad, ak v East.xlsx máte stĺpec, ktorý bol nesprávne napísaný, skončíte s 5 stĺpcami. Power Query vyplní údaje do stĺpcov, ak ich nájde, a ak nemôže nájsť stĺpec, nahlási hodnotu ako „null“.

Podobne, ak máte v niektorom z pracovných hárkov tabuliek ďalšie stĺpce, budú zahrnuté do konečného výsledku.

Ak teraz získate viac zošitov, z ktorých je potrebné skombinovať údaje, jednoducho ich skopírujte a prilepte do priečinka a obnovte Power Query.