Sloučit tabulky v aplikaci Excel pomocí Power Query (snadný průvodce krok za krokem)

Díky Power Query je práce s daty rozptýlenými mezi listy nebo dokonce sešity snazší.

Jednou z věcí, kde vám Power Query může ušetřit spoustu času, je, když musíte sloučit tabulky s různými velikostmi a sloupci na základě shodného sloupce.

Níže je video, kde přesně ukazuji, jak sloučit tabulky v Excelu pomocí Power Query.

V případě, že dáváte přednost čtení textu před sledováním videa, níže naleznete písemné pokyny.

Předpokládejme, že máte tabulku, jak je uvedeno níže:

Tato tabulka obsahuje data, která chci použít, ale stále jí chybí dva důležité sloupce - „ID produktu“ a „Region“, kde obchodní zástupce působí.

Tyto informace jsou poskytovány jako samostatné tabulky, jak je uvedeno níže:

Abyste získali všechny tyto informace do jedné tabulky, budete muset tyto tři tabulky sloučit, abyste pak mohli vytvořit kontingenční tabulku a analyzovat ji, nebo ji použít pro jiné účely vytváření sestav/dashboardu.

A sloučením nemám na mysli prosté kopírování.

Budete muset namapovat příslušné záznamy z tabulky 1 s údaji z tabulek 2 a 3.

Nyní se můžete spolehnout na VLOOKUP nebo INDEX/MATCH.

Nebo pokud jste VBA, můžete k tomu napsat kód.

Ve srovnání s Power Query jsou ale tyto možnosti časově náročné a komplikované.

V tomto tutoriálu vám ukážu, jak sloučit tyto tři tabulky aplikace Excel do jedné.

Aby tato technika fungovala, musíte mít spojovací sloupce. Například v tabulce 1 a tabulce 2 je společným sloupcem „Položka“ a v tabulce 1 a tabulce 3 je společným sloupcem „Obchodní zástupce“. Všimněte si také, že v těchto spojovacích sloupcích by nemělo docházet k žádnému opakování.

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).

Sloučit tabulky pomocí Power Query

Pojmenoval jsem tyto tabulky, jak je uvedeno níže:

1. Tabel 1 - Data_prodeje
2. Tabulka 2 - Pdt_Id
3. Tabulka 3 - Kraj

Přejmenování těchto tabulek není povinné, ale je lepší uvést názvy, které popisují, o čem je tabulka.

Na jeden pokus můžete v Power Query sloučit pouze dvě tabulky.

Nejprve tedy budeme muset sloučit tabulku 1 a tabulku 2 a poté do ní v dalším kroku sloučit tabulku 3.

Sloučení tabulky 1 a tabulky 2

Chcete -li sloučit tabulky, musíte je nejprve převést na připojení v Power Query. Jakmile máte připojení, můžete je snadno sloučit.

Zde jsou kroky k uložení tabulky aplikace Excel jako připojení v Power Query:

1. Vyberte libovolnou buňku v tabulce Sales_Data.
2. Klikněte na kartu Data.
3. Ve skupině Get & Transform klikněte na ‘From Table/Range’. Tím se otevře editor dotazů.
4. V editoru dotazů klikněte na kartu „Soubor“.
5. Klikněte na možnost „Zavřít a načíst do“.
6. V dialogovém okně „Importovat data“ vyberte „Pouze vytvořit připojení“.
7. Klikněte na OK.

Výše uvedené kroky by vytvořily spojení s názvem Sales_Data (nebo libovolným názvem, který jste zadali tabulce Excel).

Opakujte výše uvedené kroky pro tabulku 2 a tabulku 3.

Až budete hotovi, budete mít tři připojení (s názvem Sales_Data, Pdt_Id a Region).

Nyní se podívejme, jak sloučit tabulku Sales_Data a Pdt_Id.

1. Klikněte na kartu Data.
2. Ve skupině Získat a transformovat data klikněte na Získat data.
3. V rozevíracím seznamu klikněte na Sloučit dotazy.
4. Klikněte na Sloučit. Tím se otevře dialogové okno Sloučit.
5. V dialogovém okně Sloučit vyberte z prvního rozevíracího seznamu možnost „Sales_Data“.
6. Ve druhém rozevíracím seznamu vyberte „Pdt_Id“.
7. V náhledu „Data_prodeje“ klikněte na sloupec „Položka“. Tím se vybere celý sloupec.
8. V náhledu „Pdt_Id“ klikněte na sloupec „Položka“. Tím se vybere celý sloupec.
9. V rozevíracím seznamu „Připojit se k druhu“ vyberte „Levý vnější (vše od prvního, odpovídající od druhého)“.
10. Klikněte na OK.

Výše uvedené kroky by otevřely editor dotazů a zobrazily by vám data z Sales_Data s jedním dalším sloupcem (z Pdt_Id).

Sloučení tabulek aplikace Excel (tabulka 1 a 2)

Nyní proces sloučení tabulek proběhne v editoru dotazů pomocí následujících kroků:

1. V dalším sloupci (Pdt_Id) klikněte na dvojitou špičatou šipku v záhlaví.
2. Z pole možností, které se otevře, zrušte zaškrtnutí všech názvů sloupců a vyberte pouze položku. Důvodem je, že ve stávající tabulce již máme sloupec s názvem produktu a pro každý produkt chceme pouze ID produktu.
3. Zrušte zaškrtnutí možnosti „Použít původní název sloupce jako předponu“.
4. Klikněte na Ok.

Tím získáte výslednou tabulku, která má každý záznam z tabulky Sales_Data a další sloupec, který má také ID produktu (z tabulky Pdt_Id).

Nyní, pokud chcete kombinovat pouze dvě tabulky, můžete načíst tento Excel, máte hotovo.

Ale musíme sloučit tři tabulky, takže je třeba udělat ještě spoustu práce.

Tuto výslednou tabulku musíte uložit jako připojení (abychom ji mohli použít ke sloučení s tabulkou 3).

Zde jsou kroky k uložení této sloučené tabulky (s daty z tabulky Sales_Data a Pdt_Id) jako připojení:

1. Klikněte na kartu Soubor
2. Klikněte na možnost „Zavřít a načíst do“.
3. V dialogovém okně „Importovat data“ vyberte „Pouze vytvořit připojení“.
4. Klikněte na OK.

Tím se nově sloučená data uloží jako připojení. Toto připojení můžete přejmenovat, pokud chcete.

Sloučení tabulky 3 s výslednou tabulkou

Proces sloučení třetí tabulky s výslednou tabulkou (který jsme získali sloučením tabulky 1 a tabulky 2) je úplně stejný.

Zde jsou kroky ke sloučení těchto tabulek:

1. Klikněte na kartu Data.
2. Ve skupině Získat a transformovat data klikněte na „Získat data“.
3. V rozevíracím seznamu klikněte na „Sloučit dotazy.
4. Klikněte na „Sloučit“. Tím se otevře dialogové okno Sloučit.
5. V dialogovém okně Sloučit vyberte z prvního rozevíracího seznamu možnost „Sloučit1“.
6. Ve druhém rozevíracím seznamu vyberte „Region“.
7. V náhledu „Sloučení 1“ klikněte na sloupec „Obchodní zástupce“. Tím se vybere celý sloupec.
8. V náhledu regionu klikněte na sloupec „Obchodní zástupce“. Tím se vybere celý sloupec.
9. V rozevíracím seznamu „Připojit se k druhu“ vyberte Left Outer (vše od prvního, shoda od druhého).
10. Klikněte na OK.

Výše uvedené kroky by otevřely editor dotazů a zobrazily by vám data z Merge1 s jedním dalším sloupcem (Region).

Nyní proces sloučení tabulek proběhne v editoru dotazů pomocí následujících kroků:

1. V dalším sloupci (Region) klikněte na dvojitou špičatou šipku v záhlaví.
2. Z pole možností, které se otevře, zrušte zaškrtnutí všech názvů sloupců a vyberte pouze Region.
3. Zrušte zaškrtnutí možnosti „Použít původní název sloupce jako předponu“.
4. Klikněte na Ok.

Výše uvedené kroky vám poskytnou tabulku, která má všechny tři tabulky sloučené (tabulka Sales_Data s jedním sloupcem pro Pdt_Id a jedním pro oblast).

Zde jsou kroky k načtení této tabulky v aplikaci Excel:

1. Klikněte na kartu Soubor.
2. Klikněte na „Zavřít a načíst do“.
3. V dialogovém okně „Importovat data“ vyberte možnosti Tabulka a Nové listy.
4. Klikněte na OK.

To by vám poskytlo výslednou sloučenou tabulku v novém listu.

Jednou z nejlepších věcí na Power Query je, že můžete snadno přizpůsobit jakékoli změny v podkladových datech (tabulka 1, 2 a 3) pouhým obnovením.

Předpokládejme například, že se Laura převede do Asie a vy získáte nová data na další měsíc. Nyní nemusíte výše uvedené kroky znovu opakovat. Vše, co musíte udělat, je aktualizovat stůl a vše za vás udělá znovu.

Během několika sekund budete mít novou sloučenou tabulku.

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).
• Zkombinujte data z více listů do jednoho listu v aplikaci Excel.
• 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)