Tabela podatkov v programu Microsoft Excel

Pogosto je treba izračunati končni rezultat za različne kombinacije vhodnih podatkov. Tako bo uporabnik sposoben oceniti vse možne možnosti za dejanja, izbrati tiste, katerih rezultati interakcije zadovoljujejo, in končno, izberite najbolj optimalno možnost. V Excelu za izvedbo te naloge obstaja posebno orodje - "Tabela podatkov" ( "Tabela zamenjave" ). Ugotovimo, kako jo uporabiti za izvedbo zgornjih scenarijev.

Preberite tudi: Izbira parametrov v Excelu

Uporaba podatkovne tabele

Namen orodja "Podatkovna tabela" je izračunati rezultat za različne različice ene ali dveh opredeljenih spremenljivk. Po izračunu se bodo pojavile vse možne variante v obliki tabele, ki se imenuje matrika faktorske analize. "Tabela s podatki" se nanaša na analizno skupino "Kaj če" , ki se nahaja na traku na zavihku "Podatki" v bloku "Delo s podatki" . Pred Excelom 2007 je bilo to orodje imenovano "Nadomestna tabela" , ki še bolj natančno odraža njegovo bistvo kot trenutno ime.

Tabelo zamenjave se lahko uporablja v številnih primerih. Na primer, tipična možnost je, ko morate izračunati znesek mesečnega plačila posojila za različne razlike v kreditnem obdobju in znesku posojila ali obdobja kreditiranja in obrestne mere. Tudi to orodje se lahko uporabi pri analizi modelov naložbenih projektov.

Toda prav tako morate vedeti, da lahko prekomerna uporaba tega orodja povzroči zaviranje sistema, saj se podatki ponovno izračunajo neprekinjeno. Zato priporočamo, da v majhnih tabelarnih nizih za reševanje podobnih nalog ne uporabljajte tega orodja, temveč uporabite kopiranje formul z uporabo označevalnika polnila.

Utemeljena uporaba "podatkovne tabele" je le v velikih razpredelnicah miz, ko lahko kopiranje formul traja veliko časa in med postopkom poveča verjetnost napak. Toda v tem primeru je priporočljivo onemogočiti samodejno preračunavanje formul v območju nadomestne tabele, da bi se izognili nepotrebni obremenitvi sistema.

Glavna razlika med različnimi vrstami podatkovne tabele je število spremenljivk, vključenih v izračun: ena spremenljivka ali dve.

1. način: uporabite orodje z eno spremenljivko

Takoj razmislimo o možnosti, ko se uporabi podatkovna tabela z eno spremenljivo vrednostjo. Vzemimo najbolj tipičen primer posojanja.

Torej, trenutno smo ponudili naslednje kreditne pogoje:

• Trajanje kredita - 3 leta (36 mesecev);
• Znesek posojila je 900.000 rubljev;
• Obrestna mera znaša 12,5% na leto.

Plačila se izvedejo ob koncu obdobja plačila (mesec) po shemi rente, to je v enakih deležih. Hkrati je na začetku celotnega posojilnega obdobja pomemben del plačil plačilo obresti, vendar se zmanjšuje plačilo obresti, povečuje pa se tudi znesek povračila samega organa. Skupno plačilo, kot je bilo že omenjeno, ostane nespremenjeno.

Treba je izračunati, kakšen bo znesek mesečnega plačila, ki vključuje odplačilo posojilnega organa in plačila obresti. Če želite to narediti, v programu Excel obstaja operator PLT .

PLT spada v skupino finančnih funkcij in njena naloga je, da izračuna mesečno izplačilo posojila za rentno vrsto na podlagi zneska organa posojila, trajanja posojila in obrestne mere. Sintaksa te funkcije je predstavljena v tej obliki

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

"Stopnja" je argument, ki določa obrestno mero kreditnih plačil. Indikator je nastavljen za obdobje. Imamo obdobje izplačila enega meseca. Zato je treba letno stopnjo 12,5% razčleniti na število mesecev v letu, to je 12.

"Kper" - argument, ki določa število obdobij za celotno obdobje posojila. V našem primeru je obdobje enega meseca in obdobje kreditiranja je 3 leta ali 36 mesecev. Tako bo število obdobij zgodaj 36.

"PS" je argument, ki določa sedanjo vrednost posojila, to je velikost organa posojila v času izdaje. V našem primeru je ta številka 900.000 rubljev.

"BS" je utemeljitev, ki navaja znesek organa posojila v času svojega celotnega plačila. Seveda bo ta kazalnik nič. Ta argument ni potreben parameter. Če ga izpustite, se domneva, da je enaka številki "0".

"Vrsta" je tudi izbirni argument. Poroča, kdaj bo plačilo izvršeno: na začetku obdobja (parameter - "1" ) ali na koncu obdobja (parameter - "0" ). Kot se spomnimo, plačamo ob koncu koledarskega meseca, to pomeni, da bo vrednost tega argumenta enaka "0" . Ampak, glede na to, da ta indikator ni obvezen in privzeto, če ga ne uporabljate, vrednost in tako mislimo z "0" , potem v navedenem primeru na splošno ni mogoče uporabiti.

1. Torej, začnimo izračun. Izberite celico na listu, kjer bo prikazana izračunana vrednost. Kliknemo na gumb "Vstavi funkcijo" .
2. Začne se čarovnik za funkcije . Izvedemo prehod v kategorijo »Finančni« , s seznama izberite ime »PLT« in kliknite gumb »V redu« .
3. Po tem se aktivira okno argumentov zgoraj navedene funkcije.

   Postavili smo kazalko na polje "Bet" , nato pa kliknite na celico na listu z vrednostjo letne obrestne mere. Kot lahko vidite, polje takoj prikaže koordinate. Ampak, kot se spominjamo, potrebujemo mesečno stopnjo, zato rezultat delimo z 12 ( / 12 ).

   Na področju "Kper" na enak način vnesemo koordinate celic posojilnega izraza. V tem primeru vam ni treba razdeliti.

   V polju "Ps" morate določiti koordinate celice, ki vsebujejo znesek posojilnega telesa. To počnemo. Pred prikazane koordinate položite tudi znak "-" . Dejstvo je, da funkcija PLT privzeto daje končni rezultat z negativnim znakom, upravičeno ob upoštevanju mesečnega plačila posojila kot izgube. Ampak zaradi jasnosti pri uporabi podatkovne tabele mora biti ta številka pozitivna. Zato smo pred enim od argumentov funkcije postavili znak minus . Kot veste, pomnoži "minus" z "minus" sčasoma daje "plus" .

   V poljih "Bc" in "Vrsta" sploh ne izdelujemo nobenih podatkov. Kliknite gumb "V redu" .

4. Po tem operator izračuna in izračuna rezultat skupnega mesečnega plačila - 30108,26 rubljev na vnaprej določeno celico. Ampak problem je v tem, da je posojilojemalec sposoben plačati največ 29.000 rubljev na mesec, kar pomeni, mora bodisi najti banko, ki ponuja pogoje z nižjo obrestno mero ali zmanjšanje telo posojila je, ali povečati posojilo izraz. Tabela nadomestkov nam bo pomagala izračunati različne možnosti za ukrepanje.
5. Najprej uporabimo tabelo nadomestkov z eno spremenljivko. Poglejmo, kako se bo spremenila vrednost obveznega mesečnega plačila za različne variacije letnih stopnjah od 9,5% in se končajo z 12,5% v korakih po 0,5%. Vsi ostali pogoji ostanejo nespremenjeni. Prikaže se tabularno območje, katerega imena stolpcev bodo ustrezale različnim spremembam obrestne mere. Hkrati je vrstica "Mesečna plačila" ostala enaka. Prva celica mora vsebovati formulo, ki smo jo izračunali prej. Za več informacij lahko dodate vrstice "Skupni znesek posojila" in "Skupni znesek obresti" . Stolpec, v katerem je izračun, se izvede brez naslova.
6. Nato izračunamo skupni znesek posojila pod sedanjimi pogoji. Če želite to narediti, izberite prvo celico vrstice "Skupni znesek posojila" in pomnožite vsebino celic "Mesečno plačilo" in "Kreditno obdobje" . Po tem kliknite na tipko Enter .
7. Za izračun skupnega zneska obresti v tekočih pogojih odštejemo znesek posojilnega telesa od skupnega zneska posojila na podoben način. Če želite prikazati rezultat na zaslonu, kliknite gumb Enter . Tako dobimo znesek, ki ga preplačujemo ob odplačilu posojila.
8. Zdaj je čas, da uporabimo orodje Podatkovna tabela . Izberite celotno tabelo, razen imena vrstic. Po tem pojdite na kartico »Podatki «. Kliknemo na gumb na traku "Analiza kaj če" , ki se nahaja v skupini orodij "Delo s podatki" (v Excel 2016, skupini orodij "Napoved" ). Nato se odpre majhen meni. V njej izberite element »Podatkovna tabela ...« .
9. Odpre se majhno okno, ki se imenuje »Podatkovna tabela« . Kot vidite, ima dva polja. Ker delamo z eno spremenljivko, potrebujemo le eno od njih. Ker spreminjamo spremenljivko za stolpce, bomo uporabili polje » Zamenjaj vrednosti po stolpcih« . Nastavite kazalko in kliknite celico v naboru izvornih podatkov, ki vsebuje trenutno odstotno vrednost. Ko so celične koordinate prikazane na polju, kliknite gumb "OK" .
10. Orodje izračuna in zapolni celotno območje tabele z vrednostmi, ki ustrezajo različnim možnostim za obrestno mero. Če postavite kazalko v kateri koli element dane površine tabele, lahko vidite, da formula formula ne prikazuje običajne formule za izračun plačila, temveč posebno formulo za neraztopljivo matriko. To pomeni, da trenutno ne morete spremeniti vrednosti v posameznih celicah. Rezultate izračuna lahko izbrišete samo skupaj in ne ločeno.

Poleg tega lahko vidimo, da se znesek mesečno plačilo na 12,5% letno, ki izhaja iz uporabe permutacijo tabeli ustreza vrednosti za isti znesek obresti, ki smo jih prejeli z uporabo funkcije PMT. To še enkrat dokazuje pravilnost izračuna.

Če analiziramo to tabelo v tabeli, je treba reči, da je, kot smo videli, le za stopnjo mesečnega plačila (manj kot 29.000 rubljev) za nas sprejemljivi samo 9,5% letno.

Lekcija: Izračun rente v Excelu

2. način: uporabite orodje z dvema spremenljivkama

Seveda je zelo težko, če sploh, najti banke, ki izdajajo posojila v višini 9,5% na leto. Torej, poglejmo, kakšne možnosti obstajajo za vlaganje v sprejemljivo raven mesečnega plačila za različne kombinacije drugih spremenljivk: velikost posojila in trajanje kredita. Istočasno obrestno mero obdržimo nespremenjeno (12,5%). Pri reševanju te naloge nam bo orodje »Podatkovna tabela« pomagalo uporabiti dve spremenljivki.

1. Risanje nove tabele v tabeli. Zdaj, imena stolpcev bo iz izraza posojila (od 2 do 6 let v mesecih, v korakih po enem letu), in v liniji - vrednosti posojila (od 850.000 do 950.000 rubljev v korakih po 10.000 rubljev). Obvezni pogoj je, da je celica, v kateri se nahaja formula za izračun (v našem primeru PLT ), nameščena na meji imen vrstic in stolpcev. Brez tega pogoja orodje ne bo delovalo pri uporabi dveh spremenljivk.
2. Nato izberite celoten obseg tabele, vključno z imeni stolpcev, vrstic in celice s formulo PLT . Pojdite na kartico »Podatki «. Kot v prejšnjem času, kliknite gumb »Analiziraj, kaj če« v orodju »Delo s podatki« . Na odprtem seznamu izberite element »Podatkovna tabela ...« .
3. Začelo se je orodje "Data Table" . V tem primeru potrebujemo obe polji. V polju "Zamenjaj vrednosti po stolpcih v" prikažemo koordinate celice, ki vsebujejo kreditno obdobje v primarnih podatkih. V polju »Zamenjaj vrednosti po vrsticah« navajamo naslov celice začetnih parametrov, ki vsebujejo vrednost telesa posojila. Ko so vneseni vsi podatki. Kliknite gumb "V redu" .
4. Program izračunava in zapolnjuje obseg tabele s podatki. Na presečišču vrstic in stolpcev je zdaj mogoče natančno opazovati, kaj bo mesečno plačilo, z ustrezno vrednostjo letnih obresti in določenim trajanjem posojila.
5. Kot lahko vidite, obstaja veliko vrednosti. Za reševanje drugih težav lahko pride še več. Zato, da bi bil rezultat rezultatov bolj viden in takoj določiti, katere vrednosti ne izpolnjujejo določenega pogoja, lahko uporabite orodja za vizualizacijo. V našem primeru bo to pogojno oblikovanje. Izberite vse vrednosti v obsegu tabele, razen glave vrstic in stolpcev.
6. Premik na kartico »Domov « in kliknete ikono »Pogojno oblikovanje« . Nahaja se v orodni škatli "Styles" na traku. V spustnem meniju izberite »Pravila za izbiro celic« . Na dodatnem seznamu kliknite element »Manj ...« .
7. Po tem se odpre okno pogojnega oblikovanja. V levem polju določite vrednost, manj kot bodo dodeljene celice. Kot smo spomnili, smo zadovoljni s pogojem, da bo mesečno plačilo v okviru posojila manjše od 29.000 rubljev. Vnesemo to številko. V desnem polju lahko izberete barvo označevanja, čeprav jo lahko pustite privzeto. Ko so vnesene vse potrebne nastavitve, kliknite gumb »V redu« .
8. Po tem bodo vse celice, vrednosti, ki ustrezajo zgoraj navedenemu pogoju, označene z barvo.

Pri analizi tabele lahko naredite nekaj zaključkov. Kot lahko vidite, v okviru obstoječega kreditnega obdobja (36 mesecev), da bi vlagali v višji od določenega zneska mesečnega plačila, moramo vzeti posojilo, ki ne presega 860,000.00 rubljev, to je 40.000 nižje, kot je bilo prvotno načrtovano.

Če še vedno nameravamo skleniti posojilo v višini 900.000 rubljev, bi moralo biti kreditno obdobje 4 leta (48 mesecev). Samo v tem primeru mesečno plačilo ne bo preseglo določene meje 29.000 rubljev.

Tako izkoriščajo niz podatkov iz preglednice in analizirajo v "prednosti" in "slabosti" za vsako možnost, da lahko posojilojemalec sprejeti konkretne odločitve o pogojih za posojila, izbiro najbolj ustreza njegovemu željami možnosti za vse.

Seveda se pregledna tabela lahko uporablja ne le za izračun posojilnih možnosti, ampak tudi za reševanje številnih drugih težav.

Lekcija: Pogojno oblikovanje v Excelu

Na splošno je treba opozoriti, da je pregledna tabela zelo uporabno in sorazmerno enostavno orodje za določanje rezultata za različne kombinacije spremenljivk. Poleg tega lahko s pomočjo pogojnega oblikovanja tudi vizualizirate prejete informacije.

• ← Odpiranje tabele ODS v programu Microsoft Excel
• SQL poizvedbe v programu Microsoft Excel →