6 Další datové nástroje Příklad 3 Excel 2010
OPF v Karviné, Slezská univerzita v Opavě Kateřina Slaninová
TÉMA: Citlivostní analýza
Varianty, rozpisů půjčky na zakoupení nábytku, které sekretářka společnosti „Naše zahrada“ porovnávala
s tříletým pronájmem v předchozím příkladu, nebyly dostatečné. Dostala tedy za úkol je podrobněji rozpracovat.
Pro podrobnější analýzu, někdy taky nazývanou citlivostní analýza, použila nástroj Tabulka dat.
Zadání:
Otevřete soubor CitlivostniAnalyza.xlsx.
1. Na listu Jednokriteriální zjistěte pomocí nástrojů Citlivostní analýzy (analýzy hypotéz), jakým
způsobem se změní hodnoty Měsíční splátky, Celkových nákladů půjčky a Úspor, změní-li se
Počet let splácení půjčky na nábytek v rozmezí od 3 do 10 let. Použijte nástroj Tabulka dat.
2. Na listu Jednokriteriální (2) zjistěte pomocí nástrojů Citlivostní analýzy (analýzy hypotéz),
jakým způsobem se změní hodnoty Měsíční splátky, Celkových nákladů půjčky a Úspor,
změní-li se výše Úrokové sazby postupně z hodnoty 7,0% na 9,5%, vždy o 0,5%. Použijte
nástroj Tabulka dat.
3. Na listu Vícekriteriální zjistěte pomocí nástrojů Citlivostní analýzy (analýzy hypotéz), jakým
způsobem se změní hodnoty Měsíční splátky, změní-li se Počet let splácení půjčky na nábytek
v rozmezí od 3 do 10 let pro hodnoty Úrokové sazby od 7,0% do 9,5% (s krokem 0,5%).
Použijte nástroj Tabulka dat.
4. Sešit uložte a uzavřete.
Řešení
6 Další datové nástroje Příklad 3 Excel 2010
OPF v Karviné, Slezská univerzita v Opavě Kateřina Slaninová
Řešení:
Karta Soubor/Otevřít (nebo tlačítko Otevřít na panelu nástrojů Rychlý přístup, popř. klávesová
zkratka Ctrl+O). V dialogovém okně Otevřít nalézt soubor dle zadání, tlačítko Otevřít. Pozn. Sešit
lze otevřít také přímo v systému Windows (dvakrát kliknout na ikonu souboru nebo kontextová
nabídka ikony/Otevřít).
1. Pozn. Nástroje citlivostní analýzy (analýzy hypotéz) se používají v situacích, kdy potřebujeme
zjistit, jak se změní výsledek, změní-li se jeho vstupy. Umožňují tak vypočítat více verzí
v jedné operaci a porovnávat tak výsledky různých změn v listu najednou. Současně můžeme
sledovat změny více výpočtů (vzorců) najednou. Zobrazit list dle zadání, připravit v tabulce
údaje pro výpočet (připravená tabulka musí mít určitou strukturu, v jednotlivých částech
tabulky musí být připraveny údaje pro výpočet). Do sloupce Počet let (buňky D5:D12) vložit
hodnoty 3 – 10 (vstupní hodnoty pro vzorec). Dále do prvního řádku tabulky zkopírovat
ze sloupce B příslušné vzorce pro výpočet hodnot dle zadání (zkopírovat pouze vzorce klávesy
Ctrl + C, karta Domů/skupina Schránka/příkaz Vložit/Vzorce pro buňky: B7E5,
B9F5 a B13G5). Vybrat oblast buněk obsahující vzorce a seznam vstupních hodnot
včetně buněk pro výpočet (buňky D2:G12), karta Data/skupina Datové nástroje/příkaz
Analýza hypotéz…/Tabulka dat…/v dialogovém okně Tabulka dat nastavit parametry:
do pole Vstupní buňka sloupce vložit odkaz na buňku s Počtem let (B5), tlačítko OK.
Pozn. Měníme-li pouze jeden vstupní údaj pro výpočty (jednokriteriální analýza), stačí zadat
pouze jeden odpovídající parametr do dialogového okna dle toho, jak je tabulka uspořádána
– v tomto případě jsou vstupní údaje ve sloupci, jedná se tedy o vstupní buňku sloupce. Excel
do tabulky automaticky dopočítá varianty výsledků pro zadané vstupní hodnoty.
2. Zobrazit list dle zadání, připravit v tabulce údaje pro výpočet (připravená tabulka musí mít
určitou strukturu, v jednotlivých částech tabulky musí být připraveny údaje pro výpočet).
Do řádku Úroková sazba (buňky E5:J5) vložit hodnoty 7 - 9,5 s krokem 0,5 (vstupní hodnoty
pro vzorce). Dále do prvního sloupce tabulky zkopírovat ze sloupce B příslušné vzorce
pro výpočet hodnot dle zadání (zkopírovat pouze vzorce - klávesy Ctrl + C, karta
Domů/skupina Schránka/příkaz Vložit/Vzorce pro buňky: B7E5, B9E6 a B13E7).
Vybrat oblast buněk obsahující vzorce a seznam vstupních hodnot včetně buněk pro výpočet
(E4:J7), karta Data/skupina Datové nástroje/příkaz Analýza hypotéz…/Tabulka dat…/
6 Další datové nástroje Příklad 3 Excel 2010
OPF v Karviné, Slezská univerzita v Opavě Kateřina Slaninová
v dialogovém okně Tabulka dat nastavit parametry: do pole Vstupní buňka řádku vložit
odkaz na buňku s Úrokovou sazbou (B4), tlačítko OK. Pozn. Měníme-li pouze jeden vstupní
údaj pro výpočty (jednokriteriální analýza), stačí zadat pouze jeden odpovídající parametr
do dialogového okna dle toho, jak je tabulka uspořádána – v tomto případě jsou vstupní
údaje v řádku, jedná se tedy o vstupní buňku řádku. Excel do tabulky automaticky dopočítá
varianty výsledků pro zadané vstupní hodnoty.
3. Zobrazit list dle zadání, připravit v tabulce údaje pro výpočet (připravená tabulka musí mít
určitou strukturu, v jednotlivých částech tabulky musí být připraveny údaje pro výpočet).
Vedle sloupce Počet let (buňky E5:E12) vložit hodnoty 3 - 10, do řádku pod textem Úroková
sazba (buňky F4:K4) vložit hodnoty 7 - 9,5 s krokem 0,5. Dále do buňky E4 zkopírovat vzorec
pro výpočet Měsíční splátky (pouze vzorec z buňky B7 - klávesy Ctrl + C, karta Domů/skupina
Schránka/příkaz Vložit/Vzorce). Vybrat oblast buněk, která obsahuje vzorec, řádek i sloupec
vstupních hodnot včetně buněk pro výpočet (E4:K12), karta Data/skupina Datové
nástroje/příkaz Analýza hypotéz…/Tabulka dat…/v dialogovém okně Tabulka dat nastavit
parametry: do pole Vstupní buňka řádku vložit odkaz na buňku s hodnotou Úrokové sazby
(B4), do pole Vstupní buňka sloupce vložit odkaz na buňku s Počtem let (B5), tlačítko OK.
Excel do tabulky automaticky dopočítá varianty výsledků pro zadané vstupní hodnoty.
4. Uložit sešit - karta Soubor/Uložit (nebo tlačítko Uložit na panelu nástrojů Rychlý přístup nebo
klávesy Ctrl+S). Zavřít sešit - karta Soubor/Zavřít (nebo tlačítko Zavřít – x v pravém horním
rohu okna).
Zpět na zadání