Data Table sa Microsoft Excel

Madalas, kailangan mong kalkulahin ang pangwakas na resulta para sa iba't ibang mga kumbinasyon ng data ng pag-input. Kaya, masuri ng gumagamit ang lahat ng posibleng mga pagpipilian para sa mga aksyon, piliin ang mga na ang mga resulta ng pakikipag-ugnay ay masiyahan sa kanya, at, sa wakas, piliin ang pinakamainam na pagpipilian. Sa Excel, upang maisagawa ang gawaing ito, mayroong isang espesyal na tool - "Talahanayan ng data" (Talahanayan ng Pagpapalit) Alamin natin kung paano gamitin ito upang makumpleto ang mga sitwasyon sa itaas.

Basahin din: Ang pagpili ng Parameter sa Excel

Gamit ang talahanayan ng data

Instrumento "Talahanayan ng data" Ito ay inilaan upang makalkula ang resulta para sa iba't ibang mga pagkakaiba-iba ng isa o dalawang tinukoy na mga variable. Pagkatapos ng pagkalkula, ang lahat ng posibleng mga pagpipilian ay lilitaw sa anyo ng isang talahanayan, na kung saan ay tinatawag na matrix ng pagsusuri ng kadahilanan. "Talahanayan ng data" tumutukoy sa isang pangkat ng mga tool "Paano kung pagsusuri", na nakalagay sa laso sa tab "Data" sa block "Makipagtulungan sa data". Bago ang Excel 2007, tinawag ang tool na ito Talahanayan ng Pagpapalit, na mas tumpak na ipinakita ang kakanyahan nito kaysa sa kasalukuyang pangalan.

Ang mesa ng lookup ay maaaring magamit sa maraming mga kaso. Halimbawa, ang isang tipikal na pagpipilian ay kapag kailangan mong kalkulahin ang halaga ng buwanang pagbabayad ng pautang para sa iba't ibang mga pagkakaiba-iba ng panahon ng kredito at ang halaga ng pautang, o ang panahon ng pag-kredito at rate ng interes. Gayundin, ang tool na ito ay maaaring magamit sa pagsusuri ng mga modelo ng mga proyekto sa pamumuhunan.

Ngunit dapat mo ring magkaroon ng kamalayan na ang labis na paggamit ng tool na ito ay maaaring humantong sa pagpepreno ng system, dahil ang data ay patuloy na muling isinalaysay. Samakatuwid, inirerekomenda ito sa maliit na talahanayan na darating upang malutas ang mga katulad na problema na huwag gamitin ang tool na ito, ngunit upang gumamit ng formula na pagkopya gamit ang marker ng punan.

Pinahintulutan ang application "Mga talahanayan ng data" ay nasa mga malalaking saklaw lamang ng talahanayan, kapag ang pagkopya ng mga formula ay maaaring tumagal ng maraming oras, at sa panahon ng pamamaraan mismo ang posibilidad ng paggawa ng mga pagkakamali ay nagdaragdag. Ngunit sa kasong ito, inirerekumenda na huwag paganahin ang awtomatikong pag-recalculation ng mga formula sa hanay ng talahanayan ng pagpapalit 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 tingnan natin ang pagpipilian kapag ginamit ang talahanayan ng data na may isang halaga ng variable. Dumaan sa pinakakaraniwang halimbawa ng pagpapahiram.

Kaya, sa kasalukuyan ay inaalok kami ng mga sumusunod na kondisyon sa pautang:

• Termino ng pautang - 3 taon (36 buwan);
• Halaga ng pautang - 900,000 rubles;
• Rate ng interes - 12.5% ​​bawat taon.

Nangyayari ang mga pagbabayad sa pagtatapos ng panahon ng pagbabayad (buwan) ayon sa annuity scheme, iyon ay, sa pantay na pagbabahagi. Kasabay nito, sa simula ng buong termino ng pautang, isang makabuluhang bahagi ng mga pagbabayad ay mga pagbabayad ng interes, ngunit habang ang katawan ay umuurong, bumababa ang mga pagbabayad ng interes, at ang pagtaas ng halaga ng katawan mismo ay nagdaragdag. Ang kabuuang payout, tulad ng nabanggit sa itaas, ay nananatiling hindi nagbabago.

Kinakailangan upang kalkulahin kung ano ang magiging halaga ng buwanang pagbabayad, kasama ang pagbabayad ng katawan ng pautang at pagbabayad ng interes. Para dito, may isang operator si Excel PMT.

PMT kabilang sa pangkat ng mga pinansyal na pag-andar at ang tungkulin nito ay kalkulahin ang buwanang pagbabayad ng uri ng bayad sa annuity batay sa halaga ng katawan ng pautang, term ng pautang at rate ng interes. Ang syntax ng pagpapaandar na ito ay ipinakita bilang

= PLT (rate; nper; ps; bs; type)

Bid - isang argumento na tumutukoy sa rate ng interes ng mga pagbabayad sa credit. Ang tagapagpahiwatig ay nakatakda para sa panahon. Ang aming oras ng pagbabayad ay katumbas ng isang buwan. Samakatuwid, ang taunang rate ng 12.5% ​​ay dapat nahahati sa bilang ng mga buwan sa isang taon, iyon ay, 12.

"Nper" - isang argumento na tumutukoy sa bilang ng mga panahon para sa buong termino ng pautang. Sa aming halimbawa, ang panahon ay isang buwan, at ang term ng pautang ay 3 taon o 36 na buwan. Kaya, ang bilang ng mga panahon ay maagang 36.

"PS" - isang argumento na tumutukoy sa kasalukuyang halaga ng pautang, iyon ay, ito ay ang laki ng katawan ng pautang sa oras ng isyu nito. Sa aming kaso, ang figure na ito ay 900,000 rubles.

"BS" - isang argumento na nagpapahiwatig ng laki ng katawan ng pautang sa oras ng buong pagbabayad. Naturally, ang tagapagpahiwatig na ito ay magiging pantay sa zero. Opsyonal na ito ay opsyonal. Kung laktawan mo ito, ipinapalagay na ito ay katumbas ng bilang na "0".

"Uri" - din ng isang opsyonal na argumento. Inanunsyo niya kung eksaktong eksaktong gagawin ang pagbabayad: sa simula ng panahon (parameter - "1") o sa pagtatapos ng panahon (parameter - "0") Tulad ng natatandaan natin, ang aming pagbabayad ay ginawa sa pagtatapos ng buwan ng kalendaryo, iyon ay, ang halaga ng argumento na ito ay magiging katumbas "0". Ngunit, binigyan ng katotohanan na ang tagapagpahiwatig na ito ay hindi sapilitan, at sa default, kung hindi ginagamit, ang halaga ay ipinahiwatig na maging pantay "0", pagkatapos ay sa ipinahiwatig na halimbawa maaari itong tinanggal sa kabuuan.

1. Kaya, nagpapatuloy kami sa pagkalkula. Pumili ng isang cell sa sheet kung saan ipapakita ang kinakalkula na halaga. Mag-click sa pindutan "Ipasok ang function".



2. Nagsisimula Tampok Wizard. Lumipat kami sa kategorya "Pinansyal", piliin ang pangalan mula sa listahan "PLT" at mag-click sa pindutan "OK".



3. Kasunod nito, ang window ng mga argumento ng pag-andar sa itaas ay isinaaktibo.

   Ilagay ang cursor sa bukid Bid, pagkatapos nito mag-click sa cell sa sheet na may halaga ng taunang rate ng interes. Tulad ng nakikita mo, ang mga coordinate nito ay agad na ipinapakita sa larangan. Ngunit, tulad ng naaalala namin, kailangan namin ng isang buwanang rate, at samakatuwid ay hinati namin ang resulta sa pamamagitan ng 12 (/12).

   Sa bukid "Nper" sa parehong paraan pinapasok namin ang mga coordinate ng mga cell ng term ng utang. Sa kasong ito, hindi mo na kailangang magbahagi ng anuman.

   Sa bukid Sal kailangan mong tukuyin ang mga coordinate ng cell na naglalaman ng halaga ng katawan ng pautang. Ginagawa namin ito. Naglalagay din kami ng isang senyas sa harap ng ipinakitang mga coordinate "-". Ang katotohanan ay ang pagpapaandar PMT sa pamamagitan ng default binibigyan nito ang pangwakas na resulta ng isang negatibong pag-sign, nararapat na isinasaalang-alang ang buwanang pagkawala ng pagbabayad sa utang. Ngunit para sa kalinawan ng aplikasyon ng talahanayan ng data, kailangan namin ang numero na ito upang maging positibo. Samakatuwid, naglalagay kami ng isang senyas minus bago ang isa sa mga argumento ng function. Ang pagpaparami ay kilala minus sa minus sa huli ay nagbibigay plus.

   Sa bukid "Bs" at "Uri" ang data ay hindi naipasok. Mag-click sa pindutan "OK".



4. Pagkatapos nito, kinakalkula at ipinapakita ng operator ang resulta ng kabuuang buwanang pagbabayad sa isang paunang natukoy na cell - 30108,26 rubles. Ngunit ang problema ay ang borrower ay maaaring magbayad ng maximum na 29,000 rubles bawat buwan, iyon ay, dapat na siya ay makahanap ng mga kondisyon ng bank na nag-aalok ng isang mas mababang rate ng interes, o bawasan ang katawan ng pautang, o dagdagan ang termino ng pautang. Tutulungan kami ng lookup table na malaman ang iba't ibang mga pagpipilian.



5. Una, gamitin ang talahanayan ng lookup na may isang variable. Tingnan natin kung paano magbabago ang halaga ng ipinag-uutos na buwanang pagbabayad sa iba't ibang mga pagkakaiba-iba ng taunang rate, simula sa 9,5% bawat taon at pagtatapos 12,5% bawat taon sa mga pagtaas 0,5%. Ang lahat ng iba pang mga kondisyon ay naiwan. Gumuhit kami ng isang 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 linyang ito "Buwanang Pagbabayad" iwanan mo na. Ang unang cell nito ay dapat maglaman ng pormula na kinakalkula namin nang mas maaga. Para sa karagdagang impormasyon, maaari kang magdagdag ng mga linya "Kabuuang halaga ng pautang" at "Kabuuang Kabuuan". Ang haligi kung saan matatagpuan ang pagkalkula ay ginagawa nang walang header.



6. Susunod, kinakalkula namin ang kabuuang halaga ng pautang sa ilalim ng kasalukuyang mga kondisyon. Upang gawin ito, piliin ang unang cell ng hilera "Kabuuang halaga ng pautang" at dumami ang mga nilalaman ng mga cell "Buwanang pagbabayad" at "Term ng pautang". Pagkatapos nito, mag-click sa pindutan Ipasok.



7. Upang makalkula ang kabuuang halaga ng interes sa ilalim ng kasalukuyang mga kondisyon, pareho naming ibabawas ang halaga ng katawan ng pautang mula sa kabuuang halaga ng pautang. Upang maipakita ang resulta sa screen, mag-click sa pindutan Ipasok. Sa gayon, nakakakuha tayo ng halaga na labis nating binabayaran kapag binabayaran ang utang.



8. Ngayon oras na upang ilapat ang tool "Talahanayan ng data". Piliin namin ang buong hanay ng talahanayan, maliban sa mga pangalan ng hilera. Pagkatapos nito, pumunta sa tab "Data". I-click ang pindutan sa laso "Paano kung pagsusuri"na matatagpuan sa pangkat ng tool "Makipagtulungan sa data" (sa Excel 2016, isang pangkat ng mga tool "Pagtataya") Pagkatapos ay bubukas ang isang maliit na menu. Sa loob nito pumili kami ng isang posisyon "Talahanayan ng data ...".



9. Ang isang maliit na window ay bubukas, na kung saan ay tinatawag na "Talahanayan ng data". Tulad ng nakikita mo, mayroon itong dalawang larangan. Dahil nagtatrabaho kami sa isang variable, kailangan lang namin ang isa sa kanila. Dahil binago namin ang variable na haligi ayon sa haligi, gagamitin namin ang patlang Kahalagahan ng Haligi ng Haligi sa. Itakda ang cursor doon, at pagkatapos ay mag-click sa cell sa orihinal na dataset na naglalaman ng kasalukuyang porsyento. Matapos ang mga coordinate ng cell ay ipinapakita sa bukid, mag-click sa pindutan "OK".



10. Kinakalkula at pinupunan ng tool ang buong saklaw ng tabular na may mga halaga na tumutugma sa iba't ibang mga pagpipilian para sa rate ng interes. Kung inilalagay mo ang cursor sa anumang elemento ng lugar na ito ng talahanayan, maaari mong makita na ang formula bar ay hindi ipinapakita ang karaniwang formula para sa pagkalkula ng pagbabayad, ngunit isang espesyal na pormula para sa isang hindi mahuhusay na hanay. Iyon ay, imposible ngayon na baguhin ang mga halaga sa mga indibidwal na cell. Maaari mong tanggalin ang mga resulta sa pagkalkula lamang nang magkasama, at hindi hiwalay.

Bilang karagdagan, maaari mong makita na ang buwanang pagbabayad sa 12.5% ​​bawat taon na nakuha bilang isang resulta ng paglalapat ng talahanayan ng lookup ay tumutugma sa halaga para sa parehong halaga ng interes na natanggap namin sa pamamagitan ng pag-apply ng function PMT. Muli itong nagpapatunay ng tama ng pagkalkula.

Matapos suriin ang larong ito ng talahanayan, dapat itong sabihin na, tulad ng nakikita mo, sa rate lamang na 9.5% bawat taon nakakakuha kami ng isang katanggap-tanggap na antas ng buwanang pagbabayad (mas mababa sa 29,000 rubles).

Aralin: Kinakalkula ang bayad sa annuity sa Excel

Paraan 2: gamitin ang tool na may dalawang variable

Siyempre, upang mahanap sa kasalukuyang mga bangko na naglalabas ng mga pautang sa 9.5% bawat taon ay napakahirap, kung hindi imposible. Samakatuwid, makikita namin kung anong mga pagpipilian ang umiiral upang mamuhunan sa isang katanggap-tanggap na antas ng buwanang pagbabayad para sa iba't ibang mga kumbinasyon ng iba pang mga variable: ang laki ng katawan ng pautang at term ng utang. Sa kasong ito, ang rate ng interes ay mananatiling hindi nagbabago (12.5%). Sa paglutas ng problemang ito, tutulungan kami ng isang tool. "Talahanayan ng data" gamit ang dalawang variable.

1. Gumuhit kami ng isang bagong hanay ng talahanayan. Ngayon sa mga pangalan ng haligi ay ipahiwatig ang termino ng pautang (mula sa 2 bago 6 taon sa mga buwan sa mga pagdaragdag ng isang taon), at sa mga linya - ang laki ng katawan ng pautang (mula 850000 bago 950000 rubles sa mga pagtaas 10000 rubles). Sa kasong ito, isang paunang kinakailangan ay 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 kondisyong ito, hindi gagana ang tool kapag gumagamit ng dalawang variable.



2. Pagkatapos ay piliin ang buong saklaw na nagreresulta sa talahanayan, kabilang ang mga pangalan ng mga haligi, hilera at isang cell na may pormula PMT. Pumunta sa tab "Data". Tulad ng nakaraang oras, mag-click sa pindutan "Paano kung pagsusuri", sa pangkat ng tool "Makipagtulungan sa data". Sa listahan na bubukas, piliin ang "Talahanayan ng data ...".



3. Nagsisimula ang window ng tool "Talahanayan ng data". Sa kasong ito, kailangan namin ng parehong mga patlang. Sa bukid Kahalagahan ng Haligi ng Haligi sa ipahiwatig ang mga coordinate ng cell na naglalaman ng term ng pautang sa pangunahing data. Sa bukid "Kahalili ang mga halaga ng hilera ayon sa hilera sa" ipahiwatig ang address ng cell ng paunang mga parameter na naglalaman ng halaga ng katawan ng pautang. Matapos ipasok ang lahat ng data. Mag-click sa pindutan "OK".



4. Ang programa ay nagsasagawa ng pagkalkula at pinunan ang saklaw ng talahanayan ng data. Sa intersection ng mga hilera at haligi posible na ngayong obserbahan kung ano ang eksaktong buwanang pagbabayad, kasama ang kaukulang halaga ng taunang interes at ang ipinahiwatig na termino ng pautang.



5. Tulad ng nakikita mo, maraming mga halaga. Upang malutas ang iba pang mga problema, maaaring may higit pa. Samakatuwid, upang gawing mas visual ang output ng mga resulta at agad na matukoy kung aling mga halaga ang hindi nasisiyahan sa ibinigay na kondisyon, maaari mong gamitin ang mga tool sa visualization. Sa aming kaso, ito ay magiging kondisyon sa pag-format. Piliin namin ang lahat ng mga halaga ng saklaw ng talahanayan, hindi kasama ang mga heading ng hanay at haligi.



6. Ilipat sa tab "Home" at mag-click sa icon Pag-format ng Kondisyon. Matatagpuan ito sa block ng tool. Mga Estilo sa tape. Sa menu na bubukas, piliin ang Mga Panuntunan sa Seleksyon ng Cell. Sa karagdagang listahan, mag-click sa posisyon "Mas mababa ...".



7. Kasunod nito, bubukas ang window ng mga setting ng setting ng pag-format. Sa kaliwang patlang ipahiwatig ang halaga na mas mababa kaysa sa kung saan ang mga selula ay pipiliin. Tulad ng naaalala natin, nasiyahan tayo sa kondisyon na ang buwanang pagbabayad ng pautang ay mas mababa sa 29000 rubles. Pumasok kami sa numero na ito. Sa tamang patlang, maaari mong piliin ang kulay ng highlight, kahit na maaari mong iwanan ito nang default. Matapos ipasok ang lahat ng kinakailangang mga setting, mag-click sa pindutan "OK".



8. Pagkatapos nito, ang lahat ng mga cell na ang mga halaga ay tumutugma sa kondisyon sa itaas ay mai-highlight.

Ang pagkakaroon ng pag-aralan ang hanay ng talahanayan, maaari kaming gumuhit ng ilang mga konklusyon. Tulad ng nakikita mo, kasama ang umiiral na term ng pautang (36 buwan), upang mamuhunan sa ipinahiwatig na halaga ng buwanang pagbabayad, kailangan naming kumuha ng pautang na hindi hihigit sa 860000.00 rubles, iyon ay, 40,000 mas mababa kaysa sa orihinal na pinlano.

Kung balak pa rin nating kumuha ng pautang na 900,000 rubles, kung gayon ang termino ng pautang ay dapat na 4 na taon (48 buwan). Sa kasong ito lamang, ang buwanang pagbabayad ay hindi lalampas sa itinatag na limitasyon ng 29,000 rubles.

Kaya, gamit ang talahanayan na ito at pag-aralan ang mga kalamangan at kahinaan ng bawat pagpipilian, ang borrower ay maaaring gumawa ng isang tiyak na desisyon sa mga tuntunin ng pautang, pagpili ng pinaka-angkop na pagpipilian mula sa lahat ng posibleng.

Siyempre, ang talahanayan ng lookup 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: Kondisyonal na Formatting sa Excel

Sa pangkalahatan, dapat tandaan na ang talahanayan ng lookup ay isang napaka-kapaki-pakinabang at medyo simpleng tool para sa pagtukoy ng resulta para sa iba't ibang mga kumbinasyon ng mga variable. Gamit ang kondisyong pag-format nang sabay, maaari mong mailarawan ang impormasyon na natanggap.