Tebulo la Data mu Microsoft Excel

Nthawi zambiri, mumayenera kuwerengera zotsiriza zomaliza zosakanizira zingapo zamakanidwe. Chifukwa chake, wogwiritsa ntchitoyo azitha kuyesa njira zonse zomwe zingachitike, asankhe iwo omwe zotsatira zake zimamukhutiritsa, ndipo, pomaliza, asankhe njira yabwino kwambiri. Ku Excel, kuti mugwire ntchito iyi, pali chida chapadera - "Tebulo la data" (Gawo Lothandizira) Tiyeni tiwone momwe tingaugwiritsire ntchito kumaliza izi pamwambapa.

Werengani komanso: Kusankhidwa kwa Paramu ku Excel

Kugwiritsa ntchito tebulo la data

Chida "Tebulo la data" Cholinga chake ndi kuwerengera zotsatira zakusintha kwamitundu imodzi kapena iwiri. Pambuyo powerengera, zosankha zonse zomwe zingatheke zimawoneka ngati tebulo, yomwe imatchedwa matrix of factor kusanthula. "Tebulo la data" amatanthauza gulu la zida "Bwanji ngati kusanthula", yomwe imayikidwa pachifuwa "Zambiri" mu block "Gwirani ntchito ndi deta". Isanafike ku Excel 2007, chida ichi chidayitanidwa Gawo Lothandizira, yomwe idawonetsa bwino lomwe tanthauzo lake kuposa dzina lapano.

Gome lofufuza lingagwiritsidwe ntchito nthawi zambiri. Mwachitsanzo, njira yodziwika ndi yomwe muyenera kuwerengera kuchuluka kwa ndalama zomwe mumalipira ngongole mwezi uliwonse pazosintha zosiyanasiyana pakubweza ndi kuchuluka kwa ngongole, kapena nthawi yowerengera ndi chiwongola dzanja. Komanso, chida ichi chitha kugwiritsidwa ntchito pakuwunika mitundu ya polojekiti yogulitsa ndalama.

Koma muyenera kudziwa kuti kugwiritsa ntchito chida ichi mopitilira muyeso kungayambitse kusokonekera kwadongosolo, popeza amafotokozedwanso mosalekeza. Chifukwa chake, tikulimbikitsidwa m'makonzedwe ang'onoang'ono a tebulo kuti muthane ndi mavuto omwewa kuti musagwiritse ntchito chida ichi, koma kugwiritsa ntchito kachitidwe kogwiritsa ntchito chikhomo chodzaza.

Ntchito yolungamitsidwa "Ma tebulo a data" imangokhala m'magulu akulu a matebulo, pamene kukopera mafomu kumatha kutenga nthawi yambiri, ndipo munthawi yomwe akukonzekera eni ake kuthekera kokulakwitsa kumawonjezeka. Koma pankhaniyi, tikulimbikitsidwa kuti tilepheretse kuchuluka kwazomwe tikuchita mwazomwe tikuyika kuti tipewe zinthu zina.

Kusiyana kwakukulu pakati pamagwiritsidwe osiyanasiyana a tebulo la data ndi kuchuluka kwa zosintha zomwe zimakhudzidwa kuwerengedwa: chimodzi mosintha kapena ziwiri.

Njira 1: gwiritsani ntchito chida chimodzi

Nthawi yomweyo tiyeni tiwone zosankha mukamagwiritsa ntchito tebulo la data ndi mtengo umodzi wosiyanasiyana. Tengani zitsanzo zomwe zimakonda kubwereketsa.

Chifukwa chake, pakadali pano timapatsidwa ngongole zotsatirazi:

• Nthawi ya ngongole - zaka 3 (miyezi 36);
• Kuchuluka kwa ngongole - ma ruble 900,000;
• Chiwongola dzanja - 12.5% ​​pachaka.

Malipiro amachitika kumapeto kwa nthawi yolipira (mwezi) malinga ndi dongosolo la ndalama, ndiye kuti, m'magawo ofanana. Nthawi yomweyo, kumayambiriro kwa gawo lonse la ngongole, gawo lalikulu la ndalama ndi chiwongola dzanja, koma thupi likayamba kuzimiririka, chiwongoladzanja cha chiwongola dzanja chimachepa, ndipo kuchuluka kobwezeretsa kwamwini kumachulukanso. Malipiro onse, monga tafotokozera pamwambapa, sanasinthe.

Ndikofunikira kuwerengetsa kuti ndalama zomwe azilipira pamwezi ndizotani, kuphatikizapo kubweza ngongole yapa ngongole ndi chiwongola dzanja. Pazomwezi, Excel ili ndi wothandizira PMT.

PMT ndi gulu la ntchito zachuma ndipo ntchito yake ndi kuwerengera ngongole yanyumba yonse yobweza ngongole mwezi uliwonse malinga ndi gawo la ngongole, nthawi yobwereketsa komanso chiwongola dzanja. Syntax ya ntchitoyi imawonetsedwa ngati

= PLT (mtengo; nper; ps; bs; mtundu)

Pikisano - mkangano womwe umatsimikizira kuchuluka kwa chiwongola dzanja cha ngongole. Chizindikiro chimayikidwa nthawi. Nthawi yathu yolipira ndi yofanana mwezi. Chifukwa chake, chiwerengero cha chaka chilichonse cha 12.5% ​​chikuyenera kugawidwa ndi chiwerengero cha miyezi pachaka, ndiye kuti 12.

"Nper" - mkangano womwe umatsimikizira kuchuluka kwa nthawi yonse yobwereketsa. Mwachitsanzo, nthawi ndi mwezi umodzi, ndipo ngongole ndi zaka 3 kapena miyezi 36. Chifukwa chake, kuchuluka kwa magawo kumayambira 36.

"PS" - mkangano womwe umatsimikiza phindu la ngongoleyo, ndiye kuti ndi kukula kwa bungwe la ngongole panthawi yomwe imaperekedwa. M'malo mwathu, chiwerengerochi ndi ma ruble 900,000.

"BS" - mkangano wowonetsa kukula kwa thupi la ngongole panthawi yolipira kwathunthu. Mwachilengedwe, chizindikiro ichi chidzakhala chofanana ndi zero. Mkanganowu ndi wosankha. Ngati mulidumpha, ndiye kuti ndi lofanana ndi nambala ya "0".

"Mtundu" - komanso mkangano wosankha. Amalengeza kuti ndipomwe ndindalama zomwe zilipidwe: kumayambiriro kwa nthawi (gawo - "1") kapena kumapeto kwa nthawi (gawo - "0") Monga momwe timakumbukira, malipiro athu amapangidwa kumapeto kwa mwezi wa kalendala, ndiye kuti, phindu la mkanganowu likhala lofanana "0". Koma, poganizira kuti chizindikirochi sichokakamizidwa, ndipo mosakakamiza, ngati sichinagwiritsidwe, phindu limayesedwa kuti ndilofanana "0", ndiye pazomwe zikuwonetsedwa zitha kusiyidwa kwathunthu.

1. Chifukwa chake, timapitiriza kuwerengera. Sankhani khungu pa pepalalo pomwe mtengo wowerengedwa uwonetsedwa. Dinani batani "Ikani ntchito".



2. Iyamba Fotokozerani Wizard. Timasunthira ku gululi "Zachuma", sankhani dzinalo mndandandandawo "PLT" ndipo dinani batani "Zabwino".



3. Kutsatira izi, zenera zotsimikizira za ntchito ili pamwambazi zimagwira.

   Ikani wolemba m'munda Pikisano, pambuyo pake timadina foni pachidacho ndi mtengo wa chiwongola dzanja cha pachaka. Monga mukuwonera, ma mgwirizano ake amawonetsedwa nthawi yomweyo m'munda. Koma, monga tikukumbukira, timafunikira mtengo wapamwezi, motero timagawa zotsatila ndi 12 (/12).

   M'munda "Nper" momwemonso timalowetsa ma membala a maselo am'malo okongoza ngongole. Pankhaniyi, simukuyenera kugawana chilichonse.

   M'munda Sal muyenera kufotokozera magwirizanidwe a khungu lomwe lili ndi phindu la ngongole yanyumba. Timachita. Timayikanso chikwangwani pamaso paogwirizanitsa "-". Chowonadi ndi chakuti ntchito PMT mosapumira imapereka zotsatira zomaliza ndi chizindikiro chosasamala, kulingalira moyenera kutayika kwa ngongole mwezi ndi mwezi. Koma pofuna kumveketsa bwino tsatanetsatane wa zomwe tawerengazo, tifunikira kuti chiwerengerochi chikhale chabwino. Chifukwa chake, timayika chizindikiro opanda pamaso pa amodzi ntchito. Kuchulukitsa kumadziwika opanda pa opanda pamapeto amapereka kuphatikiza.

   Kulowa m'minda "Bs" ndi "Mtundu" deta sinalowe konse. Dinani batani "Zabwino".



4. Pambuyo pake, wothandizirayo amawerengera ndikuwonetsa zotsatira za kulipira kwathunthu pamwezi m'selo yosankhidwa - 30108,26 ma ruble. Koma vuto ndiloti wobwereketsa amatha kulipira ndalama zokwana ruble 29,000 pamwezi, ndiye kuti, ayenera kupeza banki yopereka chiwongola dzanja chochepa, kapena kuchepetsa thupi la ngongole, kapena kuwonjezera nthawi yobwereketsa. Tebulo lotiwona lithandizanso kudziwa njira zosiyanasiyana.



5. Choyamba, gwiritsani ntchito tebulo lanthunzi ndi chosinthika chimodzi. Tiyeni tiwone momwe kuchuluka kwa ndalama zomwe azilipira pamwezi zisintha mosiyanasiyana pamiyeso ya pachaka, kuyambira 9,5% pachaka ndi kutha 12,5% pachaka mu zowonjezera 0,5%. Zina zonse zimasinthidwa. Tijambula mndandanda wamatchulidwe, mayina a mizati yake omwe angafanane ndi mitundu yosiyanasiyana ya chiwongola dzanja. Ndi mzerewu "Malipiro a pamwezi" chokani monga momwe ziliri. Selo lake loyamba liyenera kukhala ndi kakhalidwe kamene tidawerengera koyambirira. Kuti mumve zambiri, mutha kuwonjezera mizere "Zonse ngongole" ndi "Zonse Zachidwi". Chipilala chomwe amawerengera chimachitika popanda mutu.



6. Kenako, timawerengetsa ngongole yonse pazomwe zikuchitika. Kuti muchite izi, sankhani khungu loyamba la mzere "Zonse ngongole" ndi kuchulukitsa zomwe zili m'maselo "Kulipira pamwezi" ndi "Mawu okongoletsa". Pambuyo pake, dinani batani Lowani.



7. Kuti tiwerenge chiwongola dzanja chonse chomwe tili nacho masiku ano, timachotsanso ngongole yonse yomwe ili ngongole yonse. Kuti muwonetse zotsatira pazenera, dinani batani Lowani. Chifukwa chake, timapeza kuchuluka komwe timalipira tikubweza ngongole.



8. Tsopano ndi nthawi yoti mugwiritse ntchito chida "Tebulo la data". Timasankha magulu onse a tebulo, kupatula mayina amizere. Pambuyo pake, pitani ku tabu "Zambiri". Dinani batani pa riboni "Bwanji ngati kusanthula"lomwe lili mgulu la chida "Gwirani ntchito ndi deta" (mu Excel 2016, gulu la zida "Zonenedweratu") Kenako menyu yaying'ono imatsegulidwa. Mmenemo timasankha udindo "Tebulo la data ...".



9. Windo laling'ono limatseguka, lomwe limatchedwa "Tebulo la data". Monga mukuwonera, ili ndi magawo awiri. Popeza timagwira ntchito limodzi, timangofunika imodzi yokha. Popeza tisintha mzere wosinthika ndi mizati, tidzagwiritsa ntchito ntchitoyi Kutsatira Zotsatira Zapamwamba mu. Khazikitsani cholozera pamenepo, kenako ndikudina khungu lomwe lili patsamba loyambirira lomwe lili ndi zomwe zilipo. Ma minisitala a foni atawonetsedwa m'munda, dinani batani "Zabwino".



10. Chidachi chimawerengera ndikudzaza gawo lonse la masamba ndi mfundo zomwe zimagwirizana ndi zosankha zosiyanasiyana pazachuma. Ngati mungayika cholozera chilichonse m'ndale iyi, mutha kuwona kuti njira yamulembera siziwonetsa momwe mungawerengere zolipirira, koma kakhalidwe kapadera kakang'ono kosasinthika. Ndiye kuti, tsopano sizingatheke kusintha mfundo zomwe zili m'maselo amodzi payokha. Mutha kufufuta zowerengera zonse palimodzi, osati mosiyana.

Kuphatikiza apo, mutha kuwona kuti ndalama zomwe zimaperekedwa pamwezi pa 12.5% ​​pachaka zopezeka chifukwa chogwiritsa ntchito tebulo lowona zikufanana ndi phindu la kuchuluka komweko komwe tidalandira pogwiritsa ntchitoyo PMT. Izi zikutsimikiziranso kulondola kwa kuwerengera.

Tasanthula mndandanda wamtunduwu, zitha kunenedwa kuti, monga mukuwonera, pokhapokha 9,5% pachaka timalandira gawo lovomerezeka la pamwezi (zosakwana ma ruble 29,000).

Phunziro: Kuwerengera zolipiritsa pachaka ku Excel

Njira 2: gwiritsani ntchito chida ndi mitundu iwiri

Zachidziwikire, kupeza pano mabanki omwe amapereka ngongole pa 9,5% pachaka ndizovuta kwambiri, ngati sizotheka. Chifukwa chake, tiwona zomwe angasankhe kuti azigwiritsa ntchito ndalama zolipirira pamwezi pazinthu zingapo zosiyanasiyana: kukula kwa ngongole yapa ngongole ndi nthawi yobwereketsa. Pankhaniyi, chiwongola dzanja sichikhala chosasinthika (12.5%). Pothetsa vutoli, chida chingatithandize. "Tebulo la data" pogwiritsa ntchito mitundu iwiri.

1. Tijambula mitundu yatsopano. Tsopano pamizere mayina awonetsedwa nthawi yobwereketsa (kuchokera 2 kale 6 zaka m'miyezi yowonjezera pachaka chimodzi, ndi mizere - kukula kwa ngongole (kuchokera 850000 kale 950000 ma rubles mu zowonjezera 10000 ma ruble). Poterepa, chofunikira ndichakuti selo lomwe momwe amawerengera amapezeka (kwa ife PMT), yomwe ili m'malire a mzere ndi mayina amizere. Popanda izi, chida sichigwira ntchito pogwiritsa ntchito mitundu iwiri.



2. Kenako sankhani mndandanda wonse wazomwe mukuyang'ana, kuphatikizapo mayina a mizati, mizere ndi khungu ndi formula PMT. Pitani ku tabu "Zambiri". Monga nthawi yapita, dinani batani "Bwanji ngati kusanthula", pagulu lazida "Gwirani ntchito ndi deta". Pamndandanda womwe umatsegulira, sankhani "Tebulo la data ...".



3. Windo la chida limayamba "Tebulo la data". Poterepa, timafunikira magawo onse awiri. M'munda Kutsatira Zotsatira Zapamwamba mu sonyezani maulalo a khungu lomwe lili ndi nthawi yobwereketsa mu deta yoyamba. M'munda "M'malo motsatira mzere umodzi" onetsani adilesi ya khungu la magawo oyambira omwe ali ndi phindu la ngongole yobwereketsa. Pambuyo poti data yonse idalowa. Dinani batani "Zabwino".



4. Pulogalamuyi imawerengera ndipo imadzaza mndandanda wa tebulo ndi deta. Panjira yolumikizana mizere ndizotheka kuwona momwe ndalamazo zimakhalira mwezi uliwonse, ndi ndalama zogwirizana pachaka komanso chiwongola dzanja chomwe chasonyezedwa.



5. Monga mukuwonera, pali zambiri zofunikira. Kuti muthane ndi mavuto ena, pakhoza kukhala zowonjezereka. Chifukwa chake, kupanga zotsatira ndikuwoneka bwino ndikuwona nthawi yomweyo zomwe sizikukwaniritsa zomwe mwapatsidwa, mutha kugwiritsa ntchito zida zowonera. M'malo mwathu, izi zimakhala zosintha pamakonzedwe. Timasankha zabwino zonse zomwe zili pamtunduwo, kupatula mzere ndi mutu.



6. Pitani ku tabu "Pofikira" ndikudina chizindikiro Njira Zakukonzerani. Ili mu chipangizo. Masitaelo pa tepi. Pazosankha zomwe zimatsegulira, sankhani Malamulo Osankha Maselo. Pa mindandanda yowonjezerapo, dinani pomwe muli "Zochepera ...".



7. Kutsatira izi, zikhalidwe zoika mawonekedwe pazenera zimatsegulidwa. M'munda wakumanzere onetsani mtengo wochepera womwe maselo adzasankhidwe. Monga momwe timakumbukirira, tili okhutitsidwa ndi zomwe ndalama zomwe zingakhale mwezi ulipira zingakhale zochepa 29000 ma ruble. Timalowetsa manambala. Pamtunda woyenera, mutha kusankha mawonekedwe owonetsera, ngakhale mutha kusiya mwachisawawa. Pambuyo pazosintha zonse zofunikira zitaikidwa, dinani batani "Zabwino".



8. Pambuyo pake, maselo onse omwe malingaliro awo amafanana ndi zomwe ali pamwambapa adzawunikidwa.

Tasanthula kuchuluka kwa mndandanda, titha kuzindikira. Monga mukuwonera, ndi ngongole yomwe ilipo (miyezi 36), kuti tipeze ndalama zomwe tawonetsera pamwezi, tikufunika kutenga ngongole yopitilira ma rubles 860000.00, ndiko kuti, 40,000 osakwana momwe anakonzera kale.

Ngati tikufunabe kutenga ngongole ya ma ruble 900,000, ndiye kuti ngongole iyenera kukhala zaka 4 (miyezi 48). Pokhapokha ngati izi, malipiro pamwezi sangapitirire malire okhazikitsidwa ndi ma ruble 29,000.

Chifukwa chake, pogwiritsa ntchito gululi pang'onopang'ono ndikuwona zabwino ndi zovuta zilizonse zomwe wobwereketsa, wobwereketsa angasankhe za ngongole, posankha njira yoyenera kwambiri kuchokera kuzotheka.

Zachidziwikire, tebulo lookupika lingagwiritsidwe ntchito osati kuwerengera zomwe mungasankhe ngongole, komanso kuthana ndi mavuto ena ambiri.

Phunziro: Kusintha Kwa Zinthu mu Excel

Pazonse, ziyenera kudziwidwa kuti tebulo lofufuza ndiwothandiza kwambiri komanso chida chothandiza kudziwa zotsatira za kuphatikiza kosiyanasiyana. Pogwiritsa ntchito mitundu yozungulira nthawi imodzi, kuwonjezera apo, mutha kuwona m'mawu zomwe mwalandira.