Datová tabulka v aplikaci Microsoft Excel

Často je nutné vypočítat konečný výsledek pro různé kombinace vstupních dat. Uživatel tak bude schopen vyhodnotit všechny možné možnosti akce, vybrat ty, jejichž výsledky s interakcí uspokojují, a nakonec zvolit nejoptimálnější volbu. V aplikaci Excel pro provedení tohoto úkolu existuje speciální nástroj - "Tabulka údajů" ( "Tabulka nahrazení" ). Zjistěte, jak ji použít k provedení výše uvedených scénářů.

Čtěte také: Výběr parametrů v aplikaci Excel

Použití tabulky dat

Nástroj "Data Table" je určen pro výpočet výsledku pro různé varianty jedné nebo dvou definovaných proměnných. Po výpočtu se objeví všechny možné varianty ve formě tabulky, která se nazývá matice faktorové analýzy. "Datová tabulka" odkazuje na skupinu nástrojů pro analýzu "Co když," která se nachází na pásu karet na kartě "Data" v bloku "Práce s daty" . Před programem Excel 2007 byl tento nástroj nazýván "tabulkou nahrazení" , která ještě přesněji odráží jeho podstatu než současný název.

Náhradní tabulka může být použita v mnoha případech. Například typickou volbou je, když potřebujete vypočítat částku měsíční půjčky za různé změny v úvěrovém období a výši půjčky, nebo kreditní období a úrokovou sazbu. Tento nástroj lze také použít při analýze modelů investičních projektů.

Ale měli byste také vědět, že nadměrné používání tohoto nástroje může vést k brzdění systému, protože data jsou nepřerušovaně přepočítávána. Doporučuje se proto, aby v malých tabulkových polích pro řešení podobných úkolů tento nástroj nepoužívejte, ale použijte kopírování vzorců pomocí značky pro vyplnění.

Odůvodněné použití "tabulky dat" je pouze v rozsáhlých tabulkových rozmezích, při kopírování vzorců může trvat spousta času a během procedury se zvyšuje pravděpodobnost chyb. V tomto případě se však doporučuje zakázat automatické přepočítávání vzorců v rozsahu tabulky výměny, aby se zamezilo zbytečnému zatížení systému.

Hlavním rozdílem mezi různými způsoby použití tabulky údajů je počet proměnných použitých při výpočtu: jedna proměnná nebo dvě.

Metoda 1: Použijte nástroj s jednou proměnnou

Okamžitě zvažte možnost, když se použije tabulka s jednou proměnnou hodnotou. Vezměme si typický příklad půjčky.

V současné době nám nabízíme následující kreditní podmínky:

• Doba splatnosti - 3 roky (36 měsíců);
• Výše úvěru je 900 000 rublů;
• Úroková sazba činí 12,5% ročně.

Platby se uskutečňují na konci platebního období (měsíce) v rámci anuitní schématu, tj. Rovnoměrně. Současně na začátku celého období půjčky je významnou částí plateb úrokové platby, ale jak se subjekt snižuje, úroky se snižují a navýší se splátka samotného orgánu. Celková výše platby, jak již bylo uvedeno výše, zůstává nezměněna.

Je třeba vypočítat, jaká částka bude měsíční splátka, včetně splácení úvěrového orgánu a úrokových plateb. Chcete-li to provést, existuje operátor PLT v aplikaci Excel.

PLT patří do skupiny finančních funkcí a jeho úkolem je vypočítat měsíční splátkovou platbu typu anuity na základě částky úvěrového orgánu, doby trvání úvěru a úrokové sazby. Syntaxe této funkce je uvedena v této podobě

=ПЛТ(ставка;кпер;пс;бс;тип)

"Rate" je argument, který určuje úrokovou sazbu plateb kreditu. Indikátor je nastaven na dobu. Máme výplatní dobu jednoho měsíce. Proto by měla být roční míra 12,5% rozdělena na počet měsíců v roce, to znamená 12.

"Kper" - argument, který určuje počet období pro celou dobu půjčky. V našem příkladu je toto období jeden měsíc a doba připsání je 3 roky nebo 36 měsíců. Počet období bude tedy počátkem 36.

"PS" je argument, který určuje současnou hodnotu úvěru, tj. Velikost úvěrového orgánu v okamžiku jeho vydání. V našem případě je toto číslo 900 000 rublů.

"BS" je argument, který udává výši částky úvěru v okamžiku jeho úplného zaplacení. Samozřejmě tento ukazatel bude nulový. Tento argument není požadovaným parametrem. Pokud to vynecháte, předpokládá se, že se rovná číslu "0".

"Typ" je také volitelný argument. Oznamuje, kdy bude platba provedena: na začátku období (parametr - "1" ) nebo na konci období (parametr - "0" ). Jak si pamatujeme, zaplatíme na konci kalendářního měsíce, to znamená, že hodnota tohoto argumentu bude rovna "0" . Ale vzhledem k tomu, že tento indikátor není povinný, a pokud jej nepoužijete, hodnota je označena písmenem "0" , pak ve specifikovaném příkladu nemůže být obecně používán.

1. Takže začneme výpočet. Vyberte buňku na listu, kde se zobrazí vypočtená hodnota. Klepněte na tlačítko "Vložit funkci" .
2. Spustí se Průvodce funkcí . Provedeme přechod na kategorii "Finanční" , ze seznamu vybereme název "PLT" a klikneme na tlačítko "OK" .
3. Poté je aktivováno okno s argumenty výše uvedené funkce.

   Kurzor umístíme do pole "Sázka" a poté klikneme na buňku na listu s hodnotou roční úrokové sazby. Jak vidíte, pole okamžitě zobrazí souřadnice. Ale jak si pamatujeme, potřebujeme měsíční sazbu, a proto výsledek rozdělíme o 12 ( / 12 ).

   V poli "Kper" stejným způsobem zadáváme souřadnice buněk termínu půjčky. V tomto případě nemusíte nic dělat.

   V poli "Ps" musíte zadat souřadnice buňky, která obsahuje částku půjčky. Děláme to. Označte znak "-" před zobrazenými souřadnicemi. Faktem je, že funkce PLT implicitně dává konečný výsledek s negativním znamením, správně s ohledem na měsíční úvěr jako ztrátu. Kvůli jasnosti používání tabulky údajů však musí být toto číslo kladné. Proto před jedním z argumentů funkce předáme znaménko mínus . Jak víte, násobení "minus" o "minus" nakonec dává "plus" .

   V polích "Bc" a "Typ" neposkytujeme žádné údaje vůbec. Klikněte na tlačítko "OK" .

4. Poté operátor vypočítá a vygeneruje výsledek celkové měsíční platby - 30108,26 rublů na předem určenou buňku. Problém však spočívá v tom, že dlužník je schopen zaplatit maximálně 29 000 rublů měsíčně, to znamená, že by měl buď najít banku nabízející podmínky s nižší úrokovou sazbou, nebo snížit úvěrovou instituci, nebo zvýšit dobu půjčky. Tabulka substitucí nám pomůže vypočítat různé možnosti akce.
5. Nejprve používáme tabulku substitucí s jednou proměnnou. Podívejme se, jak se hodnota povinné měsíční platby mění s různými změnami roční sazby, a to od 9,5% ročně až po 12,5% ročně v přírůstcích 0,5% . Všechny ostatní podmínky zůstávají nezměněny. Nakreslíme tabulkový rozsah, jehož názvy sloupců budou odpovídat různým změnám v úrokové sazbě. Současně zůstává řádek "Měsíční platby" takový, jaký je. První buňka musí obsahovat vzorec, který jsme vypočítali dříve. Další informace můžete přidat řádky "Celková částka úvěru" a "Celková výše úroků" . Sloupec, ve kterém je výpočet umístěn, se provádí bez názvu.
6. Dále vypočítáme celkovou výši úvěru za současných podmínek. Chcete-li to provést, vyberte první buňku řádek "Celková výše úvěru" a vynásobte obsah článků "Měsíční platba" a "Doba úvěru" . Poté klikněte na tlačítko Enter .
7. Pro výpočet celkové výše úroků za současných podmínek odečteme částku úvěrového subjektu z celkové částky úvěru podobným způsobem. Chcete-li zobrazit výsledek na obrazovce, klikněte na tlačítko Enter . Získáme tak částku, kterou zaplatíme při splácení úvěru.
8. Nyní je čas použít nástroj Data Table . Vyberte celé pole tabulky s výjimkou názvů řádků. Poté přejděte na kartu "Data" . Klepneme na tlačítko na pásu "Analýza toho, co kdyby" , která se nachází ve skupině nástrojů "Práce s daty" (v aplikaci Excel 2016, skupina nástrojů "Předpověď" ). Pak se otevře malé menu. V tom vyberte položku "Datová tabulka ..." .
9. Otevře se malé okno, které se nazývá "Datový stůl" . Jak vidíte, má dvě pole. Protože pracujeme s jednou proměnnou, potřebujeme pouze jednu z nich. Protože změníme proměnnou podle sloupců, použijeme pole "Nahradit hodnoty podle sloupců" . Nastavte kurzor a klepněte na buňku v datové sadě zdrojového souboru, která obsahuje aktuální procentuální hodnotu. Po zobrazení souřadnic buňky v poli klepněte na tlačítko "OK" .
10. Nástroj vypočítá a vyplňuje celý rozsah tabulky hodnotami, které odpovídají různým možnostem úrokové sazby. Pokud umístíte kurzor do libovolného prvku dané oblasti tabulky, uvidíte, že vzorec vzorce nevykazuje obvyklý vzorec pro výpočet platby, ale speciální vzorec pro nerozpustné pole. To znamená, že nyní nemůžete měnit hodnoty v jednotlivých buňkách. Výsledky výpočtu můžete smazat pouze společně a nikoli samostatně.

Kromě toho můžete vidět, že hodnota měsíční platby ve výši 12,5% ročně, získaná v důsledku použití tabulky permutací, odpovídá hodnotě za stejnou částku úroku, kterou jsme obdrželi při použití funkce PLT . To opět dokazuje správnost výpočtu.

Pokud analyzujeme toto tabulkové pole, mělo by se říci, že podle našeho názoru je pro nás míra měsíční platby (méně než 29 000 rublů) přijatelná pouze ve výši 9,5% ročně.

Lekce: Výpočet anuitní platby v programu Excel

Metoda 2: použijte nástroj s dvěma proměnnými

Samozřejmě je velmi těžké, pokud vůbec, najít banky, které vydávají úvěry na 9,5% ročně. Takže se podívejme, jaké možnosti existují pro investování do přijatelné úrovně měsíční platby pro různé kombinace jiných proměnných: velikost úvěru a doba půjčky. Současně udržujeme úrokovou sazbu beze změny (12,5%). Při řešení tohoto úkolu nám pomůže nástroj "Data Table" pomocí dvou proměnných.

1. Kreslení nového tabulkového pole. Nyní se v názvech sloupců uvádí doba úvěru (od 2 do 6 let v měsících v přírůstcích jednoho roku) a v řádcích - částka půjčky (od 850000 do 950000 rublů s krokem 10 000 rublů). Povinnou podmínkou je, že buňka, ve které se nachází výpočetní vzorec (v našem případě PLT ), je umístěna na hranici názvů řádků a sloupců. Bez této podmínky nástroj nebude fungovat při použití dvou proměnných.
2. Poté vyberte celý získaný rozsah tabulky včetně názvů sloupců, řádků a buňky pomocí vzorce PLT . Přejděte na kartu "Data" . Stejně jako v předchozím případě klikneme na tlačítko "Analyzovat, co kdyby" , ve skupině nástrojů "Práce s daty" . V otevřeném seznamu vyberte položku "Datová tabulka ..." .
3. Okno nástroje "Data Table" je spuštěno. V tomto případě potřebujeme obě pole. V poli "Nahradit hodnoty podle sloupců v" označujeme souřadnice primárních dat v buňce obsahující dobu úvěru. V poli "Nahradit hodnoty podle řádků v" udáváme adresu buňky počátečních parametrů obsahujících hodnotu těla úvěru. Po zadání všech dat. Klikněte na tlačítko "OK" .
4. Program vypočítá a vyplňuje rozsah tabulky s daty. Na průsečíku řádků a sloupců nyní můžete přesně vidět, jaká bude měsíční platba, s odpovídající hodnotou ročního úroku a stanoveným termínem úvěru.
5. Jak vidíte, existuje mnoho hodnot. K vyřešení dalších problémů může být ještě více. Chcete-li proto, aby výsledky výstupů byly viditelné a okamžitě zjistěte, které hodnoty nesplňují daný stav, můžete použít vizualizační nástroje. V našem případě to bude podmíněné formátování. Vyberte všechny hodnoty v rozsahu tabulky, s výjimkou záhlaví řádků a sloupců.
6. Přejděte na kartu "Domov" a klikněte na ikonu "Podmíněné formátování" . Je umístěn v krabici nástrojů "Styly" na pásu karet. V rozevírací nabídce vyberte "Pravidla výběru buňky" . V doplňkovém seznamu klikněte na položku "Méně ..." .
7. Poté se otevře okno podmíněného formátování. V levém poli zadejte hodnotu, která je menší, než které buňky budou přiděleny. Jak si připomínáme, jsme spokojeni s podmínkou, podle které bude měsíční platba v rámci půjčky nižší než 29 000 rublů. Zadejte toto číslo. V pravém poli můžete vybrat zvýrazněnou barvu, ačkoli ji můžete nechat ve výchozím nastavení. Po zadání všech požadovaných nastavení klikněte na tlačítko "OK" .
8. Poté budou všechny buňky, jejichž hodnoty odpovídají výše uvedené podmínce, zvýrazněny barvou.

Při analýze tabulkového pole můžete vyvodit některé závěry. Jak vidíme, s existujícím úvěrovým termínem (36 měsíců), abychom investovali do výše uvedené výše měsíční platby, musíme získat úvěr nepřesahující 860000,00 rublů, což je o 40 000 méně, než bylo původně plánováno.

Pokud máme stále v úmyslu uzavřít úvěr ve výši 900 000 rublů, měla by být doba připsání 4 roky (48 měsíců). Pouze v tomto případě měsíční platba nepřekročí stanovený limit 29 000 rublů.

Použitím tohoto tabulkového pole a analýzou výhod a nevýhod jednotlivých možností může dlužník konkrétně rozhodnout o podmínkách úvěru a zvolit nejvhodnější variantu ze všech možných možností.

Samozřejmě, vyhledávací tabulka může být použita nejen pro výpočet možností půjčky, ale také pro řešení mnoha dalších problémů.

Lekce: Podmíněné formátování v aplikaci Excel

Obecně je třeba poznamenat, že vyhledávací tabulka je velmi užitečný a relativně jednoduchý nástroj pro určení výsledku pro různé kombinace proměnných. Aplikuje-li současně podmíněné formátování, navíc můžete zobrazit získané informace.

• ← Otevření tabulek ODS v aplikaci Microsoft Excel
• SQL dotazy v aplikaci Microsoft Excel →