Vrlo često je potrebno izračunati konačni rezultat za različite kombinacije ulaznih podataka. Tako će korisnik moći da proceni sve moguće opcije za akciju, odabere one čiji rezultat interakcije zadovoljava, i, konačno, izabere najoptimalniju opciju. U Excelu postoji poseban alat za ovaj zadatak - "Data Table" ("Tabela za pretraživanje"). Hajde da otkrijemo kako se koristi za izvođenje gore navedenih scenarija.
Pogledajte i: Izbor parametara u Excelu
Upotreba tabele sa podacima
Alat "Data Table" osmišljen je za izračunavanje rezultata sa različitim varijacijama jedne ili dvije definirane varijable. Nakon izračunavanja, sve moguće opcije će se pojaviti u obliku tabele, koja se naziva matrica faktorske analize. "Data Table" odnosi se na grupu alata Analiza "šta ako"koji se nalazi na vrpci na kartici "Data" u bloku "Rad sa podacima". Pre Excel 2007, ovaj alat je nosio ime. "Tabela za pretraživanje"koji još preciznije odražava njegovu suštinu od sadašnjeg imena.
Pregledna tabela se može koristiti u mnogim slučajevima. Na primjer, tipična opcija je kada je potrebno izračunati iznos mjesečne uplate kredita sa različitim varijacijama perioda kreditiranja i iznosa kredita, ili perioda kreditiranja i kamatne stope. Ovaj alat se može koristiti i za analizu modela investicionih projekata.
Ali morate biti svjesni da prekomjerna upotreba ovog alata može dovesti do kočenja sistema, jer se podaci stalno preračunavaju. Stoga se preporučuje da se ovaj alat ne koristi u malim tabelarnim nizovima za rješavanje sličnih problema, već da se primjenjuje kopiranje formula pomoću markera za popunjavanje.
Opravdana aplikacija "Tabele podataka" je samo u velikim tabelarnim opsezima, kada formule za kopiranje mogu da zauzmu mnogo vremena, a tokom same procedure, verovatnoća grešaka se povećava. Ali čak iu ovom slučaju, preporučuje se da se onemogući automatsko ponovno izračunavanje formula u opsegu pregledne tabele, kako bi se izbeglo nepotrebno opterećenje sistema.
Glavna razlika između različitih upotreba tabele podataka je broj varijabli uključenih u proračun: jedna varijabla ili dvije.
Metoda 1: koristite alatku sa jednom varijablom
Odmah ćemo razmotriti opciju kada se koristi tablica podataka sa jednom vrijednošću varijable. Uzmimo najtipičniji primjer kreditiranja.
Dakle, trenutno nam se nude sledeći uslovi kreditiranja:
- Period kreditiranja - 3 godine (36 mjeseci);
- Iznos kredita - 900000 rubalja;
- Kamatna stopa - 12,5% godišnje.
Isplate se vrše na kraju perioda plaćanja (mjesec) koristeći anuitetsku shemu, odnosno u jednakim dijelovima. Istovremeno, na početku čitavog perioda kreditiranja, kamate čine značajan dio plaćanja, ali kako se tijelo smanjuje, kamate se smanjuju, a iznos otplate samog tijela se povećava. Ukupna uplata, kao što je gore navedeno, ostaje nepromijenjena.
Potrebno je izračunati koliki će biti iznos mjesečne uplate, što uključuje otplatu kreditnog tijela i plaćanje kamata. Za to, Excel ima operatora PMT.
PMT Pripada grupi finansijskih funkcija i zadatak joj je da izračuna mjesečnu isplatu kredita tipa anuiteta na osnovu iznosa kreditnog tijela, roka kredita i kamatne stope. Sintaksa za ovu funkciju je sljedeća.
= PMT (brzina; nper; ps; bs; tip)
"Bet" - Argument koji određuje kamatnu stopu kreditnih plaćanja. Indikator je podešen za period. Naš period isplate je jedan mjesec. Prema tome, godišnja stopa od 12,5% treba podijeliti na broj mjeseci u godini, odnosno 12.
"Kper" t - Argument koji određuje broj perioda za čitavo razdoblje kredita. U našem primjeru, period je jedan mjesec, a rok kredita je 3 godine ili 36 mjeseci. Tako će broj perioda biti rani 36.
"PS" - argument koji određuje sadašnju vrijednost kredita, to jest, to je veličina tijela zajma u vrijeme njegovog izdavanja. U našem slučaju, ta cifra iznosi 900.000 rubalja.
"BS" - argument koji ukazuje na veličinu tijela za zajmove u vrijeme njegove pune isplate. Naravno, ovaj indikator će biti jednak nuli. Ovaj argument je opcionalan. Ako ga preskočite, pretpostavlja se da je jednak broju "0".
"Type" - također izborni argument. On obaveštava o tome kada će se izvršiti plaćanje: na početku perioda (parametar - "1") ili na kraju perioda (parametar - "0"). Kao što se sjećamo, naše plaćanje se vrši na kraju kalendarskog mjeseca, tj. Vrijednost ovog argumenta će biti jednaka "0". Ali, s obzirom da ovaj indikator nije obavezan, a po defaultu, ako se ne koristi, pretpostavlja se da je vrednost "0", onda se u navedenom primjeru uopće ne može koristiti.
- Dakle, prelazimo na proračun. Izaberite ćeliju na listu gde će se prikazati izračunata vrednost. Kliknite na dugme "Umetni funkciju".
- Počinje Čarobnjak za funkcije. Napravite prelazak u kategoriju "Finansijski", odaberite s popisa ime "PLT" i kliknite na dugme "OK".
- Nakon toga slijedi aktiviranje prozora argumenata gore navedene funkcije.
Stavite kursor u polje "Bet"zatim kliknite na ćeliju na listu sa vrijednošću godišnje kamatne stope. Kao što vidite, njegove koordinate se odmah prikazuju u polju. Ali, kao što se sećamo, potrebna nam je mesečna stopa, i zato podelimo rezultat za 12 (/12).
Na terenu "Kper" t na isti način unosimo i koordinate pojmova kreditnih termina. U ovom slučaju, ništa se ne mora podijeliti.
Na terenu "Ps" morate odrediti koordinate ćelije koje sadrže vrijednost kreditnog tijela. Mi to radimo. Takođe smo stavili znak ispred prikazanih koordinata. "-". Poenta je u tome da je funkcija PMT po defaultu, daje konačni rezultat sa negativnim predznakom, prilično uzimajući u obzir mjesečnu isplatu kredita. Ali zbog jasnoće, treba da tabela sa podacima bude pozitivna. Dakle, stavljamo oznaku "minus" prije jednog od argumenata funkcije. Kao što je poznato, množenje "minus" on "minus" na kraju daje plus.
U poljima "Bs" i "Type" Mi uopšte ne unosimo podatke. Kliknite na dugme "OK".
- Nakon toga, operater izračunava i prikazuje u unaprijed određenoj ćeliji rezultat ukupne mjesečne uplate - 30108,26 rubalja. Ali problem je u tome što je dužnik u mogućnosti da plati maksimalno 29.000 rubalja mjesečno, odnosno treba da pronađe uslove banke sa nižom kamatnom stopom, ili da smanji kreditni kredit, ili da produži rok kredita. Izračunajte različite opcije za akciju koje će nam pomoći u preglednoj tabeli.
- Za početak, koristite tablicu pretraživanja s jednom varijablom. Hajde da vidimo kako će se vrijednost obaveznog mjesečnog plaćanja mijenjati s različitim varijacijama godišnje stope, od 9,5% godišnji i završni 12,5% pa sa korakom 0,5%. Svi ostali uslovi ostaju nepromijenjeni. Nacrtajte raspon tabela, čija imena će odgovarati različitim varijacijama kamatne stope. Sa ovom linijom "Mjesečne uplate" ostavite kako jeste. Njegova prva ćelija treba da sadrži formulu koju smo ranije izračunali. Za više informacija, možete dodati linije "Ukupan iznos kredita" i "Total Interest". Stupac u kojem se nalazi proračun se vrši bez zaglavlja.
- Zatim izračunavamo ukupan iznos kredita pod trenutnim uslovima. Da biste to uradili, izaberite prvu ćeliju u redu. "Ukupan iznos kredita" i pomnožite sadržaj ćelije "Mjesečna uplata" i "Trajanje kredita". Nakon toga kliknite na Enter.
- Za izračunavanje ukupnog iznosa kamate pod trenutnim uslovima, na sličan način oduzimamo vrijednost tijela kredita od ukupnog iznosa kredita. Za prikaz rezultata na ekranu kliknite na dugme. Enter. Dakle, dobijamo iznos koji plaćamo prilikom vraćanja kredita.
- Sada je vrijeme da primijenite alat. "Data Table". Odaberite cijeli niz tablica, osim imena reda. Nakon toga idite na karticu "Data". Kliknite na dugme na traci Analiza "šta ako"koja se nalazi u grupi alata "Rad sa podacima" (u Excelu 2016, grupa alata "Prognoza"). Zatim se otvara mali meni. U njemu biramo poziciju "Data Table ...".
- Otvara se mali prozor koji se zove "Data Table". Kao što vidite, ima dva polja. Budući da radimo sa jednom varijablom, potrebna nam je samo jedna od njih. Pošto se naše promene promenljivih dešavaju u kolonama, koristićemo polje "Zamijenite vrijednosti stupcima u". Postavimo kursor tamo, a zatim kliknemo na ćeliju u početnom skupu podataka, koja sadrži trenutnu vrijednost postotka. Nakon što se u polju prikažu koordinate ćelije, kliknite na dugme "OK".
- Alat izračunava i ispunjava cijeli raspon tablica s vrijednostima koje odgovaraju različitim opcijama kamatnih stopa. Ako postavite kursor na bilo koji element ovog tabličnog prostora, možete vidjeti da bar sa formulom ne prikazuje uobičajenu formulu za izračunavanje plaćanja, već posebnu formulu neprekidnog niza. To znači da više nije moguće mijenjati vrijednosti u pojedinačnim ćelijama. Brisanje rezultata izračuna može biti samo zajedno, a ne odvojeno.
Pored toga, može se primijetiti da vrijednost mjesečne uplate od 12,5% godišnje, dobivene primjenom pregledne tablice, odgovara vrijednosti po istoj kamatnoj stopi koju smo primili primjenom funkcije PMT. Ovo još jednom dokazuje tačnost izračuna.
Nakon analize ovog tabelarnog niza, treba reći da, kao što vidimo, samo po stopi od 9,5% godišnje, dobija se prihvatljiv mesečni nivo plaćanja (manji od 29,000 rubalja).
Lekcija: Izračunavanje anuiteta u Excelu
Metod 2: koristite alatku sa dve varijable
Naravno, veoma je teško, ako je uopće realno, pronaći banke koje izdaju kredite na 9,5% godišnje. Dakle, da vidimo koje opcije postoji za ulaganje u prihvatljiv nivo mjesečne uplate za različite kombinacije drugih varijabli: veličina tijela kredita i rok kredita. Istovremeno, kamatna stopa će ostati nepromijenjena (12,5%). Alat će nam pomoći u ovom zadatku. "Data Table" koristeći dvije varijable.
- Nacrtajte novi niz tablica. Sada će se pojam kredita prikazati u nazivima kolona (od 2 do 6 godine u mjesecima u koracima od jedne godine), au redovima - veličina tijela zajma (od 850000 do 950000 rublje u koracima 10000 rubalja). U ovom slučaju, neophodno je da ćelija u kojoj se nalazi formula za izračunavanje (u našem slučaju PMT), koji se nalazi na granici imena reda i kolone. Bez ovog stanja, alat neće raditi kada se koriste dvije varijable.
- Zatim izaberite sve rezultirajuće raspone tabela, uključujući imena kolona, redova i ćelije sa formulom PMT. Idite na karticu "Data". Kao i ranije, kliknite na dugme. Analiza "šta ako"u grupi alata "Rad sa podacima". Na listi koja se otvori, izaberite stavku "Data Table ...".
- Počinje prozor alata. "Data Table". U ovom slučaju, potrebna su nam oba polja. Na terenu "Zamijenite vrijednosti stupcima u" u primarnim podacima odredimo koordinate ćelije koje sadrže pojam kredita. Na terenu "Zamijenite vrijednosti redovima u" navedite adresu ćelije početnih parametara koji sadrže vrijednost tijela zajma. Nakon unosa svih podataka. Kliknite na dugme "OK".
- Program izvodi izračun i popunjava raspon tablica podacima. Na preseku redova i kolona, sada je moguće posmatrati kako će tačno biti mjesečna uplata, sa odgovarajućim iznosom godišnje kamate i određenim periodom kreditiranja.
- Kao što vidite, dosta je vrijednosti. Da bi se riješili drugi problemi možda će biti još više. Stoga, da bi se rezultat rezultata učinio vizualnijim i odmah odredio koje vrijednosti ne zadovoljavaju zadani uvjet, možete koristiti alate za vizualizaciju. U našem slučaju to će biti uslovno formatiranje. Odaberite sve vrijednosti raspona tablica, izuzimajući zaglavlja retka i stupaca.
- Pređite na karticu "Home" i kliknite na ikonu "Uslovno oblikovanje". Nalazi se u kutiji s alatima. "Stilovi" na kaseti. U meniju koji se otvori, izaberite stavku "Pravila za odabir ćelije". U dodatnoj listi kliknite na poziciju "Manje ...".
- Nakon toga otvara se prozor za postavke uvjetnog oblikovanja. U lijevom polju specificiramo vrijednost, manju od koje će ćelije biti odabrane. Kao što se sjećamo, zadovoljni smo uvjetom pod kojim će mjesečna uplata na kredit biti manja 29000 rubalja. Unesite ovaj broj. U desnom polju moguće je odabrati boju odabira, iako je možete ostaviti po defaultu. Nakon unosa svih potrebnih postavki, kliknite na gumb. "OK".
- Nakon toga, sve ćelije čije vrijednosti odgovaraju gore navedenom stanju će biti označene bojom.
Nakon analize niza tabela, možete izvući neke zaključke. Kao što možete vidjeti, sa trenutnim rokom kredita (36 mjeseci), kako bi se uložili u gore navedeni iznos mjesečne uplate, potrebno je uzeti kredit koji ne prelazi 8.600.000,00 rubalja, odnosno 40.000 manje od prvobitno planiranog.
Ako i dalje nameravamo da uzmemo kredit u iznosu od 900.000 rubalja, onda rok zajma treba da bude 4 godine (48 meseci). Samo u ovom slučaju, iznos mjesečne uplate neće premašiti utvrđeni limit od 29.000 rubalja.
Dakle, iskorištavanjem ovog tabelarnog niza i analizom prednosti i nedostataka svake opcije, dužnik može donijeti specifičnu odluku o uvjetima kreditiranja, birajući opciju koja najbolje odgovara njegovim potrebama.
Naravno, pregledna tablica se može koristiti ne samo za izračunavanje kreditnih opcija, već i za rješavanje mnogih drugih problema.
Lekcija: Uslovno formatiranje u Excelu
Općenito, treba napomenuti da je pregledna tablica vrlo koristan i relativno jednostavan alat za određivanje rezultata različitih kombinacija varijabli. Primjenjujući uvjetno oblikovanje zajedno s njim, pored toga, možete vizualizirati primljene informacije.
