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

Tabulka dat je oblast buněk, ve které můžete v některých buňkách změnit hodnoty některých buněk a přivést k problému různé odpovědi. Dobrým příkladem datové tabulky je funkce platba s různými částkami půjček a úrokovými sazbami k výpočtu dostupné částky na základě hypotéky na splátky. Hledání různých hodnot za účelem sledování odpovídající varianty ve výsledcích je běžným úkolem při analýze dat.

V aplikaci Microsoft Excel jsou tabulky dat součástí sady příkazů známých jako 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 změny hodnot v buňkách, abyste viděli, jak tyto změny ovlivní výsledek vzorců na listu. Tabulku dat můžete například použít ke změně úrokové sazby a délky termínu půjčky za účelem vyhodnocení potenciálních měsíčních platebních částek.

Poznámka: Pomocí tabulek dat a jazyka VBA (Visual Basic for Applications) můžete provádět rychlejší výpočty. Další informace najdete v tématu tabulky dat citlivosti aplikace Excel: rychlejší výpočty pomocí jazyka VBA.

Typy citlivostní analýzy    

V Excelu jsou tři typy nástrojů citlivostní analýzy: scénáře, datové tabulkya hledání řešení. Tabulky scénáře a data používají sady vstupních hodnot k výpočtu možných výsledků. Hledání cílů se liší od sebe, používá jeden výsledek a vypočte možné vstupní hodnoty, které by tento výsledek vytvořily.

Tabulky dat vám stejně jako scénáře pomáhají 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 prostudovat přehled o možnostech. 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 nesmí obsahovat více než dvě proměnné. Pokud chcete analyzovat víc než dvě proměnné, doporučujeme místo toho použít scénáře. I když je omezené jenom na jednu nebo dvě proměnné (jeden pro vstupní buňku řádku a jeden pro vstupní buňku sloupce), může tabulka dat obsahovat tolik různých hodnot proměnných, kolik chcete. 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 citlivostní analýzy.

V závislosti na počtu proměnných a vzorců, které chcete testovat, vytvořte tabulky dat s jednou proměnnou nebo se dvěma proměnnými.

Tabulka 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í tabulky dat s jednou proměnnou můžete například zjistit, jak různé úrokové sazby ovlivní měsíční platbu hypotéky pomocí funkce platba. Hodnoty proměnných se zadávají do jednoho sloupce nebo řádku a výsledky se zobrazí v sousedním sloupci nebo řádku.

Na následujícím obrázku buňka D2 obsahuje vzorec platby = splátka (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 můžete zjistit, jak různé hodnoty dvou proměnných v jednom vzorci změní výsledek vzorce. Pomocí tabulky dat se dvěma proměnnými můžete například zjistit, jaký vliv mají různé kombinace úrokových sazeb a podmínek půjčky měsíční platbu.

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

Data table with two variables
 

Výpočty tabulek dat    

Při každém přepočítávání listu budou také přepočítány všechny tabulky dat – i když data nebyla změněna. Chcete-li urychlit výpočet listu, který obsahuje tabulku dat, můžete změnit možnosti výpočtu a automaticky přepočítat list, ale ne tabulky dat. Další informace najdete v oddílu zrychlení výpočtu v listu, který obsahuje tabulky dat.

Tabulka dat s jednou proměnnou obsahuje vstupní hodnoty v jednom sloupci (orientované na sloupec) nebo napříč řádkem (řádková orientace). Každý vzorec v tabulce dat s jednou proměnnou musí odkazovat jenom na jednu Vstupní buňka.

Postupujte takto:

  1. Zadejte seznam hodnot, které chcete nahradit ve vstupní buňce, a to buď o jeden sloupec směrem dolů, nebo po jednotlivých řádcích. Zanechte několik prázdných řádků a sloupců na obou stranách hodnot.

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

    • Pokud je tabulka dat orientovaná na sloupec (proměnné hodnoty jsou ve sloupci), zadejte vzorec do buňky nad a vpravo od sloupce hodnot. Tato tabulka dat s jednou proměnnou se orientuje na sloupec a vzorec je obsažen v buňce D2.

      Tabulka dat s jednou proměnnou

      Pokud chcete prozkoumat účinky různých hodnot v jiných vzorcích, zadejte další vzorce do buněk napravo od prvního vzorce.

    • Pokud má tabulka dat řádkovou orientaci (hodnoty jsou na řádku), zadejte vzorec do buňky o jeden sloupec vlevo od první hodnoty a jednu buňku pod řádkem hodnot.

      Pokud chcete prozkoumat účinky různých hodnot v jiných vzorcích, zadejte další vzorce do buněk pod první vzorec.

  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 možnost citlivostní analýza >Tabulka dat (ve skupině datové nástroje nebo ve skupině prognóz Excel 2016 ). 

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

    • Pokud je tabulka dat sloupcová, zadejte Odkaz na buňku pro vstupní buňku do pole Vstupní buňka sloupce . Ve výše uvedeném obrázku je vstupní buňka B3.

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

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

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

Postup

  1. Proveďte jednu z těchto akcí:

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

    • Pokud má tabulka dat řádkovou orientaci, zadejte nový vzorec do prázdné buňky pod stávají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 možnost citlivostní analýza> Tabulka dat (ve skupině datové nástroje nebo ve skupině prognózExcel 2016 ).

  4. Dále si vyberte z následujících možností:

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

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

V tabulce dat s dvěma proměnnými se 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 se počáteční hodnoty vzorce zadávají do buněk B3, B4 a B5, zadejte do buňky C2 vzorec = splátka (B3/12; B4;-B5) .

  2. Jeden seznam vstupních hodnot zadejte do stejného sloupce pod vzorec.

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

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

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

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

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

  5. Na kartě data klikněte ve skupině datové nástroje nebo ve skupině prognóz (v Excel 2016 ) na položku citlivostní analýzy >data (ve skupině datové nástroje nebo ve skupině prognózy 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 vstupních hodnot 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

V tabulce dat se dvěma proměnnými může být uvedeno, jaký vliv mají různé kombinace úrokových sazeb a podmínek půjčky měsíční platbu. V tomto obrázku obsahuje buňka C2 vzorec platby = splátka (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, neobjeví se při přepočtu na celém sešitu žádné výpočty datové tabulky. Pokud chcete tabulku dat ručně přepočítat, vyberte její vzorce a stiskněte F9.

Při výpočtu výsledků postupujte takto:

  1. Dále si vyberte z následujících možností:

    • V Excel 2007 klikněte na tlačítko Microsoft Office Obrázek tlačítka , klikněte na Možnosti aplikace Excela potom na kategorii vzorce .

    • Ve všech ostatních verzích klikněte na > Možnosti > vzorce.

  2. V části Možnosti výpočtů v části Výpočetklikněte na automaticky s výjimkou tabulek dat.

    Tip: Případně na kartě vzorce klikněte na šipku u Možnosti výpočtůa pak klikněte na automaticky s výjimkou tabulek dat (ve skupině Výpočet ).

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

Hledání řešení

Pokud víte, že výsledek očekáváte ze vzorce, ale nevíte přesně, jaká vstupní hodnota vzorec vyžaduje, použijte funkci hledání řešení. Podívejte se na článek použití funkce hledání řešení k nalezení požadovaného výsledku úpravou vstupní hodnoty.

Řešitel aplikace Excel

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 (nazvaných proměnné rozhodnutí nebo jednoduše proměnnými buňkami), které se používají při výpočtu vzorců v buňkách cíl a omezení. Řešitel upraví hodnoty v rozhodovacích proměnných buněk tak, aby vyhovovaly limitům buněk omezení, a vytvoří výsledek, který chcete použít pro buňku cíle. Další informace najdete v tomto článku: Definování a vyřešení problému pomocí Řešitele.

Když do buňky připojíte různá čísla, můžete rychle vytvořit různé odpovědi na problém. Skvělý příklad používá funkci platba s různými úrokovými sazbami a obdobími půjčky (v měsících), které vám pomůžou zjistit, jak velkou částku půjčky můžete poskytnout domů nebo automobilu. Čísla můžete zadat do oblasti buněk, která se nazývá 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í platby ve sloupci D automaticky aktualizovat. Při použití sazby 3,75% úroková sazba D2 vrátí měsíční platbu $1 042,01 pomocí vzorce: = SPLÁTKa (C2/12; $B $3 $B $4).

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 můžete zjistit, jak se různé hodnoty jedné proměnné ve vzorci změní. Můžete například změnit úrokovou sazbu měsíční platby hypotéky pomocí funkce platba. Hodnoty proměnných se zadávají do jednoho sloupce nebo řádku a výsledky se zobrazí v blízkém sloupci nebo řádku.

Buňka D2 v tomto živém sešitu obsahuje vzorec platby =splátka (C2/12, $B $3, $B $4). Buňka B3 je Proměnná , kde můžete připojit jinou délku období (počet měsíčních platebních období). V buňce D2 připojí funkce platba v úrokové sazbě 3.75%/12, 360 měsíců a $225 000 půjčku a vypočte měsíční platbu $1 042,01.

Pomocí dvou proměnných můžete zjistit, jak se různé hodnoty dvou proměnných ve vzorci změní. Můžete třeba vyzkoušet různé kombinace úrokových sazeb a počet měsíčních platebních období pro výpočet platby hypotéky.

V tomto živém sešitu obsahuje buňka C3 vzorec platby =splátka ($B $3/12, $B $2; B4), který používá dvě proměnné buňky, B2 a B3. V buňce C2 je funkce platba Zazátkovaná jako úroková sazba 3.875%/12, 360 měsíců a $225 000 půjčka a vypočte měsíční platbu $1 058,03.

Potřebujete další pomoc?

Kdykoli se můžete zeptat některého odborníka v technické komunitě Excelu, získat podporu v komunitě pro odpovědi, případně navrhnout novou funkci nebo vylepšení na fóru Excel User Voice.

Poznámka:  Tato stránka byla přeložena automaticky a může obsahovat gramatické chyby nebo nepřesnosti. Naším cílem je to, aby pro vás byl její obsah užitečný. Mohli byste nám prosím dát vědět, jestli vám informace pomohly? Pokud chcete, můžete se podívat na anglickou verzi článku.

Rozšiřte své dovednosti s Office
Projít školení
Získejte nové funkce jako první
Připojte se k účastníkům programu Office Insiders

Byly tyto informace užitečné?

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

Děkujeme vám za váš názor. Vypadá to, že bude užitečné, když vás spojíme s některým z našich agentů z podpory Office.

×