Kutsunga kugoverana muExcel

Imwe yezviratidzo zvinotsanangura mhando yemhando yakavakirwa mumhando ndeyekutsiva ndeyekutsiva (r ^ 2), iyo inonzi zvakare kukosha kwekunyatsoenderana. Nayo, iwe unogona kuona iyo nhanho yekufanotaura kwepfungwa. Ngatidzidzei nzira yekuverenga iyi chiratidzo ichishandisa akasiyana-siyana excel maturusi.

Kuverenga kweiyo coefficient yekutsunga

Zvichienderana nemwero wekusarudza coacfficient, itsika kugovera mhando mumapoka matatu:

• 0.8 - 1 - muenzaniso wemhando yakanaka;
• 0.5 - 0.8 - muenzaniso wemhando inogamuchirika;
• 0 - 0.5 - muenzaniso wemhando isina kunaka.

Muchiitiko chekupedzisira, kunaka kwemuenzaniso kunoratidza kusakwanisa kwekushandiswa kwayo kwekufungidzira.

Kusarudzwa kwenzira yekuverengera iyo yakataurwa kukosha muExcel kunoenderana nekuti kudzoreredzwa kuri mutsara here kana kuti kwete. Muchiitiko chekutanga, iwe unogona kushandisa iyo firson basa, uye mune yechipiri iwe uchafanirwa kushandisa chishandiso chakakosha kubva kune yekuongorora package.

Nzira 1: Kuverenga kweiyo STOPSS COECEFFIENTION NEZVINHU ZVINOGONESA

Chekutanga pane zvese, tsvaga nzira yekuwana iyo yekutsunga kujairana neine mutsara basa. Mune ino kesi, ichi chiratidzo chichaenzana nekwere yekubatana. Isu tichaita kuti yaunganidzwa uchishandisa yakanyorwa Excel inoshanda pane iyo muenzaniso weimwe tafura, iyo inoratidzwa pazasi.

1. Isu tinosimbisa iyo sero panofanira kugadzirwa kwekusarudza kunze kwekuverenga kwaro, uye tinya pane "quant basa" icon.



2. Maitiro ekutanga anotanga. Isu tinofamba kuenda kuChikamu Chayo "Statistical" uye kuongorora zita rekuti "kvgirson". Inotevera, ivhu pane "ok" bhatani.



3. Iyo Quilinson inoshanda nharo hwindo rinotanga. Iyi inoshanda kubva kuhuwandu hwehuwandu hwakagadzirirwa kuverenga chikamu checrefflation coeffication yekubatana kweBearson basa, kureva kuti, mutsetse wacho basa. Uye sezvatinorangarira, neyenzuro basa, iyo yekutsiva yakangoenzana neyakaenzana pakubatana.

   Iyo syntax yeiyi opareta ndeye:

   = CVGirson (inozivikanwa_Stations_y; inozivikanwa_name_x)

   Saka, basa rine vashandisi vaviri, imwe yeiyo runyorwa rwemabasa ebasa, uye yechipiri - nharo. Vashandisi vanogona kunongedzwa zvakananga sekukosheswa zvakanyorwa kuburikidza semicolon (;) uye muchimiro chekuratidzwa kune manhamba avakarongeka. Iyi ndiyo yekupedzisira sarudzo uye ichashandiswa nesu mune uyu muenzaniso.

   Isa iyo isai mu "inozivikanwa v inokosha" munda. Isu tinoita iyo clamp yeiyo kuruboshwe bhatani bhatani uye kugadzira zvirimo zve "Y" denderedzwa retafura. Sezvauri kuona, kero yeiyo yakataurwa data yakaratidzwa ipapo pahwindo.

   Saizvozvo, zadza iyo "inozivikanwa X" munda. Isu tinoisa iyo inotuka mumunda uyu, asi panguva ino inogovera iyo tsika yeiyo column "x".

   Mushure mekunge data rese riratidzwe muCVGirson kupokana newindo, iyo ivhu pane "ok" bhatani riri muzasi rayo.



4. Sezvauri kuona, mushure meizvi, chirongwa ichi chinoita kuti uverenge zvekufungidzira uye zvinopa mhedzisiro yacho muchitokisi chakaratidzwa usati wafona mumhanzi wemabasa emabasa. Mumwe muenzaniso wedu, kukosha kwechiratidzo chekuverengerwa kwakazoenzana neyakaenzana ne 1. Izvi zvinoreva kuti muenzaniso wakaratidzwa wakavimbika zvakakwana, ndiko kuti, kubvisa chikanganiso.

Chidzidzo: Master of Mabasa muMicrosoft Excel

Nzira yechipiri: Kuverenga kweiyo STOFICtion COEFEFITION MUNONI MUFANANIDZO

Asi sarudzo iri pamusoro yekuverenga iyo yaunoda kukosha inogona kushandiswa chete kumisangano yemutsara. Zvokuita kuti uverenge kuverenga kwayo muisiri risiri replinear? Mukurinda pane mukana. Inogona kuitwa uchishandisa "kudzoreredzwa" chishandiso, icho chinhu chinobatanidza che data yekuongorora data.

1. Asi usati washandisa chishandiso chakataurwa, "kuongorora" pachayo kunofanirwa kuitwa, uko kwakaremara nekukasira mukubika. Isu tinofambisa kune iyo "faira" tab, uye wozoenda kuburikidza ne "parameter" chinhu.



2. Muwindo rinovhura, ita kuti uende kune iyo "yekuwedzera-mu" chikamu uchishandisa iyo yekufamba kuruboshwe rwemukati menyu. Pazasi pewindo rekurudyi, munda wekutonga uri. Kubva pane rondedzero yeiyo substictions inowanikwa ipapo, sarudza zita re "Wedzera-mu Execel ..." uye tinya bhatani re "enda ...", iri kurudyi kwemunda.



3. Hwindo rakatangwa. Mune chikamu chayo chepakati pane runyorwa rwevarwi anowanikwa-ons. Isa iyo bhokisi rekutarisa padyo nechimiro "Ongorora package". Kutevera izvi, iwe unofanirwa kudzvanya pane "ok" bhatani kurudyi kwewindows interface.



4. Iyo data yekuongorora data yekudzidzira mune yazvino muenzaniso muenzaniso uchaitwa. Kuwanikwa kwairi kuri pane tepi mune iyo data tab. Famba muTabu yakatsanangurwa uye ivhu pa "kuongororwa kwedata" mu "kuongororwa" Zvirongwa zveGround.



5. Iyo "kuongororwa kwedata" hwindo kunoitwa nerondedzero yeruzivo rwekugadzirisa zvishandiso. Isu tinogovera kubva pane iyi runyorwa chinhu "kudzoreredzwa" uye ivhu pane "ok" bhatani.



6. Ipapo "kudzoreredza" Window towindo rinovhura. Iyo yekutanga zvigadziriso block ndeye "Input data". Pano muminda miviri yaunoda kuti utaure kero yemarangi ingaitwa nemhando dzekupokana uye basa riripo. Isu tinoisa iyo inotuka mu "Input Internval Y" Shamba uye govera zvirimo zve "Y" ikhongwe papepa. Mushure mekero yeiyo rondedzero inoonekwa muWornational Window, tinoisa iyo inotuka mu "Input Internval Y" Munda uye kugovera masero e "X" ikhoramu nenzira imwechete.

   Pedyo neparamende "tag" uye "nguva dzose-zero" mireza haifukidze. Bhokisi rekutarisa rinogona kuiswa padhuze ne "kuvimbika" paramende uye mumunda wevanhu vanotaura kukosha kweiyo inoenderana chiratidzo (ne default 95%).

   Mu "paramende inobuda" boka, iwe unofanirwa kutaura munzvimbo iyo icharatidzwa mhedzisiro yekuverenga. Pane matatu sarudzo:

   • Nharaunda pane razvino pepa;
   • Rimwe shizha;
   • Rimwe bhuku (idzva faira).

   Simbisa sarudzo yako mumutambo wekutanga kuitira kuti iyo data sosi uye mhedzisiro inoiswa pane imwe yebasa rebasa. Isu tinoisa iyo switch pedyo ne "kuburitsa nguva yekubuda" paramende. Mumunda wakapesana nechinhu ichi, tinoisa iyo inotora. Dzvanya bhatani rekuruboshwe mbeva pane chinhu chisina chinhu pane pepa, iyo yakagadzirirwa kuve iyo yakasara yekuisa sero retafura yekubira yekuverenga. Kero yechinhu ichi inofanira kuratidzwa mu "kudzoreredzwa" hwindo.

   Mapoka ema paramita "anosara" uye "zvakajairika mukana" fungidziro, sezvo zvisina kukosha pakugadzirisa basa racho. Mushure meizvozvo, isu tiri ivhu pane "ok" bhatani, iro rakaiswa mukona yekumusoro kurudyi kwe "kudzoreredzwa" hwindo.



7. Chirongwa ichi chinoita kuverenga kwakavakirwa pane yakambopinda data uye inoratidza mhedzisiro kune yakatarwa renji. Sezvauri kuona, chishandiso ichi chinoratidza huwandu hwakakura huwandu hwemhedzisiro pane dzakasiyana paramita. Asi mumamiriro ezvinhu echidzidzo chazvino, isu tinofarira iyo r-square chiratidzo. Mune ino kesi, yakaenzana ne 0.947664, iyo inoratidza iyo yakasarudzwa muenzaniso semuenzaniso wemhando yakanaka.

Nzira 3: Kufunga Ceacffient yeTrend Line

Pamusoro pezvisarudzo zvepamusoro, kuunganidzwa kwemasimba kunogona kuratidzwa zvakananga kune iyo nzira yemigwagwa iri muGirafu yakavakwa pane yeBexcel pepa. Tsvaga kuti izvi zvinogona kuitwa sei pamuenzaniso chaiwo.

1. Isu tine hurongwa hwakavakirwa pane hwaro hwetafura yepo uye mabasa ebasa raizoshandiswa muenzaniso wapfuura. Isu tichavaka rondedzero yemutsara kwariri. Dzvanya pane chero saiti yenzvimbo yekuvaka iyo iyo iyo purogiramu inowanikwa kuruboshwe-tinya. Panguva imwecheteyo, imwezve seti yematabhu inoonekwa patepi - "Shanda nemachati". Enda kuThebhu "chimiro". Clay pane "Trend Line" bhatani, iyo iri mu "kuongorora" Toolbar. Menyu inoonekwa nesarudzo yerudzi rwemutsara wekutora. Kutanga sarudzo pane mhando inoenderana neiyo chaiyo basa. Ngatisarudzei "exponential application" sarudzo yechokwadi.



2. EXPLEC inovaka zvakananga mundege yekugadzira mutsara weti mune fomu yekuwedzera dema.



3. Iye zvino basa redu ndere kuratidza iyo chaiyo yekutsunga kujairana. Dzvanya kurudyi-Dzvanya Pamwe chete mutsara weTrend. Mamambo menyu akaitwa. Tanga sarudzo mazviri kuTrend Line Fommen Peat.

   

   Kuita shanduko kune iyo Trend Line Fommen Gomiti yewindo, iwe unogona kuita imwe nzira. Isu tinosimbisa iyo nzira yekuita neiyo yekuruboshwe mbeva bhatani pazviri. Famba muThebhu "chimiro". Clay pane "Trend Line" bhatani mu "kuongorora" block. Mune rondedzero yeiyo clay runyorwa pane yakanakisa poindi yechiitiko chechiitiko - "mamwe maitiro emutsetse paramita ...".



4. Mushure mechimwe chezvimwe zvinhu zviri pamusoro apa, iyo yepahwindo inotangwa iyo iwe yaunogona kuburitsa zvimwe zvigadziriso. Kunyanya, kuzadzisa basa redu izvo zvinodikanwa kuti vatange bhokisi rekutarisa rakatarisana nechinhu "isa kukosha kwekukodzera kwekufungidzira (r ^ 2)". Iyo iri pazasi pepahwindo. Ndokunge, saka isu tinosanganisira kuratidzwa kwekutsunga kwesimba pane yekuvaka nzvimbo. Ipapo iwe haukanganwe kudzvanya pane "padyo" bhatani pazasi pehwindo razvino.



5. Iko kukosha kwekunyatsoenderana, ndiko kuti, kukosha kwekutsunga kweshizha kuchagadziriswa pane shizha munzvimbo yekuvaka. Mune ino kesi, kukosha uku kunoonekwa, kwakaenzana ne0.9242, kunoratidzira kuenderana semuenzaniso wemhando yakanaka.



6. Chaizvoizvo nenzira iyi, iwe unogona kumisikidza kusarudzira ceefficient kuratidzwa kune chero imwe mhando yerudzi rwonzi. Iwe unogona kushandura maitiro emhando yemhando nekuita shanduko kuburikidza nebhatani pane ribbon kana mamiriro emamenji muhwindo raro parmeter, sezvakaratidzwa pamusoro. Ipapo muhwindo pachayo mune iyo "kuvaka maitiro emutsara" boka, unogona kuchinjisa kune imwe mhando. Hatikanganwe kuidzora kuitira kuti "isa kukosha kwekukodzera kwekufungidzira pane iyo dhizaini yekukosha kwekukosha". Mushure mekupedza zviito zviri pamusoro, tinya pane bhatani "Pedyo" mukona yepazasi rekona yepahwindo.



7. Nerudzi rwemutsara, iyo trend line yatove nekukosha kwekukwana kwekufungidzira kwakaenzana ne 0.9477, iyo inoratidzira iyi modhi, sekutotarisirwa kune mutsara weiyo nzira yeiyo exponential tarisiro yakafungidzirwa nesu.



8. Nekudaro, kuchinja pakati pemhando dzakasiyana dzemitsetse uye kuenzanisa yavo yekuvimbika yekuvimbika kukosha (kutsiva ceacffient), iwe unogona kuwana sarudzo iyo iyo iyo modhi inonyatsotsanangura nenzira yakatumirwa. Sarudzo ine mwero wepamusoro wekutsunga ichave yakavimbika zvakanyanya. Pahwaro hwayo, iwe unogona kuvaka iyo chaiyo chaiyo fungidziro.

   Semuenzaniso, nyaya yedu, zvaigoneka kutanga kuti chikamu chepamusoro-soro chekuvimbika chine yePolynomial rudzi rwechipiri degree. Kutsunga kwesimba mune ino kesi ndeye 1. Izvi zvinoratidza kuti iyo yakataurwa mhando yakavimbika yakavimbika, iyo inoreva kuzadza kwakakwana kwekukanganisa.

   

   Asi, panguva imwe chete, izvi hazvireve kuti kune imwe girafu, rudzi urwu rwemutsara wekutora zvichave zvakavimbika zvakanyanya. Iko kusarudzwa kwakakwana kwerudzi rwemhando yemhando inoenderana nerudzi rwebasa, pane iyo iyo purogiramu yakavakwa. Kana mushandisi asina ruzivo rwakakwana ku "paziso racho" kufungidzira iyo yepamusoro mhando sarudzo, ipapo iyo chete yekubuda kwekusarudza iyo yakanakisa fungidziro ingori yekuenzanisa kwekufunga kwekufunga, sezvakaratidzwa muenzaniso pamusoro.

Onawo:

Kuvaka Trend Line mu Execel

Kuenderana muExcel

MuBudiriro, pane maviri makuru sarudzo ekuverenga iyo yekutsunga coefficient: Iko kushandiswa kwemushandisirwo weiyo firson yeiyo firson chishandiso kubva kune yekuongorora kwedata yekuongorora. Panguva imwecheteyo, yekutanga yeiyi sarudzo inoitirwa kushandiswa chete panguva yekugadziriswa kweiyo mutsara webasa, uye imwe sarudzo inogona kushandiswa mumamiriro ese ezvinhu. Mukuwedzera, zvinokwanisika kuratidza iyo yekutsunga ceacffient yemutsetse weGirafu yeGraphs yeGraphs sekukosha kwekukosha kwekusakwana. Neichi chiratidzo, zvinokwanisika kuona mhando yemutsara wekutora, iyo ine huwandu hwepamusoro hwekuvimbika kweiyo chaiyo basa.