Výhody Pivot by Data Model

Při sestavování kontingenční tabulky v Excelu je hned v prvním dialogovém okně, kde jsme požádáni o nastavení počátečního rozsahu a výběr místa pro vložení kontingenční tabulky, níže nenápadné, ale velmi důležité zaškrtávací políčko – Přidejte tato data do datového modelu (Přidejte tyto údaje na datový model) a o něco výš vypínač Použijte datový model této knihy (Použijte datový model tohoto sešitu):

Bohužel mnoho uživatelů, kteří jsou s kontingenčními tabulkami již delší dobu obeznámeni a úspěšně je využívají při své práci, někdy smysl těchto možností skutečně nechápou a nikdy je nevyužijí. A marně. Vytvoření kontingenční tabulky pro datový model nám koneckonců poskytuje několik velmi důležitých výhod ve srovnání s klasickou kontingenční tabulkou Excelu.

Než však tyto „housky“ zvážíme zblízka, nejprve si ujasněme, co ve skutečnosti tento datový model je?

Co je datový model

Datový model (zkráceně MD nebo DM = Data Model) je speciální oblast uvnitř souboru aplikace Excel, kde můžete ukládat tabulková data – jednu nebo více tabulek, pokud je to žádoucí, vzájemně propojených. Ve skutečnosti se jedná o malou databázi (OLAP kostku) vloženou do sešitu aplikace Excel. Oproti klasickému ukládání dat ve formě běžných (či chytrých) tabulek na listech samotného Excelu má datový model několik významných výhod:

• Stolů může být až 2 miliardy řádkůa list Excelu se vejde o něco více než 1 milion.
• Navzdory gigantické velikosti je zpracování takových tabulek (filtrování, řazení, výpočty na nich, souhrn budov atd.) prováděno velmi rychle Mnohem rychlejší než samotný Excel.
• S daty v Modelu můžete provádět další (v případě potřeby velmi složité) výpočty pomocí vestavěný jazyk DAX.
  
• Všechny informace načtené do datového modelu jsou velmi silně stlačený pomocí speciálního vestavěného archivátoru a spíše mírně zvětšuje velikost původního souboru Excel.
  

Model je řízen a vypočítáván pomocí speciálního doplňku zabudovaného do Microsoft Excel – Powerpivoto kterém jsem již psal. Chcete-li to povolit, na kartě vývojka cvaknutí COM doplňky (Vývojář — doplňky COM) a zaškrtněte příslušné políčko:

Pokud tabulátory vývojka (Vývojář)na pásu karet to není vidět, můžete to zapnout skrz Soubor – Možnosti – Nastavení pásu karet (Soubor — Možnosti — Přizpůsobit pás karet). Pokud v okně zobrazeném výše v seznamu doplňků COM nemáte Power Pivot, pak není součástí vaší verze Microsoft Office 🙁

Na kartě Power Pivot, která se zobrazí, bude velké světle zelené tlačítko management (Spravovat), kliknutím na který se otevře okno Power Pivot v horní části Excelu, kde uvidíme obsah datového modelu aktuální knihy:

Důležitá poznámka na cestě: sešit aplikace Excel může obsahovat pouze jeden datový model.

Načtěte tabulky do datového modelu

Chcete-li načíst data do modelu, nejprve převedeme tabulku na dynamickou „chytrou“ klávesovou zkratku Ctrl+T a dát mu na kartě popisný název Stavitel (Design). Toto je povinný krok.

Pak můžete použít kteroukoli ze tří metod na výběr:

• Stiskněte tlačítko Přidat do modelu (Přidat do datového modelu) Karta Powerpivot Karta Domů (Domov).
  
• Výběr týmů Vložit – kontingenční tabulka (Vložit — kontingenční tabulka) a zaškrtněte políčko Přidejte tato data do datového modelu (Přidat tato data do datového modelu). V tomto případě se podle dat načtených do Modelu okamžitě sestaví také kontingenční tabulka.
  
• Na kartě Upřesnit Data (Datum) klikněte na tlačítko Z tabulky/rozsahu (Z tabulky/rozsahu)k načtení naší tabulky do editoru Power Query. Tato cesta je nejdelší, ale v případě potřeby zde můžete provádět další čištění dat, úpravy a všechny druhy transformací, ve kterých je Power Query velmi silný.

  Poté jsou načesaná data příkazem nahrána do Modelu Domů — Zavřít a načíst — Zavřít a načíst v… (Domů — Zavřít&Načíst — Zavřít&Načíst do…). V okně, které se otevře, vyberte možnost Stačí vytvořit spojení (Pouze vytvořit připojení) a hlavně zaškrtněte Přidejte tato data do datového modelu (Přidat tato data do datového modelu).
  

Vytváříme souhrn datového modelu

Chcete-li sestavit souhrnný datový model, můžete použít kterýkoli ze tří přístupů:

• stisknout tlačítko souhrnná tabulka (kontingenční tabulka) v okně Power Pivot.
• Vyberte příkazy v Excelu Vložit – kontingenční tabulka a přepněte do režimu Použijte datový model této knihy (Vložit — Kontingenční tabulka — Použijte datový model tohoto sešitu).
• Výběr týmů Vložit – kontingenční tabulka (Vložit — kontingenční tabulka) a zaškrtněte políčko Přidejte tato data do datového modelu (Přidat tato data do datového modelu). Do Modelu se načte aktuální „chytrá“ tabulka a pro celý Model se vytvoří souhrnná tabulka.

Nyní, když jsme přišli na to, jak načíst data do datového modelu a vytvořit na nich souhrn, pojďme prozkoumat výhody a výhody, které nám to přináší.

Výhoda 1: Vztahy mezi tabulkami bez použití vzorců

Běžný souhrn lze sestavit pouze pomocí dat z jedné zdrojové tabulky. Pokud jich máte více, například prodej, ceník, adresář zákazníků, registr smluv atd., budete muset nejprve shromáždit data ze všech tabulek do jedné pomocí funkcí jako VLOOKUP (VYHLEDAT), INDEX (INDEX), VÍCE EXPOZICE (ZÁPAS), SUMMESLIMN (SUMIFS) a podobně. Je to zdlouhavé, únavné a přivádí váš Excel do „přemýšlení“ s velkým množstvím dat.

V případě shrnutí datového modelu je vše mnohem jednodušší. V okně Power Pivot stačí jednou nastavit vztahy mezi tabulkami – a je hotovo. Chcete-li to provést, na kartě Powerpivot zmáčknout tlačítko management (Spravovat) a poté v okně, které se objeví – tlačítko Zobrazení grafu (Zobrazení diagramu). Zbývá přetáhnout běžné (klíčové) názvy sloupců (pole) mezi tabulkami a vytvořit tak odkazy:

Poté můžete v souhrnu pro datový model vhodit do souhrnné oblasti (řádky, sloupce, filtry, hodnoty) libovolná pole z libovolných souvisejících tabulek – vše se propojí a vypočítá automaticky:

Výhoda 2: Počítejte jedinečné hodnoty

Běžná kontingenční tabulka nám dává možnost vybrat si jednu z několika vestavěných výpočetních funkcí: součet, průměr, počet, minimum, maximum atd. V souhrnu datového modelu je k tomuto standardnímu seznamu přidána velmi užitečná funkce pro počítání počet jedinečných (neopakujících se hodnot). S jeho pomocí si například snadno spočítáte počet unikátních položek zboží (sortimentu), které v jednotlivých městech prodáváme.

Klikněte pravým tlačítkem na pole – příkaz Možnosti pole hodnoty a na kartě Operace Vybrat Počet různých prvků (Rozdílný počet):

Výhoda 3: Vlastní vzorce DAX

Někdy musíte provést různé dodatečné výpočty v kontingenčních tabulkách. V běžných souhrnech se to provádí pomocí vypočítaných polí a objektů, zatímco souhrn datového modelu používá míry ve speciálním jazyce DAX (DAX = Data Analysis Expressions).

Chcete-li vytvořit míru, vyberte na kartě Powerpivot Příkaz Opatření – Vytvořte opatření (Opatření — Nové opatření) nebo stačí kliknout pravým tlačítkem na tabulku v seznamu Pivot Fields a vybrat Přidejte míru (Přidat míru) v kontextovém menu:

V okně, které se otevře, nastavte:

• Název tabulkykam bude vytvořená míra uložena.
• Název míry – jakýkoli název nového pole, kterému rozumíte.
• Popis - volitelné.
• Vzorec – nejdůležitější věc, protože zde buď ručně zadáme, nebo klikneme na tlačítko fx a vyberte ze seznamu funkci DAX, která by měla vypočítat výsledek, když potom hodíme naše měření do oblasti Hodnoty.
• Ve spodní části okna můžete hned nastavit formát čísla pro takt v seznamu Kategorie.
  

Jazyk DAX není vždy snadno srozumitelný, protože nepracuje s jednotlivými hodnotami, ale s celými sloupci a tabulkami, tedy vyžaduje určitou restrukturalizaci myšlení po klasických vzorcích Excelu. Stojí to však za to, protože sílu jeho schopností při zpracování velkého množství dat je těžké přecenit.

Výhoda 4: Vlastní hierarchie polí

Často musíte při vytváření standardních sestav házet stejné kombinace polí do kontingenčních tabulek v daném pořadí, např. Rok-čtvrtletí-měsíc-dennebo Kategorie-Produktnebo Země-město-klient atd. V souhrnu datového modelu lze tento problém snadno vyřešit vytvořením vlastního hierarchie — vlastní sady polí.

V okně Power Pivot přepněte tlačítkem do režimu grafu Zobrazení grafu Karta Domů (Domů — Zobrazení diagramu), vyberte pomocí Ctrl požadovaná pole a klikněte na ně pravým tlačítkem. Kontextová nabídka bude obsahovat příkaz Vytvořte hierarchii (Vytvořit hierarchii):

Vytvořenou hierarchii lze přejmenovat a přetáhnout do ní myší požadovaná pole, takže je lze později jedním pohybem hodit do souhrnu:

Výhoda 5: Vlastní šablony

V návaznosti na myšlenku předchozího odstavce v souhrnu datového modelu můžete také vytvořit vlastní sady prvků pro každé pole. Například z celého seznamu měst můžete snadno vytvořit sadu pouze těch, která jsou ve vaší oblasti odpovědnosti. Nebo sbírejte pouze své zákazníky, své zboží atd. do speciální sady.

Chcete-li to provést, na kartě Analýza kontingenční tabulky v rozevíracím seznamu Pole, položky a sady existují odpovídající příkazy (Analyzovat — Fields, IPoložky a sady — Vytvořte sadu na základě položek řádků/sloupců):

V okně, které se otevře, můžete selektivně odebrat, přidat nebo změnit polohu jakýchkoli prvků a uložit výslednou sadu pod novým názvem:

Všechny vytvořené sady se zobrazí na panelu Pole kontingenční tabulky v samostatné složce, odkud je lze volně přetáhnout do oblastí řádků a sloupců jakékoli nové kontingenční tabulky:

Výhoda 6: Selektivně skryjte tabulky a sloupce

To je sice malá, ale v některých případech velmi příjemná výhoda. Kliknutím pravým tlačítkem na název pole nebo na kartu tabulky v okně Power Pivot můžete příkaz vybrat Skrýt z Client Toolkit (Skrýt z klientských nástrojů):

Skrytý sloupec nebo tabulka zmizí z podokna Seznam polí kontingenční tabulky. Je velmi výhodné, pokud potřebujete před uživatelem skrýt některé pomocné sloupce (například vypočítané nebo sloupce s klíčovými hodnotami pro vytváření vztahů) nebo dokonce celé tabulky.

Výhoda 7. Pokročilé rozbalení

Pokud dvakrát kliknete na libovolnou buňku v oblasti hodnot v běžné kontingenční tabulce, Excel zobrazí na samostatném listu kopii fragmentu zdrojových dat, který se podílel na výpočtu této buňky. Toto je velmi šikovná věc, oficiálně nazývaná Drill-down (v oni obvykle říkají „fail“).

V souhrnu datového modelu funguje tento šikovný nástroj jemněji. Tím, že se postavíte na libovolnou buňku s výsledkem, který nás zajímá, můžete kliknout na ikonu s lupou, která u ní vyskočí (tzv. Express Trends) a poté v jakékoli související tabulce vyberte libovolné pole, které vás zajímá:

Poté aktuální hodnota (Model = Průzkumník) přejde do oblasti filtru a úřady sestaví souhrn:

Takový postup lze samozřejmě mnohokrát opakovat a důsledně se ponořit do svých dat směrem, který vás zajímá.

Výhoda 8: Převod funkcí Pivot na funkce krychle

Pokud vyberete libovolnou buňku v souhrnu pro datový model a poté vyberete na kartě Analýza kontingenční tabulky Příkaz Nástroje OLAP – Převést na vzorce (Analyzovat — Nástroje OLAP — Převést na vzorce), pak se celý souhrn automaticky převede na vzorce. Nyní budou hodnoty polí v oblasti řádků a sloupců a výsledky v oblasti hodnot načteny z datového modelu pomocí speciálních funkcí krychle: CUBEVALUE a CUBEMEMBER:

Technicky to znamená, že nyní nemáme co do činění se souhrnem, ale s několika buňkami se vzorci, tj. s naší sestavou můžeme snadno provádět jakékoli transformace, které nejsou v souhrnu k dispozici, například vložit nové řádky nebo sloupce doprostřed sestavy, provádět jakékoli další výpočty uvnitř souhrnu, uspořádat je libovolným způsobem atd.

Propojení se zdrojovými daty přitom samozřejmě zůstává zachováno a v budoucnu budou tyto vzorce při změně zdrojů aktualizovány. Krása!

• Analýza plánů v kontingenční tabulce s Power Pivot a Power Query
• Kontingenční tabulka s víceřádkovým záhlavím
• Vytvořte databázi v Excelu pomocí Power Pivot