Nedávno som dostal od čitateľa otázku o kombinovaní viacerých pracovných hárkov v jednom zošite do jedného pracovného hárka.
Požiadal som ho, aby použil Power Query na kombináciu rôznych hárkov, ale potom som si uvedomil, že pre niekoho nového v Power Query to môže byť náročné.
Preto som sa rozhodol napísať tento návod a ukázať presné kroky na kombinovanie viacerých hárkov do jednej tabuľky pomocou Power Query.
Pod videom, kde uvádzam, ako kombinovať údaje z viacerých hárkov/tabuliek pomocou Power Query:
Nasleduje písomný návod, ako skombinovať viac listov (v prípade, že dávate prednosť písanému textu pred videom).
Poznámka: Power Query je možné použiť ako doplnok v Exceli 2010 a 2013 a je vstavanou funkciou od Excelu 2016 a vyššie. Na základe vašej verzie môžu niektoré obrázky vyzerať inak (obrázky použité v tomto návode sú z Excelu 2016).
Skombinujte údaje z viacerých pracovných hárkov pomocou Power Query
Pri kombinovaní údajov z rôznych hárkov pomocou Power Query je potrebné mať údaje v tabuľke Excel (alebo aspoň v pomenovaných rozsahoch). Ak údaje nie sú v tabuľke Excel, tu zobrazená metóda nebude fungovať.
Predpokladajme, že máte štyri rôzne listy - východ, západ, sever a juh.
Každý z týchto pracovných hárkov obsahuje údaje v tabuľke programu Excel a štruktúra tabuľky je konzistentná (tj. Záhlavia sú rovnaké).
Kliknutím sem stiahnete údaje a budete pokračovať.
Tento druh údajov je veľmi ľahké kombinovať pomocou Power Query (ktorý s údajmi v tabuľke Excel funguje naozaj dobre).
Aby táto technika fungovala čo najlepšie, je lepšie mať názvy tabuliek programu Excel (pracovať aj bez nich, ale používanie názvov tabuliek je jednoduchšie).
Tabuľkám som dal tieto názvy: East_Data, West_Data, North_Data a South_Data.
Tu sú kroky na kombinovanie viacerých pracovných hárkov s tabuľkami programu Excel pomocou Power Query:
- Prejdite na kartu Údaje.
- V skupine Získať a transformovať údaje kliknite na možnosť „Získať údaje“.
- Prejdite na možnosť „Z iných zdrojov“.
- Kliknite na možnosť „Prázdny dotaz“. Tým sa otvorí editor Power Query.
- V editore dotazu zadajte do panela vzorcov nasledujúci vzorec: = Excel.Aktuálna pracovná kniha(). Upozorňujeme, že vo vzorcoch Power Query sa rozlišujú malé a veľké písmená, takže musíte použiť presný vzorec, ako je uvedené (inak sa zobrazí chyba).
- Stlačte kláves Enter. To vám ukáže všetky názvy tabuliek v celom zošite (ukáže vám tiež pomenované rozsahy a/alebo spojenia v prípade, že v zošite existujú).
- [Voliteľný krok] V tomto prípade chcem skombinovať všetky tabuľky. Ak chcete kombinovať iba konkrétne tabuľky programu Excel, kliknite na rozbaľovaciu ikonu v hlavičke názvu a vyberte tie, ktoré chcete skombinovať. Podobne, ak máte pomenované rozsahy alebo pripojenia a chcete iba kombinovať tabuľky, môžete tieto pomenované rozsahy tiež odstrániť.
- V bunke záhlavia obsahu kliknite na dvojitú šípku so šípkou.
- Vyberte stĺpce, ktoré chcete skombinovať. Ak chcete spojiť všetky stĺpce, uistite sa, že je začiarknuté políčko (Vybrať všetky stĺpce).
- Zrušte začiarknutie možnosti „Použiť pôvodný názov stĺpca ako predponu“.
- Kliknite na tlačidlo OK.
Vyššie uvedené kroky by spojili údaje zo všetkých pracovných hárkov do jednej tabuľky.
Ak sa pozriete pozorne, zistíte, že posledný stĺpec (úplne vpravo) má názov tabuliek programu Excel (East_Data, West_Data, North_Data a South_Data). Toto je identifikátor, ktorý nám hovorí, ktorý záznam pochádza z ktorej tabuľky programu Excel. To je tiež dôvod, prečo som povedal, že je lepšie mať popisné názvy pre tabuľky programu Excel.
Tu je niekoľko úprav, ktoré môžete vykonať na kombinovaných údajoch v samotnom Power Query:
- Presuňte a umiestnite stĺpec Názov na začiatok.
- Odstráňte „_Data“ zo stĺpca s názvom (v stĺpci s názvom vám teda zostane východ, západ, sever a juh). Ak to chcete urobiť, kliknite pravým tlačidlom myši na hlavičku Názov a kliknite na položku Nahradiť hodnoty. V dialógovom okne Nahradiť hodnoty nahraďte _Data prázdnym políčkom.
- Zmeňte stĺpec Údaje tak, aby sa zobrazovali iba dátumy (a nie čas). Ak to chcete urobiť, kliknite na hlavičku stĺpca Dátum, prejdite na kartu „Transformácia“ a zmeňte typ údajov na Dátum.
- Premenujte dotaz na ConsolidatedData.
Teraz, keď máte kombinované údaje zo všetkých hárkov v Power Query, môžete ich načítať do Excelu - ako novú tabuľku v novom pracovnom hárku.
Urobiť toto. postupujte podľa nasledujúcich krokov:
- Kliknite na kartu „Súbor“.
- Kliknite na Zavrieť a načítať do.
- V dialógovom okne Import údajov vyberte možnosti Tabuľka a Nový pracovný hárok.
- Kliknite na Ok.
Vyššie uvedené kroky by spojili údaje zo všetkých pracovných hárkov a poskytli by vám kombinované údaje v novom pracovnom hárku.
Pri použití tejto metódy musíte vyriešiť jeden problém
V prípade, že ste použili vyššie uvedenú metódu na zlúčenie všetkých tabuliek v zošite, pravdepodobne narazíte na problém.
Pozrite sa na počet riadkov kombinovaných údajov - 1304 (čo je správne).
Ak teraz dotaz obnovím, počet riadkov sa zmení na 2607. Obnovte znova a zmení sa na 3910.
Tu je problém.
Zakaždým, keď obnovíte dotaz, pridá všetky záznamy v pôvodných údajoch do kombinovaných údajov.
Poznámka: S týmto problémom sa stretnete, iba ak ste na skombinovanie použili Power Query Všetky tabuľky EXCEL v pracovnom zošite. V prípade, že ste vybrali konkrétne tabuľky, ktoré sa majú skombinovať, nebudete s týmto problémom počítať.Poďme pochopiť príčinu tohto problému a ako ho opraviť.
Keď dotaz obnovíte, vráti sa späť a bude postupovať podľa všetkých krokov, ktoré sme vykonali na skombinovanie údajov.
V kroku, kde sme použili vzorec = Excel.CurrentWorkbook (), dalo nám to zoznam všetkých tabuliek. Prvýkrát to fungovalo dobre, pretože tam boli iba štyri stoly.
Ale keď obnovíte, v zošite je päť tabuliek - vrátane novej tabuľky, ktorú Power Query vložil tam, kde máme kombinované údaje.
Zakaždým, keď obnovíte dotaz, okrem štyroch tabuliek programu Excel, ktoré chceme skombinovať, tiež pridá do výsledných údajov existujúcu tabuľku dotazov.
Toto sa nazýva rekurzia.
Tu je postup, ako tento problém vyriešiť.
Po vložení = Excel.CurrentWorkbook () do panela vzorcov Power Query a stlačení klávesu Enter získate zoznam tabuliek programu Excel. Aby ste sa uistili, že tabuľky budete kombinovať iba z pracovného hárka, musíte nejako filtrovať iba tieto tabuľky, ktoré chcete kombinovať, a odstrániť všetko ostatné.
Tu sú kroky, aby ste sa presvedčili, že máte iba požadované tabuľky:
- Kliknite na rozbaľovaciu ponuku a umiestnite kurzor na textové filtre.
- Kliknite na možnosť Obsahuje.
- V dialógovom okne Filtrovať riadky zadajte _Data do poľa vedľa možnosti „obsahuje“.
- Kliknite na tlačidlo OK.
V údajoch sa nemusia prejaviť žiadne zmeny, ale tým sa zabráni opätovnému pridaniu výslednej tabuľky pri aktualizácii dotazu.
Všimnite si toho, že vo vyššie uvedených krokoch sme použili „_Údaje”Filtrovať, ako sme takto pomenovali tabuľky. Ale čo keď vaše tabuľky nie sú pomenované dôsledne. Čo keď sú všetky názvy tabuliek náhodné a nemajú nič spoločné.
Toto je spôsob, ako to vyriešiť - použite filter „nerovná sa“ a zadajte názov dopytu (čo by v našom prípade bola ConsolidatedData). Tým sa zabezpečí, že všetko zostane rovnaké a výsledná tabuľka dotazov, ktorá sa vytvorí, sa odfiltruje.
Okrem toho, že Power Query robí celý tento proces kombinovania údajov z rôznych hárkov (alebo dokonca rovnakých hárkov) pomerne jednoduchým, ďalšou výhodou jeho použitia je, že je dynamický. Ak do ktorejkoľvek z tabuliek pridáte ďalšie záznamy a obnovíte Power Query, automaticky vám to poskytne kombinované údaje.Dôležitá poznámka: V príklade použitom v tomto návode boli hlavičky rovnaké. V prípade, že sú hlavičky odlišné, Power Query skombinuje a vytvorí všetky stĺpce v novej tabuľke. Ak sú pre daný stĺpec údaje k dispozícii, zobrazia sa, v opačnom prípade budú mať hodnotu null.
Tiež by sa vám mohli páčiť nasledujúce návody na Power Query:
- Skombinujte údaje z viacerých zošitov v programe Excel (pomocou Power Query).
- Ako zrušiť otočenie údajov v programe Excel pomocou Power Query (aka získať a transformovať)
- Získajte zoznam názvov súborov zo priečinkov a podpriečinkov (pomocou Power Query)
- Zlúčenie tabuliek v programe Excel pomocou Power Query.
- Ako porovnať dva listy/súbory programu Excel
