Ako vykonávať analýzy What-If s tabuľkami údajov v Exceli 2016

Vykonaním analýz typu „čo keby“ s tabuľkami údajov Excelu 2016 zmeníte údaje vo vstupných bunkách a budete sledovať, aký vplyv má zmena údajov na výsledky vzorca. S tabuľkou údajov môžete súčasne experimentovať s mnohými rôznymi vstupnými bunkami a pritom experimentovať s mnohými rôznymi scenármi.

Použitie tabuľky s jedným vstupom na analýzu

V tabuľke s jedným vstupom zistíte, aké by boli rôzne výsledky vzorca, keby ste zmenili jednu vstupnú bunku vo vzorci. Tu je vstupnou bunkou úroková sadzba pôžičky. Účelom tejto tabuľky s údajmi je zistiť, ako sa líšia mesačné splátky 250 000 USD na 30-ročnej hypotéke vzhľadom na rôzne úrokové sadzby. Úroková sadzba v bunke B4 je vstupná bunka.

Jednovstupová dátová tabuľka.

Ak chcete vytvoriť tabuľku s jedným vstupom, postupujte podľa týchto krokov:

V pracovnom hárku zadajte hodnoty, ktoré chcete nahradiť hodnotu vo vstupnej bunke.

Aby vstupná tabuľka fungovala, musíte zadať náhradné hodnoty na správne miesto:

• V stĺpci: Zadajte hodnoty do stĺpca počnúc o jednu bunku nižšie a jednu bunku naľavo od bunky, v ktorej sa nachádza vzorec. V zobrazenom príklade je vzorec v bunke E4 a hodnoty sú v rozsahu buniek D5:D15.

• V riadku: Zadajte hodnoty do riadka, ktorý začína o jednu bunku vyššie a jednu bunku napravo od bunky, v ktorej je vzorec.

Vyberte blok buniek so vzorcom a nahraďte hodnoty.

Vyberte obdĺžnik buniek, ktorý zahŕňa bunku vzorca, bunku vedľa nej, všetky náhradné hodnoty a prázdne bunky, v ktorých sa čoskoro objavia nové výpočty.

• V stĺpci: Vyberte bunku vzorca, bunku naľavo od nej, všetky bunky s náhradnou hodnotou a bunky pod bunkou vzorca.

• V riadku: Vyberte bunku vzorca, bunku nad ňou, náhradné hodnoty v bunkách priamo napravo a teraz prázdne bunky, v ktorých sa objavia nové výpočty.

Na karte Údaje kliknite na tlačidlo Analýza čohokoľvek a v rozbaľovacom zozname vyberte možnosť Tabuľka údajov.

Zobrazí sa dialógové okno Tabuľka údajov.

Do textového poľa Bunka vstupu riadka alebo Bunka vstupu stĺpca zadajte adresu bunky, v ktorej sa nachádza vstupná hodnota.

Ak chcete zadať adresu tejto bunky, prejdite mimo dialógové okno Tabuľka údajov a kliknite na bunku. Vstupná hodnota je hodnota, s ktorou experimentujete vo svojej analýze. V prípade zobrazeného pracovného hárka sa vstupná hodnota nachádza v bunke B4, bunke, ktorá obsahuje úrokovú sadzbu.

Ak sa nové výpočty objavia v riadkoch, zadajte adresu vstupnej bunky do textového poľa Vstupná bunka riadka; ak sa výpočty zobrazujú v stĺpcoch, zadajte adresu vstupnej bunky do textového poľa Vstupná bunka stĺpca.

Kliknite na tlačidlo OK.

Excel vykoná výpočty a vyplní tabuľku.

Na vygenerovanie tabuľky s jedným vstupom Excel vytvorí vzorec poľa pomocou funkcie TABLE. Ak zmeníte odkazy na bunky v prvom riadku alebo do prvého stĺpca vložíte iné hodnoty, Excel automaticky aktualizuje tabuľku s jedným vstupom.

Použitie tabuľky s dvomi vstupmi na analýzu

V tabuľke s dvoma vstupmi môžete experimentovať s dvoma vstupnými bunkami namiesto jednej. Vráťme sa k príkladu splácania úveru a môžete vypočítať nielen to, ako sa menia splátky úveru so zmenou úrokových sadzieb, ale aj to, ako sa menia platby, ak sa zmení životnosť úveru. Tento obrázok ukazuje tabuľku s dvomi vstupmi na skúmanie mesačných splátok úveru pri rôznych úrokových sadzbách a dvoch rôznych podmienkach úveru, 15 rokov (180 mesiacov) a 30 rokov (360 mesiacov).

Tabuľka s dvomi vstupmi.

Ak chcete vytvoriť tabuľku s dvomi vstupmi, postupujte podľa týchto krokov:

Zadajte jednu množinu náhradných hodnôt pod vzorec v rovnakom stĺpci ako vzorec.

Tu sa zadávajú rôzne úrokové sadzby v oblasti buniek D5:D15.

Zadajte druhú množinu náhradných hodnôt v riadku napravo od vzorca.

Tu sú zadané 180 a 360. Tieto čísla predstavujú počet mesiacov životnosti úveru.

Vyberte vzorec a všetky náhradné hodnoty.

Urobte to správne a vyberiete tri stĺpce vrátane vzorca, náhradných hodnôt pod ním a dvoch stĺpcov napravo od vzorca. Vyberiete veľký blok buniek (v tomto príklade rozsah D4:F15).

Na karte Údaje kliknite na tlačidlo Analýza čohokoľvek a v rozbaľovacom zozname vyberte možnosť Tabuľka údajov.

Zobrazí sa dialógové okno Tabuľka údajov.

Do textového poľa Bunka na zadanie riadka zadajte adresu bunky, na ktorú sa odkazuje v pôvodnom vzorci, kam možno vložiť náhradné hodnoty napravo od vzorca.

Zadajte adresu bunky tak, že prejdete mimo dialógového okna a vyberiete bunku. Tu sú napríklad riadky napravo od vzorca pre dĺžku náhradných hodnôt úveru. Vyberte preto bunku B5, bunku, na ktorú sa odkazuje v pôvodnom vzorci, kde je uvedená dĺžka pôžičky.

Do textového poľa Bunka na zadanie stĺpca zadajte adresu bunky, na ktorú sa odkazuje v pôvodnom vzorci, kde sú náhradné hodnoty pod vzorcom.

Náhradné hodnoty pod bunkou vzorca sú úrokové sadzby. Preto vyberte bunku B4, bunku, na ktorú sa odkazuje v pôvodnom vzorci, kde sa zadáva úroková sadzba.

Kliknite na tlačidlo OK.

Excel vykoná výpočty a vyplní tabuľku.