V dnešním videu se podíváme na základy práce s datovými modely v Excelu. Tato metoda modelování dat je v Excelu dostupná již od verzí Excelu 2016, takže se jedná o skvělý nástroj pro analýzu dat téměř pro všechny. Ve videu si ukážeme základy datového modelování v Excelu, ukážeme si jak zdrojové tabulky do datového modelu načíst, jak mezi tabulkami vytvořit propojení a nakonec z tabulek vytvoříme propojený report s daty.
Excelový soubor ke stažení:
Power Pivot v Excelu – datové modelování v Excelu
Tvorbu datového modelu si ukážeme na příkladu, ve kterém máme tři tabulky. Z těchto tabulek potřebujeme vytvořit report, který by ukazoval celkové tržby po produktech a podle vedoucích poboček. V hlavní zdrojové tabulce máme export prodejních dat, kde máme produkty a pobočky uvedené pod kódovým označením. Druhé dvě pomocné tabulky obsahují dodatečné informace, které ve zdrojové tabulce nemáme, například informaci o vedoucím pobočky, správné názvy produktů a poboček. Tabulky chceme propojit datovým modelem právě proto, že chceme ve výsledné tabulce pracovat se všemi informacemi a nechceme tvořit jednu ohromnou tabulku, kde bychom informace spojovali například pomocí vyhledávacích funkcí.
A právě Power Pivot v Excelu umožňuje modelaci dat na základě datových modelů, relací a výpočtů. Pomocí tohoto nástroje můžeme v Excelu pracovat s obrovským množstvím dat, vytvářet propojení mezi datovými tabulkami a vytvářet složité výpočty pro analýzu dat. Pomocí datového modelu tak můžete nejen modelovat, ale i vizualizovat data v Excelu.
Od kontingenčních tabulek se liší tím, že kontingenční tabulka nám dovoluje vždy pracovat pouze s jednou zdrojovou tabulkou. Pokud chceme pracovat s daty v kontingenční tabulce z více tabulek, tak většinou tabulky spojujeme pomocí vyhledávacích funkcí nebo pomocí Power Query tabulky napojujeme nebo spojujeme. Nic z toho nemusíme s datovými modely neboli Power Pivot dělat. A právě základy modelování si ukážeme v dnešním videu.
Začneme tím, jak je možné nástroj Power Pivot v Excelu získat.
Jak povolit Power Pivot
Nástroj Power Pivot je dostupný jako součást balíku Excelu, musíme ho jen povolit. Pro povolení Power Pivot musíme kliknout v horní liště na nabídku Soubor, zde vybereme Možnosti a následně vybereme Doplňky. Ti z vás, kteří Power Pivot nemají povolený najdou v sekci Neaktivní doplňky aplikací položku, která se jmenuje Microsoft Power Pivot pro Excel. Já mám tuto položku aktivovanou, proto ji vidím v sekci Aktivní doplňky. Vedle položky Power Pivot vidíme, že se jedná o tzv. Doplněk modelu COM. Pro aktivaci tedy musíme sjet dolů a v rozbalovacím seznamu vybrat Doplňky modelu COM a potvrdit výběr. Otevře se okno, ve kterém si můžeme vybrat, které doplňky do Excelu přidáme. Zaškrtneme Microsoft Power Pivot pro Excel a výběr potvrdíme. Po potvrzení by se vám v horní liště Excelu měla objevit nová záložka s názvem Power Pivot.
Zdrojová data
Pro dnešní ukázku dat máme v excelovém sešitu tři zdrojové tabulky. V první tabulce máme export prodejních dat, která obsahuje informace o datumu, produktu a tržbě. V této zdrojové tabulce máme produkt uvedený pod kódovým označením, stejně tak zde máme uvedený kód pobočky. Vedle této zdrojové tabulky máme ještě další dvě dodatečné tabulky, ve kterých máme dodatečné informace i produktech a pobočkách.
Jak vidíme, tak mezi tabulkami existuje společný prvek, což je důležité pro vytvoření relací mezi tabulkami v datovém modelu. Zdrojovou tabulku s prodejními daty můžeme propojit s tabulkou o produktech pomocí kódu produktu. Naopak s tabulkou o pobočkách ji můžeme propojit přes kód pobočky. Pokud bychom s těmito informacemi chtěli pracovat v klasické kontingenční tabulce, tak bychom museli tabulky buď spojit přes Power Query do jedné loučené tabulky a nebo bychom do zdrojové tabulky přidali sloupce, ve kterých bychom pomocí vyhledávacích funkcí dohledali potřebné informace z tabulek.
První důležitou věc, kterou si musíte pamatovat je to, že aby propojení fungovala tak, jak mají, tak na straně pomocných tabulek, tedy tabulek poboček a produktů musí existovat seznam jedinečných položek bez duplicit. To znamená, že ve zdrojové tabulce s prodejními daty se samozřejmě kódy mohou opakovat. Nicméně v tabulce pobočka a produkty musí existovat pouze jedinečný seznam kódů, což v našem příkladu máme, žádný kód se zde neopakuje dvakrát.
Než tabulky nahrajeme do datového modelu, tak je ještě změníme na excelové tabulky. Klikneme do zdrojové tabulky a zmáčkneme klávesovou kombinaci CTRL+T, potvrdíme, že tabulka má záhlaví a z obyčejné tabulky se stane excelové tabulka. Ještě zrušíme toto typické tabulkové proužkování, karta Návrh tabulky, Styl tabulky a žádný styl. Tabulku rovnou pojmenujeme abychom se následně v tabulkách vyznali. Pojmenujeme ji třeba jako Zdroj_data. To samé uděláme s pomocnými tabulkami. Klikneme do tabulky s produkty a převedeme tabulku na excelovou tabulku. Zrušíme proužkování tabulky a tabulku pojmenujeme jako Produkt. Ještě zbývá tabulka s pobočkami. Klikneme do ní, CTRL+T, potvrdíme vytvoření tabulky a tabulku pojmenujeme jako Pobočka.
Příprava zdrojových dat je hotová.
Datový model
Teď můžeme zdrojové tabulky nahrát do datového modelu. Možností, jak nahrát data do datového modelu je několik. Můžeme data do datového modelu nahrát přes záložku Power Pivot, rovněž je tam můžeme nahrát přímo z Power Query. My v tomto videu zvolíme možnost přes kartu Power Pivot. Klikneme do zdrojové tabulky a na kartě Power Pivot najdeme možnost Přidat do datového modelu. Klikneme na tuto možnost a po chvilce se otevře okno Power Pivot.
Toto je základní ovládací okno datového modelu. Vidíme, že se nahrála tabulka zdrojových dat a dole máme záložku s názvem tabulky. Abychom mohli datový model vytvořit, tak sem musíme nahrát i ostatní tabulky. Toto okno můžeme klidně zavřít a vrátit se do Excelu. Klikneme do druhé tabulky Produkt a na kartě Power Pivot opět vybereme Přidat do datového modelu. Opět se otevřelo okno Power Pivot, kde teď již máme dvě záložky, jednu pro zdrojová data a druhou pro tabulku s produkty. To samé uděláme i s poslední tabulkou. Zavřeme okno Power Pivot a klikneme do třetí tabulky a na kartě Power Pivot potvrdíme Přidání dat do datového modelu. Teď máme v datovém modelu nahrané všechny tři zdrojové tabulky.
Propojení tabulek
Datový model není nic jiného než soubor tabulek, které jsou mezi sebou propojené přes relace. Relace mezi tabulkami vytvoříme pomocí sloupců, které mají tabulky společné. V našem případě se jedná o kódy produktů a kódy poboček. Nejjednodušším způsobem jak mezi tabulkami vytvořit propojení je přepnout se v okně Power Pivot na záložce Domů do Zobrazení diagramu. Tím se přepneme do okna, kde vidíme jednotlivé tabulky. Z těmito objekty můžeme klidně pohybovat po ploše. Stačí tabulku chytit myší a přesunout ji. To se vám bude hodit, pokud máte v datovém modelu hodně tabulek, které chcete nějak logicky uspořádat. Já si většinou umístím zdrojovou tabulku doprostřed a ostatní pomocné tabulky rozmístím okolo zdrojové tabulky. Teď můžeme přistoupit k propojení tabulek. Začneme u tabulky Produkt. Společným prvkem mezi zdrojovou tabulkou a tabulkou produktu je sloupec kód produktu. V tabulce Produkt tedy označím tento sloupec, chytím ho myší a táhnu ho do tabulky Zdroj až na sloupec Kód produktu, následně ho pustím a tím se vytvoří propojení. Jednička označuje, že na straně produkty existuje jedinečný seznam hodnot, kdežto hvězdička označuje, že v tabulce zdroj se kód produktu objevuje mnohonásobně.
To samé uděláme i pro pobočku. Společným sloupcem je kód pobočky. Je přitom jedno, které pole chytíme, zda pole ze zdrojové tabulky nebo z pomocné tabulky. Chytneme třeba pole kód pobočky ze zdroje a táhneme ho do tabulky Pobočka a pustíme ho nad polem Kód pobočka. Propojení se vytvořilo. Teď máte vytvořený první datový model v Excelu.
Teď když máme datový model vytvořený, tak se můžeme vrátit do Excelu. Můžeme buď okno Power Pivot zavřít a nebo na horní liště vybrat ikonu Excelu, tím se rovněž vrátíme do Excelu.
Zobrazení datového modelu
Ideálním způsobem, jak zobrazit data z datového modelu je pomocí kontingenční tabulky. Klikneme na kartu Vložení, kde vybereme Kontingenční tabulka. Na výběr zde máme z klasických možností, nicméně teď zde máme i možnost navíc, která se zobrazí pouze tehdy, když máme v Excelu vytvořený datový model. Máme zde možnost vytvořit kontingenční tabulku z Datového modelu. Vybereme z Datového modelu a kontingenční tabulku chceme umístit na Nový list.
Potvrdíme a do Excelu se vložil nový list, kde se vpravo objevil ovládací panel kontingenční tabulky. Od klasické kontingenční tabulky je zde ovšem podstatný rozdíl. Zatímco v kontingenční tabulce umíme pracovat jen s jednou tabulkou, takže se v polích zobrazí sloupce z tabulky, tak v kontingenční tabulce, která je založená na datovém modelu se zobrazí v polích jednotlivé tabulky, které v datovém modelu máme. Když tabulku rozklikneme, tak se objeví jednotlivé sloupce v tabulce. Jelikož máme tabulky mezi sebou propojené, tak teď můžeme tvořit kontingenční tabulku ze všech sloupců ze tří zdrojových tabulek.
Tvorba kontingenční tabulky
Vytvoříme první kontingenční tabulku. V první tabulce chceme zobrazit tržby pro jednotlivé produkty. Na řádcích chceme mít názvy produktů, tudíž vezmeme sloupec z pomocné tabulky Produkt, který se jmenuje Název produktu a přesuneme ho na řádky kontingenční tabulky. Do pole hodnoty vložíme tržby, které máme ve zdrojové tabulce, takže rozklikneme zdrojovou tabulku a přesuneme ho hodnot pole tržba. Ještě upravíme formát čísla v tabulce. Pravé tlačítko myši a formát čísla, kde vybereme českou měnu bez desetinných míst.
Ve druhé kontingenční tabulce chceme zobrazit tržby pro jednotlivé vedoucí pracovníky. Buď můžeme do listu vložit novou kontingenční tabulku a nebo si pomůžeme zkopírováním první tabulky a jen upravíme její pole. Zkopírujeme tabulku a vložíme ji vedle. Z řádků vyhodíme pole produkt a místo něho najdeme v tabulce Pobočka sloupec vedoucí pobočky. Vložíme pole na řádky a máme druhou tabulku, která zobrazuje tržba pro jednotlivé vedoucí pracovníky. Pokud by nás zajímala i informace o pobočce, tak ji můžeme z pomocné tabulky Pobočka přidat na řádky pod vedoucí pracovníky.
Takto jednoduše teď díky datovému modelu můžete pracovat s několika zdrojovými tabulkami najednou a to na základě jejich propojení v datovém modelu. To samozřejmě není jediná výhoda datových modelů, na rozdíl od kontingenčních tabulek můžeme v datovém modelu provádět výpočty a kalkulace, a to díky jazyku DAX. Stejně tak můžeme propojovat více tabulek pomocí tabulky s datumy a mnohé další. O tom ale až v dalších videích.
Jedna odpověď
👍😊