Jak provádět analýzy What-If s datovými tabulkami v Excelu 2016

Prováděním analýzy typu what-if s datovými tabulkami aplikace Excel 2016 můžete změnit data ve vstupních buňkách a sledovat, jaký vliv má změna dat na výsledky vzorce. S datovou tabulkou můžete současně experimentovat s mnoha různými vstupními buňkami a přitom experimentovat s mnoha různými scénáři.

Použití tabulky s jedním vstupem pro analýzu

V tabulce s jedním vstupem zjistíte, jaké by byly různé výsledky vzorce, kdybyste ve vzorci změnili jednu vstupní buňku . Zde je vstupní buňkou úroková sazba půjčky. Účelem této datové tabulky je zjistit, jak se liší měsíční splátky u hypotéky na 250 000 USD na 30 let s ohledem na různé úrokové sazby. Úroková sazba v buňce B4 je vstupní buňkou.

Jednovstupová datová tabulka.

Chcete-li vytvořit tabulku s jedním vstupem, postupujte takto:

Na listu zadejte hodnoty, kterými chcete nahradit hodnotu ve vstupní buňce.

Aby vstupní tabulka fungovala, musíte náhradní hodnoty zadat na správné místo:

• Ve sloupci: Zadejte hodnoty do sloupce počínaje o buňku níže a o buňku vlevo od buňky, kde se nachází vzorec. V zobrazeném příkladu je vzorec v buňce E4 a hodnoty jsou v oblasti buněk D5:D15.

• V řádku: Zadejte hodnoty do řádku začínajícího o jednu buňku výše a jednu buňku napravo od buňky, kde je vzorec.

Vyberte blok buněk se vzorcem a nahraďte hodnoty.

Vyberte obdélník buněk, který zahrnuje buňku vzorce, buňku vedle ní, všechny náhradní hodnoty a prázdné buňky, kde se brzy objeví nové výpočty.

• Ve sloupci: Vyberte buňku vzorce, buňku nalevo, všechny buňky s náhradní hodnotou a buňky pod buňkou vzorce.

• V řádku: Vyberte buňku vzorce, buňku nad ní, náhradní hodnoty v buňkách přímo vpravo a nyní prázdné buňky, kde se objeví nové výpočty.

Na kartě Data klikněte na tlačítko What-If Analysis a v rozevíracím seznamu vyberte Data Table.

Zobrazí se dialogové okno Tabulka dat.

Do textového pole Buňka vstupu řádku nebo Buňka vstupu sloupce zadejte adresu buňky, kde se nachází vstupní hodnota.

Chcete-li zadat adresu této buňky, přejděte mimo dialogové okno Tabulka dat a klikněte na buňku. Vstupní hodnota je hodnota, se kterou experimentujete ve své analýze. V případě zobrazeného listu je vstupní hodnota umístěna v buňce B4, buňce, která obsahuje úrokovou sazbu.

Pokud se nové výpočty objeví v řádcích, zadejte adresu vstupní buňky do textového pole Vstupní buňka řádku; pokud se výpočty zobrazují ve sloupcích, zadejte adresu vstupní buňky do textového pole Vstupní buňka sloupce.

Klepněte na tlačítko OK.

Excel provede výpočty a vyplní tabulku.

Chcete-li vygenerovat tabulku s jedním vstupem, Excel vytvoří maticový vzorec pomocí funkce TABLE. Pokud změníte odkazy na buňky v prvním řádku nebo vložíte jiné hodnoty do prvního sloupce, Excel automaticky aktualizuje jednovstupovou tabulku.

Použití dvouvstupové tabulky pro analýzu

V tabulce se dvěma vstupy můžete experimentovat spíše se dvěma vstupními buňkami než s jednou. Vrátíme-li se zpět k příkladu splátky úvěru, můžete vypočítat nejen to, jak se změní splátky úvěru se změnou úrokových sazeb, ale také to, jak se změní platby, pokud se změní doba trvání úvěru. Tento obrázek ukazuje dvouvstupovou tabulku pro zkoumání měsíčních splátek úvěru s různými úrokovými sazbami a dvěma různými podmínkami pro úvěr, 15 let (180 měsíců) a 30 let (360 měsíců).

Dvouvstupová datová tabulka.

Chcete-li vytvořit dvouvstupovou datovou tabulku, postupujte takto:

Zadejte jednu sadu náhradních hodnot pod vzorec ve stejném sloupci jako vzorec.

Zde se zadávají různé úrokové sazby v oblasti buněk D5:D15.

Zadejte druhou sadu náhradních hodnot do řádku napravo od vzorce.

Zde se zadávají 180 a 360. Tato čísla představují počet měsíců životnosti úvěru.

Vyberte vzorec a všechny náhradní hodnoty.

Udělejte to správně a vyberete tři sloupce včetně vzorce, náhradních hodnot pod ním a dvou sloupců napravo od vzorce. Vyberete velký blok buněk (v tomto příkladu rozsah D4:F15).

Na kartě Data klikněte na tlačítko What-If Analysis a v rozevíracím seznamu vyberte Data Table.

Zobrazí se dialogové okno Tabulka dat.

Do textového pole Buňka pro vstup řádku zadejte adresu buňky, na kterou se odkazuje v původním vzorci, kam lze vložit náhradní hodnoty napravo od vzorce.

Zadejte adresu buňky tak, že přejdete mimo dialogové okno a vyberete buňku. Zde jsou například řádky napravo od vzorce pro délku náhradních hodnot úvěru. Vyberte proto buňku B5, buňku odkazovanou v původním vzorci, kde je uvedena délka půjčky.

Do textového pole Buňka pro zadání sloupce zadejte adresu buňky, na kterou odkazuje původní vzorec, kde jsou náhradní hodnoty pod vzorcem.

Náhradní hodnoty pod buňkou vzorce jsou úrokové sazby. Vyberte proto buňku B4, buňku uvedenou v původním vzorci, kam se zadává úroková sazba.

Klepněte na tlačítko OK.

Excel provede výpočty a vyplní tabulku.