Tabuľka údajov v programe Microsoft Excel

Často je potrebné vypočítať konečný výsledok pre rôzne kombinácie vstupných údajov. Takto bude používateľ schopný vyhodnotiť všetky možné možnosti akcií, vybrať tie, ktorých výsledky interakcie to uspokoja a nakoniec zvoliť najoptimálnejšiu možnosť. V programe Excel je na vykonanie tejto úlohy špeciálny nástroj - "Tabuľka údajov" ( "Tabuľka zámeny" ). Dozvieme sa, ako ju použiť na vykonanie vyššie uvedených scenárov.

Prečítajte si tiež: Výber parametrov v programe Excel

Použitie tabuľky údajov

Nástroj "Tabuľka údajov" je navrhnutý tak, aby vypočítal výsledok pre rôzne variácie jednej alebo dvoch definovaných premenných. Po výpočte sa všetky možné varianty objavia vo forme tabuľky, ktorá sa nazýva matica faktorovej analýzy. "Tabuľka údajov" označuje analytickú skupinu "Čo ak" , ktorá sa nachádza na karte na karte "Data" v bloku "Práca s údajmi" . Pred programom Excel 2007 bol tento nástroj nazvaný "Náhradná tabuľka" , ktorá ešte presnejšie odrážala jeho podstatu ako súčasný názov.

Nahradzujúca tabuľka môže byť použitá v mnohých prípadoch. Typickou možnosťou je napríklad, ak potrebujete vypočítať výšku mesačnej úverovej platby za rôzne zmeny počas obdobia pripísania a sumu úveru alebo kreditného obdobia a úrokovú sadzbu. Tiež tento nástroj možno použiť pri analýze modelov investičných projektov.

Ale mali by ste tiež vedieť, že nadmerné používanie tohto nástroja môže viesť k brzdeniu systému, pretože údaje sa nepretržite prepočítavajú. Preto sa odporúča, aby v malých tabuľkových poliach, na vyriešenie podobných úloh, tento nástroj nepoužívajte, ale použite kopírovanie vzorcov pomocou značky naplnenia.

Odôvodnené použitie "tabuľky údajov" je len v rozsiahlych tabuľkových rozmedziach, keď kopírovanie vzorcov môže trvať veľa času a počas postupu zvyšuje pravdepodobnosť vzniku chýb. V tomto prípade sa však odporúča vypnúť automatické prepočítanie vzorcov v rozsahu tabuľky nahradenia, aby sa predišlo zbytočnému zaťaženiu systému.

Hlavný rozdiel medzi rôznymi spôsobmi použitia tabuľky údajov je počet premenných zahrnutých do výpočtu: jedna premenná alebo dve.

Metóda 1: Použiť nástroj s jednou premennou

Okamžite zvážime možnosť, keď sa použije tabuľka údajov s jednou premennou hodnotou. Urobme najtypickejší príklad poskytovania úverov.

V súčasnosti nám ponúkame nasledujúce úverové podmienky:

• Výpožičný termín - 3 roky (36 mesiacov);
• Výška úveru je 900 000 rubľov;
• Úroková sadzba je 12,5% ročne.

Platby sa vyskytujú na konci platobného obdobia (mesiaca) v rámci systému dôchodkového zabezpečenia, tj v rovnakých podieloch. Súčasne na začiatku celého obdobia pôžičky je významnou časťou platieb úrokové platby, ale v dôsledku poklesu úrokovej sadzby sa úrokové platby znižujú a zvyšuje sa splátka samotného orgánu. Celková platba, ako už bolo spomenuté vyššie, zostáva nezmenená.

Je potrebné vypočítať, akú výšku mesačnej platby, ktorá zahŕňa splatenie úverového orgánu a výplaty úrokov, bude. Ak to chcete urobiť, v programe Excel existuje operátor PLT .

PLT patrí do skupiny finančných funkcií a jej úlohou je vypočítať mesačnú splátkovú platbu typu anuity založenú na výške úverového orgánu, termín úveru a úrokovú sadzbu. Syntax tejto funkcie je uvedený v tejto forme

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

"Sadzba" je argument, ktorý určuje úrokovú sadzbu úverových platieb. Indikátor je nastavený na určité obdobie. Máme výplatnú lehotu jedného mesiaca. Preto by sa ročná miera 12,5% mala rozdeliť na počet mesiacov za rok, to znamená 12.

"Kper" - argument, ktorý určuje počet období pre celé obdobie pôžičky. V našom príklade je toto obdobie jeden mesiac a kreditné obdobie je 3 roky alebo 36 mesiacov. Počet období bude teda na začiatku 36.

"PS" je argument, ktorý určuje súčasnú hodnotu úveru, to znamená, že veľkosť úverového orgánu v čase jeho vydania. V našom prípade je toto číslo 900 000 rubľov.

"BS" je argument, ktorý udáva výšku úverového orgánu v čase jeho úplného zaplatenia. Samozrejme, tento ukazovateľ bude nulový. Tento argument nie je požadovaným parametrom. Ak to vynecháte, predpokladá sa, že sa rovná číslu "0".

"Typ" je tiež voliteľný argument. Oznamuje, kedy bude platba vykonaná: na začiatku obdobia (parameter - "1" ) alebo na konci obdobia (parameter - "0" ). Ako si pamätáme, zaplatíme na konci kalendárneho mesiaca, to znamená, že hodnota tohto argumentu sa rovná "0" . Ale vzhľadom na to, že tento indikátor nie je povinný, a ak ho nepoužijete, hodnota sa označuje ako "0" , potom sa v špecifikovanom príklade všeobecne nedá použiť.

1. Takže začnime výpočet. Vyberte bunku na hárku, kde sa zobrazí vypočítaná hodnota. Klikneme na tlačidlo "Vložiť funkciu" .
2. Spustí sa Sprievodca funkciami . Uskutočníme prechod do kategórie "Finančné" , zvoľte v zozname názov "PLT" a kliknite na tlačidlo "OK" .
3. Potom sa aktivuje okno s argumentmi uvedenej funkcie.

   Kurzor sme umiestnili do poľa "Bet" a potom kliknite na bunku na hárku s hodnotou ročnej úrokovej miery. Ako vidíte, pole okamžite zobrazí súradnice. Ale ako si pamätáme, potrebujeme mesačnú sadzbu, a preto výsledok rozdelíme o 12 ( / 12 ).

   V poli "Kper" rovnakým spôsobom zadávame súradnice buniek termínu pôžičky. V tomto prípade nemusíte rozdeliť.

   V poli "Ps" musíte špecifikovať súradnice bunky obsahujúce množstvo úverového subjektu. Robíme to. Značku "-" umiestnite aj pred zobrazené súradnice. Faktom je, že funkcia PLT štandardne dáva konečný výsledok so záporným znamením, správne vzhľadom na mesačnú úverovú platbu ako stratu. Z dôvodu jasnosti v používaní tabuľky údajov však toto číslo musí byť pozitívne. Z tohto dôvodu pred jedným z argumentov funkcie zadáme znamienko mínus . Ako viete, násobenie "mínus" o "mínus" nakoniec prináša "plus" .

   V poliach "Bc" a "Typ" neposkytujeme žiadne údaje. Kliknite na tlačidlo "OK" .

4. Potom prevádzkovateľ vypočíta a vykáže výsledok celkovej mesačnej platby - 30108,26 rubľov na vopred určenú bunku. Ale problémom je, že dlžník je schopný zaplatiť maximálne 29 000 rubľov mesačne, to znamená, že by mal nájsť banku ponúkajúcu podmienky s nižšou úrokovou sadzbou, alebo znížiť úverový orgán, alebo zvýšiť dobu pôžičky. Tabuľka substitúcií nám pomôže vypočítať rôzne možnosti akcie.
5. Najprv používame tabuľku náhrad s jednou premennou. Pozrime sa, ako sa hodnota povinnej mesačnej platby mení s rôznymi zmenami ročnej sadzby, počnúc od 9,5% ročne až po 12,5% ročne s prírastkom 0,5% . Všetky ostatné podmienky zostávajú nezmenené. Nakreslili sme tabuľkový rozsah, ktorého názvy stĺpcov budú zodpovedať rôznym zmenám v úrokovej miere. Súčasne zostáva riadok "Mesačné platby" taký, ako je. Prvá bunka musí obsahovať vzorec, ktorý sme vypočítali predtým. Pre viac informácií môžete pridať riadky "Celková výška úveru" a "Celková výška úrokovej sadzby" . Stĺpec, v ktorom sa nachádza výpočet, sa vykonáva bez názvu.
6. Ďalej vypočítame celkovú výšku úveru za súčasných podmienok. Za týmto účelom vyberte prvú bunku v riadku "Celková čiastka úveru" a vynásobte obsah buniek "Mesačná platba" a "Úverové obdobie" . Potom kliknite na kláves Enter .
7. Ak chcete vypočítať celkovú sumu úroku za súčasných podmienok, odpočítavame výšku úveru z celkovej sumy úveru rovnakým spôsobom. Ak chcete zobraziť výsledok na obrazovke, kliknite na tlačidlo Enter . Získame teda sumu, ktorú zaplatíme pri splácaní úveru.
8. Teraz je čas použiť nástroj Data Table . Vyberte celé pole tabuľky okrem názvov riadkov. Potom prejdite na kartu "Dáta" . Klikneme na tlačidlo na karte "Analýza toho, čo ak" , ktorá sa nachádza v skupine nástrojov "Práca s údajmi" (v skupine nástrojov "Predpoveď" v programe Excel 2016). Potom sa otvorí malé menu. V ňom vyberte položku "Tabuľka údajov ..." .
9. Otvorí sa malé okno, ktoré sa nazýva "Tabuľka údajov" . Ako môžete vidieť, má dve polia. Keďže pracujeme s jednou premennou, potrebujeme len jednu z nich. Keďže premennú meníme podľa stĺpcov, použijeme pole "Nahradiť hodnoty podľa stĺpcov" . Tu nastavte kurzor a potom kliknite na bunku v zdrojovej dátovej množine, ktorá obsahuje aktuálnu percentuálnu hodnotu. Po zobrazení súradníc buniek v poli kliknite na tlačidlo "OK" .
10. Nástroj vypočíta a vyplní celý rozsah tabuľky s hodnotami, ktoré zodpovedajú rôznym možnostiam úrokovej sadzby. Ak umiestnite kurzor do ľubovoľného prvku daného priestoru tabuľky, môžete vidieť, že vzorec vzorca nevykazuje bežný vzorec na výpočet platby, ale špeciálny vzorec pre nerozpustné pole. To znamená, že teraz nemôžete meniť hodnoty v jednotlivých bunkách. Výsledky výpočtu môžete odstrániť iba spolu a nie samostatne.

Okrem toho môžete vidieť, že hodnota mesačnej platby vo výške 12,5% ročne, získaná v dôsledku použitia tabuľky permutácií, zodpovedá hodnote za rovnakú výšku úroku, ktorú sme dostali pri použití funkcie PLT . To opäť dokazuje správnosť výpočtu.

Pri analýze tohto tabuľkového poľa je potrebné povedať, že podľa môjho názoru je len pre mierku 9,5% ročne prijateľná úroveň mesačnej platby (nižšia ako 29 000 rubľov).

Lekcia: Výpočet výplaty anuity v programe Excel

Metóda 2: použite nástroj s dvoma premennými

Samozrejme, je veľmi ťažké, ak vôbec, nájsť banky, ktoré vydávajú úvery vo výške 9,5% ročne. Pozrime sa teda, aké možnosti existujú na investovanie do prijateľnej úrovne mesačnej platby pre rôzne kombinácie iných premenných: veľkosť pôžičky a doba pôžičky. Súčasne udržiavame úrokovú sadzbu nezmenenú (12,5%). Pri riešení tejto úlohy nám nástroj "Tabuľka údajov" pomôže pri použití dvoch premenných.

1. Výkres nového tabuľkového poľa. Teraz názvy stĺpcov uvádzajú úverové obdobie (od 2 do 6 rokov v mesiacoch v prírastkoch jedného roka) a v riadkoch - suma úverového orgánu (od 850000 do 950000 rubľov s krokom 10 000 rubľov). Povinnou podmienkou je, že bunka, v ktorej je umiestnený výpočtový vzorec (v našom prípade PLT ), sa nachádza na rozhraní názvov riadkov a stĺpcov. Bez tejto podmienky nástroj nefunguje pri použití dvoch premenných.
2. Potom vyberte celý získaný rozsah stola vrátane názvov stĺpcov, riadkov a buniek s PLT vzorkou . Prejdite na kartu "Dáta" . Rovnako ako v predchádzajúcom prípade klikneme na tlačidlo "Analyzovať, čo keď" v skupine nástrojov "Práca s údajmi" . V otvorenom zozname vyberte položku "Tabuľka údajov ..." .
3. Otvorí sa okno nástroja "Tabuľka údajov" . V tomto prípade potrebujeme obidve polia. V poli "Nahradiť hodnoty podľa stĺpcov v" označujeme súradnice bunky obsahujúcej úverové obdobie v primárnych údajoch. V poli "Náhrady hodnôt podľa riadkov v" uvádzame adresu bunky počiatočných parametrov obsahujúcich hodnotu pôžičky. Po zadaní všetkých údajov. Kliknite na tlačidlo "OK" .
4. Program vypočíta a vyplní rozsah tabuľky s údajmi. Na križovatke riadkov a stĺpcov je teraz možné presne sledovať, akú bude mesačná platba, s príslušnou hodnotou ročného úroku a stanoveným termínom úveru.
5. Ako vidíte, existuje veľa hodnôt. Na vyriešenie ďalších problémov môže byť ešte viac. Preto, aby sa výsledok výsledkov zviditeľnil a okamžite zistite, ktoré hodnoty nespĺňajú danú podmienku, môžete použiť vizualizačné nástroje. V našom prípade to bude podmienené formátovanie. Vyberte všetky hodnoty v tabuľkovom rozsahu okrem hlavičky riadkov a stĺpcov.
6. Presunutím na kartu "Domov" a kliknutím na ikonu "Podmienené formátovanie" . Je umiestnená v krabici s nástrojmi "Styly" na karte. V rozbaľovacej ponuke vyberte "Pravidlá výberu buniek" . V dodatočnom zozname kliknite na položku "Menej ..." .
7. Potom sa otvorí okno podmieneného formátovania. V ľavom poli zadajte hodnotu, ktorá je menšia ako počet buniek. Ako sme si spomenuli, sme spokojní s podmienkou, podľa ktorej mesačná platba v rámci úveru bude menej ako 29.000 rubľov. Zadáme toto číslo. V pravom poli môžete vybrať zvýrazňovaciu farbu, aj keď ju môžete predvolene ponechať. Po zadaní všetkých požadovaných nastavení kliknite na tlačidlo "OK" .
8. Potom budú všetky bunky, ktorých hodnoty zodpovedajú vyššie uvedenej podmienke, zvýraznené farebne.

Analýzou tabuľkového poľa môžete vyvodiť niektoré závery. Ako vidíme, s existujúcim úverovým termínom (36 mesiacov), aby sme investovali do uvedenej sumy mesačnej platby, musíme vziať pôžičku nepresahujúcu 860000,00 rubľov, čo je o 40 000 menej, než bolo pôvodne plánované.

Ak máme stále v úmysle uzavrieť pôžičku vo výške 900 000 rubľov, malo by byť kreditné obdobie 4 roky (48 mesiacov). Iba v tomto prípade mesačná platba neprekročí stanovený limit 29 000 rubľov.

Použitím tohto tabuľkového poľa a analýzou výhod a nevýhod jednotlivých možností môže dlžník konkrétne rozhodnúť o podmienkach úveru a vybrať si najvhodnejšiu možnosť zo všetkých možných možností.

Samozrejme, vyhľadávací stôl môže byť použitý nielen na výpočet úverových možností, ale aj na vyriešenie mnohých ďalších problémov.

Lekcia: Podmienené formátovanie v programe Excel

Vo všeobecnosti treba poznamenať, že vyhľadávacia tabuľka je veľmi užitočným a relatívne jednoduchým nástrojom na určenie výsledkov pre rôzne kombinácie premenných. Aplikáciou podmieneného formátovania môžete súčasne zobraziť získané informácie.

• ← Otváranie tabuliek ODS v programe Microsoft Excel
• SQL dotazov v programe Microsoft Excel →