Nedávno jsem dostal dotaz od čtenáře na kombinaci více listů ve stejném sešitu do jednoho listu.
Požádal jsem ho, aby pomocí Power Query kombinoval různé listy, ale pak jsem si uvědomil, že pro někoho nového v Power Query to může být náročné.
Rozhodl jsem se tedy napsat tento tutoriál a ukázat přesné kroky, jak kombinovat více listů do jedné tabulky pomocí Power Query.
Pod videem, kde ukazuji, jak kombinovat data z více listů/tabulek pomocí Power Query:
Níže jsou uvedeny písemné pokyny, jak kombinovat více listů (v případě, že dáváte přednost psanému textu před videem).
Poznámka: Power Query lze použít jako doplněk v Excelu 2010 a 2013 a je integrovanou funkcí od Excelu 2016 a dále. Na základě vaší verze mohou některé obrázky vypadat odlišně (obrázky použité v tomto kurzu pocházejí z Excelu 2016).
Zkombinujte data z více listů pomocí Power Query
Při kombinování dat z různých listů pomocí Power Query je nutné mít data v tabulce aplikace Excel (nebo alespoň v pojmenovaných rozsazích). Pokud data nejsou v tabulce Excel, zde zobrazená metoda by nefungovala.
Předpokládejme, že máte čtyři různé listy - východ, západ, sever a jih.
Každý z těchto listů obsahuje data v tabulce aplikace Excel a struktura tabulky je konzistentní (tj. Záhlaví jsou stejná).
Kliknutím sem stáhnete data a budete pokračovat.
Tento druh dat lze extrémně snadno kombinovat pomocí Power Query (který s daty v tabulce Excel funguje opravdu dobře).
Aby tato technika fungovala co nejlépe, je lepší mít názvy pro tabulky aplikace Excel (pracovat i bez ní, ale je jednodušší ji použít, když jsou tabulky pojmenovány).
Tabulkám jsem dal následující názvy: East_Data, West_Data, North_Data a South_Data.
Zde jsou kroky ke kombinaci více listů s tabulkami aplikace Excel pomocí Power Query:
- Přejděte na kartu Data.
- Ve skupině Získat a transformovat data klikněte na možnost „Získat data“.
- Přejděte na možnost „Z jiných zdrojů“.
- Klikněte na možnost „Prázdný dotaz“. Tím se otevře editor Power Query.
- V editoru dotazů zadejte do řádku vzorců následující vzorec: = Excel.Aktuální pracovní sešit(). Všimněte si, že vzorce Power Query rozlišují velká a malá písmena, takže musíte použít přesný vzorec, jak je uvedeno (jinak se zobrazí chyba).
- Stiskněte klávesu Enter. To vám ukáže všechny názvy tabulek v celém sešitu (také vám ukáže pojmenované rozsahy a/nebo připojení v případě, že to v sešitu existuje).
- [Volitelný krok] V tomto příkladu chci zkombinovat všechny tabulky. Pokud chcete kombinovat pouze konkrétní tabulky aplikace Excel, můžete kliknout na rozevírací ikonu v záhlaví názvu a vybrat ty, které chcete kombinovat. Podobně pokud máte pojmenované rozsahy nebo připojení a chcete pouze kombinovat tabulky, můžete také pojmenované rozsahy odebrat.
- V buňce záhlaví obsahu klikněte na dvojitou šipku.
- Vyberte sloupce, které chcete kombinovat. Pokud chcete zkombinovat všechny sloupce, zkontrolujte, zda je zaškrtnuto (Vybrat všechny sloupce).
- Zrušte zaškrtnutí možnosti „Použít původní název sloupce jako předponu“.
- Klikněte na OK.
Výše uvedené kroky by spojily data ze všech listů do jedné tabulky.
Pokud se podíváte pozorně, zjistíte, že poslední sloupec (úplně vpravo) má název tabulek aplikace Excel (East_Data, West_Data, North_Data a South_Data). Toto je identifikátor, který nám říká, který záznam pochází z které tabulky Excel. To je také důvod, proč jsem řekl, že je lepší mít popisné názvy pro tabulky aplikace Excel.
Zde je několik úprav, které můžete provést u kombinovaných dat v samotném Power Query:
- Přetáhněte a umístěte sloupec Název na začátek.
- Ze sloupce názvu odeberte „_Data“ (ve sloupci názvu vám tedy zbude východ, západ, sever a jih). Chcete-li to provést, klikněte pravým tlačítkem na záhlaví Název a klikněte na Nahradit hodnoty. V dialogovém okně Nahradit hodnoty nahraďte _Data prázdným.
- Změňte sloupec Data tak, aby zobrazoval pouze data (a ne čas). Chcete -li to provést, klikněte na záhlaví sloupce Datum, přejděte na kartu „Transformovat“ a změňte typ dat na Datum.
- Přejmenujte dotaz na ConsolidatedData.
Nyní, když máte kombinovaná data ze všech listů v Power Query, můžete je načíst do Excelu - jako novou tabulku v novém listu.
Udělat toto. postupujte podle následujících kroků:
- Klikněte na kartu „Soubor“.
- Klikněte na Zavřít a načíst do.
- V dialogovém okně Import dat vyberte možnost Tabulka a Nový list.
- Klikněte na Ok.
Výše uvedené kroky by kombinovaly data ze všech listů a poskytly by vám kombinovaná data v novém listu.
Při použití této metody musíte vyřešit jeden problém
V případě, že jste použili výše uvedenou metodu ke kombinaci všech tabulek v sešitu, pravděpodobně narazíte na problém.
Podívejte se na počet řádků kombinovaných dat - 1304 (což je správně).
Pokud nyní obnovím dotaz, počet řádků se změní na 2607. Obnovte znovu a změní se na 3910.
Zde je problém.
Pokaždé, když obnovíte dotaz, přidá všechny záznamy v původních datech do kombinovaných dat.
Poznámka: K tomuto problému budete čelit pouze v případě, že jste ke zkombinování použili Power Query VŠECHNY TABULKY EXCEL v pracovním sešitě. V případě, že jste vybrali konkrétní tabulky, které chcete zkombinovat, s tímto problémem se nesetkáte.Pojďme pochopit příčinu tohoto problému a jak jej opravit.
Když obnovíte dotaz, vrátí se zpět a bude postupovat podle všech kroků, které jsme provedli ke spojení dat.
V kroku, kde jsme použili vzorec = Excel.CurrentWorkbook (), dalo nám to seznam všech tabulek. Poprvé to fungovalo dobře, protože tam byly pouze čtyři stoly.
Když ale obnovíte, je v sešitu pět tabulek - včetně nové tabulky, kterou Power Query vložil tam, kde máme kombinovaná data.
Takže pokaždé, když dotaz aktualizujete, kromě čtyř tabulek aplikace Excel, které chceme kombinovat, přidá do výsledných dat také stávající tabulku dotazů.
Tomu se říká rekurze.
Zde je návod, jak tento problém vyřešit.
Jakmile do řádku vzorců Power Query vložíte = Excel.CurrentWorkbook () a stisknete klávesu Enter, zobrazí se seznam tabulek aplikace Excel. Abyste se ujistili, že můžete z listu kombinovat pouze tabulky, musíte nějak filtrovat pouze tyto tabulky, které chcete kombinovat, a odstranit vše ostatní.
Zde jsou kroky, abyste se ujistili, že máte pouze požadované tabulky:
- Klikněte na rozevírací seznam a umístěte kurzor na textové filtry.
- Klikněte na možnost Obsahuje.
- V dialogovém okně Filtr řádků zadejte _Data do pole vedle možnosti „obsahuje“.
- Klikněte na OK.
V datech se nemusí zobrazit žádná změna, ale zabráníte tím opětovnému přidání výsledné tabulky při aktualizaci dotazu.
Všimněte si, že ve výše uvedených krocích jsme použili „_Data”Filtrovat, jak jsme takto pojmenovali tabulky. Ale co když vaše tabulky nejsou pojmenovány konzistentně. Co když jsou všechny názvy tabulek náhodné a nemají nic společného.
Zde je způsob, jak to vyřešit - použijte filtr „nerovná se“ a zadejte název dotazu (což by v našem příkladu bylo ConsolidatedData). Tím zajistíte, že vše zůstane stejné a výsledná vytvořená tabulka dotazů bude odfiltrována.
Kromě toho, že Power Query celý tento proces kombinování dat z různých listů (nebo dokonce stejného listu) docela usnadňuje, je další výhodou jeho použití, že je dynamický. Pokud do některé z tabulek přidáte další záznamy a aktualizujete Power Query, automaticky vám poskytne kombinovaná data.Důležitá poznámka: V příkladu použitém v tomto kurzu byla záhlaví stejná. V případě, že jsou záhlaví odlišná, Power Query zkombinuje a vytvoří všechny sloupce v nové tabulce. Pokud jsou pro daný sloupec data k dispozici, zobrazí se, v opačném případě se zobrazí null.
Také by se vám mohly líbit následující návody na Power Query:
- Zkombinujte data z více sešitů v aplikaci Excel (pomocí Power Query).
- Jak zrušit otočení dat v aplikaci Excel pomocí Power Query (aka Get & Transform)
- Získejte seznam názvů souborů ze složek a podsložek (pomocí Power Query)
- Sloučit tabulky v aplikaci Excel pomocí Power Query.
- Jak porovnat dva listy/soubory aplikace Excel
