Obsah
Slicery v kontingenčných tabuľkách je možné použiť nielen klasickým spôsobom – na filtrovanie zdrojových údajov, ale aj na prepínanie medzi rôznymi typmi výpočtov v oblasti hodnôt:
Implementácia je veľmi jednoduchá – všetko, čo potrebujete, je pár vzorcov a pomocná tabuľka. Toto všetko urobíme nie v bežnom súhrne, ale v súhrne zostavenom podľa dátového modelu Power Pivot.
Krok 1. Pripojenie doplnku Power Pivot
Ak sa karty doplnku Power Pivot nezobrazujú vo vašom Exceli, musíte ho najskôr povoliť. Sú na to dve možnosti:
- Tab vývojka - tlačidlo Doplnky COM (Vývojár – doplnky COM)
- Súbor – Možnosti – Doplnky – Doplnky COM – Prejsť (Súbor — Možnosti — Doplnky — Doplnky COM — Prejsť na)
Ak to nepomôže, skúste reštartovať program Microsoft Excel.
Krok 2: Načítajte údaje do dátového modelu Power Pivot
Ako počiatočné údaje budeme mať dve tabuľky:
Prvou je tabuľka s predajmi, podľa ktorej neskôr zostavíme sumár. Druhou je pomocná tabuľka, kde sa zadávajú názvy pre tlačidlá budúceho rezu.
Obe tieto tabuľky je potrebné previesť na „inteligentné“ (dynamické) pomocou klávesovej skratky ctrl+T alebo tím Domov – Formátovať ako tabuľku (Domov – Formátovať ako tabuľku) a je žiaduce dať im rozumné mená na karte staviteľ (Dizajn). Nech je napr. Predaj и Služby.
Potom je potrebné každú tabuľku načítať do dátového modelu – na to používame kartu powerpivot gombík Pridať do dátového modelu (Pridať do dátového modelu).
Volajú sa vypočítané polia v kontingenčnej tabuľke podľa dátového modelu Opatrenia. Vytvorme mieru, ktorá zobrazí názov stlačeného tlačidla na budúcom reze. Ak to chcete urobiť, v ktorejkoľvek z našich tabuliek vyberte ľubovoľnú prázdnu bunku na spodnom paneli výpočtu a do riadka vzorcov zadajte nasledujúcu konštrukciu:
Tu je na prvom mieste názov opatrenia (Stlačené tlačidlo) a potom za dvojbodkou a znakom rovnosti vzorec na výpočet pomocou funkcie HODNOTY DAX zabudovaný do Power Pivot.
Ak to zopakujete nie v doplnku Power Pivot, ale v službe Power BI, dvojbodka nie je potrebná a namiesto toho HODNOTY môžete využiť jeho modernejší náprotivok – funkciu SELECTEDVALUE.
Chybám v spodnej časti okna, ktoré sa objavia po zadaní vzorca, si nevšímame – vznikajú, pretože ešte nemáme súhrn a výsek, v ktorom je niečo zakliknuté.
Ďalším krokom je vytvorenie miery pre rôzne možnosti výpočtu v závislosti od hodnoty predchádzajúcej miery Stlačené tlačidlo. Tu je vzorec trochu komplikovanejší:
Poďme si to rozobrať kúsok po kúsku:
- Funkcie SWITCH – analóg vnoreného IF – kontroluje splnenie zadaných podmienok a vracia rôzne hodnoty v závislosti od splnenia niektorých z nich.
- Funkcie TRUE() – dáva logickú „true“, aby podmienky skontrolované neskôr funkciou SWITCH fungovali len vtedy, ak sú splnené, teda pravdivé.
- Potom skontrolujeme hodnotu stlačeného tlačidla a vypočítame konečný výsledok pre tri rôzne možnosti – ako súčet nákladov, priemernej kontroly a počtu jedinečných používateľov. Ak chcete spočítať jedinečné hodnoty, použite funkciu DISTINCTCOUNTa na zaokrúhľovanie – KOLO.
- Ak nie je splnená žiadna z vyššie uvedených troch podmienok, zobrazí sa posledný argument funkcie SWITCH – nastavíme ju ako figurínu pomocou funkcie PRÁZDNE ().
Krok 5. Vytvorenie súhrnu a pridanie rezu
Zostáva vrátiť sa z Power Pivotu do Excelu a vytvoriť tam kontingenčnú tabuľku pre všetky naše údaje a opatrenia. Ak to chcete urobiť, v okne Power Pivot zapnite Hlavné príkaz na výber karty súhrnná tabuľka (Domov – kontingenčná tabuľka).
potom:
- Hodíme pole Produkt z tabuľky Predaj do oblasti riadky (riadky).
- Hádzať tam pole Výsledok z tabuľky Služby.
- Kliknite pravým tlačidlom myši na pole Výsledoka vyberte si tím Pridajte ako plátok (Pridať ako Slicer).
- Hádzanie druhého opatrenia Záver z tabuľky Služby do oblasti hodnoty (Hodnoty).
Tu sú v skutočnosti všetky triky. Teraz môžete kliknúť na tlačidlá výrezu – a súčty v kontingenčnej tabuľke sa prepnú na funkciu, ktorú potrebujete.
Krása 🙂
- Výhody Pivot by Data Model
- Analýza plánov a faktov v kontingenčnej tabuľke v Power Pivot
- Vytvorte databázu v Exceli pomocou doplnku Power Pivot