V dnešním videu se podíváme na nástroj, který dokáže při práci se seznamy v Excelu zachránit hodiny času – a taky hodně nervů. Řeč je o slučování tabulek v Power Query. Od vnějšího, vnitřního až úplně sloučení, v tomto videu si ukážeme, jak můžete vyřešit komplexní vyhledávací dotazy mezi tabulkami bez jediné excelové funkce, a to jen s pomocí Power Query.
Excelový soubor ke stažení:
A proč by vás to mělo zajímat? Představte si, že pracujete pro pořadatele konference. Na stole vám přistanou tři různé seznamy:
- lidé, kteří se registrovali,
- lidé, kteří zaplatili poplatek,
- a lidé, kteří se konference opravdu zúčastnili.
Po skončení akce vás pořadatel osloví a chce okamžité odpovědi na několik jednoduchých, ale datově náročných otázek:
- Kdo se registroval a zároveň se zúčastnil?
- Kdo se registroval, ale nakonec nepřišel?
- Kdo se neregistroval, a přesto dorazil?
- Kdo z registrovaných stále nezaplatil?
- A můžete připravit tabulku e-mailů pro ty, kterým máme připomenout platbu?
Pokud jste doteď podobné úlohy řešili kopírováním tabulek, funkcemi SVYHLEDAT nebo XLOOKUP nebo skládáním složitých vzorců… tak tohle video vám může doslova změnit způsob práce. Power Query totiž všechny tyhle operace udělá automaticky, rychle, přesně a bez jediného vzorce. Stačí vybrat tabulky, určit společný sloupec a Power Query je propojí za vás. Čistě, logicky a s možností celý proces kdykoliv zopakovat jedním kliknutím.
Tak pojďme na to. Spojování tabulek v Power Query je nástroj, který vás posune zase v Excelu o úroveň výše.
Zdrojová data
Na listu Konference máme několik různých seznamů. V první tabulce máme uvedená jména lidí, kteří se registrovali na konferenci a byl jim přidělený registrační kód. Vedle máme seznam účastníků konference, tedy lidí, kteří se opravdu zúčastnili. Třetí seznam je export registračních kódů účastníků, kteří zaplatili registrační poplatek. A poslední tabulka obsahuje emaily všech, tedy těch co se registrovali i těch co se zúčastnili konference. Toto jsou naše vstupní data, ze kterých teď máme odpovědět na otázky.
Než začneme řešit odpovědi na jednotlivé otázky, tak převedeme všechny tyto seznamy na excelové tabulky, jelikož budeme pracovat s nástrojem Power Query, který akceptuje jako zdroj excelové tabulky v rámci stejného sešitu. Takže postupně klikneme do seznamů a převedeme je na excelové tabulky. Použijeme klávesovou kombinaci CTRL+T a potvrdíme, že tabulky mají záhlaví. Tabulky si rovnou pojmenujeme.
Teď když máme akceptovatelné zdroje pro Power Query, tak tabulky postupně nahrajeme do Power Query. Klikneme do první tabulky, karta Data a Z tabulky nebo oblasti. Tabulka se nahraje do Power Query.
Nejprve potřebujeme všechny zdrojové tabulky nahrát, takže tabulku necháme a abychom se vrátili zpátky do Excelu a nevytvářeli duplikát této tabulky, tak vybereme Zavřít a Načíst do.
A v nabídce vybereme Vytvořit pouze připojení. Tím jsme na zdrojovou tabulku vytvořili připojení, ale tabulka se nenahrála znovu do Excelu.
Toto provedeme postupně pro všechny zdrojové tabulky.
Teď máme data připravená.
Kdo se registroval na konferenci a zúčastnil se
Vrátíme se do Power Query. Chceme zjistit, kdo se registroval a zúčastnil se konference. Takže v podstatě hledáme shodu mezi tabulkami Registrace a Konference. Pokud se jméno vyskytuje na obou seznamech, tak to znamená, že se účastník jak registroval, tak se zúčastnil. Takže klikneme na dotaz Registrace a na kartě Domů vybereme Sloučit dotazy a Sloučit dotazy jako nový.
Otevře se okno, kde máme možnost vybrat si typ sloučení. Nejprve musíme zvolit tabulky, které chceme slučovat. První tabulka jsou registrovaní účastníci. Tato tabulka se z pohledu sloučení bere jako levá tabulka. A druhá tabulka je konference, tedy pravá tabulka. A teď musíme označit sloupce, podle kterých chceme tabulku sloučit, což je sloupec, který je v obou tabulkách. To je sloupec se jmény. A teď zbývá vybrat typ sloučení v rozbalovacím seznamu. Na výběr máme několik typů sloučení. Pokud chceme zjistit, kdo se vyskytuje na obou seznamech, tak vybereme Vnitřní spojení neboli Inner join. V nápovědě v závorce mám uvedeno, že se vrátí pouze odpovídající řádky, tedy řádky, které jsou shodné v obou tabulkách.
Potvrdíme spojení. A vrátí se nový dotaz, kde máme uvedená pouze jména lidí, kteří se zároveň registrovali a zúčastnili konference. Tento poslední sloupec s tabulkou zobrazuje jméno z tabulky konference.
Tento sloupec nepotřebujeme, takže ho můžeme smazat. A necháme pouze jméno a kód. Tabulka je hotová, ještě ji pojmenujeme jako Registrovaní účastníci. A teď tabulku nahrajeme zpátky do Excelu – zavřít a načíst.
A tabulka se nahrála zpátky do Excelu na samostatný list.
Kdo se registroval na konferenci, ale nakonec se nezúčastnil
Vrátíme se do Power Query a budeme postupovat stejně. Zajímá nás, kdo se registroval, tedy existuje v prvním seznamu Registrace, ale nakonec nedorazil, tedy není uvedený v seznamu Konference. Klikneme na Registraci a opět vybereme Sloučit dotazy jako nový. Opět nás zajímají tabulky Registrace a Konference. A shodným sloupcem je opět sloupec se jmény. Co se změní je ale typ spojení. Hledáme řádky, které existují v tabulce registrace (tedy levé tabulce), ale nejsou v pravé tabulce, tedy v tabulce Konference. Takže jako typ spojení vybereme Levé anti, kde v nápovědě v závorce máme uvedeno, že tento typ spojení vrátí všechny řádky z levé tabulky, kteří nemají hodnotu v pravé tabulce. Jinými slovy vrátí se seznam všech, kteří se registrovali, ale nezúčastnili se konference.
Tentokrát máme ve sloupci Konference null hodnoty, jelikož tato jména neexistují v tabulce Konference.
Dotaz pojmenujeme jako Registrovaní neúčastníci a tabulku nahrajeme zpátky do Excelu – Zavřít a načíst.
Kdo se neregistroval na konferenci ale nakonec přišel
Postup je stejný. Klikneme na tabulku Registrace a v nabídce Sloučení dotazů vybereme Sloučit dotazy jako nový. Tentokrát nás zajímají jména, která se vyskytují v tabulce Konference, ale nejsou v tabulce Registrace. Takže v nabídce sloučení vybereme Pravé anti spojení. Což je v podstatě opakem levého anti spojení.
Tentokrát je po potvrzení zobrazení jiné. V tabulce Registrace máme null hodnoty, jelikož účastníci konference nebyli registrovaní. Když ale klikneme do tabulky v Konferenci, tak se dole objeví všechna jména lidí, kteří se zúčastnili konference, ale nebyli registrovaní.
Abychom tato jména dostali do tabulky, tak v záhlaví Konference musíme rozkliknout dvojitou šipku ikony. Potvrdíme rozbalení sloupce a odstraníme dva první sloupce, které obsahují pouze null hodnoty.
Dotaz nakonec pojmenujeme jako Neregistrovaní účastníci.
Kdo ze zaregistrovaných ještě nezaplatil
V tomto případě nás zajímá, kdo z těch, kteří jsou na seznamu Registrace jsou i na seznamu Poplatky. V seznamu poplatků tentokrát nemáme jména, ale registrační kódy. Hledáme lidi, kteří nezaplatili, takže hledáme řádky z levé tabulky, které nejsou v pravé tabulce. To znamená že se opět jedná o Levé anti spojení mezi tabulkami Registrace a Poplatky.
Poslední sloupec s Poplatky opět nepotřebujeme, tak ho smažeme a dotaz pojmenujeme jako Nezaplaceno.
Připravit tabulku s emaily pro připomenutí těm, kteří nezaplatili poplatek
Tento úkol je v podstatě klasickým vyhledáváním v Excelu, kdy ke jménům lidí, kteří nezaplatili, potřebujeme z tabulky Emaily dohledat email. I tento typ úkolu lze vyřešit pomocí Sloučení tabulek. A můžeme sloučit dotaz, který jsme před chvíli vytvořili. Tabulku s těmi, kteří nezaplatili již máme vytvořenou a nazvali jsme ji jako Nezaplaceno. Takže se napojíme na tuto tabulku, což bude levá tabulka a jako pravou tabulku vybereme tabulku s emaily. Jako typ sloučení vybereme Levé vnější spojení, což je typ spojení, které ke každému řádku v levé tabulce přiřadí odpovídající hodnotu z pravé tabulky.
Teď když klikneme do tabulky ve sloupci Emaily, tak se dole rozbalí jméno a email.
Abychom emaily dostali do sloupce Emaily, tak opět v záhlaví sloupce Emaily rozklikneme dvojitou šipku. A tentokrát chceme vrátit do sloupce pouze emaily, takže odškrtneme Jméno a potvrdíme.
Tabulka je hotová – pojmenujeme ji jako nezaplacené emaily a pošleme ji zpátky do Excelu.
Úkoly jsou vyřešené.
