Přihlásit se pomocí účtu Microsoft
Přihlaste se nebo si vytvořte účet.
Dobrý den,
Vyberte jiný účet.
Máte více účtů.
Zvolte účet, pomocí kterého se chcete přihlásit.

Tabulka dat je oblast buněk, ve které můžete změnit hodnoty v některých buňkách a přijít s různými odpověďmi na problém. Dobrým příkladem tabulky dat je použití funkce PMT s různými částkami půjčky a úrokovými sazbami k výpočtu dostupné částky u hypotéky na bydlení. Běžným úkolem při analýze dat je experimentování s různými hodnotami, abyste viděli odpovídající odchylky ve výsledcích.

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.

Poznámka: S tabulkami dat a jazykem Visual Basic for Applications (VBA) můžete provádět výpočty rychleji. Další informace najdete v tématu Excel What-If datové tabulky: Rychlejší výpočet pomocí jazyka VBA.

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.

Tabulka dat s jednou proměnnou

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.

Data table with two variables
 

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.



      Tabulka dat s jednou proměnnou 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.

Data table with two variables

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.

Když do buňky zapojíte různá čísla, můžete rychle přijít s různými odpověďmi na problém. Skvělým příkladem je použití funkce PMT s různými úrokovými sazbami a obdobími půjčky (v měsících), abyste zjistili, kolik půjčky si můžete dovolit na dům nebo auto. Čísla zadáte do oblasti buněk, které se říká tabulka dat.

Tabulka dat je oblast buněk B2:D8. Můžete změnit hodnotu v B4, částku půjčky a měsíční splátky ve sloupci D automaticky aktualizovat. Při použití 3,75% úrokové sazby vrátí D2 měsíční splátku 1 042,01 Kč pomocí tohoto vzorce: =PMT(C2/12,$B 3 Kč $B 4 Kč).

Tato oblast buněk B2:D8 představuje tabulku dat.

V závislosti na počtu proměnných a vzorců, které chcete testovat, můžete použít jednu nebo dvě proměnné.

Pomocí testu s jednou proměnnou zjistíte, jak různé hodnoty jedné proměnné ve vzorci změní výsledky. Pomocí funkce PMT můžete například změnit úrokovou sazbu měsíční splátky hypotéky. Hodnoty proměnných (úrokové sazby) zadáte do jednoho sloupce nebo řádku a výsledky se zobrazí v blízkém sloupci nebo řádku.

V tomto živém sešitu obsahuje buňka D2 vzorec platby =PLATBA(C2/12;$B$3;$B$4). Buňka B3 je proměnná buňka, do které můžete zapojit jinou délku období (počet měsíčních platebních období). V buňce D2 funkce PMT zapojuje úrokovou sazbu 3,75%/12, 360 měsíců a půjčku ve výši 225 000 Kč a vypočítá měsíční splátku ve výši 1 042,01 Kč.

Pomocí testu se dvěma proměnnými zjistíte, jak různé hodnoty dvou proměnných ve vzorci změní výsledky. Můžete například otestovat různé kombinace úrokových sazeb a počtu měsíčních platebních období pro výpočet splátky hypotéky.

V tomto živém sešitu obsahuje buňka C3 platební vzorec = PLATBA($B$3/12;$B$2;B4), který používá dvě proměnné buňky B2 a B3. V buňce C2 funkce PMT zapojí úrokovou sazbu 3,875%/12, 360 měsíců a půjčku ve výši 225 000 Kč a vypočítá měsíční splátku 1 058,03 Kč.

Potřebujete další pomoc?

Kdykoli se můžete zeptat odborníka z komunity Excel Tech nebo získat podporu v komunitách.

Potřebujete další pomoc?

Chcete další možnosti?

Prozkoumejte výhody předplatného, projděte si školicí kurzy, zjistěte, jak zabezpečit své zařízení a mnohem více.

Komunity vám pomohou klást otázky a odpovídat na ně, poskytovat zpětnou vazbu a vyslechnout odborníky s bohatými znalostmi.

Byly tyto informace užitečné?

Jak jste spokojeni s kvalitou jazyka?
Co ovlivnilo váš názor?
Po stisknutí tlačítka pro odeslání se vaše zpětná vazba použije k vylepšování produktů a služeb Microsoftu. Váš správce IT bude moci tato data shromažďovat. Prohlášení o zásadách ochrany osobních údajů.

Děkujeme vám za zpětnou vazbu.

×