Power Query může být velkou pomocí, když chcete kombinovat více sešitů do jednoho sešitu.
Předpokládejme například, že máte údaje o prodejích pro různé regiony (východ, západ, sever a jih). Tato data z různých sešitů můžete zkombinovat do jednoho listu pomocí Power Query.
Pokud máte tyto sešity na různých místech/složkách, je dobré přesunout je všechny do jedné složky (nebo vytvořit kopii a kopii sešitu umístit do stejné složky).
Takže na začátek mám čtyři sešity ve složce (jak je uvedeno níže).
Nyní v tomto kurzu pokrývám tři scénáře, ve kterých můžete kombinovat data z různých sešitů pomocí Power Query:
- Každý sešit obsahuje data v tabulce aplikace Excel a všechny názvy tabulek jsou stejné.
- Každý sešit má data se stejným názvem listu. To může být případ, kdy je ve všech sešitech list s názvem „souhrn“ nebo „data“ a chcete je všechny zkombinovat.
- Každý sešit má mnoho listů a tabulek a vy chcete kombinovat konkrétní tabulky/listy. Tato metoda může být také užitečná, pokud chcete kombinovat tabulky/listy, které nemají shodný název.
Podívejme se, jak v každém případě kombinovat data z těchto sešitů.
Každý sešit má data v tabulce aplikace Excel se stejnou strukturou
Níže uvedená technika by fungovala, pokud by vaše tabulky aplikace Excel byly strukturovány stejným způsobem (stejné názvy sloupců).
Počet řádků v každé tabulce se může lišit.
Pokud některé tabulky aplikace Excel obsahují další sloupce, nedělejte si starosti. Můžete si vybrat jednu z tabulek jako šablonu (nebo jako „klíč“, jak ji Power Query nazývá) a Power Query by ji použila ke kombinaci všech ostatních tabulek aplikace Excel.
V případě, že jsou v jiných tabulkách další sloupce, budou tyto ignorovány a zkombinují se pouze ty uvedené v šabloně/klíči. Pokud má například vybraná tabulka šablon/klíčů 5 sloupců a jedna z tabulek v jiném sešitu má 2 další sloupce, budou tyto další sloupce ignorovány.
Nyní mám čtyři sešity ve složce, kterou chci zkombinovat.
Níže je snímek tabulky, kterou mám v jednom ze sešitů.
Zde jsou kroky ke sloučení dat z těchto sešitů do jednoho sešitu (jako jediné tabulky).
- Přejděte na kartu Data.
- Ve skupině Získat a transformovat klikněte na rozevírací seznam Nový dotaz.
- Umístěte kurzor na „Ze souboru“ a klikněte na „Ze složky“.
- V dialogovém okně Složka zadejte cestu k souboru ke složce, která obsahuje soubory, nebo klikněte na Procházet a vyhledejte složku.
- Klikněte na OK.
- V dialogovém okně, které se otevře, klikněte na tlačítko kombinovat.
- Klikněte na „Zkombinovat a načíst“.
- V dialogovém okně „Sloučit soubory“, které se otevře, vyberte v levém podokně tabulku. Všimněte si, že Power Query zobrazuje tabulku z prvního souboru. Tento soubor by fungoval jako šablona (nebo klíč) ke kombinaci dalších souborů. Power Query by nyní hledal „tabulku 1“ v jiných sešitech a spojil ji s tímto.
- Klikněte na OK.
Tím se načte konečný výsledek (kombinovaná data) do vašeho aktivního listu.
Všimněte si toho, že spolu s daty, Power Query automaticky přidá název sešitu jako první sloupec kombinovaných dat. To pomáhá sledovat, jaká data pocházejí z kterého sešitu.
Pokud chcete data před načtením do aplikace Excel nejprve upravit, v kroku 6 vyberte „Sloučit a upravit“. Tím se otevře konečný výsledek v editoru Power Query, kde můžete data upravovat.
Několik věcí, které je třeba vědět:
- Pokud jako šablonu vyberete tabulku aplikace Excel (v kroku 7), Power Query použije názvy sloupců v této tabulce ke zkombinování dat z jiných tabulek. Pokud mají jiné tabulky další sloupce, budou ignorovány. V případě, že tyto ostatní tabulky nemají sloupec, který je ve vaší tabulce šablon, Power Query pro něj uvede hodnotu „null“.
- Sloupce nemusí být ve stejném pořadí, protože Power Query k mapování sloupců používá záhlaví sloupců.
- Protože jste vybrali Key1 jako klíč, Power Query vyhledá Table1 ve všech sešitech a všechny tyto položky zkombinuje. V případě, že nenajde tabulku aplikace Excel se stejným názvem (v tomto příkladu tabulka 1), Power Query vám zobrazí chybu.
Přidání nových souborů do složky
Nyní si dejme minutu a pochopme, co jsme udělali s výše uvedenými kroky (což nám trvalo jen několik sekund).
Data ze čtyř různých sešitů jsme spojili do jedné tabulky během několika sekund, aniž bychom museli otevřít jakýkoli ze sešitů.
Ale to není vše.
Skutečnou POWER of Power Query je, že když nyní do složky přidáte další soubory, nemusíte opakovat žádný z těchto kroků.
Vše, co musíte udělat, je přesunout nový sešit do složky, aktualizovat dotaz a automaticky zkombinuje data ze všech sešitů v dané složce.
Například ve výše uvedeném příkladu, pokud přidám nový sešit - „Mid-West.xlsx“ do složky a obnovením dotazu mi okamžitě poskytne novou kombinovanou datovou sadu.
Dotaz aktualizujete takto:
- Klepněte pravým tlačítkem na tabulku aplikace Excel, kterou jste načtli do listu, a klepněte na Aktualizovat.
- Klikněte pravým tlačítkem na dotaz v podokně „Sešitový dotaz“ a klikněte na Obnovit
- Přejděte na kartu Data a klikněte na Obnovit.
Každý sešit má data se stejným názvem listu
V případě, že nemáte data v tabulce Excel, ale všechny názvy listů (z nichž chcete data kombinovat) jsou stejné, můžete použít metodu uvedenou v této části.
Pokud jde jen o tabulková data, a ne o tabulku v Excelu, musíte si dát pozor na několik věcí.
- Názvy pracovních listů by měly být stejné. To pomůže Power Query procházet vaše sešity a kombinovat data z listů, které mají v každém sešitu stejný název.
- Power Query rozlišuje velká a malá písmena. To znamená, že list s názvem „data“ a „Data“ jsou považovány za odlišné. Podobně se za jiný považuje sloupec se záhlavím „Store“ a sloupec s „store“.
- I když je důležité mít stejné záhlaví sloupců, není důležité mít stejné pořadí. Pokud je sloupec 2 v souboru „East.xlsx“ sloupcem 4 v souboru „West.xlsx“, Power Query jej správně porovná mapováním záhlaví.
Nyní se podívejme, jak rychle kombinovat data z různých sešitů, kde je název listu stejný.
V tomto příkladu mám složku se čtyřmi soubory.
V každém sešitu mám list s názvem „Data“, který obsahuje data v následujícím formátu (všimněte si, že toto není tabulka Excel).
Zde jsou kroky ke sloučení dat z více sešitů do jednoho listu:
- Přejděte na kartu Data.
- Ve skupině Získat a transformovat klikněte na rozevírací seznam Nový dotaz.
- Umístěte kurzor na „Ze souboru“ a klikněte na „Ze složky“.
- V dialogovém okně Složka zadejte cestu k souboru ke složce, která obsahuje soubory, nebo klikněte na Procházet a vyhledejte složku.
- Klikněte na OK.
- V dialogovém okně, které se otevře, klikněte na tlačítko kombinovat.
- Klikněte na „Zkombinovat a načíst“.
- V dialogovém okně „Sloučit soubory“, které se otevře, vyberte v levém podokně možnost „Data“. Všimněte si, že Power Query zobrazuje název listu z prvního souboru. Tento soubor by fungoval jako klíč/šablona pro kombinaci dalších souborů. Power Query projde každý sešit, najde list s názvem „Data“ a všechny tyto položky zkombinuje.
- Klikněte na OK. Nyní Power Query projde každý sešit, vyhledá v něm list s názvem „Data“ a poté všechny tyto datové sady zkombinuje.
Tím se načte konečný výsledek (kombinovaná data) do vašeho aktivního listu.
Pokud chcete data před načtením do aplikace Excel nejprve upravit, v kroku 6 vyberte „Sloučit a upravit“. Tím se otevře konečný výsledek v editoru Power Query, kde můžete data upravovat.
Každý sešit má data s různými názvy tabulek nebo názvy listů
Někdy nemusí být k dispozici strukturovaná a konzistentní data (například tabulky se stejným názvem nebo list se stejným názvem).
Předpokládejme například, že data získáte od někoho, kdo tyto datové sady vytvořil, ale pracovní listy pojmenoval jako East Data, West Data, North Data a South Data.
Nebo tato osoba možná vytvořila tabulky aplikace Excel, ale s různými jmény.
V takových případech můžete stále používat Power Query, ale musíte to udělat pomocí několika dalších kroků.
- Přejděte na kartu Data.
- Ve skupině Získat a transformovat klikněte na rozevírací seznam Nový dotaz.
- Umístěte kurzor na „Ze souboru“ a klikněte na „Ze složky“.
- V dialogovém okně Složka zadejte cestu k souboru ke složce, která obsahuje soubory, nebo klikněte na Procházet a vyhledejte složku.
- Klikněte na OK.
- V dialogovém okně, které se otevře, klikněte na tlačítko Upravit. Otevře se editor Power Query, kde uvidíte podrobnosti o všech souborech ve složce.
- Podržte klávesu Control a vyberte sloupce „Obsah“ a „Název“, klikněte pravým tlačítkem a vyberte „Odebrat další sloupce“. Tím se odstraní všechny ostatní sloupce kromě vybraných sloupců.
- Na pásu karet Editoru dotazů klikněte na „Přidat sloupec“ a poté klikněte na „Vlastní sloupec“.
- V dialogovém okně Přidat vlastní sloupec pojmenujte nový sloupec jako „Import dat“ a použijte následující vzorec = Excel.Workbook ([OBSAH]). Všimněte si, že tento vzorec rozlišuje velká a malá písmena a musíte jej zadat přesně tak, jak jsem zde ukázal.
- Nyní uvidíte nový sloupec, ve kterém je tabulka. Nyní mi vysvětlete, co se tady stalo. Poskytli jste Power Query názvy sešitů a Power Query z každého sešitu (který se nyní nachází v buňce Tabulka) stáhl objekty, jako jsou listy, tabulky a pojmenované rozsahy. Můžete kliknout na prázdné místo vedle textu Tabulka a ve spodní části se vám zobrazí informace. V tomto případě, protože v každém sešitu máme pouze jednu tabulku a jeden list, můžete vidět pouze dva řádky.
- Klikněte na ikonu dvojité šipky v horní části sloupce „Import dat“.
- V datovém poli sloupce, které se otevře, zrušte zaškrtnutí políčka „Použít původní sloupec jako předponu“ a poté klikněte na OK.
- Nyní uvidíte rozbalenou tabulku, kde vidíte jeden řádek pro každý objekt v tabulce. V tomto případě je pro každý sešit uveden objekt listu a objekt tabulky samostatně.
- Ve sloupci Druh filtrujte seznam a zobrazte pouze tabulku.
- Podržte ovládací klávesu a vyberte sloupec Název a Data. Nyní klikněte pravým tlačítkem a odeberte všechny ostatní sloupce.
- Ve sloupci Data klikněte na ikonu dvojité šipky v pravém horním rohu záhlaví dat.
- V datovém poli sloupce, které se otevře, klikněte na OK. To zkombinuje data ve všech tabulkách a zobrazí se v Power Query.
- Nyní můžete provést libovolnou transformaci, kterou potřebujete, a poté přejděte na kartu Domů a klikněte na Zavřít a načíst.
Nyní mi dovolte zkusit a rychle vysvětlit, co jsme tady dělali. Protože názvy listů nebo názvy tabulek nebyly konzistentní, použili jsme k načtení všech objektů sešitů v Power Query vzorec = Excel.Workbook. Tyto objekty mohou zahrnovat listy, tabulky a pojmenované rozsahy. Jakmile jsme měli všechny objekty ze všech souborů, filtrovali jsme je, abychom zvážili pouze tabulky aplikace Excel. Poté jsme rozšířili data v tabulkách a zkombinovali je všechny.
V tomto případě jsme data filtrovali tak, aby používala pouze tabulky aplikace Excel (v kroku 13). V případě, že chcete kombinovat listy a ne tabulky, můžete filtrovat listy.
Poznámka - tato technika vám poskytne kombinovaná data, i když existuje nesoulad v názvech sloupců. Pokud například v souboru East.xlsx máte sloupec, který byl napsán nesprávně, skončíte s 5 sloupci. Power Query vyplní data do sloupců, pokud je najde, a pokud sloupec nenajde, nahlásí hodnotu jako „null“.
Podobně, pokud máte v některém z pracovních listů tabulek nějaké další sloupce, budou tyto zahrnuty do konečného výsledku.
Pokud nyní získáte více sešitů, ze kterých je třeba data kombinovat, jednoduše je zkopírujte a vložte do složky a obnovte Power Query
