COEFFICIENT OF DETERMINATION SA EXCEL

Usa sa mga indicators nga naghulagway sa kalidad sa gitukod nga modelo sa estatistika mao ang coefficient of determination (R ^ 2), nga gitawag usab nga ang gibana-bana nga pagsalig sa bili. Uban niini, mahibal-an nimo ang lebel sa katukma sa forecast. Atong tan-awon kung giunsa nimo kalkula kining indikator gamit ang lainlaing mga himan nga Excel.

Pagkalkula sa koepisyente sa determinasyon

Depende sa lebel sa coefficient of determination, naandan ang pagbahin sa mga modelo ngadto sa tulo ka grupo:

0.8 - 1 - modelo sa maayong kalidad;
0.5 - 0.8 - usa ka modelo sa madawat nga kalidad;
0 - 0,5 - modelo sa dili maayo nga kalidad.

Sa ulahing kaso, ang kalidad sa maong modelo nagpakita sa imposible sa paggamit niini alang sa forecast.

Ang pagpili kon unsaon sa pagkalkulo sa tinud-an nga bili sa Excel nag-agad sa kung ang regresasyon lahi o dili. Sa unang kaso, mahimo nimo gamiton ang function KVPIRSON, ug sa ikaduha ikaw kinahanglan nga mogamit sa usa ka espesyal nga himan gikan sa pag-analisar nga pakete.

Pamaagi 1: pagkalkula sa koepisyente sa determinasyon nga may linear function

Una sa tanan, susiha unsaon pagpangita sa koepisyente sa determinasyon alang sa usa ka linear function. Sa sini nga kaso, ini nga indicator ang katumbas sa square sang correlation coefficient. Atong kalkahon kini gamit ang built-in nga function sa Excel gamit ang ehemplo sa usa ka piho nga lamesa, nga gipakita sa ubos.

Pilia ang cell diin ang koepisyent sa determinasyon ipasundayag human sa pagkalkulo niini, ug i-klik ang icon "Pagsukip sa pag-obra".

Pagsugod Function Wizard. Pagbalhin ngadto sa kategoriya niini "Estadistika" ug markahan ang ngalan KVPIRSON. Sunod, i-klik ang button "OK".

Ang function window sa mga argumento magsugod. KVPIRSON. Kini nga operator gikan sa statistical group gidisenyo aron makalkulo ang kwadrado sa correlation coefficient sa Pearson function, nga mao, usa ka linear function. Ug ingon sa atong nahinumduman, nga may linear function, ang koepisyent sa determinasyon parehas lamang sa square sa correlation coefficient.
Ang syntax alang niini nga pamahayag mao ang:
= KVPIRSON (known_y; well-known_x)
Sa ingon, ang usa ka function adunay duha ka mga operators, ang usa niini usa ka lista sa mga bili sa function, ug ang ikaduha usa ka argumento. Ang mga operators mahimong girepresentar nga direkta sama sa mga bili nga gilista pinaagi sa usa ka semicolon (;), ug diha sa porma sa mga sumpay ngadto sa mga han-ay diin kini nahimutang. Kini ang katapusan nga kapilian nga gamiton sa kanato sa niini nga panig-ingnan.
Ibutang ang cursor sa uma "Nahibal-an nga mga Bili". Gihimo nato ang pag-clamping sa wala nga button sa mouse ug pilia ang mga sulod sa kolum. "Y" mga lamesa. Sumala sa imong makita, ang address sa gitakda nga data array gipakita diha dayon sa bintana.
Susiha usab ang uma "Nailhan x". Ibutang ang cursor niini nga natad, apan niining higayona pilia ang mga kantidad sa kolum "X".
Human sa tanan nga mga datos nga gipakita diha sa mga window sa mga argumento KVPIRSONi-klik ang button "OK"nga nahimutang sa labing ubos niini.

Sumala sa imong makita, human niini, ang programa nagkalkula sa koepisyente sa determinasyon ug gibalik ang resulta sa selula nga gipili sa wala pa ang tawag Naggamit nga mga agalon. Sa among pananglitan, ang bili sa gikalkula nga timailhan nahimo nga 1. Kini nagpasabot nga ang gipresentar nga modelo hingpit nga kasaligan, nga mao, kini wala maglakip sa sayup.

Leksyon: Function Wizard sa Microsoft Excel

Pamaagi 2: Ang pagkalkula sa koepisyent sa determinasyon sa nonlinear functions

Apan ang opsyon sa ibabaw sa pagkalkula sa gitinguhang bili mahimong gamiton lamang sa linear functions. Unsa ang buhaton aron makamugna ang pagkalkula niini sa usa ka nonlinear function? Sa Paglabaw ang adunay ingon nga oportunidad. Mahimo kini gamit ang usa ka himan. "Pagbag-o"nga mao ang bahin sa pakete "Pagtuki sa Data".

Apan sa dili pa gamiton kini nga himan, kinahanglan nimo kining aktibo. "Pagtuki sa Package"nga pinaagi sa default ang disabled sa Excel. Padulong sa tab "File"ug unya moagi sa maong butang "Mga Pagpili".

Sa bukas nga bintana kita molihok sa seksyon. Mga Add-on pinaagi sa pag-navigate sa left vertical nga menu. Sa ubos sa tuo nga pane usa ka uma "Pagdumala". Gikan sa lista sa mga subdivision nga anaa didto mopili sa ngalan "Mas maayo ang pagdugang ..."ug dayon i-klik ang button "Lakaw ..."nahimutang sa tuo sa uma.

Nagsugod ang bintana sa pagdugang. Sa tunga nga bahin mao ang usa ka lista sa mga anaa nga mga pagdugang. Susiha ang kahon tapad sa posisyon "Pagtuki sa Package". Human niini, i-klik ang button. "OK" sa tuo nga bahin sa interface window.

Tool nga pakete "Pagtuki sa Data" sa kasamtangan nga pananglitan sa Excel ang ma-activate. Ang access niini nahimutang sa ribbon sa tab Ang "datos". Pagbalhin ngadto sa gitino nga tab ug i-klik ang button. "Pagtuki sa Data" diha sa mga setting nga grupo "Pag-usisa".

Gipadayon nga bintana "Pagtuki sa Data" uban sa usa ka lista sa espesyalista nga mga himan sa pagproseso sa impormasyon. Pagpili gikan niining lista nga aytem "Pagbag-o" ug i-klik ang button "OK".

Dayon gibuksan ang bintana sa himan. "Pagbag-o". Ang unang bloke sa mga setting - "Pagsulod". Dinhi sa duha ka mga laray kinahanglan nimo itudlo ang mga adres sa mga han-ay diin nahimutang ang mga bili ug mga katuyoan sa argumento. Ibutang ang cursor sa uma "Input interval Y" ug pilia ang mga sulod sa kolum sa sheet "Y". Pagkahuman sa address address gipakita sa bintana "Pagbag-o"ibutang ang cursor sa uma "Input interval Y" ug sa sama nga paagi pagpili sa mga selula sa kolum "X".
Mahitungod sa mga sumbanan "Tag" ug "Constant-zero" Ang mga checkbox dili ibutang. Ang checkbox mahimong ibutang duol sa parameter "Ang lebel sa pagkakasaligan" ug sa kaatbang sa umahan, gipakita ang gitinguhang bili sa katugbang nga timailhan (95% default).
Sa grupo "Mga Opsyon sa Output" kinahanglan nimong itakda kung asa nga lugar ang resulta sa pagkalkulo ipakita. Adunay tulo ka mga kapilian:
- Area sa kasamtangan nga papel;
- Lain nga papel;
- Ang laing basahon (bag-ong file).
Atong ihunong ang pagpili sa unang kapilian nga ang unang datos ug resulta gibutang sa usa ka worksheet. Ibutang ang switch nga duol sa parameter "Gawas sa mga Output". Sa kapatagan nga atbang niini nga butang ibutang ang cursor. Kami nag-klik sa wala nga button sa mouse sa walay sulod nga elemento sa sheet, nga gituyo nga mahimong wala nga ibabaw nga selula sa lamesa sa mga resulta sa kalkulasyon. Ang address niini nga elemento kinahanglan nga ipakita sa bintana "Pagbag-o".
Mga grupo sa parameter "Nagpabilin" ug "Normal nga posibilidad" ibalewala, tungod kay dili kini importante sa pagsulbad sa problema. Human niana atong i-klik ang button. "OK"nga nahimutang sa ibabaw nga tuo nga bahin sa bintana "Pagbag-o".

Ang programa nagkalkula base sa kaniadto nga pagsulod nga datos ug nagpakita sa resulta sa gitakda nga gidak-on. Sama sa imong makita, kini nga himan nagpakita sa sheet nga usa ka dako nga gidaghanon sa mga resulta sa lain-laing mga lagda. Apan sa konteksto sa kasamtangan nga leksyon kita interesado sa timailhan "R-square". Sa kini nga kaso, kini katumbas sa 0.947664, nga nagpaila sa piniling modelo isip modelo sa maayong kalidad.

Pamaagi 3: ang determination coefficient alang sa trend line

Dugang sa mga kapilian sa ibabaw, ang koepisyente sa determinasyon mahimo nga gipakita direkta alang sa trend line sa usa ka graph nga gitukod sa usa ka sheet sa Excel. Atong mahibal-an kung unsaon kini mahimo uban ang konkreto nga panig-ingnan.

Adunay usa ka graph nga gibase sa lamesa sa mga argumento ug mga bili sa function nga gigamit sa naunang pananglitan. Himoon nato ang us aka trend trend. Nag-klik kami sa bisan unsang dapit sa lugar sa pagtukod diin gibutang ang graph sa wala nga button sa mouse. Sa samang higayon, usa ka dugang nga mga tab nga makita diha sa ribbon - "Nagtrabaho sa mga Tsart". Adto sa tab "Layout". Nag-klik kami sa buton "Trend line"nga nahimutang sa tool block "Pag-usisa". Ang usa ka menu nga makita uban sa usa ka pagpili sa trend line type. Gipahunong nato ang pagpili sa matang nga katugbang sa usa ka piho nga buluhaton. Alang sa among ehemplo, atong pilion "Pagkadagkong pagsabot".

Ang Excel nagtukod og usa ka trend line sa porma sa usa ka dugang nga itom nga kurba diha mismo sa charting plane.

Karon ang among tahas mao ang pagpakita sa coefficient of determination mismo. Ma-klik namon ang trend line. Gi-activate ang menu sa konteksto. Hunonga ang pagpili niini sa butang "Trend line format ...".
Aron makahimo sa usa ka transisyon ngadto sa window sa format sa trend line, makahimo ka og alternatibong aksyon. Pilia ang trend line pinaagi sa pag-klik niini gamit ang left mouse button. Padulong sa tab "Layout". Nag-klik kami sa buton "Trend line" sa block "Pag-usisa". Diha sa lista nga giablihan, kita mag-klik sa katapusan nga butang sa lista sa mga aksyon - "Advanced Options Trend Line ...".

Human sa bisan unsang duha sa mga aksyon sa ibabaw, usa ka window sa paglihok gilansad diin mahimo ka nga makahimo og dugang mga setting. Sa partikular, aron mahimo ang among buluhaton, gikinahanglan nga susihon ang kahon sunod "Isul-ob ang tsart ang bili sa katukma sa gibana-bana (R ^ 2)". Kini nahimutang sa ilalom sa bintana. Kana mao, niining paagiha among gilakip ang pagpakita sa koepisyente sa determinasyon sa dapit nga pagtukod. Unya ayaw kalimot sa pagpadayon sa button "Duol" sa ubos sa kasamtangan nga bintana.

Ang bili sa pagsalig sa pag-angkon, nga mao, ang bili sa koepisyente sa determinasyon, ibutang sa sheet sa lugar nga luna. Sa kini nga kahimtang, kini nga bili, sumala sa atong makita, mao ang 0.9242, nga nagpaila sa gibana-bana nga, isip modelo sa maayong kalidad.

Hingpit nga eksakto aron mahimo nimong ibutang ang display sa coefficient of determination alang sa bisan unsa nga lain nga matang sa trend line. Mahimo nimo mausab ang matang sa trend line pinaagi sa paghimo sa transisyon pinaagi sa button sa ribbon o sa menu sa konteksto sa window sa mga parameter, sama sa gipakita sa ibabaw. Dayon anaa na sa bintana sa grupo "Pagtukod og usa ka trend line" mahimo nga magbag-o sa laing klase. Ayaw kalimti ang pagkontrol nga duol sa punto "Ibutang sa tsart ang bili sa katukma sa pagtandi" gitan-aw. Pagkahuman sa mga lakang sa ibabaw, i-klik ang button. "Duol" sa ubos nga tuo nga bahin sa bintana.

Sa kaso sa usa ka linear type, ang trend line aduna nay gibana-bana nga pagsalig nga kantidad sa 0.9477, nga nagpaila niini nga modelo nga mas kasaligan pa kay sa exponential type trend line nga atong nahisgutan sa sayo pa.

Busa, ang pagbalhin tali sa lainlaing matang sa trend line ug pagtandi sa ilang mga bili sa pagsalig nga pagsalig (determination coefficient), mahimo nimo makita ang lain, ang modelo nga labing tukma nga naghulagway sa gipresentar nga graph. Ang laing paagi nga adunay labing taas nga index of determination mao ang labing kasaligan. Sa sukaranan niini, mahimo nimong tukuron ang labing tukma nga panagna.
Pananglitan, alang sa among kaso, pinaagi sa eksperimento, nakahimo kami sa pagtukod nga ang labing taas nga ang-ang sa kumpiyansa mao ang polynomial nga matang sa trend line sa ikaduha nga degree. Ang koepisyaryo sa determinasyon sa niini nga kaso mao ang katumbas sa 1. Kini nagsugyot nga kining modelo hingpit nga kasaligan, nga nagpasabut nga ang hingpit nga pagwagtang sa mga kasaypanan.
Apan sa samang higayon, kini wala magpasabot nga kini nga matang sa trend line mao usab ang labing kasaligan alang sa laing tsart. Ang kamalaumon nga pagpili sa matang sa trend line nag-agad sa matang sa function sa basehan nga gitukod ang graph. Kung ang gumagamit wala'y igong kahibalo sa pagbana-bana sa labing taas nga kalidad nga kapilian, nan ang bugtong paagi aron mahibal-an ang labing maayo nga panagna mao lamang ang pagtandi sa mga coefficients sa determinasyon, ingon sa gipakita sa panig-ingnan sa ibabaw.

Tan-awa usab:
Pagtukod sa mga linya sa trend sa Excel
Paglig-on sa Kalabutan

Sa Paglabaw adunay duha ka pangunang mga kapilian sa pagkalkula sa coefficient of determination: gamit ang operator KVPIRSON ug himan nga paggamit "Pagbag-o" gikan sa putos sa mga galamiton "Pagtuki sa Data". Sa sini nga kaso, ang una sa sini nga mga kapilian gintuyo nga gamiton lamang sa pagproseso sang isa ka linear function, kag ang isa pa nga kapilian mahimo magamit sa halos tanan nga mga sitwasyon. Dugang pa, posible nga ipakita ang coefficient of determination alang sa trend line sa mga graphs isip usa ka gibana-bana nga pagsalig sa bili. Pinaagi sa paggamit niini nga indicator, posible nga mahibal-an ang matang sa trend line nga adunay labing taas nga pagsalig sa usa ka partikular nga function.