Toto je první článek z pětidílné série o analýze dat v Excelu. V této části vám ukážu, jak vytvořit a používat tabulku dat s jednou proměnnou v aplikaci Excel.
Další články ze série Data Analysis:
- Tabulka dvou proměnných dat v Excelu.
- Správce scénářů v Excelu.
- Hledání cílů v Excelu.
- Řešitel aplikace Excel.
Sledujte video - Tabulka proměnných dat v aplikaci Excel
Jedna tabulka proměnných dat v aplikaci Excel je nejvhodnější v situacích, kdy chcete vidět, jak se konečný výsledek změní, když změníte jednu ze vstupních proměnných. Pokud chcete změnit dvě proměnné, použijte tabulku dvou proměnných nebo Správce scénářů.
Kdy použít jednu tabulku proměnných dat v aplikaci Excel
Předpokládejme, že máte sadu dat, jak je uvedeno níže:
Ve výše uvedeném souboru dat máme částku úvěru, úrokovou sazbu a počet měsíčních plateb.
Hodnota měsíční platby se vypočítá na základě těchto tří proměnných (je červená, protože jde o odliv peněz). Pro výpočet měsíční platby se používá následující vzorec:
= PMT (B2/12, B3, B1)
Nyní možná budete chtít provést analýzu, abyste zjistili, jaký počet měsíčních plateb vyhovuje vašemu stavu a jak by se podle nich měsíční platby lišily. Můžete například chtít zachovat dobu splatnosti půjčky (počet měsíčních plateb) tak, aby vaše částka měsíční platby nepřekročila 1 000 $.
Rychlý způsob je změnit data v této tabulce a vzorec by se automaticky aktualizoval. Pokud však chcete vyzkoušet, zda pro mnoho různých Počet měsíčních plateb, pak by byla jedna tabulka proměnných dat v aplikaci Excel.
Nastavení jedné tabulky proměnných dat v aplikaci Excel
Zde jsou kroky k nastavení tabulky proměnných One v aplikaci Excel:
- Ve sloupci zadejte všechny různé hodnoty počtu měsíčních plateb, které chcete testovat. V tomto případě testujeme pro 72, 84, 96… 240.
- Do buňky E1 zadejte = B4, což je jeden řádek nad hodnotami v sousedním sloupci vpravo. Jedná se o konstrukci, kterou je třeba dodržovat při práci s jednou tabulkou proměnných dat v aplikaci Excel. Také se ujistěte, že hodnota v buňce E1 závisí na počtu měsíčních plateb. Pokud ručně zadáte hodnotu do buňky E1, nebude to fungovat.
V tomto případě buňka E1 odkazuje na buňku B4, která má hodnotu vypočítanou pomocí vzorce, který používá buňky B1, B2 a B3.
- Nyní jsou data nastavena pro použití v tabulce One Variable data.
- Vyberte data (D1: E16). Přejděte na kartu Data -> Datové nástroje -> Co když analýza -> Tabulka dat.
- V dialogovém okně Tabulka dat se podívejte na buňku B3 v poli Vstupní buňka sloupce. Protože používáme datovou tabulku s jednou proměnnou, ponechte vstupní buňku Řádek prázdnou. Také jsme zvolili možnost Vstupní buňka sloupce, protože data jsou strukturována ve sloupci.
- Klikněte na OK. Jakmile kliknete na OK, automaticky vyplní buňky sousedící s proměnnými datovými body (počet měsíčních plateb).
Pokud byste například chtěli vypočítat, jaký počet měsíčních plateb byste měli použít, aby byla měsíční platba nižší než 1 000 $. Můžete snadno vybrat 144 měsíců (12 let).
Poznámka:
- Jakmile vypočítáte hodnoty pomocí datové tabulky, nelze ji vrátit zpět pomocí Control + Z. Můžete však ručně vybrat všechny hodnoty a odstranit je.
- Nemůžete odstranit/upravit žádnou buňku v celé sadě vypočítaných hodnot. Protože se jedná o pole, budete muset odstranit všechny hodnoty.
- Stejný výpočet můžete provést uspořádáním dat horizontálně. Jediným rozdílem by bylo, že v dialogovém okně tabulky dat budete muset použít buňku pro zadávání řádků (místo buňky pro zadávání sloupců).
