Pokud máte složitý vzorec závislý na více proměnných a chcete pochopit, jak změna těchto vstupů efektivně ovlivní výsledky, je tabulka dat analýzy What-If v Excelu mocným nástrojem. Umožňuje vám vidět všechny možné výsledky rychlým pohledem. Zde je podrobný návod, jak vytvořit datovou tabulku v Excelu. Kromě toho probereme některé klíčové body pro efektivní používání datových tabulek a předvedeme další operace, jako je mazání, úpravy a přepočítávání datových tabulek.

Co je datová tabulka v Excelu?

Vytvořte tabulku dat s jednou proměnnou

• Sloupcově orientovaná datová tabulka
• Řádkově orientovaná datová tabulka
• Více vzorců pro tabulku dat s jednou proměnnou

Vytvořte datovou tabulku se dvěma proměnnými

Klíčové body pomocí datových tabulek

Další operace pro použití datových tabulek

• Odstraňte datovou tabulku
• Upravit výsledek tabulky dat
• Přepočítat datovou tabulku ručně

Co je datová tabulka v Excelu?

V Excelu je datová tabulka jedním z nástrojů What-If Analysis, který umožňuje experimentovat s různými vstupními hodnotami pro vzorce a sledovat změny ve výstupu vzorce. Tento nástroj je neuvěřitelně užitečný pro zkoumání různých scénářů a provádění analýz citlivosti, zvláště když vzorec závisí na více proměnných.

V Excelu existují dva typy datových tabulek:

Tabulka dat s jednou proměnnou: To vám umožní analyzovat, jak různé hodnoty jedné proměnné ovlivní vzorec. Datovou tabulku s jednou proměnnou můžete nastavit buď ve formátu orientovaném na řádky nebo sloupce, v závislosti na tom, zda chcete měnit svůj vstup přes řádek nebo dolů ve sloupci.

Tabulka dvou proměnných dat: Tento typ vám umožňuje vidět dopad změny dvou různých proměnných na výsledek vzorce. V tabulce dat se dvěma proměnnými měníte hodnoty podél řádku i sloupce.

Vytvořte tabulku dat s jednou proměnnou

Vytvoření tabulky dat s jednou proměnnou v Excelu je cenná dovednost pro analýzu toho, jak mohou změny v jednom vstupu ovlivnit různé výsledky. Tato část vás provede procesem vytváření tabulek dat s jednou proměnnou orientovaných na sloupce, řádky a více vzorců.

Úžasný! Používání efektivních karet v Excelu, jako je Chrome, Edge, Firefox a Safari!
Ušetřete 50% svého času a snížíte tisíce kliknutí myší každý den!

---

Sloupcově orientovaná datová tabulka

Datová tabulka orientovaná na sloupce je užitečná, když chcete otestovat, jak různé hodnoty jedné proměnné ovlivňují výstup vzorce, přičemž hodnoty proměnných jsou uvedeny ve sloupci. Podívejme se na jednoduchý finanční příklad:

Předpokládejme, že uvažujete o půjčce ve výši 50,000 3 $, kterou plánujete splácet po dobu 36 let (ekvivalent XNUMX měsíců). Chcete-li vyhodnotit, jaká měsíční splátka by byla dostupná na základě vašeho platu, zajímá vás, jak by různé úrokové sazby ovlivnily částku, kterou musíte každý měsíc platit.

Krok 1: Nastavte základní vzorec

Pro výpočet platby zde nastavím úrok na 5 %. Do buňky B4 zadejte vzorec PMT, ten vypočítá měsíční splátku na základě úrokové sazby, počtu období a výše půjčky. Viz snímek obrazovky:

= -PMT($B$1/12, $B$2*12, $B$3)

Krok 2: Uveďte úrokové sazby ve sloupci

Ve sloupci uveďte různé úrokové sazby, které chcete testovat. Uveďte například hodnoty od 4 % do 11 % v krocích po 1 % ve sloupci a pro výsledky ponechte alespoň jeden prázdný sloupec vpravo. Viz snímek obrazovky:

Krok 3: Vytvořte datovou tabulku

1. Do buňky E2 zadejte tento vzorec: = B4.
   
   Poznámka: B4 je buňka, kde se nachází váš hlavní vzorec, toto je vzorec, jehož výsledky chcete vidět, jak se mění, když měníte úrokovou sazbu.
   
2. Vyberte rozsah, který obsahuje váš vzorec, seznam úrokových sazeb a sousední buňky pro výsledky (např. vyberte D2 až E10). Viz snímek obrazovky:
   
3. Přejděte na pás karet, klikněte na Data klepněte na kartu Analýza What-If > Tabulka dat, viz screenshot:
   
4. v Tabulka dat v dialogovém okně klepněte na Vstupní buňka sloupce box (protože pro vstupní hodnoty používáme sloupec) a vyberte buňku proměnné, na kterou se ve vašem vzorci odkazuje. V tomto příkladu vybereme B1, který obsahuje úrokovou sazbu. Konečně klikni OK tlačítko, viz screenshot:
   
5. Excel automaticky vyplní prázdné buňky sousedící s každou z vašich proměnných hodnot (různé úrokové sazby) odpovídajícími výsledky. Viz snímek obrazovky:
   
6. Použijte požadovaný formát čísel na výsledky (Měna) podle vaší potřeby. Nyní je sloupcově orientovaná datová tabulka úspěšně vytvořena a vy můžete rychle procházet výsledky a vyhodnotit, jaké částky měsíčních splátek by pro vás byly při změně úrokové sazby dostupné. Viz snímek obrazovky:
   

Řádkově orientovaná datová tabulka

Vytvoření řádkově orientované datové tabulky v Excelu zahrnuje uspořádání dat tak, aby hodnoty proměnných byly uvedeny v řádku, nikoli ve sloupci. Vezmeme-li výše uvedený příklad jako referenci, pojďme pokračovat kroky k dokončení vytvoření řádkově orientované datové tabulky v Excelu.

Krok 1: Seznam úrokových sazeb v řadě

Umístěte hodnoty proměnných (úrokové sazby) do řádku a ujistěte se, že vlevo je alespoň jeden prázdný sloupec pro vzorec a jeden prázdný řádek níže pro výsledky, viz snímek obrazovky:

Krok 2: Vytvořte datovou tabulku

1. Do buňky A9 zadejte tento vzorec: = B4.
   
2. Vyberte rozsah, který zahrnuje váš vzorec, seznam úrokových sazeb a sousední buňky pro výsledky (např. vyberte A8 až I9). Poté klikněte Data > Analýza What-If > Tabulka dat.
3. v Tabulka dat v dialogovém okně klepněte na Vstupní buňka řádku (protože pro vstupní hodnoty používáme řádek) a vyberte buňku proměnné, na kterou váš vzorec odkazuje. V tomto příkladu vybereme B1, který obsahuje úrokovou sazbu. Konečně klikni OK tlačítko, viz screenshot:
   
4. Použijte požadovaný formát čísel na výsledky (Měna) podle vaší potřeby. Nyní je vytvořena řádkově orientovaná datová tabulka, viz snímek obrazovky:
   

Více vzorců pro tabulku dat s jednou proměnnou

Vytvoření tabulky dat s jednou proměnnou s více vzorci v Excelu vám umožní vidět, jak změna jednoho vstupu ovlivní několik různých vzorců najednou. Co když ve výše uvedeném příkladu chcete vidět změnu úrokových sazeb jak u splátek, tak u celkových úroků? Postup nastavení:

Krok 1: Přidejte nový vzorec pro výpočet celkového úroku

Do buňky B5 zadejte následující vzorec pro výpočet celkového úroku:

=B4*B2*12-B3

Krok 2: Uspořádejte zdrojová data datové tabulky

Ve sloupci uveďte různé úrokové sazby, které chcete testovat, a ponechte alespoň dva prázdné sloupce vpravo pro výsledky. Viz snímek obrazovky:

Krok 3: Vytvořte datovou tabulku:

1. Do buňky E2 zadejte tento vzorec: = B4 vytvořit odkaz na výpočet splátky v původních datech.
2. Do buňky F2 zadejte tento vzorec: = B5 vytvořit odkaz na celkový zájem o původní data.
   
3. Vyberte rozsah, který obsahuje vaše vzorce, seznam úrokových sazeb a sousední buňky pro výsledek (např. vyberte D2 až F10). Poté klikněte Data > Analýza What-If > Tabulka dat.
4. v Tabulka dat v dialogovém okně klepněte na Vstupní buňka sloupce box (protože pro vstupní hodnoty používáme sloupec) a vyberte buňku proměnné, na kterou se ve vašem vzorci odkazuje. V tomto příkladu vybereme B1, který obsahuje úrokovou sazbu. Konečně klikni OK tlačítko, viz screenshot:
   
5. Použijte požadovaný formát čísel na výsledky (Měna) podle vaší potřeby. A můžete vidět výsledky pro každý vzorec na základě různých hodnot proměnných.
   

Vytvořte datovou tabulku se dvěma proměnnými

Tabulka dat dvou proměnných v aplikaci Excel zobrazuje vliv různých kombinací dvou sad hodnot proměnných na výsledek vzorce a ukazuje, jak změny dvou vstupů vzorce současně ovlivňují jeho výsledek.

Zde jsem nakreslil jednoduchý náčrt, který vám pomůže lépe porozumět vzhledu a struktuře datové tabulky se dvěma proměnnými.

Na základě příkladu vytvoření tabulky dat s jednou proměnnou se nyní pojďme naučit, jak vytvořit tabulku dat se dvěma proměnnými v aplikaci Excel.

V níže uvedeném souboru dat máme úrokovou sazbu, dobu půjčky a výši půjčky a vypočítali jsme měsíční splátku pomocí tohoto vzorce: =-PMT($B$1/12, $B$2*12, $B$3) také. Zde se zaměříme na dvě klíčové proměnné z našich dat: úrokovou sazbu a výši úvěru, abychom sledovali, jak změny obou těchto faktorů současně ovlivňují výši splátek.

Krok 1: Nastavte dvě měnící se proměnné

1. Ve sloupci uveďte různé úrokové sazby, které chcete testovat. viz snímek obrazovky:
   
2. V řádku zadejte různé hodnoty výše úvěru těsně nad hodnoty sloupce (začínaje jednou buňkou napravo od buňky vzorce), viz snímek obrazovky:
   

Krok 2: Vytvořte datovou tabulku

1. Umístěte vzorec na průsečík řádku a sloupce, kde jsou uvedeny hodnoty proměnných. V tomto případě zadám tento vzorec: = B4 do buňky E2. Viz snímek obrazovky:
   
2. Vyberte rozsah, který zahrnuje částky vaší půjčky, úrokové sazby, buňku vzorce a buňky, kde se zobrazí výsledky.
   
3. Potom klepněte na tlačítko Data > Analýza What-If > Tabulka dat. V dialogovém okně Tabulka dat:
   • v Vstupní buňka řádku vyberte odkaz na buňku na vstupní buňku pro hodnoty proměnných v řádku (v tomto příkladu B3 obsahuje částku půjčky).
   • v Vstup sloupce cell vyberte odkaz na buňku vstupní buňky pro hodnoty proměnné ve sloupci (B1 obsahuje úrokovou sazbu).
   • A pak klikněte OK Tlačítko.
     
4. Nyní Excel vyplní datovou tabulku s výsledky pro každou kombinaci výše úvěru a úrokové sazby. Poskytuje přímý pohled na to, jak různé kombinace výše úvěru a úrokových sazeb ovlivňují měsíční splátku, což z něj činí cenný nástroj pro finanční plánování a analýzy.
   
5. Nakonec byste měli na výsledky použít požadovaný formát čísel (Měna) podle vaší potřeby.
   

Klíčové body pomocí datových tabulek

• Nově vytvořená datová tabulka musí být ve stejném listu jako původní data.
• Výstup tabulky dat závisí na buňce vzorce ve zdrojové sadě dat a jakékoli změny této buňky vzorce výstup automaticky aktualizují.
• Jakmile byly hodnoty vypočteny pomocí datové tabulky, nelze je vrátit zpět Ctrl + Z. Můžete však ručně vybrat všechny hodnoty a odstranit je.
• Datová tabulka generuje maticové vzorce, takže jednotlivé buňky v tabulce nelze měnit ani mazat.

Další operace pro použití datových tabulek

Jakmile vytvoříte datovou tabulku, budete možná potřebovat další operace pro její efektivní správu, včetně odstranění datové tabulky, úpravy jejích výsledků a provádění ručních přepočtů.

Odstraňte datovou tabulku

Protože se výsledky datové tabulky počítají pomocí maticového vzorce, nelze z datové tabulky odstranit jednotlivé hodnoty. Smazat však můžete pouze celou datovou tabulku.

Vyberte všechny buňky datové tabulky nebo pouze buňky s výsledky a stiskněte Vymazat klíč na klávesnici.

Upravit výsledek tabulky dat

Ve skutečnosti není možné přímo upravovat jednotlivé buňky v datové tabulce, protože obsahují maticové vzorce, které Excel generuje automaticky.

Chcete-li provést změny, obvykle budete muset odstranit existující datovou tabulku a poté vytvořit novou s požadovanými úpravami. To zahrnuje úpravu základního vzorce nebo vstupních hodnot a následné nastavení tabulky dat znovu tak, aby odrážela tyto změny.

Přepočítat datovou tabulku ručně

Za normálních okolností Excel při každé změně přepočítá všechny vzorce ve všech otevřených sešitech. Pokud velká datová tabulka obsahující četné hodnoty proměnných a složité vzorce způsobuje zpomalení sešitu aplikace Excel.

Chcete-li zabránit tomu, aby aplikace Excel automaticky prováděla výpočty pro všechny tabulky dat při každé změně v sešitu, můžete přepnout režim výpočtu z automatického na ruční. Postupujte prosím následovně:

Přejít na Vzorce a klepněte na tlačítko Možnosti výpočtu > Automaticky Kromě datových tabulek, viz screenshot:

Jakmile toto nastavení upravíte a přepočítáte celý sešit, Excel již nebude automaticky aktualizovat výpočty v tabulkách dat.

Pokud potřebujete aktualizovat datovou tabulku ručně, stačí vybrat buňky, kde jsou zobrazeny výsledky (buňky obsahující vzorce TABLE()), a poté stisknout F9 klíč.

Pokud budete postupovat podle kroků uvedených v této příručce a budete mít na paměti klíčové body, můžete efektivně využívat datové tabulky pro potřeby analýzy dat. Pokud máte zájem prozkoumat další tipy a triky pro Excel, naše webové stránky nabízejí tisíce výukových programů, pro přístup k nim prosím klikněte sem. Děkujeme za přečtení a těšíme se, že vám v budoucnu poskytneme další užitečné informace!