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 funkce SPLÁTKA s různými objemy půjčky a úrokovou sazbou k výpočtu dostupné částky půjčky na hypotéku na bydlení. Experimentování s různými hodnotami pro pozorování odpovídajících variant výsledků je běžným úkolem při analýze dat.

V Microsoft Excel jsou tabulky dat součástí sady příkazů označované jako What-If analytické nástroje. Když sestavíte a analyzujete tabulky dat, provedete analýzu what-if.

Co-if 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 k odlišování úrokové sazby a doby trvání půjčky – k vyhodnocení potenciálních měsíčních částek plateb.

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

Typy analýzy what-if    

Existují tři typy nástrojů pro analýzu co-if v Excel: scénáře, tabulky data hledání cílů. Scénáře a tabulky dat používají k výpočtu možných výsledků sady vstupních hodnot. Hledání cílů se výrazně liší, používá jeden výsledek a vypočítá možné vstupní hodnoty, které by výsledek vytvářely.

Stejně jako scénáře vám tabulky dat 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. Používání datových tabulek usnadňuje přehled o řadě 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íc než dvě proměnné, měli byste místo toho použít scénáře. I když je omezená jenom na jednu nebo dvě proměnné (jednu pro vstupní buňku řádku a jednu 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 tolik scénářů, kolik chcete.

Další informace najdete v článku Úvod k What-If analýzy.

V závislosti na počtu proměnných a vzorců, které potřebujete otestovat, vytvořte tabulky dat s jednou proměnnou nebo dvou proměnnými.

Tabulky dat s jednou proměnnou    

Pokud chcete vidět, 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 hypotéky pomocí funkce SPLÁTKA. 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 buňka D2 obsahuje platební vzorec =PMT(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 vidět, 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 buňka C2 obsahuje platební vzorec =PMT(B3/12;B4;-B5),který používá dvě vstupní buňky B3 a B4.

Data table with two variables
 

Výpočty tabulky dat    

Pokaždé, když list přepočítá, přepočítá se také všechny tabulky dat , a to i v případě, že se v datech nic nemění. Pokud chcete urychlit výpočet listu, který obsahuje tabulku dat, můžete změnit možnosti Výpočty tak, aby se list automaticky přepočítal, ale ne tabulky dat. Další informace najdete v části Zrychlení výpočtů v listu, který obsahuje tabulky dat.

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

Postupujte takto:

  1. Do vstupní buňky zadejte seznam hodnot, které chcete nahradit – buď o jeden sloupec dolů, nebo přes jeden řádek. Ponechejte 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 sloupce (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 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 v buňkách napravo od prvního vzorce.

    • Pokud je tabulka dat orientovaná na řádek (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 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 nahoře je tato oblast C2:D5.

  4. Na kartě Data klikněte na What-If Analysis >Data Table (ve skupině Datové nástroje nebo 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 sloupce. Na obrázku nahoře je vstupní buňka B3.

    • Pokud je tabulka dat orientovaná na řádek, zadejte odkaz na buňku vstupní buňky 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. Na obrázku jsou výsledné buňky formátované 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:

    • 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 What-If Analysis > Data Table (ve skupině Datové nástroje nebo Prognóza skupiny Excel 2016 ).

  4. Proveďte některou z těchto akcí:

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

    • Pokud je tabulka dat orientovaná na řádek, zadejte odkaz na buňku vstupní buňky 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 tyto 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 do buňky C2 vzorec =PMT(B3/12;B4;-B5).

  2. Zadejte jeden seznam vstupních hodnot ve stejném sloupci pod vzorcem.

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

  3. Zadejte druhý seznam na stejném řádku jako vzorec – doprava.

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

  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 počítané hodnoty (D3:E5).

    V takovém případě vyberte oblast C2:E5.

  5. Na kartě Data klikněte ve skupině Datové nástroje nebo Prognóza (v aplikaci Excel 2016 ) na položku What-If Analysis >Data Table (ve skupině Datové nástroje nebo Prognóza skupiny Excel 2016 ).  

  6. Do pole Vstupní buňka řádku zadejte odkaz na vstupní buňku vstupních hodnot 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 zadejteB3.

  8. Klikněte na tlačítko OK.

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

Dvouměnná tabulka dat 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 tomto obrázku buňka C2 obsahuje platební vzorec =PMT(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 celého sešitu nedojde k žádným výpočtům tabulky dat. Pokud chcete tabulku dat přepočítat ručně, vyberte její vzorce a stiskněte klávesu F9.

Výkon výpočtů můžete zlepšit takto:

  1. Klikněte na > možnosti > vzorce.

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

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

Pokud máte určité cíle nebo větší sady proměnných dat, můžete použít několik dalších nástrojů Excel analýzy.

Hledání řešení

Pokud znáte výsledek, který můžete očekávat od vzorce, ale přesně nevíte, jakou vstupní hodnotu vzorec potřebuje k získání tohoto výsledku, použijte Goal-Seek funkce. Podívejte se na článek Vyhledání výsledku úpravou vstupní hodnoty pomocí funkce Hledání cílů.

Excel Řešitel

Pomocí doplňku Excel Řešitel můžete najít optimální hodnotu pro sadu vstupních proměnných. Řešitel pracuje se skupinou buněk (nazývanou rozhodovací proměnné nebo jednoduše proměnnými buňkami), 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 omezujících omezení, a výsledek, který chcete mít pro buňku cíle. Další informace najdete v tomto článku: Definování a řešení problému pomocí Řešitelu.

Připojením různých čísel do buňky můžete rychle přijít s různými odpověďmi na problém. Skvělým příkladem je použití funkce SPLÁTKA s různými úroky a obdobími půjčky (v měsících) k tomu, abyste zjistili, jakou část půjčky si můžete dovolit pro dům nebo auto. Čísla zadáte do oblasti buněk, které se nazývají tabulka dat.

Tady je tabulka dat oblast buněk B2:D8. Hodnotu ve sloupci B4, částku půjčky a měsíční splátky ve sloupci D můžete změnit automaticky. Pomocí 3,75% úrokové sazby vrátí funkce D2 měsíční platbu 1 042,01 USD pomocí tohoto vzorce: =PMT(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ích, které chcete otestovat, můžete použít jednu nebo dvě proměnné.

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

V tomto živém sešitu obsahuje buňka D2 vzorec platby =SPLÁTKA(C2/12;$B$3;$B$4). Buňka B3 je proměnná buňka, ve které můžete připojit jinou dobu trvání (počet měsíčních platebních období). V buňce D2 se funkce SPLÁTKA zapojuje do úrokové sazby 3,75 %/12, 360 měsíců a půjčky ve výši 225 000 Kč a vypočítá měsíční splátku 1 042,01 Usd.

Pomocí dvou proměnných testu můžete vidět, jak různé hodnoty dvou proměnných ve vzorci změní výsledky. Můžete třeba 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 =PMT($B$3/12;$B$2;B4), který používá dvě proměnné buňky B2 a B3. V buňce C2 funkce SPLÁTKA zapojuje ú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 USD.

Potřebujete další pomoc?

Kdykoliv se můžete zeptat některého odborníka v technické komunitě Excelu nebo získat podporu v komunitě pro odpovědi.

Potřebujete další pomoc?

Rozšiřte své znalosti a dovednosti
Projít školení
Získejte nové funkce jako první
Připojit se systém Microsoft Office insiderům

Byly tyto informace užitečné?

Jak jste spokojeni s kvalitou jazyka?
Co ovlivnilo váš názor?

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

×