Ukubalwa kokuqina kokuzimisela ku-Microsoft Excel

Enye yezinkomba ezichaza ikhwalithi yemodeli eyakhiwe kuzibalo yi-coeff kukwana yokuzimisela (R ^ 2), ebizwa nangokuthi inani lokuqiniseka lokulinganiselwa. Ngalo, unganquma izinga lokunemba kwesimo sezulu. Ake sibheke ukuthi ungasibala kanjani lesi sikhombisi usebenzisa amathuluzi ahlukahlukene we-Excel.

Ukubalwa kokuqina kokuzimisela

Ngokuya ngezinga le-coeffanele lokuzimisela, kungokwesiko ukwahlukanisa amamodeli abe ngamaqembu amathathu:

• 0.8 - 1 - imodeli yekhwalithi enhle;
• 0.5 - 0,8 - imodeli yekhwalithi eyamukelekayo;
• 0 - 0,5 - imodeli yekhwalithi empofu.

Endabeni yamuva, ikhwalithi yemodeli ikhombisa ukungakwazi ukusetshenziswa kwayo kokubikezela.

Ukukhethwa kwendlela yokubala inani elicacisiwe ku-Excel kuya ngokuthi ukubuyelela kuqondile noma cha. Esimweni sokuqala, ungasebenzisa umsebenzi KVPIRSON, futhi kwesesibili kufanele usebenzise ithuluzi elikhethekile kusuka kuphakeji yokuhlaziya.

Indlela yoku-1: ukubalwa kokuqina kokuzimisela ngomsebenzi olayini

Okokuqala, sizothola ukuthi ungayithola kanjani i-coeff kukwana yokuzimisela yomsebenzi olayini. Kulesi simo, le nkomba izolingana nesikwele se-coefflication efanelekile. Sizoyibala sisebenzisa umsebenzi owakhelwe ngaphakathi we-Excel kwisibonelo setafula elithile, elinikezwe ngezansi.

1. Khetha iseli lapho kuzobekwa khona i-coefflication ephelele ngemuva kokubalwa kwayo, bese uchofoza isithonjana "Faka umsebenzi".



2. Kuqala Isici Wizard. Ukuthuthela esigabeni sakhe "Izibalo" bese umaka igama KVPIRSON. Ngokuchofoza inkinobho "Kulungile".



3. Iwindi lokuphikisana komsebenzi liyaqala. KVPIRSON. Lo opharetha ovela eqenjini lezibalo wenzelwe ukubala isikwele se-coefflication yomsebenzi wePearson, okuwukuthi, umsebenzi olayini. Futhi njengoba sikhumbula, ngomsebenzi olayini, i-coefflements yokuzimisela ilingana ncamashi nesikwele se-coefferior.

   I-syntax yalesi sitatimende yile:

   = KVPIRSON (amanani aziwayo_; aziwayo_x)

   Ngakho-ke, umsebenzi unabahlinzeki ababili, omunye wabo uhlu lwamanani wokusebenza, owesibili ungukuphikisana. Ama-opharetha angabekwa ngokuqondile njengamanani afakwe ku-semicolon (;), nangendlela yezixhumanisi eziya kumabanga lapho zitholakala khona. Kuyindlela yokugcina ezosetshenziswa yithi kulesi sibonelo.

   Setha isikhombisi esigangeni Izindinganiso ezaziwayo. Sibambe inkinobho yegundane kwesobunxele bese ukhetha okuqukethwe ikholomu "Y" amatafula. Njengoba ukwazi ukubona, ikheli lehlu ledatha ecacisiwe liboniswa ngokushesha efasiteleni.

   Ngendlela efanayo, gcwalisa insimu Ama-x aziwayo. Faka ikhesa kule nkambu, kepha kulokhu khetha amanani wekholomu "X".

   Ngemuva kokuthi yonke imininingwane ibonisiwe ewindini lokuphikisana KVPIRSONchofoza inkinobho "Kulungile"etholakala ezansi kwayo impela.



4. Njengoba ubona, ngemuva kwalokhu uhlelo lubala inani lokuzimisela futhi libonisa umphumela esitokisini esakhethwe ngisho nangaphambi kocingo Umsebenzi Wizards. Esibonelweni sethu, inani lesikhombi elibaliwe laphenduka laba yi-1. Lokhu kusho ukuthi imodeli eyethulwe ithembeke ngokuphelele, okungukuthi, iqeda iphutha.

Isifundo: Isici seWizard kuMicrosoft Excel

Indlela 2: ukubalwa kokuqina kokuzimisela emisebenzini engalingani

Kepha inketho engenhla yokubala inani olifunayo lingasetshenziswa kuphela emisebenzini eqondile. Yini okufanele uyenze ukuyibala ekusebenzeni okungelayini? E-Excel kunethuba elinjalo. Kungenziwa ngethuluzi. "Regression"okuyingxenye yephakeji "Ukuhlaziywa kwedatha".

1. Kepha ngaphambi kokusebenzisa ithuluzi elicacisiwe, kufanele usebenze ngokwakho Iphakheji Yokuhlaziya, ekhutshazwe ngokuzenzakalela ku-Excel. Hambisa kuthebhu Ifayelabese uya ku "Izinketho".



2. Ewindini elivulayo, dlulela esigabeni "Okengezwayo" ngokuzulazula imenyu yokuma mpo kwesobunxele. Ngezansi kwephaneli kwesokudla kwewindi kunensimu "Abaphathi". Kusukela kuhlu lweziqeshana ezitholakala lapho, khetha igama "I-Excel Add-ins ..."bese uchofoza inkinobho "Hambani ..."etholakala ngakwesokunene sensimu.



3. Iwindi lokungezwa liqalisiwe. Engxenyeni yayo ephakathi kukhona uhlu lwezengezo ezitholakalayo. Setha ibhokisi lokuhlola eduze kwesikhundla Iphakheji Yokuhlaziya. Ukulandela lokhu, chofoza inkinobho "Kulungile" ngakwesokunene esibonakalayo sewindi.



4. Iphakethe lamathuluzi "Ukuhlaziywa kwedatha" esimweni samanje se-Excel sizosebenza. Ukufinyelela kuyo kutholakala kubambo kuthebhu "Idatha". Sithuthela kuthebhu ebekiwe bese sichofoza inkinobho "Ukuhlaziywa kwedatha" eqenjini lezilungiselelo "Ukuhlaziywa".



5. Iwindows liyasebenza "Ukuhlaziywa kwedatha" nohlu lwamathuluzi akhethekile wokucutshungulwa kolwazi. Khetha into kulolu hlu "Regression" bese uchofoza inkinobho "Kulungile".



6. Bese kuvulwa iwindi lesisetshenziswa "Regression". Ibhlokhi lokuqala lezilungiselelo "Okokufaka". Lapha ezinkambini ezimbili udinga ukucacisa amakheli wamabanga lapho amanani wempikiswano nomsebenzi atholakala khona. Faka ikhesa endle "Ukufakwa kwesikhashana kwe-Y" bese ukhetha okuqukethwe kwikholamu eshidi "Y". Ngemuva kokuthi ikheli lamalungu afakiwe liboniswe ewindini "Regression"faka isikhombisi esigangeni "Ukufakwa kwesikhashana kwe-Y" bese ukhetha amaseli ekholomu ngendlela efanayo "X".

   Mayelana nemingcele "Ilebula" futhi UConst Zero ungafaki amafulegi. Ibhokisi lokuhlola lingasetwa eceleni kwepharamitha. "Ileveli yokuthembela" futhi enkambeni ephambene, bonisa inani elifunayo lesikhombi elihambisanayo (95% ngokuzenzakalelayo).

   Eqenjini Izinketho zokukhipha udinga ukucacisa ukuthi kuzoboniswa muphi umphumela wokubala. Kunezindlela ezintathu:

   • Indawo eseshidi lamanje;
   • Elinye iphepha;
   • Enye incwadi (ifayela elisha).

   Ake sikhethe inketho yokuqala ukuze idatha yomthombo nemiphumela ibekwe ekhasini elifanayo lokusebenzela. Sibeka switch eduze kwepharamitha "Isikhashana sokuphumayo". Ensimini ephikisana nale nto, faka isikhombisi. Qhafaza kwesobunxele entweni engenalutho eshidini, eklanyelwe ukuba iseli elingaphezulu kwesokunxele kwetafula lokubala. Ikheli lale nto kufanele liboniswe enkombeni yewindi "Regression".

   Amaqembu Epharamitha "Abashiyayo" futhi "Okuvamile kungenzeka" Ziba, ngoba azibalulekile ekuxazululeni umsebenzi. Ngemuva kwalokho chofoza inkinobho "Kulungile"ekhoneni eliphezulu kwesokudla sewindi "Regression".



7. Uhlelo lubala ngokususelwa kwidatha efakwe ngaphambili futhi libonisa umphumela kububanzi obucacisiwe. Njengoba ukwazi ukubona, leli thuluzi libonisa inani elikhulu lemiphumela kumapharamitha ahlukahlukene eshidini. Kepha esimweni sesifundo samanje, sinentshisekelo kwinkomba Isikwele. Kulokhu, lilingana no-0.947664, elimelela imodeli ekhethiwe njengemodeli yekhwalithi enhle.

Indlela 3: ukuzimisela kokulingana kulayini wethremu

Ngaphezu kokukhethwa kukho okungenhla, i-coeffanele yokuzimisela ingakhonjiswa ngqo ngomugqa wethenda kwigrafu eyakhelwe ekhasini lokusebenzela le-Excel. Sizothola ukuthi lokhu kungenziwa kanjani ngesibonelo esithile.

1. Sinegrafu ngokususelwa etafuleni lokuphikisana namanani wokusebenza asetshenziselwa isibonelo esedlule. Sizokwakhela umugqa wethrendi kuyo. Sichofoza kunoma iyiphi indawo yendawo yokwakha lapho kubekwa khona ishadi, nenkinobho yegundane kwesobunxele. Ngasikhathi sinye, kuvela enye isethi yamathebhu kubambo - "Ukusebenza namashadi". Iya kuthebhu "Isakhiwo". Chofoza inkinobho Umugqa Wendlelaelibekwe endaweni yethuluzi "Ukuhlaziywa". Imenyu ivela ngokukhetha kohlobo lomugqa wethenda. Simisa ukukhethwa kohlobo oluhambelana nomsebenzi othile. Ake sikhethe inketho yesibonelo sethu "Ukucacisa okucacisiwe".



2. I-Excel yakha umugqa wethenda ngesimo sejika elimnyama elengeziwe eshadini.



3. Manje umsebenzi wethu ukukhombisa amandla okuzimisela uqobo. Qhafaza kwesokudla kulayini wesitayela. Imenyu yokuqukethwe icushiwe. Simisa ukukhetha kuyo "Uhlobo lomugqa wethenda ...".

   

   Ukuze wenze ushintsho ewindini lesakhiwo somugqa wefreyimu, ungenza enye into. Khetha umugqa wesitayela ngokuchofoza kukho inkinobho yegundane kwesobunxele. Hambisa kuthebhu "Isakhiwo". Chofoza inkinobho Umugqa Wendlela ebhlokini "Ukuhlaziywa". Kuluhlu oluvulayo, chofoza entweni yokugcina kuhlu lwezenzo - "Imingcele eyengeziwe yomugqa wokuhambisa ...".



4. Ngemuva kwanoma yisiphi kulezi zenzo ezimbili ezingenhla, kuvulwa iwindi lefomethi lapho ungenza khona ezinye izilungiselelo. Ikakhulu, ukuqeda umsebenzi wethu, kuyadingeka ukuthi ubheke ibhokisi elihlukile "Beka inani lesilinganiso sokuqiniseka (R ^ 2) kumdwebo". Itholakala ezansi kwewindi. Lokho wukuthi, ngale ndlela sinika amandla ukuboniswa kokuqina kokuzimisela endaweni yokwakha. Ngemuva kwalokho ungakhohlwa uchofoze inkinobho Vala ezansi kwewindows yamanje.



5. Inani lokwethenjwa kokusondelana, okungukuthi, inani lokuqina kokuzimisela, lizokhonjiswa kushidi endaweni yokwakha. Kulesi simo, leli nani, njengoba sibona, liyi-0.9242, efanekisela ukusondela njengemodeli yekhwalithi enhle.



6. Ngqo ngale ndlela ungabeka ukubonisa okuphelele kokuzimisela kwanoma yiluphi olunye uhlobo lomugqa wethenda. Ungaguqula uhlobo lomugqa wethenda ngokwenza ushintsho ngenkinobho ekubambeni noma kwimenyu yokuqukethwe ewindini lamapharamitha alo, njengoba kukhonjisiwe ngenhla. Bese kuwindi ngokwalo eqenjini "Ukwakha umugqa wethayela" Ungashintshela kolunye uhlobo. Ngasikhathi sinye, ungakhohlwa ukulawula lokho endaweni yephuzu "Beka inani lokuqiniseka lokulinganisa kumdwebo" ibhokisi lokuhlola lahlolwa. Ngemuva kokuqeda lezi zinyathelo ezingenhla, chofoza inkinobho Vala ekhoneni elingezansi kwesokudla sewindi.



7. Ngohlobo lomugqa, umugqa wesitayela usevele unenani lokuqiniseka lokulingana elilingana ne-0.9477, elibonakalisa leli modeli lithembeke kakhulu ukwedlula umugqa wethayela wohlobo lwe-exponential olucatshangelwe kithi phambilini.



8. Ngakho-ke, ukushintshashintsha phakathi kwezinhlobo ezihlukile zemigqa yokuthambekela nokuqhathanisa amanani abo wokuqiniseka okuphelele (inhlanganisela yokuvuma), singathola inketho oyimodeli yakhe ichaza kahle igrafu eyethulwe. Inketho nge-coefficient ephezulu yokuzimisela yokuqina iyona ethembekile kunazo zonke. Ngokusekelwe kulo, ungakha isibikezelo esinembayo kunazo zonke.

   Isibonelo, ngakithi kungenzeka ukuthi ngokoqobo kutholakale ukuthi uhlobo lwe-polynomial of line Line le-degree lesibili linokuzethemba okuphakeme kakhulu. Ukuqina kokuzimisela kuleli cala kuyinto 1. Lokhu kuphakamisa ukuthi le modeli ithembeke ngokuphelele, okusho ukuthi ukushiywa ngaphandle kwamaphutha ngokuphelele.

   

   Kepha, ngasikhathi sinye, lokhu akusho neze ukuthi kwelinye ishadi lolu hlobo lomugqa wethenda nalo luthembekile kakhulu. Ukuzikhethela okuhle kohlobo lomugqa wokulinganisa kuya ngohlobo lomsebenzi ngesisekelo ishadi elakhiwe ngalo. Uma umsebenzisi engenalo ulwazi olwanele lokulinganisa ikhwalithi enhle ngokwehluka iso, khona-ke ukuphela kwendlela yokuthola isibikezelo esihle kakhulu ukuqhathanisa ama-coefficients, njengoba kukhonjisiwe kwisibonelo esingenhla.

Funda futhi:
Ukwakha umugqa wokuthambekela e-Excel
Ukusondela ku-Excel

Kunezinketho ezimbili ezibalulekile zokubalwa kwenhlanzeko yokuzimisela ku-Excel: ukusebenzisa opharetha KVPIRSON nokusetshenziswa kwamathuluzi "Regression" kusuka kubhokisi lamathuluzi "Ukuhlaziywa kwedatha". Ngaphezu kwalokho, eyokuqala yalezi zinketho ihloselwe ukusetshenziswa kuphela ekusetshenzisweni komsebenzi olayini, futhi enye inketho ingasetshenziswa cishe kuzo zonke izimo. Ngaphezu kwalokho, kungenzeka ukuthi kukhombise ukuqina kokuzimisela komugqa wethagethi yamatagi njengenani lokuthenjwa kokusondela. Usebenzisa le nkomba, kungenzeka ukunquma uhlobo lomugqa wethenda onenqanaba eliphakeme kakhulu lokuzethemba ngomsebenzi othile.