Image
12.1.2020 0 Comments

Business intelligence v Exceli – analýza hypotéz (What-if analýza)

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 použ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áreHľadanie riešenia a Tabuľky údajov

Video názorne ukazuje všetky tri funkcie analýzy hypotéz (what-if) v Exceli: správu scenárov, hľadanie riešení a tabuľku ú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 dvomi premennými, dokáže však prijať mnoho rôznych hodnôt pre tieto premenné. Scenár môže mať viacero 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 vezme výsledok a stanovia sa možné vstupné hodnoty, ktoré 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.

Ak má niekoľko používateľov špecifické informácie uložené v samostatných zošitoch, ktoré chcete v scenároch použiť, môžete tieto zošity zhromaždiť a ich scenáre zlúčiť. Po vytvorení a zhromaždení všetkých požadovaných scenárov môžete vytvoriť súhrnnú zostavu scenára, ktorá bude zahŕňať informácie z týchto scenárov. Zostava scenára zobrazuje všetky informácie scenára v jednej tabuľke nachádzajúcej sa v novom hárku.

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 (Kč/ks)
20
 
Počet hodin výroby (ks)
1
 
Náklad na pracovníka (Kč/h)
150
 
Předpokládaná cena (Kč/Ks)
200
 
Počet kusů
160
 
 
 
 
Výnosnost
4800

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. Vidíme že v tomto prípade by sme pri druhom scenári mali – 800 Kč stratu

Súhrn scenára
 
 
 
 
 
Aktuálne hodnoty:
Prvy scenar
Druhy scenar
Zmenené bunky:
 
 
 
 
Materiál__Kč_ks
50
20
50
 
Počet_hodin_výroby__ks
0,4
1
2
 
Náklad_na_pracovníka__Kč_h
180
150
180
 
Předpokládaná_cena__Kč_Ks
400
200
400
Bunky s výsledkami:
 
 
 
 
Výnosnost
111200
4800
-800
Poznámka: Stĺpec Aktuálne hodnoty predstavuje hodnoty menených
 
buniek v okamihu, keď bola vytvorená zostava scenára.
 
 
 
 
 

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 je z podnikového prostredia. HR špecialista aby získal na konci roku 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 je potrebné dosadiť do riadku december, aby bol priemer aspoň 75% 

 
Leden
70%
 
 
Únor
85%
 
 
Březen
60%
 
 
Duben
90%
 
 
Květen
94%
 
 
Červen
39%
 
 
Červenec
66%
 
 
Srpen
72%
 
 
Září
68%
 
 
Říjen
90%
 
 
Listopad
85%
 
 
Prosinec
 
 
 
 
 
 
 
Průměr
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, čiže namiesto 75% zadáte 0,75.

V našom prípade získame výsledok 0.81, čiže 81 %. Takže v decembri 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žství
50
Prodejní cena
100
Sleva (%)
5%
 
 
 
 
Výnos
4750

A taktiež 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

Vo voľnom pokračovaní ukážeme možnosti kontingenčnej tabušky a kontingenčného grafu v Exceli

 

Zobrazit Galériu

Nechajte si posielať prehľad najdôležitejších správ emailom

Ako na to 1

Naučte sa vyvíjať aplikácie pre Android za 14 dní – dotyky a gestá

27.03.2020 00:10

Implicitné ovládanie platformy Android je pomocou dotykov a giest na dotykovej obrazovke. Moderné zariadenia umožňujú snímať dotyky viacerých prstov, vrátane dynamiky a tlaku. Multi dotykový displej ...

Ako na to

Naučte sa vyvíjať aplikácie pre Android za 14 dní – intenty, alebo kto to urobí?

26.03.2020 00:00

V tejto časti seriálu riešime situáciu, keď vaša aplikácia potrebuje zobraziť webovú stránku, mapu, či prehrať multimediálny súbor. Nemá zmysel aby to robila vlastnými prostriedkami keď v smartfóne je ...

Ako na to

Naučte sa vyvíjať aplikácie pre Android za 14 dní – spustenie na reálnom zariadení

25.03.2020 00:10

Sme približne v polčase prvej základnej série o vývoji aplikácií pre Android a už máme z minulej časti Aplikácia s dvomi aktivitami  vytvorenú reálnu aj keď veľmi jednoduchú aplikáciu, odladenú na emu ...

ACER_032020

Videá