Solution of equation system mu Microsoft Excel

Kawirikawiri, amafunika kuwerengera zotsatira zomaliza zotsatizanasiyana zosiyanasiyana za deta. Potero, wogwiritsa ntchito adzatha kufufuza njira zonse zomwe mungathe kuchita, sankhani omwe zotsatira zake zokhudzana ndi kugwirizana zimamukhutiritsa, ndipo potsiriza, sankhani njira yabwino kwambiri. Mu Excel, pali chida chapadera cha ntchitoyi - "Tsamba la Deta" ("Gome loyang'ana"). Tiyeni tipeze momwe tingagwiritsire ntchito kuti tichite zochitikazi.

Onaninso: Kusankhidwa kwa Parameter ku Excel

Kugwiritsa ntchito tebulo la deta

Chida "Tsamba la Deta" Yalinganizidwa kuti iwerengere zotsatirayo ndi kusiyana kosiyana kwa chimodzi kapena ziwiri zofotokozedwa. Pambuyo mawerengedwe, zosankha zonse zikhoza kuwoneka mwa mawonekedwe a tebulo, lomwe limatchedwa matrix of analysis analysis. "Tsamba la Deta" amatanthauza gulu la zida "Kusanthula-ngati" kusanthulayomwe imayikidwa pa mpiru mu tab "Deta" mu block "Kugwira ntchito ndi deta". Pambuyo pa Excel 2007, chida ichi chinali ndi dzina. "Gome loyang'ana"zomwe zikuwonetseratu zofunikira zake kuposa dzina lomweli.

Tebulo loyang'ana lingagwiritsidwe ntchito nthawi zambiri. Mwachitsanzo, njira yowonjezera ndiyomwe muyenera kuwerengera kuchuluka kwa malipiro a ngongole ya mwezi uliwonse ndi zosiyana zosiyana ndi nthawi ya ngongole ndi ndalama za ngongole, kapena nthawi yolipira ndi chiwongoladzanja. Chida ichi chingagwiritsidwenso ntchito pofufuza zitsanzo za polojekiti yachuma.

Koma muyeneranso kudziwa kuti kugwiritsira ntchito chida ichi kungapangitse kusokoneza kayendedwe kake, chifukwa deta imakonzanso nthawi zonse. Choncho, ndikulimbikitsidwa kuti musagwiritse ntchito chida ichi m'magulu ang'onoang'ono omwe mungagwiritse ntchito pothetsa mavuto omwewo, koma kuti mugwiritse ntchito kukopera mafomu pogwiritsira ntchito chikhomo chodzaza.

Ntchito yolungamitsidwa "Ma tebulo" zili m'matawuni akuluakulu, pamene kujambula mafomu angatengere nthawi yochulukirapo, ndipo panthawi yokhayokha, kuthekera kwa zolakwa kukuwonjezeka. Koma ngakhale pakadali pano kulimbikitsidwa kuti musiye kusinthidwa kwatsopano kwa ma formula mu tebulo loyang'ana, kuti mupewe katundu wosafunika pa dongosolo.

Kusiyanitsa kwakukulu pakati pa ntchito zosiyanasiyana za tebulo la deta ndi chiwerengero cha zinthu zomwe zimagwiritsidwa ntchito kuwerengera: zosiyana kapena ziwiri.

Njira 1: Gwiritsani ntchito chidachi ndi chosinthika chimodzi

Nthawi yomweyo tiyeni tiganizire njira pamene deta ya deta ikugwiritsidwa ntchito ndi mtengo umodzi wosinthika. Tengerani chitsanzo chabwino kwambiri cha kubwereketsa.

Kotero, panopa timapatsidwa izi:

• Nthawi yokongoza - zaka 3 (miyezi 36);
• Mtengo wokwana - 900000 rubles;
• Chiwongoladzanja - 12.5% ​​pachaka.

Malipiro amapangidwa kumapeto kwa nthawi yolipira (mwezi) pogwiritsa ntchito dongosolo la annuity, ndiko kuti, mu magawo ofanana. Panthawi imodzimodziyo, kumayambiriro kwa nthawi yonse ya ngongole, malipiro a chiwongoladzanja amapanga gawo lalikulu la malipiro, koma pamene thupi likuchepa, malipiro a chiwongoladzitsitsa amachepa, ndipo kuchuluka kwa kubwezera kwa thupi palokha kumawonjezeka. Malipiro onse, monga tatchulidwa pamwambapa, akhala osasinthika.

Ndikofunika kuwerengera kuti malipiro a mwezi uliwonse adzakhala otani, kuphatikizapo kubwezeredwa kwa thupi la ngongole ndi malipiro a chiwongoladzanja. Kwa ichi, Excel ali ndi woyendetsa PMT.

PMT Ndilo gulu la ndalama ndi ntchito yake ndi kuwerengera ndalama zowononga ngongole ya mwezi uliwonse malinga ndi kuchuluka kwa thupi la ngongole, nthawi ya ngongole ndi chiwongoladzanja. Chidule cha ntchitoyi ndi chonchi.

= PMT (mlingo; nper; ps; bs; mtundu)

"Bet" - Kukangana komwe kumatsimikizira chiwongoladzanja cha chiwongoladzanja cha malipiro a ngongole. Chizindikiro chimayikidwa pa nthawiyi. Nthawi yathu yamalipiro ndi mwezi umodzi. Choncho, mlingo wapachaka wa 12.5% ​​uyenera kuthyoledwa mu chiwerengero cha miyezi pachaka, ndiko kuti, 12.

"Kper" - Kukangana kumene kumatsimikizira kuchuluka kwa nthawi yonse ya ngongole. Mu chitsanzo chathu, nthawiyi ndi mwezi umodzi, ndipo nthawi ya ngongole ndi zaka 3 kapena miyezi 36. Choncho, chiwerengero cha nthawi chidzakhala chaputala 36.

"PS" - kukangana komwe kumatsimikizira kufunika kwa ngongoleyo, ndiko kukula kwa thupi la ngongole panthaŵi yomwe akupereka. Kwa ife, chiwerengero ichi ndi ma ruble 900,000.

"BS" - mtsutso wosonyeza kukula kwa thupi la ngongole panthaŵi yake ya malipiro ake onse. Mwachibadwa, chizindikiro ichi chidzakhala chofanana ndi zero. Mtsutso uwu ndi wosankha. Ngati mumadumphira, akuganiza kuti ndi ofanana ndi nambala "0".

Lembani " - komanso ndewu yokhazikika. Amadziwitsa za nthawi yomwe malipirowo adzapangidwe: kumayambiriro kwa nthawi (parameter - "1") kapena kumapeto kwa nthawi (parameter - "0"). Pamene tikukumbukira, malipiro athu amapangidwa kumapeto kwa mwezi wa kalendala, ndiko kuti, phindu la ndemanga imeneyi lidzakhala lofanana "0". Koma, popeza kuti chizindikirochi sichiloledwa, ndipo mwachisawawa, ngati sichinagwiritsidwe ntchito, mtengowo ukuganiza kuti uli "0", ndiye muchitsanzo chomwe sichigwiritsidwe ntchito konse.

1. Kotero, ife tikupitiriza kuwerengera. Sankhani selo pa pepala pomwe mtengo wowerengeka udzawonetsedwa. Timasankha pa batani "Ikani ntchito".



2. Iyamba Mlaliki Wachipangizo. Sinthani kusintha "Ndalama", sankhani kuchokera pamndandanda wa dzina "PLT" ndipo dinani pa batani "Chabwino".



3. Pambuyo pa izi, pali kutsegulira kwazenera zokhudzana ndi ntchitoyi.

   Ikani cholozera mmunda "Bet"ndiye dinani selo pa pepala ndi mtengo wa chiwongoladzanja chaka chilichonse. Monga mukuonera, makonzedwe ake amasonyezedwa pomwepo m'munda. Koma, monga tikukumbukira, timafunika mlingo uliwonse, choncho timagawaniza zotsatira za 12 (/12).

   Kumunda "Kper" mwanjira yomweyi, ife timalowa mu makonzedwe a maselo otchedwa maselo otchedwa credit. Pankhaniyi, palibe chomwe chiyenera kugawa.

   Kumunda "Ps" muyenera kufotokoza makonzedwe a selo okhala ndi mtengo wa thumba la ngongole. Ife timachita izo. Timayikanso chizindikiro patsogolo pa makonzedwe owonetsedwa. "-". Mfundo ndi yakuti ntchitoyi PMT mwachindunji, zimapereka zotsatira zomaliza ndi chizindikiro cholakwika, kuganizira moyenera za ngongole ya mwezi ndi mwezi. Koma momveka bwino, tikufunikira tebulo la deta kuti likhale lolimbikitsa. Kotero, ife tikuyika chizindikiro "sungani" musanayambe kukambirana. Monga momwe zimadziwira, kuchulukitsa "sungani" on "sungani" potsiriza amapereka kuphatikizapo.

   M'minda "Bs" ndi Lembani " Sitilowetsa deta konse. Timasankha pa batani "Chabwino".



4. Pambuyo pake, wogwiritsira ntchito amawerengetsa ndi kusonyeza selo yoyamba kusankhidwa chifukwa cha msonkho wathunthu wa mwezi - 30108,26 ruble. Koma vuto ndilo kuti wobwereka amatha kulipira ndalama zokwana 29,000 pamwezi, ndiko kuti, ayenera kupeza mabungwe a zopereka za banki ndi chiwongoladzanja chochepa, kapena kuchepetsa thupi la ngongole, kapena kuwonjezera nthawi ya ngongole. Sungani zosankha zosiyanasiyana zomwe mungachite kuti mutithandize kuchitapo kanthu.



5. Poyamba, gwiritsani ntchito tebulo loyang'ana ndi kusintha kosiyana. Tiyeni tiwone momwe kufunika kwa malipiro oyenera mwezi uliwonse kumasiyanasiyana mosiyanasiyana kusiyana ndi mlingo wapachaka, kuyambira 9,5% pachaka ndi kutha 12,5% pa ndi sitepe 0,5%. Zolinga zina zonse zasintha. Dulani mzere wa tebulo, mayina a zipilala zomwe zidzakhale zosiyana ndi kusiyana kwa chiwongoladzanja. Ndi mzerewu "Malipiro a mwezi uliwonse" chokani monga momwe zilili. Selo yake yoyamba iyenera kukhala ndi ndondomeko yomwe tawerengedwera kale. Kuti mudziwe zambiri, mukhoza kuwonjezera mizere "Chiwerengero cha ngongole" ndi "Chiwerengero Chokwanira". Chigawo chimene mawerengedwewa alipo chikuchitika popanda mutu.



6. Kenaka, ife timawerengera kuchuluka kwa ngongole zomwe zikuchitika pakali pano. Kuti muchite izi, sankhani selo yoyamba mumzerewu. "Chiwerengero cha ngongole" ndi kuchulukitsa selo "Malipiro a mwezi uliwonse" ndi "Mawu a ngongole". Pambuyo izi dinani Lowani.



7. Kuwerengera kuchuluka kwa chiwongoladzanja pansi pano, momwemo timachotserapo mtengo wa thupi la ngongole ku chiwerengero cha ngongole. Kuti muwonetse zotsatira pamsankhulidwe dinani pa batani. Lowani. Motero, timapeza ndalama zomwe timabwereketsa pobwezera ngongole.



8. Tsopano ndi nthawi yogwiritsira ntchito chida. "Tsamba la Deta". Sankhani ma tebulo onse, kupatula maina a mzere. Zitatero pitani ku tab "Deta". Dinani pa batani pambali "Kusanthula-ngati" kusanthulayomwe imayikidwa mu gulu la zida "Kugwira ntchito ndi deta" (mu Excel 2016, gulu la zida "Chiwonetsero"). Ndiye pulogalamu yaing'ono imatsegulidwa. Mmenemo timasankha malo "Mndandanda wa Deta ...".



9. Dindo laling'ono limatsegulidwa, lomwe limatchedwa "Tsamba la Deta". Monga mukuonera, ili ndi minda iwiri. Popeza timagwira ntchito yosiyana, timafunikira imodzi yokha. Popeza kusintha kwathu kusinthika kumachitika m'mizere, tidzagwiritsa ntchito mundawu "Makhalidwe osungira ndi ndondomeko". Tikaika cholozera pamenepo, ndiyeno dinani selo muyeso lachidziwitso, zomwe zili ndi phindu la peresenti. Pambuyo pa makonzedwe a selo akuwonetsedwa mmunda, dinani pa batani "Chabwino".



10. Chidacho chimatha ndikudzaza mzere wonse wa tebulo ndi mfundo zomwe zimagwirizana ndi zosankha zosiyana siyana. Ngati muika cholojekiti mu gawo lililonse la tablespaceyi, mukhoza kuona kuti bar ya fomayi siwonetsere kachitidwe ka chiwerengero cha malipiro, koma ndondomeko yapaderayi yopanda malire. Ndikokuti, sizingatheke kusintha kusintha kwa maselo payekha. Chotsani zotsatira za chiwerengero zingakhale palimodzi, osati padera.

Kuwonjezera apo, zikhoza kuzindikirika kuti mtengo wa malipirowo pamwezi pa 12.5% ​​pachaka, wopangidwa pogwiritsa ntchito tebulo loyang'ana, umagwirizana ndi mtengo womwe uli ndi chiwongoladzanja chomwe timalandira pogwiritsa ntchito ntchitoyo PMT. Izi zimatsimikiziranso kulungama kwa mawerengedwe.

Pambuyo pofufuza mndandanda wazithunzizi, ziyenera kunenedwa kuti, monga momwe tikuonera, pokhapokha pa 9,5% pachaka, mlingo wokwanira wolipira mwezi (pansi pa 29,000 rubles) umapezeka.

PHUNZIRO: Kuwerengera kwa ndalama zowonjezera ku Excel

Njira 2: Gwiritsani ntchito chida chamitundu iwiri

Inde, ndizovuta kwambiri, ngati zili zenizeni, kupeza mabanki omwe amapereka ngongole pa 9.5% pachaka. Choncho, tiyeni tiwone njira zomwe tingagwiritse ntchito poyendetsera malipiro ovomerezeka pamwezi pazinthu zosiyana siyana: kukula kwa thupi la ngongole ndi nthawi ya ngongole. Pa nthawi yomweyi, chiwongoladzanja chidzasintha (12.5%). Chida chingatithandize pa ntchitoyi. "Tsamba la Deta" kugwiritsa ntchito mitundu iwiri.

1. Dulani tebulo latsopano. Tsopano mawu oyenerera adzawonetsedwa m'maina a mndandanda (kuchokera 2 mpaka 6 zaka mu miyezi yapakati pa chaka chimodzi), ndi mizere - kukula kwa thupi la ngongole (kuchokera 850000 mpaka 950000 ruble mu increments 10000 rubles). Pankhaniyi, nkofunika kuti selo limene mawerengedwe a chiwerengero ali (kwa ife PMT), ili pamalire a mayina a mzere ndi mndandanda. Popanda vutoli, chidachi sichigwira ntchito pogwiritsa ntchito mitundu iwiri.



2. Kenaka sankhani mtundu wonse wa tebulo, kuphatikizapo mayina a zipilala, mizere ndi selo ndi njirayi PMT. Pitani ku tabu "Deta". Monga kale, dinani pa batani. "Kusanthula-ngati" kusanthulamu gulu la zida "Kugwira ntchito ndi deta". M'ndandanda yomwe imatsegula, sankhani chinthucho "Mndandanda wa Deta ...".



3. Chida chowonekera chimayambira. "Tsamba la Deta". Pankhaniyi, tikusowa minda yonse. Kumunda "Makhalidwe osungira ndi ndondomeko" timafotokozera zogwirizanitsa za selo yomwe ili ndi malipiro ake mu deta yoyamba. Kumunda "Zopindulitsa zazing'ono ndi mizere" tchulani adiresi ya selo ya magawo oyambirira omwe ali ndi mtengo wa thumba la ngongole. Zonsezi zitalowa. Timasankha pa batani "Chabwino".



4. Pulogalamuyi imapanga mawerengedwewo ndipo imadzaza mndandanda wa tebulo ndi deta. Pakati pa mzere wa mizere ndi mizati, tsopano ndi kotheka kuona momwe malipiro amwezi alili, ndi kuchuluka kwake kwa chaka ndi chiwongoladzanja ndi nthawi yowerengetsera.



5. Monga mukuonera, pali zambiri zamakhalidwe abwino. Kuti athetse mavuto ena pangakhale zambiri. Choncho, kuti phindu la zotsatira liwoneke kwambiri ndipo nthawi yomweyo mudziwe kuti ndi mfundo ziti zomwe sizikukhutitsa mkhalidwe wapatsidwa, mungagwiritse ntchito zipangizo zowonetsera. Momwemo ife tidzakhala ndi maonekedwe oyenera. Sankhani malingaliro onse a mndandanda wa tebulo, kuphatikizapo mzere ndi mitu ya mutu.



6. Pitani ku tabu "Kunyumba" ndipo dinani pazithunzi "Mafomu Okhazikika". Ipezeka mu bokosi la zida. "Masitala" pa tepi. Mu menyu yomwe imatsegula, sankhani chinthucho "Malamulo a kusankha kusankhidwa". Mu mndandanda wowonjezera dinani pa malo "Pang'ono ...".



7. Pambuyo pazimenezi, mawonekedwe a mawonekedwe a malembawo amatsegulidwa. Kumanzere kumanzere ife timatchula mtengo, zosakwana zomwe maselo adzasankhidwa. Pamene tikukumbukira, timakhutira ndi momwe chiwongoladzanja cha mwezi ngongole chidzakhalira 29000 ruble. Lowani nambala iyi. Kumalo abwino ndizotheka kusankha mtundu wa kusankha, ngakhale mutatha kuchoka mwachinsinsi. Pambuyo pokonza zonse zofunikira, dinani pa batani. "Chabwino".



8. Pambuyo pake, maselo onse omwe amayenera kulumikizana ndi chikhalidwe cha pamwambawa adzawonetsedwa ndi mtundu.

Pambuyo pofufuza mndandanda wa tebulo, mungathe kupeza mfundo. Monga momwe mukuonera, ndi nthawi yamalonda (miyezi 36), kuti tipeze ndalama zomwe tawonetsera pamwambapa, tifunika kulandira ngongole osadutsa 8,600,000.00 ruble, kapena kuti 40,000 pansi poyambirira.

Ngati tikufuna kubweza ngongole ya ndalama zokwana 900,000, ndiye kuti nthawi ya ngongole iyenera kukhala zaka 4 (miyezi 48). Pokhapokha pokhapokha, malipiro a mwezi uliwonse sadzapitirira malire okwana 29,000.

Potero, pogwiritsa ntchito njirayi ndikuwonetsa ubwino ndi zoyipa za njira iliyonse, wobwereka akhoza kupanga lingaliro lapadera pa ngongole, kusankha chisankho chomwe chimagwirizana ndi zosowa zake.

Inde, tebulo loyang'ana lingagwiritsidwe ntchito osati kungowerengera ndalama zomwe mungasankhe, komanso kuthana ndi mavuto ena ambiri.

PHUNZIRO: Kujambula kwapadera pa Excel

Kawirikawiri, tiyenera kukumbukira kuti tebulo loyang'ana ndi lothandiza kwambiri komanso losavuta kudziwitsa zotsatira za mitundu yosiyana siyana. Pogwiritsa ntchito maonekedwe ovomerezeka pamodzi ndi izo, kuwonjezera, mungathe kuona m'maganizo mwanu uthenga womwe unalandira.