Výhody Pivot by Data Model

Pri vytváraní kontingenčnej tabuľky v Exceli je v prvom dialógovom okne, kde sme požiadaní, aby sme nastavili počiatočný rozsah a vybrali miesto na vloženie kontingenčnej tabuľky, nižšie nenápadné, ale veľmi dôležité začiarkavacie políčko – Pridajte tieto údaje do dátového modelu (Pridajte tieto údaje do dátového modelu) a trochu vyššie, prepínač Použite dátový model tejto knihy (Použite dátový model tohto zošita):

Bohužiaľ, mnohí používatelia, ktorí sú s kontingenčnými tabuľkami už dlho oboznámení a úspešne ich používajú pri svojej práci, niekedy skutočne nechápu význam týchto možností a nikdy ich nepoužívajú. A márne. Koniec koncov, vytvorenie kontingenčnej tabuľky pre dátový model nám poskytuje niekoľko veľmi dôležitých výhod v porovnaní s klasickou kontingenčnou tabuľkou Excel.

Avšak predtým, ako sa pozrieme na tieto „buchty“ zblízka, poďme najprv pochopiť, čo v skutočnosti tento dátový model je?

Čo je dátový model

Dátový model (skrátene MD alebo DM = Data Model) je špeciálna oblasť v súbore Excel, kde môžete ukladať tabuľkové údaje – jednu alebo viac tabuliek, ak je to potrebné, navzájom prepojených. V skutočnosti je to malá databáza (kocka OLAP) vložená do zošita programu Excel. V porovnaní s klasickým ukladaním údajov vo forme bežných (alebo inteligentných) tabuliek na hárkoch samotného Excelu má Dátový model niekoľko významných výhod:

• Tabuľky môžu byť až 2 miliardy riadkova do hárku programu Excel sa zmestí niečo viac ako 1 milión.
• Napriek gigantickej veľkosti sa spracovanie takýchto tabuliek (filtrovanie, triedenie, výpočty na nich, súhrn budovy atď.) vykonáva veľmi rýchlo Oveľa rýchlejšie ako samotný Excel.
• S údajmi v modeli môžete vykonávať dodatočné (ak je to potrebné, veľmi zložité) výpočty pomocou vstavaný jazyk DAX.
  
• Všetky informácie načítané do dátového modelu sú veľmi silne stlačený pomocou špeciálneho vstavaného archivátora a pomerne mierne zväčší veľkosť pôvodného súboru Excel.
  

Model je riadený a vypočítaný pomocou špeciálneho doplnku zabudovaného do programu Microsoft Excel – powerpivoto ktorej som už písal. Ak ju chcete povoliť, na karte vývojka cvaknutie Doplnky COM (Vývojár – doplnky COM) a začiarknite príslušné políčko:

Ak karty vývojka (vývojár)na stuhe to nevidno, mozes to zapnout cez Súbor – Možnosti – Nastavenie pásu (Súbor — Možnosti — Prispôsobiť pás s nástrojmi). Ak v okne zobrazenom vyššie v zozname doplnkov COM nemáte Power Pivot, potom nie je súčasťou vašej verzie balíka Microsoft Office 🙁

Na karte Power Pivot, ktorá sa zobrazí, bude veľké svetlozelené tlačidlo management (Spravovať), kliknutím na ktorý sa otvorí okno Power Pivot v hornej časti Excelu, kde uvidíme obsah dátového modelu aktuálnej knihy:

Dôležitá poznámka na ceste: zošit programu Excel môže obsahovať iba jeden dátový model.

Načítajte tabuľky do dátového modelu

Ak chcete načítať údaje do modelu, najskôr premeníme tabuľku na dynamickú „inteligentnú“ klávesovú skratku ctrl+T a na karte mu dajte priateľský názov staviteľ (Dizajn). Toto je povinný krok.

Potom môžete použiť ktorúkoľvek z troch metód na výber:

• stlač tlačidlo Pridať do modelu (Pridať do dátového modelu) pútko powerpivot pútko Domov (Domov).
  
• Výber tímov Vložiť – kontingenčná tabuľka (Vložiť – kontingenčná tabuľka) a začiarknite políčko Pridajte tieto údaje do dátového modelu (Pridajte tieto údaje do dátového modelu). V tomto prípade sa podľa údajov načítaných do Modelu okamžite zostaví aj kontingenčná tabuľka.
  
• Na karte Rozšírené dátum (Dátum) kliknite na tlačidlo Z tabuľky/rozsahu (Z tabuľky/rozsahu)na načítanie našej tabuľky do editora Power Query. Táto cesta je najdlhšia, ale v prípade potreby tu môžete vykonávať ďalšie čistenie údajov, úpravy a všetky druhy transformácií, v ktorých je Power Query veľmi silný.

  Potom sa načesané dáta príkazom nahrajú do modelu Domov — Zavrieť a načítať — Zavrieť a načítať v… (Domov — Zavrieť&Načítať — Zavrieť&Načítať do...). V okne, ktoré sa otvorí, vyberte možnosť Stačí vytvoriť spojenie (Iba vytvoriť spojenie) a hlavne zaškrtni Pridajte tieto údaje do dátového modelu (Pridajte tieto údaje do dátového modelu).
  

Vytvárame súhrn dátového modelu

Na vytvorenie súhrnného dátového modelu môžete použiť ktorýkoľvek z troch prístupov:

• stlačte tlačidlo súhrnná tabuľka (kontingenčná tabuľka) v okne Power Pivot.
• Vyberte príkazy v Exceli Vložiť – kontingenčná tabuľka a prepnite do režimu Použite dátový model tejto knihy (Vložiť — Kontingenčná tabuľka — Použite dátový model tohto zošita).
• Výber tímov Vložiť – kontingenčná tabuľka (Vložiť – kontingenčná tabuľka) a začiarknite políčko Pridajte tieto údaje do dátového modelu (Pridajte tieto údaje do dátového modelu). Aktuálna „inteligentná“ tabuľka sa načíta do modelu a vytvorí sa súhrnná tabuľka pre celý model.

Teraz, keď sme prišli na to, ako načítať údaje do dátového modelu a zostaviť na nich súhrn, poďme preskúmať výhody a výhody, ktoré nám to prináša.

Výhoda 1: Vzťahy medzi tabuľkami bez použitia vzorcov

Bežný súhrn je možné zostaviť iba pomocou údajov z jednej zdrojovej tabuľky. Ak ich máte viacero, napríklad predaj, cenník, zákaznícky adresár, register zmlúv a pod., tak najskôr budete musieť zhromaždiť údaje zo všetkých tabuliek do jednej pomocou funkcií ako VLOOKUP (VLOOKUP), INDEX (INDEX), VIAC EXPOZÍCIE (ZÁPAS), SUMMESLIMN (SUMIFS) a podobne. Je to zdĺhavé, únavné a privádza váš Excel do „premýšľania“ s veľkým množstvom údajov.

V prípade zhrnutia dátového modelu je všetko oveľa jednoduchšie. Vzťahy medzi tabuľkami stačí nastaviť raz v okne Power Pivot – a je hotovo. Ak to chcete urobiť, na karte powerpivot stlač tlačidlo management (Spravovať) a potom v zobrazenom okne – tlačidlo Zobrazenie grafu (Zobrazenie diagramu). Zostáva pretiahnuť bežné (kľúčové) názvy stĺpcov (polia) medzi tabuľkami, aby ste vytvorili prepojenia:

Potom môžete v súhrne pre dátový model vložiť do súhrnnej oblasti (riadky, stĺpce, filtre, hodnoty) ľubovoľné polia z akýchkoľvek súvisiacich tabuliek – všetko sa prepojí a vypočíta sa automaticky:

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

Bežná kontingenčná tabuľka nám dáva možnosť vybrať si jednu z niekoľkých vstavaných výpočtových funkcií: súčet, priemer, počet, minimum, maximum atď. počet jedinečných (neopakujúcich sa hodnôt). S jeho pomocou si napríklad jednoducho spočítate počet unikátnych položiek tovaru (sortimentu), ktoré predávame v jednotlivých mestách.

Kliknite pravým tlačidlom myši na pole – príkaz Možnosti poľa hodnoty a na karte Operácie Vybrať Množstvo rôznych prvkov (Rozdielny počet):

Výhoda 3: Vlastné vzorce DAX

Niekedy musíte vykonať rôzne dodatočné výpočty v kontingenčných tabuľkách. V bežných súhrnoch sa to robí pomocou vypočítaných polí a objektov, zatiaľ čo súhrn údajového modelu používa miery v špeciálnom jazyku DAX (DAX = Data Analysis Expressions).

Ak chcete vytvoriť mieru, vyberte na karte powerpivot povel Opatrenia – Vytvorte opatrenie (Opatrenia – Nové opatrenie) alebo stačí kliknúť pravým tlačidlom myši na tabuľku v zozname kontingenčných polí a vybrať Pridajte mieru (Pridať mieru) v kontextovom menu:

V okne, ktoré sa otvorí, nastavte:

• Názov tabuľkykde bude uložená vytvorená miera.
• Názov miery – akýkoľvek názov nového poľa, ktorému rozumiete.
• Popis – voliteľné.
• Vzorec – najdôležitejšia vec, pretože tu buď manuálne zadáme, alebo klikneme na tlačidlo fx a vyberte zo zoznamu funkciu DAX, ktorá by mala vypočítať výsledok, keď potom hodíme našu mieru do oblasti hodnôt.
• V spodnej časti okna môžete okamžite nastaviť formát čísla pre mieru v zozname kategórie.
  

Jazyk DAX nie je vždy ľahko pochopiteľný, pretože nepracuje s jednotlivými hodnotami, ale s celými stĺpcami a tabuľkami, teda vyžaduje určitú reštrukturalizáciu myslenia po klasických vzorcoch Excelu. Stojí to však za to, pretože silu jeho schopností pri spracovaní veľkého množstva údajov je ťažké preceňovať.

Výhoda 4: Vlastná hierarchia polí

Často pri vytváraní štandardných prehľadov musíte rovnaké kombinácie polí hádzať do kontingenčných tabuliek v danom poradí, napr. Rok-Štvrťrok-Mesiac-Deň, Alebo Kategória-Produkt, Alebo Krajina-mesto-klient atď. V súhrne dátového modelu je tento problém ľahko vyriešený vytvorením vlastného hierarchie — vlastné sady polí.

V okne Power Pivot prepnite tlačidlom do režimu grafu Zobrazenie grafu pútko Domov (Domov — Zobrazenie diagramu), vyberte pomocou ctrl požadované polia a kliknite na ne pravým tlačidlom myši. Kontextové menu bude obsahovať príkaz Vytvorte hierarchiu (Vytvoriť hierarchiu):

Vytvorenú hierarchiu je možné premenovať a myšou do nej pretiahnuť požadované polia, takže sa neskôr jedným pohybom dajú hodiť do súhrnu:

Výhoda 5: Vlastné šablóny

Pokračujúc v myšlienke z predchádzajúceho odseku, v súhrne dátového modelu môžete pre každé pole vytvoriť aj vlastné sady prvkov. Napríklad z celého zoznamu miest môžete ľahko vytvoriť súbor iba tých, ktoré sú vo vašej oblasti zodpovednosti. Alebo zbierajte len svojich zákazníkov, svoj tovar atď. do špeciálnej sady.

Ak to chcete urobiť, na karte Analýza kontingenčnej tabuľky v rozbaľovacom zozname Polia, položky a množiny existujú zodpovedajúce príkazy (Analýza — Fields, Ipoložky a sady – vytvorte sadu na základe položiek riadka/stĺpca):

V okne, ktoré sa otvorí, môžete selektívne odstrániť, pridať alebo zmeniť polohu ľubovoľných prvkov a uložiť výslednú sadu pod novým názvom:

Všetky vytvorené množiny sa zobrazia na paneli Polia kontingenčnej tabuľky v samostatnom priečinku, odkiaľ ich možno ľubovoľne presúvať do oblastí riadkov a stĺpcov akejkoľvek novej kontingenčnej tabuľky:

Výhoda 6: Selektívne skrytie tabuliek a stĺpcov

Ide síce o malú, no v niektorých prípadoch veľmi príjemnú výhodu. Kliknutím pravým tlačidlom myši na názov poľa alebo na kartu tabuľky v okne Power Pivot môžete vybrať príkaz Skryť z Client Toolkit (Skryť z klientskych nástrojov):

Skrytý stĺpec alebo tabuľka zmizne z tably Zoznam polí kontingenčnej tabuľky. Je veľmi výhodné, ak potrebujete pred používateľom skryť niektoré pomocné stĺpce (napríklad vypočítané alebo stĺpce s kľúčovými hodnotami na vytváranie vzťahov) alebo dokonca celé tabuľky.

Výhoda 7. Pokročilá hĺbková analýza

Ak dvakrát kliknete na ľubovoľnú bunku v oblasti hodnôt v bežnej kontingenčnej tabuľke, Excel zobrazí na samostatnom hárku kópiu fragmentu zdrojových údajov, ktorý sa podieľal na výpočte tejto bunky. Toto je veľmi šikovná vec, oficiálne nazývaná Drill-down (zvyčajne sa hovorí „fail“).

V súhrne dátového modelu funguje tento šikovný nástroj jemnejšie. Postavením sa na ľubovoľnú bunku s výsledkom, ktorý nás zaujíma, môžete kliknúť na ikonu s lupou, ktorá vám vyskočí vedľa (tzv. Express Trends) a potom vyberte pole, ktoré vás zaujíma, v akejkoľvek súvisiacej tabuľke:

Potom aktuálna hodnota (Model = Prieskumník) prejde do oblasti filtra a súhrn bude zostavený úradmi:

Samozrejme, takýto postup možno mnohokrát opakovať, pričom sa dôsledne ponoríte do svojich údajov v smere, ktorý vás zaujíma.

Výhoda 8: Konvertujte funkcie Pivot na funkcie kocky

Ak vyberiete ľubovoľnú bunku v súhrne pre dátový model a potom vyberte na karte Analýza kontingenčnej tabuľky povel Nástroje OLAP – Konvertovať na vzorce (Analyzovať — Nástroje OLAP — Previesť na vzorce), potom sa celý súhrn automaticky prevedie na vzorce. Teraz sa hodnoty polí v oblasti riadkov a stĺpcov a výsledky v oblasti hodnôt získajú z dátového modelu pomocou špeciálnych funkcií kocky: CUBEVALUE a CUBEMEMBER:

Technicky to znamená, že teraz nemáme do činenia so sumárom, ale s niekoľkými bunkami so vzorcami, čiže s našou zostavou môžeme jednoducho robiť akékoľvek transformácie, ktoré v súhrne nie sú dostupné, napríklad do stredu vložiť nové riadky alebo stĺpce zostavy, v súhrne vykonávať akékoľvek dodatočné výpočty, usporiadať ich ľubovoľným spôsobom atď.

Zároveň samozrejme zostáva prepojenie so zdrojovými údajmi a v budúcnosti budú tieto vzorce aktualizované pri zmene zdrojov. Krása!

• Analýza plánových faktov v kontingenčnej tabuľke s doplnkami Power Pivot a Power Query
• Kontingenčná tabuľka s viacriadkovou hlavičkou
• Vytvorte databázu v Exceli pomocou doplnku Power Pivot