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

Důležité informace:  Tento článek je strojově přeložený – přečtěte si toto upozornění. Anglickou verzi tohoto článku pro referenci najdete tady.

Tabulka dat je oblast buněk, v níž můžete změnit hodnoty v některé z buněk a najít různé odpovědi na problém. Výborným příkladem je použití funkce PLATBA s různou výší půjčky a úroku, kdy zjistíte, jakou půjčku na dům či auto si můžete dovolit. Experimentování s různými hodnotami, které dávají různé výsledky, je součástí oboru zvaného analýza dat.

Tabulky dat jsou součástí sady příkazů, která se nazývají nástroje citlivostní analýzy. Při použití tabulek dat provádíte citlivostní analýzu.

Citlivostní analýza je proces změny hodnot v buňkách s cílem zjistit, jak tyto změny ovlivní výstup vzorců na listu. Například můžete pomocí tabulky dat měnit úrokovou sazbu a termín u půjčky, abyste zjistili možné částky měsíčních splátek.

Druhy citlivostní analýzy    V aplikaci Excel existují 3 druhy nástrojů pro citlivostní analýzu: scénáře, tabulky dat a hledání řešení. Scénáře a tabulky dat určují možné výsledky na základě sad vstupních dat. Hledání řešení funguje oproti scénářům a tabulkám dat odlišně: na základě zadaného výsledku určuje možné vstupní hodnoty, které povedou k tomuto výsledku.

Podobně jako scénáře pomáhají tabulky dat posoudit řadu možných výsledků. Na rozdíl od scénářů nabízejí tabulky dat všechny výstupy v jediné tabulce na jediném listu. Při použití tabulek dat lze snadno jediným pohledem posoudit řadu možností. Protože se zaměřujete jen na jednu nebo dvě proměnné, výsledky lze snadno číst a sdílet v tabulkové podobě.

Do tabulky dat nelze vložit více než 2 proměnné. Chcete-li analyzovat více než 2 proměnné, měli byste místo tabulek dat použít scénáře. Ačkoli je tabulka dat omezena pouze na 2 proměnné (1 pro vstupní buňku na řádku a 1 pro vstupní buňku ve sloupci), může zahrnovat libovolný požadovaný počet různých hodnot proměnných. Scénář může obsahovat maximálně 32 různých hodnot, lze však vytvořit libovolný počet scénářů.

Informace o citlivostní analýzy najdete v tématu Úvod do citlivostní analýzy.

Základy tabulky dat

Lze vytvořit tabulku dat s jednou nebo se dvěma proměnnými, a to podle počtu proměnných a vzorců, které chcete testovat.

Tabulky dat jednou proměnnou    Tabulka dat s jednou proměnnou použijte, pokud chcete zobrazit různými hodnoty s jednou proměnnou v jednom nebo více vzorců se změní na výsledky těchto vzorců. Například, najdete v článku jak různé úrokové sazby pomocí tabulky dat s jednou proměnnou ovlivní měsíční splátky hypoték pomocí Platba. Zadejte hodnotách proměnných do jednoho sloupce nebo řádku a výsledky se zobrazí v sousedící sloupce nebo řádku.

Na následujícím obrázku obsahuje buňka D2 platební vzorec =PLATBA(B3/12;B4;-B5), který odkazuje na vstupní buňku B3.

Tabulka dat s jednou proměnnou

Tabulka dat se dvěma proměnnými:    Tabulku dat se dvěma proměnnými vytvořte, pokud chcete zjistit, jak budou různé hodnoty dvou proměnných v jednom vzorci měnit výsledky tohoto vzorce. Například můžete tabulku dat se dvěma proměnnými použít ke zjištění, jakým způsobem různé kombinace úrokových sazeb a termínů půjčky ovlivní měsíční splátky hypotéky.

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

Tabulka dat se dvěma proměnnými

Výpočty tabulky dat.    Tabulky dat přepočítají se při každém listu, je to i v případě nebyly změněny. Zrychlení výpočtu listu obsahujícím tabulky dat, můžete změnit možnosti výpočtů na automatický přepočet listu kromě tabulek s daty. Naleznete v části Zrychlení výpočtu v listu, který obsahuje data tabulky.

Vytvoření tabulky dat s jednou proměnnou

Tabulka dat s jednou proměnnou má vstupní hodnoty zapsané buď do sloupce směrem dolů (sloupcová orientace), nebo do řádku (řádková orientace). Vzorce použité v tabulce dat s jednou proměnnou se musejí vztahovat pouze k jedné Vstupní buňka.

  1. Do jednoho sloupce (směrem dolů) nebo do jednoho řádku zadejte seznam hodnot, které chcete ve vstupní buňce nahradit. Po obou stranách hodnot ponechejte několik prázdných řádků a sloupců.

  2. Proveďte jednu z následujících akcí:

    • Pokud má tabulka dat orientaci sloupce (proměnnými hodnotami jsou ve sloupci), zadejte vzorec v buňce jeden řádek nad a o jednu buňku vpravo od sloupce s hodnotami. Tabulka dat s jednou proměnnou obrázku v části Přehled je orientovaného sloupce, a vzorec v buňce D2.
      Pokud chcete prozkoumat účinky různé hodnoty v jiných vzorců, zadejte další vzorce do buňky vpravo od prvního vzorce.

    • Jestliže má tabulka dat řádkovou orientaci (hodnoty proměnné 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 dopady 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. V případě uvedeném na prvním obrázku v předchozí části Přehled se jedná o rozsah C2:D5.

  4. Na kartě Data ve skupině Datové nástroje nebo prognózy skupiny ( Excel 2016 ) klikněte na tlačítko Citlivostní analýza a potom klikněte na Tabulku dat.

  5. Proveďte jednu z následujících akcí:

    • Jestliže má tabulka dat sloupcovou orientaci, zadejte Odkaz na buňku odpovídající vstupní buňce do pole Vstupní buňka sloupce. Při použití příkladu na prvním obrázku je vstupní buňkou buňka B3.

    • Pokud má tabulka dat řádkovou orientaci, napište odkaz na vstupní buňku do textového pole Vstupní buňka řádku.

      Poznámka: Po vytvoření tabulky dat může být vhodné změnit formát buněk s výsledky. Na obrázku jsou tyto buňky formátovány jako měna.

Přidání vzorce k tabulce dat s jednou proměnnou

Vzorce použité v tabulce dat s jednou proměnnou se musejí vztahovat ke stejné vstupní buňce.

  1. Proveďte jednu z následujících akcí:

    • Pokud má tabulka dat sloupcovou orientaci (hodnoty proměnných jsou ve sloupci), zadejte nový vzorec do prázdné buňky v horním řádku tabulky dat vpravo od stávajícího vzorce.

    • Má-li tabulka řádkovou orientaci (hodnoty proměnných jsou v řádku), zadejte nový vzorec do prázdné buňky v prvním sloupci tabulky dat pod stávající vzorec.

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

  3. Na kartě Data ve skupině Datové nástroje nebo prognózy skupiny ( Excel 2016 ) klikněte na tlačítko Citlivostní analýza a potom klikněte na Tabulku dat.

  4. Proveďte jednu z následujících akcí:

    • Jestliže má tabulka dat sloupcovou orientaci, zadejte odkaz na buňku odpovídající vstupní buňce do pole Vstupní buňka sloupce.

    • Pokud má tabulka dat řádkovou orientaci, napište odkaz na vstupní buňku do textového pole Vstupní buňka řádku.

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

Tabulka dat se dvěma proměnnými používá vzorec obsahující dva seznamy vstupních hodnot. Vzorec se musí vztahovat ke dvěma různým vstupním buňkám.

  1. Do buňky v listu zadejte vzorec, který se vztahuje ke dvěma vstupním buňkám.

    V následujícím příkladu, kde jsou počáteční hodnoty vzorce zadány v buňkách B3, B4 a B5, zadáte do buňky C2 vzorec =PLATBA(B3/12;B4;-B5).

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

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

  3. Druhý seznam zadejte do stejného řádku jako vzorec, a to vpravo od něj.

    Zadejte termíny půjčky (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 se mají objevit vypočtené hodnoty (D3:E5).

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

  5. Na kartě Data ve skupině Datové nástroje nebo prognózy skupiny ( Excel 2016 ) klikněte na tlačítko Citlivostní analýza a potom klikněte na Tabulku dat.

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

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

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

Příklad.    Tabulka dat se dvěma proměnnými může například ukázat, jakým způsobem ovlivní různé kombinace úrokových sazeb a termínů půjčky 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), ve kterém jsou použity dvě vstupní buňky, B3 a B4.

Tabulka dat se dvěma proměnnými

Zrychlení výpočtu v listu obsahujícím tabulky dat

  1. Proveďte jednu z následujících akcí:

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

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

  2. V oddílu Možnosti výpočtů v oblasti Přepočet klepněte na možnost Automaticky kromě tabulek dat.

    Tip: Další možností je klepnout na kartě Vzorce ve skupině Výpočet na šipku nabídky Možnosti výpočtu a poté na příkaz Automaticky kromě tabulek dat.

Poznámka: Pokud vyberete tuto možnost výpočtu, budou při přepočtu sešitu vynechány tabulky dat. Chcete-li tabulku dat přepočítat ručně, vyberte její vzorce a stiskněte klávesu F9.

Co dál?

Pokud máte konkrétní řešení nebo větší množství proměnných dat, můžete pomocí dalších nástrojů aplikace Excel provést citlivostní analýzu.

Hledání řešení

Jestliže znáte výsledek vzorce, ale nevíte jaké vstupní hodnotu vzorec vyžaduje k získání daného výsledku, použijte funkci hledání řešení. Naleznete v článku Použití funkce hledání řešení k získání požadovaného výsledku úpravou vstupní hodnoty.

Řešitel v Excelu

Pomocí doplňku Řešitel aplikace Excel můžete najít optimální hodnotu podle počtu proměnných. Řešitel pracuje s skupiny buněk (označovaná rozhodovacích proměnných nebo jednoduše proměnných buněk), které se používají při výpočtu vzorců v buňkách cíle a omezení. Upravuje hodnoty v buňkám rozhodovacích proměnných splňovat omezení omezení buněk a vytvoří výsledek, který se má buňka cíle. Podívejte se článek definice a vyřešení problému pomocí Řešitele.

Začátek stránky

Poznámka: Upozornění ke strojovému překladu: Tento článek přeložil počítačový systém bez zásahu člověka. Společnost Microsoft nabízí tyto strojové překlady proto, aby umožnila uživatelům, kteří nemluví anglicky, získat informace o produktech, službách a technologiích této společnosti. Protože je tento článek strojově přeložený, může obsahovat slovní, syntaktické nebo gramatické chyby.

Rozšiřte své znalosti a dovednosti
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.

×