Gagnatafla í Microsoft Excel

Oft þarf að reikna út lokaniðurstöðuna fyrir ýmsar samsetningar innsláttargagna. Þannig mun notandinn geta lagt mat á alla mögulega valkosti fyrir aðgerðir, valið þá sem hafa árangur af samspili fullnægir honum og að lokum, valið besta valkostinn. Til að framkvæma þetta verkefni í Excel er sérstakt tól - „Gagnatafla“ (Skiptatöflu) Við skulum komast að því hvernig á að nota það til að klára ofangreindar sviðsmyndir.

Lestu einnig: Val á breytum í Excel

Notar gagnatöflu

Hljóðfæri „Gagnatafla“ Henni er ætlað að reikna útkomuna fyrir mismunandi afbrigði af einni eða tveimur skilgreindum breytum. Eftir útreikning birtast allir mögulegir valkostir í formi töflu, sem kallast fylki þáttagreiningar. „Gagnatafla“ átt við hóp verkfæra „Hvað ef greining“, sem er settur á borðið í flipanum „Gögn“ í blokk „Vinna með gögn“. Fyrir Excel 2007 var þetta tól kallað Skiptatöflu, sem endurspeglaði jafnvel nákvæmari kjarna þess en núverandi nafn.

Hægt er að nota flettitöfluna í mörgum tilvikum. Til dæmis er dæmigerður valkostur þegar þú þarft að reikna fjárhæð mánaðarlegrar lánsgreiðslu fyrir mismunandi afbrigði af skuldfærslutímabilinu og lánsupphæðinni, eða skuldfærslutímabilinu og vöxtum. Einnig er hægt að nota þetta tól við greiningar á líkönum af fjárfestingarverkefnum.

En þú ættir einnig að vera meðvitaður um að óhófleg notkun þessa tóls getur leitt til hemlunarkerfa, þar sem gögn eru endurtekin. Þess vegna er mælt með því í litlum borðröð að leysa svipuð vandamál ekki að nota þetta tól heldur nota formúluafritun með áfyllingarmerkinu.

Rökstudd umsókn „Gagnatöflur“ er aðeins í stórum töflu sviðum, þegar afritun uppskrift getur tekið mikinn tíma og meðan á aðferðinni stendur aukast líkurnar á að gera mistök. En í þessu tilfelli er mælt með því að slökkva á sjálfvirkri endurútreikningi á formúlum á bilinu skiptitöflunnar til að forðast óþarfa álag á kerfið.

Helsti munurinn á mismunandi notkun gagnataflsins er fjöldi breytna sem taka þátt í útreikningnum: ein breyting eða tvær.

Aðferð 1: notaðu tólið með einni breytu

Við skulum strax skoða möguleikann þegar gagnataflan er notuð með einu breytilegu gildi. Taktu dæmigerðasta útlánadæmið.

Svo sem stendur er okkur boðið upp á eftirfarandi lánaskilyrði:

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

Greiðslur eiga sér stað í lok greiðslutímabils (mánaðar) samkvæmt lífeyriskerfinu, það er að segja í jöfnum hlut. Á sama tíma, í byrjun alls lánstímans, er verulegur hluti greiðslna vaxtagreiðslur, en þegar líkaminn skreppur saman lækka vaxtagreiðslur og endurgreiðslufjárhæð líkamans sjálfs hækkar. Heildargreiðsla, eins og áður segir, er óbreytt.

Nauðsynlegt er að reikna út hver fjárhæð mánaðarlega greiðslunnar verður, þ.mt endurgreiðsla lánastofnunar og vaxtagreiðslur. Til þess hefur Excel rekstraraðila PMT.

PMT tilheyrir flokknum fjárhagslegum aðgerðum og verkefni þess er að reikna út mánaðarlega lánagreiðslu af tegundinni lífeyri út frá fjárhæð lánastofnunar, lánstíma og vöxtum. Setningafræði þessarar aðgerðar er kynnt sem

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

Bjóða - rök sem ákvarða vexti lánagreiðslna. Vísirinn er stilltur fyrir tímabilið. Útborgunartímabil okkar er jafnt í mánuði. Því ætti að skipta árshlutfallinu 12,5% með fjölda mánaða á ári, það er 12.

„Nper“ - rök sem ákvarðar fjölda tímabila fyrir allan lánstímann. Í dæminu 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ök sem ákvarða núvirði lánsins, það er að segja um stærð lánastofnunar við útgáfu þess. Í okkar tilviki er þessi tala 900.000 rúblur.

„BS“ - rök sem sýna stærð lánastofnunar þegar full greiðsla er gerð. Auðvitað, þessi vísir verður jafnt og núll. Þessi rök eru valkvæð. Ef þú sleppir því er gert ráð fyrir að það sé jafnt og talan „0“.

„Gerð“ - einnig valfrjáls rök. Hann tilkynnir hvenær nákvæmlega greiðslan fari fram: í upphafi tímabils (breytu - "1") eða í lok tímabilsins (breytu - "0") Eins og við munum er greiðsla okkar gerð í lok almanaksmánaðar, það er að gildi þessarar röksemdafærslu verður jafnt og "0". En miðað við þá staðreynd að þessi vísir er ekki skylda, og sjálfgefið, ef hann er ekki notaður, er gildið gefið í skyn að það sé jafnt "0", í dæminu sem tilgreint er, má sleppa því að öllu leyti.

1. Svo höldum við áfram við útreikninginn. Veldu hólf á blaði þar sem reiknað gildi verður birt. Smelltu á hnappinn „Setja inn aðgerð“.



2. Byrjar upp Lögun töframaður. Við flytjum í flokknum "Fjárhagslegt", veldu nafnið af listanum „PLT“ og smelltu á hnappinn „Í lagi“.



3. Í framhaldi af þessu er rifrunarglugginn fyrir ofangreindan virka virkur.

   Settu bendilinn í reitinn Bjóða, eftir það smellum við á reitinn á blaði með verðmæti árlegra vaxta. Eins og þú sérð eru hnit þess strax birt á þessu sviði. En eins og við munum þurfum við mánaðarlega taxta og þess vegna deilum við niðurstöðunni um 12 (/12).

   Á sviði „Nper“ á sama hátt og við komum inn í hnit frumanna á lánstímanum. Í þessu tilfelli þarftu ekki að deila neinu.

   Á sviði Ps þú þarft að tilgreina hnit frumunnar sem inniheldur gildi lánastofnunarinnar. Við gerum það. Við setjum líka skilti fyrir framan sýnd hnit "-". Staðreyndin er sú að fallið PMT sjálfgefið gefur það lokaniðurstöðuna með neikvæðu merki, með réttu miðað við mánaðarlegt tap á útborgun lána. En til að skýra beitingu gagnatöflunnar þurfum við þessa tölu að vera jákvæð. Þess vegna setjum við merki mínus áður en eitt af aðgerðarrökum. Margföldun er þekkt mínus á mínus í lokin gefur plús.

   Inn á reitina „Bs“ og „Gerð“ gögn eru alls ekki færð inn. Smelltu á hnappinn „Í lagi“.



4. Eftir það reiknar rekstraraðili út og birtir niðurstöðuna af heildar mánaðarlegu greiðslunni í fyrirfram tilgreindum reit - 30108,26 rúblur. En vandamálið er að lántakandi getur borgað að hámarki 29.000 rúblur á mánuði, það er að hann ætti annað hvort að finna banka sem býður upp á skilyrði með lægri vöxtum, eða lækka lánastofnunina, eða hækka lánstímann. Uppflettitaflan hjálpar okkur að reikna út hina ýmsu valkosti.



5. Notaðu fyrst leitartöfluna með einni breytu. Við skulum sjá hvernig fjárhæð lögboðinna mánaðarlegra greiðslna mun breytast með ýmsum afbrigðum af árlegum taxta, frá og með 9,5% á ári og lýkur 12,5% á ári í þrepum 0,5%. Öll önnur skilyrði eru óbreytt. Við teiknum töflu svið, nöfn dálka sem munu samsvara ýmsum breytingum á vöxtum. Með þessari línu „Mánaðarlegar greiðslur“ láta eins og það er. Fyrsta fruman hennar ætti að innihalda formúluna sem við reiknuðum út fyrr. Fyrir frekari upplýsingar er hægt að bæta við línum „Heildarlánafjárhæð“ og „Heildaráhugi“. Dálkurinn sem útreikningurinn er í er gerður án haus.



6. Næst reiknum við út heildarlánsfjárhæðina við núverandi aðstæður. Veldu fyrsta reitinn í röðinni til að gera þetta „Heildarlánafjárhæð“ og margfalda innihald frumanna „Mánaðarleg greiðsla“ og „Lánstímabil“. Eftir það skaltu smella á hnappinn Færðu inn.



7. Til að reikna heildarupphæð vaxta við núverandi aðstæður drögum við álíka fjárhæð lánastofnunar frá heildarlánafjárhæðinni. Til að birta niðurstöðuna á skjánum, smelltu á hnappinn Færðu inn. Þannig fáum við þá upphæð sem við borgum of mikið við endurgreiðslu lánsins.



8. Nú er kominn tími til að beita tólinu „Gagnatafla“. Við veljum allan borðreitinn, nema röð nöfn. Eftir það skaltu fara á flipann „Gögn“. Smelltu á hnappinn á borði „Hvað ef greining“sem er staðsett í verkfærahópnum „Vinna með gögn“ (í Excel 2016, hóp verkfæra „Spá“) Þá opnast lítill matseðill. Í henni veljum við stöðu „Gagnatafla ...“.



9. Lítill gluggi opnast sem kallast „Gagnatafla“. Eins og þú sérð hefur það tvo reiti. Þar sem við vinnum með einni breytu þurfum við aðeins eina af þeim. Þar sem við breytum breytunni dálki eftir dálki notum við svæðið Settu gildi dálks í stað. Settu bendilinn þar og smelltu síðan á reitinn í upprunalegu gagnapakkanum sem inniheldur núverandi prósentu. Smelltu á hnappinn eftir að hnit frumanna birtast á þessu sviði „Í lagi“.



10. Tólið reiknar út og fyllir allt töflusviðið með gildi sem samsvara mismunandi valkostum fyrir vextina. Ef þú setur bendilinn í einhvern þátt á þessu töflusvæði geturðu séð að formúlulínan birtir ekki venjulega formúlu til að reikna út greiðsluna, heldur sérstaka formúlu fyrir órjúfanlega fylki. Það er, það er nú ómögulegt að breyta gildunum í einstökum frumum. Þú getur eingöngu eytt útreikningsniðurstöðum öllum saman, en ekki sérstaklega.

Að auki geturðu séð að mánaðarleg greiðsla 12,5% á ári sem fæst vegna beitingar á uppflettitöflu samsvarar verðmæti fyrir sömu upphæð vaxta og við fengum með því að beita aðgerðinni PMT. Þetta sannar enn og aftur réttmæti útreikningsins.

Eftir að hafa greint þessa töfluúrval, þá skal það segja að eins og þú sérð, aðeins með hlutfallinu 9,5% á ári fáum við viðunandi mánaðarlegt greiðslustig (innan við 29.000 rúblur).

Lexía: Útreikningur á lífeyri í Excel

Aðferð 2: notaðu tólið með tveimur breytum

Að finna um þessar mundir banka sem gefa út lán á 9,5% á ári er auðvitað mjög erfitt, ef ekki ómögulegt. Þess vegna munum við sjá hvaða möguleikar eru fyrir hendi til að fjárfesta í viðunandi mánaðarlegri greiðslu fyrir ýmsar samsetningar af öðrum breytum: stærð lánastofnunar og lánstíma. Í þessu tilfelli verða vextirnir óbreyttir (12,5%). Tól hjálpar okkur við að leysa þennan vanda. „Gagnatafla“ að nota tvær breytur.

1. Við teiknum nýja borðreit. Nú í dálkinum verða nöfn lánsins tilgreind (frá kl 2 áður 6 ár í mánuði í þrepum eins árs), og í línum - stærð lánastofnunar (frá 850000 áður 950000 rúblur í þrepum 10000 rúblur). Í þessu tilfelli er forsenda þess að fruman sem reikniformúlan er í (í okkar tilfelli PMT), staðsett á jörðu nafna röð og dálka. Án þessa skilyrðis mun verkfærið ekki virka þegar tvær breytur eru notaðar.



2. Veldu síðan allt töflabilið sem myndast, þar á meðal nöfn dálka, línur og reit með formúlunni PMT. Farðu í flipann „Gögn“. Smellið á hnappinn eins og í fyrra skiptið „Hvað ef greining“, í verkfærahópnum „Vinna með gögn“. Veldu á listanum sem opnast „Gagnatafla ...“.



3. Verkfæraglugginn byrjar „Gagnatafla“. Í þessu tilfelli þurfum við báða reitina. Á sviði Settu gildi dálks í stað tilgreinið hnit frumunnar sem inniheldur lánstímann í frumgögnum. Á sviði „Skiptu gildi röð fyrir röð í“ tilgreinið heimilisfang klefans í upphafsbreytunum sem innihalda gildi lánastofnunarinnar. Eftir að öll gögn eru færð inn. Smelltu á hnappinn „Í lagi“.



4. Forritið framkvæmir útreikninginn og fyllir töflusviðið með gögnum. Á gatnamótum lína og dálka er nú hægt að fylgjast með hver nákvæmlega mánaðarleg greiðsla verður, með samsvarandi fjárhæð árlegra vaxta og tilgreindum lánstíma.



5. Eins og þú sérð eru mörg gildi. Til að leysa önnur vandamál geta verið jafnvel fleiri. Þess vegna, til að gera afköst niðurstaðna sýnilegri og strax ákvarða hvaða gildi fullnægja ekki tilteknu ástandi, getur þú notað sjónrænt verkfæri. Í okkar tilviki verður þetta skilyrt snið. Við veljum öll gildi töflusviðsins, fyrir utan röð og dálkfyrirsagnir.



6. Færðu á flipann „Heim“ og smelltu á táknið Skilyrt snið. Það er staðsett í verkfærakassanum. Stílar á segulbandinu. Veldu í valmyndinni sem opnast Reglur um val á klefi. Smelltu á stöðuna í viðbótarlistanum "Minna ...".



7. Í framhaldi af þessu opnast skilyrðisstillingarglugginn. Tilgreindu gildi vinstra reitsins en frumurnar verða valdar. Eins og við munum erum við ánægð með það skilyrði að mánaðarleg lánagreiðsla verði minni en 29000 rúblur. Við sláum inn þetta númer. Í hægri reitnum geturðu valið hápunktarlitinn, þó að það sé sjálfgefið skilið það eftir. Eftir að allar nauðsynlegar stillingar hafa verið slegnar inn, smelltu á hnappinn „Í lagi“.



8. Eftir það verða allar frumur sem gildi samsvara ofangreindu ástandi auðkenndar.

Þegar við höfum greint töfluúrvalið getum við dregið nokkrar ályktanir. Eins og þú sérð með núverandi lánstíma (36 mánuðir), til að fjárfesta í tilgreindri upphæð mánaðarlega greiðslunnar, verðum við að taka lán sem er ekki meira en 860000,00 rúblur, það er 40.000 minna en upphaflega var áætlað.

Ef við ætlum okkur samt að taka 900.000 rúblur lán, þá ætti lánstíminn að vera 4 ár (48 mánuðir). Aðeins í þessu tilfelli mun mánaðarleg greiðsla ekki fara yfir staðfest mörk 29.000 rúblur.

Þannig að með því að nota þessa töflufeld og greina kosti og galla hvers valréttar, getur lántakandi tekið sérstaka ákvörðun um skilmála lánsins, valið viðeigandi valkost úr öllum mögulegum.

Auðvitað er hægt að nota flettitöfluna ekki aðeins til að reikna út lánamöguleika, heldur einnig til að leysa mörg önnur vandamál.

Lexía: Skilyrt snið í Excel

Almennt er rétt að taka það fram að uppflettitöflan er mjög gagnlegt og tiltölulega einfalt tæki til að ákvarða útkomuna fyrir ýmsar samsetningar breytna. Með því að nota skilyrt snið á sama tíma, að auki, geturðu séð upplýsingarnar sem berast.