Kadalasan, kinakailangang kalkulahin ang huling resulta para sa iba't ibang mga kumbinasyon ng data ng pag-input. Sa gayon, magagawa ng user na suriin ang lahat ng posibleng pagpipilian para sa aksyon, piliin ang mga resulta ng pakikipag-ugnayan na natutugunan sa kanya, at, sa wakas, piliin ang pinakamainam na pagpipilian. Sa Excel, mayroong isang espesyal na tool para sa gawaing ito - "Talaan ng Data" ("Paghahanap ng talahanayan"). Alamin kung paano gamitin ito upang isagawa ang mga pangyayari sa itaas.
Tingnan din ang: Parameter selection sa Excel
Paggamit ng talahanayan ng data
Tool "Talaan ng Data" ito ay dinisenyo upang kalkulahin ang resulta sa iba't ibang mga pagkakaiba-iba ng isa o dalawang tinukoy na mga variable. Pagkatapos ng pagkalkula, ang lahat ng mga posibleng pagpipilian ay lilitaw sa anyo ng isang talahanayan, na tinatawag na matrix ng factor analysis. "Talaan ng Data" ay tumutukoy sa isang pangkat ng mga tool "Ano-kung" pagtatasana kung saan ay nakalagay sa laso sa tab "Data" sa bloke "Paggawa gamit ang data". Bago ang Excel 2007, ang tool na ito ay nagsuot ng pangalan. "Paghahanap ng talahanayan"na mas tumpak na nakalarawan ang kakanyahan nito kaysa sa kasalukuyang pangalan.
Maaaring gamitin ang lookup table sa maraming mga kaso. Halimbawa, ang isang pangkaraniwang opsyon ay kapag kailangan mo upang kalkulahin ang halaga ng isang buwanang pagbabayad ng pautang na may iba't ibang mga pagkakaiba-iba ng kredito at ang halaga ng pautang, o ang kredito at rate ng interes. Ang tool na ito ay maaari ring gamitin kapag sinusuri ang mga modelo ng proyekto sa pamumuhunan.
Ngunit dapat mo ring malaman na ang labis na paggamit ng tool na ito ay maaaring humantong sa sistema ng pagpepreno, dahil ang data ay patuloy na muling pagkalkula. Samakatuwid, inirerekomenda na huwag gamitin ang tool na ito sa mga maliliit na tabular arrays para sa paglutas ng mga katulad na problema, ngunit upang ilapat ang pagkopya ng mga formula gamit ang marker ng fill.
Ang pinatutunayan na aplikasyon "Mga Table ng Data" ay lamang sa mga malalaking hanay ng mga tabular, kapag ang mga kopya ng mga formula ay maaaring tumagal ng isang malaking halaga ng oras, at sa panahon ng pamamaraan mismo ang posibilidad ng mga error ay tumaas. Ngunit kahit na sa kasong ito, inirerekumenda na huwag paganahin ang awtomatikong muling pagkalkula ng mga formula sa saklaw ng lookup table, upang maiwasan ang hindi kinakailangang pag-load sa system.
Ang pangunahing pagkakaiba sa pagitan ng iba't ibang paggamit ng talahanayan ng data ay ang bilang ng mga variable na kasangkot sa pagkalkula: isang variable o dalawa.
Paraan 1: gamitin ang tool na may isang variable
Agad na isaalang-alang natin ang opsyon kapag ang isang talahanayan ng data ay ginagamit sa isang variable na halaga. Kunin ang pinaka-karaniwang halimbawa ng pagpapahiram.
Kaya, sa kasalukuyan ay inaalok namin ang mga sumusunod na kundisyon ng kredito:
- Panahon ng utang - 3 taon (36 buwan);
- Halaga ng pautang - 900000 rubles;
- Rate ng interes - 12.5% kada taon.
Ang mga pagbabayad ay ginawa sa katapusan ng panahon ng pagbabayad (buwan) gamit ang annuity scheme, samakatuwid nga, sa pantay na pagbabahagi. Kasabay nito, sa simula ng buong panahon ng pautang, ang pagbabayad ng interes ay bumubuo ng isang makabuluhang bahagi ng mga pagbabayad, ngunit habang ang katawan ay lumiit, bumaba ang mga pagbabayad ng interes, at ang halaga ng pagbabayad ng katawan mismo ay nagdaragdag. Ang kabuuang pagbabayad, gaya ng nabanggit sa itaas, ay nananatiling hindi nagbabago.
Kinakailangan upang kalkulahin kung ano ang magiging halaga ng buwanang kabayaran, na kinabibilangan ng pagbabayad ng katawan ng pautang at mga pagbabayad ng interes. Para sa mga ito, Excel ay may isang operator PMT.
PMT Ito ay kabilang sa isang pangkat ng mga pinansiyal na pag-andar at ang gawain ay upang makalkula ang buwanang pagbabayad ng pautang ng uri ng kinikita sa isang taon batay sa halaga ng katawan ng pautang, ang term loan at ang rate ng interes. Ang syntax para sa function na ito ay ang mga sumusunod.
= PMT (rate; nper; ps; bs; uri)
"Bet" - Ang argumento sa pagtukoy sa interes rate ng mga pagbabayad ng credit. Ang tagapagpahiwatig ay nakatakda para sa panahon. Ang aming payout period ay isang buwan. Samakatuwid, ang taunang rate ng 12.5% ay dapat ibagsak sa bilang ng mga buwan sa isang taon, iyon ay, 12.
"Kper" - Ang argumento na tumutukoy sa bilang ng mga panahon para sa buong panahon ng utang. Sa aming halimbawa, ang panahon ay isang buwan, at ang panahon ng pautang ay 3 taon o 36 na buwan. Kaya, ang bilang ng mga panahon ay magiging maaga 36.
"PS" - ang argumento na tumutukoy sa kasalukuyang halaga ng utang, ibig sabihin, ito ay ang sukat ng katawan ng pautang sa panahon ng pagpapalabas nito. Sa aming kaso, ang pigura na ito ay 900,000 rubles.
"BS" - isang argument na nagpapahiwatig ng sukat ng katawan ng pautang sa panahon ng buong pagbabayad nito. Natural, ang indicator na ito ay magiging katumbas ng zero. Ang argument na ito ay opsyonal. Kung laktawan mo ito, ipinapalagay na ito ay katumbas ng numero "0".
"Uri" - din opsyonal na argumento. Nagpapaalam siya tungkol sa kung kailan gagawin ang pagbabayad: sa simula ng panahon (parameter - "1") o sa dulo ng panahon (parameter - "0"). Habang naaalala natin, ang pagbabayad ay ginawa sa katapusan ng buwan ng kalendaryo, ibig sabihin, ang halaga ng argument na ito ay magiging katumbas ng "0". Ngunit, kung ang tagapagpahiwatig na ito ay hindi sapilitan, at bilang default, kung hindi ito ginagamit, ang halaga ay ipinapalagay na "0", pagkatapos ay sa tinukoy na halimbawa hindi ito maaaring gamitin sa lahat.
- Kaya, nagpatuloy kami sa pagkalkula. Piliin ang cell sa sheet kung saan ipapakita ang kinakalkula na halaga. Nag-click kami sa pindutan "Ipasok ang pag-andar".
- Nagsisimula Function Wizard. Gawin ang paglipat sa kategorya "Financial", pumili mula sa listahan ng pangalan "PLT" at mag-click sa pindutan "OK".
- Kasunod nito, mayroong isang pag-activate ng mga window ng argumento ng pag-andar sa itaas.
Ilagay ang cursor sa field "Bet"pagkatapos ay mag-click sa cell sa sheet na may halaga ng taunang rate ng interes. Tulad ng makikita mo, ang mga coordinate nito ay agad na ipinapakita sa patlang. Ngunit, sa tandaan namin, kailangan namin ng isang buwanang rate, at sa gayon ay hatiin namin ang resulta sa pamamagitan ng 12 (/12).
Sa larangan "Kper" sa parehong paraan, ipinasok namin ang mga coordinate ng mga credit term na cell. Sa kasong ito, walang kailangang mahati.
Sa larangan "Ps" dapat mong tukuyin ang mga coordinate ng cell na naglalaman ng halaga ng katawan ng kredito. Ginagawa namin ito. Naglagay din kami ng isang senyas sa harap ng mga ipinapakita na mga coordinate. "-". Ang punto ay ang pag-andar PMT sa pamamagitan ng default, binibigyan nito ang pangwakas na resulta ng isang negatibong pag-sign, medyo isinasaalang-alang ang pagbabayad ng buwanang utang isang pagkawala. Ngunit para sa kaliwanagan, kailangan namin ang data table upang maging positibo. Samakatuwid, inilalagay namin ang isang marka "minus" bago ang isa sa mga argumento sa pag-andar. Tulad ng nalalaman, pagpaparami "minus" sa "minus" sa huli ay nagbibigay plus.
Sa mga patlang "Bs" at "Uri" Hindi namin ipinasok ang data sa lahat. Nag-click kami sa pindutan "OK".
- Pagkatapos nito, ang operator ay kinakalkula at ipinapakita sa pre-itinalagang cell ang resulta ng kabuuang buwanang pagbabayad - 30108,26 rubles. Ngunit ang problema ay ang may-utang ay maaaring magbayad ng isang maximum na 29,000 rubles sa isang buwan, iyon ay, dapat siya mahanap ang isang kondisyon na nag-aalok ng bangko na may mas mababang rate ng interes, o bawasan ang pautang katawan, o pahabain ang term loan. Kalkulahin ang iba't ibang mga pagpipilian para sa aksyon ay makakatulong sa amin ang lookup table.
- Upang magsimula, gamitin ang lookup table na may isang variable. Tingnan natin kung paano magkakaiba ang halaga ng ipinag-uutos na buwanang pagbabayad sa iba't ibang mga pagkakaiba-iba sa taunang rate, mula sa 9,5% taunang at nagtatapos 12,5% pa sa hakbang 0,5%. Ang lahat ng iba pang mga kondisyon ay naiwang hindi nabago. Gumuhit ng hanay ng talahanayan, ang mga pangalan ng mga haligi na kung saan ay tumutugma sa iba't ibang mga pagkakaiba-iba ng rate ng interes. Sa linya na ito "Buwanang pagbabayad" iwanan ito. Ang unang cell ay dapat maglaman ng formula na kinakalkula namin nang mas maaga. Para sa karagdagang impormasyon, maaari kang magdagdag ng mga linya "Kabuuang halaga ng pautang" at "Kabuuang Interes". Ang haligi kung saan matatagpuan ang pagkalkula ay tapos na walang header.
- Susunod, tinatantya namin ang kabuuang halaga ng utang sa ilalim ng kasalukuyang mga kondisyon. Upang gawin ito, piliin ang unang cell ng hilera. "Kabuuang halaga ng pautang" at paramihin ang mga nilalaman ng cell "Buwanang pagbabayad" at "Term loan". Pagkatapos na mag-click sa Ipasok.
- Upang makalkula ang kabuuang halaga ng interes sa ilalim ng mga kasalukuyang kondisyon, sa katulad na paraan binabawasan namin ang halaga ng katawan ng pautang mula sa kabuuang halaga ng utang. Upang ipakita ang resulta sa screen mag-click sa pindutan. Ipasok. Sa gayon, nakukuha natin ang halaga na labis nating kabayaran sa pagbabalik ng utang.
- Ngayon ay oras na upang ilapat ang tool. "Talaan ng Data". Piliin ang buong hanay ng talahanayan, maliban sa mga pangalan ng hilera. Matapos na pumunta sa tab "Data". Mag-click sa pindutan sa laso "Ano-kung" pagtatasana kung saan ay inilagay sa isang pangkat ng mga tool "Paggawa gamit ang data" (sa Excel 2016, isang grupo ng mga tool "Pagtataya"). Pagkatapos ay bubukas ang isang maliit na menu. Sa loob nito ay piliin natin ang posisyon "Table ng Data ...".
- Magbubukas ang isang maliit na window, na tinatawag "Talaan ng Data". Tulad ng makikita mo, mayroon itong dalawang larangan. Dahil nagtatrabaho kami sa isang variable, kailangan namin ang isa sa mga ito. Dahil nangyayari ang mga pagbabago sa variable sa mga haligi, gagamitin namin ang patlang "Pinalitan ang mga halaga ayon sa mga haligi sa". Inilalagay namin ang cursor doon, at pagkatapos ay mag-click sa cell sa unang hanay ng data, na naglalaman ng kasalukuyang halaga ng porsyento. Matapos ang mga coordinate ng cell ay ipinapakita sa patlang, mag-click sa pindutan "OK".
- Kinakalkula at ginagamit ng tool ang buong hanay ng talahanayan na may mga halaga na tumutugma sa iba't ibang mga pagpipilian sa rate ng interes. Kung inilalagay mo ang cursor sa anumang elemento ng mga talahanayan na ito, makikita mo na ang formula bar ay hindi nagpapakita ng isang regular na formula sa pagkalkula ng pagbabayad, ngunit isang espesyal na pormula ng isang di-paglabag array. Iyon ay, hindi na posible na baguhin ang mga halaga sa indibidwal na mga selula. Tanggalin ang mga resulta ng pagkalkula ay maaari lamang na magkakasama, at hindi hiwalay.
Bilang karagdagan, maaari itong mapansin na ang halaga ng buwanang pagbabayad sa 12.5% kada taon, na nakuha sa pamamagitan ng pag-aaplay sa lookup table, ay tumutugma sa halaga sa parehong rate ng interes na natanggap namin sa pamamagitan ng paglalapat ng function PMT. Ito ay muling nagpapatunay na ang katumpakan ng pagkalkula.
Matapos suriin ang hanay na ito sa tabla, dapat sabihin na, tulad ng nakikita natin, lamang sa isang rate ng 9.5% bawat taon, ang katanggap-tanggap na antas ng pagbabayad (mas mababa sa 29,000 rubles) ay nakuha.
Aralin: Pagkalkula ng pagbabayad sa annuity sa Excel
Paraan 2: gumamit ng isang tool na may dalawang variable
Siyempre, napakahirap, kung makatotohanan, upang makahanap ng mga bangko na nag-isyu ng mga pautang sa 9.5% bawat taon. Samakatuwid, tingnan natin kung anong mga opsyon ang dapat mamuhunan sa isang katanggap-tanggap na antas ng buwanang pagbabayad para sa iba't ibang mga kumbinasyon ng iba pang mga variable: ang sukat ng katawan ng pautang at ang panahon ng pautang. Sa parehong oras, ang interes rate ay mananatiling hindi nagbabago (12.5%). Ang kasangkapan ay makakatulong sa amin sa gawaing ito. "Talaan ng Data" gamit ang dalawang mga variable.
- Gumuhit ng isang bagong hanay ng talahanayan. Ngayon ang mga kredito na term ay ipapakita sa mga pangalan ng haligi (mula sa 2 hanggang sa 6 taon sa buwan sa mga hakbang ng isang taon), at sa mga hilera - ang sukat ng katawan ng pautang (mula sa 850000 hanggang sa 950000 rubles sa mga palugit 10000 rubles). Sa kasong ito, kinakailangan na ang cell kung saan matatagpuan ang formula ng pagkalkula (sa aming kaso PMT), na matatagpuan sa hangganan ng mga pangalan ng hilera at haligi. Kung wala ang kundisyong ito, hindi gagana ang tool kapag gumagamit ng dalawang variable.
- Pagkatapos ay piliin ang lahat ng nagresultang hanay ng talahanayan, kabilang ang mga pangalan ng mga haligi, mga hilera at ang cell na may pormula PMT. Pumunta sa tab "Data". Tulad ng sa nakaraang oras, mag-click sa pindutan. "Ano-kung" pagtatasasa isang pangkat ng mga tool "Paggawa gamit ang data". Sa listahan na bubukas, piliin ang item "Table ng Data ...".
- Nagsisimula ang window ng tool. "Talaan ng Data". Sa kasong ito, kailangan namin ang parehong larangan. Sa larangan "Pinalitan ang mga halaga ayon sa mga haligi sa" tinutukoy namin ang mga coordinate ng cell na naglalaman ng term loan sa pangunahing data. Sa larangan "Pinalitan ang mga halaga ayon sa mga hilera sa" tukuyin ang address ng cell ng unang mga parameter na naglalaman ng halaga ng katawan ng utang Matapos maipasok ang lahat ng data. Nag-click kami sa pindutan "OK".
- Ang programa ay nagsasagawa ng pagkalkula at pumupuno sa hanay ng talahanayan na may data. Sa intersection ng mga hilera at mga haligi, posible na ngayon upang obserbahan kung paano eksaktong ang buwanang pagbabayad ay, na may isang nararapat na halaga ng taunang interes at isang tinukoy na panahon ng kredito.
- Tulad ng iyong nakikita, medyo maraming mga halaga. Upang malutas ang iba pang mga problema ay maaaring maging higit pa. Samakatuwid, upang makagawa ng output ng mga resulta ng mas visual at agad na matukoy kung aling mga halaga ang hindi nakakatugon sa ibinigay na kondisyon, maaari mong gamitin ang mga visualization tool. Sa aming kaso ito ay magiging kondisyong pag-format. Piliin ang lahat ng mga halaga ng hanay ng talahanayan, hindi kasama ang mga header ng hanay at hanay.
- Ilipat sa tab "Home" at mag-click sa icon "Conditional Formatting". Matatagpuan ito sa toolbox. "Estilo" sa tape. Sa menu na bubukas, piliin ang item "Mga panuntunan para sa pagpili ng cell". Sa karagdagang listahan mag-click sa posisyon "Less ...".
- Kasunod nito, bubukas ang setting ng conditional formatting window. Sa kaliwang larangan ay tinutukoy namin ang halaga, mas mababa kaysa kung saan pinili ang mga cell. Habang naaalala natin, nasiyahan tayo sa kalagayan kung saan mas mababa ang buwanang pagbabayad sa utang 29000 rubles. Ipasok ang numerong ito. Sa kanang patlang posible upang piliin ang kulay ng pagpili, bagaman maaari mong iwanan ito sa pamamagitan ng default. Pagkatapos na maipasok ang lahat ng kinakailangang setting, mag-click sa pindutan. "OK".
- Pagkatapos nito, ang lahat ng mga cell na ang mga halaga ay tumutugma sa kalagayan sa itaas ay mai-highlight sa kulay.
Pagkatapos pag-aralan ang table array, maaari kang gumuhit ng ilang konklusyon. Tulad ng makikita mo, sa kasalukuyang panahon ng pautang (36 na buwan), upang mamuhunan sa nabanggit na halaga ng isang buwanang pagbabayad, kailangan naming kumuha ng utang na hindi hihigit sa 8,600,000.00 rubles, ibig sabihin, 40,000 mas mababa kaysa sa orihinal na pagpaplano.
Kung gusto pa rin naming magbayad sa halagang 900,000 rubles, ang term loan ay dapat na 4 na taon (48 na buwan). Sa kasong ito lamang, ang halaga ng buwanang kabayaran ay hindi lalampas sa itinakdang limitasyon ng 29,000 rubles.
Sa gayon, sinasamantala ang hanay na ito ng pangkat na ito at pinag-aaralan ang mga kalamangan at kahinaan ng bawat opsyon, ang borrower ay maaaring gumawa ng isang tiyak na desisyon sa mga tuntunin ng pagpapahiram, pagpili ng pagpipilian na pinakamahusay na nababagay sa kanyang mga pangangailangan.
Siyempre, ang lookup table ay maaaring gamitin hindi lamang upang makalkula ang mga pagpipilian sa kredito, kundi pati na rin upang malutas ang maraming iba pang mga problema.
Aralin: Conditional Formatting sa Excel
Sa pangkalahatan, dapat tandaan na ang lookup table ay isang napaka-kapaki-pakinabang at medyo simpleng tool para sa pagtukoy ng resulta ng iba't ibang mga kumbinasyon ng mga variable. Sa pamamagitan ng paglalapat ng conditional formatting kasama ito, bilang karagdagan, maaari mong maisalarawan ang natanggap na impormasyon.
