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 What-If analytické nástroje. Při vytváření a analýze tabulek dat provádíte citlivostní analýzu.

Citlivostní analýza je proces změny hodnot v buňkách, abyste viděli, jak tyto změny ovlivní výsledek vzorců na listu. Pomocí tabulky dat můžete například měnit úrokovou sazbu a dobu trvání půjčky – k vyhodnocení potenciálních měsíčních plateb.

Typy citlivostní analýzy    

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

Podobně jako ve scénářích vám tabulky dat pomohou prozkoumat sadu možných výsledků. Na rozdíl od scénářů zobrazují tabulky dat všechny výsledky v jedné tabulce na jednom listu. Pomocí tabulek dat můžete snadno na první pohled prozkoumat celou řadu 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 omezena pouze na jednu nebo dvě proměnné (jednu pro vstupní buňku řádku a jednu pro buňku pro vstup sloupce), tabulka dat může obsahovat tolik různých proměnných hodnot, kolik potřebujete. Scénář může mít maximálně 32 různých hodnot, ale můžete vytvořit libovolný počet scénářů.

Další informace najdete v článku Úvod do What-If Analysis.

Vytvořte tabulky dat s jednou nebo dvěma proměnnými v závislosti na počtu proměnných a vzorců, které potřebujete otestovat.

Tabulky dat s jednou proměnnou    

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

Na následujícím obrázku obsahuje buňka D2 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 zjistíte, 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 podmínek půjčky ovlivní měsíční splátku hypotéky.

Na následujícím obrázku obsahuje buňka C2 platební vzorec = PLATBA(B3/12;B4;-B5), který používá dvě vstupní buňky B3 a B4.

 

Výpočty tabulek dat    

Pokaždé, když list přepočítá, přepočítají se také všechny tabulky dat, i když nedošlo k žádné změně dat. Chcete-li urychlit výpočet listu, který obsahuje tabulku dat, můžete změnit možnosti Výpočet tak, aby se automaticky přepočetl list, 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 vstupní hodnoty buď v jednom sloupci (orientovaném na sloupce), nebo v řádku (orientovaném na řádky). Každý vzorec v tabulce dat s jednou proměnnou musí odkazovat pouze na jeden Vstupní buňka.

Postupujte takto:

1. Zadejte seznam hodnot, které chcete nahradit do vstupní buňky – buď o jeden sloupec, nebo přes jeden řádek. Na obou stranách hodnot ponechte několik prázdných řádků a sloupců.

2. Udělejte něco z tohoto:

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

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

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 klikněte na citlivostní analýza > tabulka dat (ve skupině Datové nástroje nebo ve skupině Prognóza Excel 2016 ).

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

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

   • Pokud je tabulka dat orientovaná na řádky, zadejte odkaz na vstupní buňku do pole Vstupní buňka řádku .

     Poznámka: Po vytvoření tabulky dat můžete chtít změnit formát výsledných buněk. Výsledné buňky jsou na obrázku formátované jako měna.

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

Postup

1. Udělejte jednu z těchto možností:

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

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

2. Vyberte oblast buněk obsahující tabulku dat a nový vzorec.

3. Na kartě Data klikněte na citlivostní analýza > tabulku dat (ve skupině Datové nástroje nebo ve skupině PrognózaExcel 2016 ).

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

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

   • Pokud je tabulka dat orientovaná na řádky, zadejte odkaz na vstupní buňku do pole Vstupní buňka řá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 zadány v buňkách B3, B4 a B5, zadáte do buňky C2 vzorec =PMT(B3/12;B4;-B5 ).

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

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

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

   Do buněk D2 a E2 zadejte podmínky půjčky (v měsících).

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

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

5. Na kartě Data klikněte ve skupině Datové nástroje nebo Prognóza (v Excel 2016 ) na tlačítko Citlivostní analýza > tabulka dat (ve skupině Datové nástroje nebo ve skupině Prognóza Excel 2016 ).

6. Do pole Vstupní buňka řádku zadejte odkaz na vstupní buňku pro vstupní hodnoty v řádku.
   Do pole Vstupní buňka řádku zadejte buňku B4.

7. Do pole Vstupní buňka sloupce zadejte odkaz na vstupní buňku pro vstupní hodnoty ve sloupci.
   Do pole Vstupní buňka sloupce zadejte B3.

8. Klikněte na 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 podmínek půjčky ovlivní měsíční splátku hypotéky. Na obrázku obsahuje buňka C2 platební vzorec = PLATBA(B3/12;B4;-B5), který používá dvě vstupní buňky, B3 a B4.

Když nastavíte tuto možnost výpočtu, při přepočtu v celém sešitu nedojde k žádným výpočtům s tabulkou dat. Pokud chcete tabulku dat přepočítat ručně, vyberte její vzorce a stiskněte klávesu F9.

Pokud chcete zvýšit výkon výpočtů, postupujte následovně:

1. Klikněte na Možnosti > souborů > Vzorce.

2. V části Možnosti výpočtu klikněte v části Výpočet na Automaticky s výjimkou tabulek dat.

   Tip: Volitelně můžete na kartě Vzorce kliknout na šipku u možnosti výpočtu a potom na automaticky kromě tabulek dat (ve skupině Výpočet ).

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

Hledání řešení

Pokud znáte výsledek, který má vzorec očekávat, ale nevíte přesně, jakou vstupní hodnotu vzorec potřebuje k získání tohoto výsledku, použijte funkci Goal-Seek. Informace o tom, jak najít požadovaný výsledek úpravou vstupní hodnoty, najdete v článku Použití hledání cílů.

Řešitel v Excelu

Pomocí doplňku Řešitel v Excelu můžete najít optimální hodnotu pro sadu vstupních proměnných. Řešitel pracuje se skupinou buněk (označovanými 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í. Řešitel upraví hodnoty v buňkách rozhodovacích proměnných tak, aby splňovaly limity buněk omezení, a vygeneruje požadovaný výsledek pro buňku cíle. Další informace najdete v tomto článku: Definování a řešení problému pomocí Řešitele.