Business intelligence v Exceli: Analýza hypotéz a kontingenčné tabuľky

0

 

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
BI

Mohlo by vás zaujímať

Mohlo by vás zaujímať