Toto je druhý článek z pětidílné série o analýze dat v Excelu. V této části vám ukážu, jak používat tabulku dvou proměnných dat v aplikaci Excel.
Další články z této série:
- Jedna tabulka 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 dat se dvěma proměnnými v aplikaci Excel
Tabulka dat se dvěma proměnnými je nejvhodnější v situacích, kdy chcete vidět, jak se konečný výsledek mění, když se současně mění dvě ze vstupních proměnných (oproti jedné tabulce proměnných dat, kde se mění pouze jedna ze vstupních proměnných).
Pokud chcete analyzovat data, když se změní více než 2 proměnné, je správným řešením správce scénářů.
Kdy použít tabulku dvou 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. Na základě těchto 3 vstupních proměnných se vypočítá měsíční platba (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á by měla být ideální kombinace výše půjčky a počtu měsíčních plateb, aby vyhovovala vašim požadavkům. Můžete například chtít ponechat měsíční splátku na 500 $ nebo méně a analyzovat, jaká kombinace kombinací výše půjčky a držby vám to může poskytnout.
V takové situaci by měla být použita tabulka dvou proměnných dat.
Nastavení tabulky dvou proměnných dat v Excelu
Zde jsou kroky k nastavení tabulky dvou proměnných dat v aplikaci Excel:
- Ve sloupci zadejte všechny různé hodnoty, které chcete testovat pro počet měsíčních plateb. V tomto případě testujeme pro 72, 84, 96… 240. Současně mějte různé hodnoty částky půjčky v řádku těsně nad hodnotami sloupců (počínaje jednou buňkou vpravo), jak je znázorněno na obrázku níže.
- Do buňky D1 zadejte = B4, což je jeden řádek nad hodnotami ve sloupci. Toto je konstrukce, kterou je třeba dodržovat při práci s tabulkou dvou proměnných dat. Také se ujistěte, že hodnota v buňce D1 závisí na obou proměnných (počet měsíčních plateb a výše půjčky). Pokud ručně zadáte hodnotu do buňky D1, nebude to fungovat.
V tomto případě buňka D1 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 k použití pro výpočet tabulky s dvěma proměnnými daty.
- Vyberte data (D1: J16). Přejděte na kartu Data -> Datové nástroje -> Co když analýza -> Tabulka dat
- V dialogovém okně Tabulka dat použijte následující odkazy:
- Vstupní buňka řádku: $ B $ 1
- Vstupní buňka sloupce: $ B $ 3
- Klikněte na OK. Jakmile kliknete na OK, okamžitě vyplní všechny prázdné buňky ve vybraném rozsahu dat. Rychle vám poskytne pohled na měsíční platby pro různé kombinace výše úvěru a počtu měsíčních plateb.
Pokud například chcete identifikovat kombinace částky půjčky a počtu měsíčních plateb, které by vedly k měsíční platbě nižší než 500 USD za měsíc, můžete jednoduše použít tuto metodu 2 proměnných datových tabulek.
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.
