POWER PIVOT pro Excel – přidání dat do datového modelu, kalkulované sloupce
Zveřejněno: 31. 07. 2018
Kategorie: Microsoft Office
ata, se kterými pracuji v tomto článku, najdete zde. Dnes si spolu ukážeme, jak přidat data do datového modelu POWER PIVOT a následně s nimi pracovat na úrovni kalkulovaných sloupců či kalkulovaných metrik. Způsobů, jak přidat data do POWER PIVOT je několik, my si ukážeme postup přidání z tabulky v Excel.
1. Vytvořte si tabulku s daty v Excel
Pokud data nejsou v tabulce, POWER PIVOT tabulku vytvoří a následně data vloží do datového modelu. Nově vytvořená tabulka bude mít název „Tabulka 1“, což není příliš praktické pro práce s daty z více zdrojů. Proto je vhodné si tabulku vytvořit „ručně“ a dát jí relevantní název, pro náš případ použijme hodnotu „web“.
Přidání tabulky v listu Excel
Pojmenování tabulky v Excel
2. Přidejte data do datového modelu
V hlavním pásu karet klikněte na kartu „Power Pivot“ a následně „Přidat do datového modelu“, Excel vás následně přesměruje do rozhraní POWER PIVOT.
Přidání dat do datového modelu
3. Změna datových typů a formátů
V prostředí POWER PIVOT si jistě všimnete toho, že ve sloupci „Datum“ přibyly hodiny. Toto lze snadno změnit změnou formátu datového typu, viz níže.
Excel přebírá datové typy ze zdroje dat, rozdíly mohou vzniknout u zobrazovaného formátu. V případě, že máte ve zdrojovém souboru nastaveny datové typy špatně, např. čísla máte jako datový typ „text“, je vhodné toto upravit ještě ve zdrojovém souboru, případně v prostředí POWER PIVOT.
Ukázka změny datového typu
Přidání kalkulovaného sloupce
Tak a teď přichází na řadu „síla“ POWER PIVOT. Řekněme, že si chceme vypočítat obrat s DPH, tj. sečíst sloupce „Obrat za produkty“ a „DPH“. Prvním krokem je vytvoření nového kalkulovaného sloupce tak, že kliknete do prvního volného sloupce zleva (prvního sloupce pojmenovaného „Přidat sloupec“) a vložíte nový název, v našem případu „Obrat celkem“. Všimněte si, že hlavička sloupce není zbarvena zeleně, ale má černou barvu. Takto POWER PIVOT rozlišuje sloupce importované ze zdroje a uživatelem vytvořené sloupce.
K výpočtu celkového obratu nám poslouží vzorec „web[Obrat za produkty]+web[DPH]“.
Tak, jak budete postupně psát výše uvedené hodnoty, POWER PIVOT vám nabídne automatické dokončení hodnot. Na výběr budete mít hodnoty bez uvedení názvu tabulky „web“ a s názvem tabulky „web“. Pro účely tohoto příspěvku použijme hodnoty s názvem tabulky. Pokud pracujete se standardními tabulkami v Excel, již jste se jistě setkali s tím, že do vzorce píšete názvy sloupců a ne názvy konkrétních buněk. V POWER PIVOT nezadáváte vzorce pro jednotlivé buňky, ale pro konkrétní sloupce, tj. POWER PIVOT nerozumí vzorci „=D1+E1“. Pokud zadáte do libovolné buňky ve sloupci vzorec, je tento vzorec aplikován na celý sloupec
Legenda k použitému vzorci
Výpočet obratu s DPH
To bylo jednoduché, co říkáte? Pokud toto zkusíte na velkém objemu dat, hned poznáte výrazný rozdíl v rychlosti výpočtu, kdy POWER PIVOT spočítá i miliony řádků během několika málo sekund!
Nyní si umíte vytvořit kalkulovaný sloupec, v případě, že si chcete vyzkoušet i složitější výpočty a nečekat přitom na další díly tohoto seriálu, bude se vám hodit seznam funkcí DAX, případně náš kurz „Pokročilá analýza dat pomocí jazyka DAX“.
Kalkulované metriky
Velmi brzy přijdete na to, že v rozhraní POWER PIVOT není možné do tabulky zapisovat data, tj. přepisovat hodnoty sloupců ze zdroje. Stejně tak nelze přidat sloupec s agregovanými výpočty jako jsou suma, průměr apod.
V případě, že chcete zjistit sumu všech hodnot ve sloupci, můžete si vytvořit nový sloupec a do něj napsat vzorec „sum([Obrat celkem]). Jak vidíte, hodnoty ve všech řádcích jsou stejné, POWER PIVOT vypsal výsledek výpočtu do všech řádků tabulky. Z pohledu funkčnosti se nic špatného nestalo, nicméně, existuje i elegantnější varianta – kalkulované metriky
Výpočet sumy celkového obratu
A právě kalkulované metriky budou předmětem dalšího článku věnovaného POWER PIVOT v Excel.
