V prechádzajúcom článku sme si ukázali, ako využíť linkované tabuľky. Dnes si ukážeme pokročilejšiu techniku, ako môžeme využiť linkované tabuľky na vytvorenie vlastných kategórií dát. Táto technika sa dá samozrejme použiť aj s bežnými typmi tabuliek, ale výhodou použitia linkovaných tabuliek je to, že môžete ľahko zmeniť počet kategórií, aj ich rozsah, a výsledok hneď uvidíte v PowerPivote. Preto použijeme aj my linkované tabuľky.

V článku o klasickej kategorizácii dát sme si ukázali, ako vytvoriť fixný počet kategórií a ich rozsah pomocou funkcie IF. Veľakrát však pri tvorbe riešenia ešte neviete, či budete potrebovať 3, 4, 5 alebo viac kategórií, a veľakrát neviete ani ich presný rozsah (od-do). To zistíte až neskôr, metódou pokus/omyl, a medzitým sa upíšete k smrti pri neustálej zmene vnorených IFov… A keď ich aj zo začiatku viete presne, tak neskôr sa situácia z rôznych dôvodov zmení, a Vy sa tomu potrebujete prispôsobiť. Preto si teraz ukážeme, ako to spraviť dynamicky, tzn. pomocou tabuľky, v ktorej budeme mať zapísaný na každom riadku názov kategórie, rozsah od-do, a poradie zobrazovania. A vždy, keď v nej niečo zmeníme – či už počet riadkov alebo len samostatné čísla – tak nám to PowerPivot prepočíta.

Ideme teda na to. Ako štartovací súbor použijeme excelovský súbor z predchádzajúceho článku. V ňom budeme kategorizovať produkty podľa ich obratu do kategórií predajnosti. V tomto súbore si na liste Číselníky vytvoríme nasledovnú tabuľku, a stlačením Ctrl+T ju prekonvertujeme na pomenovanú oblasť:

V nej zadefinujeme 3 kategórie predaja, s hranicami od 0 do 10 tisíc, od 10 tisíc do 1 milióna, a od 1 milióna do 1 miliardy. Posledná hranica zvyčajne musí byť nejaké dostatočne veľké číslo, aby sa do tohto intervalu zmestili aj tie najväčšie predaje. Takisto si dajte pozor na to, aby sa Vám rozsahy kategórií neprekrývali (s výnimkou hraničných čísel), pretože potom to nebude vracať správne výsledky.

Keď teda máme zadefinované kategórie v tabuľke, pripojíme si ju do nášho dátového modelu. V excelovskom menu, na lište POWERPIVOT, kliknite na Pridať do modelu údajov:

Následne nám to pridá túto tabuľku do PowerPivotu ako linkovanú tabuľku. Túto tabuľku si premenujeme (ak ste to nespravili ešte v Exceli pred pripájaním) na „Kategórie predaja“.

Teraz ale jedna zásadná zmena. Túto tabuľku totižto NEprepojíme na ostatné tabuľky. Pretože medzi dátami v našom modeli a touto novou tabuľkou nie je žiaden vzťah. A namiesto toho si vo vzorci pri kategorizácii vyhľadáme príslušný riadok v tejto tabuľke.

Prejdite teda do tabuľky Produkty, a vytvorte nový vypočítaný stĺpec. Ak ešte nemáte ku každému produktu vypočítaný obrat za produkt, tak vytvorte vypočítaný stĺpec s názvom „Obrat za produkt“ s týmto vzorcom:

=SUMX(RELATEDTABLE(‚Objednávky‘); ‚Objednávky'[Cena objednávky])

A podľa tohto stĺpca a našej tabuľky „Kategórie predaja“ teraz budeme kategorizovať produkty podľa ich predaja do im prislúchajúcich kategórií. To spravíme tak, že v tabuľke Produkty vytvoríme ďalší vypočítaný stĺpec, nazveme ho „Predajnosť produktu“, a zadáme doňho tento vzorec:

=CALCULATE(VALUES(‚Kategórie predaja'[Názov kategórie]);
FILTER(‚Kategórie predaja‘; [Obrat za produkt] >= ‚Kategórie predaja'[Rozsah od] && [Obrat za produkt] < ‚Kategórie predaja'[Rozsah do])
)

Výsledok bude vyzerať takto:

A ako to celé funguje? Použili sme hneď niekoľko trikov a využili sme to, ako funguje na pozadí dátový model s funkciou CALCULATE. V tomto prípade sme dali spočítať funkciu VALUES v kontexte aktuálneho riadku, ktorý sme odfiltrovali funkciou FILTER. Všimnite si ale to, že vo funkcii FILTER filtrujeme tabuľku „Kategórie predaja“, ktorá ale nemá žiaden vzťah/prepojenie na tabuľku Produkty, v ktorej počítame tento vzorec. V takomto prípade potom platí, že PowerPivot priradí aktuálne počítanému riadku všetky riadky z neprepojenej tabuľky. Čiže ku každému jednému produktu v tabuľke Produkty teraz patria všetky riadky z tabuľky „Kategórie predaja“. Tie ale potrebujeme odfiltrovať funkciou FILTER tak, aby nám to vrátilo len riadok s hľadanou kategóriou produktu. To docielime podmienkou, ktorá je zadaná ako druhý parameter funkcie FILTER – čiže „vráť také riadky z tabuľky ‚Kategórie predaja‘, ktoré majú ‚Obrat za produkt‘ väčší alebo rovný stĺpcu ‚Rozsah od‘ a zároveň menší ako stĺpec ‚Rozsah do‘„. Ak ste v konfiguračnej tabuľke zadali neprekrývajúce sa intervaly od-do, a zároveň medzi týmito intervalmi nie sú „diery“, tak tú podmienku bude spĺňať vždy len 1 riadok. A tento riadok nám vráti funkcia FILTER (áno, funkcia FILTER technicky vracia vždy riadky).

Keďže ale vo výsledku potrebujeme len jednu hodnotu z tohto riadku, ktorá sa uloží do aktuálne počítanej bunky v našom vypočítavanom stĺpci, pomôžeme si funkciami VALUES a CALCULATE. Funkcia VALUES vracia zo zadanej tabuľky len 1 zadaný stĺpec. Všimnite si, že v nej používame tabuľku „Kategórie predaja“, ale táto funkcia sa už počíta v kontexte filtrov funkcie CALCULATE. Čiže v skutočnosti sa počíta už len nad tým 1 odfiltrovaným riadkom, ktorý je výsledkom funkcie FILTER. A keď z tohto jedného riadku „vysekneme“ funkciou VALUES len 1 stĺpec – „Názov kategórie“ – tak nám zostane len 1 riadok s 1 stĺpcom, v ktorom bude názov hľadanej kategórie. Funkcia CALCULATE na pozadí takýto prípad automaticky prekonvertuje na hodnotu, a keďže je to už hodnota, tak to PowerPivot bude vedieť uložiť do aktuálne počítanej bunky vo vypočítavanom stĺpci. A takto elegantne vypočítame a dotiahneme názov kategórie ku každému produktu 🙂

Keď tento nový stĺpec „Predajnosť produktu“ použijeme v kontingenčke, kde na riadkoch bude tento stĺpec, a v hodnotách bude merítko Obrat, tak výsledok bude vyzerať takto:

Takže takto si otestujeme, že to funguje. Ideme sa s tým ale pohrať ešte viac – rozšírime si našu konfiguračnú tabuľku kategórií v Exceli na 4 kategórie, aby sme videli, či/ako sa nám to prepočíta v PowerPivote. Zmeňte teda konfiguračnú tabuľku kategórií takto:

Strednú kategóriu sme rozdelili na dve – prvú od 10 tisíc do 100 tisíc, a druhú od 100 tisíc do 1 milióna. Teraz by mal PowerPivot sám zaktualizovať dáta – to je jedna z noviniek v Exceli 2013. Táto novinka však takmer nikdy nefunguje, takže keď prejdete naspäť do kontingenčky, tak bude vyzerať stále rovnako:

To vieme dať do poriadku 2 spôsobmi:

1. choďte do PowerPivotu a potom naspäť do Excelu,
2. alebo v Exceli choďte v menu do záložky POWERPIVOT a tam kliknite na ikonku Aktualizovať všetko:

V oboch prípadoch sa zmeny v linkovanej tabuľke prelejú do PowerPivotu, PowerPivot prepočíta zmeny v našej kategorizácii dát, a Excel následne zobrazí správnu kontingenčku so 4 kategóriami:

A takto to treba robiť vždy, keď spravíte zmeny do linkovanej tabuľky, a PowerPivot to automaticky neprepočíta. Chyba nie je v PowerPivote, ale v Exceli, takže v PowerPivote s tým momentálne nič nespravíme.

Pozrite sa ešte ale na poradie kategórií v kontingenčke, vs. poradie kategórií v konfiguračnej tabuľke. Podľa konfiguračnej tabuľky by sa mala najprv zobraziť kategória „stredný predaj – slabý“ a až po nej kategória „stredný predaj – dobrý“. V kontingenčke sa ale zobrazujú kategórie abecedne, pretože stĺpec Poradie z konfiguračnej tabuľky sme nikde nepoužili. Preto si v PowerPivote, v tabuľke Produkty, vytvoríme ďalší vypočítaný stĺpec, do ktorého si toto poradie dotiahneme. A potom dáme zotriediť stĺpec „Predajnosť produktu“ podľa tohto stĺpca.

Vytvorte si teda ďalší vypočítaný stĺpec s názvom „Poradie predajnosti“ a týmto vzorcom:

=CALCULATE(VALUES(‚Kategórie predaja'[Poradie]);
FILTER(‚Kategórie predaja‘; [Obrat za produkt] >= ‚Kategórie predaja'[Rozsah od] && [Obrat za produkt] < ‚Kategórie predaja'[Rozsah do]))

Tento stĺpec dajte Skryť v klientských nástrojoch, pretože pre užívateľa bude zbytočný. Výsledok bude vyzerať takto:

Ako posledný krok dajte zoradiť stĺpec „Predajnosť produktu“ podľa tohto nového stĺpca. Keď sa potom prepnete naspäť do kontingenčnej tabuľky, tak poradie kategórií už bude správne:

Takto teda najprv vytvoríte dynamickú kategorizáciu produktov, a následne, pomocou ďalšieho vypočítavaného stĺpca, nastavíte aj správne poradie kategórií v kontingečke. A takto to robia aj profíci, ktorí nič nenechajú na náhodu 🙂