Binciken daidaitawa a cikin Excel. Misali na yin nazarin alaƙa

Binciken daidaitawa hanya ce ta bincike ta gama gari da ake amfani da ita don tantance matakin dogaro na ƙimar 1st akan na 2nd. Rubutun yana da kayan aiki na musamman wanda ke ba ka damar aiwatar da irin wannan bincike.

Ma'anar nazarin daidaituwa

Wajibi ne a ƙayyade dangantakar tsakanin nau'i biyu daban-daban. A wasu kalmomi, yana bayyana a cikin wane shugabanci (ƙarami / girma) ƙimar ta canza dangane da canje-canje a cikin na biyu.

Manufar nazarin daidaituwa

Dogaro yana kafa lokacin da aka fara gano ƙimar haɗin kai. Wannan hanyar ta bambanta da bincike na koma baya, saboda akwai alama ɗaya kawai da aka ƙididdige ta amfani da daidaituwa. Tazarar tana canzawa daga +1 zuwa -1. Idan yana da kyau, to, karuwa a cikin ƙimar farko yana taimakawa wajen karuwa a cikin 2nd. Idan mummunan, to, karuwa a cikin ƙimar 1st yana taimakawa wajen ragewa a cikin 2nd. Mafi girman ƙima, ƙimar ɗaya mai ƙarfi tana shafar na biyu.

Muhimmin! A 0th coefficient, babu dangantaka tsakanin yawa.

Ƙididdiga na haɗin kai

Bari mu bincika lissafin akan samfurori da yawa. Misali, akwai bayanan tambura, inda aka bayyana kashe kuɗi akan tallan talla da ƙarar tallace-tallace ta watanni a cikin ginshiƙai daban-daban. Dangane da tebur, za mu gano matakin dogaro da ƙarar tallace-tallace akan kuɗin da aka kashe akan tallan talla.

Hanyar 1: Ƙayyade Daidaitawa Ta Mayen Aiki

CORREL - aikin da ke ba ku damar aiwatar da nazarin alaƙa. Gabaɗaya form - CORREL (massiv1; massiv2). Cikakken umarni:

1. Wajibi ne don zaɓar tantanin halitta wanda aka shirya don nuna sakamakon lissafin. Danna "Saka Aiki" dake gefen hagu na filin rubutu don shigar da dabara.

2. Mayen Aiki yana buɗewa. Anan kuna buƙatar nemo CORREL, danna shi, sannan a kan "Ok".

3. Tagan muhawara yana buɗewa. A cikin layin "Array1" dole ne ku shigar da daidaitawar tazara na 1st na ƙimar. A cikin wannan misalin, wannan shine ginshiƙin Ƙimar Talla. Kuna buƙatar kawai zaɓi duk sel waɗanda ke cikin wannan ginshiƙi. Hakazalika, kuna buƙatar ƙara haɗin haɗin shafi na biyu zuwa layin "Array2". A cikin misalinmu, wannan shine ginshiƙin Kudin Talla.

4. Bayan shigar da duk jeri, danna kan "Ok" button.

An nuna ƙididdiga a cikin tantanin halitta da aka nuna a farkon ayyukanmu. Sakamakon da aka samu shine 0,97. Wannan alamar tana nuna babban dogaro na ƙimar farko akan na biyu.

Hanyar 2: Ƙididdiga Daidaitawa Ta Amfani da Kayan Aikin Bincike

Akwai wata hanya don tantance alaƙa. Anan ana amfani da ɗayan ayyukan da aka samo a cikin kunshin bincike. Kafin amfani da shi, kuna buƙatar kunna kayan aiki. Cikakken umarni:

1. Je zuwa sashin "Fayil".

2. Wani sabon taga zai buɗe, wanda a cikinsa kake buƙatar danna kan sashin "Settings".
3. Danna "Add-ons".
4. Mun sami kashi "Management" a kasa. Anan kuna buƙatar zaɓar "Excel Add-ins" daga menu na mahallin kuma danna "Ok".

5. An buɗe taga add-ons na musamman. Sanya alamar bincike kusa da sashin "Kundin Nazari". Mun danna "Ok".
6. Kunnawa yayi nasara. Yanzu bari mu je Data. Toshe "Analysis" ya bayyana, a cikin abin da kuke buƙatar danna "Binciken Bayanai".
7. A cikin sabon taga da ya bayyana, zaɓi abin "Correlation" kuma danna "Ok".

8. Tagan saitunan bincike ya bayyana akan allon. A cikin layin "Input Interval" wajibi ne a shigar da kewayon dukkan ginshiƙai masu shiga cikin bincike. A cikin wannan misali, waɗannan su ne ginshiƙan "Ƙimar tallace-tallace" da "Kudin Talla". Saitunan nunin fitarwa an fara saita su zuwa Sabon Aiki, wanda ke nufin za a nuna sakamakon a wata takarda daban. Optionally, za ka iya canza wurin fitarwa na sakamakon. Bayan yin duk saitunan, danna kan "Ok".

Maki na ƙarshe sun fita. Sakamakon daidai yake a cikin hanyar farko - 0,97.

Ma'anar da ƙididdige ƙididdiga masu yawa a cikin MS Excel

Don gano matakin dogaro na adadi da yawa, ana amfani da ƙididdiga masu yawa. A nan gaba, ana taƙaita sakamakon a cikin wani tebur daban, wanda ake kira matrix correlation.

Cikakken jagora:

1. A cikin sashin "Bayanai", mun sami sanannen toshe "Analysis" da aka sani kuma danna "Binciken Bayanai".

2. A cikin taga da ya bayyana, danna maɓallin "Correlation" kuma danna "Ok".
3. A cikin layin "Input Interval" muna tuƙi a cikin tazara don ginshiƙai uku ko fiye na teburin tushen. Za a iya shigar da kewayon da hannu ko kuma kawai zaɓi shi tare da LMB, kuma zai bayyana ta atomatik a cikin layin da ake so. A cikin "Rukunin" zaɓi hanyar haɗawa da ta dace. A cikin "Tsarin fitarwa" yana ƙayyade wurin da za a nuna sakamakon daidaitawa. Mun danna "Ok".

4. Shirya! An gina matrix ɗin daidaitawa.

Biyu Correlation Coefficient a cikin Excel

Bari mu gano yadda za a zana daidaitattun daidaituwa guda biyu a cikin ma'auni na Excel.

Ƙididdiga na haɗin haɗin gwiwa guda biyu a cikin Excel

Misali, kuna da ƙimar x da y.

X shine madaidaicin dogara kuma y shine mai zaman kansa. Wajibi ne a nemo alkibla da karfin alakar da ke tsakanin wadannan alamomin. Umurni na mataki-mataki:

1. Bari mu nemo matsakaicin ƙimar ta amfani da aikin ZUCIYA.

2. Bari mu lissafta kowanne х и xavg, у и AVG ta amfani da ma'aikacin «-».

3. Muna ninka bambance-bambancen lissafi.

4. Muna ƙididdige jimlar masu nuni a cikin wannan shafi. Mai ƙididdigewa shine sakamakon da aka samo.

5. Yi ƙididdige maƙasudin bambancin х и x-matsakaici, y и y-matsakaici. Don yin wannan, za mu yi squaring.

6. Amfani da aikin AUTOSUMMA, nemo masu nuna alama a cikin ginshiƙan da aka samu. Muna yin ninkawa. Amfani da aikin Akidar daidaita sakamakon.

7. Muna lissafin ƙididdigewa ta amfani da ƙimar ƙima da ƙididdigewa.

8. CORREL wani haɗin gwiwar aiki ne wanda ke ba ku damar hana ƙididdiga masu rikitarwa. Za mu je zuwa "Aikin Wizard", zaɓi CORREL kuma saka arrays na masu nuna alama. х и у. Muna gina jadawali wanda ke nuna ƙimar da aka samu.

Matrix na Haɗin Haɗin Haɗin Biyu a cikin Excel

Bari mu yi nazarin yadda ake ƙididdige ƙididdige ƙididdiga na matrices guda biyu. Misali, akwai matrix na masu canji huɗu.

Umurni na mataki-mataki:

1. Za mu je zuwa "Data Analysis", located a cikin "Analysis" block na "Data" tab. Zaɓi Daidaitawa daga lissafin da ya bayyana.
2. Mun saita duk saitunan da ake bukata. "Tazarar shigarwa" - tazarar duk ginshiƙai huɗu. "Tazarar fitarwa" - wurin da muke son nuna jimlar. Muna danna maɓallin "Ok".
3. An gina matrix daidaitawa a wurin da aka zaɓa. Kowace tsaka-tsaki na jere da ginshiƙi madaidaicin daidaituwa ne. Ana nuna lamba 1 lokacin da masu haɗin gwiwa suka dace.

Ayyukan CORREL don ƙayyade alaƙa da alaƙa a cikin Excel

CORREL – aikin da aka yi amfani da shi don ƙididdige ƙididdige ƙididdiga tsakanin tsararraki 2. Bari mu kalli misalan guda huɗu na dukkan iyawar wannan aikin.

Misalai na amfani da aikin CORREL a cikin Excel

Misali na farko. Akwai faranti mai bayani game da matsakaicin albashin ma'aikatan kamfanin na tsawon shekaru goma sha daya da kuma canjin dala. Wajibi ne a gano alakar da ke tsakanin wadannan adadi guda biyu. Teburin yayi kama da haka:

Algorithm na lissafin yana kama da haka:

Makin da aka nuna yana kusa da 1. Sakamako:

Ƙaddamar da haɗin kai na tasirin ayyuka akan sakamakon

Misali na biyu. Masu neman takara biyu sun tunkari hukumomi biyu daban-daban don neman taimako tare da karin girma na kwanaki goma sha biyar. Kowace rana an gudanar da zaben jin ra'ayin jama'a, wanda ya ƙayyade matakin tallafi ga kowane mai nema. Duk wanda aka yi hira da shi zai iya zaɓar ɗaya daga cikin masu neman biyu ko kuma ya yi adawa da duka. Wajibi ne a ƙayyade nawa kowane tallan talla ya rinjayi matakin tallafi ga masu nema, wanda kamfani ya fi dacewa.

Amfani da dabarar da ke ƙasa, muna ƙididdige ƙididdige ƙididdiga:

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

results:

Daga sakamakon da aka samu, ya bayyana a fili cewa matakin goyon baya ga mai nema na 1 ya karu tare da kowace rana na tallan tallace-tallace, sabili da haka, hanyoyin haɗin gwiwar haɗin gwiwar 1. Lokacin da aka ƙaddamar da talla, ɗayan mai nema yana da babban adadin amana, kuma don Kwanaki 5 an sami kyakkyawan yanayi. Sa'an nan darajar amana ta ragu kuma a rana ta goma sha biyar ta ragu a ƙasa da alamun farko. Ƙananan maki suna nuna cewa haɓakawa ya yi mummunan tasiri ga tallafi. Kar a manta cewa sauran abubuwan haɗin gwiwa waɗanda ba a yi la'akari da su a cikin tambura suma na iya shafar masu nuni.

Binciken shaharar abun ciki ta hanyar daidaita ra'ayoyin bidiyo da sake bugawa

Misali na uku. Mutumin da zai tallata nasa bidiyon akan tallan bidiyo na YouTube yana amfani da cibiyoyin sadarwar jama'a don tallata tashar. Ya lura cewa akwai wasu alaƙa tsakanin adadin sake aikawa a cikin cibiyoyin sadarwar jama'a da yawan ra'ayoyi akan tashar. Shin yana yiwuwa a iya hasashen aikin gaba ta amfani da kayan aikin maƙunsar bayanai? Wajibi ne a gano ma'anar yin amfani da ma'auni na regression na layi don tsinkayar adadin ra'ayoyin bidiyo dangane da adadin sakewa. Teburi mai ƙima:

Yanzu ya zama dole don ƙayyade kasancewar dangantaka tsakanin alamun 2 bisa ga dabarar da ke ƙasa:

0,7;IF

Idan sakamakon sakamakon ya fi 0,7, to, ya fi dacewa don amfani da aikin koma baya na layi. A cikin wannan misali, muna yin:

Yanzu muna gina jadawali:

Muna amfani da wannan ma'auni don tantance adadin ra'ayoyi a hannun jari 200, 500 da 1000: =9,2937*D4-206,12. Muna samun sakamako kamar haka:

aiki FASADI yana ba ka damar ƙayyade adadin ra'ayoyi a halin yanzu, idan akwai, misali, ɗari biyu da hamsin reposts. Muna nema: 0,7; tsinkaya (D7; B3: B8; A3: A8); "Dabi'u ba su da alaƙa")'class='formula'>. Muna samun sakamako kamar haka:

Fasalolin amfani da aikin CORREL a cikin Excel

Wannan aikin yana da fasali masu zuwa:

1. Ba a la'akari da sel marasa komai.
2. Kwayoyin da ke ɗauke da bayanan nau'in Boolean da Rubutu ba a la'akari da su ba.
3. Ana amfani da ƙididdigewa sau biyu “-” don yin lissafin ƙimar ma'ana ta hanyar lambobi.
4. Dole ne adadin ƙwayoyin sel a cikin tsararrun da aka yi nazari su dace, in ba haka ba za a nuna saƙon #N/A.

Ƙimar mahimmancin ƙididdiga na haɗin haɗin kai

Lokacin gwada mahimmancin haɗin haɗin kai, hasashe maras kyau shine cewa mai nuna alama yana da ƙimar 0, yayin da madadin baya. Ana amfani da dabara mai zuwa don tabbatarwa:

Kammalawa

Binciken daidaitawa a cikin maƙunsar bayanai tsari ne mai sauƙi kuma mai sarrafa kansa. Don yin shi, kawai kuna buƙatar sanin inda kayan aikin da ake buƙata suke da kuma yadda ake kunna su ta hanyar saitunan shirin.