Zlúčenie tabuliek v programe Excel pomocou Power Query (jednoduchý sprievodca krok za krokom)

Vďaka Power Query je práca s údajmi rozptýlenými do pracovných hárkov alebo dokonca zošitov jednoduchšia.

Jedna z vecí, pri ktorých vám Power Query môže ušetriť veľa času, je, keď musíte zlúčiť tabuľky s rôznymi veľkosťami a stĺpcami na základe zodpovedajúceho stĺpca.

Nasleduje video, kde presne ukazujem, ako zlučovať tabuľky v Exceli pomocou Power Query.

V prípade, že dávate prednosť čítaniu textu pred sledovaním videa, nižšie sú uvedené písomné pokyny.

Predpokladajme, že máte tabuľku uvedenú nižšie:

Táto tabuľka obsahuje údaje, ktoré chcem použiť, ale stále jej chýbajú dva dôležité stĺpce - „ID produktu“ a „Región“, v ktorom pôsobí obchodný zástupca.

Tieto informácie sú poskytované ako samostatné tabuľky, ako je uvedené nižšie:

Aby ste získali všetky tieto informácie do jednej tabuľky, budete musieť tieto tri tabuľky zlúčiť, aby ste potom mohli vytvoriť kontingenčnú tabuľku a analyzovať ju alebo použiť na iné účely prehľadov/dashboardu.

A zlúčením nemám na mysli jednoduchú vloženú kópiu.

Príslušné záznamy z tabuľky 1 budete musieť namapovať s údajmi z tabuliek 2 a 3.

Teraz sa môžete spoľahnúť na VLOOKUP alebo INDEX/MATCH.

Alebo ak ste šikovný VBA, môžete na to napísať kód.

Tieto možnosti sú však v porovnaní s Power Query časovo náročné a komplikované.

V tomto tutoriále vám ukážem, ako zlúčiť tieto tri tabuľky programu Excel do jednej.

Aby táto technika fungovala, musíte mať spojovacie stĺpce. Napríklad v tabuľke 1 a tabuľke 2 je spoločným stĺpcom „Položka“ a v tabuľke 1 a tabuľke 3 je spoločným stĺpcom „Predajca“. Všimnite si tiež, že v týchto spojovacích stĺpcoch by sa nemalo opakovať.

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

Zlúčenie tabuliek pomocou Power Query

Tieto tabuľky som pomenoval tak, ako je uvedené nižšie:

1. Tabuľka 1 - Údaje o predaji
2. Tabuľka 2 - Pdt_Id
3. Tabuľka 3 - Región

Premenovanie týchto tabuliek nie je povinné, ale je lepšie uviesť názvy, ktoré opisujú, o čom je tabuľka.

V Power Query môžete naraz zlúčiť iba dve tabuľky.

Najprv teda budeme musieť zlúčiť tabuľku 1 a tabuľku 2 a potom do nej v nasledujúcom kroku zlúčiť tabuľku 3.

Zlúčenie tabuľky 1 a tabuľky 2

Ak chcete zlúčiť tabuľky, musíte ich v Power Query najskôr previesť na pripojenia. Akonáhle máte pripojenia, môžete ich jednoducho zlúčiť.

Tu sú kroky na uloženie tabuľky programu Excel ako pripojenia v programe Power Query:

1. Vyberte ľubovoľnú bunku v tabuľke Údaje_predaja.
2. Kliknite na kartu Údaje.
3. V skupine Získať a transformovať kliknite na položku „Z tabuľky/rozsahu“. Tým sa otvorí editor dotazov.
4. V editore dotazov kliknite na kartu „Súbor“.
5. Kliknite na možnosť „Zavrieť a načítať do“.
6. V dialógovom okne „Importovať údaje“ vyberte „Len vytvoriť pripojenie“.
7. Kliknite na tlačidlo OK.

Vyššie uvedené kroky vytvoria spojenie s názvom Sales_Data (alebo akýmkoľvek názvom, ktorý ste zadali tabuľke Excel).

Zopakujte vyššie uvedené kroky pre tabuľku 2 a tabuľku 3.

Takže keď budete hotoví, budete mať tri spojenia (s názvom Sales_Data, Pdt_Id a Region).

Teraz sa pozrime, ako zlúčiť tabuľku Sales_Data a Pdt_Id.

1. Kliknite na kartu Údaje.
2. V skupine Získať a transformovať údaje kliknite na položku Získať údaje.
3. V rozbaľovacom zozname kliknite na položku Skombinovať dopyty.
4. Kliknite na položku Zlúčiť. Tým sa otvorí dialógové okno Zlúčiť.
5. V dialógovom okne Zlúčiť vyberte z prvého rozbaľovacieho zoznamu položku „Údaje_predaja“.
6. V druhom rozbaľovacom zozname vyberte „Pdt_Id“.
7. V ukážke „Sales_Data“ kliknite na stĺpec „Položka“. Týmto sa vyberie celý stĺpec.
8. V ukážke „Pdt_Id“ kliknite na stĺpec „Položka“. Týmto sa vyberie celý stĺpec.
9. V rozbaľovacej ponuke „Pripojiť sa k druhu“ vyberte „Vľavo (všetky od prvého, od druhého zodpovedajúce)“.
10. Kliknite na tlačidlo OK.

Vyššie uvedené kroky by otvorili editor dotazov a ukázali by vám údaje z údajov Sales_Data s ďalším stĺpcom (z Pdt_Id).

Zlúčenie tabuliek programu Excel (tabuľka 1 a 2)

Teraz bude proces zlúčenia tabuliek prebiehať v editore dotazov pomocou nasledujúcich krokov:

1. V dodatočnom stĺpci (Pdt_Id) kliknite na dvojitú šípku v hlavičke.
2. V okne možností, ktoré sa otvorí, zrušte začiarknutie všetkých názvov stĺpcov a vyberte iba položku. Dôvodom je, že v existujúcej tabuľke už máme stĺpec s názvom produktu a pre každý výrobok chceme iba ID produktu.
3. Zrušte začiarknutie políčka „Použiť pôvodný názov stĺpca ako predponu“.
4. Kliknite na Ok.

To by vám poskytlo výslednú tabuľku, ktorá má všetky záznamy z tabuľky Sales_Data a ďalší stĺpec, ktorý má tiež ID produktu (z tabuľky Pdt_Id).

Teraz, ak chcete kombinovať iba dve tabuľky, môžete si načítať tento Excel, máte hotovo.

Máme však tri tabuľky, ktoré musíme zlúčiť, takže je potrebné vykonať ešte viac práce.

Túto výslednú tabuľku musíte uložiť ako spojenie (aby sme ju mohli použiť na zlúčenie s tabuľkou 3).

Tu sú kroky na uloženie tejto zlúčenej tabuľky (s údajmi z tabuľky Sales_Data a Pdt_Id) ako pripojenia:

1. Kliknite na kartu Súbor
2. Kliknite na možnosť „Zavrieť a načítať do“.
3. V dialógovom okne „Importovať údaje“ vyberte „Len vytvoriť pripojenie“.
4. Kliknite na tlačidlo OK.

Novo zlúčené údaje sa uložia ako pripojenie. Ak chcete, môžete toto pripojenie premenovať.

Zlúčenie tabuľky 3 s výslednou tabuľkou

Proces zlúčenia tretej tabuľky s výslednou tabuľkou (ktorý sme získali zlúčením tabuliek 1 a tabuľky 2) je úplne rovnaký.

Nasledujú kroky na zlúčenie týchto tabuliek:

1. Kliknite na kartu Údaje.
2. V skupine Získať a transformovať údaje kliknite na položku „Získať údaje“.
3. V rozbaľovacej ponuke kliknite na položku „Skombinovať dopyty“.
4. Kliknite na „Zlúčiť“. Tým sa otvorí dialógové okno Zlúčiť.
5. V dialógovom okne Zlúčiť vyberte z prvého rozbaľovacieho zoznamu položku „Zlúčiť1“.
6. V druhom rozbaľovacom zozname vyberte položku „Región“.
7. V ukážke „Merge1“ kliknite na stĺpec „Obchodný zástupca“. Týmto sa vyberie celý stĺpec.
8. V ukážke regiónu kliknite na stĺpec „Predajca“. Týmto sa vyberie celý stĺpec.
9. V rozbaľovacej ponuke „Pripojiť sa k druhu“ vyberte položku Left Outer (všetky od prvého, od druhého zodpovedajúce).
10. Kliknite na tlačidlo OK.

Vyššie uvedené kroky by otvorili editor dotazov a ukázali by vám údaje zo súboru Merge1 s ďalším stĺpcom (oblasť).

Teraz bude proces zlúčenia tabuliek prebiehať v editore dotazov pomocou nasledujúcich krokov:

1. V dodatočnom stĺpci (Región) kliknite na dvojitú šípku v hlavičke.
2. V okne možností, ktoré sa otvorí, zrušte začiarknutie všetkých názvov stĺpcov a vyberte iba oblasť.
3. Zrušte začiarknutie políčka „Použiť pôvodný názov stĺpca ako predponu“.
4. Kliknite na Ok.

Vyššie uvedené kroky by vám poskytli tabuľku, ktorá má všetky tri tabuľky zlúčené (tabuľka Sales_Data s jedným stĺpcom pre Pdt_Id a jedným pre región).

Tu sú kroky na načítanie tejto tabuľky v programe Excel:

1. Kliknite na kartu Súbor.
2. Kliknite na „Zavrieť a načítať do“.
3. V dialógovom okne „Importovať údaje“ vyberte možnosti Tabuľka a Nové pracovné listy.
4. Kliknite na tlačidlo OK.

Výsledkom by bola výsledná zlúčená tabuľka v novom pracovnom hárku.

Jednou z najlepších vecí na Power Query je, že môžete jednoducho prispôsobiť akýmkoľvek zmenám v podkladových údajoch (tabuľka 1, 2 a 3) ich jednoduchým obnovením.

Predpokladajme napríklad, že sa Laura prenesie do Ázie a získate nové údaje na ďalší mesiac. Teraz už vyššie uvedené kroky nemusíte opakovať. Všetko, čo musíte urobiť, je aktualizovať stôl a všetko za vás urobí znova.

V priebehu niekoľkých sekúnd budete mať novú zlúčenú tabuľku.

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).
• Skombinujte údaje z viacerých pracovných hárkov do jedného pracovného hárka v programe Excel.
• 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)