Yn eithaf aml, mae angen i chi gyfrifo'r canlyniad terfynol ar gyfer cyfuniadau amrywiol o ddata mewnbwn. Felly, bydd y defnyddiwr yn gallu gwerthuso'r holl opsiynau posibl ar gyfer gweithredoedd, dewis y rhai y mae eu canlyniadau rhyngweithio yn ei fodloni, ac, yn olaf, dewis yr opsiwn mwyaf optimaidd. Yn Excel, i gyflawni'r dasg hon, mae yna offeryn arbennig - "Tabl data" (Tabl Amnewid) Gadewch i ni ddarganfod sut i'w ddefnyddio i gwblhau'r senarios uchod.
Darllenwch hefyd: Dewis paramedr yn Excel
Defnyddio tabl data
Offeryn "Tabl data" Y bwriad yw cyfrifo'r canlyniad ar gyfer amrywiadau amrywiol o un neu ddau newidyn diffiniedig. Ar ôl cyfrifo, mae'r holl opsiynau posibl yn ymddangos ar ffurf tabl, a elwir yn fatrics dadansoddi ffactorau. "Tabl data" yn cyfeirio at grŵp o offer “Beth os dadansoddiad”, sy'n cael ei roi ar y rhuban yn y tab "Data" mewn bloc "Gweithio gyda data". Cyn Excel 2007, galwyd yr offeryn hwn Tabl Amnewid, a oedd hyd yn oed yn adlewyrchu ei hanfod yn fwy cywir na'r enw cyfredol.
Gellir defnyddio'r tabl edrych mewn llawer o achosion. Er enghraifft, opsiwn nodweddiadol yw pan fydd angen i chi gyfrifo swm y taliad benthyciad misol ar gyfer amrywiadau amrywiol o'r cyfnod credydu a swm y benthyciad, neu'r cyfnod credydu a'r gyfradd llog. Hefyd, gellir defnyddio'r offeryn hwn wrth ddadansoddi modelau prosiectau buddsoddi.
Ond dylech hefyd fod yn ymwybodol y gall defnydd gormodol o'r offeryn hwn arwain at frecio system, wrth i ddata gael ei adrodd yn barhaus. Felly, argymhellir mewn araeau bwrdd bach i ddatrys problemau tebyg i beidio â defnyddio'r offeryn hwn, ond i ddefnyddio copïo fformiwla gan ddefnyddio'r marciwr llenwi.
Cais wedi'i gyfiawnhau "Tablau data" dim ond mewn ystodau bwrdd mawr, pan all copïo fformwlâu gymryd llawer o amser, ac yn ystod y weithdrefn ei hun mae'r tebygolrwydd o wneud camgymeriadau yn cynyddu. Ond yn yr achos hwn, argymhellir analluogi ailgyfrifiad fformwlâu yn awtomatig yn ystod y tabl amnewid, er mwyn osgoi llwyth gormodol ar y system.
Y prif wahaniaeth rhwng gwahanol ddefnyddiau'r tabl data yw nifer y newidynnau sy'n rhan o'r cyfrifiad: un newidyn neu ddau.
Dull 1: defnyddiwch yr offeryn gydag un newidyn
Ar unwaith, gadewch i ni edrych ar yr opsiwn pan ddefnyddir y tabl data gydag un gwerth amrywiol. Cymerwch yr enghraifft fenthyca fwyaf nodweddiadol.
Felly, ar hyn o bryd rydym yn cael cynnig yr amodau benthyciad canlynol:
- Tymor y benthyciad - 3 blynedd (36 mis);
- Swm y benthyciad - 900,000 rubles;
- Cyfradd llog - 12.5% y flwyddyn.
Mae taliadau'n digwydd ar ddiwedd y cyfnod talu (mis) yn ôl y cynllun blwydd-dal, hynny yw, mewn cyfranddaliadau cyfartal. Ar yr un pryd, ar ddechrau tymor cyfan y benthyciad, rhan sylweddol o daliadau yw taliadau llog, ond wrth i'r corff grebachu, mae taliadau llog yn lleihau, ac mae swm ad-daliad y corff ei hun yn cynyddu. Mae cyfanswm y taliad allan, fel y soniwyd uchod, yn aros yr un fath.
Mae angen cyfrifo swm y taliad misol, gan gynnwys ad-dalu'r corff benthyciadau a thaliadau llog. Ar gyfer hyn, mae gan Excel weithredwr PMT.
PMT yn perthyn i'r grŵp o swyddogaethau ariannol a'i dasg yw cyfrifo'r taliad benthyciad math blwydd-dal misol yn seiliedig ar swm y corff benthyciad, tymor y benthyciad a'r gyfradd llog. Cyflwynir cystrawen y swyddogaeth hon fel
= PLT (cyfradd; nper; ps; bs; math)
Bid - dadl sy'n pennu cyfradd llog taliadau credyd. Mae'r dangosydd wedi'i osod ar gyfer y cyfnod. Mae ein cyfnod talu yn hafal i fis. Felly, dylid rhannu'r gyfradd flynyddol o 12.5% â nifer y misoedd mewn blwyddyn, hynny yw, 12.
"Nper" - dadl sy'n pennu nifer y cyfnodau ar gyfer tymor cyfan y benthyciad. Yn ein enghraifft ni, mis yw'r cyfnod, a thymor y benthyciad yw 3 blynedd neu 36 mis. Felly, bydd nifer y cyfnodau yn gynnar yn 36.
"PS" - dadl sy'n pennu gwerth presennol y benthyciad, hynny yw, maint y corff benthyciad ar adeg ei gyhoeddi. Yn ein hachos ni, y ffigur hwn yw 900,000 rubles.
"BS" - dadl yn nodi maint y corff benthyca ar adeg y taliad llawn. Yn naturiol, bydd y dangosydd hwn yn hafal i sero. Mae'r ddadl hon yn ddewisol. Os ydych chi'n ei hepgor, tybir ei fod yn hafal i'r rhif "0".
"Math" - dadl ddewisol hefyd. Mae'n cyhoeddi pryd yn union y bydd y taliad yn cael ei wneud: ar ddechrau'r cyfnod (paramedr - "1") neu ar ddiwedd y cyfnod (paramedr - "0") Fel y cofiwn, gwneir ein taliad ar ddiwedd y mis calendr, hynny yw, bydd gwerth y ddadl hon yn hafal i "0". Ond, o ystyried y ffaith nad yw'r dangosydd hwn yn orfodol, ac yn ddiofyn, os na chaiff ei ddefnyddio, awgrymir bod y gwerth yn gyfartal "0", yna yn yr enghraifft a nodir gellir ei hepgor yn gyfan gwbl.
- Felly, awn ymlaen i'r cyfrifiad. Dewiswch gell ar y ddalen lle bydd y gwerth wedi'i gyfrifo yn cael ei arddangos. Cliciwch ar y botwm "Mewnosod swyddogaeth".
- Yn cychwyn Dewin Nodwedd. Rydym yn symud i'r categori "Ariannol", dewiswch yr enw o'r rhestr "PLT" a chlicio ar y botwm "Iawn".
- Yn dilyn hyn, gweithredir ffenestr dadleuon y swyddogaeth uchod.
Rhowch y cyrchwr yn y maes Bid, ac ar ôl hynny rydym yn clicio ar y gell ar y ddalen gyda gwerth y gyfradd llog flynyddol. Fel y gallwch weld, mae ei gyfesurynnau yn cael eu harddangos ar unwaith yn y maes. Ond, fel rydyn ni'n cofio, mae angen cyfradd fisol arnom, ac felly rydyn ni'n rhannu'r canlyniad â 12 (/12).
Yn y maes "Nper" yn yr un modd rydyn ni'n nodi cyfesurynnau celloedd tymor y benthyciad. Yn yr achos hwn, nid oes angen i chi rannu unrhyw beth.
Yn y maes Ps rhaid i chi nodi cyfesurynnau'r gell sy'n cynnwys gwerth y corff benthyca. Rydyn ni'n ei wneud. Rydym hefyd yn rhoi arwydd o flaen y cyfesurynnau a arddangosir "-". Y gwir yw bod y swyddogaeth PMT yn ddiofyn mae'n rhoi arwydd negyddol i'r canlyniad terfynol, gan ystyried yn gywir y golled fisol o daliad benthyciad. Ond er eglurder cymhwysiad y tabl data, mae angen i'r rhif hwn fod yn gadarnhaol. Felly, rydyn ni'n rhoi arwydd minws cyn un o'r dadleuon swyddogaeth. Mae lluosi yn hysbys minws ymlaen minws yn y diwedd yn rhoi plws.
I mewn i'r caeau "Bs" a "Math" ni chofnodir data o gwbl. Cliciwch ar y botwm "Iawn".
- Ar ôl hynny, mae'r gweithredwr yn cyfrifo ac yn arddangos canlyniad cyfanswm y taliad misol mewn cell a ddynodwyd ymlaen llaw - 30108,26 rubles. Ond y broblem yw bod y benthyciwr yn gallu talu uchafswm o 29,000 rubles y mis, hynny yw, dylai naill ai ddod o hyd i fanc sy'n cynnig amodau gyda chyfradd llog is, neu ostwng y corff benthyca, neu gynyddu'r tymor benthyciad. Bydd y tabl edrych yn ein helpu i ddarganfod yr amrywiol opsiynau.
- Yn gyntaf, defnyddiwch y tabl edrych gydag un newidyn. Dewch i ni weld sut y bydd swm y taliad misol gorfodol yn newid gydag amrywiadau amrywiol o'r gyfradd flynyddol, gan ddechrau o 9,5% y flwyddyn ac yn gorffen 12,5% y flwyddyn mewn cynyddrannau 0,5%. Mae'r holl amodau eraill yn cael eu gadael yn ddigyfnewid. Rydym yn llunio ystod tabl, y bydd enwau ei golofnau yn cyfateb i amrywiadau amrywiol o'r gyfradd llog. Gyda'r llinell hon "Taliadau Misol" gadael fel y mae. Dylai ei gell gyntaf gynnwys y fformiwla a gyfrifwyd gennym yn gynharach. Am fwy o wybodaeth, gallwch ychwanegu llinellau "Cyfanswm y benthyciad" a "Cyfanswm Llog". Gwneir y golofn y lleolir y cyfrifiad heb bennawd.
- Nesaf, rydym yn cyfrifo cyfanswm y benthyciad o dan yr amodau cyfredol. I wneud hyn, dewiswch gell gyntaf y rhes "Cyfanswm y benthyciad" a lluosi cynnwys y celloedd "Taliad misol" a "Tymor benthyciad". Ar ôl hynny, cliciwch ar y botwm Rhowch i mewn.
- I gyfrifo cyfanswm y llog o dan yr amodau cyfredol, rydym yn yr un modd yn tynnu swm y corff benthyciad o gyfanswm y benthyciad. I arddangos y canlyniad ar y sgrin, cliciwch ar y botwm Rhowch i mewn. Felly, rydyn ni'n cael y swm rydyn ni'n ei ordalu wrth ad-dalu'r benthyciad.
- Nawr mae'n bryd defnyddio'r offeryn "Tabl data". Rydym yn dewis yr arae bwrdd gyfan, heblaw am enwau rhes. Ar ôl hynny, ewch i'r tab "Data". Cliciwch y botwm ar y rhuban “Beth os dadansoddiad”sydd wedi'i leoli yn y grŵp offer "Gweithio gyda data" (yn Excel 2016, grŵp o offer "Rhagolwg") Yna mae bwydlen fach yn agor. Ynddo rydym yn dewis swydd "Tabl data ...".
- Mae ffenestr fach yn agor, a elwir "Tabl data". Fel y gallwch weld, mae ganddo ddau faes. Gan ein bod yn gweithio gydag un newidyn, dim ond un ohonynt sydd ei angen arnom. Ers i ni newid y golofn amrywiol yn ôl colofn, byddwn yn defnyddio'r maes Gwerthoedd Colofn Amnewid yn. Gosodwch y cyrchwr yno, ac yna cliciwch ar y gell yn y set ddata wreiddiol sy'n cynnwys y ganran gyfredol. Ar ôl i'r cyfesurynnau celloedd gael eu harddangos yn y maes, cliciwch ar y botwm "Iawn".
- Mae'r offeryn yn cyfrifo ac yn llenwi'r ystod dablau gyfan gyda gwerthoedd sy'n cyfateb i wahanol opsiynau ar gyfer y gyfradd llog. Os ydych chi'n gosod y cyrchwr mewn unrhyw elfen o'r ardal fwrdd hon, gallwch weld nad yw'r bar fformiwla yn arddangos y fformiwla arferol ar gyfer cyfrifo'r taliad, ond fformiwla arbennig ar gyfer arae annatod. Hynny yw, mae bellach yn amhosibl newid y gwerthoedd mewn celloedd unigol. Dim ond gyda'i gilydd y gallwch chi ddileu canlyniadau cyfrifo, ac nid ar wahân.
Yn ogystal, gallwch weld bod y taliad misol ar 12.5% y flwyddyn a gafwyd o ganlyniad i gymhwyso'r tabl edrych yn cyfateb i'r gwerth am yr un faint o log ag a gawsom trwy gymhwyso'r swyddogaeth. PMT. Mae hyn unwaith eto yn profi cywirdeb y cyfrifiad.
Ar ôl dadansoddi'r arae tabl hon, dylid dweud, fel y gallwch weld, dim ond ar gyfradd o 9.5% y flwyddyn y cawn lefel talu misol dderbyniol (llai na 29,000 rubles).
Gwers: Cyfrifo taliad blwydd-dal yn Excel
Dull 2: defnyddiwch yr offeryn gyda dau newidyn
Wrth gwrs, mae'n anodd iawn dod o hyd i fanciau sy'n rhoi benthyciadau ar 9.5% y flwyddyn, os nad yn amhosibl. Felly, byddwn yn gweld pa opsiynau sy'n bodoli i fuddsoddi mewn lefel dderbyniol o daliad misol ar gyfer cyfuniadau amrywiol o newidynnau eraill: maint y corff benthyciadau a thymor y benthyciad. Yn yr achos hwn, bydd y gyfradd llog yn aros yr un fath (12.5%). Wrth ddatrys y broblem hon, bydd offeryn yn ein helpu. "Tabl data" gan ddefnyddio dau newidyn.
- Rydym yn tynnu cyfres bwrdd newydd. Nawr yn y golofn bydd enwau'r term benthyciad (o 2 o'r blaen 6 blynyddoedd mewn misoedd mewn cynyddrannau o flwyddyn), ac mewn llinellau - maint y corff benthyca (o 850000 o'r blaen 950000 rubles mewn cynyddrannau 10000 rubles). Yn yr achos hwn, rhagofyniad yw bod y gell y mae'r fformiwla gyfrifo wedi'i lleoli ynddi (yn ein hachos ni PMT), wedi'i leoli ar ffin enwau rhes a cholofn. Heb yr amod hwn, ni fydd yr offeryn yn gweithio wrth ddefnyddio dau newidyn.
- Yna dewiswch yr ystod tabl gyfan sy'n deillio o hyn, gan gynnwys enwau colofnau, rhesi a chell gyda'r fformiwla PMT. Ewch i'r tab "Data". Fel yr amser blaenorol, cliciwch ar y botwm “Beth os dadansoddiad”, yn y grŵp offer "Gweithio gyda data". Yn y rhestr sy'n agor, dewiswch "Tabl data ...".
- Mae'r ffenestr offer yn cychwyn "Tabl data". Yn yr achos hwn, mae angen y ddau faes arnom. Yn y maes Gwerthoedd Colofn Amnewid yn nodwch gyfesurynnau'r gell sy'n cynnwys term y benthyciad yn y data sylfaenol. Yn y maes "Gwerthoedd amnewid fesul rhes yn" nodwch gyfeiriad cell y paramedrau cychwynnol sy'n cynnwys gwerth y corff benthyca. Ar ôl i'r holl ddata gael ei gofnodi. Cliciwch ar y botwm "Iawn".
- Mae'r rhaglen yn cyflawni'r cyfrifiad ac yn llenwi'r ystod tabl â data. Ar groesffordd rhesi a cholofnau mae bellach yn bosibl arsylwi beth yn union fydd y taliad misol, gyda'r swm cyfatebol o log blynyddol a thymor y benthyciad a nodwyd.
- Fel y gallwch weld, mae yna lawer o werthoedd. I ddatrys problemau eraill, efallai y bydd mwy fyth. Felly, er mwyn gwneud allbwn y canlyniadau yn fwy gweledol a phenderfynu ar unwaith pa werthoedd nad ydynt yn bodloni'r amod a roddir, gallwch ddefnyddio offer delweddu. Yn ein hachos ni, fformatio amodol fydd hwn. Rydym yn dewis holl werthoedd yr ystod tabl, ac eithrio'r penawdau rhes a cholofn.
- Symud i'r tab "Cartref" a chlicio ar yr eicon Fformatio Amodol. Mae wedi'i leoli yn y bloc offer. Arddulliau ar y tâp. Yn y ddewislen sy'n agor, dewiswch Rheolau Dewis Celloedd. Yn y rhestr ychwanegol, cliciwch ar y sefyllfa "Llai ...".
- Yn dilyn hyn, mae'r ffenestr gosodiadau fformatio amodol yn agor. Yn y maes chwith nodwch y gwerth llai na dewisir y celloedd. Fel y cofiwn, rydym yn fodlon â'r amod y bydd y taliad benthyciad misol yn llai na 29000 rubles. Rydyn ni'n nodi'r rhif hwn. Yn y maes cywir, gallwch ddewis y lliw uchafbwynt, er y gallwch ei adael yn ddiofyn. Ar ôl nodi'r holl leoliadau gofynnol, cliciwch ar y botwm "Iawn".
- Ar ôl hynny, tynnir sylw at yr holl gelloedd y mae eu gwerthoedd yn cyfateb i'r cyflwr uchod.
Ar ôl dadansoddi amrywiaeth y tabl, gallwn ddod i rai casgliadau. Fel y gallwch weld, gyda'r tymor benthyciad presennol (36 mis), er mwyn buddsoddi yn y swm a nodwyd o'r taliad misol, mae angen i ni gymryd benthyciad nad yw'n fwy na 860000.00 rubles, hynny yw, 40,000 yn llai na'r hyn a gynlluniwyd yn wreiddiol.
Os ydym yn dal i fwriadu cymryd benthyciad o 900,000 rubles, yna dylai tymor y benthyciad fod yn 4 blynedd (48 mis). Dim ond yn yr achos hwn, ni fydd y taliad misol yn fwy na'r terfyn sefydledig o 29,000 rubles.
Felly, gan ddefnyddio'r arae tabl hon a dadansoddi manteision ac anfanteision pob opsiwn, gall y benthyciwr wneud penderfyniad penodol ar delerau'r benthyciad, gan ddewis yr opsiwn mwyaf addas o bob un posibl.
Wrth gwrs, gellir defnyddio'r tabl edrych nid yn unig i gyfrifo opsiynau credyd, ond hefyd i ddatrys llawer o broblemau eraill.
Gwers: Fformatio Amodol yn Excel
Yn gyffredinol, dylid nodi bod y tabl edrych yn offeryn defnyddiol a chymharol syml iawn ar gyfer pennu'r canlyniad ar gyfer cyfuniadau amrywiol o newidynnau. Gan ddefnyddio fformatio amodol ar yr un pryd, gallwch hefyd ddelweddu'r wybodaeth a dderbynnir.
