Tebur na bayanai a Microsoft Excel

Kusan sau da yawa, kuna buƙatar yin ƙididdigar sakamako na ƙarshe don ɗakuna daban-daban na shigar da bayanai. Don haka, mai amfani zai iya kimanta duk zaɓuɓɓuka masu yiwuwa don ayyuka, zaɓi waɗanda waɗanda sakamakon hulɗarsu suka gamsar da shi, kuma, a ƙarshe, zaɓi zaɓi mafi kyau duka. A cikin Excel, don yin wannan aikin, akwai kayan aiki na musamman - "Tebur data" (Teburin Maimaitawa) Bari mu gano yadda za mu yi amfani da shi don kammala abubuwan binciken da ke sama.

Karanta kuma: Zaɓin siga a cikin Excel

Amfani da tebur data

Kayan aiki "Tebur data" An ƙididdige shi don ƙididdige sakamako saboda bambancin halaye masu yawa na ɗaya ko biyu da aka ƙayyade. Bayan yin lissafi, duk zaɓuɓɓuka masu yiwuwa suna bayyana ne a cikin tebur, wanda ake kira matrix of analysis factor. "Tebur data" yana nufin ƙungiyar kayan aikin "Idan bincike", wanda aka sanya akan kintinkiri a cikin shafin "Bayanai" a toshe "Aiki tare da bayanai". Kafin Excel 2007, an kira wannan kayan aiki Teburin Maimaitawa, wanda har ma ya fi dacewa ya nuna ainihin abin da yake nunawa fiye da sunan yanzu.

Za'a iya amfani da teburin dubawa a yanayi da yawa. Misali, wani zaɓi na yau da kullun shine lokacin da kuke buƙatar yin lissafin adadin biyan bashin kowane wata don bambance-bambancen kuɗi na lokacin lamuni da adadin rance, ko lokacin biyan kuɗi da yawan kuɗi. Hakanan, za'a iya amfani da wannan kayan aiki a cikin nazarin samfuran ayyukan ayyukan zuba jari.

Amma yakamata ku sani cewa amfani da wannan kayan aiki mai yawa na iya haifar da sikelin brakin, kamar yadda ake ta tatsar bayanai akai-akai. Sabili da haka, an bada shawarar a cikin ƙananan teburin tebur don warware irin waɗannan matsalolin ba don amfani da wannan kayan aiki ba, amma don amfani da kwafin dabara ta amfani da alamar cika.

Aikace-aikacen aikace-aikace "Tabatattun bayanai" ne kawai a cikin babban jeri na tebur, lokacin da aka kwafa dabaru na iya ɗaukar lokaci mai yawa, kuma yayin aiwatar da kanta yiwuwar yin kuskure yana ƙaruwa. Amma a wannan yanayin, ana bada shawara a kashe nakasar recalculation na atomatik a cikin kewayon tebur na musanya, don guje wa ɗaukar nauyi a kan tsarin.

Babban bambanci tsakanin amfani da tebur ɗin data shine adadin masu canji da aka haɗa cikin ƙididdigar: mai sau ɗaya ko biyu.

Hanyar 1: yi amfani da kayan aiki tare da m

Nan da nan bari mu kalli zaɓi yayin da aka yi amfani da teburin data tare da ƙimar canji ɗaya. Theauki mafi kyawun misalin lamunin lamuni.

Don haka, a halin yanzu an ba mu sharuɗɗan lamuni kamar haka:

• Lokacin lamuni - shekaru 3 (watanni 36);
• Adadin rance - 900,000 rubles;
• Kudin riba - 12.5% ​​a cikin shekara.

Biya yana faruwa a ƙarshen lokacin biyan kuɗi (wata) gwargwadon tsarin ƙididdigar shekara, wato a cikin daidai rabo. A lokaci guda, a farkon lokacin ajiyar kuɗi, wani sashi mai tsoka na biyan kuɗi shine biyan riba, amma kamar yadda jiki ke ƙaruwa, biyan bashin ya karu, kuma adadin kuɗin jikin da kansa yana ƙaruwa. Adadin kuɗi duka, kamar yadda aka ambata a sama, ya kasance ba a canzawa.

Wajibi ne a lissafa ko menene adadin biya na wata-wata, wanda ya hada da biyan kungiyar rance da biyan bashin. A kan wannan, Excel yana da mai aiki PMT.

PMT nasa ne ga rukunin ayyukan ayyukan kuɗi kuma aikinsa shi ne ƙididdige irin biyan bashin na wata-wata gwargwadon yawan kuɗin rance, rancen rang da riba. Gasar wannan aikin an gabatar dashi azaman

= PLT (kudi; nper; ps; bs; nau'in)

Biya - wata takaddama wacce ke tantance ƙimar biyan kuɗi. An saita mai nuna alama don zamani. Lokacin biyanmu daidai yake da wata daya. Don haka, yakamata a raba adadin shekara na 12.5% ​​da adadin watanni a shekara, wato, 12.

"Muryar ƙasa" - wata takaddama wacce ke tantance yawan lokutan duk lokacin biyan bashi. A cikin misalinmu, lokacin shine wata daya, kuma lokacin aro shine shekaru 3 ko watanni 36. Don haka, adadin lokutan zai kasance da wuri 36.

"PS" - wata mahawara wacce ke tantance kimar ta yanzu, watau, ita ce girman jikin mai bada bashi a lokacin da aka ba ta. A cikin lamarinmu, wannan adadi shine 900,000 rubles.

"BS" - wata mahawara mai nuna girman jikin rance a lokacin cikakken biya. A zahiri, wannan alamar zata yi daidai da sifili. Wannan magana ba na tilas bane. Idan ka tsallake ta, ana tsammanin ta yi daidai da adadin "0".

"Nau'in" - kuma zaɓi ne na zaɓi. Ya sanar da lokacin da za a biya daidai lokacin: a farkon lokacin (sigogi - "1") ko a ƙarshen zamani (sigogi - "0") Kamar yadda muke tunawa, an biya kuɗin mu a ƙarshen watan kalanda, wato, darajar wannan hujja zata kasance daidai da ita "0". Amma, ba da gaskiyar cewa wannan alamar ba ta zama ta wajibi ba, kuma ta asali, idan ba a yi amfani da shi ba, ana nuna darajar ta zama daidai "0", sannan a cikin misalin da aka nuna ana iya cire shi baki daya.

1. Don haka, muna ci gaba zuwa lissafin. Zaɓi sel a kan takardar inda za a nuna ƙididdigar ƙididdigar ta. Latsa maballin "Saka aikin".



2. Ya fara Mayan fasalin. Mun matsa zuwa rukuni "Kudi", zaɓi sunan daga jeri "PLT" kuma danna maballin "Ok".



3. Bayan wannan, an kunna aikin muhawara na aikin da ke sama.

   Sanya siginan kwamfuta a cikin filin Biya, bayan haka mun danna kan tantanin da ke kan takardar tare da ƙimar kuɗin ƙimar shekara-shekara. Kamar yadda kake gani, daidaitawarsa ana nuna shi nan take a fagen. Amma, kamar yadda muka tuna, muna buƙatar ƙimar kowane wata, sabili da haka mun rarraba sakamakon ta 12 (/12).

   A fagen "Muryar ƙasa" Haka kuma muna shiga cikin ayyukan sel na ajalin aro. A wannan yanayin, ba kwa buƙatar raba komai.

   A fagen Zab kuna buƙatar ƙididdigar ayyukan haɗin sel wanda ke ɗauke da ƙimar jikin lamunin. Muna yi. Mun kuma sanya alamar a gaban abubuwan daidaitawar da aka nuna "-". Gaskiyar ita ce aikin PMT ta hanyar tsoho yana ba da sakamako na ƙarshe tare da alama mara kyau, da kyau la'akari da asarar biyan kuɗi na kowane wata. Amma don tsinkaye aikace-aikace na teburin bayanai, muna buƙatar wannan lambar don zama tabbatacce. Saboda haka, mun sanya alama debewa kafin ɗayan muhawara na aiki. An san abubuwa da yawa debewa a kunne debewa a karshen bada da.

   A cikin filayen "Bs" da "Nau'in" ba a shigar da bayanai kwata-kwata ba. Latsa maballin "Ok".



4. Bayan wannan, ma'aikacin ya ƙididdige kuma ya nuna sakamakon jimlar biyan kowane wata a cikin tantanin da aka riga aka tsara - 30108,26 rubles. Amma matsalar ita ce, mai ba da bashi zai iya biyan iyakar 29,000 rubles a wata, wato, yakamata ya sami banki da ke ba da yanayin tare da ƙaramar riba, ko rage ƙungiyar rance, ko kuma ƙara adadin lokacin aro. Teburin neman zai taimaka mana gano hanyoyin zaɓuɓɓuka da yawa.



5. Da farko, yi amfani da teburin dubawa tare da m. Bari mu ga yadda adadin biyan kuɗi na wata-wata zai canza tare da bambance-bambancen ragi na shekara-shekara, farawa daga 9,5% kowace shekara da ƙarewa 12,5% kowace shekara a kari 0,5%. Duk sauran sauran al'adun an bar su ba su canzawa. Mun zana kewayon tebur, sunayen sassan abubuwan da zasu dace da bambancin daban-daban na ƙimar sha'awa. Tare da wannan layin "Biyan Bilkaba" bar kamar yadda yake. Cellwayar ta na farko ya kamata ya ƙunshi tsarin da muka lissafta a baya. Don ƙarin bayani, zaku iya ƙara layuka Jimlar bada lamuni da "Gaba daya sha'awa". Shafin da ke ciki ana yin lissafin ne ba tare da taken ba.



6. Na gaba, muna lissafta jimlar rance a ƙarƙashin halayen yanzu. Don yin wannan, zaɓi sashin farko na jere Jimlar bada lamuni kuma ninka abubuwan da ke cikin sel "Biyan biya na wata" da "Lamunin lamuni". Bayan haka, danna maɓallin Shigar.



7. Yin lissafta jimlar riba karkashin yanayin yanzu, haka mu ma rage yawan jikin aro daga yawan aro. Don nuna sakamakon akan allo, danna maɓallin Shigar. Don haka, muna samun adadin abin da muka biya lokacin biyan bashin.



8. Yanzu lokaci ya yi da za a yi amfani da kayan aiki "Tebur data". Mun zaɓi ɗayan teburin gabaɗaya, ban da sunayen layi. Bayan haka, je zuwa shafin "Bayanai". Danna maɓallin a kan kintinkiri "Idan bincike"wanda ke cikin rukunin kayan aiki "Aiki tare da bayanai" (a cikin Excel 2016, ƙungiyar kayan aiki "Tsinkaya") Sannan ƙaramin menu ya buɗe. A ciki muke zaɓi matsayi "Teburin data ...".



9. Wani karamin taga yana bude, wanda ake kira "Tebur data". Kamar yadda kake gani, yana da filaye biyu. Tunda muna aiki tare da mai lamuni ɗaya, muna buƙatar ɗayansu. Tunda mun canza lamba mai canzawa ta shafi, zamuyi amfani da filin Ididdigar Girman stan Naya cikin. Saita siginan kwamfuta a wurin, sannan danna kan tantanin halitta a cikin takaddun bayanan na ainihi wanda ya ƙunshi yawan kashi na yanzu. Bayan an nuna ayyukan tantancewar a cikin filin, danna maballin "Ok".



10. Kayan aiki yana yin lissafi da cika duka tabular tare da dabi'un da suka dace da zaɓuɓɓuka daban-daban don ƙimar sha'awa. Idan kun sanya siginan kwamfuta a kowane ɓangaren wannan yanki na teburin, zaku iya gani cewa masarar dabara bata nuna ƙididdigar yau da kullun don ƙididdige biyan, amma dabara ta musamman don tsarin da ba za a iya jurewa ba. Wannan shine, yanzu ba shi yiwuwa a canza dabi'u a cikin sel ɗaya. Zaka iya share sakamakon lissafi kawai gaba daya, kuma ba daban.

Bugu da kari, zaku iya ganin cewa biyan wata-wata a kashi 12.5% ​​a cikin kowace shekara da aka samu sakamakon saka teburin duba yana dacewa da ƙimar daidai adadin kuɗin da muka karɓa ta hanyar sanya aikin PMT. Wannan ya sake tabbatar da daidai lissafin.

Bayan nazarin wannan tebur ɗin, ya kamata a faɗi cewa, kamar yadda kuke gani, kawai a cikin kudi na 9.5% kowace shekara muna samun matakin biyan kuɗi na wata-wata (ƙasa da 29,000 rubles).

Darasi: Lissafin biyan kowace shekara a Excel

Hanyar 2: yi amfani da kayan aiki tare da masu canji biyu

Tabbas, samun bankuna na yanzu waɗanda ke ba da rance a kashi 9.5% a cikin shekara yana da wuya sosai, idan ba zai yiwu ba. Sabili da haka, zamu ga irin zaɓuɓɓukan da suke wanzu don saka jari a matakin karɓa na biyan kuɗi na wata-wata don haɗuwa iri daban-daban na sauran masu canji: girman jikin mai rance da lokacin aro. A wannan halin, ƙimar sha'awa ba zai canzawa ba (12.5%). A warware wannan matsalar, kayan aiki zai taimaka mana. "Tebur data" amfani da masu canji guda biyu.

1. Mun zana sabon tsarin tebur. Yanzu a cikin jerin sunaye za a nuna lokacin aro (daga 2 a da 6 shekaru a cikin watanni a cikin ƙaruwa na shekara guda), kuma a cikin layi - girman jikin lamuni (daga 850000 a da 950000 rubles a kari 10000 rubles). A wannan yanayin, abin da ake bukata shine ainihin tantanin halitta wanda yake cikin ƙididdigar lissafin aiki (a cikin lamarinmu) PMT), wanda ke kan iyakar layin da sunayen sunaye. Ba tare da wannan yanayin ba, kayan aikin ba zai yi aiki ba yayin amfani da masu canji guda biyu.



2. Sannan zaɓi duka sakamakon kewayon tebur, gami da sunayen ginshiƙai, layuka da tantanin halitta tare da dabara PMT. Je zuwa shafin "Bayanai". Kamar yadda ya gabata, danna maɓallin "Idan bincike", a cikin rukunin kayan aiki "Aiki tare da bayanai". Cikin jeri dake buɗe, zaɓi "Teburin data ...".



3. Ana fara aiki da taga kayan aiki "Tebur data". A wannan yanayin, muna buƙatar filayen biyu. A fagen Ididdigar Girman stan Naya cikin nuna daidaitawar tantanin da ke dauke da ajalin aro a cikin bayanan farko. A fagen "Canza dabi'u masu jere nuna adireshin tantanin gidan sigogi na farko wanda ke ɗauke da darajar jikin rancen. Bayan an shigar da bayanan duka. Latsa maballin "Ok".



4. Shirin yana yin lissafin kuma yana cika kewayon tebur tare da bayanai. A tsakiyar hanyoyin layuka da ginshiƙai yanzu ana iya lura da abin da daidai lokacin biya zai kasance, tare da adadin adadin amfani na shekara-shekara da ajalin aro.



5. Kamar yadda kake gani, akwai kyawawan dabi'u masu yawa. Don magance wasu matsaloli, ana iya samun ƙari. Sabili da haka, don samar da sakamakon sakamako ya zama mafi gani kuma nan da nan ƙaddara waɗanne dabi'u basu gamsar da yanayin da aka bayar ba, zaku iya amfani da kayan aikin gani. A cikin lamarinmu, wannan zai zama tsarin tsari ne. Mun zaɓi duk dabi'u na kewayon tebur, ban da jere da kanun kan layi.



6. Matsa zuwa shafin "Gida" kuma danna kan gunkin Tsarin Yanayi. An samo shi a cikin toshe kayan aiki. Salo a kan tef. A menu na buɗe, zaɓi Dokokin Zabi na Cell. A cikin ƙarin jerin, danna kan matsayin "Kadan ...".



7. Bayan wannan, taga tsarin tsara yanayin yana buɗe. A cikin filin hagu yana nuna ƙimar ƙasa da wacce za'a zaɓi ƙwayoyin. Kamar yadda muke tunawa, mun gamsu da yanayin cewa biyan bashin kowane wata zai zama ƙasa da yadda yake 29000 rubles. Mun shigar da wannan lambar. A cikin madaidaicin filin, zaku iya zaɓar launi mai alama, kodayake zaku iya barin ta ta asali. Bayan an shigar da dukkan saitunan da ake buƙata, danna maɓallin "Ok".



8. Bayan haka, duk sel waɗanda dabi'un su suka dace da yanayin da ke sama za a nuna su.

Bayan mun yi nazari akan tsarin teburin, zamu iya kawo wasu yanke shawarwari. Kamar yadda zaku iya gani, tare da lokacin biyan bashin (watanni 36), don saka hannun jari a cikin adadin da aka nuna na biyan wata-wata, muna buƙatar ɗaukar rancen da bai wuce 860000.00 rubles ba, wato 40,000 ƙasa da yadda aka tsara tun farko.

Idan har yanzu muna da niyyar karɓar lamuni na 900,000 rubles, to ajalin aro ya zama shekaru 4 (48 watanni). Kawai a wannan yanayin, biyan kuɗin wata ba zai wuce iyakar kafa 29,000 rubles ba.

Don haka, ta amfani da wannan tebur da kuma bincika fa'idodi da halaye na kowane zaɓi, mai ba da bashi na iya yin takamaiman yanke shawara kan sharuɗan aro, zaɓi zaɓi mafi dacewa daga duk mai yiwuwa.

Tabbas, za a iya amfani da teburin duba ba kawai don ƙididdige zaɓuɓɓukan lamuni ba, har ma don magance sauran matsalolin da yawa.

Darasi: Tsarin Kayan Yanayi a Excel

Gabaɗaya, ya kamata a lura cewa teburin duba kayan aiki ne mai matukar amfani kuma mai sauƙin kayan aiki don ƙayyade sakamako don haɗuwa iri-iri masu bambanci. Yin amfani da tsara tsararren yanayi a lokaci guda, a ƙari, zaku iya ganin hoton da aka karɓa.