Matsar Matsakaicin Hanyar a Microsoft Excel

Hanyar matsakaita matsakaici shine kayan aiki na ƙididdiga wanda zaka iya magance nau'ikan matsaloli iri daban-daban. Musamman, ana amfani dashi sauƙaƙe. A cikin Excel, zaka iya amfani da wannan kayan aikin don magance matsalolin da yawa. Bari mu ga yadda ake amfani da matsakaiciyar motsi a cikin Excel.

Matsar da Matsakaicin Aikace-aikacen

Ma'anar wannan hanyar ita ce cewa tare da taimakonsa, an canza kyawawan dabi'un jerin abubuwan da aka zaɓa zuwa lissafin ma'anar ilmin lissafi na wani lokaci ta hanyar kwantar da bayanan. Ana amfani da wannan kayan aiki don ƙididdigar tattalin arziki, tsinkaya, kan aiwatar da ciniki kan musanya, da dai sauransu. Amfani da matsakaiciyar hanyar motsi na Excel shine mafi kyawun yin shi ta amfani da kayan aiki mai ƙididdiga na ƙididdiga Kunshin bincike. Hakanan zaka iya amfani da aikin ginanniyar aikin Excel don manufa guda. KYAUTA.

Hanyar 1: Fakitin Bincike

Kunshin bincike shine addara mai Excelarshe da aka kashe ta hanyar tsohuwa. Sabili da haka, da farko, kuna buƙatar kunna shi.

1. Matsa zuwa shafin Fayiloli. Danna abu. "Zaɓuɓɓuka".



2. A cikin sigogi na taga wanda zai buɗe, je zuwa sashin "Karin abubuwa". A kasan taga a cikin akwatin "Gudanarwa" dole ne a saita sigogi Addara Add-ins. Latsa maballin Je zuwa.



3. Mun shiga cikin taga add-kan. Duba akwatin kusa da Kunshin Nazarin kuma danna maballin "Ok".

Bayan wannan aikin, kunshin "Nazarin Bayanai" an kunna, kuma mabuɗin mai dacewa ya bayyana a kan kintinkiri a cikin shafin "Bayanai".

Yanzu bari mu bincika yadda zaka iya amfani da fasalin kunshin kai tsaye. Nazarin bayanai don matsakaicin matsakaiciyar motsi. Bari mu yi jigajancin watan na sha biyu bisa la’akari da bayanin kudin shiga na kamfanin na tsawon shekaru 11 da suka gabata. Don yin wannan, zamu yi amfani da tebur cike da bayanai, da kayan aikin Kunshin bincike.

1. Je zuwa shafin "Bayanai" kuma danna maballin "Nazarin Bayanai", wanda aka sanya a kan kintinkon kayan aiki a cikin toshe "Bincike".



2. Jerin kayan aikin da ake da su a ciki Kunshin bincike. Zabi suna daga cikinsu Matsakaita Matsakaici kuma danna maballin "Ok".



3. An ƙaddamar da taga shigarwa na bayanan tsinkayar matsakaita matsakaita.

   A fagen Tsarin Tsarin Input nuna adireshin kewayon inda aka samo adadin kuɗin wata-wata ba tare da tantanin da za'a lissafta bayanan ba.

   A fagen Tazara Ya kamata ku saka tazara ta sarrafa abubuwa ta hanyar kyakyawa. Da farko, bari mu saita ƙimar smoothing zuwa watanni uku, sabili da haka shigar da lamba "3".

   A fagen "Matsakaicin fitarwa" kuna buƙatar ƙaddamar da kewayon fanko na sabani a kan takardar inda za a nuna bayanan bayan an gama aiki, wanda ya kamata ya zama sel ɗaya mafi girma fiye da tazara.

   Hakanan duba akwatin kusa da sigogi. "Tabbatattun kurakurai".

   Idan ya cancanta, Hakanan zaka iya duba akwatin kusa da "Fitar da hoto" don zanga-zangar gani, kodayake a cikin yanayinmu wannan ba lallai ba ne.

   Bayan an gama dukkan saiti, danna maballin "Ok".



4. Shirin yana nuna sakamakon aiki.



5. Yanzu za mu yi aiki mai sauƙi a cikin tsawon watanni biyu don bayyana wane sakamako ne mafi daidai. Don waɗannan dalilai, sake kunna kayan aiki. Matsakaita Matsakaici Kunshin bincike.

   A fagen Tsarin Tsarin Input mun bar waɗannan dabi'u kamar yadda suke a baya.

   A fagen Tazara sanya lamba "2".

   A fagen "Matsakaicin fitarwa" saka adireshin sabuwar fanko, wanda, kuma, ya kamata ya zama sel ɗaya ya fi girma tsakanin tazara.

   Sauran saitunan an bar su ba canzawa. Bayan haka, danna maɓallin "Ok".



6. Bayan wannan, shirin yana lissafi kuma yana nuna sakamako akan allon. Don sanin ko wanne ne daga cikin samfura biyu da ya fi daidai, muna buƙatar kwatanta madaidaiciyar kuskure. Karamin wannan mai nuna alama, mafi girman yiwuwar gaskiyar sakamakon. Kamar yadda kake gani, ga dukkan dabi'u, daidaitacciyar kuskure a cikin yin lissafin tsalle-tsalle na watanni biyu kasa da alamar guda ɗaya tsawon watanni 3. Don haka, ƙimar da aka ƙaddara don Disamba za a iya ɗaukar ƙimar da aka ƙididdige ta hanyar maɓallin hanyar ƙarshe. A cikin lamarinmu, wannan darajar shine 990,4 dubu rubles.

Hanyar 2: ta amfani da AVERAGE

A cikin Excel akwai wata hanya don amfani da matsakaicin matsakaiciyar hanyar. Don amfani da shi, kuna buƙatar amfani da daidaitattun ayyukan shirye-shiryen daidaitattun abubuwa, waɗanda tushen su don amfaninmu KYAUTA. A matsayin misali, zamu yi amfani da tebur iri ɗaya na kudaden shiga kamar yadda a farkon yanayin.

Kamar yadda lokacin ƙarshe, muna buƙatar ƙirƙirar jerin lokaci mai sauƙi. Amma a wannan karon, ayyukan ba za su zama masu sarrafa kansa ba. Ya kamata ku ƙididdige matsakaita na kowane biyun, sannan wata uku, don a iya gwada sakamakon.

Da farko, muna lissafin matsakaicin ƙimar abubuwan da suka gabata na shekaru biyu ta amfani da aikin KYAUTA. Za mu iya yin wannan kawai fara daga Maris, saboda don kwanakin ƙarshe akwai hutu a cikin dabi'u.

1. Zaɓi waya a cikin akwati mara komai a jere don Maris. Bayan haka, danna kan gunkin "Saka aikin"wanda aka sanya a kusa da masarar dabara.



2. An kunna taga Wizards na Aiki. A cikin rukuni "Na lissafi" neman ma'ana SRZNACH, zaɓi shi kuma danna maballin "Ok".



3. Shafin Hujja Mai Furewa Mai Aiki KYAUTA. Gaskiyar magana kamar haka:

   = SAURARA (lamba1; lamba2; ...)

   Hujja daya ce kawai ake bukata.

   A lamarinmu, a fagen "Lambar1" dole ne mu samar da hanyar haɗi zuwa kewayon inda aka samu kudin shiga na lokatan biyu da suka gabata (Janairu da Fabrairu). Saita siginan kwamfuta a cikin filin kuma zaɓi sel masu dacewa a kan takardar a cikin shafi Haraji. Bayan haka, danna maɓallin "Ok".



4. Kamar yadda kake gani, sakamakon lissafin matsakaicin darajar na lokutan biyu da suka gabata an nuna shi a cikin tantanin halitta. Don yin lissafin irin wannan duka sauran watanni na wannan lokacin, muna buƙatar kwafin wannan dabara zuwa wasu ƙwayoyin sel. Don yin wannan, za mu zama siginan kwamfuta a cikin ƙananan kusurwar dama na tantanin da ke ɗauke da aikin. Maimaita siginar don nuna alamar cikawa, wanda yake kama da gicciye. Riƙe maɓallin linzamin kwamfuta na hagu ka ja shi zuwa ƙarshen shafi.



5. Mun sami lissafin sakamakon ƙimar matsakaiciyar watanni biyu da suka gabata kafin ƙarshen shekara.



6. Yanzu zabi tantanin a cikin layi na gaba na gaba a cikin jere don Afrilu. Kira taga aikin muhawara KYAUTA daidai da yadda aka bayyana a baya. A fagen "Lambar1" shigar da daidaitawar sel a cikin shafi Haraji Janairu zuwa Maris. Saika danna maballin "Ok".



7. Yin amfani da alamar cikawa, kwafa dabarar zuwa sel na teburin da ke ƙasa.



8. Don haka, mun lasafta dabi'u. Yanzu, kamar yadda a cikin lokacin da ya gabata, zamu buƙaci gano wane nau'in bincike ne mafi kyau: tare da sauƙaƙewa a watanni 2 ko 3. Don yin wannan, ƙididdigar daidaitattun karkatattun abubuwa da wasu alamun. Da farko, muna lissafin cikakken karkatacciyar hanya ta amfani da daidaitaccen aikin Excel ABS, wanda maimakon lambobi masu kyau ko mara kyau suna dawo da matsayin su. Wannan ƙimar za ta yi daidai da bambanci tsakanin mai nuna ainihin kudaden shiga don watan da aka zaɓa da mai hasashen. Sanya siginan kwamfuta zuwa layi na gaba a cikin layi don Mayu. Muna kira Mayan fasalin.



9. A cikin rukuni "Ilmin lissafi" zaɓi sunan aiki "ABS". Latsa maballin "Ok".



10. Farashin muhawara na aiki yana farawa ABS. A filin guda "Lambar" nuna bambanci tsakanin abubuwan da ke cikin sel a cikin sassan Haraji da Watanni 2 na Mayu. Saika danna maballin "Ok".



11. Yin amfani da alamar cikawa, kwafa wannan dabarar zuwa duk layuka na tebur har zuwa watan Nuwamba.



12. Mun ƙididdige matsakaicin darajar cikakkiyar karkatacciyar hanya na tsawon lokacin ta amfani da aikin da muka riga muka sani KYAUTA.



13. Muna yin irin wannan hanya don ƙididdige cikakken karkatarwa ga mai motsawa cikin watanni 3. Da farko, amfani da aikin ABS. A wannan lokacin ne kawai muke la'akari da bambanci tsakanin abubuwan da ke cikin sel tare da ainihin kudin shiga da wanda aka shirya, ana lissafta ta amfani da matsakaicin matsakaiciyar motsi na watanni 3.



14. Na gaba, muna lissafin matsakaicin darajar duk ɓataccen ɓarnar bayanai ta amfani da aikin KYAUTA.



15. Mataki na gaba shine yin lissafin karkatar da dangi. Ya yi daidai da rabo na karkatacciyar karkacewa zuwa ainihin mai nunawa. Don guje wa ƙimar mummuna, za mu sake amfani da damar da mai ba da sabis ya bayar ABS. Wannan lokacin, ta amfani da wannan aikin, muna rarrabe darajar cikakken karkatarwa yayin amfani da matsakaicin matsakaiciyar motsi na watanni 2 ta ainihin kudin shiga ga watan da aka zaɓa.



16. Amma karkatar da dangi yawanci ana nuna shi a cikin nau'i nau'i. Sabili da haka, zaɓi zangon da ya dace akan takardar, je zuwa shafin "Gida"a ina a cikin akwatin kayan aiki "Lambar" a fagen tsara bayanai na musamman mun sanya nau'in tsarin. Bayan wannan, sakamakon lissafin ɓacewar dangi an nuna shi cikin kashi.



17. Muna yin irin wannan aiki don ƙididdige karkatar da dangi tare da bayanai ta amfani da smoothing watanni 3. A wannan yanayin kawai, don yin lissafi azaman rabo, muna amfani da wani shafin tebur, wanda muke da suna "Abs. A kashe (3m)". Sannan muna fassara dabi'un lambobi zuwa tsari dari.



18. Bayan haka, muna ƙididdige matsakaiciyar ƙimar duka ɓangarorin biyu tare da karkatar da ɗangi, kamar kafin amfani da aikin KYAUTA. Tunda mun dauki ƙididdigar darajar abubuwa azaman muhawara kan aikin, ba ma buƙatar ƙara yin juyawa. Mai sarrafa kayan fitarwa yana ba da sakamakon tuni cikin tsari dari.



19. Yanzu mun zo lissafin daidaitaccen karkatarwa. Wannan manuniyar zata bamu damar kwatanta ingancin lissafin kai tsaye lokacin amfani da kayan laushi na watanni biyu da uku. A cikin yanayinmu, daidaitaccen karkatar zai zama daidai da tushen murabba'in adadin murabba'un bambance-bambance na ainihin kudaden shiga da matsakaita mai motsi wanda ya raba ta adadin watanni. Domin yin lissafi a cikin shirin, dole ne muyi amfani da ayyuka da yawa, musamman TAFIYA, ZAMU CIGABA da LABARI. Misali, don kirga ma'anar murabba'i mai ma'ana yayin amfani da layin smoothing tsawon watanni biyu a watan Mayu, a cikin yanayinmu, za a yi amfani da tsari mai zuwa:

    = GUDA (KYAUTA (B6: B12; C6: C12) / COUNT (B6: B12))

    Kwafi shi zuwa wasu ƙwayoyin a cikin shafi tare da lissafin daidaitaccen karkatarwa ta amfani da alamar cike.



20. Ana yin irin wannan aikin don ƙididdige daidaitattun daidaituwa don matsakaicin motsi don watanni 3.



21. Bayan haka, muna lissafin matsakaicin darajar tsawon lokacin duka waɗannan alamomin, suna aiwatar da aikin KYAUTA.



22. Ta hanyar yin lissafin amfani da matsakaicin matsakaiciyar motsi tare da smoothing a 2 da 3 don alamun kamar cikakkiyar karkatacciyar hanya, karkatar da dangi da karkatacciyar hanya, zamu iya amincewa da cewa smoothing watanni biyu yana ba da sakamako mafi aminci fiye da sanya smoothing na watanni uku. Wannan an tabbatar da gaskiyar cewa alamomin da ke sama na matsakaicin watanni biyu ƙasa da na watanni uku.



23. Don haka, ƙaddarar da aka nuna game da kudin kamfanin na watan Disamba zai zama 990.4 dubu rubles. Kamar yadda kake gani, wannan ƙimar ta haɗu gaba ɗaya da wacce muka samu ta hanyar yin amfani da kayan aikin Kunshin bincike.

Darasi: Mayan Maɗaukaki

Mun ƙididdige hasashen ta yin amfani da matsakaiciyar motsi ta hanyoyi biyu. Kamar yadda kake gani, wannan hanyar ita ce mafi sauƙin aiwatarwa ta amfani da kayan aikin. Kunshin bincike. Koyaya, wasu masu amfani basu yarda da lissafin atomatik ba kuma sun fi son amfani da aikin don lissafin. KYAUTA da masu aiki da ke da alaƙa don tabbatar da zaɓin mafi amintacce. Kodayake, idan an yi komai daidai, fitar da lissafin ya kamata ya zama ɗaya gaba ɗaya.