HVERNIG á Að LEYSA JöFNUNARKERFI í EXCEL

Oft er það krafist að reikna endanlega niðurstöðu fyrir ýmsar samsetningar inntaksgagna. Þannig mun notandinn vera fær um að meta allar mögulegar valkostir til aðgerða, veldu þá sem samskipti afleiðingin uppfyllir honum og að lokum velja hagkvæmasta valkostinn. Í Excel er sérstakt tól fyrir þetta verkefni - "Gögn töflu" ("Leitartöflunni"). Við skulum finna út hvernig á að nota það til að framkvæma ofangreindar aðstæður.

Sjá einnig: Parameter val í Excel

Notkun gagnatafla

Tól "Gögn töflu" það er hannað til að reikna út niðurstöðurnar með mismunandi afbrigðum af einum eða tveimur skilgreindum breytum. Eftir útreikning munu allar mögulegar valkostir birtast í formi töflu, sem kallast fylkið af þáttagreiningargreiningu. "Gögn töflu" vísar til hóps verkfæri "Hvað-ef" greiningsem er sett á borðið í flipanum "Gögn" í blokk "Vinna með gögn". Fyrir Excel 2007 var þetta tól notað. "Leitartöflunni"sem endurspeglaði enn frekar kjarna þess en núverandi heiti.

Útlitstöflunni er hægt að nota í mörgum tilvikum. Til dæmis er dæmigerður valkostur þegar þú þarft að reikna út upphæð mánaðarlegs lánveitingar með mismunandi afbrigði af lánshæfismatinu og lánsfjárhæðinni eða lánshæfiseinkunn og vexti. Þetta tól er einnig hægt að nota við greiningu á verkefnum fyrir fjárfestingarverkefni.

En þú ættir líka að vera ljóst að of mikil notkun þessarar tóls getur leitt til hemlunar í kerfinu, þar sem gögn eru stöðugt endurreiknuð. Þess vegna er mælt með því að nota þetta tól ekki í litlum töfluupplýsingum til að leysa svipaðar vandamál en að beita afritunarformúlum með fylla merkinu.

Rökstudd umsókn "Gagnatöflur" er aðeins í stórum tabular sviðum, þegar afrita formúlur geta tekið mikinn tíma og í aðgerðinni sjálf er líkurnar á villum auknar. En jafnvel í þessu tilfelli er mælt með því að gera sjálfvirk endurútreikning á formúlum á bilinu útlitstöflu óvirk, til að koma í veg fyrir óþarfa álag á kerfinu.

Helstu munurinn á hinum ýmsu notkun gagnatafla er fjöldi breytinga sem taka þátt í útreikningi: ein breytu eða tveir.

Aðferð 1: Notaðu tækið með einum breytu

Við skulum strax skoða valkostinn þegar gagnatafla er notuð með einum breytu. Taktu dæmigerðu dæmi um útlán.

Svo, nú erum við í boði eftirfarandi kredit skilyrði:

Lánstími - 3 ár (36 mánuðir);
Lánshæð - 900000 rúblur;
Vextir - 12,5% á ári.

Greiðslur eru gerðar í lok greiðslutímabilsins (mánuður) með því að nota lífeyrikerfið, það er í jöfnum hlutum. Á sama tíma, í upphafi lánstímans, eru vaxtagreiðslur mikilvægir hluti greiðslna en þegar líkaminn minnkar minnkar vaxtagreiðslur og magn endurgreiðslu líkamans sjálft eykst. Heildargreiðslan, eins og nefnd er hér að framan, er óbreytt.

Nauðsynlegt er að reikna út hvaða upphæð mánaðarlegra greiðslna verður, þar með talin endurgreiðsla lánveitandans og vaxtagreiðslna. Fyrir þetta hefur Excel rekstraraðila PMT.

PMT Það tilheyrir flokki fjárhagslegra aðgerða og verkefnið er að reikna út mánaðarlaunagreiðslu lífeyrissamningsins miðað við fjárhæð lánafyrirtækis, lánstíma og vexti. Samantektin fyrir þessa aðgerð er sem hér segir.

= PMT (hlutfall; nper; ps; bs; tegund)

"Veðja" - Rökin sem ákvarða vexti lána greiðslna. Vísirinn er stilltur fyrir tímabilið. Útborgunartími okkar er ein mánuður. Þess vegna ætti árleg hlutfall 12,5% að vera sundurliðaður í fjölda mánaða á ári, það er 12.

"Kper" - Rökin sem ákvarðar fjölda tímabila fyrir allt tímabil lánsins. Í dæmi okkar er tímabilið einn mánuður og lánstíminn er 3 ár eða 36 mánuðir. Þannig verður fjöldi tímabila snemma 36.

"PS" - rökin sem ákvarðar núvirði lánsins, það er stærð lánshópsins við útgáfu þess. Í okkar tilviki er þessi tala 900.000 rúblur.

"BS" - rök sem gefur til kynna stærð lánshópsins þegar fullur greiðsla er fyrir hendi. Auðvitað mun þessi vísir vera jafnt og núll. Þetta rök er valfrjálst. Ef þú sleppir því er gert ráð fyrir að það sé jafnt við töluna "0".

"Tegund" - einnig valfrjálst rök. Hann upplýsir um hvenær greiðsla verður tekin: í byrjun tímabilsins (breytu - "1") eða í lok tímabilsins (breytu - "0"). Eins og við munum, er greiðslan okkar tekin í lok mánaðarins, það er gildi þessarar greinar mun vera jafnt "0". En miðað við að þessi vísir er ekki skyltur og sjálfgefið, ef það er ekki notað, er gert ráð fyrir að verðmæti sé "0", þá í tilgreint dæmi er það ekki hægt að nota yfirleitt.

Svo halda áfram við útreikninginn. Veldu reitinn á blaðinu þar sem reiknuð gildi birtist. Við smellum á hnappinn "Setja inn virka".

Byrjar Virka Wizard. Gerðu breytinguna í flokkinn "Financial", veldu nafnið á listanum "PLT" og smelltu á hnappinn "OK".

Eftir þetta er virkjun rökargluggans af ofangreindum aðgerðum.
Settu bendilinn í reitinn "Veðja"smelltu síðan á reitinn á blaðinu með verðmæti árlegra vaxta. Eins og þú sérð eru hnitin strax birt í reitnum. En, eins og við munum, þurfum við mánaðarlegt hlutfall og því skiptum við niðurstöðuna um 12/12).
Á sviði "Kper" á sama hátt innum við hnit lánsfjárfrumna. Í þessu tilfelli þarf ekkert að skipta neinu.
Á sviði "Ps" þú verður að tilgreina hnit frumunnar sem inniheldur verðmæti líkama lánsins. Við gerum það. Við setjum einnig tákn fyrir framan hnitin sem birtist. "-". Aðalatriðið er að hlutverkið PMT Sjálfgefið gefur það endanlega niðurstöðu með neikvætt tákn, nokkuð miðað við mánaðarlegt lán greiða tap. En fyrir skýrleika, þurfum við gagnatöflunni að vera jákvæð. Þess vegna setjum við mark "mínus" fyrir einn af aðgerðargögnum. Eins og vitað er, margföldun "mínus" á "mínus" að lokum gefur plús.
Í reitunum "Bs" og "Tegund" Við sláum ekki inn gögn alls. Við smellum á hnappinn "OK".

Eftir það reiknar rekstraraðilinn og birtir í fyrirfram tilgreindum klefi niðurstöðu heildar mánaðarlegrar greiðslu - 30108,26 rúblur. En vandamálið er að lántakandi geti greitt hámark 29.000 rúblur á mánuði, það er að hann ætti annaðhvort að finna bankaútboðsaðstæður með lægri vöxtum eða draga úr lánastofnuninni eða lengja lánstíma. Reiknaðu hinar ýmsu valkosti til aðgerða mun hjálpa okkur að leita töflunni.

Til að byrja skaltu nota leitartöflunni með einum breytu. Leyfðu okkur að sjá hvernig verðmæti skylt mánaðarleg greiðsla er breytileg eftir mismunandi breytingum á ársverði, allt frá 9,5% árlega og endir 12,5% á með skrefi 0,5%. Öll önnur skilyrði eru óbreytt. Teikna borðsvið, nöfn dálkanna sem samsvara mismunandi afbrigði vaxta. Með þessari línu "Mánaðarlegar greiðslur" fara eins og það er. Fyrsti flokkurinn hans ætti að innihalda formúluna sem við reiknuðum áður. Nánari upplýsingar er að bæta við línum "Samtals lánsfjárhæð" og "Samtals vextir". Dálkurinn þar sem útreikningur er staðsettur er gerður án haus.

Næstum reiknum við heildarfjárhæð lánsins við núverandi aðstæður. Til að gera þetta skaltu velja fyrsta reit í röðinni. "Samtals lánsfjárhæð" og margfalda klefi innihaldsins "Mánaðarleg greiðsla" og "Lánstími". Eftir þetta smellirðu á Sláðu inn.

Til að reikna heildarfjárhæð vaxta við núverandi aðstæður, á sama hátt draga við gildi lánshlutans úr heildarfjárhæð lánsins. Til að birta niðurstöðuna á skjánum, smelltu á hnappinn. Sláðu inn. Þannig fáum við þá upphæð sem við greiðum þegar við endurheimtum lánið.

Nú er kominn tími til að sækja um tækið. "Gögn töflu". Veldu alla töflukerfið, nema fyrir radnöfnin. Eftir það ferðu að flipanum "Gögn". Smelltu á hnappinn á borðið "Hvað-ef" greiningsem er sett í hóp verkfæra "Vinna með gögn" (í Excel 2016, hópur verkfæra "Spá"). Þá opnast smá valmynd. Í því veljum við stöðu "Gögn töflu ...".

Smá gluggi opnar, sem heitir "Gögn töflu". Eins og þú sérð hefur það tvö svið. Þar sem við vinnum með einum breytu þurfum við aðeins einn af þeim. Þar sem breytingarnar okkar eiga sér stað í dálkum munum við nota svæðið "Staðgengill gildi með dálkum í". Við setjum bendilinn þarna og smelltu síðan á reitinn í upphafsgögnum sem inniheldur núverandi gildi prósentunnar. Eftir að hnit frumunnar birtist í reitnum, smelltu á hnappinn "OK".

Tækið reiknar út og fyllir allt borðið með gildum sem svara til mismunandi vaxtastigs valkosta. Ef þú setur bendilinn í hvaða þætti sem er í þessum borði, geturðu séð að formúluborðið birtir ekki reglulega útreikningsformúlu en sérstakan formúlu sem ekki er að brjóta. Það er ekki lengur hægt að breyta gildum í einstökum frumum. Eyða útreikningarniðurstöðum getur aðeins verið allt saman, og ekki sérstaklega.

Að auki má taka fram að verðmæti mánaðarlegrar greiðslna á 12,5% á ári, fengin með því að beita leitarnetinu, samsvarar gildinu við sama vexti sem við fengum með því að beita virkni PMT. Þetta reynir enn einu sinni réttmæti útreikningsins.

Eftir að hafa sýnt þessa töfluflokki ætti að segja að eins og við sjáum aðeins 9,5% á ári er viðunandi mánaðarlegt greiðslustig (minna en 29.000 rúblur) náð.

Lexía: Útreikningur á lífeyrisgreiðslunni í Excel

Aðferð 2: Notaðu tól með tveimur breytum

Auðvitað er mjög erfitt, ef það er raunhæft, að finna banka sem gefa út lán í 9,5% á ári. Þess vegna, láttu okkur sjá hvaða valkostir það er að fjárfesta í viðunandi stigi mánaðarlegrar greiðslu fyrir ýmsar samsetningar annarra breytinga: stærð lánshlutans og lánstímann. Á sama tíma er vaxtastigið óbreytt (12,5%). Tólið mun hjálpa okkur með þetta verkefni. "Gögn töflu" með tveimur breytum.

Teiknaðu nýja töflukerfi. Nú verður tímasetningin tilgreind í dálkunum (frá 2 allt að 6 ár í mánuðum í einu ársfjórðungi) og í röðum - stærð lánshópsins (frá 850000 allt að 950000 rúblur í þrepum 10000 rúblur). Í þessu tilfelli er mikilvægt að fruman sem útreikningsformúlan er staðsett (í okkar tilviki PMT), staðsett á landamærum róður- og dálkheitum. Án þessa ástands mun tólið ekki virka þegar tveir breytur eru notaðar.

Veldu síðan allt borðvalið, þar á meðal nöfnin á dálkunum, röðum og reitnum með formúlunni PMT. Farðu í flipann "Gögn". Eins og í fyrri tíma, smelltu á hnappinn. "Hvað-ef" greiningí hópi verkfæra "Vinna með gögn". Í skránni sem opnast skaltu velja hlutinn "Gögn töflu ...".

Verkfærið byrjar. "Gögn töflu". Í þessu tilfelli þurfum við báðar reiti. Á sviði "Staðgengill gildi með dálkum í" Við tilgreinir hnit frumunnar sem inniheldur lánstíma í aðalgögnum. Á sviði "Staðgengill gildi með raðum í" tilgreinið heimilisfang frumunnar í upphaflegu breyturunum sem innihalda gildi líkamans lánsins Eftir öll gögnin eru færð inn. Við smellum á hnappinn "OK".

Forritið framkvæmir útreikninginn og fyllir borðsviðið með gögnum. Á skurðpunktum raða og dálka er nú hægt að fylgjast með því hvernig nákvæmlega mánaðarleg greiðsla verður með samsvarandi fjárhæð árlegra vaxta og tiltekins tímabils.

Eins og þú getur séð, alveg fullt af gildum. Til að leysa önnur vandamál kann að vera enn meira. Til þess að gera framleiðsluna af niðurstöðum sýnilegri og strax ákveðið hvaða gildi ekki fullnægja tilteknu ástandinu geturðu notað verkfæri til visualization. Í okkar tilviki verður skilyrt formatting. Veldu allar gildin á töfluvalinu, að undanskilinni röð og dálkahausum.

Færa í flipann "Heim" og smelltu á táknið "Skilyrt snið". Það er staðsett í verkfærakistunni. "Stíll" á borði. Í valmyndinni sem opnast skaltu velja hlutinn "Reglur um val á klefi". Í viðbótarlistanum smelltu á stöðu "Minna ...".

Eftir þetta opnast glugganum um skilyrt formatting. Í vinstri reitnum tilgreinum við gildi, minna en sem frumurnar verða valdar. Eins og við munum, erum við ánægðir með ástandið þar sem mánaðarleg greiðsla lánsins verður minni 29000 rúblur. Sláðu inn þetta númer. Í hægri reitnum er hægt að velja lit á valinu, þótt þú getir skilið það sjálfgefið. Þegar allar nauðsynlegar stillingar hafa verið gerðar skaltu smella á hnappinn. "OK".

Eftir það munu allir frumur sem meta gildi í samræmi við ofangreind skilyrði verða auðkenndar í lit.

Eftir að borðið hefur verið metið geturðu dregið nokkrar ályktanir. Eins og þú getur séð með núverandi lánstíma (36 mánuðir), til þess að fjárfesta í ofangreindri tilgreindri mánaðarlegu greiðslu, þurfum við að taka lán sem er ekki meira en 8.600.000,00 rúblur, það er 40.000 minna en upphaflega áætlað.

Ef við ætlum enn að taka lán að fjárhæð 900.000 rúblur, þá ætti lánstíminn að vera 4 ár (48 mánuðir). Aðeins í þessu tilfelli mun upphæð mánaðarlegrar greiðslna ekki fara yfir tilgreind mörk 29.000 rúblur.

Þannig að nýta sér þessa töflu og greina kostir og gallar hvers valkostar, getur lántakandi ákveðið ákveðinn ákvörðun um útlán og valið þann möguleika sem best hentar þörfum hans.

Að sjálfsögðu er hægt að nota útlitstöfluna ekki aðeins til að reikna út lánsvalkostir, heldur einnig til að leysa mörg önnur vandamál.

Lexía: Skilyrt snið í Excel

Almennt skal tekið fram að útlitstaflan er mjög gagnlegt og tiltölulega einfalt tól til að ákvarða niðurstöðu ýmissa samsetningar breytinga. Með því að beita skilyrt formatting ásamt því geturðu einnig séð þær upplýsingar sem berast.