Po tom, čo sme zvládli vytvoriť jednoduchý vypočítaný stĺpec, je čas prejsť na ďalší spôsob ich využitia – vytvorenie nových analytických kategórií, ktoré nám podstatne zjednodušia analýzu dát. A zároveň si ukážeme možnosť vnárania funkcií do seba.Skúste si napríklad zobrať excelovský súbor s PowerPivotom z predchádzajúcich častí blogu (alebo si stiahnite tento vzorový). V tomto zošite chceme analyzovať predaje v závislosti od ceny produktov, aby sme zistili, ako cena produktu ovplyvňuje celkový predaj daného produktu. Zostavíme si kontingenčku takto:

1. do oblasti hodnôt dáme stĺpec “Cena objednávky” z tabuľky Objednávky,
2. do oblasti riadkov dáme stĺpec ListPrice z tabuľky Produkty, ktorý obsahuje predajnú cenu pre každý produkt

Kontingenčka bude vyzerať takto:

Kontingenčka zobrazí 1 riadok pre každú unikátnu cenu produktu, a k nej príslušné predaje. Čo je síce pekné, ale dosť nepraktické, ak máte stovky produktov ako v našom príklade, pretože kontingenčka je rozťahaná na 10 strán.

Lepšie by bolo, keby sme rozdelili všetky produkty do pásiem napr. takto:

1. Ak je cena produktu do 100 dolárov (alebo eur, …), nech je zaradený do kategórie “nízka cena”
2. Ak je cena produktu od 100 do 1000 dolárov, nech je zaradený do kategórie “stredná cena”
3. Ak je cena produktu od 1000 dolárov vyššie, nech je zaradený do kategórie “vysoká cena”

A toto vieme veľmi jednoducho spraviť pomocou vypočítaného stĺpca, ktorý nazveme “Cenové pásmo”, a ktorý bude zaraďovať každý produkt do jedného z hore uvedených pásiem. Tejto technike sa v angličtine hovorí banding.

Prejdeme teda do okna PowerPivotu, a tam prejdeme do tabuľky Produkty, keďže stĺpec si chceme dopočítať tam. V tejto tabuľke prejdite do posledného stĺpca, ktorý sa, ako vždy, volá “Pridať stĺpec”:

Kliknite na ľubovoľnú bunku v rámci tohto stĺpca, a napíšte tam tento vzorec:

 

=IF([ListPrice] < 100; “nízka cena”; IF([ListPrice] >= 100 && [ListPrice] < 1000;
“stredná cena”; “vysoká cena”))

Stlačte Enter, nechajte PowerPivot pár sekúnd, aby spočítal vzorec, a potom premenujte tento stĺpec z názvu CalculatedColumn1 na názov “Cenové pásmo”. Výsledok bude vyzerať takto:

Ako vidíte, pre každý produkt to vypočítalo názov jeho kategórie podľa definície vyššie. Najprv ideme stĺpec vyskúšať v kontingenčke, a potom si vysvetlíme, ako vzorec funguje. Prepnite sa naspäť do Excelu a zmeňte kontingenčku tak, aby:

1. v oblasti hodnôt zostalo pole “Cena objednávky”,
2. v oblasti stĺpcov aby bolo len pole “Cenová úroveň” z tabuľky Produkty.

Výsledok bude vyzerať takto:

Ako vidíte, máme tam naše 3 cenové kategórie, plus jednu navyše, ktorá sa nazýva “(prázdne)”. Tá tam bola pridaná samotným PowerPivotom kvôli tomu, že niektoré z objednávok odkazujú na tabuľku produktov pomocou neexistujúceho čísla produktu (cez ktoré je tabuľka Objednávky naviazaná na tabuľku Produkty). Tým pádom PowerPivot nevie, kam má takéto objednávky priradiť. Preto im vytvorí samostatnú kategóriu pre každý stĺpec z tabuľky Produkty, aby ste v kontingenčke videli, že nemáte úplne konzistentné vstupné dáta. Opravou takýchto dát sa budeme venovať v jednom z ďalších článkov.

Každopádne, práve sme z pohľadu užívateľa zredukovali množstvo riadkov z niekoľkých stoviek na tri, resp. štyri. Takáto kontingenčka má oveľa väčšiu výpovednú hodnotu ako tá pôvodná, ktorú sme použili na začiatku článku. A zároveň je aj ľahšie použiteľná a prehľadnejšia. Užívateľ má však ešte stále možnosť analyzovať dáta “po starom”, t.j. podľa cien produktov, ak by ho to zaujímalo. Väčšina z nich to však nevyužije, dokým nebudú chcieť hľadať detaily v dátach. Takže ak chcete, môžete im ten pôvodný stĺpec ListPrice skryť.

Teraz si však ideme vysvetliť náš vzorec, ktorý sme použili. Vzorec bol takýto (pre prehľadnosť je naformátovaný do viacerých riadkov):

=
IF (
     [ListPrice] < 100;
     “nízka cena”;
     IF ( [ListPrice] >= 100 && [ListPrice] < 1000; “stredná cena”; “vysoká cena” )
)

Tento vzorec sa samozrejme zavolá pre každý riadok tabuľky, v ktorej sa nachádza, tak ako aj ostatné vzorce vypočítavaných stĺpcov. Najprv sa v ňom zavolá prvá funkcia IF, v ktorej otestujeme, či cena produktu je nižšia ako 100. Na základe výsledku vrátime nasledovné:

1. ak je podmienka splnená, vrátime hodnotu “nízka cena”,
2. ak podmienka nie je splnená, zavoláme vnorenú funkciu IF, v ktorej testujeme ďalej.

Ak sa vyhodnocovanie vzorca dostane do vnorenej funkcie IF, tak v nej testujeme, či cena je väčšia alebo rovná 100 a zároveň menšia ako 1000. A ak je tá podmienka splnená, vrátime hodnotu “stredná cena”, a v opačnom prípade “vysoká cena”. Pretože logicky z toho vychádza, že ak cena nie je “menšia ako 100” ani “väčšia alebo rovná 100 a zároveň menšia ako 1000”, tak môže byť už len väčšia alebo rovná 1000.

A takto môžete vnárať do seba takmer hocikoľko funkcií, tak ako v Exceli, za predpokladu, že sú medzi sebou kompatibilné, resp. že vonkajšia funkcia dostane na vstupe výsledok z vnútornej funkcie v očakávanej forme. Jediným limitom v PowerPivote je počet vnorení funkcií do seba na 64 úrovní. To však celkom stačí, a v najhorších prípadoch sa dá obísť znova, tak ako všetko ostatné, ďalšími vypočítanými stĺpcami 🙂

Vzorec sa však dá skrátiť napr. takto:

=
IF (
     [ListPrice] < 100;
     “nízka cena”;
     IF ( [ListPrice] < 1000; “stredná cena”; “vysoká cena” )
)

Keď sa vyhodnocovanie vzorca dostane do vnorenej funkcie IF, tak v nej testujeme, či cena je menšia ako 1000. Keďže sa už ale vyhodnotila podmienka “cena je menšia ako 100” vo vonkajšej funkcii IF, tak to v skutočnosti znamená, že testujeme antipodmienku vo vonkajšej funkcii IF dokopy s podmienkou vo vnorenej funkcii IF. Čo znamená, že tá podmienka vo vnorenej funkcii IF je v skutočnosti “cena je väčšia alebo rovná 100 a zároveň menšia ako 1000”. A preto ide tento vzorec takto skrátiť.

Keby ste teraz chceli vytvoriť viacero kategórií, tak máte na výber 2 hlavné možnosti:

1. ak chcete mať fixný počet kategórií, jednoducho vnorte X-krát do seba funkciu IF podľa príkladu vyššie,
2. ak chcete mať dynamický počet kategórií podľa konfiguračnej tabuľky, kde zadáte zoznam hraníc pásiem, tak treba využiť dynamické vytváranie pásiem, ktorému sa budeme venovať v jednom z budúcich článkov.

Kategórie sa však dajú vytvárať nielen podľa “surových” dát, ale aj podľa dát, ktoré si vieme dopočítať z, alebo pomocou iných tabuliek v našom dátovom modeli. A to následne ponúka ďalšie, veľmi zaujímavé možnosti, ktorým sa budeme venovať v ďalšom článku.