V dnešním videu si ukážeme, jak můžeme spojovat tabulky pomocí excelového nástroje Power Query. Pomocí Power Query můžeme tabulky nejen napojovat na sebe, což jsme si ukázali v dřívějším videu Jak sloučit více tabulek do jedné pomocí Power Query, ale můžeme rovněž tabulky spojovat, což je obdoba vyhledávání informací v Excelu pomocí vyhledávacích funkcí jako SVYHLEDAT nebo INDEX & POZVYHLEDAT. Metoda je velmi jednoduchá, snadná na vytvoření a vše je samozřejmě plně dynamické. Kdykoliv se data změní nebo přidáte nová data do tabulky, tak stačí obnovit připojení u tabulek a Power Query za vás všechnu práci odvede.
Excelový soubor ke stažení
Jak sloučit tabulky v Power Query
V dnešním příkladu máme dvě tabulky. V první tabulce máme údaje o tržbách jednotlivých produktů, které zde máme uvedené pod kódovým označením. Ve druhé tabulce máme k jednotlivým produktům uvedené produktové názvy, lokalitu a výši marže v procentech z tržeb. Do první tabulky bychom rádi dohledali z druhé tabulky produktový název, lokalitu a příslušnou marži. Samozřejmě bychom to mohli udělat pomocí excelových vyhledávacích funkcí jakými jsou třeba SVYHLEDAT, XLOOKUP nebo INDEX & POZVYHLEDAT. My si dnes ukážeme, jak ke spojení tabulek použít nástroje Power Query.
Jediným předpokladem pro sloučení tabulek pomocí Power Query je, že v obou tabulkách existuje nějaký společný prvek. Stejně jako u vyhledávacích funkcí. Musí tedy existovat hodnoty, které jsou v obou tabulkách stejné. V našem případě je spojovacím prvkem kódové označení produktu. Každý produkt má jedinečný kód, který je uvedený jak v tabulce s prodejními daty, tak ve druhé tabulky s dodatečnými informacemi. Pomocí tohoto spojovacího prvku tabulky v Power Query sloučíme.
První, co musíme udělat je, nahrát obě dvě tabulky do Power Query. Nicméně abychom to mohli udělat, tak nejprve změníme tabulky na excelové tabulky. Klikneme do první tabulky a pomocí klávesové kombinace CTRL+T změníme tabulku na excelovou tabulku. Potvrdíme, že tabulka má záhlaví a potvrdíme. Zrušíme toto typické proužkování a to z karty Návrh tabulky, kde ve stylech vybereme Žádný styl. Rovnou ještě pojmenujeme tabulku jako ProdejníData. To samé uděláme i s druhou tabulkou. Klikneme do tabulky, zmáčkneme klávesovou kombinaci CTRL+T a změníme tabulku na excelovou tabulku. Zrušíme proužkování tabulky a tuto druhou tabulku pojmenujeme jako ZdrojováData.
Teď můžeme tabulky nahrát do Power Query. Klikneme do první tabulky ProdejníData a na kartě Data najdeme sekci Načíst a Transformovat data a zde vybereme Načíst data, Z jiných zdrojů a z Tabulky nebo oblasti. Po chvilce se otevře excelový nástroj Power Query.
Do Power Query se nahrála první tabulka s prodejními daty. Jsme teď v klasickém zobrazení Power Query, takže pokud bychom data potřebovali ještě upravit a vyčistit, tak zde máme možnost.
Jak vidíme, tak se nám datum zobrazuje i s časem, což nepotřebujeme, takže upravíme datový typ sloupce. Najedeme na záhlaví sloupce, klikneme na ikonu datového sloupce a zde vybereme pouze Datum. Potvrdíme změnu.
Více úprav s tabulkou nepotřebujeme, takže se můžeme vrátit do Excelu a nahrát do Power Query i druhou tabulku. Na kartě Domů najdeme Zavřít a Načíst, ale neklikneme přímo na tuto ikonu, ale rozklikneme šipku. Zde vybereme Zavřít a Načíst do. Důvod, proč to děláme je ten, že pokud bychom klikli jen na Zavřít a Načíst, tak se tabulka znovu nahraje do Excelu na nový list. Budeme tedy mít duplikát tabulky ve stejném sešitu, což není potřeba. V nabídce Importovat data vybereme Pouze vytvořit připojení.
Tím se tabulka načetla do Power Query a vytvořila si na tabulku pouze připojení a nebude ji tedy po zavření znovu nahrávat na speciální list v Excelu, čímž se nám nebudou zbytečně duplikovat data. Potvrdíme vytvoření připojení a počkáme až se připojení vytvoří. V pravém ovládacím panelu vidíme, že se připojení na tabulku vytvořilo a to, že jsme na tabulku pouze připojeni poznáme podle toho, že pod názvem tabulky je Jenom připojení.
To samé uděláme i s druhou tabulkou ZdrojováData. Klikneme do tabulky a před kartu Data najdeme Načíst data, kde vybereme z jiného zdroje a z tabulky nebo oblasti. Zdrojová tabulku se rovněž načte do Power Query a v případě potřeby máme možnost provést patřičné úpravy. S touto tabulkou nemusíme žádné úpravy provádět, datové typy jsou v pořádku, takže teď můžeme stejně jako první tabulku nahrát zpátky a to tím, že rovněž vytvoříme pouze připojení na tabulku.
Takže Zavřít a Načíst a Zavřít a Načíst do. Pokud byste se spletli a omylem klikli na Načíst a Zavřít, tak se nic neděje. Tabulka by se v tom případě nahrála na samostatný list v Excelu. Rozdíl vidíme v pravém ovládacím panelu. Zatímco první tabulku máme propojenou jen připojením, tak druhá tabulka je načtená na samostatný list, což poznáme podle toho, že vidíme, kolik řádků se do tabulky načetlo. Pokud jsme se spletli a chtěli jsme na tabulku vytvořit pouze připojení, tak máme možnost tento list, na který se tabulka nahrála smazat a tím se na tabulku vytvoří pouze připojení. Klikneme na list a vybereme Odstranit a Odstranit list. List se odstranil a tím pádem se na tabulku vytvořilo pouze připojení.
Teď když máme obě tabulky nahrané do Power Query, tak můžeme přistoupit ke sloučení tabulek. V pravém ovládacím panelu klikneme pravým tlačítkem na tabulku ProdejníData a vybereme Sloučit. Tím se otevře okno Sloučení.
Zde v rozbalovacím seznamu máme možnost vybrat, které tabulky chceme sloučit. My máme v Excelu pouze dvě tabulky, takže tou první je tabulka s prodejními daty. Jako druhou tabulku, ze které chceme data slučovat vybereme tabulku ZdrojováData. Teď musíme vybrat sloupec, podle kterého chceme tabulky pomocí Power Query spojit. Tabulky chceme spojit pomocí sloupce kód produktu. Všimněte si, že pro Power Query není důležité, aby se spojovací sloupce jmenovaly stejně. V tabulce s Prodejními daty máme sloupec nazvaný jako produkt, ve druhé tabulce jako kód. Klikneme tedy na sloupec a tím Power Query označíme, podle kterých sloupců chceme tabulky spojit.
Kromě sloupců máme na výběr ještě z typu sloučení. Na výběr máme několik možností sloučení, o kterých na Akademii Excelu budeme mít v budoucnu podrobnější video. Pro teď bude stačit, že vybereme první základní možnost Levé vnější. Tím pádem se k první tabulce, tedy k tabulce s Prodejními daty připojí odpovídající položky z druhé tabulky, což je přesně to, co potřebujeme. Zde dole vidíme, že v prodejní tabulce máme celkem 74 záznamů a že k těmto 74 záznamům bylo úspěšně přiřazeno 74 záznamů z druhé tabulky. Vše tedy vypadá dobře, takže sloučení potvrdíme.
Po potvrzení se přepneme do Power Query, kde se zobrazí tabulka ProdejníData, do které přibyl nový sloupec. Nezapomeneme pojmenovat tabulku a nazveme ji jako Sloučená Data. V tomto posledním sloupci se zobrazil odkaz Table jako Tabulka. Pokud klikneme na první buňku do tohoto sloupce, tak se zde dole objeví, co v této tabulce je. A vidíme, že se v podstatě jedná o záznam z druhé zdrojové tabulky. V prvním řádku máme produkt A101, což je Produkt A. Správně se tedy na řádek připojili informace ze zdrojové tabulky s produktem A.
Klikneme na druhý řádek, kde máme produkt B201 a vidíme, že zde máme opět správný záznam ze zdrojové tabulky o produktu B, který má kód B201. Důvod, proč se v posledním sloupci zobrazil tento odkaz tabulka je, že jsme ještě Power Query neřekli, které všechny informace z druhé tabulky chceme do první tabulky sloučit.
Teď co musíme udělat je, kliknout v záhlaví tohoto nového sloupce na tyto dvě šipky. Tím se rozbalí filtr záhlaví Zdrojové tabulky, kde vidíme názvy ze záhlaví. Zde si pomocí zaškrtávání označíme, které sloupce ze Zdrojové tabulky chceme do Prodejní tabulky přiřadit. Chceme přiřadit produktové označení, lokalitu a marži. Kód nechceme, jelikož ten už v prodejní tabulce máme. Před potvrzením ještě odškrtneme Používat původní název sloupce jako předponu.
Potvrdíme a v tu chvíli se do prodejní tabulky nahráli příslušné sloupce ze zdrojové tabulky.
Ještě si ukážeme, co by se stalo, kdybychom neodškrtli poslední pole o původním názvu. Smažu tento poslední krok, čímž se vrátím do předchozího bodu a znovu vyberu sloupce, které chci připojit. Tentokrát toto pole nechám zaškrtnuté.
Potvrdíme a rozdíl je v tom, že se teď před název sloupce přidal ještě název tabulky, což je většinou zbytečné. Proto je lepší pole odškrtávat. Vrátím se do původního bodu, kde jsme sloupce připojili správně.
Všimněte si, že se u připojeného sloupce produkt objevila jednička, jelikož název produkt už v tabulce existoval. Bylo by tedy lepší ho přejmenovat. Dvojitě klikneme na záhlaví a sloupec přejmenujeme na Produktové označení.
Když už jsme v Power Query, tak ještě rovnou můžeme dopočítat marži z produktů. Známe tržbu a teď jsme do tabulky i připojili procento marže z tržby. Potřebujeme tedy přidat nový sloupec, kde bychom marži dopočítali v absolutní hodnotě. Klikneme tedy na kartu Přidání sloupce a zde vybereme Vlastní sloupec. Název sloupce bude Marže hodnota a výpočet bude Tržba * Marže. Takže dvojitě klikneme na sloupec Tržba, napíšeme krát a znovu dvojitě klikneme na sloupec s Marží.
Vzorec je hotový, tak ho potvrdíme. Do tabulky se přidal nový sloupec Marže hodnota.
Ještě u něho musíme změnit datový typ. Stačí nám celé číslo.
Tabulka je připravená a můžeme ji nahrát zpátky do Excelu. Teď tabulku chceme nahrát na samostatný list v Excelu, takže vybereme Zavřít a Načíst. Tabulka se po chvilce načte na samostatný list v Excelu.
Výhodou napojení tabulek na Power Query je jejich snadná aktualizace v případě nových dat. Na listu Nová data mám schovaná nová data. V prodejních datech se nám přitom objevil nový produkt. Nová data nakopírujeme do prodejní tabulky a nový produkt nakopírujeme do zdrojové tabulky. Teď jediné co stačí udělat je přepnout se na list se sloučenou tabulkou a na kartě Data vybrat Aktualizovat vše. Všimněte si, že teď máme ve sloučené tabulce celkem 74 řádků. Power Query začne obnovovat spojení mezi tabulkami a po chvilce vrátí nová data do sloučené tabulky. A teď máme ve sloučené tabulce celkem 82 řádků a úspěšně nám přibyl i nový produkt, stejně jako se rovnou dopočítala marže v absolutní hodnotě.
Takto jednoduše můžete spojovat tabulky pomocí Power Query v Excelu. Výhodou je, že pokud chcete z tabulek přiřazovat velké množství informací, tak nemusíte psát několik excelových funkcí, ale stačí vám tabulky spojit v Power Query.
Jedna odpověď
😊