Tablica podataka u programu Microsoft Excel

Često je potrebno izračunati konačni rezultat za različite kombinacije ulaznih podataka. Stoga će korisnik moći procijeniti sve moguće opcije za radnje, odabrati one čije rezultate interakcije zadovoljavaju, i, naposljetku, odabrati najoptimalniju opciju. U programu Excel, za izvršenje ovog zadatka postoji poseban alat - "Tablica podataka" ( "Tablica zamjene" ). Let's saznati kako ga koristiti za obavljanje gore navedenih scenarija.

Pročitajte također: Odabir parametara u programu Excel

Upotreba podatkovne tablice

Alat "Tablica podataka" osmišljen je za izračunavanje rezultata za različite varijacije jedne ili dvije definirane varijable. Nakon izračuna, sve će se moguće varijante pojaviti u obliku tablice, što se naziva matrica faktorske analize. "Tablica podataka" odnosi se na grupu za analizu "Što ako" , koja se nalazi na vrpci na kartici "Podaci" u bloku "Rad s podacima" . Prije programa Excel 2007 ovaj je alat nazvan "Tablicu zamjene" , koja još preciznije odražava njegovu suštinu nego trenutni naziv.

Zamjenska tablica može se koristiti u mnogim slučajevima. Na primjer, tipična je mogućnost kada morate izračunati iznos mjesečne isplate kredita za različite varijacije u razdoblju kreditiranja i iznos kredita, razdoblje kreditiranja i kamatnu stopu. Također, ovaj alat može se koristiti u analizi modela investicijskih projekata.

No, trebali biste znati i da prekomjerna upotreba ovog alata može dovesti do kočenja sustava jer se podaci ponovno preračunavaju. Stoga se preporučuje da u malim tabličnim rasporedima riješite slične zadatke, nemojte koristiti ovaj alat, ali upotrijebite kopiranje formula pomoću oznake za ispunjavanje.

Opravdana primjena "Tablice podataka" je samo u velikim tabličnim rasponima, pri kopiranju formula može potrajati puno vremena, a tijekom postupka povećava vjerojatnost pogrešaka. No, u ovom se slučaju preporučuje i onemogućiti automatsko ponovno izračunavanje formula u rasponu tablice zamjene, kako bi se izbjegao nepotrebno opterećenje sustava.

Glavna razlika između različitih primjena tablice podataka je broj varijabli uključenih u izračun: jedna varijabla ili dva.

1. način: primijenite alat s jednom varijablom

Odmah razmotrimo mogućnost kada se podatkovna tablica koristi s jednim varijabilnim vrijednostima. Uzmimo tipičan primjer pozajmljivanja.

Dakle, trenutačno nam se nude sljedeći uvjeti:

• Trajanje zajma - 3 godine (36 mjeseci);
• Iznos kredita iznosi 900.000 rubalja;
• Kamatna stopa iznosi 12,5% godišnje.

Plaćanja se ostvaruju na kraju razdoblja plaćanja (mjesec) u okviru sheme renta, tj. U jednakim udjelima. Istodobno, na početku cijelog razdoblja zajma, značajan dio isplata je plaćanje kamata, ali kako se tijelo smanjuje, smanjenje kamata i povećanje iznosa otplata samog tijela. Ukupna uplata, kao što je gore navedeno, ostaje nepromijenjena.

Potrebno je izračunati koliko će iznos mjesečne uplate, koji uključuje otplatu kreditnog tijela i plaćanja kamata. Da biste to učinili, u programu Excel postoji operater PLT .

PLT pripada skupini financijskih funkcija, a njezin zadatak je izračunavanje mjesečnog plaćanja najma vrste renta na temelju iznosa kredita, razdoblja zajma i kamatne stope. Sintaksa ove funkcije prikazana je u ovom obliku

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

"Stopa" je argument koji određuje kamatnu stopu plaćanja kredita. Indikator je postavljen na određeno vrijeme. Imamo rok isplate od jednog mjeseca. Stoga bi godišnja stopa od 12,5% trebala biti podijeljena na broj mjeseci u godini, odnosno 12.

"Kper" - argument koji određuje broj razdoblja za cijelo razdoblje zajma. U našem primjeru razdoblje je mjesec dana, a razdoblje kreditiranja je 3 godine ili 36 mjeseci. Dakle, broj razdoblja bit će rano 36.

"PS" je argument koji određuje sadašnju vrijednost zajma, tj. Veličina tijela kredita u vrijeme izdavanja. U našem slučaju, ta je brojka 900.000 rubalja.

"BS" je argument koji ukazuje na iznos zajma u trenutku njezina potpunog plaćanja. Naravno, taj će pokazatelj biti nula. Ovaj argument nije potreban parametar. Ako ga izostavite, pretpostavlja se da je jednak broju "0".

"Tip" je također izborni argument. On izvješćuje o plaćanju: početkom razdoblja (parametar - "1" ) ili na kraju razdoblja (parametar "0" ). Kao što se sjećamo, platimo na kraju kalendarskog mjeseca, tj. Vrijednost ovog argumenta jednaka je "0" . No, uzevši u obzir da ovaj pokazatelj nije obvezan, a prema zadanim postavkama, ako ga ne upotrebljavate, vrijednost se označava s "0" , a zatim se u navedenom primjeru općenito ne može upotrebljavati.

1. Započnimo izračun. Odaberite ćeliju na listi, gdje će se prikazati izračunata vrijednost. Kliknemo na gumb "Umetni funkciju" .
2. Pokreće se Čarobnjak za funkcije . Prijelaz prelazimo na kategoriju "Financijska" , s popisa odaberite naziv "PLT" i kliknite gumb "OK" .
3. Nakon toga aktivira se prozor argumenata gore navedene funkcije.

   Stavili smo kursor na polje "Bet" , a zatim kliknite na ćeliju na listi s vrijednošću godišnje kamatne stope. Kao što možete vidjeti, polje odmah prikazuje svoje koordinate. No, kako se sjećamo, potrebna nam je mjesečna stopa, pa stoga dijelimo rezultat za 12 ( / 12 ).

   Na polju "Kper" na isti način unosimo koordinate ćelija u terminima kredita. U ovom slučaju, ne morate podijeliti ništa.

   U polju "Ps" morate odrediti koordinate ćelije koje sadrže iznos kredita. Mi to radimo. Također stavite znak "-" prije prikazanih koordinata. Činjenica je da funkcija PLT-a po defaultu daje konačni rezultat negativnim znakom, s pravom uzimajući u obzir mjesečnu isplatu kredita kao gubitak. No, zbog jasnoće u korištenju tablice podataka, ovaj broj mora biti pozitivan. Stoga smo stavili minus znak pred jedan od argumenata funkcije. Kao što znate, množenjem "minus" za "minus" konačno daje "plus" .

   U polju "Bc" i "Type" uopće ne unosimo nikakve podatke. Kliknite gumb "OK" .

4. Nakon toga operater izračunava i izlaže rezultat ukupne mjesečne uplate - 30108,26 rubalja u unaprijed određenu ćeliju. No, problem je što zajmoprimac može platiti najviše 29.000 rubalja mjesečno, tj. Treba pronaći banku koja nudi uvjete s nižom kamatom, ili smanjiti kreditni organ ili povećati razdoblje zajma. Tablica supstitucija pomoći će nam izračunati različite mogućnosti djelovanja.
5. Prvo, koristimo tablicu zamjena s jednom varijablom. Pogledajmo kako se vrijednost obvezne mjesečne isplate varira s različitim varijacijama godišnje stope, počevši od 9,5% godišnje i završava na 12,5% godišnje u 0,5% povećanjima. Svi ostali uvjeti ostaju nepromijenjeni. Izrađujemo tablični raspon čiji će nazivi stupaca odgovarati različitim varijacijama kamatne stope. Istovremeno, linija "Mjesečna plaćanja" ostaje onakva kakva jest. Prva ćelija mora sadržavati formulu koju smo ranije izračunali. Za više informacija možete dodati retke "Ukupni iznos kredita" i "Ukupna kamatna stopa" . Stupac u kojem se izračun nalazi nalazi se bez naslova.
6. Zatim izračunavamo ukupni iznos zajma u trenutnim uvjetima. Da biste to učinili, odaberite prvu ćeliju retka "Ukupni iznos zajma" i pomnožite sadržaj ćelija "Mjesečno plaćanje" i "Kreditno razdoblje" . Nakon toga kliknite na tipku Enter .
7. Da bismo izračunali ukupni iznos kamate u trenutnim uvjetima, na sličan način oduzimamo iznos kredita od ukupnog iznosa kredita. Da biste prikazali rezultat na zaslonu, kliknite gumb Enter . Dakle, dobivamo iznos koji preplatimo prilikom vraćanja zajma.
8. Sada je vrijeme za primjenu alata za tablicu podataka . Odaberite cijeli niz tablica, osim naziva redaka. Nakon toga idite na karticu "Podaci" . Kliknemo na gumb na vrpci "Analiza onoga što ako" koji se nalazi u grupi alata "Rad s podacima" (u programu Excel 2016, grupa "Prognoza" ). Tada se otvara mali izbornik. U njemu odaberite stavku "Tablica podataka ..." .
9. Otvara se mali prozor, koji se naziva "Tablica podataka" . Kao što možete vidjeti, on ima dva polja. Budući da radimo s jednom varijablom, trebamo samo jedan od njih. Budući da promijenimo varijablu prema stupcima, upotrijebit ćemo polje "Zamjenske vrijednosti po stupcima u" . Postavite pokazivač tamo, a zatim kliknite ćeliju u izvoru koji sadrži trenutnu postotnu vrijednost. Nakon što se koordinate ćelija prikazuju u polju, kliknite gumb "OK" .
10. Alat izračunava i popunjava cijeli raspon tablica s vrijednostima koje odgovaraju različitim opcijama za kamatnu stopu. Ako stavite pokazivač na bilo koji element određene površine tablice, možete vidjeti da formula formula ne prikazuje uobičajenu formulu za izračun plaćanja, ali je posebna formula za neraskidivo polje. To jest, sada ne možete promijeniti vrijednosti u pojedinačnim ćelijama. Rezultati proračuna možete izbrisati samo zajedno, a ne odvojeno.

Osim toga, možete vidjeti da je vrijednost mjesečne uplate na 12,5% godišnje, dobivena kao rezultat primjene tablice permutations, odgovara vrijednosti za isti iznos koji smo dobili primjenom funkcije PLT . Ovo još jednom dokazuje točnost izračuna.

Analizom ovog tabličnog niza treba reći da je, kao što vidimo, samo po stopi od 9,5% godišnje prihvatljiva razina mjesečne uplate (manje od 29.000 rubalja).

Pouka: Izračunavanje plaćanja anuiteta u programu Excel

Način 2: upotrebljavajte alat s dvije varijable

Naravno, vrlo je teško pronaći banke koje izdaju zajmove 9,5% godišnje. Pogledajmo dakle mogućnosti za ulaganje u prihvatljivu razinu mjesečne uplate za različite kombinacije ostalih varijabli: veličinu tijela kredita i kreditni rok. Istovremeno zadržavamo kamatnu stopu nepromijenjenu (12,5%). U rješavanju ovog zadatka alat "Tablica podataka" pomoći će nam upotrebljavati dvije varijable.

1. Crtanje novog stola tablice. Sada će nazivi stupaca naznačiti kreditno razdoblje (od 2 do 6 godina u mjesecima u koracima od jedne godine), a u redovima - iznos kredita (od 850000 do 950000 rubalja s korakom od 10.000 rubalja). Obvezno stanje je da se stanica na kojoj se nalazi formula za izračun (u našem slučaju PLT ) nalazi na granici imena redaka i stupaca. Bez ovog uvjeta alat neće funkcionirati kada se koriste dvije varijable.
2. Zatim odaberite cijeli dobiveni tablični raspon, uključujući nazive stupaca, redaka i ćelije s PLT formulom. Idite na karticu "Podaci" . Kao i prethodno, kliknemo na gumb "Analiziraj što ako" u grupi alata "Rad s podacima" . Na otvorenom popisu odaberite stavku "Tablica podataka ..." .
3. Pokreće se prozor alata "Tablica podataka" . U ovom slučaju trebamo oba polja. U polju "Zamjenske vrijednosti po stupcima u" označavamo koordinate ćelije koje sadrže kreditno razdoblje u primarnim podacima. U polju "Zamjenske vrijednosti redaka" označavamo adresu stanice početnih parametara koji sadrže vrijednost tijela zajma. Nakon unosa svih podataka. Kliknite gumb "OK" .
4. Program izračunava i popunjava raspon stolova s ​​podacima. Na sjecištu redaka i stupaca sada možete vidjeti točno što će biti mjesečna isplata, uz odgovarajuću vrijednost godišnje kamate i određeni rok kredita.
5. Kao što možete vidjeti, postoji mnogo vrijednosti. Da bi riješili druge probleme, možda ih ima čak i više. Stoga, da biste rezultati izlaza vidljiviji i odmah odredite koje vrijednosti ne zadovoljavaju zadano stanje, možete koristiti vizualizacijske alate. U našem slučaju to će biti uvjetno oblikovanje. Odaberite sve vrijednosti u rasponu tablice, osim zaglavlja redaka i stupaca.
6. Prijeđite na karticu "Početna" i kliknite ikonu "Uvjetno oblikovanje" . Nalazi se u alatu "Stilovi" na vrpci. Na padajućem izborniku odaberite "Pravila odabira ćelija" . Na dodatnom popisu kliknite stavku "Manje ..." .
7. Nakon toga otvara se prozor uvjetnog oblikovanja. U lijevom polju, navedite vrijednost, manje od onih koje će se dodijeliti stanice. Kao što se sjećamo, zadovoljni smo uvjetima u kojima će mjesečna isplata zajma biti manja od 29.000 rubalja. Unosimo taj broj. U desnom polju možete odabrati boju za označavanje, iako je možete ostaviti prema zadanim postavkama. Nakon što unesete sve potrebne postavke, kliknite gumb "OK" .
8. Nakon toga, sve stanice, vrijednosti u kojima odgovara gore navedenom stanju, bit će istaknute u boji.

Analizom polja tablice možete izvući neke zaključke. Kao što vidimo, s postojećim kreditnim rokom (36 mjeseci), kako bismo uložili u gore navedeni iznos mjesečne uplate, moramo uzeti zajam koji ne prelazi 860000,00 rubalja, odnosno 40.000 manje od prvobitno planiranih.

Ako namjeravamo izdati zajam od 900.000 rubalja, razdoblje kreditiranja trebalo bi biti 4 godine (48 mjeseci). Samo u tom slučaju mjesečna isplata neće premašiti utvrđenu granicu od 29.000 rubalja.

Dakle, pomoću ove tablice niz i analizom pro i kontra svake opcije, dužnik može donijeti određenu odluku o uvjetima kredita, odabirom najprikladnijih opcija iz svih mogućih opcija.

Naravno, pregledna tablica može se koristiti ne samo za izračunavanje opcija kredita već i za rješavanje mnogih drugih problema.

Pouka: Uvjetno oblikovanje u programu Excel

Općenito treba napomenuti da je tablica za pretraživanje vrlo koristan i relativno jednostavan alat za određivanje rezultata za različite kombinacije varijabli. Primjenom uvjetnog oblikovanja istodobno možete vizualizirati primljene informacije.

• ← Otvaranje ODS tablica u Microsoft Excelu
• SQL upita u Microsoft Excelu →