Ukuhlaziywa kokuxhumana ku-Excel. Isibonelo sokwenza ukuhlaziya kokuhlobana

Ukuhlaziywa kokuhlobana kuyindlela yocwaningo evamile esetshenziselwa ukunquma izinga lokuncika kwevelu yoku-1 ku-2nd. Ispredishithi sinethuluzi elikhethekile elikuvumela ukuthi usebenzise lolu hlobo locwaningo.

Ingqikithi yokuhlaziya ukuhlobana

Kuyadingeka ukunquma ubudlelwano phakathi kwamanani amabili ahlukene. Ngamanye amazwi, iveza ukuthi ukuphi (okuncane / okukhulu) inani lishintsha kuye ngoshintsho lwesibili.

Inhloso yokuhlaziya ukuhlobana

Ukuncika kuyasungulwa lapho ukuhlonza i-coefficient yokuhlobanisa kuqala. Le ndlela ihluke ekuhlaziyeni ukuhlehla, njengoba kunenkomba eyodwa kuphela ebalwa kusetshenziswa ukuhlobana. Isikhawu sishintsha ukusuka ku-+1 kuye ku- -1. Uma kukuhle, khona-ke ukwanda kwenani lokuqala kunomthelela ekwenyukeni kwesi-2. Uma kunegethivu, khona-ke ukukhuphuka kwenani loku-1 kunomthelela ekwehleni kwesi-2. Uma i-coefficient iphezulu, inani elinamandla elilodwa lithinta elesibili.

Kubalulekile! Ku-coefficient engu-0, abukho ubudlelwano phakathi kwamanani.

Ukubalwa kwe-coefficient yokuxhumanisa

Ake sihlaziye ukubala kumasampula amaningana. Isibonelo, kunedatha yethebula, lapho ukusetshenziswa kwemali ekunyusweni kokukhangisa kanye nevolumu yokuthengisa kuchazwa ngezinyanga kumakholomu ahlukene. Ngokusekelwe kuthebula, sizothola izinga lokuncika kwevolumu yokuthengisa emalini esetshenziswe ekukhuthazeni ukukhangisa.

Indlela 1: Ukunquma Ukuhlobana Ngeselekeleli Somsebenzi

I-CORREL – umsebenzi okuvumela ukuthi usebenzise ukuhlaziya kokuhlanganisa. Ifomu elijwayelekile - I-CORREL(i-massiv1;i-massiv2). Imiyalelo enemininingwane:

1. Kuyadingeka ukukhetha iseli lapho kuhlelwe khona ukubonisa umphumela wokubala. Chofoza okuthi “Faka Umsebenzi” otholakala kwesokunxele senkambu yombhalo ukuze ufake ifomula.

2. I-Function Wizard iyavula. Lapha udinga ukuthola I-CORREL, chofoza kukho, bese ucindezela "Kulungile".

3. Iwindi lezimpikiswano liyavuleka. Emugqeni othi “Array1” kufanele ufake izixhumanisi zezikhawu zo-1st wamanani. Kulesi sibonelo, lena yikholomu Yenani Lokuthengisa. Udinga nje ukukhetha wonke amaseli akule kholomu. Ngokufanayo, udinga ukwengeza izixhumanisi zekholomu yesibili emgqeni othi "Array2". Esibonelweni sethu, lena yikholomu Yezindleko Zokukhangisa.

4. Ngemuva kokufaka wonke amabanga, chofoza inkinobho ethi "OK".

I-coefficient iboniswe kuseli eyayikhonjiswe ekuqaleni kwezenzo zethu. Umphumela otholiwe ungu-0,97. Le nkomba ibonisa ukuncika okuphezulu kwevelu yokuqala kweyesibili.

Indlela yesi-2: Bala ukuhlobana usebenzisa i-Analysis ToolPak

Kukhona enye indlela yokunquma ukuhlobana. Lapha kusetshenziswa omunye wemisebenzi etholakala kuphakheji yokuhlaziya. Ngaphambi kokuyisebenzisa, udinga ukuvula ithuluzi. Imiyalelo enemininingwane:

1. Yiya engxenyeni ethi “Ifayela”.

2. Iwindi elisha lizovuleka, lapho udinga uchofoze ingxenye ethi "Izilungiselelo".
3. Chofoza "Izengezo".
4. Sithola isici esithi "Ukuphatha" ngezansi. Lapha udinga ukukhetha "Izengezo ze-Excel" kumenyu yokuqukethwe bese uchofoza "Kulungile".

5. Iwindi lezengezo elikhethekile livuliwe. Beka uphawu lokuhlola eduze kwento ethi "Iphakheji Lokuhlaziya". Sichofoza "OK".
6. Ukwenza kusebenze kube yimpumelelo. Manje ake siye kuDatha. Ibhulokhi ethi "Ukuhlaziya" ivele, lapho udinga ukuchofoza "Ukuhlaziywa Kwedatha".
7. Ewindini elisha elivelayo, khetha into ethi "Ukuxhumana" bese uchofoza "Kulungile".

8. Iwindi lezilungiselelo zokuhlaziya livele esikrinini. Emgqeni othi “Isikhawu sokufaka” kuyadingeka ukuthi ufake ububanzi bawo wonke amakholomu abamba iqhaza ekuhlaziyeni. Kulesi sibonelo, lawa amakholomu “Inani lokuthengisa” kanye “Nezindleko zokukhangisa”. Izilungiselelo zokubonisa okukhiphayo ziqale zisethwe kokuthi Iphepha Lokusebenzela Elisha, okusho ukuthi imiphumela izovezwa eshidini elihlukile. Ngokuzithandela, ungashintsha indawo yokuphuma komphumela. Ngemva kokwenza zonke izilungiselelo, chofoza "OK".

Amaphuzu okugcina asephumile. Umphumela ufana nendlela yokuqala - 0,97.

Incazelo nokubalwa kwe-coefficient yokuhlanganisa okuningi ku-MS Excel

Ukukhomba izinga lokuncika kwamanani amaningana, ama-coefficients amaningi asetshenziswa. Ngokuzayo, imiphumela ifinyezwa kuthebula elihlukile, elibizwa ngokuthi i-matrix yokuhlanganisa.

Umhlahlandlela onemininingwane

1. Esigabeni esithi "Idatha", sithola "Ukuhlaziya" okwaziwayo kakade vimba bese uchofoza "Ukuhlaziywa Kwedatha".

2. Ewindini elivelayo, chofoza into ethi "Ukuxhumana" bese uchofoza "Kulungile".
3. Emgqeni othi "Isikhawu sokufaka" sishayela ngesikhawu amakholomu amathathu noma ngaphezulu ethebula lomthombo. Ibanga lingafakwa ngesandla noma livele likhethe nge-LMB, futhi lizovela ngokuzenzakalelayo kulayini owufunayo. Ku-"Grouping" khetha indlela efanele yokuqoqa. Ku-"Output Parameter" icacisa indawo lapho imiphumela yokuhlobana izoboniswa khona. Sichofoza "OK".

4. Ilungile! I-matrix yokuhlanganisa yakhiwe.

Bhangqa I-Coefficient Yokuxhumanisa ku-Excel

Ake sithole ukuthi singadweba kanjani kahle i-coefficient yokuhambisana ngakunye kusipredishithi se-Excel.

Ukubalwa kwe-coefficient yokuhambisana ngakubili ku-Excel

Isibonelo, unamanani angu-x kanye no-y.

U-X uwukuhluka okuncikile futhi u-y uzimele. Kuyadingeka ukuthola isiqondiso namandla obudlelwano phakathi kwalezi zinkomba. Isinyathelo ngesinyathelo imiyalelo:

1. Ake sithole amanani amaphakathi sisebenzisa umsebenzi INHLIZIYO.

2. Ake sibale ngayinye х и xavg, у и avg usebenzisa i-opharetha «-».

3. Siphindaphinda umehluko obaliwe.

4. Sibala isamba sezinkomba kule kholomu. Inombolo umphumela otholiwe.

5. Bala amadinominetha omehluko х и x-isilinganiso, y и y-omaphakathi. Ukuze wenze lokhu, sizokwenza i-squaring.

6. Ukusebenzisa umsebenzi I-AUTOSUMMA, thola izinkomba kumakholomu aphumayo. Senza ukuphindaphinda. Ukusebenzisa umsebenzi izimpande isikwele umphumela.

7. Sibala i-quotient sisebenzisa amanani edinominetha nenumerator.

8. I-CORREL iwumsebenzi ohlanganisiwe okuvumela ukuthi uvimbele izibalo eziyinkimbinkimbi. Siya ku-"Function Wizard", khetha i-CORREL bese ucacisa izinkomba zezinkomba х и у. Sakha igrafu ebonisa amanani atholiwe.

I-Matrix ye-Pairwise Correlation Coefficients ku-Excel

Ake sihlaziye ukuthi singabala kanjani ama-coefficients wamatrices abhanqiwe. Isibonelo, kukhona i-matrix yeziguquguqukayo ezine.

Isinyathelo ngesinyathelo imiyalelo:

1. Siya kokuthi "Ukuhlaziywa Kwedatha", etholakala kubhulokhi elithi "Ukuhlaziya" lethebhu ethi "Idatha". Khetha Ukuhlobana ohlwini oluvelayo.
2. Setha zonke izilungiselelo ezidingekayo. “Isikhawu sokufaka” – isikhawu sawo wonke amakholomu amane. "Isikhawu sokukhipha" - indawo lapho sifuna ukubonisa khona amanani. Sichofoza inkinobho ethi "OK".
3. I-matrix yokuhlanganisa yakhiwe endaweni ekhethiwe. Ukuphambana ngakunye komugqa nekholomu kuyi-coefficient yokuhlobana. Inombolo 1 iboniswa uma izixhumanisi zifana.

Umsebenzi we-CORREL wokunquma ubudlelwano nokuhlobana ku-Excel

I-CORREL – umsebenzi osetshenziselwa ukubala i-coefficient yokuhlobanisa phakathi kwamalungu afanayo angu-2. Ake sibheke izibonelo ezine zawo wonke amakhono alo msebenzi.

Izibonelo zokusebenzisa umsebenzi we-CORREL ku-Excel

Isibonelo sokuqala. Kunepuleti elinolwazi mayelana nesilinganiso samaholo abasebenzi benkampani phakathi neminyaka eyishumi nanye kanye nezinga lokushintshisana lika-$. Kuyadingeka ukukhomba ubudlelwano phakathi kwalezi zibalo ezimbili. Ithebula libukeka kanje:

I-algorithm yokubala ibonakala kanje:

Isikolo esibonisiwe siseduze no-1. Umphumela:

Ukunqunywa kwe-coefficient yokuxhumanisa komthelela wezenzo kumphumela

Isibonelo sesibili. Amathenda amabili athintane nama-ejensi amabili ahlukene ukuze bathole usizo ngephromoshini yezinsuku eziyishumi nanhlanu. Nsuku zonke kwenziwa inhlolovo yomphakathi, eyanquma izinga lokusekela komfakisicelo ngamunye. Noma yimuphi umuntu obuzwayo angakhetha oyedwa kwababili abafake izicelo noma aphikise bonke. Kudingeka ukunquma ukuthi iphromoshini ngayinye yokukhangisa ibe nomthelela ongakanani ezingeni lokusekelwa kwabafake izicelo, iyiphi inkampani esebenza kahle kakhulu.

Sisebenzisa amafomula angezansi, sibala i-coefficient yokuxhumanisa:

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

Ezenye:

Kusukela emiphumeleni etholiwe, kuyacaca ukuthi izinga lokusekela lomfakisicelo wokuqala likhuphuka ngosuku ngalunye lokukhuthazwa kokukhangisa, ngakho-ke, i-coefficient yokuhlanganisa isondela 1. Lapho ukukhangisa kwethulwa, omunye umfakisicelo wayenenombolo enkulu yokwethenjwa, Izinsuku ezi-1 bekukhona ukuthambekela okuhle. Khona-ke izinga lokuthembela liyehla futhi ngosuku lweshumi nanhlanu lehla ngaphansi kwezinkomba zokuqala. Izikolo eziphansi ziphakamisa ukuthi ukuphromotha kube nomthelela omubi ekusekelweni. Ungakhohlwa ukuthi ezinye izici ezihambisanayo ezingacatshangwa ngendlela yethebula nazo zingathinta izinkomba.

Ukuhlaziywa kokuduma kokuqukethwe ngokuhlotshaniswa kokubukwa kwevidiyo nokuthunyelwe kabusha

Isibonelo sesithathu. Umuntu wokuphromotha amavidiyo akhe ekusingatheni amavidiyo e-YouTube usebenzisa izinkundla zokuxhumana ukukhangisa isiteshi. Uyaqaphela ukuthi kunobudlelwano obuthile phakathi kwenani lokuphinda okuthunyelwe ezinkundleni zokuxhumana kanye nenani lokubukwa esiteshini. Ingabe kungenzeka ukubikezela ukusebenza kwesikhathi esizayo usebenzisa amathuluzi espredishithi? Kuyadingeka ukukhomba ukufaneleka kokusebenzisa isibalo sokuhlehla komugqa ukuze ubikezele inani lokubukwa kwevidiyo kuye ngenani lokuphinda kuthunyelwe. Ithebula elinamanani:

Manje kuyadingeka ukunquma ubukhona bobudlelwano phakathi kwezinkomba ezi-2 ngokuya ngefomula engezansi:

0,7;UMA(I-CORREL(A3:A8;B3:B8)>0,7;“Ubudlelwano obuqinile obuqondile”;“Ubudlelwano obuqinile obuphambene”);“Ubudlelwano obubuthakathaka noma abukho”)' class='ifomula'>

Uma i-coefficient ewumphumela ingaphezu kuka-0,7, khona-ke kufaneleka kakhulu ukusebenzisa umsebenzi wokuhlehla ngomugqa. Kulesi sibonelo, senza:

Manje sakha igrafu:

Sisebenzisa le zibalo ukuze sinqume inani lokubukwa kumasheya angu-200, 500 kanye no-1000: =9,2937*D4-206,12. Sithola imiphumela elandelayo:

Umsebenzi ISIBIKEZELO ikuvumela ukuthi unqume inani lokubuka okwamanje, uma ngabe kukhona, isibonelo, ukuphinda kuthunyelwe okungamakhulu amabili namashumi amahlanu. Sifaka isicelo: 0,7;PREDICTION(D7;B3:B8;A3:A8);“Amanani awahlobene”)' class='formula'>. Sithola imiphumela elandelayo:

Izici zokusebenzisa umsebenzi we-CORREL ku-Excel

Lo msebenzi unezici ezilandelayo:

1. Amaseli angenalutho awanakwa.
2. Amaseli aqukethe ulwazi lwe-Boolean kanye nohlobo lombhalo awanakwa.
3. Ukuphika kabili okuthi “-” kusetshenziselwa ukulandisa amanani anengqondo ngendlela yezinombolo.
4. Inani lamaseli kumalungu afanayo afundiwe kufanele afane, ngaphandle kwalokho umlayezo othi #N/A uzoboniswa.

Ukuhlolwa kokubaluleka kwezibalo ze-coefficient yokuhlobana

Lapho kuhlolwa ukubaluleka kwe-coefficient yokuhlanganisa, i-null hypothesis iwukuthi inkomba inenani elingu-0, kuyilapho enye ingenayo. Ifomula elandelayo isetshenziselwa ukuqinisekiswa:

Isiphetho

Ukuhlaziywa kokuhlobana kusipredishithi kuyinqubo elula futhi ezenzakalelayo. Ukuze ukwenze, udinga kuphela ukwazi ukuthi amathuluzi adingekayo atholakala kuphi nokuthi ungawenza kanjani asebenze ngezilungiselelo zohlelo.