Kinakalkula ang koepisyent ng ugnayan sa excel. Isang halimbawa ng paghahanap ng koepisyent ng ugnayan

Koepisyent ng ugnayan (o linear coefficient ugnayan) ay tinutukoy bilang "r" (sa mga bihirang kaso bilang "ρ") at nagpapakilala linear na ugnayan(iyon ay, isang relasyon na ibinibigay ng ilang halaga at direksyon) ng dalawa o higit pang mga variable. Ang halaga ng koepisyent ay nasa pagitan ng -1 at +1, ibig sabihin, ang ugnayan ay maaaring parehong positibo at negatibo. Kung ang koepisyent ng ugnayan ay -1, mayroong perpektong negatibong ugnayan; kung ang koepisyent ng ugnayan ay +1, mayroong perpektong positibong ugnayan. Sa ibang mga kaso, mayroong positibong ugnayan, negatibong ugnayan, o walang ugnayan sa pagitan ng dalawang variable. Maaaring manu-manong kalkulahin ang koepisyent ng ugnayan, gamit ang mga libreng online na calculator, o gamit ang isang mahusay na graphing calculator.

Mga hakbang

Manu-manong pagkalkula ng koepisyent ng ugnayan

Mangolekta ng data. Bago mo simulan ang pagkalkula ng koepisyent ng ugnayan, pag-aralan ang ibinigay na pares ng mga numero. Mas mainam na isulat ang mga ito sa isang talahanayan na maaaring ilagay nang patayo o pahalang. Lagyan ng label ang bawat row o column bilang "x" at "y".

• Halimbawa, ang apat na pares ng mga halaga (mga numero) ng mga variable na "x" at "y" ay ibinigay. Maaari kang lumikha ng sumusunod na talahanayan:
  • x || y
  • 1 || 1
  • 2 || 3
  • 4 || 5
  • 5 || 7

1. Kalkulahin ang arithmetic mean ng "x". Upang gawin ito, idagdag ang lahat ng mga halaga ng "x", at pagkatapos ay hatiin ang resultang resulta sa bilang ng mga halaga.

   • Sa aming halimbawa, apat na halaga ng variable na "x" ang ibinigay. Upang kalkulahin ang arithmetic mean ng "x", idagdag ang mga halagang ito, at pagkatapos ay hatiin ang kabuuan sa 4. Ang mga kalkulasyon ay isusulat nang ganito:
   • μ x = (1 + 2 + 4 + 5) / 4 (\displaystyle \mu _(x)=(1+2+4+5)/4)
   • μ x = 12 / 4 (\displaystyle \mu _(x)=12/4)
   • μ x = 3 (\displaystyle \mu _(x)=3)

2. Hanapin ang arithmetic mean na "y". Upang gawin ito, tumakbo mga katulad na aksyon, iyon ay, idagdag ang lahat ng mga halaga ng "y", at pagkatapos ay hatiin ang kabuuan sa bilang ng mga halaga.

   • Sa aming halimbawa, apat na halaga ng variable na "y" ang ibinigay. Idagdag ang mga halagang ito, at pagkatapos ay hatiin ang kabuuan sa 4. Ang mga kalkulasyon ay isusulat nang ganito:
   • μ y = (1 + 3 + 5 + 7) / 4 (\displaystyle \mu _(y)=(1+3+5+7)/4)
   • μ y = 16 / 4 (\displaystyle \mu _(y)=16/4)
   • μ y = 4 (\displaystyle \mu _(y)=4)

3. Kalkulahin ang karaniwang paglihis ng "x". Pagkatapos kalkulahin ang average na mga halaga ng "x" at "y", hanapin karaniwang mga paglihis mga variable na ito. Ang karaniwang paglihis ay kinakalkula gamit ang sumusunod na formula:

   • σ x = 1 n − 1 Σ (x − μ x) 2 (\displaystyle \sigma _(x)=(\sqrt ((\frac (1)(n-1))\Sigma (x-\mu _( x))^(2))))
   • σ x = 1 4 − 1 ∗ ((1 − 3) 2 + (2 − 3) 2 + (4 − 3) 2 + (5 − 3) 2) (\displaystyle \sigma _(x)=(\sqrt ((\frac (1)(4-1))*((1-3)^(2)+(2-3)^(2)+(4-3)^(2)+(5-3) ^(2)))))
   • σ x = 1 3 ∗ (4 + 1 + 1 + 4) (\displaystyle \sigma _(x)=(\sqrt ((\frac (1)(3))*(4+1+1+4)) ))
   • σ x = 1 3 ∗ (10) (\displaystyle \sigma _(x)=(\sqrt ((\frac (1)(3))*(10))))
   • σ x = 10 3 (\displaystyle \sigma _(x)=(\sqrt (\frac (10)(3))))
   • σ x = 1, 83 (\displaystyle \sigma _(x)=1,83)

4. Kalkulahin ang karaniwang paglihis ng "y". Sundin ang mga hakbang na inilarawan sa nakaraang hakbang. Gumamit ng parehong formula, ngunit palitan ang mga halaga ng "y" dito.

   • Sa aming halimbawa, ang mga kalkulasyon ay isusulat tulad nito:
   • σ y = 1 4 − 1 ∗ ((1 − 4) 2 + (3 − 4) 2 + (5 − 4) 2 + (7 − 4) 2) (\displaystyle \sigma _(y)=(\sqrt ((\frac (1)(4-1))*((1-4)^(2)+(3-4)^(2)+(5-4)^(2)+(7-4) ^(2)))))
   • σ y = 1 3 ∗ (9 + 1 + 1 + 9) (\displaystyle \sigma _(y)=(\sqrt ((\frac (1)(3))*(9+1+1+9)) ))
   • σ y = 1 3 ∗ (20) (\displaystyle \sigma _(y)=(\sqrt ((\frac (1)(3))*(20))))
   • σ y = 20 3 (\displaystyle \sigma _(y)=(\sqrt (\frac (20)(3))))
   • σ y = 2.58 (\displaystyle \sigma _(y)=2.58)

5. Isulat ang pangunahing pormula para sa pagkalkula ng koepisyent ng ugnayan. Kasama sa formula na ito ang ibig sabihin, standard deviations, at numero (n) na mga pares ng mga numero para sa parehong variable. Ang koepisyent ng ugnayan ay tinutukoy bilang "r" (sa mga bihirang kaso bilang "ρ"). Gumagamit ang artikulong ito ng formula upang kalkulahin ang koepisyent ng ugnayan ng Pearson.

   • Dito at sa iba pang mga mapagkukunan, ang mga dami ay maaaring italaga sa ibang paraan. Halimbawa, ang ilang mga formula ay naglalaman ng "ρ" at "σ", habang ang iba ay naglalaman ng "r" at "s". Ang ilang mga aklat-aralin ay nagbibigay ng iba pang mga formula, ngunit ang mga ito ay mga analogue sa matematika ng formula sa itaas.

6. Nakalkula mo ang mga paraan at karaniwang mga paglihis ng parehong mga variable, kaya maaari mong gamitin ang formula upang kalkulahin ang koepisyent ng ugnayan. Alalahanin na ang "n" ay ang bilang ng mga pares ng mga halaga para sa parehong mga variable. Ang mga halaga ng iba pang mga dami ay kinakalkula nang mas maaga.

   • Sa aming halimbawa, ang mga kalkulasyon ay isusulat tulad nito:
   • ρ = (1 n − 1) Σ (x − μ x σ x) ∗ (y − μ y σ y) (\displaystyle \rho =\left((\frac (1)(n-1))\kanan) \Sigma \left((\frac (x-\mu _(x))(\sigma _(x)))\right)*\left((\frac (y-\mu _(y))(\sigma _(y)))\kanan))
   • ρ = (1 3) ∗ (\displaystyle \rho =\kaliwa((\frac (1)(3))\kanan)*)[ (1 − 3 1 , 83) ∗ (1 − 4 2 , 58) + (2 − 3 1 , 83) ∗ (3 − 4 2 , 58) (\displaystyle \left((\frac (1-3)( 1.83))\kanan)*\kaliwa((\frac (1-4)(2.58))\kanan)+\kaliwa((\frac (2-3)(1.83))\kanan) *\kaliwa((\ frac (3-4)(2.58))\kanan))
     + (4 − 3 1 , 83) ∗ (5 − 4 2 , 58) + (5 − 3 1 , 83) ∗ (7 − 4 2 , 58) (\displaystyle +\left((\frac (4-3) )(1.83))\kanan)*\kaliwa((\frac (5-4)(2.58))\kanan)+\kaliwa((\frac (5-3)(1.83))\ kanan)*\kaliwa( (\frac (7-4)(2.58))\kanan))]
   • ρ = (1 3) ∗ (6 + 1 + 1 + 6 4 , 721) (\displaystyle \rho =\left((\frac (1)(3))\right)*\left((\frac (6 +1+1+6)(4,721))\kanan))
   • ρ = (1 3) ∗ 2 , 965 (\displaystyle \rho =\left((\frac (1)(3))\kanan)*2.965)
   • ρ = (2 , 965 3) (\displaystyle \rho =\left((\frac (2.965)(3))\kanan))
   • ρ = 0.988 (\displaystyle \rho =0.988)

7. Pag-aralan ang resulta. Sa aming halimbawa, ang correlation coefficient ay 0.988. Ang halagang ito sa ilang paraan ay nagpapakilala sa hanay ng mga pares ng numero. Bigyang-pansin ang sign at magnitude ng halaga.

   • Dahil positibo ang halaga ng koepisyent ng ugnayan, mayroong positibong ugnayan sa pagitan ng mga variable na "x" at "y". Iyon ay, habang ang halaga ng "x" ay tumataas, ang halaga ng "y" ay tumataas din.
   • Dahil ang halaga ng koepisyent ng ugnayan ay napakalapit sa +1, ang mga halaga ng mga variable na "x" at "y" ay lubos na magkakaugnay. Kung mag-plot ka ng mga punto sa coordinate plane, sila ay matatagpuan malapit sa isang tiyak na tuwid na linya.

   Paggamit ng mga online na calculator upang kalkulahin ang koepisyent ng ugnayan

   1. Maghanap ng calculator sa Internet upang makalkula ang koepisyent ng ugnayan. Ang koepisyent na ito ay madalas na kinakalkula sa mga istatistika. Kung mayroong maraming mga pares ng mga numero, halos imposible na kalkulahin ang koepisyent ng ugnayan nang manu-mano. Samakatuwid, mayroong mga online na calculator upang kalkulahin ang koepisyent ng ugnayan. Sa isang search engine, ilagay ang "correlation coefficient calculator" (nang walang mga panipi).

   2. Ilagay ang iyong mga detalye. Pakisuri ang mga tagubilin sa website upang matiyak na naipasok mo nang tama ang data (mga pares ng numero). Napakahalaga na ipasok ang naaangkop na mga pares ng mga numero; kung hindi, makakakuha ka ng hindi tamang resulta. Tandaan na ang iba't ibang mga website ay may iba't ibang mga format ng pagpasok ng data.

      • Halimbawa, sa website na http://ncalculators.com/statistics/correlation-coefficient-calculator.htm ang mga halaga ng mga variable na "x" at "y" ay ipinasok sa dalawang pahalang na linya. Ang mga halaga ay pinaghihiwalay ng mga kuwit. Iyon ay, sa aming halimbawa, ang mga halaga ng "x" ay ipinasok tulad nito: 1,2,4,5, at ang mga halaga ng "y" tulad nito: 1,3,5,7.
      • Sa isa pang site, http://www.alcula.com/calculators/statistics/correlation-coefficient/, ang data ay ipinasok patayo; sa kasong ito, huwag malito ang mga katumbas na pares ng mga numero.

   3. Kalkulahin ang koepisyent ng ugnayan. Pagkatapos ipasok ang data, i-click lamang ang "Kalkulahin", "Kalkulahin" o katulad na pindutan upang makuha ang resulta.

      Gamit ang isang graphing calculator

      1. Ilagay ang iyong mga detalye. Kumuha ng graphing calculator, pumunta sa statistical mode at piliin ang Edit command.

         • Ang iba't ibang mga calculator ay nangangailangan ng iba't ibang mga keystroke upang pinindot. Tinatalakay ng artikulong ito ang Texas Instruments TI-86 calculator.
         • Para lumipat sa statistical calculation mode, pindutin ang – Stat (sa itaas ng “+” key). Pagkatapos ay pindutin ang F2 - I-edit.

      2. Tanggalin ang nakaraang na-save na data. Karamihan sa mga calculator ay nag-iimbak ng mga istatistika na iyong ipinasok hanggang sa i-clear mo ang mga ito. Upang maiwasang malito ang lumang data sa bagong data, tanggalin muna ang anumang nakaimbak na impormasyon.

         • Gamitin ang mga arrow key upang ilipat ang cursor at i-highlight ang heading na "xStat". Pagkatapos ay pindutin ang Clear at Enter para i-clear ang lahat ng value na ipinasok sa column ng xStat.
         • Gamitin ang mga arrow key upang i-highlight ang "yStat" na heading. Pagkatapos ay pindutin ang Clear at Enter para i-clear ang lahat ng value na ipinasok sa column ng yStat.

      3. Ipasok ang paunang data. Gamitin ang mga arrow key upang ilipat ang cursor sa unang cell sa ilalim ng heading na "xStat". Ipasok ang unang halaga at pindutin ang Enter. Ang “xStat (1) = __” ay ipapakita sa ibaba ng screen, kung saan lalabas ang inilagay na value sa halip na isang espasyo. Pagkatapos mong pindutin ang Enter, ang inilagay na halaga ay lilitaw sa talahanayan at ang cursor ay lilipat sa susunod na linya; ipapakita nito ang "xStat (2) = __" sa ibaba ng screen.

         • Ipasok ang lahat ng mga halaga para sa variable na "x".
         • Kapag naipasok mo na ang lahat ng value para sa x variable, gamitin ang mga arrow key para lumipat sa column ng yStat at ilagay ang mga value para sa y variable.
         • Kapag naipasok na ang lahat ng pares ng mga numero, pindutin ang Exit para i-clear ang screen at lumabas sa statistical calculation mode.

      4. Kalkulahin ang koepisyent ng ugnayan. Inilalarawan nito kung gaano kalapit ang data sa isang tiyak na linya. Ang isang graphing calculator ay maaaring mabilis na matukoy ang naaangkop na linya at makalkula ang koepisyent ng ugnayan.

         • I-click ang Stat – Calc. Sa TI-86 kailangan mong pindutin ang – –.
         • Piliin ang function na "Linear Regression". Sa TI-86, pindutin ang , na may label na "LinR". Ipapakita ng screen ang linyang "LinR_" na may kumikislap na cursor.
         • Ngayon ipasok ang mga pangalan ng dalawang variable: xStat at yStat.
           • Sa TI-86, buksan ang listahan ng mga pangalan; Upang gawin ito, pindutin ang – – .
           • Ang ilalim na linya ng screen ay magpapakita ng mga magagamit na variable. Piliin (malamang na kailangan mong pindutin ang F1 o F2 upang gawin ito), maglagay ng kuwit, at pagkatapos ay piliin ang .
           • Pindutin ang Enter upang iproseso ang ipinasok na data.

      5. Pag-aralan ang iyong mga resulta. Sa pamamagitan ng pagpindot sa Enter, ang sumusunod na impormasyon ay ipapakita sa screen:

         • y = a + b x (\displaystyle y=a+bx): Ito ay isang function na naglalarawan ng isang tuwid na linya. Pakitandaan na ang function ay hindi nakasulat sa karaniwang anyo (y = kh + b).
         • a = (\displaystyle a=). Ito ang "y" coordinate ng punto kung saan ang linya ay nag-intersect sa Y axis.
         • b = (\displaystyle b=). Ito ang slope ng linya.
         • corr = (\displaystyle (\text(corr))=). Ito ang koepisyent ng ugnayan.
         • n = (\displaystyle n=). Ito ang bilang ng mga pares ng mga numero na ginamit sa mga kalkulasyon.

Kalkulahin natin ang koepisyent ng ugnayan at covariance para sa iba't ibang uri relasyon ng mga random na variable.

Koepisyent ng ugnayan(pamantayan ng ugnayan Pearson, Ingles Coefficient ng ugnayan ng Pearson Product Moment) tumutukoy sa antas linear relasyon sa pagitan ng mga random na variable.

Tulad ng sumusunod mula sa kahulugan, upang makalkula koepisyent ng ugnayan kinakailangang malaman ang distribusyon ng mga random na variable X at Y. Kung ang mga distribusyon ay hindi alam, pagkatapos ay tantiyahin koepisyent ng ugnayan ginamit sample na koepisyent ng ugnayanr ( ito ay itinalaga rin bilang Rxy o r xy) :

kung saan S x - karaniwang paglihis mga sample random variable x, kinakalkula ng formula:

Tulad ng makikita mula sa formula ng pagkalkula mga ugnayan, ang denominator (ang produkto ng mga karaniwang paglihis) ay nag-normalize lamang ng numerator nang ganoon ugnayan lumalabas na walang sukat na numero mula -1 hanggang 1. Kaugnayan At covariance magbigay ng parehong impormasyon (kung alam karaniwang mga paglihis), Ngunit ugnayan mas maginhawang gamitin, dahil ito ay isang walang sukat na dami.

Kalkulahin koepisyent ng ugnayan At sample covariance sa MS EXCEL ay hindi mahirap, dahil may mga espesyal na function na CORREL() at KOVAR() para sa layuning ito. Ito ay mas mahirap na malaman kung paano bigyang-kahulugan ang mga nakuhang halaga na ang karamihan sa artikulo ay nakatuon dito.

Theoretical retreat

Alalahanin natin iyon koneksyon ng ugnayan tumawag sa isang istatistikal na relasyon na binubuo sa katotohanan na ang iba't ibang mga halaga ng isang variable ay tumutugma sa iba karaniwan iba ang mga halaga (na may pagbabago sa halaga ng X average na halaga Y nagbabago sa isang regular na paraan). Ipinapalagay na pareho Ang mga variable na X at Y ay random mga halaga at may tiyak na random na scatter na nauugnay sa kanila average na halaga.

Tandaan. Kung ang isang variable lamang, halimbawa, Y, ay may random na kalikasan, at ang mga halaga ng isa ay deterministiko (itinakda ng mananaliksik), kung gayon maaari lamang nating pag-usapan ang tungkol sa regression.

Kaya, halimbawa, kapag pinag-aaralan ang pag-asa ng average na taunang temperatura, hindi maaaring pag-usapan ng isa mga ugnayan temperatura at taon ng pagmamasid at, nang naaayon, maglapat ng mga tagapagpahiwatig mga ugnayan kasama ang kanilang kaukulang interpretasyon.

Kaugnayan sa pagitan ng mga variable ay maaaring lumitaw sa maraming paraan:

1. Ang pagkakaroon ng isang sanhi na relasyon sa pagitan ng mga variable. Halimbawa, ang halaga ng pamumuhunan sa siyentipikong pananaliksik(variable X) at ang bilang ng mga patent na natanggap (Y). Ang unang variable ay lilitaw bilang malayang variable (factor), pangalawa - dependent variable (kinalabasan). Dapat alalahanin na ang pag-asa ng mga dami ay tumutukoy sa pagkakaroon ng isang ugnayan sa pagitan nila, ngunit hindi sa kabaligtaran.
2. Ang pagkakaroon ng conjugation ( karaniwang dahilan). Halimbawa, habang lumalaki ang organisasyon, tumataas ang pondo ng sahod (payroll) at ang halaga ng pag-upa ng mga lugar. Malinaw, mali na ipagpalagay na ang pagrenta ng mga lugar ay nakasalalay sa payroll. Pareho sa mga variable na ito ay nakadepende nang linear sa bilang ng mga tauhan sa maraming mga kaso.
3. Mutual na impluwensya ng mga variable (kapag ang isa ay nagbago, ang pangalawang variable ay nagbabago, at vice versa). Sa pamamaraang ito, pinapayagan ang dalawang pormulasyon ng problema; Anumang variable ay maaaring kumilos bilang isang independent variable at bilang isang dependent variable.

kaya, tagapagpahiwatig ng ugnayan nagpapakita kung gaano kalakas linear na relasyon sa pagitan ng dalawang salik (kung mayroong isa), at pinapayagan ka ng regression na mahulaan ang isang salik batay sa isa pa.

Kaugnayan, tulad ng iba pang istatistikal na tagapagpahiwatig, kung kailan tamang paggamit maaaring maging kapaki-pakinabang, ngunit mayroon din itong mga limitasyon sa paggamit. Kung ito ay nagpapakita ng malinaw na tinukoy na linear na relasyon o kumpletong kawalan mga relasyon, kung gayon ugnayan magpapakita ito ng kamangha-mangha. Ngunit, kung ang data ay nagpapakita ng isang non-linear na relasyon (halimbawa, quadratic), ang pagkakaroon ng hiwalay na mga grupo ng mga halaga o outlier, kung gayon ang kinakalkula na halaga koepisyent ng ugnayan maaaring nakaliligaw (tingnan ang halimbawang file).

Kaugnayan malapit sa 1 o -1 (i.e. malapit sa absolute value sa 1) ay nagpapakita ng isang malakas na linear na relasyon sa pagitan ng mga variable, isang value na malapit sa 0 ay nagpapakita ng walang kaugnayan. Positibo ugnayan nangangahulugan na sa isang pagtaas sa isang tagapagpahiwatig, ang isa sa average na pagtaas, at sa isang negatibong tagapagpahiwatig, ito ay bumababa.

Upang makalkula ang koepisyent ng ugnayan, kinakailangan na ang mga inihambing na mga variable ay matugunan ang mga sumusunod na kondisyon:

• ang bilang ng mga variable ay dapat na katumbas ng dalawa;
• ang mga variable ay dapat na quantitative (hal. dalas, timbang, presyo). Ang kinakalkula na average ng mga variable na ito ay may katuturan: average na presyo o average na timbang ng pasyente. Hindi tulad ng mga quantitative variable, ang qualitative (nominal) na mga variable ay kumukuha lamang ng mga halaga mula sa isang may hangganan na hanay ng mga kategorya (halimbawa, kasarian o uri ng dugo). Ang mga halagang ito ay karaniwang nauugnay sa mga numerong halaga (halimbawa, ang kasarian ng babae ay 1, at ang kasarian ng lalaki ay 2). Ito ay malinaw na sa kasong ito ang pagkalkula average na halaga, na kinakailangan upang mahanap mga ugnayan, ay hindi tama, at samakatuwid ang pagkalkula mismo ay hindi tama mga ugnayan;
• ang mga variable ay dapat na random na mga variable at mayroon .

Ang dalawang-dimensional na data ay maaaring magkaroon ng iba't ibang istruktura. Ang ilan sa mga ito ay nangangailangan ng ilang mga diskarte upang gumana sa:

• Para sa data na may non-linear na relasyon ugnayan dapat gamitin nang may pag-iingat. Para sa ilang mga problema, maaaring maging kapaki-pakinabang ang pagbabago ng isa o parehong mga variable upang makabuo ng isang linear na relasyon (nangangailangan ito ng paggawa ng isang pagpapalagay tungkol sa uri ng nonlinear na relasyon upang magmungkahi ng uri ng pagbabagong kailangan).
• Sa pamamagitan ng paggamit scatter plots Ang ilang data ay maaaring magpakita ng hindi pantay na pagkakaiba-iba (scatter). Ang problema sa hindi pantay na pagkakaiba-iba ay ang mga lokasyong may mataas na pagkakaiba-iba ay hindi lamang nagbibigay ng hindi bababa sa tumpak na impormasyon, ngunit mayroon ding pinakamalaking epekto kapag kinakalkula ang mga istatistika. Ang problemang ito ay madalas ding nalulutas sa pamamagitan ng pagbabago ng data, tulad ng paggamit ng logarithms.
• Maaaring maobserbahan ang ilang data na nahahati sa mga grupo (clustering), na maaaring magpahiwatig ng pangangailangan na hatiin ang populasyon sa mga bahagi.
• Ang isang outlier (isang matalas na lumilihis na halaga) ay maaaring masira ang kinakalkula na halaga ng koepisyent ng ugnayan. Ang isang outlier ay maaaring dahil sa pagkakataon, isang error sa pagkolekta ng data, o maaaring aktwal na nagpapakita ng ilang tampok ng relasyon. Dahil ang outlier ay lubhang lumihis mula sa average na halaga, ito ay gumagawa ng malaking kontribusyon sa pagkalkula ng indicator. Ang mga istatistikal na tagapagpahiwatig ay kadalasang kinakalkula nang may at hindi isinasaalang-alang ang mga outlier.

Gamit ang MS EXCEL upang makalkula ang ugnayan

Kunin natin ang 2 variable bilang isang halimbawa X At Y at, ayon dito, sample na binubuo ng ilang mga pares ng mga halaga (X i; Y i). Para sa kalinawan, buuin natin ang .

Tandaan: Para sa karagdagang impormasyon tungkol sa pagbuo ng mga diagram, tingnan ang artikulo. Sa halimbawang file para sa gusali scatter plots ginamit dahil dito kami ay lumihis mula sa kinakailangan na ang variable X ay random (pinasimple nito ang henerasyon iba't ibang uri mga relasyon: pagbuo ng mga uso at isang naibigay na spread). Para sa totoong data, dapat kang gumamit ng Scatter chart (tingnan sa ibaba).

Mga kalkulasyon mga ugnayan Gumuhit tayo ng mga ugnayan sa pagitan ng mga variable para sa iba't ibang mga kaso: linear, parisukat at sa kawalan ng komunikasyon.

Tandaan: Sa halimbawang file, maaari mong itakda ang mga parameter ng linear trend (slope, Y-intercept) at ang antas ng scatter na nauugnay sa trend line na ito. Maaari mo ring ayusin ang mga quadratic na parameter.

Sa halimbawang file para sa gusali scatter plots kung walang dependence ng mga variable, isang scatter diagram ang ginagamit. Sa kasong ito, ang mga punto sa diagram ay nakaayos sa anyo ng isang ulap.

Tandaan: Pakitandaan na sa pamamagitan ng pagbabago ng sukat ng tsart patayo o pahalang na axis, ang ulap ng mga punto ay maaaring bigyan ng hitsura ng isang patayo o pahalang na linya. Malinaw na ang mga variable ay mananatiling independyente.

Tulad ng nabanggit sa itaas, upang makalkula koepisyent ng ugnayan sa MS EXCEL mayroong CORREL() function. Maaari mo ring gamitin ang katulad na PEARSON() function, na nagbabalik ng parehong resulta.

Upang matiyak na ang mga kalkulasyon mga ugnayan ay ginawa ng CORREL() function gamit ang mga formula sa itaas; mga ugnayan gamit ang mas detalyadong mga formula:

=COVARIANCE.G(B28:B88;D28:D88)/STDEV.G(B28:B88)/STDEV.G(D28:D88)

=COVARIANCE.B(B28:B88;D28:D88)/STDEV.B(B28:B88)/STDEV.B(D28:D88)

Tandaan: Kuwadrado koepisyent ng ugnayan r ay katumbas ng koepisyent ng determinasyon R2, na kinakalkula kapag gumagawa ng isang regression line gamit ang QPIRSON() function. Ang halaga ng R2 ay maaari ding maging output sa scatter diagram sa pamamagitan ng pagbuo ng linear trend gamit ang karaniwang MS EXCEL functionality (piliin ang chart, piliin ang tab Layout, pagkatapos ay sa grupo Pagsusuri i-click ang pindutan Linya ng uso at piliin Linear approximation). Para sa higit pang impormasyon sa pagbuo ng trend line, tingnan, halimbawa, .

Paggamit ng MS EXCEL para Kalkulahin ang Covariance

Covariance ay malapit sa kahulugan sa (isang sukatan din ng pagpapakalat) na may pagkakaiba na ito ay tinukoy para sa 2 variable, at pagpapakalat- para sa isa. Samakatuwid, cov(x;x)=VAR(x).

Upang kalkulahin ang covariance sa MS EXCEL (simula sa bersyon 2010), ang mga function na COVARIATION.G() at COVARIATION.B() ay ginagamit. Sa unang kaso, ang formula para sa pagkalkula ay katulad sa itaas (end .G ay kumakatawan sa Populasyon ), sa pangalawa, sa halip na ang multiplier 1/n, 1/(n-1) ang ginagamit, i.e. pagtatapos .SA ay kumakatawan sa Sample.

Tandaan: Ang COVAR() function, na nasa MS EXCEL sa mga naunang bersyon, ay katulad ng COVARIATION.G() function.

Tandaan: Ang CORREL() at COVAR() function ay ipinakita sa English na bersyon bilang CORREL at COVAR. Ang gumaganang COVARIANCE.G() at COVARIANCE.B() bilang COVARIANCE.P at COVARIANCE.S.

Mga karagdagang formula para sa pagkalkula covariances:

=SUMPRODUCT(B28:B88-AVERAGE(B28:B88);(D28:D88-AVERAGE(D28:D88)))/COUNT(D28:D88)

=SUMPRODUCT(B28:B88-AVERAGE(B28:B88),(D28:D88))/COUNT(D28:D88)

=SUMPRODUCT(B28:B88;D28:D88)/COUNT(D28:D88)-AVERAGE(B28:B88)*AVERAGE(D28:D88)

Ginagamit ng mga formula na ito ang property covariances:

Kung ang mga variable x At y independyente, kung gayon ang kanilang covariance ay 0. Kung ang mga variable ay hindi independyente, kung gayon ang pagkakaiba ng kanilang kabuuan ay katumbas ng:

VAR(x+y)= VAR(x)+ VAR(y)+2COV(x;y)

A pagpapakalat pantay ang kanilang pagkakaiba

VAR(x-y)= VAR(x)+ VAR(y)-2COV(x;y)

Pagtataya ng statistical significance ng correlation coefficient

Upang masubukan ang hypothesis, dapat nating malaman ang distribusyon ng random variable, i.e. koepisyent ng ugnayan r. Karaniwan, ang hypothesis ay sinubok hindi para sa r, ngunit para sa random na variable t r:

na may n-2 degrees ng kalayaan.

Kung ang kinakalkula na halaga ng random variable |t r | ay mas malaki kaysa sa kritikal na halaga t α,n-2 (α-tinukoy), pagkatapos ay ang null hypothesis ay tinanggihan (ang relasyon sa pagitan ng mga halaga ay makabuluhang istatistika).

Add-in ng package ng pagsusuri

B para kalkulahin ang covariance at correlation may mga instrumento ng parehong pangalan pagsusuri.

Pagkatapos tawagan ang tool, lalabas ang isang dialog box na naglalaman ng mga sumusunod na field:

• Interval ng pag-input: kailangan mong maglagay ng link sa isang hanay na may source data para sa 2 variable
• Pagpapangkat: Bilang panuntunan, ang source data ay ipinasok sa 2 column
• Mga label sa unang linya: kung ang checkbox ay naka-check, pagkatapos Interval ng pag-input dapat maglaman ng mga header ng column. Inirerekomenda na lagyan ng tsek ang kahon upang ang resulta ng Add-in ay naglalaman ng mga column na nagbibigay-kaalaman
• Output interval: ang hanay ng mga cell kung saan ilalagay ang mga resulta ng pagkalkula. Ito ay sapat na upang ipahiwatig ang itaas na kaliwang cell ng hanay na ito.

Ibinabalik ng add-in ang kinakalkula na mga halaga ng ugnayan at covariance (para sa covariance, ang mga pagkakaiba-iba ng parehong mga random na variable ay kinakalkula din).

Ang isang quantitative na katangian ng relasyon ay maaaring makuha sa pamamagitan ng pagkalkula ng correlation coefficient.

Pagsusuri ng Kaugnayan sa Excel

Ang mismong function ay mayroon pangkalahatang pananaw CORREL(array1, array2). Sa field na "Array1", ipasok ang mga coordinate ng hanay ng mga cell ng isa sa mga halaga, kung saan dapat matukoy ang dependence nito. Tulad ng nakikita mo, ang koepisyent ng ugnayan sa anyo ng isang numero ay lilitaw sa cell na dati naming pinili. Magbubukas ang isang window na may mga parameter ng pagsusuri ng ugnayan. Hindi tulad ng nakaraang pamamaraan, sa field na "Input interval" ipinapasok namin ang interval hindi ng bawat column nang hiwalay, ngunit ng lahat ng column na lumahok sa pagsusuri. Tulad ng nakikita mo, nag-aalok ang Excel application ng dalawang paraan ng pagsusuri ng ugnayan nang sabay-sabay.

Correlation graph sa excel

6) Ang unang elemento ng huling talahanayan ay lilitaw sa itaas na kaliwang cell ng napiling lugar. Samakatuwid, ang H0 hypothesis ay tinanggihan, iyon ay, ang mga parameter ng regression at ang koepisyent ng ugnayan ay hindi random na naiiba mula sa zero, ngunit makabuluhang istatistika. 7. Ang nakuhang mga pagtatantya ng equation ng regression ay nagpapahintulot na magamit ito para sa pagtataya.

Paano makalkula ang koepisyent ng ugnayan sa Excel

Kung ang koepisyent ay 0, ito ay nagpapahiwatig na walang kaugnayan sa pagitan ng mga halaga. Upang mahanap ang kaugnayan sa pagitan ng mga variable at y, gamitin ang built-in na function Microsoft Excel"CORREL". Halimbawa, para sa "Array1" piliin ang mga y value, at para sa "Array2" piliin ang mga x value. Bilang resulta, matatanggap mo ang koepisyent ng ugnayan na kinakalkula ng programa. Susunod, kailangan mong kalkulahin ang pagkakaiba sa pagitan ng bawat x at xav, at yav. Sa mga napiling cell isulat mga formula x-x, y-. Huwag kalimutang i-pin ang mga cell na may mga average. Ang resultang makukuha ay ang nais na koepisyent ng ugnayan.

Ang formula sa itaas para sa pagkalkula ng koepisyent ng Pearson ay nagpapakita kung gaano labor-intensive ang prosesong ito kung gagawin nang manu-mano. Pangalawa, mangyaring irekomenda kung anong uri ng pagsusuri ng ugnayan ang maaaring gamitin para sa iba't ibang mga sample na may malaking pagkalat ng data? Paano ko mapapatunayan ayon sa istatistika na may makabuluhang pagkakaiba sa pagitan ng pangkat na higit sa 60 at ng iba pa?

DIY: Pagkalkula ng Currency Correlations Gamit ang Excel

Halimbawa, gumagamit kami ng Microsoft Excel, ngunit ang anumang iba pang programa kung saan maaari kang gumamit ng formula ng ugnayan ay magagawa. 7.Pagkatapos nito, piliin ang mga cell na may data ng EUR/USD. 9. Pindutin ang Enter para kalkulahin ang correlation coefficient para sa EUR/USD at USD/JPY. Ito ay hindi nagkakahalaga ng pag-update ng mga numero araw-araw (well, maliban kung ikaw ay nahuhumaling sa mga ugnayan ng pera).

Naranasan mo na ang pangangailangang kalkulahin ang antas ng koneksyon sa pagitan ng dalawa istatistikal na dami at tukuyin ang pormula kung saan iniuugnay ang mga ito? Upang gawin ito, ginamit ko ang CORREL function - mayroong ilang impormasyon tungkol dito. Ibinabalik nito ang antas ng ugnayan sa pagitan ng dalawang hanay ng data. Sa teorya, ang pag-andar ng ugnayan ay maaaring pinuhin sa pamamagitan ng pag-convert nito mula sa linear patungo sa exponential o logarithmic. Ang pagsusuri ng data at mga graph ng ugnayan ay maaaring mapabuti ang pagiging maaasahan nito nang lubos.

Ipagpalagay natin na ang cell B2 ay naglalaman ng correlation coefficient mismo, at ang cell B3 ay naglalaman ng bilang ng mga kumpletong obserbasyon. Mayroon ka bang opisina na nagsasalita ng Ruso, may nakita din akong pagkakamali - hindi kinakalkula ang kahalagahan para sa mga negatibong ugnayan. Kung ang parehong mga variable ay sukatan at mayroon normal na pamamahagi, pagkatapos ay ginawa nang tama ang pagpili. At, posible bang makilala ang pamantayan ng pagkakatulad ng mga kurba gamit lamang ang isang CC Wala kang pagkakatulad ng "mga kurba", ngunit ang pagkakatulad ng dalawang serye, na sa prinsipyo ay maaaring ilarawan ng isang kurba?

Sa koneksyon ng ugnayan ang parehong halaga ng isang katangian ay tumutugma sa iba't ibang mga halaga ng isa pa. Halimbawa: mayroong isang ugnayan sa pagitan ng taas at timbang, sa pagitan ng saklaw ng mga malignant na neoplasma at edad, atbp.

Mayroong 2 mga pamamaraan para sa pagkalkula ng koepisyent ng ugnayan: ang paraan ng mga parisukat (Pearson), ang paraan ng mga ranggo (Spearman).

Ang pinakatumpak ay ang paraan ng mga parisukat (Pearson), kung saan ang koepisyent ng ugnayan ay tinutukoy ng formula: , kung saan

Ang r xy ay ang koepisyent ng ugnayan sa pagitan ng istatistikal na serye X at Y.

d x ay ang paglihis ng bawat isa sa mga numero ng istatistikal na serye X mula sa arithmetic mean nito.

d y ay ang paglihis ng bawat isa sa mga numero ng istatistikal na serye Y mula sa arithmetic mean nito.

Depende sa lakas ng koneksyon at direksyon nito, ang correlation coefficient ay maaaring mula 0 hanggang 1 (-1). Ang isang koepisyent ng ugnayan na 0 ay nagpapahiwatig ng kumpletong kakulangan ng koneksyon. Kung mas malapit ang antas ng koepisyent ng ugnayan sa 1 o (-1), mas malaki at mas malapit ang direktang o feedback na sinusukat nito. Kapag ang correlation coefficient ay katumbas ng 1 o (-1), ang koneksyon ay kumpleto at gumagana.

Scheme para sa pagtatasa ng lakas ng correlation gamit ang correlation coefficient

Ang lakas ng koneksyon	Ang halaga ng koepisyent ng ugnayan kung magagamit
	direktang koneksyon (+)	puna (-)
Walang koneksyon
Ang koneksyon ay maliit (mahina)	mula 0 hanggang +0.29	mula 0 hanggang –0.29
Average ng koneksyon (katamtaman)	mula +0.3 hanggang +0.69	mula –0.3 hanggang –0.69
Malaki ang koneksyon (malakas)	mula +0.7 hanggang +0.99	mula –0.7 hanggang –0.99
Buong komunikasyon (functional)

Upang kalkulahin ang koepisyent ng ugnayan gamit ang parisukat na paraan, isang talahanayan ng 7 mga haligi ay pinagsama-sama. Tingnan natin ang proseso ng pagkalkula gamit ang isang halimbawa:

TUKUYIN ANG LAKAS AT KALIKASAN NG KONEKSIYON SA PAGITAN

	Oras na- ness goiter (V y )	d x = V x –M x	d y= V y –M y	d x d y	d x 2	d y 2
				Σ -1345 ,0	Σ 13996 ,0	Σ 313 , 47

1. Tukuyin ang karaniwang nilalaman ng iodine sa tubig (sa mg/l).

mg/l

2. Tukuyin ang average na saklaw ng goiter sa %.

3. Tukuyin ang paglihis ng bawat V x mula sa M x, i.e. dx.

201–138=63; 178–138=40, atbp.

4. Katulad nito, tinutukoy namin ang paglihis ng bawat V y mula sa M y, i.e. d y.

0.2–3.8=-3.6; 0.6–38=-3.2, atbp.

5. Tukuyin ang mga produkto ng mga deviations. Binubuo namin ang nagresultang produkto at nakukuha.

6. Namin parisukat d x at sum up ang mga resulta, makuha namin.

7. Katulad nito, parisukat namin ang d y, sum up ang mga resulta, nakukuha namin

8. Sa wakas, pinapalitan namin ang lahat ng natanggap na halaga sa formula:

Upang malutas ang isyu ng pagiging maaasahan ng koepisyent ng ugnayan, tukuyin ito average na error ayon sa formula:

(Kung ang bilang ng mga obserbasyon ay mas mababa sa 30, kung gayon ang denominator ay n–1).

Sa ating halimbawa

Ang halaga ng correlation coefficient ay itinuturing na maaasahan kung ito ay hindi bababa sa 3 beses na mas mataas kaysa sa average na error nito.

Sa ating halimbawa

Kaya, ang koepisyent ng ugnayan ay hindi maaasahan, na nangangailangan ng pagtaas sa bilang ng mga obserbasyon.

Ang koepisyent ng ugnayan ay maaaring matukoy sa isang bahagyang hindi gaanong tumpak, ngunit mas madaling paraan - ang paraan ng mga ranggo (Spearman).

Paraan ng Spearman: P=1-(6∑d 2 /n-(n 2 -1))

gumawa ng dalawang hanay ng magkapares na maihahambing na mga tampok, na itinatalaga ang una at pangalawang hilera x at y, ayon sa pagkakabanggit. Sa kasong ito, ipakita ang unang hilera ng katangian sa pababang o pataas na pagkakasunud-sunod, at ilagay ang mga numerong halaga ng pangalawang hilera sa tapat ng mga halaga ng unang hilera kung saan sila tumutugma.

palitan ang halaga ng katangian sa bawat isa sa pinaghahambing na serye ng serial number (ranggo). Ang mga ranggo, o mga numero, ay nagpapahiwatig ng mga lugar ng mga tagapagpahiwatig (mga halaga) ng una at ikalawang hanay. Kasabay nito mga numerong halaga ng pangalawang katangian, ang mga ranggo ay dapat na italaga sa parehong pagkakasunud-sunod na pinagtibay kapag inilalaan ang mga ito sa mga halaga ng unang katangian. Sa pantay na mga halaga ng isang katangian sa isang serye, ang mga ranggo ay dapat matukoy bilang ang average na numero mula sa kabuuan ng mga ordinal na numero ng mga halagang ito

tukuyin ang pagkakaiba ng ranggo sa pagitan ng x at y (d): d = x - y

parisukat ang resultang pagkakaiba ng ranggo (d 2)

makuha ang kabuuan ng mga parisukat ng pagkakaiba (Σ d 2) at palitan ang mga resultang halaga sa formula:

Halimbawa: Gamit ang paraan ng ranggo, itatag ang direksyon at lakas ng ugnayan sa pagitan ng mga taon ng karanasan sa trabaho at ang dalas ng mga pinsala kung nakuha ang sumusunod na data:

Katwiran para sa pagpili ng pamamaraan: Upang malutas ang isang problema, isang paraan lamang ang maaaring piliin ugnayan ng ranggo, dahil Ang unang hilera ng katangian na "karanasan sa trabaho sa mga taon" ay may mga bukas na opsyon (karanasan sa trabaho hanggang 1 taon at 7 o higit pang mga taon), na hindi pinapayagan ang paggamit ng isang mas tumpak na paraan - ang paraan ng mga parisukat - upang magtatag ng isang koneksyon sa pagitan ng mga inihambing na katangian.

Solusyon. Ang pagkakasunud-sunod ng mga kalkulasyon ay ipinakita sa teksto, ang mga resulta ay ipinakita sa talahanayan. 2.

Talahanayan 2

Karanasan sa trabaho sa mga taon	Bilang ng mga pinsala	Ordinal na numero (ranggo)		Pagkakaiba ng ranggo	Squared na pagkakaiba ng mga ranggo
				d(x-y)	d 2

Ang bawat isa sa mga hilera ng magkapares na katangian ay itinalaga ng "x" at "y" (mga hanay 1-2).

Ang halaga ng bawat tampok ay pinapalitan ng isang ranggo (ordinal) na numero. Ang pagkakasunud-sunod ng pamamahagi ng mga ranggo sa row na "x" ay ang mga sumusunod: ang minimum na halaga ng attribute (karanasan hanggang 1 taon) ay itinalaga ang serial number na "1", ang mga kasunod na variant ng parehong hilera ng attribute, ayon sa pagkakabanggit, sa pagtaas ng pagkakasunod-sunod, ika-2, ika-3, ika-4 at ika-5 na serial number - mga ranggo (tingnan ang hanay 3). Ang isang katulad na pagkakasunud-sunod ay sinusunod kapag namamahagi ng mga ranggo sa pangalawang katangian na "y" (hanay 4). Sa mga kaso kung saan mayroong ilang mga opsyon ng pantay na laki (halimbawa, sa karaniwang problema ito ay 12 at 12 pinsala sa bawat 100 manggagawa na may karanasan ng 3-4 na taon at 5-6 na taon, ang serial number ay itinalaga ng average na numero mula sa kabuuan ng kanilang mga serial number Kapag nagra-rank, ang data sa bilang ng mga pinsala (12 pinsala) ay dapat sumakop sa 2 at 3 lugar, kaya ang average na bilang ng mga ito ay (2 + 3)/2 = 2.5 ng mga pinsala ay "12" at "12" (attribute ) ang parehong mga numero ng ranggo ay dapat ipamahagi - "2.5" (hanay 4).

Tukuyin ang pagkakaiba ng ranggo d = (x - y) - (column 5)

Kuwadrado ang pagkakaiba ng ranggo (d 2) at makuha ang kabuuan ng mga parisukat ng pagkakaiba sa ranggo Σ d 2 (kolumna 6).

Kalkulahin ang rank correlation coefficient gamit ang formula:

kung saan ang n ay ang bilang ng mga pares ng mga opsyon na inihahambing sa row "x" at sa row na "y"

Pagsusuri ng ugnayan- tanyag na pamamaraan istatistikal na pananaliksik, na ginagamit upang matukoy ang antas ng pag-asa ng isang tagapagpahiwatig sa isa pa. Ang Microsoft Excel ay may espesyal na tool na idinisenyo upang maisagawa ang ganitong uri ng pagsusuri. Alamin natin kung paano gamitin ang feature na ito.

Ang kakanyahan ng pagsusuri ng ugnayan

Ang layunin ng pagsusuri ng ugnayan ay upang matukoy ang pagkakaroon ng isang relasyon sa pagitan iba't ibang salik. Ibig sabihin, natutukoy kung ang pagbaba o pagtaas sa isang indicator ay nakakaapekto sa pagbabago sa isa pa.

Kung ang pag-asa ay itinatag, pagkatapos ay ang koepisyent ng ugnayan ay tinutukoy. Hindi tulad ng regression analysis, ito lang ang indicator na nagkalkula ang pamamaraang ito istatistikal na pananaliksik. Ang koepisyent ng ugnayan ay mula +1 hanggang -1. Kung mayroong positibong ugnayan, ang pagtaas sa isang tagapagpahiwatig ay nag-aambag sa pagtaas sa pangalawa. Sa isang negatibong ugnayan, ang pagtaas sa isang tagapagpahiwatig ay nangangailangan ng pagbawas sa isa pa. Kung mas malaki ang module ng correlation coefficient, mas kapansin-pansin ang pagbabago sa isang indicator na makikita sa pagbabago sa pangalawa. Kapag ang koepisyent ay 0, walang ganap na pag-asa sa pagitan nila.

Pagkalkula ng koepisyent ng ugnayan

Ngayon subukan nating kalkulahin ang koepisyent ng ugnayan gamit ang isang tiyak na halimbawa. Mayroon kaming talahanayan kung saan ang mga gastos sa advertising at dami ng mga benta ay nakalista buwan-buwan sa magkahiwalay na mga column. Kailangan nating alamin ang antas kung saan nakasalalay ang bilang ng mga benta sa halaga cash, na ginugol sa advertising.

Paraan 1: Pagtukoy ng Kaugnayan sa pamamagitan ng Function Wizard

Ang isang paraan kung saan maaaring maisagawa ang pagsusuri ng ugnayan ay sa pamamagitan ng paggamit ng CORREL function. Ang mismong function ay may pangkalahatang anyo na CORREL(array1, array2).

1. Piliin ang cell kung saan dapat ipakita ang resulta ng pagkalkula. Mag-click sa pindutang "Insert Function", na matatagpuan sa kaliwa ng formula bar.
2. Sa listahan na ipinakita sa window ng Function Wizard, hanapin at piliin ang CORREL function. Mag-click sa pindutang "OK".
3. Bubukas ang window ng function arguments. Sa field na "Array1", ipasok ang mga coordinate ng hanay ng mga cell ng isa sa mga halaga, kung saan dapat matukoy ang dependence nito. Sa aming kaso, ito ang magiging mga halaga sa column na "Halaga ng benta". Upang maipasok ang address ng array sa field, piliin lang ang lahat ng mga cell na may data sa column sa itaas.

   Sa field na "Array2" kailangan mong ipasok ang mga coordinate ng pangalawang column. Para sa amin ito ay mga gastos sa advertising. Sa eksaktong parehong paraan tulad ng sa nakaraang kaso, ipinasok namin ang data sa field.

   Mag-click sa pindutang "OK".

Tulad ng nakikita mo, ang koepisyent ng ugnayan sa anyo ng isang numero ay lilitaw sa cell na dati naming pinili. SA sa kasong ito ito ay katumbas ng 0.97, na napaka mataas na tanda dependence ng isang dami sa isa pa.

Paraan 2: Kalkulahin ang ugnayan gamit ang package ng pagsusuri

Bilang kahalili, maaaring kalkulahin ang ugnayan gamit ang isa sa mga tool na ibinigay sa package ng pagsusuri. Ngunit kailangan muna nating i-activate ang tool na ito.

1. Pumunta sa tab na "File".
2. Sa window na bubukas, lumipat sa seksyong "Mga Setting".
3. Susunod, pumunta sa item na "Mga Add-on".
4. Sa ibaba ng susunod na window, sa seksyong "Pamamahala," ilipat ang switch sa posisyon na "Excel Add-in" kung ito ay nasa ibang posisyon. Mag-click sa pindutang "OK".
5. Sa window ng mga add-on, lagyan ng check ang kahon sa tabi ng item na "Package ng pagsusuri". Mag-click sa pindutang "OK".
6. Pagkatapos nito, isinaaktibo ang pakete ng pagsusuri. Pumunta sa tab na "Data". Tulad ng nakikita mo, lumilitaw ito dito sa tape bagong block mga tool - "Pagsusuri". Mag-click sa pindutan ng "Pagsusuri ng Data", na matatagpuan dito.
7. Magbubukas ang isang listahan na may iba't ibang mga pagpipilian pagsusuri ng datos. Piliin ang item na "Kaugnayan". Mag-click sa pindutang "OK".
8. Magbubukas ang isang window na may mga parameter ng pagsusuri ng ugnayan. Hindi tulad ng nakaraang pamamaraan, sa field na "Input interval" ipinapasok namin ang interval hindi ng bawat column nang hiwalay, ngunit ng lahat ng column na lumahok sa pagsusuri. Sa aming kaso, ito ay data sa mga column na "Mga gastos sa advertising" at "Halaga ng benta."

   Iniiwan namin ang parameter na "Pagpapangkat" na hindi nagbabago - "Ayon sa mga column", dahil ang aming mga pangkat ng data ay nahahati sa dalawang column. Kung sila ay pinaghiwa-hiwalay na linya sa pamamagitan ng linya, ang switch ay kailangang ilipat sa "Sa pamamagitan ng linya" na posisyon.

   Sa mga default na parameter ng output, ang item na "Bagong worksheet" ay nakatakda, iyon ay, ang data ay magiging output sa isa pang sheet. Maaari mong baguhin ang lokasyon sa pamamagitan ng paglipat ng switch. Ito ay maaaring ang kasalukuyang sheet (pagkatapos ay kailangan mong tukuyin ang mga coordinate ng mga cell ng output ng impormasyon) o isang bagong workbook (file).

   Kapag naitakda na ang lahat ng mga setting, mag-click sa pindutang "OK".

Dahil ang lokasyon ng output para sa mga resulta ng pagsusuri ay naiwan bilang default, lumipat kami sa isang bagong sheet. Tulad ng nakikita mo, ang koepisyent ng ugnayan ay ipinahiwatig dito. Naturally, ito ay kapareho ng kapag ginagamit ang unang paraan - 0.97. Ito ay dahil ang parehong mga pagpipilian ay gumaganap ng parehong mga kalkulasyon, maaari lamang silang gawin sa iba't ibang paraan.

Tulad ng nakikita mo, nag-aalok ang Excel application ng dalawang paraan ng pagsusuri ng ugnayan nang sabay-sabay. Ang resulta ng mga kalkulasyon, kung gagawin mo nang tama ang lahat, ay magiging ganap na magkapareho. Ngunit, ang bawat user ay maaaring pumili ng isang mas maginhawang opsyon sa pagkalkula para sa kanya.

Natutuwa kaming natulungan ka naming malutas ang problema.

Tanungin ang iyong tanong sa mga komento, na naglalarawan sa kakanyahan ng problema nang detalyado. Susubukan ng aming mga espesyalista na sumagot nang mabilis hangga't maaari.

Nakatulong ba sa iyo ang artikulong ito?

Ang regression at correlation analysis ay mga istatistikal na pamamaraan ng pananaliksik. Ito ang mga pinakakaraniwang paraan upang ipakita ang pagtitiwala ng isang parameter sa isa o higit pang mga independiyenteng variable.

Sa ibaba sa tiyak praktikal na mga halimbawa Tingnan natin ang dalawang napakasikat na pagsusuring ito sa mga ekonomista. Magbibigay din kami ng isang halimbawa ng pagkuha ng mga resulta kapag pinagsama ang mga ito.

Pagsusuri ng Pagbabalik sa Excel

Ipinapakita ang impluwensya ng ilang mga halaga (independiyente, independyente) sa umaasa na variable. Halimbawa, paano nakadepende ang bilang ng aktibong populasyon sa ekonomiya sa bilang ng mga negosyo, sahod at iba pang mga parameter. O: paano nakakaapekto ang mga dayuhang pamumuhunan, presyo ng enerhiya, atbp. sa antas ng GDP.

Ang resulta ng pagsusuri ay nagpapahintulot sa iyo na i-highlight ang mga priyoridad. At batay sa mga pangunahing kadahilanan, hulaan, planuhin ang pagbuo ng mga priyoridad na lugar, at gumawa ng mga desisyon sa pamamahala.

Nangyayari ang pagbabalik:

• linear (y = a + bx);
• parabolic (y = a + bx + cx2);
• exponential (y = a * exp(bx));
• kapangyarihan (y = a*x^b);
• hyperbolic (y = b/x + a);
• logarithmic (y = b * 1n(x) + a);
• exponential (y = a * b^x).

Tingnan natin ang halimbawa ng konstruksyon modelo ng regression sa Excel at interpretasyon ng mga resulta. Kunin natin ang linear na uri ng regression.

Gawain. Sa 6 na negosyo, ang average na buwanan sahod at ang bilang ng mga empleyadong umalis. Ito ay kinakailangan upang matukoy ang pag-asa ng bilang ng mga humihinto sa mga empleyado sa average na suweldo.

Modelo linear regression ay may sumusunod na anyo:

Y = a0 + a1x1 +…+akhk.

Kung ang a ay mga coefficient ng regression, ang x ay nakakaimpluwensya sa mga variable, ang k ay ang bilang ng mga salik.

Sa aming halimbawa, ang Y ay ang tagapagpahiwatig ng pagtigil sa mga empleyado. Ang salik na nakakaimpluwensya ay sahod (x).

Ang Excel ay may mga built-in na function na makakatulong sa iyong kalkulahin ang mga parameter ng isang linear regression model. Ngunit gagawin ito ng add-on na "Analysis Package" nang mas mabilis.

Ina-activate namin ang isang malakas na tool sa pagsusuri:

1. I-click ang button na "Office" at pumunta sa tab na "Excel Options". "Mga Add-on".
2. Sa ibaba, sa ilalim ng drop-down na listahan, sa field na "Pamahalaan" magkakaroon ng inskripsyon na "Excel Add-in" (kung wala ito, mag-click sa checkbox sa kanan at piliin). At ang pindutang "Go". I-click.
3. Magbubukas ang isang listahan ng mga available na add-on. Piliin ang “Analysis package” at i-click ang OK.

Kapag na-activate na, magiging available ang add-on sa tab na Data.

Ngayon gawin natin ang pagsusuri ng regression mismo.

1. Buksan ang menu ng tool na "Pagsusuri ng Data". Piliin ang "Regression".
2. Magbubukas ito ng menu para sa pagpili ng mga halaga ng input at mga opsyon sa output (kung saan ipapakita ang resulta). Sa mga field para sa paunang data, ipinapahiwatig namin ang hanay ng inilarawang parameter (Y) at ang salik na nakakaimpluwensya dito (X). Ang natitira ay hindi kailangang punan.
3. Pagkatapos ng pag-click sa OK, ipapakita ng programa ang mga kalkulasyon sa isang bagong sheet (maaari kang pumili ng agwat na ipapakita sa kasalukuyang sheet o magtalaga ng output sa isang bagong workbook).

Una sa lahat, binibigyang pansin natin ang R-squared at coefficients.

Ang R-squared ay ang koepisyent ng determinasyon. Sa aming halimbawa - 0.755, o 75.5%. Nangangahulugan ito na ang mga kinakalkula na parameter ng modelo ay nagpapaliwanag ng 75.5% ng ugnayan sa pagitan ng mga pinag-aralan na parameter. Kung mas mataas ang koepisyent ng pagpapasiya, mas mahusay ang modelo. Mabuti - higit sa 0.8. Masama – mas mababa sa 0.5 (halos hindi maituturing na makatwiran ang naturang pagsusuri). Sa aming halimbawa - "hindi masama".

Ang koepisyent na 64.1428 ay nagpapakita kung ano ang magiging Y kung ang lahat ng mga variable sa modelong isinasaalang-alang ay katumbas ng 0. Ibig sabihin, ang halaga ng nasuri na parameter ay naiimpluwensyahan din ng iba pang mga kadahilanan na hindi inilarawan sa modelo.

Ang koepisyent -0.16285 ay nagpapakita ng bigat ng variable X sa Y. Ibig sabihin, ang average na buwanang suweldo sa loob ng modelong ito ay nakakaapekto sa bilang ng mga umalis na may timbang na -0.16285 (ito ay isang maliit na antas ng impluwensya). Ang sign na "-" ay nagpapahiwatig ng isang negatibong epekto: kung mas mataas ang suweldo, mas kaunting mga tao ang huminto. Alin ang patas.

Pagsusuri ng Kaugnayan sa Excel

Tumutulong ang pagsusuri ng ugnayan na matukoy kung may kaugnayan sa pagitan ng mga tagapagpahiwatig sa isa o dalawang sample. Halimbawa, sa pagitan ng oras ng pagpapatakbo ng isang makina at ang halaga ng pag-aayos, ang presyo ng kagamitan at ang tagal ng operasyon, ang taas at bigat ng mga bata, atbp.

Kung may koneksyon, ang pagtaas sa isang parameter ay humahantong sa pagtaas (positibong ugnayan) o pagbaba (negatibo) ng isa pa. Ang pagsusuri ng ugnayan ay tumutulong sa analyst na matukoy kung ang halaga ng isang tagapagpahiwatig ay maaaring gamitin upang hulaan posibleng kahulugan isa pa.

Ang koepisyent ng ugnayan ay tinutukoy ng r. Nag-iiba mula +1 hanggang -1. Pag-uuri mga ugnayan Para sa iba't ibang lugar magiging iba. Kapag ang coefficient ay 0 linear dependence ay hindi umiiral sa pagitan ng mga sample.

Tingnan natin kung paano hanapin ang koepisyent ng ugnayan gamit ang Excel.

Upang makahanap ng mga ipinares na coefficient, ginagamit ang CORREL function.

Layunin: Tukuyin kung may kaugnayan sa pagitan ng oras ng pagpapatakbo ng isang lathe at ang halaga ng pagpapanatili nito.

Ilagay ang cursor sa anumang cell at pindutin ang fx button.

1. Sa kategoryang "Istatistika," piliin ang function na CORREL.
2. Argumentong "Array 1" - ang unang hanay ng mga halaga - oras ng pagpapatakbo ng makina: A2:A14.
3. Argumentong "Array 2" - pangalawang hanay ng mga halaga - gastos sa pagkumpuni: B2:B14. I-click ang OK.

Upang matukoy ang uri ng koneksyon, kailangan mong tingnan ang ganap na bilang ng koepisyent (bawat larangan ng aktibidad ay may sariling sukat).

Para sa pagsusuri ng ugnayan ng ilang mga parameter (higit sa 2), mas maginhawang gamitin ang "Pagsusuri ng Data" (ang add-on na "Analysis Package"). Kailangan mong pumili ng ugnayan mula sa listahan at italaga ang array. Lahat.

Ang mga resultang coefficient ay ipapakita sa correlation matrix. ganito:

Pagsusuri ng ugnayan at regression

Sa pagsasagawa, ang dalawang pamamaraan na ito ay madalas na ginagamit nang magkasama.

1. Bumubuo kami ng patlang ng ugnayan: "Insert" - "Diagram" - "Scatter diagram" (pinapayagan kang maghambing ng mga pares). Ang hanay ng mga halaga ay ang lahat ng numeric na data sa talahanayan.
2. Mag-left-click sa anumang punto sa diagram. Tapos tama. Sa menu na bubukas, piliin ang "Magdagdag ng linya ng trend."
3. Magtalaga ng mga parameter para sa linya. Uri - "Linear". Sa ibaba – “Ipakita ang equation sa diagram.”
4. I-click ang "Isara".

Ngayon ang data ng pagsusuri ng regression ay naging nakikita.

1. Buksan ang Excel

2.Gumawa ng mga column ng data. Sa ating halimbawa, isasaalang-alang natin ang kaugnayan, o ugnayan, sa pagitan ng pagsalakay at pagdududa sa sarili sa mga unang baitang. 30 bata ang lumahok sa eksperimento, ang data ay ipinakita sa talahanayan ng Excel:

1 column - numero ng paksa

Column 2 - pagiging agresibo sa mga puntos

Column 3 - pagdududa sa sarili sa mga puntos

3. Pagkatapos ay kailangan mong pumili ng isang walang laman na cell sa tabi ng talahanayan at mag-click sa icon f(x) sa panel ng Excel

4.Bubukas ang function menu, dapat kang pumili sa mga kategorya Istatistika, at pagkatapos ay kabilang sa listahan ng mga function na hanapin ayon sa alpabeto CORREL at i-click ang OK

5. Pagkatapos ay magbubukas ang isang menu ng mga argumento ng function, na magbibigay-daan sa iyong piliin ang mga column ng data na kailangan namin. Para piliin ang unang column pagiging agresibo kailangan mong mag-click sa asul na pindutan sa tabi ng linya Array1

6.Pumili ng data para sa Array1 mula sa hanay pagiging agresibo at i-click ang asul na button sa dialog box

7. Pagkatapos, katulad ng Array 1, i-click ang asul na button sa tabi ng linya Array2

8.Pumili ng data para sa Array2- hanay Pagdududa sa sarili at pindutin muli ang asul na button, pagkatapos ay OK

9. Dito, ang r-Pearson correlation coefficient ay kinakalkula at isinulat sa napiling cell Sa aming kaso, ito ay positibo at humigit-kumulang pantay. Ito ay nagsasalita tungkol sa katamtamang positibo mga koneksyon sa pagitan ng pagiging agresibo at pagdududa sa sarili sa mga unang baitang

kaya, istatistikal na hinuha eksperimento ay magiging: r = 0.225, isang katamtamang positibong ugnayan sa pagitan ng mga variable ay ipinahayag pagiging agresibo At pagdududa sa sarili.

Ang ilang mga pag-aaral ay nangangailangan ng p-level ng kahalagahan ng koepisyent ng ugnayan upang matukoy, gayunpaman Excel program, hindi tulad ng SPSS, ay hindi nagbibigay ng ganoong opsyon. Ayos lang, may mga mesa kritikal na halaga mga ugnayan (A.D. Nasledov).

Maaari ka ring bumuo ng linya ng regression sa Excel at ilakip ito sa mga resulta ng pananaliksik.