Ngokuvamile, kuyadingeka ukubala umphumela wokugcina wenhlanganisela ehlukahlukene yedatha yokufaka. Ngakho-ke, umsebenzisi uzokwazi ukuhlola zonke izinketho zokwenza isenzo, ukhethe labo okutholwa ngumphumela wokusebenzisana naye, futhi ekugcineni, ukhethe okukhethwa kukho okungcono kakhulu. Ku-Excel, kukhona ithuluzi elikhethekile kulo msebenzi - "Ithebula Ledatha" ("Ithebula lokubuka"). Ake sithole ukuthi singayisebenzisa kanjani ukwenza lezi zimo ezingenhla.
Bheka futhi: Ukukhetha kwe-Parameter ku-Excel
Ukusebenzisa ithebula lwedatha
Ithuluzi "Ithebula Ledatha" iklanyelwe ukubala umphumela ngokuhlukahluka okuhlukile kweyodwa noma ezimbili eziguquliwe ezichazwe. Ngemuva kokubala, zonke izinketho ezikhona zizovela ngendlela yetafula, okuthiwa i-matrix yokuhlaziywa kwezinto. "Ithebula Ledatha" ibhekisela eqenjini lamathuluzi "Ukuthi-uma" ukuhlaziywaokuyinto efakwe kwi riboni kuthebhu "Idatha" ku-block "Ukusebenza nedatha". Ngaphambi kwe-Excel 2007, leli thuluzi laligqoke igama. "Ithebula lokubuka"ukuthi ngisho nangokwedlulele ngokwengeziwe kubonakala kubaluleke kakhulu kunegama lamanje.
Ithebula le-lookup lingasetshenziswa ezimweni eziningi. Isibonelo, inketho evamile yilapho udinga ukubala inani lokukhokhwa kwenyanga mboleko ngokuhlukahluka okuhlukene kwesikhathi sokubheka kanye nenani lemalimboleko, noma isikhathi sokubheka kanye nesilinganiso senzalo. Leli thuluzi lingasetshenziswa futhi uma kucubungula izindlela zokutshala izimali.
Kodwa kufanele futhi uqaphele ukuthi ukusetshenziselwa ngokweqile kwalesi thuluzi kungaholela ohlelweni lwe-braking, ngoba idatha ivuselelwa njalo. Ngakho-ke, kunconywa ukuthi ungasebenzisi leli thuluzi kumathuluzi amancane we-tabular wokuxazulula izinkinga ezifanayo, kodwa ukusebenzisa ukukopisha kwamafomula usebenzisa uphawu lokugcwalisa.
Isicelo sokulungiswa "Amatafula Emininingwane" kuphela kuma-range angaphakathi, uma ukukopisha amafomula kungathatha isikhathi esiningi, futhi ngesikhathi senqubo ngokwayo iningi lamaphutha landa. Kodwa nakulokhu, kunconywa ukukhubaza ukuvuselelwa okuzenzakalelayo kwamafomula ebangeni lethebula lokubheka, ukuze ugweme ukulayisha okungadingekile ohlelweni.
Umehluko omkhulu phakathi kokusetshenziswa okuhlukahlukene kwetafula lwedatha inani lezinguquko ezihilelekile ekubalweni: okuguquguqukayo okukodwa noma amabili.
Indlela 1: sebenzisa ithuluzi ngenani elilodwa
Ngokushesha ake sicabangele inketho uma ithebula lwedatha lisetshenziswe ngenani eliguquguqukayo elilodwa. Thatha isibonelo esihle kakhulu sokuboleka imali.
Ngakho-ke, njengamanje sinikezwe izimo ezilandelayo zezikweletu:
- Isikweletu - iminyaka emithathu (izinyanga ezingu-36);
- Isikweletu semali - ama-ruble angu-900000;
- Isilinganiso senzalo - 12.5% ngonyaka.
Izinkokhelo zenziwa ekupheleni kwesikhathi sokukhokha (inyanga) usebenzisa uhlelo lwe-annuity, okungukuthi, ngokwabelana okulinganayo. Ngesikhathi esifanayo, ekuqaleni kwesikweletu sonke, izinkokhelo zenzalo ziyingxenye enkulu yezinkokhelo, kodwa njengoba umzimba wehla, izinkokhelo zenzalo ziyancipha, kanti inani lokubuyisela komzimba ngokwayo liyanda. Inkokhelo yenkokhelo, njengoba kushiwo ngenhla, ihlala ingashintshi.
Kubalulekile ukubala ukuthi inani lenkokhelo yanyanga zonke kuyoba yimuphi, okufaka ukubuyiselwa komzimba wesibopho kanye nezinkokhelo zenzalo. Kulokhu, i-Excel ine-opharetha I-PMT.
I-PMT Kungenxa yeqembu lemisebenzi yezezimali kanye nomsebenzi walo ukubala inkokhelo yenyanga yenyanga yokukhokhwa kwenani le-annuity esekelwe emalini omboleko wemali, isikhathi sokuboleka imali kanye nesilinganiso senzalo. I-syntax yalo msebenzi ilandelayo.
= I-PMT (isilinganiso; nper; ps; bs; uhlobo)
"Bet" - Iphikisana ekunqumeni izinga lesithakazelo sokukhokha isikweletu. Inkomba isethelwe isikhathi. Isikhathi sethu sokukhokha inyanga eyodwa. Ngakho-ke, isilinganiso saminyaka yonke se-12.5% kufanele sidilizwe sibe yinani lezinyanga ngonyaka, okungukuthi, 12.
"Kper" - Iphikisana elikhomba inani lezinkathi zesikhathi sonke semali mboleko. Esikhathini sethu, isikhathi sinyanga eyodwa, kanti isikhathi sokubolekwa iminyaka engu-3 noma izinyanga ezingu-36. Ngakho-ke, inani lezinkathi lizoqala kusenesikhathi.
"PS" - impikiswano ekhomba inani lamanje lemalimboleko, okungukuthi, ubukhulu bomzimba wesikweletu ngesikhathi sokukhishwa kwayo. Esikhathini sethu, lesi sibalo yi-rubles angu-900 000.
"BS" - impikiswano ebonisa ubukhulu besikhwama semalimboleko ngesikhathi sokukhokha okugcwele. Ngokuvamile, lesi sibonakaliso sizolingana no-zero. Le mpikiswano iyakhethwa. Uma uyidlula, kucatshangwa ukuthi lilingana nenombolo "0".
"Thayipha" - nokuphikisana okuzikhethela. Ukwazisa lapho ukukhokhwa kuzokwenziwa: ekuqaleni kwesikhathi (i-parameter - "1") noma ekupheleni kwesikhathi (i-parameter - "0"). Njengoba sikhumbula, ukukhokhwa kwethu kwenziwa ekupheleni kwenyanga yekhalenda, okungukuthi, inani lale mpikiswano lizolingana "0". Kodwa, kunikezwe ukuthi lesi sibonakaliso asivumelekile, futhi ngokuzenzakalelayo, uma singasetshenzisiwe, inani lithathwa njengenani "0", khona-ke kusibonelo esicacisiwe ngeke sisetshenziswe nhlobo.
- Ngakho-ke, siyaqhubeka nokubala. Khetha iseli kushidi lapho inani elibalwe lizoboniswa khona. Sichofoza inkinobho "Faka umsebenzi".
- Iqala Umsebenzi weWebhu. Yenza ushintsho esigabeni "Izezimali", khetha ohlwini ohlwini "PLT" bese uchofoza inkinobho "Kulungile".
- Ngemva kwalokhu, kukhona ukuvuselelwa kwefasitela yeziphakamiso zomsebenzi ongenhla.
Faka i-cursor ensimini "Bet"bese uchofoza esitokisini kushidi ngenani lentengo yonyaka. Njengoba ungabona, izixhumanisi zayo ziboniswa masinyane ensimini. Kodwa, njengoba sikhumbula, sidinga isilinganiso sanyanga zonke, ngakho-ke sihlukanisa umphumela we-12 (/12).
Ensimini "Kper" ngendlela efanayo, singena izixhumanisi zamangqamuzana wesikweletu. Kulokhu, akukho okudingeka kuhlukaniswe.
Ensimini "Ps" kufanele ucacise izixhumanisi zeseli equkethe inani lomzimba wesikweletu. Siyakwenza. Siphinde sibhale uphawu phambi kwezixhumanisi ezibonisiwe. "-". Iphuzu liwukuthi umsebenzi I-PMT ngokuzenzakalelayo, inikeza umphumela wokugcina ngesibonakaliso esingalungile, ngokucabangela ukukhokhwa kwemali ngenyanga ngenyanga. Kodwa ngokucacile, sidinga ithebula lwedatha ukuze libe nesimo esihle. Ngakho-ke, sibeka uphawu "susa" ngaphambi kwesinye sezimpikiswano zomsebenzi. Njengoba kuyaziwa, ukubuyabuyelela "susa" kuqhubeke "susa" ekugcineni unikeza plus.
Emasimini "Bs" futhi "Thayipha" Asifaki idatha nhlobo. Sichofoza inkinobho "Kulungile".
- Emva kwalokho, opharetha uyalinganisa futhi abonise esitokisini esandulelwe ngaphambi komphumela wokukhokhelwa kwenyanga yonke - 30108,26 ama-ruble. Kodwa inkinga yukuthi lowo obolekayo uyakwazi ukukhokha ama-ruble angama-29,000 ngenyanga, okungukuthi, kufanele athole izimo zokunikela ngebhange ngenani lesenzalo esincane, noma anciphise umzimba womboleko, noma wandise isikhathi sokuboleka imali. Bala izinketho ezahlukene zesenzo kuzosisiza ithebula lokubheka.
- Ukuze uqale, sebenzisa ithebula lokubuka ngokuguquguquka okukodwa. Ake sibone ukuthi ukubaluleka kokukhokhwa kwenyanga okugunyazayo kuzohluka kanjani ngokuhlukahluka okwezinga lonyaka, kusuka kulo 9,5% ngonyaka kanye nokuphela 12,5% p ngesinyathelo 0,5%. Zonke ezinye izimo zishiywe zingashintshi. Dweba ububanzi bethebula, amagama amakholomu azohambisana nokuhlukahluka okuhlukile kwesilinganiso senzalo. Ngalesi line "Izinkokhelo zenyanga" shiya njengoba kunjalo. Iseli layo lokuqala kufanele libe nefomula esiyibalwe ngaphambili. Ukuze uthole olunye ulwazi, ungakwazi ukwengeza imigqa "Ingqikithi yenani lemali" futhi "Inzalo Yonke". Ikholomu lapho kubalwa khona ukukala ngaphandle kwenhloko.
- Okulandelayo, sibala inani eliphelele lemali ngaphansi kwezimo zamanje. Ukuze wenze lokhu, khetha iseli lokuqala lomugqa. "Ingqikithi yenani lemali" bese ukwandisa okuqukethwe kwe-cell "Ukukhokha ngenyanga" futhi "Igama lokuboleka". Ngemuva kokuchofoza ku- Ngena.
- Ukubala inani lezithakazelo ngaphansi kwezimo zamanje, ngendlela efanayo nathi sisusa ukubaluleka komzimba wesikweletu kusukela esilinganisweni semalimboleko. Ukuze ubonise umphumela ekucholeni kwesikrini ku-inkinobho. Ngena. Ngakho-ke, sithola imali esiyikhokhela ngokukhokha imali.
- Manje yisikhathi sokusebenzisa ithuluzi. "Ithebula Ledatha". Khetha lonke uhlu lwetafula, ngaphandle kwamagama emigqa. Emva kwalokho iya kuthebhu "Idatha". Chofoza inkinobho ku-ribbon "Ukuthi-uma" ukuhlaziywaokufakwa eqenjini lamathuluzi "Ukusebenza nedatha" (ku-Excel 2016, iqembu lamathuluzi "Isibikezelo"). Khona-ke imenyu encane ivula. Kuyo sikhetha isikhundla "Ithebula Ledata ...".
- Iwindi elincane livuleka, elibizwa ngokuthi "Ithebula Ledatha". Njengoba ungabona, inezinkambu ezimbili. Njengoba sisebenza ngokuhlukahluka okukodwa, sidinga eyodwa kuphela. Njengoba izinguquko zethu eziguquguqukayo zenzeka kumakholomu, sizosebenzisa insimu "Amagugu amancane angama-columns". Sibeka isikhombisi lapho, bese uchofoza esitokisini kusethi yokuqala yedatha, equkethe inani lamanje lamaphesenti. Ngemuva kokuthi izixhumanisi zeseli zikhonjiswe ensimini, chofoza inkinobho "Kulungile".
- Ithuluzi libala futhi ligcwalisa lonke ibanga lethebula ngamagugu ahambisana nezinketho ezahlukene zenzalo. Uma ufaka isikhombisi kunoma yisiphi isici salesi sigaba samatafula, ungabona ukuthi ibha yefomula ayibonisi ifomula yokubala yokukhokha evamile, kodwa ifomula ekhethekile ye-non-break array. Okusho ukuthi akusakwazi ukushintsha amanani kumaseli ngabanye. Susa imiphumela yokubala ingaba kuphela ndawonye, hhayi ngokuhlukile.
Ngaphezu kwalokho, kungaphawulwa ukuthi ukubaluleka kokukhokhwa kwenyanga ku-12.5% ngonyaka, etholakalayo ngokusebenzisa ithebula lokubheka, kufana nenani lentengo efanayo nesithakazelo esasithola ngokusebenzisa umsebenzi I-PMT. Lokhu kuphinda kubonakalise ukufaneleka kokubala.
Ngemuva kokuhlaziya lolu hlu lwethebhulethi, kufanele kuthiwa, njengoba sibona, kuphela ngesilinganiso se-9.5% ngonyaka, izinga lokukhokha lenyanga lokutholwa (ngaphansi kwama-ruble angama-29,000) litholakala.
Isifundo: Ukubalwa kokukhokhwa kwemali ku-Excel
Indlela 2: sebenzisa ithuluzi enezinhlobo ezimbili
Yiqiniso, kunzima kakhulu, uma kunengqondo, ukuthola amabhange akhipha izikweletu ngo-9.5% ngonyaka. Ngakho-ke, ake sibone ukuthi yiziphi izinketho ezizofakwa kuzo ezingeni elivumelekile lokukhokha nyangazonke ngezinhlanganisela ezihlukahlukene zezinye iziguquguquko: ubungakanani bomzimba wesikweletu kanye nesikweletu. Ngesikhathi esifanayo, izinga lesithakazelo lizohlala lingashintshi (12.5%). Ithuluzi liyasisiza ngalolu msebenzi. "Ithebula Ledatha" usebenzisa iziguquko ezimbili.
- Dweba uhlu olusha lwetafula. Manje igama lesikweletu lizokhonjiswa emagama ekholomu (kusuka 2 kuze kube 6 iminyaka ezinyangeni ezinyathelweni zonyaka owodwa), nasemigqeni - ubukhulu bomzimba wesikweletu (kusuka 850000 kuze kube 950000 i-ruble in increments 10000 rubles). Kulesi simo, kubalulekile ukuthi iseli lapho ifomula yokubala ikhona (kithi I-PMT), etholakala emngceleni wamagama olayini namakholomu. Ngaphandle kwalesi simo, ithuluzi ngeke lisebenze uma usebenzisa iziguquko ezimbili.
- Bese ukhetha uhla lwethebula olufakwe, kufaka phakathi amagama amakholomu, imigqa kanye neseli ngefomula I-PMT. Iya kuthebhu "Idatha". Njengengesikhathi esidlule, chofoza inkinobho. "Ukuthi-uma" ukuhlaziywaeqenjini lamathuluzi "Ukusebenza nedatha". Kulolu hlu oluvula, khetha into "Ithebula Ledata ...".
- Iwindi lamathuluzi liqala. "Ithebula Ledatha". Kulokhu, sidinga zombili izinkambu. Ensimini "Amagugu amancane angama-columns" sichaza izixhumanisi zeseli eliqukethe isikhathi sokubolekwa kwedatha eyinhloko. Ensimini "Amagugu angenayo imali ngemigqa" ucacise ikheli leseli lemingcele yokuqala equkethe inani lomboleko Ngemuva kokuthi yonke idatha ifakiwe. Sichofoza inkinobho "Kulungile".
- Uhlelo lwenza ukubala futhi lugcwalisa ububanzi betafula nedatha. Emkhakheni wezinsika namakholomu, manje kungenzeka ukuthi ubheke ukuthi inkokhelo yenyanga izoba kanjani, ngenani elilinganayo lentshisekelo yonyaka nesikhathi esibekiwe sokubaluleka.
- Njengoba ubona, kunamanani amaningi kakhulu. Ukuxazulula ezinye izinkinga kungenzeka kube ngaphezulu. Ngakho-ke, ukuze wenze umkhiqizo wemiphumela ibonakale futhi ubone ngokushesha ukuthi yiziphi izimiso ezinganelisi isimo esinikeziwe, ungasebenzisa amathuluzi okubuka. Esikhathini sethu kuzoba nokufometha okunemibandela. Khetha wonke amanani webanga letafula, ngaphandle kwamakhanda elayini namakholomu.
- Hambisa kuthebhu "Ikhaya" bese uchofoza isithonjana "Ukufometha okunemibandela". Itholakala ebhokisini lamathuluzi. "Izitayela" kwi-tape. Emenyu evula, khetha into "Imithetho yokukhethwa kweseli". Kuhlu olwengeziwe chofoza kusikhundla "Okungaphansi ...".
- Ukulandela lokhu, iwindi yokumisa ifomethi yokumiswa ivula. Ensimini engakwesokunxele sichaza inani, ngaphansi kokuthi amaseli azokhethwa yini. Njengoba sikhumbula, sinelisekile ngesimo lapho inkokhelo yenyanga yokubolekwa kuyobe yancipha khona 29000 ama-ruble. Faka le nombolo. Endaweni efanelekile kungenzeka ukhethe umbala wokukhethwa, nakuba ungayishiya ngokuzenzakalelayo. Ngemuva kokuthi zonke izilungiselelo ezidingekayo zifakiwe, chofoza inkinobho. "Kulungile".
- Ngemuva kwalokho, wonke amaseli anezindinganiso ezihambisana nesimo esingenhla uzogqanyiswa ngombala.
Ngemuva kokuhlaziya uhlu lwetafula, ungathola ezinye iziphetho. Njengoba ubona, ngesikhatsi samanje semalimboleko (izinyanga ezingu-36), ukuze utshale imali ekhonjisiwe ngenhla, kufanele siboleke imali engadluli 8,600,000.00 rubles, okuyi-40,000 engaphansi kwehlelwe ekuqaleni.
Uma sisahlose ukubolekisa ngemali engu-900,000 ruble, isikhathi sokuboleka kufanele sibe iminyaka engu-4 (izinyanga ezingu-48). Kuphela kulokhu, inani lokukhokhelwa kwenyanga ngeke lidlule umkhawulo omisiwe wama-ruble angu-29,000.
Ngakho-ke, ukusebenzisa lolu hlu lwe-tabular futhi uhlaziye ubuhle nokuzikhethela kokukhethwa kukho, umbolekisi angenza isinqumo esithile ngemigomo yokuboleka, akhethe ukhetho oluhambisana nezidingo zakhe.
Yiqiniso, ithebula lokubheka lingasetshenziswa nje kuphela ukubala izinketho zezikweletu, kodwa futhi ukuxazulula ezinye izinkinga eziningi.
Isifundo: Ukufometha okunemibandela ku-Excel
Ngokuvamile, kufanele kuqashelwe ukuthi ithebula lokungena kuyithuluzi eliwusizo kakhulu futhi elulula kakhulu lokunquma umphumela wezinhlanganisela ezihlukahlukene zeziguquko. Ngokusebenzisa ukufometha okunemibandela kanye nalo, ngaphezu kwalokho, ungabona ngeso lengqondo ulwazi olutholiwe.
