Jak používat Správce scénářů v Excelu 2013

Možnost Správce scénářů aplikace Excel 2013 v rozbalovací nabídce tlačítka What-If Analysis na kartě Data na pásu karet vám umožňuje vytvářet a ukládat sady různých vstupních hodnot, které poskytují různé vypočítané výsledky, pojmenované scénáře . Protože jsou tyto scénáře uloženy jako součást sešitu, můžete jejich hodnoty použít k přehrání co-když jednoduše tak, že otevřete Správce scénářů a Excel zobrazí scénář v listu.

Po nastavení různých scénářů pro tabulku můžete také nechat Excel vytvořit souhrnnou zprávu, která zobrazí jak vstupní hodnoty použité v každém scénáři, tak i výsledky, které produkují ve vašem vzorci.

Jak nastavit různé scénáře v Excelu 2013

Klíčem k vytvoření různých scénářů pro tabulku je identifikace různých buněk v datech, jejichž hodnoty se mohou v jednotlivých scénářích lišit. Poté vyberte tyto buňky (známé jako změna buněk) v listu předtím, než otevřete dialogové okno Správce scénářů kliknutím na Data→ Analýza What-If→ Správce scénářů na pásu karet nebo stisknutím Alt+AWS.

Níže vidíte tabulku Prognóza prodeje 2014 po výběru tří měnících se buněk v listu – H3 s názvem Prodej_Růst, H4 s názvem COGS (náklady na prodané zboží) a H6 s názvem Výdaje – a poté otevřete dialogové okno Správce scénářů (Alt+AWS ).

Vytvořme tři scénáře pomocí následujících sad hodnot pro tři měnící se buňky:

• Nejpravděpodobnější případ, kdy procento Sales_Growth je 5 %, COGS je 20 % a náklady jsou 28 %

• Nejlepší případ, kdy procento Sales_Growth je 8 %, COGS je 18 % a náklady jsou 20 %

• Nejhorší případ, kdy procento růstu prodeje je 2 %, COGS je 25 % a náklady jsou 35 %

Chcete-li vytvořit první scénář, kliknutím na tlačítko Přidat v dialogovém okně Správce scénářů otevřete dialogové okno Přidat scénář, do pole Název scénáře zadejte Nejpravděpodobnější případ a klepněte na OK. (Nezapomeňte, že tři buňky aktuálně vybrané v listu, H3, H4 a H6, jsou již uvedeny v textovém poli Změna buněk v tomto dialogovém okně.)

Excel poté zobrazí dialogové okno Hodnoty scénáře, kde před kliknutím na tlačítko Přidat přijmete následující hodnoty již zadané do každého ze tří textových polí (z tabulky Prognóza prodeje 2014), Prodej_Růst, COGS a Výdaje:

• 0,05 v textovém poli Sales_Growth

• 0,2 v textovém poli COGS

• 0,28 v textovém poli Výdaje

Vždy přiřaďte názvy rozsahů měnícím se buňkám, než začnete vytvářet různé scénáře, které je používají. Tímto způsobem Excel vždy zobrazí názvy oblastí buněk, nikoli jejich adresy v dialogovém okně Hodnoty scénáře.

Po kliknutí na tlačítko Přidat Excel znovu zobrazí dialogové okno Přidat scénář, kde do pole Název scénáře zadáte Nejlepší případ a do dialogového okna Hodnoty scénáře následující hodnoty:

• 0,08 v textovém poli Sales_Growth

• 0,18 v textovém poli COGS

• 0,20 v textovém poli Výdaje

Po provedení těchto změn klikněte znovu na tlačítko Přidat. Tím se otevře dialogové okno Přidat scénář, kde zadáte nejhorší případ jako název scénáře a následující hodnoty scénáře:

• 0,02 v textovém poli Sales_Growth

• 0,25 v textovém poli COGS

• 0,35 v textovém poli Výdaje

Protože toto je poslední scénář, který chcete přidat, klepněte na tlačítko OK namísto Přidat. Tím se znovu otevře dialogové okno Správce scénářů, tentokrát se zobrazením názvů všech tří scénářů – Nejpravděpodobnější případ, Nejlepší případ a Nejhorší případ – ve svém seznamu Scénáře.

Chcete-li, aby aplikace Excel začlenila měnící se hodnoty přiřazené k některému z těchto tří scénářů do tabulky Prodejní prognóza 2014, klikněte na název scénáře v tomto seznamu a poté na tlačítko Zobrazit.

Po přidání různých scénářů pro tabulku do tabulky nezapomeňte po zavření dialogového okna Správce scénářů sešit uložit. Tímto způsobem budete mít přístup k různým scénářům pokaždé, když otevřete sešit v Excelu, jednoduše otevřete Správce scénářů, vyberete název scénáře a kliknete na tlačítko Zobrazit.

Jak vytvořit souhrnnou zprávu Excel 2013

Po přidání scénářů do tabulky v tabulkovém procesoru můžete nechat Excel vytvořit souhrnnou zprávu. Tato sestava zobrazuje měnící se a výsledné hodnoty nejen pro všechny scénáře, které jste definovali, ale také aktuální hodnoty zadané do měnících se buněk v tabulce listu v době generování sestavy.

Chcete-li vytvořit souhrnnou zprávu, otevřete dialogové okno Správce scénářů (Data→ Analýza What-If→ Správce scénářů nebo Alt+AWS) a poté kliknutím na tlačítko Souhrn otevřete dialogové okno Souhrn scénářů.

Toto dialogové okno vám dává na výběr mezi vytvořením (statického) Souhrnu scénáře (výchozí) a (dynamické) sestavy kontingenční tabulky scénářů. Můžete také upravit rozsah buněk v tabulce, která je zahrnuta v části Buňky výsledků souhrnné sestavy, úpravou rozsahu buněk v textovém poli Buňky výsledků předtím, než kliknutím na tlačítko OK vygenerujete sestavu.

Po klepnutí na tlačítko OK Excel vytvoří souhrnnou sestavu pro měnící se hodnoty ve všech scénářích (a aktuálním listu) spolu s vypočítanými hodnotami v buňkách výsledků na novém listu (s názvem Souhrn scénářů). Potom můžete přejmenovat a přemístit list Souhrn scénářů, než jej uložíte jako součást souboru sešitu.