Ithebula ledatha kuMicrosoft Excel

Kaningi, udinga ukubala umphumela wokugcina wokuhlanganiswa okuhlukahlukene kwedatha yokufaka. Ngakho-ke, umsebenzisi uzokwazi ukuhlola zonke izindlela ezinokwenzeka zezenzo, khetha labo imiphumela yokuxhumana okumgculisayo, bese, ekugcineni, bakhethe inketho enhle kakhulu. E-Excel, ukwenza lo msebenzi, kukhona ithuluzi elikhethekile - "Ithebula ledatha" (Ithebula Lokuthenga) Ake sibheke ukuthi singayisebenzisa kanjani ukuqedela lezi zimo ezingenhla.

Funda futhi: Ukukhetha ipharamitha ku-Excel

Isebenzisa itafula ledatha

Insimbi "Ithebula ledatha" Kuhloswe ukubala umphumela wokuhlukahluka okuhlukahlukene kokuguquguquka okukodwa noma okubili okuchaziwe. Ngemuva kokubalwa, zonke izinketho ezingenzeka zivele ngendlela yetafula, ebizwa ngokuthi yi-matrix yokuhlaziywa kwe-factor. "Ithebula ledatha" kubhekiswa eqenjini lamathuluzi "Yini uma uhlaziya", ebekwe kubambo kuthebhu "Idatha" ebhlokini "Sebenza nedatha". Ngaphambi kwe-Excel 2007, leli thuluzi labizwa Ithebula Lokuthenga, okukhombise kahle ubunjalo bayo ngokwedlula igama lamanje.

Itafula lokubheka lingasetshenziswa ezimweni eziningi. Isibonelo, inketho ejwayelekile yilapho udinga ukubala inani lemali ekhokhwa mboleko yanyanga zonke ngokwahluka okuhlukahlukene kwesikhathi sokukweleta nenani lemalimboleko, noma isikhathi sokukhumbula kanye nesilinganiso senzalo. Futhi, leli thuluzi lingasetshenziswa ekuhlaziyweni kwamamodeli wamaphrojekthi wokutshala imali.

Kodwa kufanele futhi wazi ukuthi ukusetshenziswa ngokweqile kwaleli thuluzi kungaholela ekubambeni kwesistimu, njengoba idatha ilandiswa ngokuqhubekayo. Ngakho-ke, kunconyelwa kumalungiselelo amancane etafula ukuxazulula izinkinga ezifanayo hhayi ukusebenzisa leli thuluzi, kodwa ukusebenzisa ukukopisha ifomula usebenzisa umaki wokugcwalisa.

Uhlelo lokusebenza olunesizathu "Amatafula wedatha" isemaphepheni amakhulu etafula kuphela, lapho ukukopisha amafomathi kungathatha isikhathi esiningi, futhi phakathi nenqubo uqobo lwayo amathuba wokwenza amaphutha ayanda. Kepha kulokhu, kunconyelwa ukukhubaza ukuphindaphinda okuzenzakalelayo kwamafomula kuhla lwethebula lokufakwa esikhundleni ukugwema umthwalo ongadingekile ohlelweni.

Umehluko omkhulu phakathi kokusetshenziswa okuhlukile kwetafula ledatha yinombolo yokuhlukahluka okubandakanyekile ekubalweni: eyodwa eguquguqukayo noma emibili.

Indlela 1: sebenzisa ithuluzi ngokuhlukahluka okukodwa

Ngokushesha ake sibheke inketho lapho ithebula ledatha lisetshenziswa ngenani elilodwa lokuguquguquka. Thatha isibonelo esivame ukubolekwa.

Ngakho-ke, njengamanje sinikezwa imibandela elandelayo yemalimboleko:

• Isikhathi semalimboleko - iminyaka emi-3 (izinyanga ezingama-36);
• Inani lemalimboleko - ama-ruble angama-900,000;
• Izinga lenzalo - 12.5% ​​ngonyaka.

Izinkokhelo zenzeka ekugcineni kwesikhathi sokukhokha (inyanga) ngokohlelo lwe-annuity, okungukuthi, ngamasheya alinganayo. Ngaso leso sikhathi, ekuqaleni kwayo yonke inkathi yemalimboleko, ingxenye ebalulekile yezinkokhelo iyizinkokhelo zenzalo, kodwa njengoba umzimba uncipha, izinkokhelo zenzalo ziyehla, futhi inani lokukhokha lomzimba uqobo liyakhuphuka. Imali ekhokhwayo ephelele, njengoba kushiwo ngenhla, ihlala ingashintshiwe.

Kuyadingeka ukubala ukuthi ingakanani imali ekhokhwa njalo ngenyanga, okubandakanya ukukhokhelwa komzimba wemalimboleko nezinkokhelo zenzalo. Kulokhu, i-Excel ine-opharetha PMT.

PMT kungokwesiqalo semisebenzi yezezimali futhi umsebenzi wayo ukubala ukukhokhelwa kohlobo lwemalimboleko lwanyangazonke lwenyanga. I-syntax yalomsebenzi yethulwa njenge

= PLT (isilinganiso; nper; ps; bs; uhlobo)

Ibhidi - impikiswano enquma inani lenzalo lokukhokha isikweletu. Inkomba ihlelelwe inkathi. Isikhathi sethu sokukhokha silingana nenyanga. Ngakho-ke, inani lonyaka le-12,5% kufanele lihlukaniswe ngenani lezinyanga ngonyaka, okungukuthi, eziyi-12.

"Nper" -ukuphikisana okunquma inani lezikhathi zikhathi yonke yemalimboleko. Ngokwesibonelo sethu, isikhathi inyanga eyodwa, futhi isikhathi semalimboleko yiminyaka emi-3 noma izinyanga ezingama-36. Ngakho-ke, inani lezikhathi lizoqala ekuqaleni kuka-36.

"PS" -ukuphikisana okunquma inani lamanje lemalimboleko, okungukuthi, usayizi womzimba wemalimboleko ngesikhathi sokukhishwa kwalo. Esimweni sethu, lesi sibalo singama-ruble angama-900,000.

"BS" - ingxabano ekhombisa usayizi womzimba wemalimboleko ngesikhathi sokukhokha ngokugcwele. Ngokwemvelo, le nkomba izolingana no-zero. Le ngxabano iyakhethwa. Uma uyiqaqa, kucatshangwa ukuthi ilingana nenombolo "0".

"Thayipha" - futhi nengxabano ongayikhetha. Umemezela ukuthi inkokhelo izokwenziwa nini ngqo: ekuqaleni kwesikhathi (ipharamitha - "1") noma ekupheleni kwesikhathi (ipharamitha - "0") Njengoba sikhumbula, ukukhokha kwethu kwenziwa ekupheleni kwenyanga yekhalenda, okungukuthi, inani lale ngxabano lizolingana "0". Kepha, uma ngabe iqiniso lokuthi le nkomba ayiphoqelekile, futhi ngokwakhona, uma kungenjalo, inani lishiwo ukuthi lilingane "0", ke kwisibonelo esikhonjisiwe ingashiywa ngokuphelele.

1. Ngakho-ke, siya phambili ekubalweni. Khetha iseli eshidini lapho kuzobukiswa khona inani elibaliwe Chofoza inkinobho "Faka umsebenzi".



2. Kuqala Isici Wizard. Siqhubekela esigabeni "Okwezimali", khetha igama ohlwini "PLT" bese uchofoza inkinobho "Kulungile".



3. Ukulandela lokhu, iwindi lokuphikisa lomsebenzi ongenhla liyasebenza.

   Faka ikhesa endle Ibhidi, ngemuva kwalokho sichofoza kuseli ephepheni ngenani lesilinganiso senzalo sonyaka. Njengoba ukwazi ukubona, izixhumanisi zayo ziboniswa ngokushesha endle. Kepha, njengoba sikhumbula, sidinga isilinganiso sanyanga zonke, ngakhoke sihlukanisa umphumela ngawu-12 (/12).

   Ensimini "Nper" ngendlela efanayo singena ngokuxhumanisa kwamangqamuzana wesikhathi semalimboleko. Kulokhu, awudingi ukuhlanganyela noma yini.

   Ensimini IHu udinga ukucacisa izixhumanisi zeseli eziqukethe inani lomzimba wemalimboleko. Siyakwenza. Siphinde sibeke uphawu phambi kwezixhumanisi ezikhonjisiwe "-". Iqiniso ukuthi umsebenzi PMT ngokuzenzakalelayo inika umphumela wokugcina ngophawu olubi, icubungula kahle ukulahleka kwemali ekhokhwa mboleko yenyanga. Kepha ukuze kucace ukusetshenziswa kwetafula ledatha, sidinga ukuthi le nombolo ibe nethemba. Ngakho-ke, sibeka isibonakaliso Susa ngaphambi kwenye yezimpikiswano zomsebenzi. Ukuphindaphindeka kuyaziwa Susa kuvuliwe Susa ekugcineni kunika kanye.

   Emasimini "Bs" futhi "Thayipha" idatha ayifakiwe nhlobo. Chofoza inkinobho "Kulungile".



4. Ngemuva kwalokho, opharetha abale futhi akhombise umphumela wenkokhelo yanyanga zonke esitokisini esikhishwe ngaphambilini - 30108,26 ama-ruble. Kepha inkinga ukuthi lowo obolekayo uyakwazi ukukhokha imali engaphezu kwama-ruble angama-29,000 ngenyanga, okungukuthi, kufanele athole ibhange elinikeza imibandela ngenzalo ephansi, noma anciphise umzimba wemalimboleko, noma akhuphule isikhathi semalimboleko. Ithebula lokubheka lizosisiza ukuthola izindlela ezahlukahlukene.



5. Okokuqala, sebenzisa itafula lokuhlola ngokuhlukahluka okukodwa. Ake sibheke ukuthi inani lemali ekhokhwayo yanyanga zonke izoguquka kanjani ngokuhlukahluka okuhlukahlukene kwesilinganiso sonyaka, kuqala kusuka 9,5% ngonyaka nokuphela 12,5% ngonyaka ku-ezikhuphukayo 0,5%. Zonke ezinye izimo zishiywa zingashintshiwe. Sidweba uhla lwethebula, amagama amakholomu azohambisana nokuhlukahluka okuhlukahlukene kwenzalo yenzalo. Nale ulayini "Izinkokhelo Zanyanga zonke" hamba njengoba unjalo. Iseli layo lokuqala kufanele liqukethe ifomula esiyibalile ngaphambili. Ngemininingwane engaphezulu, ungangeza imigqa "Inani lemali mboleko ephelele" futhi "Inzalo Ephelele". Ikholomu lapho kubalwa khona kwenziwa ngaphandle kwesihloko.



6. Okulandelayo, sibala inani lemali mboleko ephelele ngaphansi kwezimo zamanje. Ukuze wenze lokhu, khetha iseli lokuqala lomugqa "Inani lemali mboleko ephelele" bese liphindaphindeka okuqukethwe amaseli "Inkokhelo yanyanga zonke" futhi "Igama lemalimboleko". Ngemuva kwalokho chofoza inkinobho Ngena.



7. Ukubala inani lenzalo ngaphansi kwezimo zamanje, nathi sisusa inani lemizimba yemalimboleko lemali eliphelele. Ukubonisa umphumela esikrinini, chofoza inkinobho Ngena. Ngakho-ke, sithola inani eselikhokha lapho sibuyisa imalimboleko.



8. Manje yisikhathi sokusebenzisa ithuluzi "Ithebula ledatha". Sikhetha lonke uhlu lwethebula, ngaphandle kwamagama omugqa. Ngemuva kwalokho, iya kuthebhu "Idatha". Chofoza inkinobho kubambo "Yini uma uhlaziya"elibekwe eqenjini leThuluzi "Sebenza nedatha" (ku-Excel 2016, iqembu lamathuluzi "Isimo sezulu") Bese kuvula imenyu encane. Kuyo sikhetha isikhundla "Ithebula ledatha ...".



9. Kuvulwa iwindi elincane, elibizwa "Ithebula ledatha". Njengoba ubona, inezindima ezimbili. Njengoba sisebenza ngokuguquguquka okukodwa, sidinga esisodwa sazo. Njengoba siguqula ikholomu eguquguqukayo ngekholomu, sizosebenzisa inkambu Amanani Amakholamu Wokubamba indawo ku. Setha isikhombisi lapho, bese uqhafaza kuseli kudathasethi yangempela equkethe amaphesenti wamanje. Ngemuva kokuthi izixhumanisi zeseli zikhonjiswe endle, chofoza inkinobho "Kulungile".



10. Ithuluzi libala futhi ligcwalisa lonke uhla lwe-tabular ngamanani ahambelana nokukhethwa okuhlukile kwesilinganiso senzalo. Uma ufaka isikhombisi kunoma iyiphi into yale ndawo yetafula, ungabona ukuthi ibha yefomula ayibonisi ifomula ejwayelekile yokubala inkokhelo, kepha ifomula ekhethekile yamalungu afanayo angenakulinganiswa. Okusho ukuthi, manje akunakwenzeka ukuguqula amanani aselangeni ngalinye. Ungasusa imiphumela yokubala kuphela ngokuhlangene, hhayi ngokuhlukile.

Ngaphezu kwalokho, ungabona ukuthi ukukhokhwa kwanyanga zonke ngo-12.5% ​​ngonyaka okutholwe ngenxa yokusebenzisa ithebula lokuhlola kufana nenani lemali efanayo yenzalo esiyitholile ngokusebenzisa umsebenzi PMT. Lokhu kuphinda kufakazele ukunemba kwesibalo.

Ngemuva kokuhlaziya loluhlu lwethebula, kufanele kuthiwe, njengoba ubona, kuphela ngesilinganiso se-9.5% ngonyaka sithola isilinganiso esivumelekile senkokhelo yanyanga zonke (ngaphansi kwama-ruble angama-29,000).

Isifundo: Kubalwa inkokhelo yeminyaka yonke e-Excel

Indlela 2: Sebenzisa ithuluzi ngezinto ezimbili ezihlukile

Vele, ukuthola kumanje amabhange akhipha imali mboleko ngo-9.5% ngonyaka kunzima kakhulu, uma kungenakwenzeka. Ngakho-ke, sizobona ukuthi yiziphi izinketho ezikhona zokutshala imali esezingeni elifanelekile lokukhokha njalo ngenyanga ngezinhlanganisela ezahlukahlukene zokunye okuhlukahlukene: usayizi womzimba wemalimboleko kanye nethemu yemalimboleko. Kulokhu, inani lenzalo lizohlala lingashintshiwe (12,5%). Ekuxazululeni le nkinga, ithuluzi lizosisiza. "Ithebula ledatha" kusetshenziswa okuguquguqukayo okubili.

1. Sidweba uhlu olusha lwethebula. Manje kumakholamu amagama azokhonjiswa isikhathi semalimboleko (kusuka 2 ngaphambili 6 iminyaka ngezinyanga ezikhuphukeni zonyaka owodwa), kanye nemigqa - usayizi womzimba wemalimboleko (kusuka 850000 ngaphambili 950000 ama-ruble ezikhuphukeni 10000 ama-ruble). Kulokhu, isidingo sokuqala ukuthi iseli lapho kufakwa khona ifomula yokubala (kithi PMT), etholakala emngceleni womugqa namagama wekholamu. Ngaphandle kwalesi simo, ithuluzi ngeke lisebenze uma kusetshenziswa okuguquguqukayo okubili.



2. Bese ukhetha lonke uhla lwethebula oluphumela kulo, kufaka phakathi amagama amakholomu, imigqa neseli ngefomula PMT. Iya kuthebhu "Idatha". Njengesikhathi esedlule, chofoza inkinobho "Yini uma uhlaziya", eqenjini lamathuluzi "Sebenza nedatha". Kuluhlu oluvulayo, khetha "Ithebula ledatha ...".



3. Iwindi lamathuluzi liyaqala "Ithebula ledatha". Kulokhu, sidinga zombili izinkambu. Ensimini Amanani Amakholamu Wokubamba indawo ku khombisa izixhumanisi zeseli eziqukethe isikhathi semalimboleko kwidatha eyinhloko. Ensimini "Amanani entengo engaphansi khombisa ikheli leseli lamapharamitha okuqala aqukethe inani lomzimba wemalimboleko. Ngemuva kwayo yonke imininingwane efakiwe. Chofoza inkinobho "Kulungile".



4. Uhlelo lwenza ukubala futhi lugcwalisa uhla lwethebula ngemininingwane. Ekuxhumaneni kwemigqa namakholamu manje sekuyabonakala ukuthi imali oyokhokha nyangazonke izoba yiphi, nenani elihambisanayo lenzalo yonyaka kanye nesikhathi semali mboleko ekhonjisiwe.



5. Njengoba ubona, ziningi izindinganiso. Ukuxazulula ezinye izinkinga, kungahle kube nokwengeziwe. Ngakho-ke, ukwenza ukuphuma kwemiphumela kubonakale kakhulu futhi unqume ngokushesha ukuthi yimaphi amanani angagculisi isimo esinikeziwe, ungasebenzisa amathuluzi okubuka. Kithi, lokhu kuzoba ukufomatha okunemibandela. Sikhetha wonke amanani ohla lwethebula, ngaphandle komugqa nezihloko zekholomu.



6. Hambisa kuthebhu "Ikhaya" bese uchofoza isithonjana Ukufomatha okunemibandela. Itholakala kwibhulokhi yamathuluzi. Izitayela ekhasethini. Kumenyu evulayo, khetha Imithetho Yokukhetha Iseli. Uhlu olungeziwe, chofoza isikhundla "Ngaphansi ...".



7. Ukulandela lokhu, iwindi lezilungiselelo zokufometha okunemibandela liyavula. Ensimini yangakwesokunxele khombisa inani elingaphansi kwalokho kuzokhethwa amaseli. Njengoba sikhumbula, sanelisekile ngesimo sokuthi inkokhelo yemalimboleko yanyanga zonke izoba ngaphansi 29000 ama-ruble. Sifaka le nombolo. Ensimini efanele, ungakhetha umbala wokugqamisa, yize ungawushiya ngokuzenzakalelayo. Ngemuva kokuthi wonke amasethingi adingekayo afakiwe, chofoza inkinobho "Kulungile".



8. Emva kwalokho, kuzoqokonyiswa wonke amaseli amanani awo ahambelana nalesi simo esingenhla.

Ngemuva kokuhlaziya uhlu lwamatafula, singafinyelela iziphetho ezithile. Njengoba ubona, kanye nesikhathi esikhona semalimboleko (izinyanga ezingama-36), ukuze sitshale imali ekhonjisiwe yenkokhelo yanyanga zonke, sidinga ukuthatha imalimboleko engeqi ama-ruble angama-860000,00, okungukuthi, ama-40,000 ngaphansi kwalokho obekuhleliwe ekuqaleni.

Uma sisahlose ukuthatha imalimboleko yama-ruble angama-900,000, khona-ke isikhathi semalimboleko kufanele sibe yiminyaka emi-4 (izinyanga ezingama-48). Kulesi simo kuphela, inkokhelo yanyanga zonke ngeke yedlule umkhawulo osungulwe wama-ruble ayi-29,000.

Ngakho-ke, ngokusebenzisa leli banga lethebula futhi kuhlaziywa izinzuzo nokulimala kukhetho ngalunye, obolekayo angenza isinqumo esithile ngokwemibandela yemalimboleko, akhethe inketho efanelekile kunakho konke okusemandleni.

Vele, ithebula lokuhlola lingasetshenziswa hhayi ukubala izinketho zesikweletu kuphela, kepha futhi nokuxazulula ezinye izinkinga eziningi.

Isifundo: Ukufometha okunemibandela ku-Excel

Ngokuvamile, kufanele kuphawulwe ukuthi itafula lokuhlola liyithuluzi eliwusizo kakhulu nelilula lokuthola umphumela wokuhlanganiswa okuhlukahlukene. Usebenzisa ukufomatha okunemibandela ngasikhathi sinye, ngaphezu kwalokho, ungabona ngeso lengqondo ulwazi olutholakele.