Correlation analysis muExcel. Muenzaniso wekuita ongororo yekubatanidza

Correlation analysis inzira yakajairika yekutsvagisa inoshandiswa kuona huwandu hwekutsamira kweiyo 1st kukosha pa2nd. The spreadsheet ine chinhu chakakosha chinokubvumira kushandisa rudzi urwu rwekutsvakurudza.

Izvo zvakakosha zvekuongorora kuwirirana

Izvo zvinodikanwa kuti uone hukama pakati pehuwandu huviri hwakasiyana. Mune mamwe mazwi, inoratidza kuti ndeipi nzira (diki / yakakura) kukosha kunoshanduka zvichienderana nekuchinja kwechipiri.

Chinangwa chekuongorora kuwirirana

Kutsamira kunotangwa kana kuzivikanwa kweiyo coefficient yekubatanidza kunotanga. Iyi nzira inosiyana nekuongorora kudzoreredza, sezvo paine chiratidzo chimwe chete chakaverengerwa uchishandisa correlation. Iyo nguva inoshanduka kubva pa +1 kuenda -1. Kana iri yakanaka, saka kuwedzera kwekutanga kukosha kunobatsira kuwedzera kwe2. Kana zvisina kunaka, saka kuwedzera kwekutanga kukosha kunobatsira pakuderera kwechipiri. Iyo yakakwirira iyo coefficient, iyo yakasimba imwe kukosha inobata yechipiri.

Zvinokosha! Pa 0th coefficient, hapana hukama pakati pehuwandu.

Kuverengera kweiyo coefficient yekubatanidza

Ngationgororei kuverenga pane akati wandei sampuli. Semuyenzaniso, kune tabular data, uko kushandisa mari pakushambadzira kushambadzira uye vhoriyamu yekutengesa kunotsanangurwa nemwedzi mumakoramu akasiyana. Kubva patafura, tichaona chiyero chekutsamira kwehuwandu hwekutengesa pamari inoshandiswa pakushambadzira kushambadzira.

Method 1: Kusarudza Correlation kuburikidza neBasa Wizard

CORREL - basa rinokubvumira kuti uite kuongorora kwekubatanidza. General fomu - CORREL(massiv1;massiv2). Detailed instructions:

1. Izvo zvinodiwa kuti usarudze sero iyo yakarongwa kuratidza mhedzisiro yekuverenga. Dzvanya "Insert Function" iri kuruboshwe rwendima yemavara kuti uise fomula.

2. Iyo Function Wizard inovhura. Pano unofanira kuwana CORREL, tinya pairi, ipapo pa "OK".

3. Hwindo renharo rinovhurwa. Mumutsara we "Array1" iwe unofanirwa kuisa marongero enguva dzeiyo 1st yezvakakosha. Mumuenzaniso uyu, iyi ndiyo Sales Value column. Iwe unongoda kusarudza maseru ese ari mukoramu iyi. Saizvozvo, iwe unofanirwa kuwedzera marongero echikamu chechipiri kune "Array2" mutsara. Mumuenzaniso wedu, iyi ndiyo Koramu Yekushambadza Mari.

4. Mushure mekupinda muzvikamu zvese, tinya bhatani "OK".

Iyo coefficient yakaratidzwa musero iyo yakaratidzwa pakutanga kwezviito zvedu. Mhedzisiro yakawanikwa ndeye 0,97. Ichi chiratidzo chinoratidza kutsamira kwepamusoro kwekutanga kukosha pane yechipiri.

Method 2: Verenga Correlation Uchishandisa Analysis ToolPak

Pane imwe nzira yekusarudza kuwirirana. Pano rimwe remabasa rinowanikwa mupakeji yekuongorora rinoshandiswa. Usati waishandisa, unofanirwa kumisa chishandiso. Detailed instructions:

1. Enda kune "Faira" chikamu.

2. Iwindo idzva richazaruka, umo iwe unofanirwa kudzvanya pane "Settings" chikamu.
3. Dzvanya pa "Add-ons".
4. Isu tinowana chinhu "Management" pazasi. Pano iwe unofanirwa kusarudza "Excel Add-ins" kubva pane zvemukati menyu uye tinya "OK".

5. Hwindo rakakosha rekuwedzera rakavhurwa. Isa cheki padivi pe "Analysis Package" chinhu. Tinya "OK".
6. Kushandisa kwabudirira. Zvino ngatiende kuData. Iyo "Analysis" block yakaonekwa, umo iwe unofanirwa kudzvanya "Data Analysis".
7. Muhwindo idzva rinoonekwa, sarudza chinhu che "Correlation" uye tinya "OK".

8. Hwindo rekuongorora zvigadziriso rakaonekwa pachiratidziri. Mumutsara we "Input interval" zvinodikanwa kuti uise huwandu hwemakoramu ese ari kutora chikamu mukuongorora. Mumuenzaniso uyu, aya ndiwo makoramu "Kukosha kwekutengesa" uye "Mari yekushambadzira". Izvo zvinobuda zvekuratidzira zvigadziriso zvinotanga kuiswa kuNew Worksheet, zvinoreva kuti mhedzisiro icharatidzwa pane rakasiyana pepa. Optionally, unogona kushandura inobuda nzvimbo yemugumisiro. Mushure mekuita zvese zvigadziriso, tinya "OK".

Zvibodzwa zvekupedzisira zvabuda. Chigumisiro chakafanana nenzira yekutanga - 0,97.

Tsanangudzo uye kuverenga kweakawanda kuwirirana coefficient muMS Excel

Kuti uone huwandu hwekutsamira kwehuwandu hwakawanda, akawanda coefficients anoshandiswa. Mune ramangwana, zvigumisiro zvinopfupikiswa mune imwe tafura, inonzi correlation matrix.

Gwara rakadzama:

1. Muchikamu che "Data", tinowana yakatozivikanwa "Analysis" block uye tinya "Data Analysis".

2. Muhwindo rinoonekwa, tinya pane "Correlation" chinhu uye tinya "OK".
3. Mumutsara we "Input interval" tinotyaira mukati menguva yezvikamu zvitatu kana kupfuura zvetafura yezvinyorwa. Iyo renji inogona kuiswa nemaoko kana kungoisarudza neLMB, uye inongoerekana yaonekwa mumutsara waunoda. Mu "Grouping" sarudza nzira yakakodzera yekuunganidza. Mu "Output Parameter" inotsanangura nzvimbo iyo mibairo yekubatanidza icharatidzwa. Tinya "OK".

4. Ready! Iyo yekubatanidza matrix yakagadzirwa.

Pair Correlation Coefficient muExcel

Ngationei nzira yekudhirowa nemazvo maviri ekubatanidza coefficient muExcel spreadsheet.

Kuverengera kwepairi corelation coefficient muExcel

Semuenzaniso, une x uye y kukosha.

X ndiye mutsauko unoenderana uye y ndiye akazvimiririra. Izvo zvinodiwa kuti uwane kutungamira uye simba rehukama pakati pezviratidzo izvi. Nhanho-ne-nhanho mirayiridzo:

1. Ngatitsvagei avhareji kukosha tichishandisa basa MOYO.

2. Ngativerengei imwe neimwe х и xavg, у и avg uchishandisa iyo «-» mushandisi.

3. Tinowanza misiyano yakaverengwa.

4. Isu tinoverenga huwandu hwezviratidzo zviri mukoramu iyi. Nhamba ndiyo mhedzisiro yawanikwa.

5. Verenga madhinomineta emusiyano х и x-avhareji, y и y-pakati. Kuti tiite izvi, tichaita squaring.

6. Kushandisa basa AUTOSUMMA, tsvaga zviratidzo mumakoramu anoguma. Isu tinoita kuwanda. Kushandisa basa ROOT sikweya mugumisiro.

7. Isu tinoverenga iyo quotient tichishandisa kukosha kweiyo denominator uye numerator.

8. CORREL ibasa rakabatanidzwa rinokutendera kuti udzivise maverengero akaomarara. Tinoenda ku "Function Wizard", sarudza CORREL uye tsanangura maitiro ezviratidzo х и у. Isu tinovaka girafu rinoratidza maitiro akawanikwa.

Matrix yePairwise Correlation Coefficients muExcel

Ngationgororei kuti tingaverengera sei coefficients epairi matrices. Semuenzaniso, kune matrix emhando ina.

Nhanho-ne-nhanho mirayiridzo:

1. Tinoenda ku "Data Analysis", iri mu "Analysis" block ye "Data" tab. Sarudza Correlation kubva pane rondedzero inooneka.
2. Isu tinogadzirisa zvese zvinodiwa. "Input interval" - nguva yezvikamu zvina. "Output interval" - nzvimbo yatinoda kuratidza zvikamu. Tinya bhatani "OK".
3. A correlation matrix yakavakwa panzvimbo yakasarudzwa. Mharadzano yega yega yemutsara uye koramu ndiyo coefficient yekubatanidza. Nhamba 1 inoratidzwa kana makongisheni achienderana.

CORREL basa rekuona hukama uye kuwirirana muExcel

CORREL - basa rinoshandiswa kuverenga coefficient yekubatanidza pakati pe 2 arrays. Ngatitarisei mienzaniso mina yemano ose ebasa iri.

Mienzaniso yekushandisa iyo CORREL basa muExcel

Muenzaniso wekutanga. Pane ndiro ine ruzivo nezve avhareji yemuhoro yevashandi vekambani mukati memakore gumi nerimwe uye mutengo wekuchinjana wemadhora. Zvakakosha kuziva hukama huri pakati pezviviri izvi. Tafura inoita seizvi:

Iyo algorithm yekuverenga inoita seizvi:

Chibodzwa chataridzwa chave pedyo ne1. Mhedzisiro:

Kutemerwa kweiyo coefficient yekubatanidza yekukanganisa kwezviito pamhedzisiro

Muenzaniso wechipiri. Vaviri vakadaidzira vakaenda kumasangano maviri akasiyana kuti vabatsirwe nekusimudzirwa kwemazuva gumi nemashanu. Zuva rega rega sarudzo yemagariro yaiitwa, iyo yairatidza mwero werutsigiro rwemunyoreri wega wega. Chero munhu anobvunzurudzwa anogona kusarudza mumwe wevaviri vanyoreri kana kupikisa vese. Izvo zvinodikanwa kuti uone kuti yakawanda sei kushambadzira kwega kwega kusimudzira kwakapesvedzera dhigirii rerutsigiro rwevanyoreri, iyo kambani inoshanda zvakanyanya.

Tichishandisa mafomula ari pazasi, tinoverengera coefficient yekubatanidza:

• =CORREL(A3:A17;B3:B17).
• =CORREL(A3:A17;C3:C17).

Results:

Kubva pamigumisiro yakawanikwa, zvinova pachena kuti dhigirii yerutsigiro rwemunyoreri we1 yakawedzera nezuva rimwe nerimwe rekusimudzira kushambadzira, saka, iyo coefficient coefficient inosvika 1. Apo kushambadzira kwakatangwa, mumwe munyoreri aive nenhamba huru yekuvimba, uye ye 5 days paive nemaitiro akanaka. Ipapo dhigirii yekuvimba yakaderera uye nezuva regumi neshanu yakadonha pasi pezviratidzo zvekutanga. Zvibodzwa zvakaderera zvinoratidza kuti kukwidziridzwa kwakanganisa rutsigiro. Usakanganwa kuti zvimwe zvinopindirana izvo zvisina kutariswa mune tabular fomu zvinogona kukanganisa zviratidzo.

Ongororo yemukurumbira wezvemukati nekuwirirana kwevhidhiyo maonero uye repost

Muenzaniso wechitatu. Munhu wekusimudzira mavhidhiyo avo paYouTube vhidhiyo yekutambira anoshandisa masocial network kushambadza chiteshi. Anoona kuti pane hukama pakati pehuwandu hwezvinodzokororwa mumasocial network uye nhamba yemaonero pachiteshi. Zvinoita here kufanotaura kuita kweramangwana uchishandisa maturusi espredishiti? Izvo zvinodikanwa kuti uone kunzwisisika kwekushandisa iyo mutsara regression equation kufanotaura huwandu hwevhidhiyo maonero zvichienderana nehuwandu hwezvinodzokororwa. Tafura ine kukosha:

Zvino zvakakosha kuona kuvepo kwehukama pakati pe2 zviratidzo zvinoenderana neformula pazasi:

0,7;KANA(CORREL(A3:A8;B3:B8)>0,7;”hukama hwakananga hwakasimba”;“hukama hwakasimba”);“husina hukama kana husina”)' class='formula'>

Kana iyo inoguma coefficient yakakwirira kudarika 0,7, zvino inonyanya kukodzera kushandisa mutsara wekugadzirisa basa. Mumuenzaniso uyu, tinoita:

Iye zvino tiri kugadzira girafu:

Isu tinoshandisa iyi equation kuona huwandu hwemaonero pa200, 500 uye 1000 shares: =9,2937*D4-206,12. Tinowana zvinotevera:

basa ZVINOFANIRA inokubvumira kuti uone nhamba yemaonero panguva ino, kana paiva, semuenzaniso, mazana maviri nemakumi mashanu reposts. Isu tinoshandisa: 0,7;PREDICTION(D7;B3:B8;A3:A8);“Zvinhu hazvina hukama”)' class='formula'>. Tinowana zvinotevera:

Zvimiro zvekushandisa iyo CORREL basa muExcel

Iri basa rine zvinhu zvinotevera:

1. Masero asina chinhu haana kuverengerwa.
2. Masero ane Boolean uye Runyoro rwemhando yeruzivo haaverengerwe.
3. Kuramba kaviri "-" kunoshandiswa kuverengera maitiro ane musoro nenzira yenhamba.
4. Huwandu hwemasero ari muzvikamu zvakadzidzwa hunofanirwa kuenderana, zvikasadaro meseji #N/A icharatidzwa.

Kuongororwa kwehuwandu hwehuwandu hwehuwandu hwekugadzirisa

Paunenge uchiyedza kukosha kweiyo coefficient yekubatanidza, iyo null hypothesis ndeyekuti chiratidzo chine kukosha kwe0, nepo imwe nzira haina. Iyo inotevera fomula inoshandiswa kusimbisa:

mhedziso

Correlation wongororo muspredishiti inzira yakapfava uye yega. Kuti uzviite, iwe unongoda kuziva kuti maturusi anodiwa ari kupi uye kuti ungaamisa sei kuburikidza nehurongwa hwehurongwa.