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

Tabulka dat je oblast buněk, která ukazuje, jak změna jedné nebo dvou proměnných ve vzorci ovlivní výsledky těchto vzorců. Tabulky dat poskytují zkratku pro výpočet více výsledků v jedné operaci a způsob, jak zobrazit a porovnat výsledky všech různých variant společně na listu.

Přehled

Tabulky dat jsou součástí sady příkazů, která se nazývá citlivostní analytické nástroje. Při použití tabulek dat provádíte citlivostní analýzu. Citlivostní analýza změní hodnoty v buňkách, aby zjistila, jak tyto změny ovlivní výsledky vzorců na listu. Pomocí tabulky dat můžete například změnit úrokovou sazbu a délku splatnosti půjčky a určit tak možné výši měsíčních plateb.

V Excelu existují tři druhy analytických nástrojů citlivostní analýzy: scénáře, tabulky dat a hledání řešení. Scénáře a tabulky dat na základě sad vstupních hodnot určují možné výsledky. Hledání řešení se od scénářů a tabulek dat liší v tom, že na základě výsledku se určují možné vstupní hodnoty, které zajistí daný výsledek. 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 mnoho 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.

Základy tabulky dat

V závislosti na počtu proměnných a vzorců, které chcete testovat, můžete vytvořit tabulku dat s jednou nebo dvěma proměnnými. 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.

Další informace najdete v tématu Funkce PLATBA.

Buňka D2 obsahuje vzorec platby =PLATBA(B3/12,B4,-B5), který odkazuje na vstupní buňku B3.

Tabulka dat s jednou proměnnou

Vstupní buňka.

Seznam hodnot, které Excel nahrazuje ve vstupní buňce (B3).

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.

Buňka C2 obsahuje vzorec platby =PLATBA(B3/12,B4,-B5), který používá dvě vstupní buňky B3 a B4.

Tabulka dat se dvěma proměnnými

Vstupní buňka sloupce.

Seznam hodnot, které Excel dosadí do vstupní buňky řádku (B4).

Vstupní buňka řádku.

Seznam hodnot, které Excel nahradí ve vstupní buňce sloupce (B3).

Tabulky dat jsou při přepočtu listu přepočítány, i když se nezměnily. 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.

Vytvoření tabulky dat s jednou proměnnou

Tabulka dat s jednou proměnnou obsahuje vstupní hodnoty, které jsou uvedené buď ve sloupci směrem dolů (sloupcově orientovaná), nebo v řádku (řádkově orientovaná). Vzorce, které se používají v tabulce dat s jednou proměnnou, musí odkazovat pouze na jednu vstupní buňku.

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	Postup
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. Ilustrace tabulky dat s jednou proměnnou zobrazená v části Přehled je sloupcově orientovaná a vzorec je obsažen v buňce D2. Poznámka: Pokud chcete prozkoumat vliv různých hodnot na jiné vzorce, zadejte další vzorce do buněk napravo od prvního vzorce.
Řá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 Poznámka: Pokud chcete prozkoumat vliv různých hodnot na jiné vzorce, zadejte další vzorce do buněk pod prvním vzorcem.

1. Vyberte oblast buněk obsahující vzorce a hodnoty, které chcete nahradit. Podle prvního příkladu v předchozí části Přehled je tento rozsah C2:D5.

2. > Klikněte naTabulka datcitlivostní analýzy> dat.

   

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

Pokud je tabulka dat	Postup
Sloupcově orientované	Zadejte odkaz na vstupní buňku do pole vstupní buňky sloupce . V příkladu uvedeném na prvním obrázku je vstupní buňka B3.
Řádkově orientovaná	Zadejte odkaz na vstupní buňku do pole vstupní buňky řádku .

Přidání vzorce do tabulky dat s jednou proměnnou

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

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

Pokud je tabulka dat	Postup
Sloupcově orientované (hodnoty proměnných jsou ve sloupci)	Zadejte nový vzorec do prázdné buňky napravo od existujícího vzorce v horním řádku tabulky dat.
Řádkově orientované (hodnoty proměnných jsou v řádku)	Zadejte nový vzorec do prázdné buňky pod existujícím vzorcem v prvním sloupci tabulky dat.

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

2. > Klikněte naTabulka datcitlivostní analýzy> dat.

   

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

Pokud je tabulka dat	Postup
Sloupcově orientované	Zadejte odkaz na vstupní buňku do pole vstupní buňky sloupce .
Řádkově orientovaná	Zadejte odkaz na vstupní buňku do pole vstupní buňky řádku .

Vytvoření tabulky dat se dvěma proměnnými

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.

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, zadejte 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. > Klikněte naTabulka datcitlivostní analýzy> dat.

   

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

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.

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. 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.

Vstupní buňka sloupce.

Seznam hodnot, které Excel dosadí do vstupní buňky řádku (B4).

Vstupní buňka řádku.

Seznam hodnot, které Excel nahradí ve vstupní buňce sloupce (B3).

Zrychlení výpočtu na listu, který obsahuje tabulky dat

1. V nabídce Excel klikněte na Předvolby.
2. V části Vzorce a seznamy klikněte na Výpočet a pak klikněte na Automaticky s výjimkou tabulek dat.

Viz také

Úvod do analýzy What-If

Předpověď trendů dat

Definování a vyřešení problému pomocí Řešitele

Použití funkce Hledání řešení k nalezení požadovaného výsledku vzorce