Pagsusuri ng ugnayan sa Excel. Isang halimbawa ng pagsasagawa ng pagsusuri ng ugnayan

Ang pagsusuri ng ugnayan ay isang karaniwang paraan ng pananaliksik na ginagamit upang matukoy ang antas ng pag-asa ng 1st value sa ika-2. Ang spreadsheet ay may espesyal na tool na nagbibigay-daan sa iyong ipatupad ang ganitong uri ng pananaliksik.

Ang kakanyahan ng pagsusuri ng ugnayan

Ito ay kinakailangan upang matukoy ang relasyon sa pagitan ng dalawang magkaibang dami. Sa madaling salita, ipinapakita nito kung saang direksyon (mas maliit / mas malaki) nagbabago ang halaga depende sa mga pagbabago sa pangalawa.

Layunin ng pagsusuri ng ugnayan

Ang pag-asa ay itinatag kapag nagsimula ang pagkakakilanlan ng koepisyent ng ugnayan. Ang pamamaraang ito ay naiiba sa pagsusuri ng regression, dahil mayroon lamang isang tagapagpahiwatig na kinakalkula gamit ang ugnayan. Nagbabago ang pagitan mula +1 hanggang -1. Kung ito ay positibo, kung gayon ang pagtaas sa unang halaga ay nag-aambag sa pagtaas sa ika-2. Kung negatibo, ang pagtaas sa 1st value ay nag-aambag sa pagbaba sa ika-2. Ang mas mataas na koepisyent, ang mas malakas na isang halaga ay nakakaapekto sa pangalawa.

Mahalaga! Sa 0th coefficient, walang ugnayan sa pagitan ng mga dami.

Pagkalkula ng koepisyent ng ugnayan

Suriin natin ang pagkalkula sa ilang mga sample. Halimbawa, mayroong tabular na data, kung saan ang paggastos sa pag-promote ng advertising at dami ng benta ay inilalarawan ng mga buwan sa magkahiwalay na mga column. Batay sa talahanayan, malalaman natin ang antas ng pag-asa ng dami ng mga benta sa perang ginugol sa pag-promote ng advertising.

Paraan 1: Pagtukoy ng Kaugnayan sa Pamamagitan ng Function Wizard

CORREL – isang function na nagbibigay-daan sa iyong magpatupad ng pagsusuri ng ugnayan. Pangkalahatang anyo - CORREL(massiv1;massiv2). Mga detalyadong tagubilin:

1. Ito ay kinakailangan upang piliin ang cell kung saan ito ay binalak upang ipakita ang resulta ng pagkalkula. I-click ang "Insert Function" na matatagpuan sa kaliwa ng field ng teksto upang ipasok ang formula.

2. Bubukas ang Function Wizard. Dito kailangan mong hanapin CORREL, i-click ito, pagkatapos ay sa “OK”.

3. Bubukas ang window ng mga argumento. Sa linyang "Array1" dapat mong ipasok ang mga coordinate ng mga pagitan ng 1st ng mga halaga. Sa halimbawang ito, ito ang column ng Sales Value. Kailangan mo lang piliin ang lahat ng mga cell na nasa column na ito. Katulad nito, kailangan mong idagdag ang mga coordinate ng pangalawang hanay sa linya ng "Array2". Sa aming halimbawa, ito ang column na Mga Gastos sa Advertising.

4. Matapos ipasok ang lahat ng mga saklaw, mag-click sa pindutang "OK".

Ang koepisyent ay ipinakita sa cell na ipinahiwatig sa simula ng aming mga aksyon. Ang resultang nakuha ay 0,97. Ang tagapagpahiwatig na ito ay sumasalamin sa mataas na pag-asa ng unang halaga sa pangalawa.

Paraan 2: Kalkulahin ang Correlation Gamit ang Analysis ToolPak

May isa pang paraan para sa pagtukoy ng ugnayan. Dito ginagamit ang isa sa mga function na matatagpuan sa package ng pagsusuri. Bago gamitin ito, kailangan mong i-activate ang tool. Mga detalyadong tagubilin:

1. Pumunta sa seksyong "File".

2. Magbubukas ang isang bagong window, kung saan kailangan mong mag-click sa seksyong "Mga Setting".
3. Mag-click sa "Mga Add-on".
4. Nakita namin ang elementong "Pamamahala" sa ibaba. Dito kailangan mong piliin ang "Excel Add-in" mula sa menu ng konteksto at i-click ang "OK".

5. Nagbukas ang isang espesyal na window ng add-on. Maglagay ng checkmark sa tabi ng elemento ng "Analysis Package". I-click namin ang "OK".
6. Naging matagumpay ang pag-activate. Ngayon pumunta tayo sa Data. Lumitaw ang block na "Analysis", kung saan kailangan mong i-click ang "Data Analysis".
7. Sa bagong window na lilitaw, piliin ang elemento ng "Correlation" at mag-click sa "OK".

8. Ang window ng mga setting ng pagsusuri ay lumitaw sa screen. Sa linyang "Input interval" kinakailangan na ipasok ang saklaw ng ganap na lahat ng mga hanay na nakikilahok sa pagsusuri. Sa halimbawang ito, ito ang mga column na "Halaga ng benta" at "Mga gastos sa advertising." Ang mga setting ng pagpapakita ng output ay unang nakatakda sa Bagong Worksheet, na nangangahulugan na ang mga resulta ay ipapakita sa ibang sheet. Opsyonal, maaari mong baguhin ang lokasyon ng output ng resulta. Matapos gawin ang lahat ng mga setting, mag-click sa "OK".

Ang huling mga marka ay lumabas. Ang resulta ay pareho sa unang paraan - 0,97.

Kahulugan at pagkalkula ng maramihang koepisyent ng ugnayan sa MS Excel

Upang matukoy ang antas ng pag-asa ng ilang mga dami, maraming mga koepisyent ang ginagamit. Sa hinaharap, ang mga resulta ay ibubuod sa isang hiwalay na talahanayan, na tinatawag na correlation matrix.

Detalyadong gabay:

1. Sa seksyong "Data", nakita namin ang kilala nang bloke ng "Pagsusuri" at i-click ang "Pagsusuri ng Data".

2. Sa window na lilitaw, mag-click sa elemento ng "Correlation" at mag-click sa "OK".
3. Sa linyang "Input interval" humimok kami sa pagitan para sa tatlo o higit pang mga column ng source table. Ang hanay ay maaaring ipasok nang manu-mano o piliin lamang ito gamit ang LMB, at awtomatiko itong lilitaw sa nais na linya. Sa "Pagpapangkat" piliin ang naaangkop na paraan ng pagpapangkat. Sa "Output Parameter" ay tumutukoy sa lokasyon kung saan ipapakita ang mga resulta ng ugnayan. I-click namin ang "OK".

4. handa na! Ang correlation matrix ay binuo.

Ipares ang Correlation Coefficient sa Excel

Alamin natin kung paano iguguhit nang tama ang koepisyent ng ugnayan ng pares sa isang spreadsheet ng Excel.

Pagkalkula ng koepisyent ng ugnayan ng pares sa Excel

Halimbawa, mayroon kang mga halaga ng x at y.

Ang X ay ang dependent variable at ang y ay ang independent. Ito ay kinakailangan upang mahanap ang direksyon at lakas ng relasyon sa pagitan ng mga tagapagpahiwatig na ito. Hakbang-hakbang na pagtuturo:

1. Hanapin natin ang mga average na halaga gamit ang function PUSO.

2. Kalkulahin natin ang bawat isa х и xavg, у и avg gamit ang operator na «-».

3. Pinaparami namin ang mga kinakalkula na pagkakaiba.

4. Kinakalkula namin ang kabuuan ng mga tagapagpahiwatig sa hanay na ito. Ang numerator ay ang resulta na natagpuan.

5. Kalkulahin ang mga denominador ng pagkakaiba х и x-average, y и y-medium. Upang gawin ito, gagawin namin ang pag-squaring.

6. Gamit ang function AUTOSUMMA, hanapin ang mga indicator sa mga resultang column. Ginagawa namin ang multiplication. Gamit ang function Ugat parisukat ang resulta.

7. Kinakalkula namin ang quotient gamit ang mga halaga ng denominator at numerator.

8. Ang CORREL ay isang pinagsamang function na nagbibigay-daan sa iyo upang maiwasan ang mga kumplikadong kalkulasyon. Pumunta kami sa "Function Wizard", piliin ang CORREL at tukuyin ang mga arrays ng mga indicator х и у. Bumubuo kami ng isang graph na nagpapakita ng mga nakuhang halaga.

Matrix ng Pairwise Correlation Coefficients sa Excel

Suriin natin kung paano kalkulahin ang mga koepisyent ng mga ipinares na matrice. Halimbawa, mayroong isang matrix ng apat na variable.

Hakbang-hakbang na tagubilin:

1. Pumunta kami sa "Pagsusuri ng Data", na matatagpuan sa bloke ng "Pagsusuri" ng tab na "Data". Piliin ang Kaugnayan mula sa listahang lalabas.
2. Itinakda namin ang lahat ng kinakailangang mga setting. "Input interval" - ang pagitan ng lahat ng apat na column. "Agwat ng output" - ang lugar kung saan gusto naming ipakita ang mga kabuuan. Nag-click kami sa pindutan ng "OK".
3. Isang correlation matrix ang itinayo sa napiling lugar. Ang bawat intersection ng isang row at column ay isang correlation coefficient. Ang numero 1 ay ipinapakita kapag tumugma ang mga coordinate.

CORREL function upang matukoy ang relasyon at ugnayan sa Excel

CORREL – isang function na ginagamit upang kalkulahin ang koepisyent ng ugnayan sa pagitan ng 2 array. Tingnan natin ang apat na halimbawa ng lahat ng kakayahan ng function na ito.

Mga halimbawa ng paggamit ng CORREL function sa Excel

Unang halimbawa. Mayroong isang plato na may impormasyon tungkol sa karaniwang suweldo ng mga empleyado ng kumpanya sa loob ng labing-isang taon at ang halaga ng palitan na $. Kinakailangang tukuyin ang kaugnayan sa pagitan ng dalawang dami na ito. Ang talahanayan ay ganito ang hitsura:

Ang algorithm ng pagkalkula ay ganito ang hitsura:

Ang ipinapakitang marka ay malapit sa 1. Resulta:

Pagpapasiya ng koepisyent ng ugnayan ng epekto ng mga aksyon sa resulta

Pangalawang halimbawa. Dalawang bidder ang lumapit sa dalawang magkaibang ahensya para sa tulong sa isang labinlimang araw na promosyon. Araw-araw ay isinasagawa ang isang social poll, na tumutukoy sa antas ng suporta para sa bawat aplikante. Ang sinumang kinapanayam ay maaaring pumili ng isa sa dalawang aplikante o tutulan ang lahat. Kinakailangang matukoy kung gaano nakaimpluwensya ang bawat promosyon sa advertising sa antas ng suporta para sa mga aplikante, kung aling kumpanya ang mas mahusay.

Gamit ang mga formula sa ibaba, kinakalkula namin ang koepisyent ng ugnayan:

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

Mga Resulta:

Mula sa mga resulta na nakuha, nagiging malinaw na ang antas ng suporta para sa unang aplikante ay tumaas sa bawat araw ng pag-promote ng advertising, samakatuwid, ang koepisyent ng ugnayan ay lumalapit sa 1. Noong inilunsad ang advertising, ang ibang aplikante ay may malaking bilang ng tiwala, at para sa 1 araw nagkaroon ng positibong kalakaran. Pagkatapos ay bumaba ang antas ng tiwala at sa ikalabinlimang araw ay bumaba ito sa ibaba ng mga unang tagapagpahiwatig. Iminumungkahi ng mababang marka na ang pag-promote ay may negatibong epekto sa suporta. Huwag kalimutan na ang iba pang magkakatulad na mga kadahilanan na hindi isinasaalang-alang sa tabular form ay maaari ring makaapekto sa mga tagapagpahiwatig.

Pagsusuri ng kasikatan ng nilalaman sa pamamagitan ng ugnayan ng mga panonood ng video at mga repost

Pangatlong halimbawa. Ang isang tao upang i-promote ang kanilang sariling mga video sa YouTube video hosting ay gumagamit ng mga social network upang i-advertise ang channel. Napansin niya na may ilang kaugnayan sa pagitan ng bilang ng mga repost sa mga social network at ang bilang ng mga view sa channel. Posible bang mahulaan ang pagganap sa hinaharap gamit ang mga tool sa spreadsheet? Kinakailangang tukuyin ang pagiging makatwiran ng paglalapat ng linear regression equation upang mahulaan ang bilang ng mga panonood ng video depende sa bilang ng mga repost. Talahanayan na may mga halaga:

Ngayon ay kinakailangan upang matukoy ang pagkakaroon ng isang relasyon sa pagitan ng 2 mga tagapagpahiwatig ayon sa pormula sa ibaba:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Malakas na direktang relasyon”;”Malakas na kabaligtaran na relasyon”);”Mahina o walang relasyon”)' class='formula'>

Kung ang resultang koepisyent ay mas mataas sa 0,7, mas angkop na gamitin ang linear regression function. Sa halimbawang ito, ginagawa namin:

Ngayon ay bumubuo kami ng isang graph:

Inilapat namin ang equation na ito upang matukoy ang bilang ng mga view sa 200, 500 at 1000 na pagbabahagi: =9,2937*D4-206,12. Nakukuha namin ang mga sumusunod na resulta:

tungkulin FORECAST ay nagbibigay-daan sa iyo upang matukoy ang bilang ng mga view sa sandaling ito, kung mayroong, halimbawa, dalawang daan at limampung repost. Nag-a-apply kami: 0,7;PREDICTION(D7;B3:B8;A3:A8);”Ang mga value ay hindi nauugnay”)' class='formula'>. Nakukuha namin ang mga sumusunod na resulta:

Mga tampok ng paggamit ng CORREL function sa Excel

Ang function na ito ay may mga sumusunod na tampok:

1. Ang mga walang laman na cell ay hindi isinasaalang-alang.
2. Ang mga cell na naglalaman ng impormasyon sa uri ng Boolean at Text ay hindi isinasaalang-alang.
3. Ang dobleng negation na "-" ay ginagamit upang isaalang-alang ang mga lohikal na halaga sa anyo ng mga numero.
4. Dapat tumugma ang bilang ng mga cell sa pinag-aralan na array, kung hindi ay ipapakita ang #N/A na mensahe.

Pagtatasa ng istatistikal na kahalagahan ng koepisyent ng ugnayan

Kapag sinusuri ang kahalagahan ng isang koepisyent ng ugnayan, ang null hypothesis ay ang tagapagpahiwatig ay may halaga na 0, habang ang kahalili ay wala. Ang sumusunod na formula ay ginagamit para sa pagpapatunay:

Konklusyon

Ang pagsusuri ng ugnayan sa isang spreadsheet ay isang simple at automated na proseso. Upang maisagawa ito, kailangan mo lamang malaman kung saan matatagpuan ang mga kinakailangang tool at kung paano i-activate ang mga ito sa pamamagitan ng mga setting ng programa.