Kayan aikin Haske a cikin Microsoft Excel

Hasashen ƙasa muhimmiyar rawa ce ta kusan duk wani aiki na aiki, daga tattalin arziƙi zuwa injiniya. Akwai software da yawa da suka ƙware a wannan fannin. Abin baƙin ciki, ba duk masu amfani ba ne suka san cewa kayan aikin gidan watsa labarai na yau da kullun suna da kayan aikin arsenal don hasashen ƙira, waɗanda ba su da ƙima ga shirye-shiryen ƙwararru a iyawar su. Bari mu gano menene waɗannan kayan aikin kuma yadda za a iya yin hasashen kayan aiki a aikace.

Tsarin Haske

Dalilin kowane tsinkaya shine gano yanayin da ake ciki yanzu, da kuma tantance sakamakon da ake tsammanin dangane da abin da aka yi nazari akai a wani lokaci a nan gaba.

Hanyar 1: layin Trend

Daya daga cikin shahararrun nau'ikan tsinkayar hoto a cikin Excel shine karin haske ta hanyar gina layin zamani.

Bari muyi kokarin yin hasashen adadin ribar da kamfanin ya samu a cikin shekaru 3 bisa la’akari da bayanai kan wannan manuniya na shekaru 12 da suka gabata.

1. Mun gina jadawalin dogaro bisa tsarin data tabo wanda ya kunshi muhawara da dabi'u na aiki. Don yin wannan, zaɓi yankin tebur, sannan, kasancewa a cikin shafin Saka bayanai, danna kan gunkin nau'in tambarin da ake so, wanda yake a sandar Charts. Sannan mun zaɓi nau'in da ya dace da takamaiman yanayi. Zai fi kyau a zabi ginshiƙi mai watsarwa. Kuna iya zaɓar wani kallo, amma, don haka, don a nuna bayanan daidai, za ku sami yin gyare-gyare, musamman, cire layin gardamar kuma zaɓi sabon ma'aunin tsinkayar.



2. Yanzu muna buƙatar gina layin aiki. Mun danna-dama kan kowane daga cikin maki a cikin zane. A cikin yanayin mahallin da aka kunna, dakatar da zaɓi akan abu Sanya layin Trend.



3. Tsarin tsara layin zamani yana buɗewa. A ciki zaka iya zaɓar ɗayan nau'ikan kusan shida:
   • Layi;
   • Logarithmic;
   • Exponential;
   • .Arfi;
   • Polynomial;
   • Tace mai layi.

   Bari mu fara da zabar kwatancen layi.

   A cikin toshe saitin "Tsinkaya" a fagen "Ci gaba zuwa" saita lamba "3,0", tunda muna buƙatar yin hasashen shekaru uku a gaba. Bugu da kari, zaku iya duba akwatin kusa da saitunan. "Nuna daidaito a cikin zane" da "Sanya kwatankwacin tabbacin kimar (R ^ 2) akan zane". Alamar ƙarshe ta nuna ingancin layin da ake yi. Bayan an saita saiti, danna maballin Rufe.



4. An gina layin Trend kuma daga gare shi zamu iya ƙayyade kimanin adadin ribar a cikin shekaru uku. Kamar yadda muke gani, a wannan lokacin ya zama sama da 4500 dubu rubles. Mai aiki R2Kamar yadda aka ambata a sama, yana nuna ingancin layin da ake yi. A cikin lamarinmu, darajar R2 gyara 0,89. Higheraukaka mafi girma, mafi girman amincin layin. Matsakaicin matsayinta na iya zama ɗaya 1. An yarda dashi gabaɗaya tare da mai aiki tare a sama 0,85 layin Trend amintacce ne.



5. Idan matakin amincewa bai dace da ku ba, to, zaku iya komawa zuwa layin layin sabon tsari kuma zaɓi kowane nau'in na kusanci. Kuna iya gwada duk zaɓuɓɓukan da ake samu don nemo mafi daidai.

   

   Ya kamata a sani cewa jigajanan amfani da zato ta hanyar layin zai iya zama mai tasiri idan lokacin tsinkayen bai wuce kashi 30% na tushen lokaci ba. Wannan shine, lokacin da muke bincika tsawon shekaru 12, ba za mu iya yin ingantaccen tsinkaya ba sama da shekaru 3-4. Amma ko da a wannan yanayin, zai zama abin dogara idan idan a wannan lokacin ba za a sami majeure mai ƙarfi ba, ko akasin haka, yanayi mai kyau, waɗanda ba a lokutan baya ba.

Darasi: Yadda za a gina layin Trend a Excel

Hanyar 2: mai ba da izini ga mai ba da izini

Extrapolation don tabular bayanai za a iya yi ta hanyar daidaitaccen aikin Excel KYAUTA. Wannan hujja tana cikin nau'in kayan aikin ƙididdiga kuma yana da asalin magana:

= PREDICT (X; sananne_y_values; known_x_values)

"X" magana ce wacce ake bukatar ƙaddara darajar aikin. A cikin lamarinmu, gardamar ita ce shekarar da ya kamata a faɗi hasashen.

Sanannan dabi'u - ginin sanannun ayyukan ƙimar. A cikin yanayinmu, ana yin rawar da yawan riba don lokutan baya.

Sanin x uesimar su ne muhawara wacce sanannun dabi'u na aikin ke daidai. A cikin rawar da suka taka, muna da lambobin shekarun da aka tattara bayanai game da ribar shekarun da suka gabata.

A zahiri, gardamar ba ta zama tazarar lokaci. Misali, yana iya zama zazzabi, kuma kimar aikin na iya zama matakin fadada ruwa lokacin da yake zafi.

Lokacin yin lissafin wannan hanyar, ana amfani da hanyar juyawar layi.

Bari mu kalli yanayin yadda ake amfani da mai aiki KYAUTA a kan wani kankare misali. Theauki teburin duka. Muna buƙatar sanin tsinkayar ribar don 2018.

1. Zaɓi wani ɓoyayyen tantanin halitta akan takardar inda kuka shirya nuna sakamakon aiki. Latsa maballin "Saka aikin".



2. Yana buɗewa Mayan fasalin. A cikin rukuni "Na lissafi" zaɓi sunan "KYAUTA"sannan kuma danna maballin "Ok".



3. Daga nan sai taga gardamar ta fara. A fagen "X" nuna darajar jayayya wacce kake son nemo darajar aikin. A cikin lamarinmu, wannan shine 2018. Saboda haka, muna rubutu "2018". Amma yana da kyau a nuna wannan alamar a cikin tantanin halitta a kan takarda, da kuma a fagen "X" kawai ba da hanyar haɗi zuwa gare shi. Wannan zai ba da damar nan gaba don yin aiki da lissafin kuma idan ya cancanta, a sauƙaƙe shekara.

   A fagen Sanannan dabi'u saka aikin daidaita shafin "Riba na kamfanin". Ana iya yin wannan ta hanyar sanya siginan kwamfuta a cikin filin, sannan kuma riƙe maɓallin linzamin kwamfuta na hagu da kuma sa alama a kan shafi.

   Hakanan a fagen Sanin x uesimar shigar da adireshin shafi "Shekara" tare da bayanai na lokacin da ya gabata.

   Bayan an shigar da dukkan bayanan, danna maɓallin "Ok".



4. Mai aiki yana yin lissafi gwargwadon bayanan da aka shigar kuma yana nuna sakamako akan allo. Domin 2018, an shirya shi don samun riba a cikin yanki na 4,564.7 dubu rubles. Dangane da tebur da aka haifar, zamu iya gina jadawali ta amfani da kayan aikin tsarawa da aka tattauna a sama.



5. Idan ka canza shekara a cikin tantanin da aka yi amfani da shi don shigar da hujja, sakamakon zai canza daidai, kuma jadawalin zai sabunta ta atomatik. Misali, bisa ga hasashe a cikin 2019, yawan ribar zai zama 4637,8 dubu rubles.

Amma kar a manta cewa, kamar yadda ake yin layin cigaban zamani, tsawon lokaci kafin lokacin hasashen kada ya wuce kashi 30% na duk lokacin da aka tara bayanan.

Darasi: Karin bayanai a cikin Excel

Hanyar 3: TREND mai aiki

Tsinkaya, zaku iya amfani da wani aikin - GASKIYA. Hakanan ta ƙunshi nau'ikan masu aiki na ƙididdiga. Syntax dinsa yana kama da kayan aiki na kayan aiki KYAUTA kuma yayi kama da wannan:

= TREND (Saninan da aka sani_y; sanannan dabi'u_x; sabon_shanci_x; [const])

Kamar yadda kake gani, muhawara Sanannan dabi'u da Sanin x uesimar gaba daya yayi daidai da irin abubuwan da ke aiki na mai aiki KYAUTA, da hujja "Sabbar dabi'u x" yayi magana "X" kayan aiki na baya. Bugu da kari, GASKIYA akwai ƙarin hujja "M", amma ba na tilas bane kuma ana amfani dashi ne kawai idan akwai dalilai na dindindin.

Ana amfani da wannan mai amfani da nagarta sosai a gaban aikin dogaro da aikin.

Bari mu ga yadda wannan kayan aikin zai yi aiki tare da tsararrun bayanai iri ɗaya. Don kwatanta sakamakon, muna ayyana matsayin hasashen kamar 2019.

1. Mun tsara tantanin halitta don nuna sakamakon kuma gudu Mayan fasalin a cikin hanyar da ta saba. A cikin rukuni "Na lissafi" Nemo da kuma nuna sunan "GASKIYA". Latsa maballin "Ok".



2. Wurin Shaida Mai aiki Yana buɗewa GASKIYA. A fagen Sanannan dabi'u ta hanyar da aka bayyana a sama mun shigar da daidaitawar shafi "Riba na kamfanin". A fagen Sanin x uesimar shigar da adireshin shafi "Shekara". A fagen "Sabbar dabi'u x" mun shigar da hanyar haɗi zuwa sel inda aka sanya lambar shekarar wanda ya kamata a nuna alamar jigajanar. A cikin lamarinmu, 2019 kenan. Filin "M" bar shi babu komai. Latsa maballin "Ok".



3. Mai aiki yana aiki da bayanai kuma yana nuna sakamakon akan allon. Kamar yadda kake gani, adadin ribar da aka tsara don 2019, wanda aka lissafta ta hanyar dogara da layi, zai zama, kamar yadda a cikin hanyar lissafin da ya gabata, 4637,8 dubu rubles.

Hanyar 4: Mai sarrafawa

Wani aikin da za a iya amfani da shi don tsinkayarwa a cikin Excel shine mai sarrafa GRANTH. Hakanan yana cikin ƙungiyar ƙididdigar kayan aikin, amma, sabanin waɗanda suka gabata, lokacin ƙididdige shi, baya amfani da hanyar dogaro da layi, amma ƙayyadaddun bayanan. Ma’anonin wannan kayan aikin sune kamar haka:

= KYAUTATA (Sana'rorin da aka sani_y; sanannun dabi'u_x; sabon_shanan_x; [const])

Kamar yadda kake gani, muhawara ta wannan aikin daidai maimaita muhawara ta mai aiki GASKIYA, saboda haka ba zamu zauna a kan bayanin su ba a karo na biyu, amma nan da nan ci gaba zuwa aikace-aikacen wannan kayan aikin.

1. Mun zabi tantanin don fitar da sakamakon kuma mu kira shi ta hanyar da ta saba Mayan fasalin. A cikin jerin masu aikin ƙididdiga, nemi abu RANAR, zaɓi shi kuma danna maballin "Ok".



2. Ana kunna taga gardamar na aikin da ke sama. Shigar da bayanai a cikin filayen wannan taga daidai kamar yadda muka shigar da su a cikin taga muhawara mai amfani GASKIYA. Bayan an shigar da bayanin, danna maballin "Ok".



3. Sakamakon sarrafa bayanai an nuna shi akan mai duba a cikin tantanin da aka nuna a baya. Kamar yadda kake gani, wannan lokacin sakamakon shine 4682.1 dubu rubles. Bambanci daga sakamakon sarrafa kayan aiki GASKIYA marasa ƙima, amma suna da samuwa. Wannan ya faru ne saboda gaskiyar cewa waɗannan kayan aikin suna amfani da hanyoyin ƙididdige daban-daban: hanyar dogaro da layi da kuma hanyar dogara da ƙayyadaddun ka'idodi.

Hanyar 5: ma'aikacin LINEAR

Mai aiki LADA a lissafin yana amfani da hanyar ƙididdigar layi. Bai kamata a rikita shi tare da hanyar dogaro da kayan aiki ba. GASKIYA. Syntax kamar haka:

= LINE (Saninan da aka sani_y; sanannan dabi'u_x; sabuwa_sabu_ni; [const]; [kididdiga])

Hujja biyun da ta gabata ba na tilas bane. Tare da biyun farko, mun saba da hanyoyin da suka gabata. Amma wataƙila kun lura cewa babu wata muhawara a cikin wannan aikin da ke nuni da sababbin dabi'u. Gaskiyar ita ce cewa wannan kayan aikin kawai yana ƙayyade canji a cikin kudaden shiga ta naúrar zamani, wanda a cikin yanayinmu daidai yake da shekara ɗaya, amma dole ne mu ƙididdige jimlar sakamakon dabam, ƙara sakamakon lissafin mai aiki zuwa ƙimar ƙimar ƙarshe na ƙarshe. LADAsau yawan shekaru.

1. Mun zabi satin da za'a aiwatar da lissafin kuma za ayi gudanar da Aiki tare. Zaɓi suna LINEIN a cikin rukuni "Na lissafi" kuma danna maballin "Ok".



2. A fagen Sanannan dabi'u, da taga bude muhawara, shigar da daidaitawar shafi "Riba na kamfanin". A fagen Sanin x uesimar shigar da adireshin shafi "Shekara". Sauran filayen an bar su babu komai. Saika danna maballin "Ok".



3. Shirin yana lissafin kuma yana nuna darajar Trend Trend a cikin tantanin da aka zaba.



4. Yanzu dole ne mu gano girman ribar da aka tsara don shekarar 2019. Saita alamar "=" ga kowane sel mara komai a kan takardar. Mun danna wayar da ke kunshe da adadin ribar da aka samu a shekarar data gabata (2016). Mun sanya alama "+". Bayan haka, danna kan wayar wanda ke kunshe da yanayin layin da aka lissafa a baya. Mun sanya alama "*". Tun daga tsakanin shekarar da ta gabata ta lokacin nazari (2016) da shekarar da kuke son yin hasashen (2019), tsawon lokacin shekaru uku kenan, mun sanya lamba a cikin tantanin. "3". Don yin lissafi danna maɓallin Shigar.

Kamar yadda kake gani, ƙaddarar ribar da aka ƙaddara ta hanyar ƙididdigar layi a cikin 2019 zai kai 4,614.9 dubu rubles.

Hanyar 6: LGRFPPRIBLE mai aiki

Kayan aiki na karshe da zamu duba zai kasance YADDA. Wannan mai aikin yana yin lissafin ne gwargwadon hanyoyin ƙididdigewa. Harshen sa na faɗi yana da tsarin mai zuwa:

= LGRFPRIBLE (Karatun da aka sani_y; sanannan dabi'u_x; sabon_shanci_x; [const]; [kididdiga])

Kamar yadda kake gani, duk mahaɗan gaba ɗaya suna maimaita abubuwan da suka dace da aikin da ya gabata. Algorithm na ƙididdigar annabta zai canza kaɗan. Aikin yana kirga yawan yanayin aiki, wanda ke nuna sau nawa adadin kudaden shiga zai canza zuwa lokaci daya, wato har shekara daya. Muna buƙatar gano bambanci a cikin riba tsakanin lokacin ƙarshe na ƙarshe da na farkon wanda aka shirya, ninka shi da adadin lokacin da aka tsara (3) kuma ƙara da sakamakon jimlar lokacin ƙarshe na ƙarshe.

1. A cikin jerin masu gudanar da Aiki mai aiki, zaɓi suna LGRFPPRIBL. Latsa maballin "Ok".



2. Daga nan sai taga gardamar ta fara. A ciki, muna shigar da bayanai daidai kamar yadda muka yi, ta amfani da aikin LADA. Latsa maballin "Ok".



3. Sakamakon takaddar ƙayyadaddun halaye ana lissafta kuma an nuna shi a cikin sel da aka tsara.



4. Mun sanya alama "=" a cikin tantanin halitta. Bude kwarjinin kuma zaɓi wayar da ke kunshe da darajar kuɗin shiga na ƙarshe na ƙarshe. Mun sanya alama "*" sannan ka zabi tantanin da ke kunshe da yanayin bayyana. Mun sanya alamar debewa kuma danna wani abu kuma wanda kudaden shiga na zamani ya kasance. Rufe tambarin kuma fitar da haruffa "*3+" ba tare da ambato ba. Haka kuma, danna kan wayar da aka zaba don ƙarshe. Don aiwatar da lissafin, danna maɓallin Shigar.

Jectedimar da aka ƙaddara a 2019, wanda aka ƙididdige ta hanyar ƙididdigar adadin ƙididdigar, zai zama 4,639.2 dubu rubles, wanda kuma ba ya bambanta sosai da sakamakon da aka samu a ƙididdigar da ta gabata.

Darasi: Sauran ayyukan ƙididdiga a cikin Excel

Mun gano yadda ake yin tsinkaya a cikin shirin Excel. Ana iya yin wannan ta hanyar hoto ta hanyar amfani da layin da ake yi, tare da bincike ta hanyar amfani da adadin ayyukan ƙididdiga. Sakamakon sarrafa bayanai iri ɗaya daga waɗannan masu aikin, ana iya samun sakamako daban. Amma wannan ba abin mamaki bane, tunda duka suna amfani da hanyoyin ƙididdige daban. Idan yanayin canzawa yayi karami, to duk waɗannan zaɓuɓɓukan da suka dace da wani yanayin ana iya ɗaukar su amintacce.