Yn aml iawn, mae'n ofynnol iddo gyfrifo'r canlyniad terfynol ar gyfer gwahanol gyfuniadau o ddata mewnbwn. Felly, bydd y defnyddiwr yn gallu gwerthuso'r holl opsiynau posibl ar gyfer gweithredu, dewis y rhai y mae ei ganlyniad rhyngweithio yn ei fodloni, ac, yn olaf, dewis yr opsiwn gorau posibl. Yn Excel, mae yna offeryn arbennig ar gyfer y dasg hon - "Tabl Data" ("Edrych ar y tabl"). Gadewch i ni ddarganfod sut i'w ddefnyddio i berfformio'r senarios uchod.
Gweler hefyd: Dewis paramedr yn Excel
Defnyddio tabl data
Offeryn "Tabl Data" fe'i cynlluniwyd i gyfrifo'r canlyniad gyda gwahanol amrywiadau o newidynnau diffiniedig un neu ddau. Ar ôl cyfrifo, bydd pob opsiwn posibl yn ymddangos ar ffurf tabl, a elwir yn fatrics o ddadansoddiad ffactor. "Tabl Data" yn cyfeirio at grŵp o offer Dadansoddiad "Beth-os"sy'n cael ei roi ar y rhuban yn y tab "Data" mewn bloc "Gweithio gyda data". Cyn Excel 2007, roedd yr offeryn hwn yn gwisgo enw. "Edrych ar y tabl"bod hyd yn oed yn fwy cywir yn adlewyrchu ei hanfod na'r enw presennol.
Gellir defnyddio'r tabl am-edrych mewn llawer o achosion. Er enghraifft, opsiwn nodweddiadol yw pan fydd angen i chi gyfrifo swm taliad benthyciad misol gydag amrywiadau gwahanol o'r cyfnod credydu a swm y benthyciad, neu'r cyfnod credydu a'r gyfradd llog. Gellir defnyddio'r offeryn hwn hefyd wrth ddadansoddi modelau prosiect buddsoddi.
Ond dylech hefyd fod yn ymwybodol y gall defnyddio'r offeryn hwn yn ormodol arwain at frecio systemau, gan fod data'n cael ei ail-gyfrifo'n gyson. Felly, argymhellir peidio â defnyddio'r offeryn hwn mewn araeau tablau bach ar gyfer datrys problemau tebyg, ond defnyddio copïo fformiwlâu gan ddefnyddio'r marciwr llenwi.
Cymhwysiad wedi'i gyfiawnhau "Tablau Data" mewn ystodau tablau mawr yn unig, wrth gopïo fformiwlâu gall gymryd llawer o amser, ac yn ystod y weithdrefn ei hun, cynyddir y tebygolrwydd o wallau. Ond hyd yn oed yn yr achos hwn, argymhellir analluogi ail-gyfrifo fformiwlâu yn awtomatig yn ystod y tabl am-edrych, er mwyn osgoi llwyth diangen ar y system.
Y prif wahaniaeth rhwng gwahanol ddefnyddiau tabl data yw nifer y newidynnau sy'n gysylltiedig â'r cyfrifiad: un newidyn neu ddau.
Dull 1: defnyddio'r offeryn gydag un newidyn
Yn syth, gadewch i ni ystyried yr opsiwn pan ddefnyddir tabl data gydag un gwerth amrywiol. Cymerwch yr enghraifft fwyaf nodweddiadol o fenthyca.
Felly, ar hyn o bryd cynigir yr amodau credyd canlynol i ni:
- Cyfnod benthyca - 3 blynedd (36 mis);
- Swm y benthyciad - 900000 rubles;
- Cyfradd llog - 12.5% y flwyddyn.
Gwneir taliadau ar ddiwedd y cyfnod talu (mis) gan ddefnyddio'r cynllun blwydd-dal, hynny yw, mewn cyfrannau cyfartal. Ar yr un pryd, ar ddechrau'r cyfnod benthyca cyfan, mae taliadau llog yn rhan sylweddol o'r taliadau, ond wrth i'r corff grebachu, mae taliadau llog yn gostwng, ac mae swm yr ad-daliad o'r corff ei hun yn cynyddu. Mae cyfanswm y taliad, fel y crybwyllwyd uchod, yn aros yr un fath.
Mae angen cyfrifo swm y taliad misol, sy'n cynnwys ad-dalu'r corff benthyciadau a thaliadau llog. Ar gyfer hyn, mae gan Excel weithredwr PMT.
PMT Mae'n perthyn i grŵp o swyddogaethau ariannol a'i dasg yw cyfrifo taliad benthyciad misol y math o flwydd-dal yn seiliedig ar swm y corff benthyca, tymor y benthyciad a'r gyfradd llog. Mae'r gystrawen ar gyfer y swyddogaeth hon fel a ganlyn.
= PMT (cyfradd; nper; ps; bs; type)
"Bet" - Y ddadl sy'n pennu cyfradd llog taliadau credyd. Gosodir y dangosydd ar gyfer y cyfnod. Mae ein cyfnod talu yn fis. Felly, dylid rhannu'r gyfradd flynyddol o 12.5% yn nifer y misoedd mewn blwyddyn, hynny yw, 12.
"Kper" - Y ddadl sy'n pennu nifer y cyfnodau ar gyfer cyfnod cyfan y benthyciad. Yn ein enghraifft ni, mae'r cyfnod yn fis, ac mae'r cyfnod benthyca yn 3 blynedd neu 36 mis. Felly, bydd nifer y cyfnodau yn gynnar yn 36.
"PS" - y ddadl sy'n pennu gwerth presennol y benthyciad, hynny yw, maint y corff benthyciadau ar adeg ei gyhoeddi. Yn ein hachos ni, y ffigur hwn yw 900,000 rubles.
"BS" - dadl sy'n dangos maint y corff benthyciadau ar adeg ei daliad llawn. Yn naturiol, bydd y dangosydd hwn yn hafal i ddim. Mae'r ddadl hon yn ddewisol. Os byddwch yn ei hepgor, tybir ei fod yn hafal i'r rhif "0".
"Math" - hefyd ddadl ddewisol. Mae'n rhoi gwybod pryd y caiff y taliad 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 gofio nad yw'r dangosydd hwn yn orfodol, ac yn ddiofyn, os na chaiff ei ddefnyddio, tybir bod y gwerth "0", yna yn yr enghraifft benodol ni ellir ei defnyddio o gwbl.
- Felly, rydym yn symud ymlaen at y cyfrifiad. Dewiswch y gell ar y daflen lle bydd y gwerth wedi'i gyfrifo yn cael ei arddangos. Rydym yn clicio ar y botwm "Mewnosod swyddogaeth".
- Yn dechrau Dewin Swyddogaeth. Gwnewch y newid i'r categori "Ariannol", dewiswch o'r rhestr yr enw "PLT" a chliciwch ar y botwm "OK".
- Yn dilyn hyn, mae ffenestr dadleuon y swyddogaeth uchod yn actifadu.
Rhowch y cyrchwr yn y maes "Bet"yna cliciwch ar y gell ar y ddalen gyda gwerth y gyfradd llog flynyddol. Fel y gwelwch, caiff ei gyfesurynnau eu harddangos ar unwaith yn y maes. Ond, fel y cofiwn, mae angen cyfradd fisol arnom, ac felly rydym yn rhannu'r canlyniad â 12 (/12).
Yn y maes "Kper" yn yr un modd, byddwn yn cofnodi cyfesurynnau'r celloedd term credyd. Yn yr achos hwn, nid oes angen rhannu dim.
Yn y maes "Ps" rhaid i chi nodi cyfesurynnau'r gell sy'n cynnwys gwerth corff y credyd. Rydym yn ei wneud. Rydym hefyd yn rhoi arwydd o flaen y cyfesurynnau a arddangosir. "-". Y pwynt yw bod y swyddogaeth PMT yn ddiofyn, mae'n rhoi arwydd negyddol i'r canlyniad terfynol, gan ystyried yn deg y taliad benthyciad misol colled. Ond er eglurder, mae angen i'r tabl data fod yn gadarnhaol. Felly, rydym yn rhoi marc "minws" cyn un o'r dadleuon swyddogaeth. Fel sy'n hysbys, lluosi "minws" ymlaen "minws" yn y pen draw yn rhoi plws.
Yn y caeau "Bs" a "Math" Nid ydym yn cofnodi data o gwbl. Rydym yn clicio ar y botwm "OK".
- Wedi hynny, bydd y gweithredwr yn cyfrifo ac arddangos canlyniad y taliad misol ymlaen llaw yn y gell a ddynodwyd ymlaen llaw - 30108,26 rubles. Ond y broblem yw bod y benthyciwr yn gallu talu uchafswm o 29,000 o rubles y mis, hynny yw, dylai naill ai ddod o hyd i fanc sy'n cynnig amodau gyda chyfradd llog is, neu leihau'r corff benthyciadau, neu ymestyn tymor y benthyciad. Cyfrifwch y gwahanol opsiynau ar gyfer gweithredu a fydd yn ein helpu i edrych ar y tabl.
- I ddechrau, defnyddiwch y tabl am-edrych gydag un newidyn. Gadewch i ni weld sut y bydd gwerth y taliad misol gorfodol yn amrywio gyda gwahanol amrywiadau yn y gyfradd flynyddol, yn amrywio o 9,5% blynyddol a diweddglo 12,5% pa gyda cham 0,5%. Mae'r holl amodau eraill yn cael eu gadael heb eu newid. Tynnwch ystod bwrdd, a bydd enwau'r colofnau yn cyfateb i amrywiadau gwahanol 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". Mae'r golofn lle mae'r cyfrifiad wedi'i leoli yn cael ei wneud heb bennawd.
- Nesaf, rydym yn cyfrifo cyfanswm y benthyciad o dan yr amodau presennol. I wneud hyn, dewiswch gell gyntaf y rhes. "Cyfanswm y benthyciad" a lluosi cynnwys y gell "Taliad misol" a "Term Benthyciad". Ar ôl hyn cliciwch ar Rhowch i mewn.
- I gyfrifo cyfanswm y llog o dan yr amodau presennol, mewn ffordd debyg, byddwn yn tynnu gwerth y corff benthyciad o gyfanswm y benthyciad. I arddangos y canlyniad ar y sgrin cliciwch ar y botwm. Rhowch i mewn. Felly, rydym yn cael y swm yr ydym yn ei ordalu wrth ddychwelyd y benthyciad.
- Nawr mae'n amser cymhwyso'r offeryn. "Tabl Data". Dewiswch yr holl haen tabl, ac eithrio'r enwau rhes. Ar ôl hynny ewch i'r tab "Data". Cliciwch ar y botwm ar y rhuban Dadansoddiad "Beth-os"sy'n cael ei roi mewn grŵp o offer "Gweithio gyda data" (yn Excel 2016, grŵp o offer "Rhagolwg"). Yna mae bwydlen fach yn agor. Ynddo dewiswn y sefyllfa "Tabl Data ...".
- Mae ffenestr fach yn agor, a elwir "Tabl Data". Fel y gwelwch, mae ganddo ddau gae. Gan ein bod yn gweithio gydag un newidyn, dim ond un ohonynt sydd ei angen arnom. Gan fod ein newidiadau amrywiol yn digwydd mewn colofnau, byddwn yn defnyddio'r maes "Yn lle gwerthoedd yn ôl colofnau yn". Rydym yn gosod y cyrchwr yno, ac yna'n clicio ar y gell yn y set ddata gychwynnol, sy'n cynnwys gwerth cyfredol y cant. Ar ôl arddangos cyfesurynnau'r gell yn y maes, cliciwch ar y botwm "OK".
- Mae'r offeryn yn cyfrifo ac yn llenwi'r ystod tabl gyfan gyda gwerthoedd sy'n cyfateb i wahanol opsiynau cyfraddau llog. Os ydych chi'n gosod y cyrchwr mewn unrhyw elfen o'r gofod hwn, gallwch weld nad yw'r bar fformiwla yn dangos fformiwla cyfrifo taliadau rheolaidd, ond fformiwla arbennig o amrywiaeth anhysbys. Hynny yw, nid yw bellach yn bosibl newid y gwerthoedd mewn celloedd unigol. Gall dileu'r canlyniadau cyfrifo fod gyda'i gilydd yn unig, ac nid ar wahân.
Yn ogystal, gellir nodi bod gwerth y taliad misol ar 12.5% y flwyddyn, a gafwyd drwy gymhwyso'r tabl am-edrych, yn cyfateb i'r gwerth ar yr un gyfradd llog a gawsom drwy gymhwyso'r swyddogaeth PMT. Mae hyn unwaith eto yn profi cywirdeb y cyfrifiad.
Ar ôl dadansoddi'r casgliad tablau hwn, dylid dweud, fel y gwelwn, dim ond ar gyfradd o 9.5% y flwyddyn, y ceir y lefel taliad misol dderbyniol (llai na 29,000 rubles).
Gwers: Cyfrifo'r taliad blwydd-dal yn Excel
Dull 2: defnyddio offeryn gyda dau newidyn
Wrth gwrs, mae'n anodd iawn, o gwbl realistig, dod o hyd i fanciau sy'n rhoi benthyciadau ar 9.5% y flwyddyn. Felly, gadewch i ni weld pa opsiynau sydd i'w buddsoddi mewn lefel dderbyniol o daliad misol ar gyfer gwahanol gyfuniadau o newidynnau eraill: maint y corff benthyciadau a'r cyfnod benthyca. Ar yr un pryd, ni fydd y gyfradd llog yn newid (12.5%). Bydd yr offeryn yn ein helpu gyda'r dasg hon. "Tabl Data" gan ddefnyddio dau newidyn.
- Tynnwch lun bwrdd newydd. Nawr bydd y term credydau yn cael ei nodi yn yr enwau colofnau (o 2 hyd at 6 blynyddoedd mewn misoedd mewn camau o flwyddyn), ac yn y rhesi - maint y corff benthyciadau (o 850000 hyd at 950000 rubles mewn cynyddrannau 10000 rubles). Yn yr achos hwn, mae'n hanfodol bod y gell lle mae'r fformiwla cyfrifo wedi'i lleoli (yn ein hachos ni PMT), wedi ei leoli ar ffin enwau rhes a cholofn. Heb y cyflwr hwn, ni fydd yr offeryn yn gweithio wrth ddefnyddio dau newidyn.
- Yna dewiswch yr holl ystod tabl sy'n deillio, gan gynnwys enwau'r colofnau, rhesi a'r gell gyda'r fformiwla PMT. Ewch i'r tab "Data". Fel yn yr amser blaenorol, cliciwch ar y botwm. Dadansoddiad "Beth-os"mewn grŵp o offer "Gweithio gyda data". Yn y rhestr sy'n agor, dewiswch yr eitem "Tabl Data ...".
- Mae'r ffenestr offer yn dechrau. "Tabl Data". Yn yr achos hwn, mae angen y ddau faes arnom. Yn y maes "Yn lle gwerthoedd yn ôl colofnau yn" rydym yn nodi cyfesurynnau'r gell sy'n cynnwys tymor y benthyciad yn y data cynradd. Yn y maes "Yn lle gwerthoedd yn ôl rhesi yn" nodi cyfeiriad cell y paramedrau cychwynnol sy'n cynnwys gwerth corff y benthyciad. Ar ôl cofnodi'r holl ddata. Rydym yn clicio ar y botwm "OK".
- Mae'r rhaglen yn cyflawni'r cyfrifiad ac yn llenwi'r ystod tabl gyda data. Ar groesffordd rhesi a cholofnau, mae bellach yn bosibl gweld sut yn union y bydd y taliad misol, gyda swm cyfatebol o log blynyddol a chyfnod credyd penodedig.
- Fel y gwelwch, mae llawer o werthoedd. Er mwyn datrys problemau eraill gall fod hyd yn oed mwy. Felly, er mwyn gwneud allbwn y canlyniadau yn fwy gweledol a phenderfynu ar unwaith pa werthoedd nad ydynt yn bodloni'r amod penodol, gallwch ddefnyddio offer delweddu. Yn ein hachos ni, bydd yn fformatio amodol. Dewiswch holl werthoedd yr ystod tabl, ac eithrio penawdau rhes a cholofn.
- Symudwch i'r tab "Cartref" a chliciwch ar yr eicon "Fformatio Amodol". Mae wedi'i leoli yn y blwch offer. "Arddulliau" ar y tâp. Yn y ddewislen sy'n agor, dewiswch yr eitem "Rheolau ar gyfer dewis celloedd". Yn y rhestr ychwanegol cliciwch ar y sefyllfa "Llai ...".
- Yn dilyn hyn, mae'r ffenestr gosod fformatau amodol yn agor. Yn y maes chwith, rydym yn nodi'r gwerth, llai na'r hyn y caiff y celloedd eu dewis. Fel y cofiwn, rydym yn fodlon â'r cyflwr lle bydd y taliad misol ar y benthyciad yn llai 29000 rubles. Rhowch y rhif hwn. Yn y maes cywir mae'n bosibl dewis lliw'r dewis, er y gallwch ei adael yn ddiofyn. Ar ôl cofnodi'r holl leoliadau gofynnol, cliciwch ar y botwm. "OK".
- Wedi hynny, bydd pob lliw y mae ei werth yn cyfateb i'r amod uchod yn cael ei amlygu mewn lliw.
Ar ôl dadansoddi'r arae tabl, gallwch ddod i rai casgliadau. Fel y gwelwch, gyda'r cyfnod benthyca cyfredol (36 mis), er mwyn buddsoddi yn y swm misol uchod o daliad misol, mae angen i ni gymryd benthyciad nad yw'n fwy na 8,600,000.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 cyfnod y benthyciad fod yn 4 blynedd (48 mis). Dim ond yn yr achos hwn, ni fydd swm y taliad misol yn fwy na'r terfyn sefydledig o 29,000 rubles.
Felly, gan fanteisio ar yr amrywiaeth tablau hon a dadansoddi manteision ac anfanteision pob opsiwn, gall y benthyciwr wneud penderfyniad penodol ar delerau benthyca, gan ddewis yr opsiwn sy'n gweddu orau i'w anghenion.
Wrth gwrs, gellir defnyddio'r tabl am-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 am-edrych yn offeryn defnyddiol iawn a chymharol syml ar gyfer pennu canlyniad gwahanol gyfuniadau o newidynnau. Trwy gymhwyso fformatio amodol gydag ef, yn ogystal, gallwch ddychmygu'r wybodaeth a dderbyniwyd.
