Lissafin kuɗin aiki na Microsoft Excel

Ofaya daga cikin alamomin da ke bayyana ingancin ƙirar da aka ƙira a ƙididdiga, shine ƙayyadaddun ƙuduri (R ^ 2), wanda kuma ana kiranta ƙimar amincewa kusan. Tare da shi, zaku iya tantance matakin daidaito na hasashen. Bari mu bincika yadda zaku iya lissafin wannan alamar ta amfani da kayan aikin Excel masu yawa.

Lissafin abubuwan da ke tattare da hukunci

Ya danganta da matsayin ƙayyadadden ƙarfin tabbatarwa, al'ada ce a rarraba samfuran a cikin rukuni uku:

• 0.8 - 1 - samfurin kyawawan halaye;
• 0.5 - 0.8 - samfurin kyawun yarda;
• 0 - 0.5 - ƙarancin ƙira mai kyau.

A magana ta ƙarshe, ingancin samfurin yana nuna rashin yiwuwar amfanin sa don hasashen annabta.

Zabi na yadda za'a kirkiri ƙayyadaddun ƙimar a Excel ya dogara ne akan ko tayarwar ta kasance layi ne ko a'a. A cikin yanayin farko, zaku iya amfani da aikin KVPIRSON, kuma a cikin na biyu dole ne kayi amfani da kayan aiki na musamman daga kunshin bincike.

Hanyar 1: yin ƙididdigar ikon daidaitawa tare da aiki mai layi

Da farko dai, zamu gano yadda zamu nemo ma'anar karfin hali don aiki mai amfani. A wannan yanayin, wannan nuna alama zai zama daidai da murabba'in ma'anar daidai. Zamu lissafta shi ta amfani da ginanniyar aikin Excel akan misalin takamaiman tebur, wanda aka bayar a ƙasa.

1. Zaɓi wayar inda mai nuna ƙudurin zai nuna bayan ƙididdigar sa, kuma danna kan gunkin "Saka aikin".



2. Ya fara Mayan fasalin. Motsawa zuwa ga rukuni "Na lissafi" kuma yi alama sunan KVPIRSON. Nan gaba danna maballin "Ok".



3. Farashin muhawara na aiki fara. KVPIRSON. Wannan ma'aikacin daga ƙungiyar ƙididdigar an tsara shi don ƙididdige murabba'in ma'anar daidai na aikin Pearson, wato, aikin layi. Kuma kamar yadda muke tunawa, tare da aiki na yau da kullun, ma'anar daidaitawa daidai take da murabba'in ma'anar daidaitawa.

   Gwanin wannan bayanin shine:

   = KVPIRSON (sananniyar darajar_; sanannu_x dabi'u)

   Don haka, aiki yana da masu aiki guda biyu, ɗayan ɗayan jerin abubuwan ƙimar aiki ne, na biyu kuma hujja ne. Ana iya wakilta masu aiki a matsayin kai tsaye kamar yadda dabi'un da aka lissafa ta hanyar wasan Semicolon (;), kuma a cikin hanyar haɗi zuwa jeri inda aka samo su. Zabi ne na karshen da zamu yi amfani da shi a wannan misalin.

   Sanya siginan kwamfuta a cikin filin Sanannan dabi'u. Mun riƙe maɓallin linzamin kwamfuta na hagu kuma zaɓi abubuwan da ke cikin shafi "Y" alluna. Kamar yadda kake gani, adreshin bayanan bayanan da aka kayyade yana bayyana nan da nan a cikin taga.

   Haka kuma, cika filin Sanin x uesimar. Sanya siginan kwamfuta a cikin wannan filin, amma wannan lokacin zaɓi ƙimar shafi "X".

   Bayan duk bayanan sun nuna a cikin taga muhawara KVPIRSONdanna maballin "Ok"located a sosai kasa.



4. Kamar yadda kake gani, bayan wannan shirin yana lissafin ilimin coefficient na ƙuduri kuma yana nuna sakamako a cikin tantanin da aka zaɓa tun ma kafin kiran. Wizards na Aiki. A cikin misalinmu, ƙimar ƙididdigar mai nuna ƙididdigar ta juya ya zama 1. Wannan yana nufin cewa samfurin da aka gabatar yana da cikakken abin dogara, wato, yana kawar da kuskure.

Darasi: Wipe Feature in Microsoft Excel

Hanyar 2: yin ƙididdige ikon yanke hukunci a cikin ayyukan da ba na layi ba

Amma zaɓi na sama don ƙididdigar darajar da ake so za a iya amfani da shi kawai zuwa ayyukan layin. Me za a yi don lissafa ta a aikin da ba na zamani ba? A cikin Excel akwai irin wannan damar. Ana iya yin shi tare da kayan aiki. "Juyowa"wanda shine bangare na kunshin "Nazarin Bayanai".

1. Amma kafin amfani da kayan aikin da aka ƙayyade, dole ne ka kunna shi da kanka Kunshin Nazarin, wanda aka kunna ta tsohuwa a cikin Excel. Matsa zuwa shafin Fayilolisannan ku tafi "Zaɓuɓɓuka".



2. A cikin taga da ke buɗe, matsa zuwa ɓangaren "Karin abubuwa" ta hanyar kewaya menu na tsaye. A kasan ɓangaren dama na taga akwai filin "Gudanarwa". Daga jerin jerin ƙananan yankuna da suke akwai, zaɓi sunan "Madalla da Karin ... ..."sannan kuma danna maballin "Ku tafi ..."located a hannun dama na filin.



3. Ana buɗe ƙara-kan taga. A ɓangaren tsakiyar sa akwai jerin listara-da za'a samu. Saita akwati kusa da matsayin Kunshin Nazarin. Bayan wannan, danna maballin "Ok" a gefen dama na allon dubawa.



4. Kunshin kayan aiki "Nazarin Bayanai" a halin yanzu na Excel za a kunna. Samun damar zuwa wurin yana kasancewa a kan kintinkiri a cikin shafin "Bayanai". Mun matsa zuwa shafin da aka ambata kuma danna kan maɓallin "Nazarin Bayanai" a cikin rukunin saiti "Bincike".



5. Ana kunna taga "Nazarin Bayanai" tare da jerin kayan aikin sarrafa bayanai na musamman. Zaɓi abu daga wannan jeri "Juyowa" kuma danna maballin "Ok".



6. Sai taga kayan aiki ya buɗe "Juyowa". Farkon saiti shine "Input". Anan a cikin fannoni biyu kuna buƙatar bayyana adreshin jeri inda ƙimar gardamar da aiki suke. Sanya siginan kwamfuta a cikin filin "Hanyar shigowa ta Y" sannan ka zabi abinda ke cikin shafin a takardar "Y". Bayan an gabatar da adireshin farashi a cikin taga "Juyowa"sanya siginan kwamfuta a cikin filin "Hanyar shigowa ta Y" kuma zaɓi sel sashin layi daidai "X".

   Game da sigogi "Label" da M Zero kar a sanya flags. Za'a iya saita akwati na gaba da sashi. "Matakiyya" kuma a filin gaban, nuna ƙimar da ake so na mai nuna alama (95% ta tsohuwa).

   A cikin rukunin Zaɓin fitarwa kana buƙatar tantance wane yanki ne za'a bayyana sakamakon lissafin. Akwai zaɓuɓɓuka uku:

   • Yankin akan takardar na yanzu;
   • Wata takardar;
   • Wani littafi (sabon fayil).

   Bari mu zabi wani zaɓi na farko domin a sanya tushen tushen sakamakon sakamakon a takardar aiki iri ɗaya. Mun sanya juyawa a kusa da sigogi "Matsakaicin fitarwa". A cikin filin gaban wannan abun, sanya siginan kwamfuta. Hagu-danna maɓallin da babu komai a kan takardar, wanda aka ƙera shi ya zama sel hagu na sama na tebur ɗin fitarwa. Adireshin wannan kashi yakamata a nuna a filin taga "Juyowa".

   Ungiyoyi na Baturai "Ragowar" da "Yiwuwar al'ada ce" Yi watsi da su, saboda ba su da mahimmanci don warware aikin. Bayan haka, danna maɓallin "Ok"located a cikin sama kusurwar dama na taga "Juyowa".



7. Shirin yana lissafin yin amfani da bayanan da aka shigar a baya kuma yana nuna sakamakon a cikin adadin da aka kayyade. Kamar yadda kake gani, wannan kayan aikin yana nuna yawan sakamako mai kyau akan sigogi daban-daban akan takardar. Amma a cikin yanayin darasi na yanzu, muna sha'awar mai nuna alama R-murabba'i. A wannan yanayin, ya yi daidai da 0.947664, wanda ke nuna samfurin da aka zaɓa a matsayin ƙira mai kyau.

Hanyar 3: ba da isasshen ƙarfi don layin da ake yi

Bayan zaɓuɓɓukan da aka zaɓa, za a iya nuna wainiyar nuna ƙuduri ta kai tsaye don layin da ake yi a jadawalin da aka gina akan takardar aikin Excel. Zamu gano yadda za'a iya aiwatar da wannan tare da takamaiman misali.

1. Muna da jadawali dangane da tebur na muhawara da ƙimar aikin da aka yi amfani da shi don misalin da ya gabata. Zamu gina layin zamani Mun danna kowane wuri na yankin gini wanda aka sanya ginshiƙi, tare da maɓallin linzamin kwamfuta na hagu. A lokaci guda, ƙarin saitin shafuka suna bayyana akan kintinkiri - "Aiki tare da ginshiƙi". Je zuwa shafin "Layout". Latsa maballin Layin Trendwanda yake a cikin shinge na kayan aiki "Bincike". Menu yana bayyana tare da zaɓi na nau'in layin Trend. Mun dakatar da zaɓin nau'in da ya dace da takamaiman aikin. Bari mu zaɓi zaɓi don misalinmu "Kimantawa kan kari".



2. Excel yana gina layin Trend a cikin nau'i na ƙarin baƙin baki dama akan ginshiƙi.



3. Yanzu aikinmu shi ne nuna namu mai wadatar zuci kanta. Danna-dama akan layin da ake yi. Ana kunna menu na mahallin. Mun dakatar da zabin a ciki a "Tsarin layin Trend ...".

   

   Don aiwatar da sauyawa zuwa taga layi na tsari, za ku iya aiwatar da wani aikin. Zaɓi layin Trend ta danna shi tare da maɓallin linzamin kwamfuta na hagu. Matsa zuwa shafin "Layout". Latsa maballin Layin Trend a toshe "Bincike". A cikin jerin da ke buɗe, danna kan abu na ƙarshe a cikin jerin ayyuka - "Additionalarin abubuwan lamuran cigaba ...".



4. Bayan ɗayan waɗannan ayyukan biyu da ke sama, ana buɗe taga format wanda za ku iya yin ƙarin saiti. Musamman, don kammala aikinmu, wajibi ne don duba akwatin kishiyar "Sanya kwatankwacin tabbacin kimar (R ^ 2) akan zane". Tana can kasan ƙasan taga. Wato, ta wannan hanyar za mu taimaka da nuna rarrabuwar ƙarfi a fannin yankin. Don haka kar a manta danna maballin Rufe a kasan taga na yanzu.



5. Ofimar abin dogaro da kusancin, wato, darajar ma'abuta ƙuduri, za a nuna shi a kan takardar a yankin gini. A wannan yanayin, wannan darajar, kamar yadda muke gani, ita ce 0.9242, wanda ke siffanta kusanci azaman ƙira mai inganci.



6. Babu shakka daidai ta wannan hanyar zaku iya saita nuni na rashin daidaituwa na tabbatarwa ga kowane nau'in layin da ake yi. Kuna iya canza nau'in layin ta hanyar yin sauyawa ta hanyar maɓallin akan kintinkiri ko menu na mahallin a taga sigoginsa, kamar yadda aka nuna a sama. Sannan a cikin taga kanta a cikin rukunin "Gina layi mai dacewa" Kuna iya canzawa zuwa wani nau'in. A lokaci guda, kar a manta don sarrafa abin a kewayen "Sanya kwatankwacin kwatancen kwatankwacin hoto akan zane" an duba akwatin. Bayan kammala matakan da ke sama, danna maballin Rufe a cikin ƙananan kusurwar dama na taga.



7. Tare da nau'in layi-layi, layin da ake yi yanzu ya riga ya sami tabbacin darajar kusan daidai yake da 0.9477, wanda ke nuna wannan ƙirar har ma da abin dogara fiye da layin sabon yanayin abubuwan da muke la'akari da su a baya.



8. Don haka, sauyawa tsakanin nau'ikan layin da aka yi amfani da shi yayin kwatanta kimar amincewa da kwatankwacinsu (wanda yake da ƙima sosai), mutum na iya samun zaɓi wanda ƙirar sa ta kwatanta ainihin jadawalin da aka gabatar. Zaɓin zaɓi tare da mafi girman ikon daidaitawa zai zama mafi aminci. Dangane da shi, zaku iya gina mafi tsinkayarwa.

   Misali, a cikin shari'ar mu ya yiwu ne a tabbatar da cewa nau'in nau'in polynomial na yanayin cigaba na digiri na biyu yana da babban matakin amincewa. Determinationudurin wanda ke da iko sosai a wannan yanayin shine 1. Wannan yana nuna cewa wannan samfurin gaba ɗaya abin dogara ne, wanda ke nufin cikakken wariyar kurakurai.

   

   Amma, a lokaci guda, wannan baya nufin gaba ɗaya don wani ginshiƙi wannan nau'in layin ɗin ma zai zama abin dogaro. Mafi kyawun zaɓi na nau'in layin Trend ya dogara da nau'in aikin akan abin da aka gina ginshiƙi. Idan mai amfani ba shi da isasshen ilimin da zai iya kimanta mafi kyawun bambancin inganci ta ido, to, hanya guda ɗaya don tantance mafi kyawun hasashen shi ne kwatanta ƙididdigar ƙuduri, kamar yadda aka nuna a misalin da ke sama.

Karanta kuma:
Gina layin Trend a cikin Excel
Kimantawa a Excel

Akwai manyan zaɓuɓɓuka guda biyu don ƙididdige ikon daidaitawa a cikin Excel: ta amfani da mai amfani KVPIRSON da kuma amfani da kayan aiki "Juyowa" daga akwatin kayan aiki "Nazarin Bayanai". Haka kuma, farkon waɗannan zaɓuɓɓukan an yi niyya don amfani ne kawai a cikin aiki na layi, kuma za a iya amfani da ɗayan zaɓi a kusan dukkanin yanayi. Bugu da kari, yana yiwuwa a nuna mai iyawa na tabbatar da dalilin layin kwalliyar a matsayin darajar amincin kusancin. Amfani da wannan alamar, yana yiwuwa a ƙayyade nau'in layin da ke da babban matakin amincewa ga wani aiki na musamman.