Daga cikin hanyoyin tsinkaye daban-daban, mutum ba zai iya ɗaukar kimanin rabin. Amfani da shi, zaku iya yin kimantawa da kirga alamun da aka tsara ta hanyar maye gurbin abubuwan asali da masu sauki. A cikin Excel, akwai kuma yiwuwar amfani da wannan hanyar don tsinkayarwa da bincike. Bari mu bincika yadda za a iya amfani da wannan hanyar a cikin tsararren shirin tare da kayan aikin ginannun.
Kimantawa
Sunan wannan hanyar ya fito ne daga kalmar Latin proxima - “mafi kusa.” Kimantawa ne ta hanyar sauƙaƙawa da kuma sanannun alamun sanannu, gina su zuwa cikin yanayin da asalinta. Amma ana iya amfani da wannan hanyar ba kawai don tsinkayar ba, har ma don nazarin sakamakon da ake samu. Bayan haka, kusanci shine, a zahiri, sauƙaƙe bayanan asalin, kuma samfurin da aka sauƙaƙa yana da sauƙin bincike.
Babban kayan aiki wanda ake yin daskararru a cikin Excel shine gina layin Trend. Gaskiyar shine, bisa alamomi masu gudana, ana aiwatar da jadawalin aiki don al'amuran da za ayi nan gaba. Babban mahimmancin layin da aka yi, kamar yadda zaku iya tsammani, shine sanya hasashen yanayi ko gano yanayin gaba ɗaya.
Amma ana iya yinsa ta amfani da ɗayan nau'ikan kusan biyar:
- Layi;
- Abun Neman;
- Logarithmic;
- Polynomial;
- .Arfi.
Munyi la'akari da kowane zaɓi a cikin ƙarin daki-daki daban.
Darasi: Yadda za a gina layin Trend a Excel
Hanyar 1: laushi mai laushi
Da farko, bari mu duba zaɓi mafi sauƙaƙawa, wato amfani da aikin layi. Zamuyi cikakken bayani akan sa, tunda zamu tsara abubuwan gaba daya wadanda suke halayen wasu hanyoyin ne, shine, tsara jadawalin wasu abubuwanda baza muyi tsokaci dasu ba idan akayi la'akari da wadannan zabuka.
Da farko dai, zamu gina jadawali, a kan wanda zamu aiwatar da tsarin laushi. Don gina jadawalin, muna ɗaukar tebur wanda aka nuna farashin kowane wata na masana'antar da kamfanin ke fitarwa da ribar da ta dace a cikin wani lokaci da aka bayar. Aikin hoto wanda zamu gina zai nuna dogaro da karuwar riba akan rage farashin kayan masarufi.
- Don yin dabara, da farko, zaɓi ginshiƙai "Kudin Kuɗin" da Riba. Bayan haka, matsa zuwa shafin Saka bayanai. Bayan haka, akan kintinkiri a cikin akwatunan ayyukan Charts, danna maballin "Haske". A lissafin da ya buɗe, zaɓi sunan "Spot tare da santsi masu lankwasa da alamomi". Wannan nau'in ginshiƙi ne mafi dacewa don aiki tare da layin da ake yi, sabili da haka, don amfani da hanyar ƙididdigar a cikin Excel.
- An gina jadawalin.
- Don ƙara layin da ake yi, zaɓi shi ta danna maɓallin linzamin kwamfuta na dama. Maɓallin mahallin ke bayyana. Zaɓi abu a ciki "Sanya wani layin ....
Akwai wani zaɓi don ƙara shi. A cikin ƙarin rukunin shafuka akan kintinkiri "Aiki tare da ginshiƙi" matsa zuwa shafin "Layout". Ci gaba a cikin toshe kayan aiki "Bincike" danna maballin Layin Trend. Jerin yana buɗewa. Tunda muna buƙatar aiwatar da ƙididdigar layi, mun zaɓi daga matsayin da aka gabatar "Kimanin layi daya".
- Idan duk da haka kun zaɓi zaɓi na farko tare da ƙara ayyuka ta hanyar mahallin, taga taga zai buɗe.
A cikin toshe na sigogi "Gina layin da ake yi (kusanci da santsi)" saita canzawa zuwa matsayi "Linear".
Idan ana so, zaku iya duba akwatin kusa da matsayin "Nuna daidaito a cikin zane". Bayan haka, za a nuna daidaituwa na aikin mai laushi a kan zane.Hakanan a cikin yanayinmu, don kwatanta zaɓuɓɓukan ɗan raƙuman ra'ayoyi daban-daban, yana da mahimmanci a duba akwatin kusa "Sanya kwalliyar darajar amintaccen tsari (R ^ 2)". Wannan alamar na iya bambanta daga 0 a da 1. Mafi girman shi ne, kusancin shine mafi kyau (ƙarin abin dogara). An yi imani da cewa tare da darajar wannan alamar 0,85 kuma mafi girma, smoothing za a iya ɗauka abin dogara ne, amma idan mai nuna alama yana ƙasa, to babu.
Bayan an gama dukkan shirye-shiryen da ke sama. Latsa maballin Rufewanda yake a gindin taga.
- Kamar yadda kake gani, ana shirya layin cigaba akan ginshiƙi. Tare da kusantowa mai layi, ana nuna shi ta layin madaidaiciya baki. Za'a iya amfani da ƙayyadaddun nau'in smoothing a cikin mafi sauƙaƙan lokacin da bayanai suka canza sosai da sauri kuma dogara da ƙimar aikin akan hujja a bayyane yake.
Murmushi mai amfani da wannan yanayin an bayyana shi ta hanyar mai zuwa:
y = gatari + b
A cikin halinmu na musamman, tsari yana ɗaukar mai zuwa:
y = -0.1156x + 72.255
Ofimar daidai daidaituwa tayi daidai da 0,9418, wanda yake shi ne sakamako wanda za'a yarda dashi wanda yake nuna kyawun abu mai amintacce.
Hanyar 2: Tabbatarwar Nasihu
Yanzu bari mu kalli nau'ikan ƙididdigar kimanin a cikin Excel.
- Don canza nau'in layin da ake yi, zaɓi shi ta danna maɓallin linzamin kwamfuta na dama kuma zaɓi abu a cikin menu mai ɓoye. "Tsarin layin Trend ...".
- Bayan haka, taga sananne yana farawa. A cikin nau'in zaɓin nau'in zaɓin, saita sauya zuwa "Gaskiyar magana". Sauran saitunan za su kasance iri ɗaya ne kamar na farkon. Latsa maballin Rufe.
- Bayan haka, layin da za'a yi amfani da shi za'a tsara shi akan jadawalin. Kamar yadda kake gani, lokacin amfani da wannan hanyar, tana da sifo mai ɗan kadan. A wannan yanayin, matakin amincewa ne 0,9592, wanda yafi lokacin amfani da layi na kusan layin layi. Ana amfani da hanyar ƙayyadaddun lokacin da dabi'u suka canza da sauri sannan kuma ɗauki tsari mai daidaita.
Babban tsarin aikin murmushi shine kamar haka:
y = kasance ^ x
ina e shine tushen logarithm na halitta.
A cikin halinmu na musamman, tsari yana ɗaukar mai zuwa:
y = 6282.7 * e ^ (- 0.012 * x)
Hanyar 3: laushin larura
Yanzu shine juyi don la'akari da hanyar logarithmic kimanin.
- Haka kuma kamar lokacin da muka gabata, muna ƙaddamar da taga layi na tsari ta hanyar menu. Saita canji zuwa wuri "Logarithmic" kuma danna maballin Rufe.
- Akwai hanya don gina layin da ake yi tare da kimantawa na logarithmic. Kamar yadda ya gabata, wannan mafi kyawun amfani ana amfani dashi idan da farko bayanan sun canza da sauri sannan ɗaukar matakan daidaita. Kamar yadda kake gani, matakin amincewa shine 0.946. Wannan ya fi yadda ake amfani da hanyar layin kwalliya, amma ƙasa da ingancin layin Trend tare da ƙoshin laushi.
Gabaɗaya, ƙaramin tsari mai laushi yayi kama da wannan:
y = a * ln (x) + b
ina ln shine darajar logarithm na halitta. Saboda haka sunan hanyar.
A cikin lamarinmu, dabarar tana ɗaukar mai zuwa:
y = -62.81ln (x) +404.96
Hanyar 4: nomarancin polynomial
Lokaci ya yi da za a yi la’akari da hanyar da ake amfani da ƙarancin ƙwayoyin cuta.
- Je zuwa taga layi na tsari, kamar yadda aka yi fiye da sau ɗaya. A toshe "Gina layi mai dacewa" saita canzawa zuwa matsayi "Kayan doshol". A hannun dama na wannan abun shine filin "Digiri". Lokacin zabar darajar "Kayan doshol" ya zama mai aiki. Anan zaka iya tantance kowane darajar wutar lantarki daga 2 (saita ta tsohuwa) zuwa 6. Wannan alamar yana ƙayyade yawan maxima da minima na aikin. Lokacin shigar da polynomial na digiri na biyu, mafi girman ɗaya ne kawai aka bayyana, kuma lokacin shigar da polynomial na digiri na shida, za'a iya bayanin har zuwa biyar maxima. Da farko, bari mu bar saitunan tsoho, shine, zamu nuna digiri na biyu. Mun bar sauran saitunan guda kamar yadda muka sanya su a hanyoyin da suka gabata. Latsa maballin Rufe.
- An tsara layin amfani da wannan hanyar. Kamar yadda kake gani, ya fi ƙarfinta fiye da lokacin amfani da ƙayyadaddun ƙayyadaddu. Matsayin ƙarfin gwiwa ya fi yadda ake amfani da waɗancan hanyoyin da aka yi amfani da su a baya, kuma haka ne 0,9724.
Za'a iya amfani da wannan hanyar cikin nasara idan bayanan koyaushe suke canzawa. Aikin da ke bayanin wannan nau'in kyakyawa yayi kama da wannan:
y = a1 + a1 * x + a2 * x ^ 2 + ... + an * x ^ nA cikin lamarinmu, dabarar ta ɗauki wannan tsari:
y = 0.0015 * x ^ 2-1.7202 * x + 507.01 - Yanzu bari mu canza matsayin polynomials don ganin idan sakamakon zai bambanta. Mun koma zuwa ga taga tsarin. Mun bar nau'in polyimial na kimanin, amma akasin haka, a cikin taga digiri, saita ƙimar mafi girman yiwu - 6.
- Kamar yadda kake gani, bayan wannan layin namu ya fara nuna fifiko, wanda adadin maxima ya cika shida. Confidencearfin kwarjinin ya ƙaru har da, adadin 0,9844.
Dabarar da ke bayanin wannan nau'in kayan smoothing tana ɗaukar mai zuwa:
y = 8E-08x ^ 6-0,0003x ^ 5 + 0,3725x ^ 4-269,33x ^ 3 + 109525x ^ 2-2E + 07x + 2E + 09
Hanyar 5: smoothing ikon
A ƙarshe, muna yin la’akari da hanyar ƙididdige dokar-iko a cikin Excel.
- Mun matsa zuwa taga Tsarin Layi na Trend. Sanya nau'in sauƙin smoothing zuwa wuri "Ikon". Nunin daidaituwa da matakin amincewa, kamar yadda koyaushe, aka bari. Latsa maballin Rufe.
- Shirin samar da layin da ake yi. Kamar yadda kake gani, a cikin yanayinmu layin ne da ɗan lanƙwasa. Matsayi na amincewa shine 0,9618, wanda yake babban ƙimar kuɗi ne. Daga cikin dukkanin hanyoyin da ke sama, matakin amincewa ya kasance mafi girma kawai lokacin amfani da hanyar polynomial.
Ana amfani da wannan hanyar yadda ya kamata a lokuta na canji mai mahimmanci na bayanan aiki. Yana da mahimmanci a la'akari da cewa wannan zaɓi yana aiki ne kawai a cikin yanayin da aikin da jayayya ba su karɓa ƙimar mutunci ko ƙira.
Maganar gaba daya wacce ke bayanin wannan hanyar tana da tsari mai zuwa:
y = bx ^ n
A cikin halinmu na musamman, yana kama da wannan:
y = 6E + 18x ^ (- 6.512)
Kamar yadda kake gani, lokacin amfani da takamaiman bayanan da muka yi amfani da shi azaman misali, hanyar polyumial mai kusanci tare da ɗimbin polynomial zuwa digiri na shida ya nuna matakin mafi girma na dogaro (0,9844), mafi girman matakin dogaro ga hanyar layin kwance (0,9418) Amma wannan ba yana nufin kwata-kwata cewa irin wannan tunanin zai kasance tare da wasu misalai. A'a, matakin inganci na hanyoyin da ke sama na iya bambanta sosai, gwargwadon takamaiman nau'in aikin da za a gina layin da ake yi. Saboda haka, idan hanyar da aka zaɓa tana da tasiri sosai ga wannan aikin, wannan ba yana nufin kwata-kwata zai iya zama ingantacce a wani yanayin ba.
Idan baku iya tantancewa nan da nan ba, gwargwadon shawarwarin da aka ambata a sama, wane irin kusanci ne ya dace musamman game da shari'ar ku, don haka yana da ma'ana a gwada duk hanyoyin. Bayan gina layin da ake dubawa kuma duba matakin amincewarsa, zai yuwu a zaɓi mafi kyawun zaɓi.
