COEFFICIENT OF DETERMINATION SA EXCEL

Ang isa sa mga tagapagpahiwatig na naglalarawan sa kalidad ng itinayong modelo sa mga istatistika ay ang koepisyent ng pagpapasiya (R ^ 2), na tinatawag ding approximation confidence value. Gamit ito, maaari mong matukoy ang antas ng katumpakan ng forecast. Alamin kung paano mo maaaring kalkulahin ang indicator 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 grupo:

0.8 - 1 - isang modelo ng magandang kalidad;
0.5 - 0.8 - isang modelo ng katanggap-tanggap na kalidad;
0 - 0,5 - isang modelo ng mahinang kalidad.

Sa huli kaso, ang kalidad ng mga modelo ay nagpapahiwatig ng hindi magaganap ng paggamit nito para sa forecast.

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

Paraan 1: pagkalkula ng koepisyent ng pagpapasiya na may isang linear na function

Una sa lahat, alamin kung paano hanapin 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. Kalkulahin namin ito gamit ang built-in na function na Excel gamit ang halimbawa ng isang partikular na talahanayan, na ipinapakita sa ibaba.

Piliin ang cell kung saan ipinapakita ang koepisyent ng pagpapasiya pagkatapos ng pagkalkula nito, at mag-click sa icon "Ipasok ang pag-andar".

Nagsisimula Function Wizard. Ilipat sa kategorya nito "Statistical" at markahan ang pangalan KVPIRSON. Susunod, mag-click sa pindutan "OK".

Nagsisimula ang window ng mga function argument. KVPIRSON. Ang operator mula sa statistical group ay dinisenyo upang kalkulahin ang parisukat ng koepisyent ng ugnayan ng Pearson function, iyon ay, isang linear function. At habang naaalala natin, na may linear function, ang koepisyent ng determinasyon ay katumbas lamang sa parisukat ng koepisyent ng ugnayan.
Ang syntax para sa pahayag na ito ay:
= KVPIRSON (kilala_y; well-kilala_x)
Kaya, ang isang function ay may dalawang operator, ang isa ay isang listahan ng mga halaga ng function, at ang pangalawa ay isang argumento. Ang mga operator ay maaaring kinakatawan bilang direkta bilang mga halaga na nakalista sa pamamagitan ng isang tuldok-kuwit (;), at sa anyo ng mga link sa mga saklaw kung saan sila matatagpuan. Ito ay ang huling pagpipilian na gagamitin ng sa amin sa halimbawang ito.
Itakda ang cursor sa field "Mga Kilalang Y Halaga". Isinasagawa namin ang pag-clamping ng kaliwang pindutan ng mouse at piliin ang mga nilalaman ng haligi. "Y" mga talahanayan. Tulad ng iyong nakikita, ang address ng tinukoy na data array ay agad na ipinapakita sa window.
Katulad din punan ang patlang "Kilalang x". Ilagay ang cursor sa patlang na ito, ngunit oras na ito piliin ang mga halaga ng haligi "X".
Matapos ang lahat ng data ay ipinapakita sa window ng mga argumento KVPIRSONi-click ang pindutan "OK"na matatagpuan sa pinakadulo nito.

Tulad ng makikita mo, pagkatapos nito, kinakalkula ng programa ang koepisyent ng pagpapasiya at ibabalik ang resulta sa selula na napili bago ang tawag Function masters. Sa aming halimbawa, ang halaga ng kinakalkula na tagapagpahiwatig ay naging 1. Ito ay nangangahulugan na ang ipinakita na modelo ay ganap na maaasahan, ibig sabihin, inaalis nito ang error.

Aralin: Function Wizard sa Microsoft Excel

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

Ngunit ang opsyon sa itaas ng pagkalkula ng ninanais na halaga ay maaaring mailapat lamang sa mga linear function. Ano ang dapat gawin upang gumawa ng pagkalkula nito sa isang nonlinear function? Sa Excel mayroong ganitong pagkakataon. Magagawa ito sa isang tool. "Pagbabalik"na bahagi ng pakete "Pagsusuri ng Data".

Ngunit bago gamitin ang tool na ito, dapat mong buhayin ito sa iyong sarili. "Package ng Pagsusuri"na kung saan sa pamamagitan ng default ay hindi pinagana sa Excel. Ilipat sa tab "File"at pagkatapos ay pumunta sa pamamagitan ng item "Mga Pagpipilian".

Sa binuksan na window, lumipat kami sa seksyon. Mga Add-on sa pamamagitan ng pag-navigate sa kaliwang vertical na menu. Sa ibaba ng kanang pane ay isang patlang "Pamamahala". Mula sa listahan ng mga magagamit na subsection doon piliin ang pangalan "Excel add-ins ..."at pagkatapos ay mag-click sa pindutan "Go ..."na matatagpuan sa kanan ng patlang.

Nagsisimula ang window ng mga add-on. Sa gitnang bahagi ay isang listahan ng mga magagamit na mga add-in. Lagyan ng tsek ang kahon sa tabi ng posisyon "Package ng Pagsusuri". Kasunod nito, mag-click sa pindutan. "OK" sa kanang bahagi ng window ng interface.

Pakete ng tool "Pagsusuri ng Data" sa kasalukuyang pagkakataon ng Excel ay mai-activate. Ang access dito ay matatagpuan sa laso sa tab "Data". Ilipat sa tinukoy na tab at i-click ang pindutan. "Pagsusuri ng Data" sa pangkat ng mga setting "Pagsusuri".

Pinagana ang window "Pagsusuri ng Data" na may isang listahan ng mga pinasadyang mga tool sa pagpoproseso ng impormasyon. Pumili mula sa item na listahang ito "Pagbabalik" at mag-click sa pindutan "OK".

Pagkatapos ay bubuksan ang tool window. "Pagbabalik". Ang unang bloke ng mga setting - "Input". Dito sa dalawang larangan kailangan mong tukuyin ang mga address ng mga saklaw kung saan matatagpuan ang mga halaga at mga function ng argumento. Ilagay ang cursor sa field "Input interval Y" at piliin ang mga nilalaman ng haligi sa sheet "Y". Matapos ang array address ay ipapakita sa window "Pagbabalik"ilagay ang cursor sa field "Input interval Y" at sa eksakto sa parehong paraan piliin ang mga cell ng haligi "X".
Tungkol sa mga parameter "Tag" at "Constant-zero" Hindi naka-set ang mga checkbox. Ang checkbox ay maaaring itakda malapit sa parameter "Antas ng pagiging maaasahan" at sa kabaligtaran sa patlang, ipahiwatig ang ninanais na halaga ng naaayong tagapagpahiwatig (bilang default na 95%).
Sa pangkat "Mga Pagpipilian sa Output" kailangan mong tukuyin kung anong lugar ang resulta ng pagkalkula ay ipapakita. Mayroong tatlong mga pagpipilian:
- Lugar sa kasalukuyang sheet;
- Isa pang sheet;
- Isa pang libro (bagong file).
Itigil natin ang pagpipilian sa unang pagpipilian na ang paunang data at resulta ay inilagay sa isang worksheet. Ilagay ang switch na malapit sa parameter "Output Spacing". Sa patlang sa tapat ng item na ito ilagay ang cursor. I-click namin ang kaliwang pindutan ng mouse sa walang laman na sangkap sa sheet, na nilayon upang maging kaliwang itaas na selula ng talahanayan ng mga resulta ng pagkalkula. Ang address ng sangkap na ito ay dapat na ipapakita sa window "Pagbabalik".
Mga grupo ng parameter "Nananatili" at "Normal na posibilidad" huwag pansinin, dahil hindi mahalaga ang mga ito para sa paglutas ng problema. Matapos na namin mag-click sa pindutan. "OK"na kung saan ay matatagpuan sa itaas na kanang sulok ng window "Pagbabalik".

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

Paraan 3: ang koepisyent ng pagpapasiya para sa trend line

Bilang karagdagan sa mga pagpipilian sa itaas, ang koepisyent ng pagpapasiya ay maaaring maipakita nang direkta para sa trend line sa isang graph na binuo sa isang sheet ng Excel. Matutuklasan natin kung paano ito maaaring gawin sa isang kongkreto halimbawa.

Mayroon kaming isang graph batay sa talahanayan ng mga argumento at mga halaga ng function na ginamit para sa nakaraang halimbawa. Let's make a trend line to it. Mag-click kami sa anumang lugar sa lugar ng pagtatayo kung saan inilalagay ang graph sa kaliwang pindutan ng mouse. Kasabay nito, ang isang karagdagang hanay ng mga tab ay lilitaw sa laso - "Paggawa gamit ang Mga Tsart". Pumunta sa tab "Layout". Nag-click kami sa pindutan "Trend line"na matatagpuan sa bloke ng tool "Pagsusuri". Lumilitaw ang isang menu na may pagpipilian ng uri ng trend line. Itigil namin ang pagpili sa uri na tumutugma sa isang partikular na gawain. Para sa aming halimbawa, pumili tayo "Ang pagpaparami ng pagtatantya".

Ang Excel ay nagtatayo ng isang trend line sa anyo ng isang karagdagang itim na curve karapatan sa charting eroplano.

Ngayon ang aming gawain ay upang ipakita ang koepisyent ng pagpapasiya mismo. Mag-right-click kami sa trend line. Isinaaktibo ang menu ng konteksto. Itigil ang pagpili dito sa item "Trend line format ...".
Upang makagawa ng isang paglipat sa window ng format ng trend ng trend, maaari kang magsagawa ng alternatibong aksyon. Piliin ang trend line sa pamamagitan ng pag-click dito gamit ang kaliwang pindutan ng mouse. Ilipat sa tab "Layout". Nag-click kami sa pindutan "Trend line" sa bloke "Pagsusuri". Sa listahan na bubukas, nag-click kami sa huling huling item sa listahan ng mga pagkilos - "Mga Advanced na Pagpipilian sa Tren Line ...".

Pagkatapos ng alinman sa dalawang aksyon sa itaas, isang window ng format ay inilunsad kung saan maaari kang gumawa ng karagdagang mga setting. Sa partikular, upang isagawa ang aming gawain, kinakailangan upang suriin ang kahon sa tabi "Ilagay sa tsart ang halaga ng katumpakan ng approximation (R ^ 2)". Ito ay matatagpuan sa ilalim ng window. Iyon ay, sa ganitong paraan isinama natin ang pagpapakita ng koepisyent ng pagpapasiya sa lugar ng konstruksiyon. Pagkatapos ay huwag kalimutan na pindutin ang pindutan "Isara" sa ilalim ng kasalukuyang window.

Ang halaga ng kumpiyansa ng approximation, iyon ay, ang halaga ng koepisyent ng pagpapasiya, ay ipapakita sa sheet sa lugar ng plot. Sa kasong ito, ang halaga na ito, tulad ng nakikita natin, ay katumbas ng 0.9242, na kinikilala ang approximation, bilang isang modelo ng mahusay na kalidad.

Ganap na eksakto upang maaari mong itakda ang pagpapakita ng koepisyent ng pagpapasiya para sa anumang iba pang uri ng trend line. Maaari mong baguhin ang uri ng trend line sa pamamagitan ng paggawa ng paglipat sa pamamagitan ng pindutan sa laso o sa menu ng konteksto sa window ng mga parameter nito, tulad ng ipinapakita sa itaas. Pagkatapos ay nasa window sa grupo "Building a trend line" maaaring lumipat sa ibang uri. Huwag kalimutang kontrolin na malapit sa punto "Ilagay sa tsart ang halaga ng katumpakan ng pagtatantya" ay naka-check. Matapos makumpleto ang mga hakbang sa itaas, mag-click sa pindutan. "Isara" sa ibabang kanang sulok ng window.

Sa kaso ng isang linear type, ang trend line ay may isang approximation confidence value ng 0.9477, na characterizes modelo na ito bilang mas maaasahan kaysa sa exponential uri trend line na isinasaalang-alang namin mas maaga.

Kaya, ang paglipat sa pagitan ng iba't ibang mga uri ng mga linya ng trend at paghahambing ng kanilang mga halaga ng tiwala ng approximation (determinasyon koepisyent), maaari mong makita ang variant, ang modelo kung saan ang pinaka tumpak na naglalarawan sa iniharap na graph. Ang variant na may pinakamataas na index ng determinasyon ay ang pinaka maaasahan. Sa batayan nito, maaari kang bumuo ng pinakasumpanteng forecast.
Halimbawa, para sa aming kaso, sa pamamagitan ng eksperimento, aming pinamamahalaang upang maitaguyod na ang pinakamataas na antas ng kumpiyansa ay sa polinomyal na uri ng trend line ng ikalawang antas. Ang koepisyent ng pagpapasiya sa kasong ito ay katumbas ng 1. Ito ay nagpapahiwatig na ang modelong ito ay ganap na maaasahan, na nangangahulugang ang kumpletong pag-aalis ng mga pagkakamali.
Ngunit sa parehong oras, ito ay hindi nangangahulugan sa lahat na ang ganitong uri ng trend line ay magiging ang pinaka maaasahan para sa isa pang tsart. Ang pinakamainam na pagpili ng uri ng trend line ay depende sa uri ng pag-andar batay sa kung saan itinayo ang graph. Kung ang gumagamit ay walang sapat na kaalaman upang matantya ang pinakamataas na kalidad na opsyon, ang tanging paraan upang matukoy ang pinakamahusay na hula ay paghahambing lamang ng mga coefficients ng pagpapasiya, tulad ng ipinakita sa halimbawa sa itaas.

Tingnan din ang:
Pagbuo ng mga linya ng trend sa Excel
Excel Approximation

Sa Excel mayroong dalawang pangunahing mga pagpipilian para sa pagkalkula ng koepisyent ng pagpapasiya: gamit ang operator KVPIRSON at tool ng application "Pagbabalik" mula sa pakete ng mga tool "Pagsusuri ng Data". Sa kasong ito, ang una sa mga opsyon na ito ay inilaan para gamitin lamang sa pagpoproseso ng isang linear na function, at ang iba pang pagpipilian ay maaaring gamitin sa halos lahat ng mga sitwasyon. Bilang karagdagan, posible na ipakita ang koepisyent ng pagpapasiya para sa trend line ng mga graph bilang isang approximation na halaga ng kumpyansa. Gamit ang indicator na ito, posibleng matukoy ang uri ng trend line na may pinakamataas na antas ng kumpyansa para sa isang partikular na function.