Využití nástroje Citlivostní analýzy – Tabulka dat v Microsoft Excel

Nástroj Tabulka dat může usnadnit analýzu výsledků vzorců a funkcí při změně jedné nebo dvou proměnných. Proto v souvislosti s tímto příkazem se také hovoří o takzvané citlivostní analýze, a to jedné nebo dvou proměnných.

Prakticky se dá říci, že se jedná o jeden příkaz, ale podle zadaných dat si zvolíte, zda potřebujete vyhledávat jednu nebo dvě proměnné. Při zadání jedné proměnné můžete současně zpracovávat více vzorců pro jednu skupinu vstupních dat. Při řešení dvou proměnných lze zpracovat pouze jeden vzorec, ale pro dvě skupiny dat.

Pro řešení se tedy předpokládá s existencí vzorce a množiny nebo dvou množin (skupin) vstupních dat. Přičemž vstupní data nelze zadat přímo, ale jsou načítána do nástroje Tabulka pomocí takzvané Vstupní buňky. Výsledek vám dá hodnoty, ale pokud se podíváte na řádek vzorců, uvidíte, že ve skutečnosti vzniklo pole funkcí Tabelovat.

Využití nástroje Citlivostní analýzy – Tabulka dat v Microsoft Excel, Jubela s.r.o.

Řešení s jednou proměnnou

Představte si, že si chcete půjčit 100 000 Kč. Víte, že úrok je 14,5 % a zajímá vás, jak doba splatnosti ovlivní nejenom vaši měsíční splátku, ale především částku, kterou při pravidelném splácení přeplatíte.

Pro výpočet výše měsíční splátky se běžně používá funkce Platba. Tato funkce umí při zadání vypůjčené částky, úroku a doby splatnosti vypočítat právě velikost měsíční splátky. Zkuste si vše nejprve vypočítat například pro 12 měsíců, tedy 12 splátek.

Využití nástroje Citlivostní analýzy – Tabulka dat v Microsoft Excel, Jubela s.r.o.

Pokud chcete zjistit také částku, kterou přeplatíte, je třeba přidat vzorec, který vynásobí počet splátek a výši splátky. Poté od této hodnoty odečte skutečnou výši vaší půjčky. Tedy =B2*B5-B1.

Využití nástroje Citlivostní analýzy – Tabulka dat v Microsoft Excel, Jubela s.r.o.

Při době splácení 12 měsíců tedy přeplatíte přibližně 8 tisíc. Jak se ale daná částka bude měnit při změně doby splatnosti? Připravte si nyní oblast pro výpočet podobně jako na následujícím obrázku.

Využití nástroje Citlivostní analýzy – Tabulka dat v Microsoft Excel, Jubela s.r.o.

Do sloupce A (například buňky A9 až A15) si napište různé doby splatnosti a potom označte pro výpočet celou oblast buněk (například A8 až B15) k řešení, která zahrnuje proměnné, vzorce i takzvanou výpočetní oblast. Poté v nabídce Data klepněte na příkaz Tabulka. Zobrazí se dialogové okno Tabulka, ve kterém je třeba doplnit vstupní buňku.

Využití nástroje Citlivostní analýzy – Tabulka dat v Microsoft Excel, Jubela s.r.o.

Všimněte si, že dialogové okno Tabulka má na doplnění dvě políčka, a to Vstupní buňku řádkuVstupní buňku sloupce. Hodnota, která se zde mění, má být doba splatnosti. Přitom různé doby splatnosti jsou nyní zapsány ve sloupci. Proto se bude doplňovat v daném případě Vstupní buňka sloupce a ne řádku. Nyní musí Vstupní buňka řádku zůstat prázdná.

Do vstupní buňky sloupce zadejte odkaz na buňku s dobou splatnosti, tedy na buňku B2. Poté jen klepněte na tlačítko OK.

Využití nástroje Citlivostní analýzy – Tabulka dat v Microsoft Excel, Jubela s.r.o.

A výpočetní oblast se vám hned zaplní výslednými hodnotami. Ve skutečnosti je pro buňky B9B15 doplněno daným nástrojem výpočtové pole ve tvaru {=TABELOVAT(;B2)}.

Díky tomu můžete snadno porovnávat částku, kterou při půjčce zaplatíte navíc a jednotlivé doby splatnosti. Je logické, že s delší dobou splatnosti i tato částka narůstá.

Řešení se dvěma proměnnými

Představte si, že máte spočítat náhradu za vyčištění studny, například jako událost v rámci pojištění. Obvykle znáte průměr studny a její hloubku. Náhrada se pak odvíjí od objemu, to znamená, že je třeba vypočítat objem pro různé velikosti studny. Zároveň víte, že náhrada za 1 m3 je například 400 Kč.

Pro celý výpočet je třeba si nejprve vytvořit přehlednou tabulku se zadáním jednotlivých údajů a především se vzorcem, který bude náhradu počítat. Objem studny se vypočítá ze vzorce =2*π*r2*v. A poté stačí jen objem vynásobit finanční částkou a vzorec pro výpočet náhrady je vytvořen.

Využití nástroje Citlivostní analýzy – Tabulka dat v Microsoft Excel, Jubela s.r.o.

Pro danou situaci tedy vznikne v tabulce MS Excelu vzorec ve tvaru =2*PI()*(D3/2)^2*D2*D4. Důležité pak je do řádku vedle vzorce zapsat jednu proměnnou hodnotu, například průměr studny a do sloupce pod vzorcem uvést druhou proměnnou hodnotu, například hloubku studny.

Nyní již stačí označit celou oblast k řešení, tedy buňky B7I16. Pak již v nabídce Data klepněte na příkaz Tabulka. Zobrazí se dialogové okno Tabulka, ve kterém je třeba doplnit vstupní buňky.

Využití nástroje Citlivostní analýzy – Tabulka dat v Microsoft Excel, Jubela s.r.o.

Jako Vstupní buňka řádku musí být odkaz na buňku se zadaným průměrem studny, tedy D3. Vstupní buňka sloupce zase musí být odkaz na hloubku studny, tedy na buňku D2.

Potom již můžete klepnout na tlačítko OK a celý výpočet proběhne. Tím se vyplní celá oblast řešení výslednými hodnotami.

Využití nástroje Citlivostní analýzy – Tabulka dat v Microsoft Excel, Jubela s.r.o.

Nyní tedy dostanete tabulku, ve které můžete snadno zjistit náhradu za čištění studny s různými rozměry.

Tento web používá k poskytování služeb, personalizaci reklam a analýze návštěvnosti soubory cookie. Používáním tohoto webu s tím souhlasíte.
Další informace