Business intelligence v Exceli: Analýza hypotéz a kontingenčné tabuľky
Manažéri, ekonómovia či marketingoví špecialisti hľadajú kvalitné, dostupné a intuitívne ovládateľné nástroje, ktoré by im umožnili analyzovať údaje, s ktorými pracujú. Chcú vytvárať reporty bez toho, aby potrebovali asistenciu analytikov a špecialistov z IT oddelení. Často však majú požadované nástroje na dosah ruky, presnejšie vo svojom počítači v podobe aplikácie Excel z kancelárskeho balíka Office. V článku predstavíme dva užitočné analytické a reportovacie nástroje.
Analýza hypotéz
Analýza hypotéz je what-if proces zmeny hodnôt v bunkách tabuľky, vďaka ktorému môžete vidieť, ako tieto zmeny ovplyvnia výsledky vzorcov v hárku. Pomocou nástrojov na analýzu hypotéz v Exceli môžete v jednom alebo vo viacerých vzorcoch použiť rôzne množiny hodnôt a preskúmať tak rôzne výsledky. Analýzu hypotéz môžete napríklad využiť na zostavenie dvoch rozpočtov, pričom sa v obidvoch predpokladá určitá úroveň výnosov. Prípadne môžete zadať výsledok, ktorý má prostredníctvom vzorca vzniknúť, a potom môžete určiť, akou množinou hodnôt sa tento výsledok dosiahne. Súčasťou Excelu sú tri typy nástrojov analýzy hypotéz: scenáre, hľadanie riešenia a tabuľky údajov.
Scenáre a tabuľky údajov používajú množiny vstupných hodnôt a určujú možné výsledky. Tabuľka údajov funguje len s jednou alebo dvoma premennými, umožňuje však vložiť veľa rôznych hodnôt pre tieto premenné. Scenár môže mať viac premenných, ale zvládne prijať len 32 hodnôt. Hľadanie riešenia sa líši od scenárov a tabuliek údajov tým, že sa definuje požadovaný výsledok a zisťujú sa možné vstupné hodnoty, ktoré by viedli k tomuto výsledku.
Scenáre
Scenár je množina hodnôt, ktoré Excel ukladá a môže automaticky dosadiť do buniek v hárku. V hárku môžete vytvárať a ukladať rôzne skupiny hodnôt a potom môžete prepnúť na ktorýkoľvek z týchto nových scenárov a zobraziť rôzne výsledky. Predpokladajme napríklad, že máte dva scenáre rozpočtu: scenár najhoršieho prípadu a scenár najlepšieho prípadu. Na vytvorenie oboch scenárov v jednom hárku a na prepínanie medzi nimi môžete použiť správcu scenárov. Pri každom scenári zadajte bunky, ktoré sa menia, a hodnoty, ktoré sa v tomto scenári používajú. Pri prepínaní medzi scenármi sa bunka s výsledkom mení, čo vlastne odráža rôzne hodnoty meniacich sa buniek.
Excel umožňuje rýchlo porovnať rôzne scenáre, ktoré sa môžu týkať prakticky čohokoľvek. V našom príklade porovnávame rôzne scenáre výroby.
Príklad údajov a vzorcov
|
Materiál (EUR/ks) |
20 |
|
|
Počet hodín výroby (ks) |
1 |
|
|
Náklad na pracovníka (EUR/h) |
8 |
|
|
Predpokladaná cena (EUR/ks) |
50 |
|
|
Počet kusov |
1 000 |
|
Zisk závisí od ceny materiálu, času potrebného na výrobu jedného kusa, nákladov na pracovnú silu na výrobu jedného kusa a, samozrejme, od predpokladanej ceny a počtu kusov, ktoré sa vyrobia, pričom je zbožné prianie, že sa aj predajú,
Vzorec je
Zisk =Cena*PocetKusov - (Material* PocetKusov + PocetKusov*PocetHodinNaKus*NakladyNaPracu)
Aktivujte položku menu Údaje -> Analýza hypotéz -> Správa scenárov a následne postupne vytvárajte scenáre, v ktorých budete meniť jednotlivé parametre. Následne si môžete nechať zobraziť súhrn s porovnaním zadaných scenárov.
|
Súhrn scenára |
|
|
|
|
|
|
|
Aktuálne hodnoty |
Prvý scenár |
Druhý scenár |
|
Zmenené bunky: |
|
|
|
|
|
|
Materiál |
20 |
50 |
40 |
|
|
Počet_hodin_výroby |
1 |
1 |
2 |
|
|
Náklad_na_pracovníka |
8 |
10 |
8 |
|
|
Predpokladaná_cena |
50 |
80 |
60 |
|
Bunky s výsledkami: |
|
|
|
|
|
|
Zisk |
22 000 |
20 000 |
4 000 |
Stĺpec Aktuálne hodnoty zobrazuje hodnoty menených buniek v okamihu, keď bola vytvorená zostava scenára. Menené bunky každého scenára sú označené sivou farbou.
Hľadanie riešenia
Keď poznáte požadovaný výsledok vzorca, no nie ste si istí, akú vstupnú hodnotu vzorec vyžaduje na dosiahnutie daného výsledku, použite funkciu Hľadanie riešenia. Predpokladajme napríklad, že si potrebujete požičať určitú sumu peňazí. Viete, aká má byť suma, ako dlho chcete pôžičku splácať a aké mesačné splátky si môžete dovoliť. Môžete použiť funkciu Hľadanie riešenia, pomocou ktorej zistíte, aká má byť výška úrokovej sadzby, aby ste danú pôžičku splatili. Ukážeme príklad demonštrujúci správu scenárov z podnikového prostredia. HR špecialista, aby získal na konci roka odmenu, musí dosiahnuť priemerné percento udržania nových zamestnancov vo firme 75 %. Pozná údaje za 11 mesiacov a potrebuje vedieť, aké percento musí dosiahnuť v decembri, aby dostal odmenu. Alebo inak povedané, akú hodnotu minimálnu treba dosadiť do riadka december, aby bol priemer aspoň 75 %.
|
Január |
70% |
||
|
Február |
85% |
||
|
Marec |
60% |
||
|
Apríl |
90% |
||
|
Máj |
94% |
||
|
Jún |
39% |
||
|
Júl |
66% |
||
|
August |
72% |
||
|
September |
68% |
||
|
Október |
90% |
||
|
November |
85% |
||
|
December |
|
||
|
Priemer |
74% |
Stačí aktivovať položku menu Údaje -> Analýza hypotéz -> Hľadanie riešenia a do dialógu zadať, v ktorom poli akú hodnotu potrebujeme dosiahnuť a pomocou ktorej bunky to plánujeme ovplyvniť. Percentuálnu hodnotu v dialógu zadávame ako desatinné číslo, teda namiesto 75 % zadáte 0,75. V našom prípade získame výsledok 0,81 čiže 81 %. Takže za december je potrebné, aby 81 % získaných zamestnancov zostalo pracovať vo firme.
Tabuľky údajov
Ak máte vzorec, ktorý používa jednu alebo dve premenné, prípadne viacero vzorcov, pričom všetky používajú jednu spoločnú premennú, môžete funkciu Tabuľky údajov použiť na zobrazenie všetkých výsledkov na jednom mieste. Pomocou funkcie Tabuľky údajov môžete na prvý pohľad jednoduchšie preskúmať rozsah možností. Keďže sa zameriavate len na len jednu alebo dve premenné, výsledky sa dajú v podobe tabuľky ľahko interpretovať a zdieľať. Ak je v zošite zapnuté automatické prepočítavanie, údaje sa pomocou funkcie Tabuľky údajov okamžite prepočítajú a výsledkom budú nové údaje. Tabuľka údajov nedokáže prijať viac ako dve premenné. Ak chcete analyzovať viac ako dve premenné, môžete použiť scenáre. Aj napriek obmedzeniu na jednu alebo dve premenné môže tabuľka údajov používať ľubovoľný počet rôznych premenných hodnôt. Scenár môže mať maximálne 32 rôznych hodnôt, môžete však vytvoriť ľubovoľný počet scenárov.
Príklad ukazuje analýzu toho, ako budú vyzerať výnosy firmy pri rôznych kombináciách množstva predaných produktov a predajnej ceny.
Pripravíte si tabuľku so vzorcom:
|
Množstvo |
50 |
|
Predajná cena |
100 |
|
Zľava (%) |
5% |
|
Zisk |
4750 |
A takisto si pripravíte tabuľku, v ktorej chcete mať vypočítané hodnoty:
|
10 |
15 |
20 |
25 |
30 |
35 |
|
|
20 |
|
|||||
|
40 |
|
|||||
|
60 |
|
|||||
|
80 |
|
|||||
|
100 |
|
|||||
|
120 |
|
|||||
|
140 |
|
|||||
|
160 |
|
|||||
Výsledkom analýzy hypotéz typu tabuľka údajov bude vyplnená tabuľka:
|
10 |
15 |
20 |
25 |
30 |
35 |
|
|
20 |
190 |
285 |
380 |
475 |
570 |
665 |
|
40 |
380 |
570 |
760 |
950 |
1140 |
1330 |
|
60 |
570 |
855 |
1140 |
1425 |
1710 |
1995 |
|
80 |
760 |
1140 |
1520 |
1900 |
2280 |
2660 |
|
100 |
950 |
1425 |
1900 |
2375 |
2850 |
3325 |
|
120 |
1140 |
1710 |
2280 |
2850 |
3420 |
3990 |
|
140 |
1330 |
1995 |
2660 |
3325 |
3990 |
4655 |
|
160 |
1520 |
2280 |
3040 |
3800 |
4560 |
5320 |
Kontingenčná tabuľka a graf
Kontingenčná tabuľka (pivot table) má na rozdiel od klasickej tabuľky niekoľko špeciálnych vlastností. Umožňuje výmenu riadkov a stĺpcov, ale hlavne riadky a stĺpce môžu mať hierarchickú štruktúru. To poskytuje široké možnosti pri zoskupovaní údajov. Funkcia kontingenčnej tabuľky spočíva v tom, že zoberie údaje a vytvorí z nich súhrn tak, aby dávali zmysel. Netreba pritom zadávať žiadne vzorce.
Rozdiel medzi klasickou a kontingenčnou tabuľkou najlepšie pochopíte, ak ich porovnáme:
Klasická tabuľka
|
rok |
kvartal |
zisk |
|
2018 |
1 |
6300 |
|
2018 |
2 |
6900 |
|
2018 |
3 |
7100 |
|
2019 |
4 |
7150 |
|
2019 |
1 |
7300 |
|
2019 |
2 |
7700 |
|
2019 |
3 |
8000 |
|
2019 |
4 |
8100 |
Kontingenčná tabuľka
|
rok |
Q1 |
Q2 |
Q3 |
Q4 |
Spolu za rok |
|
2018 |
6300.0000 |
6900.0000 |
7100.0000 |
7150.0000 |
27450.0000 |
|
2019 |
7300.0000 |
7700.0000 |
8000.0000 |
8100.0000 |
31100.0000 |
V ďalšom príklade máme tabuľku, ktorá pre jednotlivé krajiny udáva jednak celkový počet obyvateľov, jednak počet obyvateľov žijúcich v mestách. Príklad údajov:
|
Krajina |
Kontinent |
Populácia (v tisícoch) |
Populácia v mestách (v tisícoch) |
|
Zimbabwe |
Afrika |
16529,9043 |
5328,745167 |
|
Zambia |
Afrika |
17094,13086 |
7346,373975 |
|
Uganda |
Afrika |
42862,95703 |
9942,491147 |
|
Tunisko |
Afrika |
11532,12695 |
7915,882386 |
Ak kontingenčné tabuľky dobre nepoznáte alebo neviete, ako začať, skvelá voľba je odporúčaná kontingenčná tabuľka. Pri použití tejto funkcie Excel priradí údaje k najvhodnejším oblastiam v kontingenčnej tabuľke a určí zmysluplné rozloženie. Získate tak východiskový bod na ďalšie experimentovanie. Po vytvorení odporúčanej kontingenčnej tabuľky môžete preskúmať rôzne orientácie a zmeniť usporiadanie polí, aby ste dosiahli konkrétne výsledky.
Návrhový formulár kontingenčnej tabuľky je umiestnený priamo na ploche hárka programu Excel v pravej časti. Vaša činnosť pri návrhu tabuľky je zredukovaná na presúvanie ikoniek s názvami atribútov z hornej časti panela do jednotlivých polí návrhového formulára. Tieto polia sú označené ako Hodnoty, Riadky, Stĺpce a Filtre. Naša úloha je povedzme zistiť pre jednotlivé kontinenty celkový počet obyvateľov a aj to, koľko obyvateľov z toho žije v mestách. Takže do poľa hodnoty presunieme atribúty Populácia a Ludia-v-mestách a do stĺpca Riadky atribút Kontinent. Získate prehľadnú tabuľku s agregovanými údajmi pre jednotlivé kontinenty. Polia kontingenčnej tabuľky umiestnené v oblasti Hodnoty sa predvolene zobrazia ako SÚČET. Ak Excel údaje interpretuje ako text, zobrazia sa ako POČET. Keď presuniete pole do oblasti Hodnoty, v dolnej časti zoznamu polí kontingenčnej tabuľky sa zobrazí položka Súčet z... (Ak Excel nemá dostatok miesta, názov položky sa niekedy nemusí zobraziť celý.)
|
Označenia riadkov |
Súčet Populácia (v tisícoch) |
Súčet Populácia v mestách (v tisícoch) |
|
Afrika |
1061991,023 |
446744,0028 |
|
Ázia |
4522757,774 |
2250806,231 |
|
Európa |
597596,2602 |
443724,0873 |
|
Severná Amerika |
565082,0344 |
445083,3802 |
|
Oceánia |
39718,89455 |
27070,097 |
|
Južná Amerika |
381079,3098 |
320150,357 |
|
Celkový súčet |
7168225,297 |
3933578,155 |
Kontingenčné tabuľky začnú byť zaujímavé, keď pridáte podmienky, pomocou ktorých rozdelíte pole hodnoty. Ak atribút Kontinent presuniete do poľa Filtre, môžete údaje filtrovať pre jednotlivé kontinenty.
|
Kontinent |
Európa |
|
Súčet Populácia (v tisícoch) |
Súčet Populácia v mestách (v tisícoch) |
|
597596,2602 |
443724,0873 |
Ak do poľa Riadky presuniete viac atribútov, ktoré tvoria prirodzenú hierarchiu, v našom prípade je to Kontinent a Krajina, môžete potom túto hierarchickú štruktúru rozbaľovať. Môžete si napríklad nechať zobraziť údaje pre jednotlivé kontinenty, ale Európu môžete rozbaliť tak, že sa zobrazia údaje pre jednotlivé krajiny. Jednotlivé hierarchie rozbaľujete pomocou ikonky + vľavo od názvu atribútu.
|
Označenia riadkov |
Súčet Populácia (v tisícoch) |
Súčet Populácia v mestách (v tisícoch) |
|
Africa |
1061991,023 |
446744,0028 |
|
Asia |
4522757,774 |
2250806,231 |
|
Europe |
597596,2602 |
443724,0873 |
|
Albania |
2930,187012 |
1740,032936 |
|
Austria |
8735,453125 |
5074,774293 |
|
Belarus |
9468,337891 |
7397,991382 |
|
Belgium |
11429,33594 |
11196,29161 |
|
Bosnia and Herzegovina |
3507,01709 |
1679,019483 |
Z tejto množiny údajov by bolo zaujímavé vedieť percento ľudí, ktorí na jednotlivých kontinentoch a v jednotlivých krajinách žijú v mestách. Do kontingenčnej tabuľky preto vložíme Vypočítavané pole.
V ňom (klikaním na názvy atribútov) vytvoríme jednoduchý vzorec na výpočet percent v desatinnom vyjadrení, v našom prípade vzorec bude:
= 'Populácia v mestách (v tisícoch)'/ 'Populácia (v tisícoch)'
Zostáva len naformátovať pole, aby sa hodnoty zobrazovali v percentách, a skryť atribúty celkový počet populácie a počet ľudí žijúcich v mestách. Aj v tejto tabuľke môžete rozbaľovať jednotlivé úrovne hierarchie.
|
Označenia riadkov |
Súčet z Percento v mestách |
|
Africa |
42,07% |
|
Asia |
49,77% |
|
Europe |
74,25% |
|
North America |
78,76% |
|
Oceania |
68,15% |
|
Australia |
85,90% |
|
Fiji |
55,74% |
|
Kiribati |
53,26% |
|
Marshall Islands |
76,63% |
|
Nauru |
100,00% |
|
New Zealand |
86,47% |
|
Palau |
79,36% |
|
Papua New Guinea |
13,10% |
|
Samoa |
18,45% |
|
Solomon Islands |
23,29% |
|
Tonga |
23,17% |
|
Tuvalu |
61,53% |
|
Vanuatu |
25,16% |
|
South America |
84,01% |
|
Celkový súčet |
54,88% |
Jednotlivé hierarchie pri zobrazení kontingenčnej tabuľky obsahujú agregované údaje zo zdrojových tabuliek. V našom príklade sme mali údaje pre jednotlivé krajiny. Tieto údaje sa následne dajú agregovať podľa kontinentov či geopolitických zoskupení, ako je napríklad Európska únia.
Kontingenčnú tabuľku môžete kedykoľvek zobraziť aj vo forme názorného grafu. Zatiaľ čo štandardné grafy sú prepojené priamo s bunkami hárka, kontingenčné grafy sú založené na údajoch priradenej kontingenčnej tabuľky. Podobne ako tabuľky aj kontingenčné grafy sú interaktívne. Pomocou panela filtrov môžete údaje zoraďovať a filtrovať. Zmeny vykonané v rozložení a údajoch priradenej kontingenčnej tabuľky sa okamžite premietnu do rozloženia a údajov v kontingenčnom grafe a naopak.
Kontingenčná tabuľka je názorný a hlavne interaktívny spôsob rýchleho zhrnutia veľkého množstva údajov. Umožňuje agregovať veľké množstvo údajov rôznymi spôsobmi, poskytuje možnosť výpočtu medzisúčtov či vytváranie súhrnov údajov podľa kategórií a podkategórií a vytváranie vlastných výpočtov a vzorcov. Môžete rozbaliť alebo zbaliť jednotlivé úrovne hierarchií s cieľom zamerať sa na určitý fókus výsledkov. Kedykoľvek môžete prejsť zo súhrnných údajov na detaily a naopak.
Ľuboslav Lacko
Zobrazit Galériu
