Često morate izračunati krajnji rezultat za različite kombinacije ulaznih podataka. Tako će korisnik moći procijeniti sve moguće opcije djelovanja, odabrati one čiji rezultati interakcije ga zadovoljavaju i, na kraju, odabrati najoptimalniju opciju. U Excelu za obavljanje ovog zadatka postoji poseban alat - "Tabela podataka" (Tabela zamjene) Doznajmo kako ga koristiti za dovršavanje gornjih scenarija.
Pročitajte i: Odabir parametara u Excelu
Upotreba tablice podataka
Instrument "Tabela podataka" Namjera je izračunati rezultat za različite varijacije jedne ili dvije definirane varijable. Nakon izračuna, sve moguće opcije se pojavljuju u obliku tablice, koja se naziva matricom faktorske analize. "Tabela podataka" odnosi se na grupu alata „Šta ako analiza“, koji se nalazi na vrpci u kartici "Podaci" u bloku "Rad sa podacima". Prije Excela 2007, ovaj alat se zvao Tabela zamjene, što je još tačnije odražavalo njegovu suštinu od trenutnog naziva.
Tablica pretraživanja može se koristiti u mnogim slučajevima. Na primjer, tipična opcija je kada trebate izračunati iznos mjesečne isplate zajma za različite varijacije razdoblja kreditiranja i iznos kredita, odnosno razdoblje kreditiranja i kamatnu stopu. Takođe se ovaj alat može koristiti u analizi modela investicionih projekata.
Ali također biste trebali imati na umu da pretjerana upotreba ovog alata može dovesti do kočenja sustava, jer se podaci neprestano prepričavaju. Zbog toga se za rješavanje sličnih problema preporučuje u malim nizovima tablica ne koristiti ovaj alat, već koristiti kopiranje formula pomoću markera ispunjavanja.
Opravdana primjena "Tabele podataka" je samo u velikim rasponima tablica, kada kopiranje formula može potrajati puno vremena, a tijekom samog postupka vjerojatnost pogreške se povećava. Ali u ovom slučaju, preporučuje se onemogućiti automatsko ponovno izračunavanje formula u rasponu tablice zamjene kako bi se izbjeglo nepotrebno opterećenje sustava.
Glavna razlika između različitih upotreba tablice podataka je broj varijabli koje su uključene u proračun: jedna varijabla ili dvije.
1. metoda: koristite alat s jednom varijablom
Odmah pogledajmo opciju kada se tablica podataka koristi s jednom promjenjivom vrijednošću. Uzmite najtipičniji primjer pozajmljivanja.
Dakle, trenutno nam se nude sljedeći uvjeti zajma:
- Rok zajma - 3 godine (36 mjeseci);
- Iznos zajma - 900.000 rubalja;
- Kamatna stopa - 12,5% godišnje.
Isplate se vrše na kraju perioda plaćanja (mjeseca) prema shemi anuiteta, to jest u jednakim udjelima. Istodobno, na početku cijelog trajanja kredita značajan dio plaćanja predstavlja plaćanje kamate, ali kako se tijelo smanjuje, plaćanja kamata smanjuju se i iznos otplate samog tijela raste. Kao što je gore spomenuto, ukupna isplata ostaje nepromijenjena.
Potrebno je izračunati koliki će biti iznos mjesečne isplate, uključujući otplatu kreditnog tijela i isplate kamata. Excel za to ima operatera PMT.
PMT spada u skupinu financijskih funkcija i njegov je zadatak izračunati mjesečnu anuitetnu vrstu plaćanja na temelju iznosa kreditnog tijela, roka kredita i kamatne stope. Sintaksa ove funkcije predstavljena je kao
= PLT (stopa; nper; ps; bs; tip)
Ponuda - argument koji određuje kamatnu stopu plaćanja kredita. Indikator se postavlja za razdoblje. Naš rok isplate je jednak mjesecu. Stoga bi godišnju stopu od 12,5% trebalo podijeliti s brojem mjeseci u godini, odnosno 12.
"Nper" - argument koji određuje broj razdoblja za cijeli rok zajma. U našem primjeru razdoblje je mjesec dana, a rok zajma 3 godine ili 36 mjeseci. Stoga će broj razdoblja biti 36.
"PS" - argument koji određuje sadašnju vrijednost zajma, odnosno veličinu kreditnog tijela u trenutku njegovog izdavanja. U našem slučaju ta brojka je 900 000 rubalja.
"BS" - argument koji ukazuje na veličinu kreditnog tijela u vrijeme pune isplate. Naravno, ovaj pokazatelj će biti jednak nuli. Ovaj argument nije obavezan. Ako ga preskočite, pretpostavlja se da je jednak broju "0".
"Tip" - takođe fakultativni argument. On najavljuje kada će se tačno izvršiti plaćanje: na početku razdoblja (parametar - "1") ili na kraju razdoblja (parametar - "0") Kao što se sjećamo, naša je uplata izvršena na kraju kalendarskog mjeseca, odnosno vrijednost ove argumentacije bit će jednaka "0". Ali, s obzirom na činjenicu da ovaj pokazatelj nije obavezan, a prema zadanom, ako se ne koristi, vrijednost se podrazumijeva kao jednaka "0", onda se u navedenom primjeru može potpuno izostaviti.
- Dakle, nastavljamo na proračun. Odaberite ćeliju na listu na kojoj će se prikazati izračunata vrijednost. Kliknite na dugme "Umetanje funkcije".
- Počinje Čarobnjak za funkcije. Prelazimo na kategoriju "Finansijski", odaberite ime sa liste "PLT" i kliknite na dugme "OK".
- Nakon toga aktivira se prozor argumenata gornje funkcije.
Stavite kursor u polje Ponuda, nakon čega kliknemo na ćeliju na listu s vrijednošću godišnje kamatne stope. Kao što vidite, njegove se koordinate odmah prikazuju u polju. Ali, kao što pamtimo, potrebna nam je mjesečna stopa i zato rezultat podijelimo sa 12 (/12).
U polju "Nper" na isti način unosimo koordinate ćelija termina zajma. U ovom slučaju ne morate ništa dijeliti.
U polju Ps morate odrediti koordinate ćelije koja sadrži vrijednost tijela kredita. Mi to radimo. Ispred prikazanih koordinata stavljamo i znak "-". Činjenica je da je funkcija PMT prema zadanim postavkama daje krajnji rezultat s negativnim predznakom, s pravom uzimajući u obzir mjesečni gubitak plaćanja kredita. Ali zbog jasnoće primjene tablice podataka potreban nam je ovaj broj da bude pozitivan. Stoga stavljamo znak minus pred jednim od argumenata funkcije. Množenje je poznato minus na minus na kraju daje plus.
Na polja "Bs" i "Tip" podaci se uopće ne unose. Kliknite na dugme "OK".
- Nakon toga, operator izračunava i prikazuje rezultat ukupne mjesečne uplate u unaprijed određenoj ćeliji - 30108,26 rubalja. Ali problem je što je zajmoprimac u mogućnosti plaćati najviše 29.000 rubalja mjesečno, odnosno trebao bi naći ili banku koja nudi uvjete s nižom kamatnom stopom, ili smanjiti kreditno tijelo, ili povećati rok kredita. Tablica pretraživanja pomoći će nam da smislimo različite mogućnosti.
- Prvo upotrijebite tablicu za pretraživanje s jednom varijablom. Pogledajmo kako će se iznos obavezne mjesečne isplate mijenjati s različitim varijacijama godišnje stope, počev od 9,5% godišnje i kraj 12,5% godišnje u koracima 0,5%. Svi ostali uslovi ostaju nepromijenjeni. Nacrtamo raspon tablica, čiji će stupci odgovarati različitim varijacijama kamatne stope. Ovom linijom "Mesečne isplate" ostavi kakav jest. Njegova prva ćelija treba da sadrži formulu koju smo ranije izračunali. Za više informacija možete dodati redove "Ukupni iznos zajma" i "Ukupna kamata". Stupac u kojem se nalazi izračun vrši se bez zaglavlja.
- Zatim izračunavamo ukupni iznos zajma pod trenutnim uvjetima. Da biste to učinili, odaberite prvu ćeliju retka "Ukupni iznos zajma" i umnožiti sadržaj ćelija "Mesečna uplata" i "Pojam zajma". Nakon toga kliknite na gumb Unesite.
- Da bismo izračunali ukupni iznos kamate u trenutnim uvjetima, na isti način oduzimamo iznos kreditnog tijela od ukupnog iznosa kredita. Za prikaz rezultata na ekranu, kliknite na gumb Unesite. Tako dobivamo iznos koji preplaćujemo prilikom vraćanja kredita.
- Sada je vrijeme za primjenu alata "Tabela podataka". Biramo čitav niz tablica, osim imena redaka. Nakon toga, idite na karticu "Podaci". Kliknite gumb na vrpci „Šta ako analiza“koja se nalazi u grupi alata "Rad sa podacima" (u programu Excel 2016, grupa alata "Prognoza") Tada se otvara mali meni. U njemu biramo poziciju "Tabela podataka ...".
- Otvara se mali prozor koji je pozvan "Tabela podataka". Kao što vidite, ima dva polja. Budući da radimo s jednom varijablom, potrebna nam je samo jedna od njih. Budući da mijenjamo stupac varijable po stupcu, koristit ćemo polje Zamjena vrijednosti stupca u. Tamo postavite kursor, a zatim kliknite ćeliju u izvornom skupu podataka koji sadrži trenutni postotak. Nakon što su koordinate ćelija prikazane u polju, kliknite na gumb "OK".
- Alat izračunava i popunjava čitav tabelarni raspon vrijednostima koje odgovaraju različitim opcijama za kamatnu stopu. Ako pokazivač postavite na bilo koji element ovog područja tablice, možete vidjeti da traka formule ne prikazuje uobičajenu formulu za izračunavanje plaćanja, već posebnu formulu za neraskidivi niz. Odnosno, sada je nemoguće mijenjati vrijednosti u pojedinim ćelijama. Rezultate izračuna možete izbrisati samo sve zajedno, a ne odvojeno.
Pored toga, možete vidjeti da mjesečna uplata od 12,5% godišnje dobivena kao rezultat primjene pregledne tablice odgovara vrijednosti za isti iznos kamate koji smo primili primjenom funkcije PMT. Ovo još jednom dokazuje ispravnost izračuna.
Nakon analize ovog niza tablica, trebalo bi reći da, kao što vidite, samo po stopi od 9,5% godišnje dobivamo prihvatljiv nivo mjesečnog plaćanja (manji od 29.000 rubalja).
Lekcija: Izračun plaćanja anuiteta u Excelu
Druga metoda: koristite alat s dvije varijable
Naravno, trenutno je pronaći banke koje daju kredite po 9,5% godišnje vrlo je teško, ako ne i nemoguće. Stoga ćemo vidjeti koje mogućnosti postoje za ulaganje u prihvatljivu razinu mjesečnog plaćanja za razne kombinacije drugih varijabli: veličinu tijela kredita i rok kredita. U ovom slučaju kamatna stopa će ostati nepromijenjena (12,5%). U rješavanju ovog problema pomoći će nam alat. "Tabela podataka" koristeći dvije varijable.
- Nacrtamo novi niz tablica. Sada će u nazivima stupaca biti naveden rok zajma (od 2 prije 6 godina u mjesecima s povećanjem od jedne godine), a u redovima - veličina tijela kredita (od 850000 prije 950000 rubalja u koracima 10000 rubalja). U ovom slučaju preduvjet je ćelija u kojoj se nalazi formula izračuna (u našem slučaju) PMT), koji se nalazi na granici imena redaka i stupaca. Bez ovog uvjeta, alat neće raditi kada se koriste dvije varijable.
- Potom odaberite cijeli rezultirajući raspon tablica, uključujući imena stupaca, redaka i ćeliju s formulom PMT. Idite na karticu "Podaci". Kao i prethodni put, kliknite na gumb „Šta ako analiza“, u grupi alata "Rad sa podacima". Na listi koja se otvori odaberite "Tabela podataka ...".
- Pokreće se prozor alata "Tabela podataka". U ovom slučaju su nam potrebna oba polja. U polju Zamjena vrijednosti stupca u u primarnim podacima navedite koordinate ćelije koja sadrži pojam zajma. U polju "Zamjena vrijednosti red po red u" navedite adresu ćelije početnih parametara koja sadrži vrijednost tijela kredita. Nakon što se unose svi podaci. Kliknite na dugme "OK".
- Program izvodi proračun i ispunjava raspon tablica podacima. Na raskrižju redova i stupaca sada je moguće promatrati koja će tačno biti mjesečna uplata, s pripadajućim iznosom godišnje kamate i naznačenim rokom kredita.
- Kao što vidite, ima puno vrijednosti. Za rješavanje drugih problema može biti još više. Stoga, da biste rezultate rezultata učinili vizualnijima i odmah utvrdili koje vrijednosti ne zadovoljavaju zadani uvjet, možete koristiti alate za vizualizaciju. U našem slučaju to će biti uslovno oblikovanje. Biramo sve vrijednosti raspona tablice, isključujući zaglavlja redaka i stupaca.
- Pomicanje na karticu "Početna" i kliknite na ikonu Uslovno oblikovanje. Nalazi se u bloku alata. Stilovi na vrpci. U meniju koji se otvori odaberite Pravila za odabir ćelija. Na dodatnoj listi kliknite na položaj "Manje ...".
- Nakon toga, otvara se prozor postavki uslovnog oblikovanja. U lijevom polju naznačite vrijednost manju od koje će stanice biti odabrane. Kao što se sjećamo, zadovoljni smo uvjetom da će mjesečno plaćanje kredita biti manje od 29000 rubalja. Unosimo ovaj broj. U desnom polju možete odabrati boju osvjetljenja, mada je prema zadanim postavkama možete ostaviti. Nakon što ste unijeli sva potrebna podešavanja, kliknite na gumb "OK".
- Nakon toga bit će istaknute sve ćelije čije vrijednosti odgovaraju gore navedenom stanju.
Analizirajući niz tablica, možemo izvući neke zaključke. Kao što vidite, uz postojeći rok kredita (36 mjeseci) da bismo uložili u naznačeni iznos mjesečne uplate potrebno je uzeti kredit koji ne prelazi 860000,00 rubalja, odnosno 40.000 manje nego što je prvobitno planirano.
Ako i dalje namjeravamo uzeti zajam od 900.000 rubalja, tada bi rok kredita trebao biti 4 godine (48 mjeseci). Samo u ovom slučaju, mesečna uplata neće premašiti utvrđeni limit od 29 000 rubalja.
Stoga, koristeći ovaj niz tablica i analizirajući prednosti i nedostatke svake opcije, dužnik može donijeti određenu odluku o uvjetima zajma, odabireći najpogodniju opciju od svih mogućih.
Naravno, tablica za pretraživanje može se koristiti ne samo za izračunavanje kreditnih opcija, već i za rješavanje mnogih drugih problema.
Lekcija: Uslovno oblikovanje u Excelu
Općenito, treba napomenuti da je pretraživačka tablica vrlo koristan i relativno jednostavan alat za određivanje rezultata za različite kombinacije varijabli. Koristeći istodobno uvjetno oblikovanje, osim toga možete vizualizirati primljene informacije.
