Výpočet více výsledků pomocí tabulky dat

V Microsoft Excelu jsou tabulky dat součástí sady příkazů označovaných jako analytické nástroje citlivostní analýzy. Při vytváření a analýze tabulek dat provádíte citlivostní analýzu.

Citlivostní analýza je proces, při kterém se provádějí změny hodnot v buňkách za účelem zjištění, jak dané změny ovlivní výsledky vzorců na listu. Pomocí tabulky dat můžete například změnit úrokovou sazbu a délku splatnosti úvěru, abyste mohli vyhodnotit potenciální výši měsíčních plateb.

Typy citlivostní analýzy    

V Excelu existují tři typy analytických nástrojů citlivostní analýzy: scénáře, tabulky dat a hledání řešení. Scénáře a tabulky dat používají sady vstupních hodnot k výpočtu možných výsledků. Hledání řešení se výrazně liší, používá jeden výsledek a vypočítává možné vstupní hodnoty, které by tento výsledek generovaly.

Podobně jako scénáře vám tabulky dat pomáhají prozkoumat řadu možných výsledků. Na rozdíl od scénářů zobrazují tabulky dat všechny výsledky v jedné tabulce na jednom listu. Použití tabulek dat usnadňuje okamžité prozkoumání rozsahu možností. Zaměřujete se jenom na jednu nebo dvě proměnné, a proto jsou výsledky srozumitelné a dají se snadno sdílet ve formátu tabulky.

Tabulka dat nemůže obsahovat více než dvě proměnné. Pokud chcete analyzovat více než dvě proměnné, měli byste místo toho použít scénáře. I když je tato možnost omezená jenom na jednu nebo dvě proměnné (jednu pro vstupní buňku řádku a jednu pro vstupní buňku sloupce), tabulka dat může obsahovat libovolný počet různých hodnot proměnných. Scénář může obsahovat maximálně 32 různých hodnot, ale můžete vytvořit tolik scénářů, kolik potřebujete.

Další informace najdete v článku Úvod do citlivostní analýzy.

V závislosti na počtu proměnných a vzorců, které potřebujete testovat, vytvořte buď tabulku dat s jednou nebo dvěma proměnnými.

Tabulky dat s jednou proměnnou    

Pokud chcete zjistit, jak různé hodnoty jedné proměnné v jednom nebo více vzorcích změní výsledky těchto vzorců, použijte tabulku dat s jednou proměnnou. Pomocí tabulky dat s jednou proměnnou můžete například zjistit, jak různé úrokové sazby ovlivňují měsíční splátku hypotečního úvěru, a to pomocí funkce PLATBA. Hodnoty proměnných zadáte do jednoho sloupce nebo řádku a výsledky se zobrazí v sousedním sloupci nebo řádku.

V následujícím příkladu buňka D2 obsahuje vzorec platby =PLATBA(B3/12,B4,-B5), který odkazuje na vstupní buňku B3.

Tabulky dat se dvěma proměnnými    

Pomocí tabulky dat se dvěma proměnnými můžete zjistit, jak různé hodnoty dvou proměnných v jednom vzorci změní výsledky tohoto vzorce. Pomocí tabulky dat se dvěma proměnnými můžete například zjistit, jak různé kombinace úrokových sazeb a termínů úvěru ovlivní měsíční splátku hypotečního úvěru.

V následujícím příkladu buňka C2 obsahuje vzorec platby =PLATBA(B3/12,B4,-B5), který používá dvě vstupní buňky B3 a B4.

 

Výpočty tabulky dat    

Kdykoli se list přepočítá, přepočítají se také všechny tabulky dat, a to i v případě, že nedošlo k žádné změně dat. Pokud chcete urychlit výpočet listu, který obsahuje tabulku dat, můžete změnit možnosti výpočtu tak, aby se list automaticky přepočítal, ale ne tabulky dat. Další informace najdete v části Zrychlení výpočtu na listu, který obsahuje tabulky dat.

Tabulka dat s jednou proměnnou obsahuje své vstupní hodnoty buď v jednom sloupci (sloupcově orientovaná), nebo v řádku (řádkově orientovaná). Každý vzorec v tabulce dat s jednou proměnnou musí odkazovat pouze na jednu Vstupní buňka.

Postupujte takto:

1. Zadejte seznam hodnot, které chcete ve vstupní buňce nahradit – buď v jednom sloupci, nebo v jednom řádku. Na obou stranách hodnot ponechte několik prázdných řádků a sloupců.

2. Udělejte jednu z těchto věcí:

   • Pokud je tabulka dat sloupcově orientovaná (hodnoty proměnných jsou ve sloupci), zadejte vzorec do buňky o jeden řádek výše a jednu buňku napravo od sloupce hodnot. Tato tabulka dat s jednou proměnnou je sloupcově orientovaná a vzorec je obsažen v buňce D2.
     
     
     
     Pokud chcete prozkoumat vliv různých hodnot na jiné vzorce, zadejte do buněk napravo od prvního vzorce další vzorce.

   • Pokud je tabulka dat řádkově orientovaná (hodnoty proměnných jsou v řádku), zadejte vzorec do buňky o jeden sloupec vlevo od první hodnoty a jednu buňku pod řádkem hodnot.
     
     Pokud chcete prozkoumat vliv různých hodnot na jiné vzorce, zadejte do buněk pod prvním vzorcem další vzorce.

3. Vyberte oblast buněk obsahující vzorce a hodnoty, které chcete nahradit. Na obrázku výše je tento rozsah C2:D5.

4. Na kartě Data vyberte Citlivostní analýza >Tabulka dat (ve skupině Datové nástroje nebo ve skupině Prognóza Excel 2016). 

5. Udělejte něco z tohoto:

   • Pokud je tabulka dat sloupcově orientovaná, zadejte Odkaz na buňku na vstupní buňku do pole vstupní buňky sloupce. Na obrázku výše je vstupní buňka B3.

   • Pokud je tabulka dat řádkově orientovaná, zadejte odkaz na vstupní buňku do pole vstupní buňky řádku.

     Poznámka: Po vytvoření tabulky dat můžete chtít změnit formát buněk s výsledky. Na obrázku jsou buňky s výsledky formátovány jako měna.

Vzorce, které se používají v tabulce dat s jednou proměnnou, musí odkazovat na stejnou vstupní buňku.

Postup

1. Proveďte jednu z těchto možností:

   • Pokud je tabulka dat sloupcově orientovaná, zadejte nový vzorec do prázdné buňky napravo od existujícího vzorce v horním řádku tabulky dat.

   • Pokud je tabulka dat řádkově orientovaná, zadejte nový vzorec do prázdné buňky pod existujícím vzorcem v prvním sloupci tabulky dat.

2. Vyberte oblast buněk, která obsahuje tabulku dat, a nový vzorec.

3. Na kartě Data vyberte Citlivostní analýza > Tabulka dat (ve skupině Datové nástroje nebo ve skupině PrognózaExcel 2016).

4. Proveďte některý z těchto kroků:

   • Pokud je tabulka dat sloupcově orientovaná, zadejte odkaz na vstupní buňku do pole vstupní buňky sloupce.

   • Pokud je tabulka dat řádkově orientovaná, zadejte odkaz na vstupní buňku do pole vstupní buňky řádku.

Tabulka dat se dvěma proměnnými používá vzorec, který obsahuje dva seznamy vstupních hodnot. Vzorec musí odkazovat na dvě různé vstupní buňky.

Postupujte takto:

1. Do buňky na listu zadejte vzorec, který odkazuje na dvě vstupní buňky.

   V následujícím příkladu, ve kterém jsou počáteční hodnoty vzorce zadané v buňkách B3, B4 a B5, zadáte vzorec =PLATBA(B3/12,B4,-B5) do buňky C2.

2. Do stejného sloupce zadejte pod vzorec jeden seznam vstupních hodnot.

   V tomto případě zadejte různé úroky do buněk C3, C4 a C5.

3. Zadejte druhý seznam do stejného řádku jako vzorec – napravo od něj.

   Zadejte termíny úvěru (v měsících) do buněk D2 a E2.

4. Vyberte oblast buněk, která obsahuje vzorec (C2), řádek i sloupec hodnot (C3:C5 a D2:E2) a buňky, ve kterých chcete počítané hodnoty (D3:E5).

   V tomto případě vyberte oblast C2:E5.

5. Na kartě Data ve skupině Datové nástroje nebo ve skupině Prognóza (v Excel 2016) vyberte Citlivostní analýza >Tabulka dat (ve skupině Datové nástroje nebo ve skupině Prognóza Excel 2016). 

6. Do pole vstupní buňky řádku zadejte odkaz na vstupní buňku pro vstupní hodnoty v řádku.
   Zadejte buňku B4 do pole vstupní buňky řádku.

7. Do pole vstupní buňky sloupce zadejte odkaz na vstupní buňku pro vstupní hodnoty ve sloupci.
   Zadejte B3 do pole vstupní buňky sloupce.

8. Vyberte OK.

Příklad tabulky dat se dvěma proměnnými

Tabulka dat se dvěma proměnnými může ukázat, jak různé kombinace úrokových sazeb a termínů půjčky ovlivní měsíční splátku hypotečního úvěru. Na obrázku buňka C2 obsahuje vzorec platby =PLATBA(B3/12,B4,-B5), který používá dvě vstupní buňky B3 a B4.

Když nastavíte tuto možnost výpočtu, neproběhnou při přepočítání celého sešitu žádné výpočty tabulky dat. Pokud chcete tabulku dat přepočítat ručně, vyberte její vzorce a stiskněte klávesu F9.

Výkon výpočtu zvýšíte následujícím postupem:

1. Vyberte Soubor > Možnosti > Vzorce.

2. V části Možnosti výpočtu vyberte Automaticky.

   Tip: Volitelně můžete na kartě Vzorce vybrat šipku v části Možnosti výpočtu a pak vybrat Automaticky.

Pokud máte konkrétní cíle nebo větší sady proměnných dat, můžete k citlivostní analýze použít několik dalších excelových nástrojů.

Hledání řešení

Pokud znáte výsledek, který od vzorce očekáváte, ale nevíte přesně, jakou vstupní hodnotu vzorec potřebuje k získání tohoto výsledku, použijte funkci Hledání řešení. Přečtěte si článek Použití funkce Hledání řešení k nalezení požadovaného výsledku úpravou vstupní hodnoty.

Excel Solver

Pomocí doplňku Excel Solver můžete najít optimální hodnotu pro sadu vstupních proměnných. Solver pracuje se skupinou buněk (označovanou jako rozhodovací proměnné nebo jednoduše proměnné buňky), které se používají při výpočtu vzorců v buňkách cíle a omezení. Solver upraví hodnoty v buňkách rozhodovací proměnné tak, aby splňovaly limity pro buňky omezení, a vytvořil požadovaný výsledek pro cílovou buňku. Další informace najdete v tomto článku: Definování a řešení problému pomocí nástroje Solver.