Enye yezinkomba ezichaza ikhwalithi yemodeli eyakhiwe kuzibalo yi-coefficient yokuzimisela (R ^ 2), okubizwa nangokuthi inani lokuzethemba lokulinganisa. Ngalo, ungakwazi ukunquma izinga lokunemba kokubikezela. Ake sithole ukuthi ungabala kanjani le nkhomba usebenzisa ama-Excel amathuluzi amaningi.
Ukubalwa kwe-coefficient yokuzimisela
Kuye ngesilinganiso se-coefficient yokuzimisela, kuyisiko ukuhlukanisa ama-model abe ngamaqembu amathathu:
- 0.8 - 1 - imodeli yekhwalithi elihle;
- 0.5 - 0.8 - imodeli yekhwalithi eyamukelekayo;
- 0 - 0,5 - imodeli yekhwalithi empofu.
Esikhathini esilandelayo, izinga lemodeli libonisa ukuthi akunakwenzeka ukusetshenziswa kwalo ukubikezela.
Ukukhethwa kokubala inani elibekiwe ku-Excel kuncike ekutheni ukuguqulwa kuhambisana noma kungenjalo. Esimweni sokuqala, ungasebenzisa lo msebenzi I-KVPIRSON, futhi okwesibili uzodinga ukusebenzisa ithuluzi elikhethekile kusuka kuphakheji yokuhlaziya.
Indlela 1: ukubalwa kwe-coefficient yokuzimisela ngomsebenzi ohambisanayo
Okokuqala, thola ukuthi ungathola kanjani ukhetho lokuzimisela komsebenzi olinganiselwe. Kulesi simo, le nkhomba izolingana nengqikithi yokulingana kokuhlangana. Sizoyibala ngokusebenzisa umsebenzi owakhiwe ngaphakathi we-Excel usebenzisa isibonelo setafula elithile, eliboniswa ngezansi.
- Khetha iseli lapho i-coefficient yokuzimisela izoboniswa emva kokubala kwayo, bese uchofoza kwisithonjana "Faka umsebenzi".
- Iqala Umsebenzi weWebhu. Hambisa kusigaba saso "Isitatimende" bese ubhala igama I-KVPIRSON. Okulandelayo, chofoza inkinobho "Kulungile".
- I-window ye-arguments function iqala. I-KVPIRSON. Lo opharetha kusukela eqenjini lezibalo lenzelwe ukubala isikwele somlinganiso wokuhlanganiswa komsebenzi we-Pearson, okungukuthi, umsebenzi ohambisanayo. Futhi njengoba sikhumbula, ngomsebenzi olinganiselwe, i-coefficient yokuzimisela isilingana nesigcawu se-coefficient yokuhlanganisa.
I-syntax yalesi sitatimende yile:
= KVPIRSON (owaziwayo_y; owaziwayo_x)Ngakho-ke, umsebenzi unama-opharetha amabili, elinye lawo liluhlu lwamanani omsebenzi, kanti okwesibili kungukuphikisana. Ama-operators angamelwa ngokuqondile njengamagugu adweliswe nge-semicolon (;), futhi ngendlela yezixhumanisi ezigabeni lapho zitholakala khona. Kuyindlela yokugcina ezosetshenziswa kithi kulolu sibonelo.
Setha isikhombisi emkhakheni "Izimpawu Zama-Y". Senza ukugubha inkinobho yesinxele segundane bese ukhetha okuqukethwe kukholomu. "Y" amatafula. Njengoba ungabona, ikheli lendawo ecacisiwe yedatha liboniswa ngokushesha efasiteleni.
Ngokufanayo gcwalisa insimu "Eyaziwa x". Faka i-cursor kule nkambu, kodwa manje khetha amanani ekholomu "X".
Ngemuva kokuthi yonke idatha iboniswe efasiteleni lezingxoxo I-KVPIRSONchofoza inkinobho "Kulungile"etholakala phansi kakhulu.
- Njengoba ungabona, emva kwalokhu, lolu hlelo lubala inani lokuzibophezela futhi libuyisela umphumela esitokisini okhethiwe ngaphambi kocingo Amakhosi omsebenzi. Esikhathini sethu, ukubaluleka kwesibonakaliso esibalwe kwaba yi-1. Lokhu kusho ukuthi imodeli eveziwe inokwethenjelwa ngokuphelele, okungukuthi, iqeda iphutha.
Isifundo: Umsebenzi weWebhu ku-Microsoft Excel
Indlela 2: Ukubalwa kwe-coefficient yokuzimisela kwimisebenzi engekho emthethweni
Kodwa ukhetho olungenhla lokubala inani elifunayo lingasetshenziswa kuphela kwimisebenzi ehambisanayo. Yini okufanele uyenze ukuze ukhiqize ukubalwa kwayo emsebenzini ongasebenzi? E-Excel kunethuba elinjalo. Kungenziwa ngethuluzi. "Ukunciphisa"okuyingxenye yephakheji "Ukuhlaziywa kwedatha".
- Kodwa ngaphambi kokusebenzisa leli thuluzi, kufanele ulisebenze ngokwakho. "I-Package Analysis"okuyinto ngokuzenzakalelayo kukhutshaziwe ku-Excel. Hambisa kuthebhu "Ifayela"bese uhamba ngale nto "Izinketho".
- Ewindini evuliwe sithuthela esigabeni. Engeza-ons ngokuzulazula ngokusebenzisa imenyu yokuma kwesokunxele. Ngaphansi kwelawulwa kwesokudla yinsimu "Ukuphathwa". Kusukela ohlwini lwezigatshana ezikhona khona khetha igama "I-Excel engenayo ..."bese uchofoza inkinobho "Hamba ..."ikhona ngakwesokudla kwensimu.
- Iwindi le-add-on iqala. Engxenyeni ephakathi kukhona uhlu lwezingeniso ezitholakalayo. Hlola ibhokisi eduze kwesimo "I-Package Analysis". Ukulandela lokhu, chofoza inkinobho. "Kulungile" ngakwesokudla kwes window esibonakalayo.
- Iphakheji yethuluzi "Ukuhlaziywa kwedatha" esimweni samanje se-Excel sizosebenza. Ukufinyelela kuwo kukhonjiswe kuthebhu "Idatha". Hambisa kuthebhu ecacisiwe bese uchofoza inkinobho. "Ukuhlaziywa kwedatha" kwiqembu lezilungiselelo "Ukuhlaziywa".
- Iwindi elisebenzayo "Ukuhlaziywa kwedatha" kanye nohlu lwamathuluzi okucubungula ulwazi olukhethekile. Khetha kusuka kulolu hlu lohlu "Ukunciphisa" bese uchofoza inkinobho "Kulungile".
- Khona-ke iwindi lesithuluzi livuleka. "Ukunciphisa". I-block yokuqala yezilungiselelo - "Ukufaka". Lapha emikhakheni emibili oyidingayo ukucacisa amakheli emigqa lapho amanani nemigomo ekhona khona. Faka i-cursor ensimini "Isikhathi sokufaka i-Y" bese ukhetha okuqukethwe kukholomu kushidi "Y". Ngemuva kokuthi ikheli elihle liboniswe efasiteleni "Ukunciphisa"faka isikhombisi ensimini "Isikhathi sokufaka i-Y" futhi ngendlela efanayo ngokufanayo ukhethe amaseli ekholomu "X".
Mayelana nemingcele "Maka" futhi "Okuqhubekayo-zero" amabhokisi wokuhlola awasethiwe. Ibhokisi lokuhlola lingasethwa eduze kwepharamitha "Izinga lokuthembeka" futhi ensimini ephikisanayo, khombisa inani elifunayo lenkomba ehambisanayo (ngokuzenzakalelayo 95%).
Eqenjini "Izinketho zokukhipha" udinga ukucacisa ukuthi iyiphi indawo umphumela wokubala uzoboniswa. Kunezinketho ezintathu:
- Indawo esetsheni samanje;
- Esinye ishidi;
- Enye incwadi (ifayela elisha).
Masiyeke ukhetho ekukhethweni kokuqala ukuthi idatha yokuqala kanye nemiphumela ibekwe ekhasini elilodwa lokusebenzela. Beka inkinobho eduze kwepharamitha "Isikhala sokuPhuma". Emkhakheni obhekene nalento ubeke isikhombisi. Sichofoza inkinobho yesinxele yesinxele engxenyeni engenalutho eshadini, okuhloswe ukuba ibe iseli eliphezulu lesokunxele etafuleni lemiphumela yokubala. Ikheli laleli qembu kufanele liboniswe efasiteleni "Ukunciphisa".
Amaqembu we-parameter "Ukuhlala" futhi "Amathuba avamile" qaphela, ngoba akubalulekile ekuxazululeni inkinga. Ngemuva kwalokho sichofoza inkinobho. "Kulungile"okuyinto ekhoneni elingenhla kwesokudla kwewindi "Ukunciphisa".
- Lolu hlelo lubala ngesisekelo sedatha yangena ngaphambilini futhi lubonisa umphumela ebangeni elichaziwe. Njengoba ubona, leli thuluzi libonisa eshadini inani elikhulu kakhulu lemiphumela kumingcele ehlukahlukene. Kodwa kumongo wesifundo samanje sinesithakazelo kulesi sibonakaliso "R-square". Kulesi simo, lilingana no-0.947664, elichaza imodeli ekhethiwe njengendlela yokulinganisa ikhwalithi elihle.
Indlela yesithathu: i-coefficient yokuzimisela komugqa wokuhamba
Ngaphandle kwezinketho ezingenhla, i-coefficient yokuzimisela ingaboniswa ngokuqondile kumzila wesitayela kugrafu ekwakhiweni kwe-Excel sheet. Sizobona ukuthi lokhu kungenziwa kanjani ngesibonelo sokhonkolo.
- Sinegrafu esekelwe etafuleni lama-arguments kanye namagugu womsebenzi owawusetshenziselwa isibonelo sangaphambilini. Ake senze umugqa wendabuko kuwo. Sichofoza kunoma iyiphi indawo endaweni yokwakhiwa lapho igrafu ifakwa khona inkinobho yegundane lesokunxele. Ngesikhathi esifanayo, isethi eyengeziwe yamathebhu ibonakala ku-ribbon - "Ukusebenza ngamashadi". Iya kuthebhu "Ukuhlelwa". Sichofoza inkinobho "Umugqa wethenda"esitholakala kwibhulogi yamathuluzi "Ukuhlaziywa". Imenyu ibonakala ngokukhetha uhlobo lohlobo lomzila. Siyeka ukukhetha ngohlobo oluhambisana nomsebenzi othize. Ukuze sibone isibonelo sethu, ake sikhethe "Ukulinganisa kwe-Exponential".
- I-Excel yakha umugqa wendabuko ngesimo sekhava elimnyama elingeziwe ngqo edizeni lokushayela.
- Manje umsebenzi wethu ukubonisa coefficient yokuzimisela ngokwayo. Sichofoza ngakwesokudla kulayini lomkhuba. Imenyu yenkathi ivuliwe. Yeka ukhetho kulo nto "Ithebula lesikhangiso somugqa ...".
Ukwenza ushintsho ewindini lefomethi yefomethi yezintambo, ungenza isenzo esithile. Khetha umugqa wesitayela ngokuchofoza kuso ngenkinobho yegundane lesokunxele. Hambisa kuthebhu "Ukuhlelwa". Sichofoza inkinobho "Umugqa wethenda" ku-block "Ukuhlaziywa". Ohlu oluvulekayo, sichofoza entweni yokugcina ohlwini lwezenzo - "Izinketho ezithuthukisiwe ze-Trend Line ...".
- Ngemuva kweyodwa yalezi zenzo ezimbili ezingenhla, iwindi lesakhiwo liqaliswe lapho ungenza khona izilungiselelo ezengeziwe. Ngokuyinhloko, ukwenza umsebenzi wethu, kubalulekile ukuhlola ibhokisi eliseduze "Faka ishadi ukubaluleka kokunemba kwesilinganiso (R ^ 2)". Itholakala phansi kakhulu kwefasitela. Okusho ukuthi ngale ndlela sihlanganisa ukuboniswa kwe-coefficient yokuzimisela endaweni yokwakhiwa. Bese ungakhohlwa ukucindezela inkinobho "Vala" ngezansi kwewindi langoku.
- Inani lokuzethemba lokulinganisa, okungukuthi, inani le-coefficient yokuzimisela, lizoboniswa eshidi endaweni yendawo. Kulesi simo, leli xabiso, njengoba sibona, liyi-0.9242, elichaza ukulinganisa, njengesibonelo sekhwalithi enhle.
- Kulungile ukuthi ungabeka ukubonisa kwe-coefficient yokuzimisela kunoma yiluphi olunye uhlobo lomugqa wendabuko. Ungashintsha uhlobo lomzila wesitayela ngokwenza ushintsho ngokusebenzisa inkinobho ebhethini noma kwimenyu yokuqukethwe kwindiza yayo yemingcele, njengoba kuboniswe ngenhla. Khona-ke kakade efasiteleni eqenjini "Ukwakha umugqa womthamo" ingashintshela kolunye uhlobo. Ungakhohlwa ukulawula ukuze eduze nendawo "Beka eshadini ukubaluleka kokunemba kwesilinganiso" ihlolwe. Ngemva kokuqeda izinyathelo ezingenhla, chofoza inkinobho. "Vala" ekhoneni elingaphansi kwesokudla lewindi.
- Uma kwenzeka uhlobo olulinganiselwe, umugqa wesitayela usuvele unenani lokuzethemba lokulinganiselwa kwe-0.9477, elichaza lo modeli njengokwethenjelwa nakakhulu kunomugqa wendabuko yohlobo lomdwebo esacatshangelwe ekuqaleni.
- Ngakho-ke, ukushintsha phakathi kwezinhlobo ezahlukene zemikhawulo yokuthambekela nokuqhathanisa amanani abo wokuzethemba kokulinganisa (coefficient yokuzimisela), ungathola okuhlukile, umdwebo ochaza ngokunembile igrafu ethunyelwe. Ukuhluka okuhambisana nenani eliphakeme kakhulu lokuzimisela kuzoba okuthembekile kakhulu. Ngesisekelo salo, ungakha isibikezelo esinembile kakhulu.
Isibonelo, ukuze icala lethu, ngokuhlolwa, sikwazi ukuthola ukuthi izinga eliphakeme lokuzethemba liwuhlobo lwe-polynomial yomzila wesitayela sesibili-degree. I-coefficient yokuzimisela kuleli cala ilingana no-1. Lokhu kusikisela ukuthi le model impela enokwethenjelwa, okusho ukuqedwa okuphelele kwamaphutha.
Kodwa ngesikhathi esifanayo, lokhu akusho neze ukuthi lolu hlobo lomzila wesitayela luzobe lunokwethenjelwa kakhulu kwesinye ishadi. Ukukhetha okuhle kohlobo lomzila wendabuko kuxhomeke kuhlobo lomsebenzi ngesisekelo esakhiwe ngalo igrafu. Uma umsebenzisi engenawo ulwazi olwanele lokulinganisa ukhetho oluphezulu kakhulu, khona-ke indlela kuphela yokuthola ukubikezela okuhle kakhulu nje ukuqhathaniswa kwama-coefficients of determination, njengoba kuboniswe kusibonelo esingenhla.
Bheka futhi:
Ukwakha imigqa yesimo se-Excel
Ukulinganisa kwe-Excel
E-Excel kunezinketho ezimbili eziyinhloko zokubala i-coefficient yokuzimisela: usebenzisa opharetha I-KVPIRSON kanye nethuluzi lesicelo "Ukunciphisa" kusuka emphakathini wamathuluzi "Ukuhlaziywa kwedatha". Kulokhu, okokuqala kokukhethwa kukho kuhloswe ukuthi kusetshenziswe kuphela ekusetshenzisweni komsebenzi olinganiselwe, futhi enye inketho ingasetshenziswa cishe kuzo zonke izimo. Ngaphezu kwalokho, kungenzeka ukubonisa i-coefficient yokuzimisela komugqa wendabuko wamagrafu njengenani lokuzethemba lokulinganisa. Ukusebenzisa le nkomba, kungenzeka ukunquma uhlobo lomzila wesitayela ophethe izinga eliphezulu lokuzethemba lomsebenzi othile.
