Kusanthula kwa mgwirizano mu Excel. Chitsanzo chochita kusanthula kogwirizana

Kusanthula kwa mgwirizano ndi njira yodziwika yofufuzira yomwe imagwiritsidwa ntchito kudziwa kuchuluka kwa kudalira kwa mtengo wa 1 pa 2nd. Tsambali lili ndi chida chapadera chomwe chimakulolani kuti mugwiritse ntchito kafukufuku wamtunduwu.

Chofunika cha kusanthula kwa mgwirizano

Ndikofunikira kudziwa kugwirizana pakati pa mitundu iwiri yosiyana. Mwanjira ina, imawulula komwe (yaing'ono / yokulirapo) mtengo umasintha kutengera kusintha kwachiwiri.

Cholinga cha kusanthula mgwirizano

Kudalira kumakhazikitsidwa pamene chizindikiritso cha coefficient coefficient chikuyamba. Njirayi imasiyana ndi kusanthula kwa regression, popeza pali chizindikiro chimodzi chokha chowerengedwa pogwiritsa ntchito kugwirizanitsa. Nthawiyi imasintha kuchoka pa +1 kupita ku -1. Ngati zili zabwino, ndiye kuti kuwonjezeka kwa mtengo woyamba kumathandizira kuwonjezeka kwa 2. Ngati zoipa, ndiye kuwonjezeka kwa mtengo wa 1 kumathandizira kuchepa kwa 2. Kukwera kwa coefficient, mphamvu imodzi imakhudza chachiwiri.

Zofunika! Pa coefficient 0, palibe mgwirizano pakati pa kuchuluka.

Kuwerengera kwa coefficient yolumikizana

Tiyeni tipende kuwerengera pa zitsanzo zingapo. Mwachitsanzo, pali ma tabular data, pomwe ndalama zogulira malonda ndi kuchuluka kwa malonda zimafotokozedwa ndi miyezi m'magawo osiyana. Kutengera tebulo, tipeza mulingo wodalira kuchuluka kwa malonda pandalama zomwe zimagwiritsidwa ntchito potsatsa malonda.

Njira 1: Kuzindikira Kulumikizana Kudzera mu Wizard Yogwira Ntchito

CORREL - ntchito yomwe imakulolani kuti mugwiritse ntchito kusanthula kogwirizana. Fomu Yonse - CORREL(massiv1;massiv2). Malangizo atsatanetsatane:

1. Ndikofunikira kusankha cell yomwe ikukonzekera kuwonetsa zotsatira za kuwerengera. Dinani "Insert Function" yomwe ili kumanzere kwa gawo lolemba kuti mulowetse chilinganizo.

2. Function Wizard imatsegula. Apa muyenera kupeza CORREL, alemba pa izo, ndiyeno "Chabwino".

3. Zenera la mikangano limatsegulidwa. Mu mzere wa "Array1" muyenera kuyika ma coordinates a magawo a 1st of the values. Muchitsanzo ichi, iyi ndi gawo la Sales Value. Mukungoyenera kusankha ma cell onse omwe ali mugawoli. Mofananamo, muyenera kuwonjezera makonzedwe a gawo lachiwiri ku mzere wa "Array2". Muchitsanzo chathu, iyi ndi gawo la Mtengo Wotsatsa.

4. Pambuyo polowa mumitundu yonse, dinani batani "Chabwino".

Coefficient idawonetsedwa mu cell yomwe idawonetsedwa koyambirira kwa zochita zathu. Zotsatira zopezeka ndi 0,97. Chizindikirochi chikuwonetsa kudalira kwakukulu kwa mtengo woyamba pa chachiwiri.

Njira 2: Werengetsani Kulumikizana Pogwiritsa Ntchito Analysis ToolPak

Palinso njira ina yodziwira kugwirizana. Apa imodzi mwa ntchito zomwe zimapezeka mu phukusi lowunikira zimagwiritsidwa ntchito. Musanagwiritse ntchito, muyenera kuyambitsa chida. Malangizo atsatanetsatane:

1. Pitani ku gawo la "Fayilo".

2. Zenera latsopano lidzatsegulidwa, momwe muyenera kudina gawo la "Zikhazikiko".
3. Dinani "Zowonjezera".
4. Timapeza chinthu "Management" pansi. Apa muyenera kusankha "Excel Add-ins" kuchokera ku menyu yankhani ndikudina "Chabwino".

5. Zenera lapadera lowonjezera latsegulidwa. Ikani cholembera pafupi ndi chinthu cha "Analysis Package". Timadina "Chabwino".
6. Kutsegula kunapambana. Tsopano tiyeni tipite ku Data. Chotchinga cha "Analysis" chinawonekera, chomwe muyenera kudina "Analysis Data".
7. Pazenera latsopano lomwe likuwoneka, sankhani chinthu cha "Correlation" ndikudina "Chabwino".

8. Zenera la zoikamo zowunikira lidawonekera pazenera. Pamzere wa "Input interval" m'pofunika kuyika mndandanda wa magawo onse omwe akutenga nawo mbali pakuwunika. Muchitsanzo ichi, awa ndi mizati "mtengo wogulitsa" ndi "Ndalama zotsatsa". Zosintha zowonetsera zotuluka zimayikidwa ku New Worksheet, zomwe zikutanthauza kuti zotsatira zidzawonetsedwa papepala lina. Optionally, mukhoza kusintha linanena bungwe malo chifukwa. Mukapanga zokonda zonse, dinani "Chabwino".

Zigoli zomaliza zatuluka. Zotsatira zake ndizofanana ndi njira yoyamba - 0,97.

Tanthauzo ndi kuwerengera kwa ma coefficient angapo olumikizana mu MS Excel

Kuti adziwe kuchuluka kwa kudalira kwazinthu zingapo, ma coefficients angapo amagwiritsidwa ntchito. M'tsogolomu, zotsatirazo zikufotokozedwa mwachidule mu tebulo lapadera, lotchedwa correlation matrix.

Tsatanetsatane wa malangizo:

1. Mu gawo la "Data", timapeza "Analysis" yodziwika kale chipika ndikudina "Data Analysis".

2. Pazenera lomwe likuwoneka, dinani chinthu cha "Correlation" ndikudina "Chabwino".
3. Mu mzere "Input interval" timayendetsa mu nthawi ya magawo atatu kapena angapo a tebulo loyambira. Mtunduwu utha kulowetsedwa pamanja kapena kungosankha ndi LMB, ndipo umangowonekera pamzere womwe mukufuna. Mu "Gulu" sankhani njira yoyenera yopangira magulu. Mu "Output Parameter" imatchula malo omwe zotsatira zogwirizanitsa zidzasonyezedwe. Timadina "Chabwino".

4. Okonzeka! Matrix olumikizana adapangidwa.

Pair Correlation Coefficient mu Excel

Tiyeni tiwone momwe tingajambulire molondola coefficient yolumikizirana awiri mu Excel spreadsheet.

Kuwerengera kwa coefficient yolumikizirana awiri mu Excel

Mwachitsanzo, muli ndi ma values ​​a x ndi y.

X ndiye wodalira ndipo y ndi wodziyimira pawokha. Ndikofunikira kupeza chitsogozo ndi mphamvu ya ubale pakati pa zizindikirozi. Malangizo a pang'onopang'ono:

1. Tiyeni tipeze mtengo wapakati pogwiritsa ntchito ntchitoyi MTIMA.

2. Tiyeni tiwerengere chilichonse х и xavg, у и av kugwiritsa ntchito «-».

3. Timachulukitsa kusiyana kowerengeka.

4. Timawerengera kuchuluka kwa zizindikiro zomwe zili mugawoli. Nambala ndi zotsatira zomwe zapezeka.

5. Werengani ma denominators a kusiyana х и x-avereji, y и y-wapakatikati. Kuti tichite izi, timachita squaring.

6. Kugwiritsa ntchito AUTOSUMMA, pezani zizindikiro m'mizere yotsatila. Timachulukitsa. Kugwiritsa ntchito muzu manambala zotsatira.

7. Timawerengera quotient pogwiritsa ntchito milingo ya denominator ndi manambala.

8. CORREL ndi ntchito yophatikizika yomwe imakupatsani mwayi wopewa kuwerengera zovuta. Timapita ku "Function Wizard", sankhani CORREL ndikulongosola mndandanda wa zizindikiro х и у. Timamanga graph yomwe imasonyeza zomwe tapeza.

Matrix a Pairwise Correlation Coefficients mu Excel

Tiyeni tiwone momwe tingawerengere ma coefficients a matrices ophatikizidwa. Mwachitsanzo, pali matrix a mitundu inayi.

Malangizo a pang'onopang'ono:

1. Timapita ku "Data Analysis", yomwe ili mu "Analysis" block ya "Data". Sankhani Correlation kuchokera pamndandanda womwe umawonekera.
2. Timayika zokonda zonse zofunika. "Input interval" - nthawi ya magawo onse anayi. "Nthawi yotulutsa" - malo omwe tikufuna kuwonetsa ziwopsezo. Timadina batani "Chabwino".
3. Matrix olumikizana adapangidwa pamalo osankhidwa. Mzere uliwonse wa mzere ndi gawo ndi coefficient coefficient. Nambala 1 ikuwonetsedwa pamene zogwirizanitsa zikugwirizana.

CORREL imagwira ntchito kuti muwone ubale ndi kulumikizana mu Excel

CORREL - ntchito yomwe imagwiritsidwa ntchito powerengera coefficient yolumikizana pakati pa magulu awiri. Tiyeni tione zitsanzo zinayi za luso lonse la ntchitoyi.

Zitsanzo zogwiritsa ntchito ntchito ya CORREL mu Excel

Chitsanzo choyamba. Pali mbale yokhala ndi chidziwitso chokhudza malipiro apakati a ogwira ntchito pakampaniyo pazaka khumi ndi chimodzi komanso kusinthana kwa $. Ndikofunikira kuzindikira kugwirizana pakati paziwirizi. Tebulo likuwoneka motere:

Algorithm yowerengera ikuwoneka motere:

Chigoli chowonetsedwa chayandikira 1. Zotsatira:

Kutsimikiza kwa coefficient yolumikizana ya zomwe zimachitika pazotsatira

Chitsanzo chachiwiri. Otsatsa awiri adalumikizana ndi mabungwe awiri osiyanasiyana kuti awathandize ndi kukwezedwa kwamasiku khumi ndi asanu. Tsiku lililonse kafukufuku wokhudzana ndi chikhalidwe cha anthu adachitika, omwe adatsimikiza kuchuluka kwa chithandizo cha wopempha aliyense. Aliyense wofunsidwa akhoza kusankha mmodzi mwa awiriwo kapena kutsutsa onse. Ndikofunikira kudziwa kuchuluka kwa kutsatsa kulikonse komwe kumakhudza kuchuluka kwa chithandizo kwa ofunsira, ndi kampani iti yomwe imagwira bwino ntchito.

Pogwiritsa ntchito njira zomwe zili pansipa, timawerengera coefficient yolumikizana:

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

Results:

Kuchokera pazotsatira zomwe zapezedwa, zikuwonekeratu kuti mlingo wa chithandizo kwa wopempha 1 unawonjezeka ndi tsiku lililonse la kutsatsa malonda, choncho, coefficient coefficient coefficient imayandikira 1. Pamene kutsatsa kunayambika, wopempha winayo anali ndi chiwerengero chachikulu cha chikhulupiliro, ndipo kwa Masiku a 5 panali njira yabwino. Ndiye mlingo wa chikhulupiriro unachepa ndipo pofika tsiku lakhumi ndi chisanu unatsika pansi pa zizindikiro zoyamba. Zotsatira zotsika zikuwonetsa kuti kukwezedwa kwasokoneza chithandizo. Musaiwale kuti zinthu zina zofananira zomwe sizimaganiziridwa mu mawonekedwe a tabular zitha kukhudzanso zizindikiro.

Kuwunika kwa kutchuka kwazinthu polumikizana ndi mawonedwe amakanema ndi zolembedwanso

Chitsanzo chachitatu. Munthu wotsatsa mavidiyo awo pa kuchititsa makanema pa YouTube amagwiritsa ntchito malo ochezera a pa Intaneti kutsatsa tchanelo. Amawona kuti pali ubale wina pakati pa kuchuluka kwa anthu omwe amatumizidwanso m'malo ochezera a pa Intaneti ndi kuchuluka kwa mawonedwe pa tchanelo. Kodi ndizotheka kulosera zam'tsogolo pogwiritsa ntchito zida zamasamba? Ndikofunikira kuzindikira kufunikira kogwiritsa ntchito mzere wobwerezabwereza kuti muneneretu kuchuluka kwa makanema omwe amawonera kutengera kuchuluka kwa zobwereza. Table yokhala ndi ma values:

Tsopano m'pofunika kudziwa kukhalapo kwa ubale pakati pa 2 zizindikiro malinga ndi chilinganizo pansipa:

0,7;NGATI(CORREL(A3:A8;B3:B8)>0,7;“Ubale Wachindunji Wamphamvu”;“Ubale Wamphamvu”);“Ubale Wofooka kapena wopanda”)' class='formula'>

Ngati coefficient yotsatiridwayo ili yoposa 0,7, ndiye kuti ndi koyenera kugwiritsa ntchito mzere wowongolera. Mu chitsanzo ichi, tikuchita:

Tsopano tikupanga graph:

Timagwiritsa ntchito equation iyi kuti tiwone kuchuluka kwa mawonedwe pa 200, 500 ndi 1000 magawo: =9,2937*D4-206,12. Timapeza zotsatirazi:

ntchito MALONJEZO amakulolani kudziwa chiwerengero cha mawonedwe panthawiyi, ngati panali, mwachitsanzo, mazana awiri ndi makumi asanu obwereza. Timalemba: 0,7;PREDICTION(D7;B3:B8;A3:A8);“Zinthu sizikugwirizana”)' class='formula'>. Timapeza zotsatirazi:

Momwe mungagwiritsire ntchito ntchito ya CORREL mu Excel

Ntchitoyi ili ndi izi:

1. Maselo opanda kanthu samaganiziridwa.
2. Maselo omwe ali ndi Boolean ndi mtundu wa Text samaganiziridwa.
3. Kukana kawiri "-" kumagwiritsidwa ntchito kuwerengera zomveka ngati manambala.
4. Kuchuluka kwa ma cell omwe akufufuzidwa ayenera kufanana, apo ayi uthenga wa #N/A uwonetsedwa.

Kuwunika kwa chiwerengero cha chiwerengero cha coefficient coefficient

Poyesa kufunikira kwa coefficient coefficient, null hypothesis ndi yakuti chizindikirocho chili ndi mtengo wa 0, pamene njira ina ilibe. Njira yotsatirayi imagwiritsidwa ntchito potsimikizira:

Kutsiliza

Kusanthula kwa mgwirizano mu spreadsheet ndi njira yosavuta komanso yodzipangira. Kuti muchite izi, muyenera kudziwa komwe zida zofunika zilipo komanso momwe mungayambitsire kudzera pazokonda pulogalamu.