Pagkalkula ng koepisyent ng pagpapasiya sa Microsoft Excel

Ang isa sa mga tagapagpahiwatig na naglalarawan ng kalidad ng itinayo na modelo sa mga istatistika ay ang koepisyent ng pagpapasiya (R ^ 2), na kung saan ay tinawag din na tinatayang halaga ng kumpiyansa. Gamit ito, maaari mong matukoy ang antas ng kawastuhan ng forecast. Alamin natin kung paano mo makalkula ang tagapagpahiwatig na ito gamit ang iba't ibang mga tool sa Excel.

Pagkalkula ng koepisyent ng pagpapasiya

Depende sa antas ng koepisyent ng pagpapasiya, kaugalian na hatiin ang mga modelo sa tatlong mga grupo:

• 0.8 - 1 - modelo ng magandang kalidad;
• 0.5 - 0.8 - modelo ng katanggap-tanggap na kalidad;
• 0 - 0.5 - mahinang kalidad ng modelo.

Sa huling kaso, ang kalidad ng modelo ay nagpapahiwatig ng imposibilidad ng paggamit nito para sa pagtataya.

Ang pagpili kung paano kalkulahin ang tinukoy na halaga sa Excel ay depende sa kung ang regression ay linear o hindi. Sa unang kaso, maaari mong gamitin ang pagpapaandar KVPIRSON, at sa pangalawa kailangan mong gumamit ng isang espesyal na tool mula sa pakete ng pagtatasa.

Paraan 1: pagkalkula ng koepisyent ng pagpapasiya sa isang guhit na pag-andar

Una sa lahat, malalaman natin kung paano mahahanap ang koepisyent ng pagpapasiya para sa isang linear function. Sa kasong ito, ang tagapagpahiwatig na ito ay magiging katumbas ng parisukat ng koepisyent ng ugnayan. Kinakalkula namin ito gamit ang built-in na pagpapaandar ng Excel sa halimbawa ng isang tukoy na talahanayan, na ibinibigay sa ibaba.

1. Piliin ang cell kung saan ipapakita ang koepisyent ng pagpapasiya matapos ang pagkalkula nito, at mag-click sa icon "Ipasok ang function".



2. Nagsisimula Tampok Wizard. Paglipat sa kanyang kategorya "Statistical" at markahan ang pangalan KVPIRSON. Susunod na mag-click sa pindutan "OK".



3. Ang pagsisimula ng window ng pag-andar ay nagsisimula. KVPIRSON. Ang operator na ito mula sa grupong istatistika ay dinisenyo upang makalkula ang parisukat ng koepisyent ng ugnayan ng pagpapaandar ng Pearson, iyon ay, isang gulong na pag-andar. At bilang naaalala natin, na may isang guhit na pag-andar, ang koepisyent ng pagpapasiya ay eksaktong katumbas ng parisukat ng koepisyent ng ugnayan.

   Ang syntax para sa pahayag na ito ay:

   = KVPIRSON (kilalang_y halaga_; kilalang_x na halaga)

   Kaya, ang isang pag-andar ay may dalawang mga operator, ang isa sa kung saan ay isang listahan ng mga halaga ng pag-andar, at ang pangalawa ay isang argumento. Ang mga operator ay maaaring kinakatawan nang direkta bilang mga halaga na binilang sa pamamagitan ng isang semicolon (;), at sa anyo ng mga link sa mga saklaw kung saan matatagpuan ang mga ito. Ito ang huli na pagpipilian na gagamitin sa amin sa halimbawang ito.

   Itakda ang cursor sa bukid Kilalang y Values. Hawak namin ang kaliwang pindutan ng mouse at piliin ang mga nilalaman ng haligi "Y" mga talahanayan. Tulad ng nakikita mo, ang address ng tinukoy na hanay ng data ay agad na ipinapakita sa window.

   Sa parehong paraan, punan ang bukid Kilalang x Mga Pinahahalagahan. Ilagay ang cursor sa patlang na ito, ngunit sa oras na ito piliin ang mga halagang haligi "X".

   Matapos ang lahat ng data ay ipinapakita sa window ng mga argumento KVPIRSONmag-click sa pindutan "OK"matatagpuan sa mismong ilalim nito.



4. Tulad ng nakikita mo, pagkatapos nito kinakalkula ng programa ang koepisyent ng pagpapasiya at ipinapakita ang resulta sa cell na napili kahit bago ang tawag Mga Wizards ng Function. Sa aming halimbawa, ang halaga ng kinakalkula na tagapagpahiwatig ay naging 1. Ito ay nangangahulugan na ang ipinakita na modelo ay ganap na maaasahan, iyon ay, inaalis ang pagkakamali.

Aralin: Tampok na Wizard sa Microsoft Excel

Paraan 2: pagkalkula ng koepisyent ng pagpapasiya sa mga nonlinear function

Ngunit ang pagpipilian sa itaas para sa pagkalkula ng ninanais na halaga ay maaari lamang mailapat sa mga pag-andar sa gulong. Ano ang gagawin upang makalkula ito sa isang nonlinear function? Sa Excel mayroong isang pagkakataon. Maaari itong gawin sa tool. "Regression"na bahagi ng pakete "Pagsusuri ng Data".

1. Ngunit bago gamitin ang tinukoy na tool, dapat mo itong buhayin mismo Pakete ng Pagsusuri, na hindi pinagana sa pamamagitan ng default sa Excel. Ilipat sa tab Fileat pagkatapos ay pumunta sa "Mga pagpipilian".



2. Sa window na bubukas, lumipat sa seksyon "Mga add-on" sa pamamagitan ng pag-navigate sa kaliwang patayong menu. Sa ilalim ng kanang window ng window ay isang patlang "Pamamahala". Mula sa listahan ng mga subscription na magagamit doon, piliin ang pangalan "Excel Add-in ..."at pagkatapos ay mag-click sa pindutan "Go ..."matatagpuan sa kanan ng bukid.



3. Inilunsad ang add-ons window. Sa gitnang bahagi nito ay isang listahan ng mga magagamit na mga add-on. Itakda ang checkbox sa tabi ng posisyon Pakete ng Pagsusuri. Kasunod nito, mag-click sa pindutan "OK" sa kanang bahagi ng interface ng window.



4. Pakete ng tool "Pagsusuri ng Data" sa kasalukuyang halimbawa ng Excel ay isasaktibo. Ang pag-access dito ay matatagpuan sa laso sa tab "Data". Lumipat kami sa tinukoy na tab at mag-click sa pindutan "Pagsusuri ng Data" sa pangkat ng mga setting "Pagtatasa".



5. Ang window ay isinaaktibo "Pagsusuri ng Data" na may isang listahan ng mga dalubhasang tool sa pagproseso ng impormasyon. Piliin ang item mula sa listahang ito "Regression" at mag-click sa pindutan "OK".



6. Pagkatapos ay bubukas ang window window "Regression". Ang unang bloke ng mga setting ay "Input". Dito sa dalawang larangan na kailangan mong tukuyin ang mga address ng mga saklaw kung saan matatagpuan ang mga halaga ng argumento at pagpapaandar. Ilagay ang cursor sa bukid "Input Interval Y" at piliin ang mga nilalaman ng haligi sa sheet "Y". Matapos ang address ng array ay ipinapakita sa window "Regression"ilagay ang cursor sa bukid "Input Interval Y" at piliin ang mga cell cells sa eksaktong paraan "X".

   Tungkol sa mga parameter "Label" at Constant Zero huwag maglagay ng mga watawat. Ang checkbox ay maaaring itakda sa tabi ng parameter. "Antas ng pagiging maaasahan" at sa patlang sa kabaligtaran, ipahiwatig ang ninanais na halaga ng kaukulang tagapagpahiwatig (95% nang default).

   Sa pangkat Mga Pagpipilian sa Output kailangan mong tukuyin kung aling lugar ang ipapakita ang resulta ng pagkalkula. Mayroong tatlong mga pagpipilian:

   • Ang lugar sa kasalukuyang sheet;
   • Ang isa pang sheet;
   • Ang isa pang libro (bagong file).

   Piliin natin ang unang pagpipilian upang ang data ng mapagkukunan at ang resulta ay nakalagay sa parehong worksheet. Inilalagay namin ang switch malapit sa parameter "Output Interval". Sa patlang sa tapat ng item na ito, ilagay ang cursor. Mag-click sa kaliwa sa isang walang laman na elemento sa sheet, na idinisenyo upang maging itaas na kaliwang cell ng talahanayan ng output ng pagkalkula. Ang address ng elementong ito ay dapat ipakita sa larangan ng window "Regression".

   Mga Grupo ng Parameter "Mga Kaliwa" at "Normal na probabilidad" Huwag pansinin, dahil hindi sila mahalaga sa paglutas ng gawain. Pagkatapos nito, mag-click sa pindutan "OK"na matatagpuan sa kanang itaas na sulok ng window "Regression".



7. Kinakalkula ng programa batay sa nakapasok na data at ipinapakita ang resulta sa tinukoy na saklaw. Tulad ng nakikita mo, ang tool na ito ay nagpapakita ng isang medyo malaking bilang ng mga resulta sa iba't ibang mga parameter sa isang sheet. Ngunit sa konteksto ng kasalukuyang aralin, interesado kami sa tagapagpahiwatig R-square. Sa kasong ito, katumbas ito ng 0.947664, na nagpapakilala sa napiling modelo bilang isang modelo ng magandang kalidad.

Paraan 3: koepisyent ng pagpapasiya para sa linya ng uso

Bilang karagdagan sa mga pagpipilian sa itaas, ang koepisyent ng pagpapasiya ay maaaring ipakita nang direkta para sa linya ng trend sa isang graph na binuo sa isang worksheet sa Excel. Malalaman natin kung paano ito magagawa sa isang tiyak na halimbawa.

1. Mayroon kaming isang graph batay sa isang talahanayan ng mga argumento at mga halaga ng pagpapaandar na ginamit para sa nakaraang halimbawa. Kami ay magtatayo ng isang linya ng uso dito. Nag-click kami sa anumang lugar ng lugar ng konstruksyon kung saan inilalagay ang tsart, na may kaliwang pindutan ng mouse. Kasabay nito, isang karagdagang hanay ng mga tab ang lilitaw sa laso - "Nagtatrabaho sa mga tsart". Pumunta sa tab "Layout". Mag-click sa pindutan Linya ng Trendna matatagpuan sa block ng tool "Pagtatasa". Lumilitaw ang isang menu na may pagpipilian ng uri ng linya ng trend. Pinahinto namin ang pagpili ng uri na tumutugma sa isang tiyak na gawain. Pumili tayo ng isang pagpipilian para sa aming halimbawa "Exponential approximation".



2. Bumubuo ang Excel ng isang linya ng trend sa anyo ng isang karagdagang itim na kurba sa tsart.



3. Ngayon ang aming gawain ay upang ipakita ang koepisyent ng pagpapasiya mismo. Mag-right-click sa linya ng trend. Ang menu ng konteksto ay isinaaktibo. Pinahinto namin ang pagpili sa loob nito "Ang format ng linya ng uso ...".

   

   Upang maisagawa ang paglipat sa window ng format ng trend line, maaari kang magsagawa ng isang alternatibong pagkilos. Piliin ang linya ng trend sa pamamagitan ng pag-click dito gamit ang kaliwang pindutan ng mouse. Ilipat sa tab "Layout". Mag-click sa pindutan Linya ng Trend sa block "Pagtatasa". Sa listahan na bubukas, mag-click sa pinakahuling item sa listahan ng mga aksyon - "Karagdagang mga linya ng linya ng trend ...".



4. Matapos ang alinman sa dalawang nasa itaas na aksyon, inilunsad ang isang window ng format kung saan maaari kang gumawa ng mga karagdagang setting. Sa partikular, upang makumpleto ang aming gawain, kinakailangan upang suriin ang kahon sa tabi "Ilagay ang tinatayang halaga ng kumpiyansa (R ^ 2) sa diagram". Matatagpuan ito sa mismong ilalim ng bintana. Iyon ay, sa ganitong paraan pinagana natin ang pagpapakita ng koepisyent ng pagpapasiya sa lugar ng konstruksyon. Pagkatapos ay huwag kalimutang mag-click sa pindutan Isara sa ilalim ng kasalukuyang window.



5. Ang halaga ng pagiging maaasahan ng approximation, iyon ay, ang halaga ng koepisyent ng pagpapasiya, ay ipapakita sa isang sheet sa lugar ng konstruksyon. Sa kasong ito, ang halagang ito, tulad ng nakikita natin, ay 0.9242, na nagpapakilala sa pagtatantya bilang isang modelo ng magandang kalidad.



6. Ganap na eksakto sa ganitong paraan maaari mong itakda ang pagpapakita ng koepisyent ng pagpapasiya para sa anumang iba pang uri ng linya ng trend. Maaari mong baguhin ang uri ng linya ng trend sa pamamagitan ng paggawa ng isang paglipat sa pamamagitan ng pindutan sa laso o menu ng konteksto sa window ng mga parameter nito, tulad ng ipinakita sa itaas. Pagkatapos sa window mismo sa pangkat "Pagbuo ng isang linya ng trend" Maaari kang lumipat sa isa pang uri. Kasabay nito, huwag kalimutang kontrolin iyon sa paligid "Ilagay ang tinatayang halaga ng kumpiyansa sa diagram" naka-check ang checkbox. Matapos makumpleto ang mga hakbang sa itaas, mag-click sa pindutan Isara sa ibabang kanang sulok ng bintana.



7. Gamit ang uri ng linear, ang takbo ng linya ay mayroon ng isang halaga ng kumpiyansa na tinatayang katumbas ng 0.9477, na nagpapakilala sa modelong ito kahit na mas maaasahan kaysa sa linya ng trend ng uri ng exponential na isinasaalang-alang sa amin ng mas maaga.



8. Kaya, ang paglipat sa pagitan ng iba't ibang mga uri ng mga linya ng uso at paghahambing ng kanilang mga tinatayang halaga ng kumpiyansa (koepisyent ng pagpapasiya), mahahanap natin ang pagpipilian na ang modelo ay tumpak na naglalarawan sa ipinakita na graph. Ang opsyon na may pinakamataas na koepisyent ng koepisyent ng pagpapasiya ay ang pinaka maaasahan. Batay dito, maaari kang bumuo ng pinaka tumpak na forecast.

   Halimbawa, para sa aming kaso posible na maitaguyod upang maitaguyod na ang polynomial na uri ng takbo ng linya ng ikalawang degree ay may pinakamataas na antas ng kumpiyansa. Ang koepisyent ng pagpapasiya sa kasong ito ay 1. Ipinapahiwatig nito na ang modelong ito ay ganap na maaasahan, na nangangahulugang ang kumpletong pagbubukod ng mga error.

   

   Ngunit, sa parehong oras, hindi ito nangangahulugang lahat para sa isa pang tsart na ang ganitong uri ng linya ng takbo ay magiging pinaka maaasahan. Ang pinakamainam na pagpipilian ng uri ng linya ng trend ay nakasalalay sa uri ng pag-andar batay sa kung saan itinayo ang tsart. Kung ang gumagamit ay walang sapat na kaalaman upang matantya ang pinakamahusay na kalidad na variant sa pamamagitan ng mata, kung gayon ang tanging paraan upang matukoy ang pinakamahusay na forecast ay upang ihambing ang mga coefficients ng pagpapasiya, tulad ng ipinakita sa halimbawa sa itaas.

Basahin din:
Ang pagbuo ng isang linya ng trend sa Excel
Pagtataya sa Excel

Mayroong dalawang pangunahing mga pagpipilian para sa pagkalkula ng koepisyent ng pagpapasiya sa Excel: gamit ang operator KVPIRSON at paggamit ng tool "Regression" mula sa toolbox "Pagsusuri ng Data". Bukod dito, ang una sa mga pagpipilian na ito ay inilaan para sa paggamit lamang sa pagproseso ng isang gulong na pag-andar, at ang iba pang pagpipilian ay maaaring magamit sa halos lahat ng mga sitwasyon. Bilang karagdagan, posible na ipakita ang koepisyent ng pagpapasiya para sa takbo ng linya ng mga tsart bilang isang halaga ng pagiging maaasahan ng approximation. Gamit ang tagapagpahiwatig na ito, posible upang matukoy ang uri ng linya ng trend na may pinakamataas na antas ng kumpiyansa para sa isang partikular na pag-andar.