Jak vytvořit datovou tabulku pro analýzu „co kdyby“ v Excelu

Microsoft Excel nabízí několik funkcí pro analýzu scénářů „co kdyby“. Jedním z takových nástrojů je datasheet. S ním můžete experimentovat s různými hodnotami, abyste viděli různé výsledky.

Je to užitečný způsob, jak zobrazit věci, jako jsou půjčky, investice, přirážky k produktům a tak dále, s různými proměnnými. Můžete například zadat několik různých úrokových sazeb pro půjčku, abyste viděli, jaká bude vaše platba u každé sazby, to vše na jednom místě.

Vytvořte datovou tabulku s jednou proměnnou

Datové tabulky v Excelu mohou obsahovat jeden nebo dva různé vstupy. Začněme tedy datovou tabulkou s jednou proměnnou. Budoucí hodnotu naší investice jsme vypočítali pomocí funkce FV a konstant pro roční úrokovou sazbu, počet plateb a výši platby.

Zde uvidíme, jaká bude budoucí hodnota našich investic při různých úrokových sazbách.

Své hodnoty můžete zadat do sloupce nebo řádku, ale umístění ovlivňuje, kam byste měli vzorec umístit. Pokud jsou vaše hodnoty ve sloupci, umístěte vzorec o buňku výše a vpravo. Pokud jsou vaše hodnoty v řadě, umístěte vzorec o jednu buňku dolů a doleva.

V našem příkladu používáme sloupcově orientovanou datovou tabulku. Jak můžete vidět na níže uvedeném snímku obrazovky, přesunuli jsme náš vzorec do sloupce D a naše úrokové sazby jsme uvedli ve sloupci C.

Vyberte buňky obsahující vzorec, hodnoty a očekávané výsledky. U nás je to od C2 po D6.

Klikněte na kartu Data, klikněte na šipku rozevíracího seznamu What-If Analysis a vyberte Data Table.

V okně Tabulka dat, které se otevře, zadejte odkaz na buňku pro proměnnou, kterou chcete upravit, a podle svého nastavení. V našem příkladu zadáváme odkaz na buňku B3 pro měnící se úrokovou sazbu do pole Buňka pro zadání sloupce. Opět používáme datovou tabulku založenou na sloupcích.

Klikněte na OK a uvidíte výsledky. Nyní máme budoucí hodnotu naší investice s každou zadanou úrokovou sazbou.

Vytvořte datovou tabulku se dvěma proměnnými

Pro podrobnější analýzu můžete do tabulky dat přidat dvě proměnné. Na stejném příkladu chceme nejen získat budoucí hodnotu našich investic s různými úrokovými sazbami, ale chceme také vidět různé budoucí hodnoty změnou počtu plateb.

Protože používáte dvě proměnné, budete používat datovou tabulku, která je orientovaná jak na sloupec, tak na řádek. Umístěte vzorec přímo nad sloupec s hodnotou a nalevo od řádku s hodnotou, jak je znázorněno níže.

Nyní vyberte všechny buňky obsahující vzorec, hodnoty a očekávané výsledky. U nás je to od C2 do F6.

Klepněte na kartu Data a vyberte volbu What-If Analysis > Data Table. Když se zobrazí okno, zadejte odkazy na vstupní buňky řádku a sloupce.

Pomocí našeho příkladu zadáme B3 pro buňku pro zadání sloupce, která je pro naši úrokovou sazbu, a B4 pro buňku pro zadání řádku, která je pro naši částku platby.

Klikněte na OK a měli byste vidět všechny výstupy vašich proměnných. Zde máme budoucí hodnotu naší investice pomocí různých úrokových sazeb a různého počtu plateb, vše zobrazeno v pěkné a přehledné tabulce.

Až budete příště chtít analyzovat data na listu s různými čísly nebo množstvími, zvažte použití datového listu. Nejen, že za vás provedeme výpočty, ale výsledek vám poskytne strukturovanou tabulku pro snadnou analýzu.

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna.