Korrelációs táblázat excelben. Példa a korreláció kiszámítására, a lineáris regresszió felépítésére és a két RV függésének hipotézisének tesztelésére szolgáltatásunkkal

Regresszió és korrelációs elemzés– statisztikai kutatási módszerek. Ezek a leggyakoribb módok egy paraméter egy vagy több független változótól való függésének kimutatására.

Lent konkrétan gyakorlati példák Tekintsük ezt a két, a közgazdászok körében igen népszerű elemzést. Példát adunk az eredmények elérésére is, ha ezeket kombináljuk.

Regressziós elemzés Excelben

Megmutatja néhány érték (független, független) hatását a függő változóra. Például, hogyan függ a gazdaságilag aktív népesség száma a vállalkozások számától, a bérektől és egyéb paraméterektől. Vagy: hogyan befolyásolják a GDP szintjét a külföldi befektetések, az energiaárak stb.

Az elemzés eredménye lehetővé teszi a rangsorolást. A főbb tényezők alapján pedig előre jelezni, megtervezni a fejlesztést kiemelt területek vezetői döntések meghozatalára.

Regresszió történik:

• lineáris (y = a + bx);
• parabola (y = a + bx + cx 2);
• exponenciális (y = a * exp(bx));
• teljesítmény (y = a*x^b);
• hiperbolikus (y = b/x + a);
• logaritmikus (y = b * 1n(x) + a);
• exponenciális (y = a * b^x).

Tekintsük a regressziós modell Excelben való felépítésének és az eredmények értelmezésének példáját. Vegyünk egy lineáris típusú regressziót.

Egy feladat. 6 vállalkozásnál a havi átlag bérés a nyugdíjas alkalmazottak száma. Meg kell határozni a nyugdíjba vonult alkalmazottak számának az átlagkeresettől való függését.

Modell lineáris regresszió a következő formája van:

Y \u003d a 0 + a 1 x 1 + ... + a k x k.

Ahol a a regressziós együtthatók, x a befolyásoló változók, és k a tényezők száma.

Példánkban Y a kilépő dolgozók mutatója. A befolyásoló tényező a bérek (x).

Az Excel beépített függvényekkel rendelkezik, amelyek segítségével kiszámítható a lineáris regressziós modell paraméterei. De az Analysis ToolPak bővítmény gyorsabban megteszi.

Aktiváljon egy hatékony elemző eszközt:

Az aktiválás után a kiegészítő elérhető lesz az Adatok lapon.

Most közvetlenül a regressziós elemzéssel fogunk foglalkozni.

Mindenekelőtt az R-négyzetre és az együtthatókra figyelünk.

Az R-négyzet a determinációs együttható. Példánkban ez 0,755, azaz 75,5%. Ez azt jelenti, hogy a modell számított paraméterei 75,5%-ban magyarázzák a vizsgált paraméterek közötti kapcsolatot. Minél nagyobb a determinációs együttható, annál jobb a modell. Jó - 0,8 felett. Gyenge - kevesebb, mint 0,5 (egy ilyen elemzés aligha tekinthető ésszerűnek). Példánkban - "nem rossz".

A 64,1428 együttható azt mutatja meg, hogy mi lesz Y, ha a vizsgált modellben minden változó 0-val egyenlő. Vagyis a modellben nem leírt egyéb tényezők is befolyásolják az elemzett paraméter értékét.

A -0,16285 együttható az X változó súlyát mutatja az Y-n. Vagyis az átlagos havi fizetés ebben a modellben -0,16285 súllyal befolyásolja a kilépők számát (ez kis mértékű befolyást jelent). A „-” jel negatív hatást jelez: minél magasabb a fizetés, annál kevesebb a kilépés. Ami tisztességes.



Korrelációelemzés Excelben

A korrelációs elemzés segít megállapítani, hogy egy vagy két mintában van-e kapcsolat a mutatók között. Például a gép üzemideje és a javítási költség között, a felszerelések ára és a működés időtartama, a gyerekek magassága és súlya stb.

Ha van kapcsolat, akkor az egyik paraméter növekedése a másikban növekedést (pozitív korrelációt) vagy csökkenést (negatív) eredményez-e. A korrelációs elemzés segít az elemzőnek eldönteni, hogy az egyik mutató értéke előre jelezheti-e egy másik mutató lehetséges értékét.

A korrelációs együtthatót r-vel jelöljük. +1 és -1 között változik. Összefüggések osztályozása a különböző területeken más lesz. Ha az együttható értéke 0, nincs lineáris kapcsolat a minták között.

Fontolja meg, hogyan használja az Excelt a korrelációs együttható meghatározásához.

A CORREL függvény a párosított együtthatók megkeresésére szolgál.

Feladat: Állapítsa meg, van-e összefüggés a munkaidő között! esztergapadés karbantartásának költségét.

Helyezze a kurzort bármelyik cellába, és nyomja meg az fx gombot.

1. A "Statisztika" kategóriában válassza ki a CORREL funkciót.
2. "1. tömb" argumentum - az első értéktartomány - a gép ideje: A2: A14.
3. A "2. tömb" argumentum - a második értéktartomány - a javítási költségek: B2:B14. Kattintson az OK gombra.

A kapcsolat típusának meghatározásához meg kell nézni az együttható abszolút számát (minden tevékenységi területnek saját skálája van).

Számos paraméter (több mint 2) korrelációs elemzéséhez kényelmesebb az "Adatelemzés" ("Analysis Package" kiegészítő) használata. A listában ki kell választania egy korrelációt, és ki kell jelölnie egy tömböt. Összes.

Az eredményül kapott együtthatók a korrelációs mátrixban jelennek meg. Mint ez:

Korrelációs-regressziós elemzés

A gyakorlatban ezt a két technikát gyakran együtt alkalmazzák.

Példa:

Most már láthatóak a regressziós elemzés adatai.

A „korreláció” latinul „korrelációt”, „kapcsolatot” jelent. A kapcsolat mennyiségi jellemzőjét a korrelációs együttható kiszámításával kaphatjuk meg. Ez népszerű itt statisztikai elemzések az együttható megmutatja, hogy vannak-e összefüggések valamely paraméterrel (például magasság és súly; intelligenciaszint és tanulmányi teljesítmény; sérülések száma és munkaórák).

A korreláció használata

A korrelációszámítást különösen széles körben alkalmazzák a közgazdaságtanban, a szociológiai kutatásokban, az orvostudományban és a biometriában – mindenhol, ahol két olyan adathalmazt kaphatunk, amelyek között összefüggés található.

A korrelációt manuálisan is kiszámíthatja egyszerű aritmetikai műveletek végrehajtásával. A számítási folyamat azonban nagyon időigényes, ha az adatkészlet nagy. A módszer sajátossága, hogy gyűjtést igényel egy nagy szám forrásadatokat, hogy a legpontosabban jelenítse meg, hogy van-e kapcsolat a funkciók között. Ezért a korrelációelemzés komoly alkalmazása lehetetlen számítástechnika nélkül. Az egyik legnépszerűbb és legolcsóbb program ennek a problémának a megoldására.

Hogyan végezzünk korrelációt Excelben?

A korreláció meghatározásának legidőigényesebb lépése az adathalmaz. Az összehasonlítandó adatok általában két oszlopba vagy sorban vannak elrendezve. A táblázatot úgy kell elkészíteni, hogy a cellákban hézagok ne legyenek. Modern változatok Az Excel (2007-től és fiatalabbaktól) nem igényel további beállításokat a statisztikai számításokhoz; A szükséges manipulációk elvégezhetők:

1. Válasszon ki egy üres cellát, amelyben a számítás eredménye megjelenik.
2. Kattintson a "Képletek" elemre az Excel főmenüjében.
3. A „Funkciókönyvtárban” csoportosított gombok közül válassza az „Egyéb funkciók” lehetőséget.
4. A legördülő listákban válassza ki a korreláció számítási funkciót (Statistical - CORREL).
5. Az Excel megnyitja a Függvényargumentumok panelt. Az "1. tömb" és a "2. tömb" az összehasonlított adatok tartományai. E mezők automatikus kitöltéséhez egyszerűen kiválaszthatja a kívánt táblázatcellákat.
6. Kattintson az OK gombra a függvényargumentumok ablakának bezárásához. A számított korrelációs együttható megjelenik a cellában.

A korreláció lehet közvetlen (ha az együttható nagyobb, mint nulla) és inverz (-1-től 0-ig).

Az első azt jelenti, hogy az egyik paraméter növekedésével a másik is növekszik. Az inverz (negatív) korreláció azt a tényt tükrözi, hogy az egyik változó növekedésével a másik csökken.

A korreláció közel nullához lehet. Ez általában azt jelzi, hogy a vizsgált paraméterek nem kapcsolódnak egymáshoz. De néha nulla korreláció lép fel, ha olyan sikertelen mintát készítünk, amely nem tükrözi a kapcsolatot, vagy a kapcsolat összetett, nemlineáris jellegű.

Ha az együttható közepes vagy erős összefüggést mutat (±0,5 és ±0,99 között), akkor ne feledjük, hogy ez csak egy statisztikai összefüggés, amely egyáltalán nem garantálja az egyik paraméter hatását a másikra. Az sem zárható ki, hogy mindkét paraméter független egymástól, de valamilyen harmadik nem figyelembe vett tényező befolyásolja őket. Az Excel segít a korrelációs együttható azonnali kiszámításában, de általában csak kvantitatív módszerek nem elegendő az ok-okozati összefüggések megállapításához összehasonlítható mintákban.

A korrelációs együtthatót (vagy lineáris korrelációs együtthatót) "r"-ként jelöljük (in ritka esetek mint "ρ"), és két vagy több változó lineáris korrelációját (vagyis valamilyen érték és irány által adott összefüggést) jellemzi. Az együttható értéke -1 és +1 között van, azaz a korreláció lehet pozitív és negatív is. Ha a korrelációs együttható -1, akkor tökéletes negatív korreláció van; ha a korrelációs együttható +1, akkor tökéletes pozitív korreláció van. Más esetekben a két változó között pozitív vagy negatív korreláció van, vagy nincs korreláció. A korrelációs együttható kiszámítható manuálisan, ingyenes online számológépekkel vagy egy jó grafikus számológéppel.

Lépések

A korrelációs együttható manuális kiszámítása

Adatgyűjtés. Mielőtt elkezdené a korrelációs együttható számítását, vizsgálja meg az adott számpárt. Jobb, ha egy függőlegesen vagy vízszintesen elhelyezhető táblázatba írjuk le őket. Minden sort vagy oszlopot "x" és "y" betűvel jelöljön meg.

• Például adott négy értékpár (szám) az "x" és "y" változókhoz. A következő táblázatot hozhatja létre:
  • x || y
  • 1 || 1
  • 2 || 3
  • 4 || 5
  • 5 || 7

1. Számítsd ki az "x" számtani átlagot! Ehhez adja össze az „x” összes értékét, majd ossza el az eredményt az értékek számával.

   • Példánkban négy értéket kapunk az "x" változóhoz. Az "x" számtani átlag kiszámításához adja össze ezeket az értékeket, majd ossza el az összeget 4-gyel. A számításokat a következőképpen írjuk le:
   • μ 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. Keresse meg az "y" számtani átlagát. Ehhez kövesse ugyanazokat a lépéseket, azaz adja össze az „y” összes értékét, majd ossza el az összeget az értékek számával.

   • Példánkban négy értéket kapunk az "y" változóhoz. Adja hozzá ezeket az értékeket, majd ossza el az összeget 4-gyel. A számításokat a következőképpen írjuk le:
   • μ 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. Számítsa ki az "x" szórását. Miután kiszámolta x és y átlagát, keresse meg ezeknek a változóknak a szórását. A szórást a következő képlet segítségével számítjuk ki:

   • σ 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. Számítsa ki az "y" szórást. Kövesse az előző lépés lépéseit. Használja ugyanazt a képletet, de helyettesítse be az "y" értékeket.

   • Példánkban a számításokat a következőképpen írjuk le:
   • σ 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. Írja fel a korrelációs együttható kiszámításának alapképletét! Ez a képlet tartalmazza az átlagokat, a szórásokat és mindkét változó számpárjainak számát (n). A korrelációs együtthatót "r"-ként jelöljük (ritka esetekben "ρ"-ként). Ez a cikk a képletet használja a Pearson-korrelációs együttható kiszámításához.

   • Itt és más forrásokban a mennyiségek különböző módon jelölhetők. Például egyes képletekben „ρ” és „σ”, míg másokban „r” és „s”. Egyes tankönyvek más képleteket is adnak, de ezek a fenti képlet matematikai megfelelői.

6. Kiszámolta mindkét változó átlagát és szórását, így a képlet segítségével kiszámíthatja a korrelációs együtthatót. Emlékezzünk vissza, hogy "n" mindkét változó értékpárjainak száma. Az egyéb mennyiségek értékét korábban kiszámoltuk.

   • Példánkban a számításokat a következőképpen írjuk le:
   • ρ = (1 n − 1) Σ (x − μ x σ x) ∗ (y − μ y σ y) (\displaystyle \rho =\left((\frac (1)(n-1))\right) \Sigma \left((\frac (x-\mu _(x))(\sigma _(x)))\right)*\left((\frac (y-\mu _(y))(\sigma _(y)))\jobbra))
   • ρ = (1 3) ∗ (\displaystyle \rho =\left((\frac (1)(3))\right)*)[ (1 - 3 1 , 83) ∗ (1 - 4 2 , 58) + (2 - 3 1 , 83) ∗ (3 - 4 2 , 58) (\displaystyle \left((\frac (1-3)() 1,83))\right)*\left((\frac (1-4)(2,58))\right)+\left((\frac (2-3)(1,83))\right) *\left((\) frac (3-4)(2,58))\jobbra))
     + (4 - 3 1 , 83) ∗ (5 - 4 2, 58) + (5 - 3 1 , 83) ∗ (7 - 4 2, 58) (\displaystyle +\left((\frac (4-3) )(1.83))\jobb)*\bal((\frac (5-4)(2.58))\jobb)+\bal((\frac (5-3)(1.83))\jobb)*\bal( (\frac (7-4)(2,58))\jobbra))]
   • ρ = (1 3) ∗ (6 + 1 + 1 + 6 4 , 721) (\displaystyle \rho =\left((\frac (1)(3))\right)*\left((\frac (6) +1+1+6)(4721))\jobbra))
   • ρ = (1 3) ∗ 2 , 965 (\displaystyle \rho =\left((\frac (1)(3))\right)*2,965)
   • ρ = (2 , 965 3) (\displaystyle \rho =\left((\frac (2,965)(3))\jobbra)
   • ρ = 0,988 (\displaystyle\rho =0,988)

7. Elemezze az eredményt. Példánkban a korrelációs együttható 0,988. Ez az érték valamilyen módon jellemzi a számpárok adott halmazát. Ügyeljen az érték előjelére és nagyságára.

   • Mivel a korrelációs együttható értéke pozitív, pozitív korreláció van az "x" és "y" változók között. Vagyis ha az "x" értéke nő, akkor az "y" értéke is nő.
   • Mivel a korrelációs együttható értéke nagyon közel van a +1-hez, az x és y változók értékei erősen korrelálnak. Ha pontokat teszel rá Koordináta sík, valamilyen egyenes vonal közelében helyezkednek el.

   Online számológépek használata a korrelációs együttható kiszámításához

   1. Keressen egy számológépet az interneten a korrelációs együttható kiszámításához. Ezt az együtthatót gyakran számítják ki a statisztikákban. Ha sok számpár van, akkor gyakorlatilag lehetetlen manuálisan kiszámítani a korrelációs együtthatót. Ezért vannak online számológépek a korrelációs együttható kiszámításához. A keresőbe írja be a "korrelációs együttható kalkulátor" kifejezést (idézőjelek nélkül).

   2. Adja meg az adatokat. Az adatok (számpárok) helyes beviteléhez olvassa el az oldalon található utasításokat. Rendkívül fontos a megfelelő számpárok megadása; különben rossz eredményt kap. Ne feledje, hogy a különböző webhelyek eltérő adatbeviteli formátumokkal rendelkeznek.

      • Például a http://ncalculators.com/statistics/correlation-coefficient-calculator.htm webhelyen az "x" és az "y" változók értékei két vízszintes sorban vannak megadva. Az értékek vesszővel vannak elválasztva. Ez azt jelenti, hogy példánkban az "x" értékei a következőképpen vannak megadva: 1,2,4,5, az "y" értékei pedig így: 1,3,5,7.
      • Egy másik oldalon, http://www.alcula.com/calculators/statistics/correlation-coefficient/ , az adatok függőlegesen kerülnek bevitelre; ebben az esetben ne keverjük össze a megfelelő számpárokat.

   3. Számítsa ki a korrelációs együtthatót! Az adatok megadása után egyszerűen kattintson a "Számítás", "Kiszámítás" vagy hasonló gombra az eredmény eléréséhez.

      Grafikus számológép használata

      1. Adja meg az adatokat. Fogjon egy grafikus számológépet, váltson statisztikai számítási módba, és válassza a Szerkesztés parancsot.

         • Különböző számológépeken különböző gombokat kell megnyomnia. Ez a cikk a Texas Instruments TI-86 számológépre összpontosít.
         • A statisztikai számítási módra váltáshoz nyomja meg a - Stat gombot (a "+" gomb felett). Ezután nyomja meg az F2 - Szerkesztés (Szerkesztés) gombot.

      2. Törölje a korábbi mentett adatokat. A legtöbb számológép megőrzi a beírt statisztikákat, amíg nem törli azokat. A régi adatok új adatokkal való összekeverésének elkerülése érdekében először törölje a tárolt információkat.

         • A nyílbillentyűkkel mozgassa a kurzort, és jelölje ki az „xStat” címsort. Ezután nyomja meg a Clear és az Enter billentyűt az xStat oszlopba beírt összes érték törléséhez.
         • A nyílbillentyűkkel jelölje ki az "yStat" címsort. Ezután nyomja meg a Törlés és az Enter billentyűt az yStat oszlopba beírt összes érték törléséhez.

      3. Adja meg a kezdeti adatokat. A nyílbillentyűkkel vigye a kurzort az "xStat" fejléc alatti első cellára. Írja be az első értéket, és nyomja meg az Enter billentyűt. A képernyő alján az "xStat (1) = __" jelenik meg, szóköz helyett a beírt értékkel. Az Enter megnyomása után a beírt érték megjelenik a táblázatban, és a kurzor a következő sorra ugrik; ekkor a képernyő alján megjelenik az "xStat(2) = __".

         • Adja meg az "x" változó összes értékét.
         • Miután megadta az x változó összes értékét, a nyílbillentyűkkel navigáljon az yStat oszlophoz, és adja meg az y változó értékeit.
         • Az összes számpár megadása után nyomja meg az Kilépés gombot a képernyő törléséhez és az összesítési módból való kilépéshez.

      4. Számítsa ki a korrelációs együtthatót! Azt jellemzi, hogy az adat milyen közel van valamilyen egyeneshez. A grafikus számológép gyorsan meghatározza a megfelelő egyenest és kiszámítja a korrelációs együtthatót.

         • Kattintson a Stat (Statisztika) - Calc (Számítások) lehetőségre. A TI-86-on nyomja meg a - - gombot.
         • Válassza ki a "Lineáris regresszió" funkciót. A TI-86-on nyomja meg a "LinR" feliratú gombot. A „LinR _” sor villogó kurzorral jelenik meg a képernyőn.
         • Most írja be két változó nevét: xStat és yStat.
           • A TI-86-on nyissa meg a névlistát; ehhez nyomja meg a – – .
           • Az elérhető változók a képernyő alsó sorában jelennek meg. Válassza ki (valószínűleg az F1 vagy az F2 billentyű lenyomásával), írjon be egy vesszőt, majd válassza a lehetőséget.
           • Nyomja meg az Entert a bevitt adatok feldolgozásához.

      5. Elemezze az eredményeket. Az Enter megnyomásával a következő információk jelennek meg a képernyőn:

         • y = a + b x (\displaystyle y=a+bx): egy egyenest leíró függvény. Vegye figyelembe, hogy a függvény nem szabványos formában van írva (y = kx + b).
         • a = (\displaystyle a=). Ez annak a pontnak az y-koordinátája, ahol az egyenes metszi az y tengellyel.
         • b = (\displaystyle b=). azt lejtő egyenes.
         • corr = (\displaystyle (\text(corr))=). Ez a korrelációs együttható.
         • n = (\displaystyle n=). Ez a számítás során használt számpárok száma.

Sok vállalatnál és vállalkozásnál széles körben használt segédprogram. A valóság az, hogy szinte minden dolgozónak ismernie kell bizonyos mértékig az Excelt, mivel ez a program nagyon sokféle feladat megoldására szolgál. A táblázatokkal való munka során gyakran meg kell határozni, hogy bizonyos változók kapcsolódnak-e egymáshoz. Ehhez az úgynevezett korrelációt használják. Ebben a cikkben részletesen megvizsgáljuk, hogyan lehet kiszámítani a korrelációs együtthatót az Excelben. Találjuk ki. Megy!

Kezdjük azzal, hogy mi a korrelációs együttható általában. Két elem közötti kapcsolat mértékét jelzi, és mindig -1-től (erős inverz kapcsolat) 1-ig (erős előremutató kapcsolat) terjed. Ha az együttható 0, ez azt jelzi, hogy nincs kapcsolat az értékek között.

Most, miután foglalkoztunk az elmélettel, térjünk át a gyakorlatra. A változók és az y közötti kapcsolat megkereséséhez használja a Microsoft Excel beépített „CORREL” funkcióját. Ehhez kattintson a függvényvarázsló gombra (a képletmező mellett található). A megnyíló ablakban válassza ki a „CORREL” elemet a függvények listájából. Ezután állítsa be a tartományt a "Tömb1" és a "Tömb2" mezőben. Például a „Tömb1”-nél válassza ki az y értékeket, a „Tömb2”-nél pedig az x értékeket. Ennek eredményeként megkapja a program által kiszámított korrelációs együtthatót.

A következő módszer olyan tanulók számára lesz releváns, akiknek egy adott képlet alapján kell függőséget találniuk. Először is ismernie kell az x és y változók átlagos értékét. Ehhez válassza ki a változó értékeit, és használja az "ÁTLAG" funkciót. Ezután ki kell számítania az egyes x és x avg, valamint az y avg közötti különbséget. A kijelölt cellákba írja be x-x képletek, y-. Ne felejtse el rögzíteni a cellákat átlagos értékekkel. Ezután húzza le a képletet, hogy a többi számra vonatkozzon.

Most, hogy minden szükséges adatunk megvan, kiszámolhatjuk a korrelációt. A kapott különbségeket így szorozzuk meg: (x-x avg) * (y-y avg). Miután megkapta az egyes változók eredményét, összegezze a kapott számokat az autosum függvény segítségével. Így kerül kiszámításra a számláló.

Most térjünk át a nevezőre. A számított különbségeket négyzetre kell emelni. Ehhez egy külön oszlopba írja be a képleteket: (x-x avg) 2 és (y-y avg) 2 . Ezután nyújtsa ki a képleteket a teljes tartományra. Ezután az "AutoSum" gombbal keresse meg az összes oszlop összegét (x és y esetén). Marad a talált összegek szorzása és kivonása belőlük Négyzetgyök. Az utolsó lépés a számláló elosztása a nevezővel. A kapott eredmény a kívánt korrelációs együttható lesz.

Amint láthatja, a Microsoft Excel függvényekkel való helyes munka ismeretében jelentősen leegyszerűsítheti az összetett matematikai kifejezések kiszámításának feladatát. A programban megvalósított eszközöknek köszönhetően egyszerűen pár perc alatt készíthet korrelációelemzést Excelben, ezzel időt és energiát takarít meg. Írja meg a megjegyzésekben, hogy a cikk segített-e megérteni a problémát, kérdezzen mindenről, ami érdekelte a megvitatott témában.

Értesítés! Az Ön konkrét problémájának megoldása hasonlóan fog kinézni ezt a példát, beleértve az alábbi táblázatokat és magyarázó szövegeket, de figyelembe véve az Ön kezdeti adatait ...

Egy feladat:
Van egy kapcsolódó minta 26 értékpárból (x k , y k ):

k	1	2	3	4	5	6	7	8	9	10
x k	25.20000	26.40000	26.00000	25.80000	24.90000	25.70000	25.70000	25.70000	26.10000	25.80000
y k	30.80000	29.40000	30.20000	30.50000	31.40000	30.30000	30.40000	30.50000	29.90000	30.40000

k	11	12	13	14	15	16	17	18	19	20
x k	25.90000	26.20000	25.60000	25.40000	26.60000	26.20000	26.00000	22.10000	25.90000	25.80000
y k	30.30000	30.50000	30.60000	31.00000	29.60000	30.40000	30.70000	31.60000	30.50000	30.60000

k	21	22	23	24	25	26
x k	25.90000	26.30000	26.10000	26.00000	26.40000	25.80000
y k	30.70000	30.10000	30.60000	30.50000	30.70000	30.80000

Ki kell számolni/megépíteni:
- korrelációs együttható;
- tesztelje az X és Y valószínűségi változók függésének hipotézisét α = 0,05 szignifikancia szinten;
- a lineáris regressziós egyenlet együtthatói;
- szórásdiagram (korrelációs mező) és regressziós egyenes grafikon;

MEGOLDÁS:

1. Számítsa ki a korrelációs együtthatót!

A korrelációs együttható két valószínűségi változó kölcsönös valószínűségi hatásának mutatója. Korrelációs együttható Rértékeket vehet át -1 előtt +1 . Ha az abszolút érték közelebb van a 1 , akkor ez bizonyíték erős kapcsolatértékek között, és ha közelebb van 0 - akkor gyenge kapcsolatot vagy annak hiányát jelzi. Ha az abszolút érték R egyenlő eggyel, akkor a mennyiségek közötti funkcionális kapcsolatról beszélhetünk, vagyis egy mennyiség matematikai függvény segítségével kifejezhető egy másikkal.

A korrelációs együtthatót a következő képletekkel számíthatja ki:

n

Σ

k = 1

(x k -M x) 2, y 2 =

M x

=

1 n

n Σ k = 1

x k ,

Az én

=

vagy a képlet szerint Rx,y = M xy - M x M y SxSy (1.4), ahol: M x = 1 n n Σ k = 1 x k , Az én = 1 n n Σ k = 1 y k , Mxy = 1 n n Σ k = 1 x k y k (1,5) S x 2 = 1 n n Σ k = 1 x k 2 - M x 2, S y 2 = 1 n n Σ k = 1 y k 2 - M y 2 (1,6) A gyakorlatban az (1.4) képletet gyakrabban használják a korrelációs együttható kiszámításához, mivel kevesebb számítást igényel. Ha azonban a kovariancia korábban számított cov(X,Y), akkor előnyösebb az (1.1) képlet alkalmazása, mert a kovariancia valós értéke mellett felhasználhatja a közbenső számítások eredményeit is. 1.1 Számítsa ki a korrelációs együtthatót az (1.4) képlet segítségével!, ehhez kiszámítjuk az x k 2, y k 2 és x k y k értékeket és beírjuk az 1. táblázatba. Asztal 1 k x k y k x k 2 y k 2 x ky k 1 2 3 4 5 6 1 25.2 30.8 635.04000 948.64000 776.16000 2 26.4 29.4 696.96000 864.36000 776.16000 3 26.0 30.2 676.00000 912.04000 785.20000 4 25.8 30.5 665.64000 930.25000 786.90000 5 24.9 31.4 620.01000 985.96000 781.86000 6 25.7 30.3 660.49000 918.09000 778.71000 7 25.7 30.4 660.49000 924.16000 781.28000 8 25.7 30.5 660.49000 930.25000 783.85000 9 26.1 29.9 681.21000 894.01000 780.39000 10 25.8 30.4 665.64000 924.16000 784.32000 11 25.9 30.3 670.81000 918.09000 784.77000 12 26.2 30.5 686.44000 930.25000 799.10000 13 25.6 30.6 655.36000 936.36000 783.36000 14 25.4 31 645.16000 961.00000 787.40000 15 26.6 29.6 707.56000 876.16000 787.36000 16 26.2 30.4 686.44000 924.16000 796.48000 17 26 30.7 676.00000 942.49000 798.20000 18 22.1 31.6 488.41000 998.56000 698.36000 19 25.9 30.5 670.81000 930.25000 789.95000 20 25.8 30.6 665.64000 936.36000 789.48000 21 25.9 30.7 670.81000 942.49000 795.13000 22 26.3 30.1 691.69000 906.01000 791.63000 23 26.1 30.6 681.21000 936.36000 798.66000 24 26 30.5 676.00000 930.25000 793.00000 25 26.4 30.7 696.96000 942.49000 810.48000 26 25.8 30.8 665.64000 948.64000 794.64000 1.2. Kiszámítjuk M x-et az (1.5) képlettel. 1.2.1. x k x 1 + x 2 + ... + x 26 = 25,20000 + 26,40000 + ... + 25,80000 = 669,500000 1.2.2. 669.50000 / 26 = 25.75000 M x = 25,750000 1.3. Hasonlóképpen számítjuk ki M y-t. 1.3.1. Adjuk hozzá az összes elemet egymás után y k y 1 + y 2 + … + y 26 = 30,80000 + 29,40000 + ... + 30,80000 = 793,000000 1.3.2. A kapott összeget elosztjuk a mintaelemek számával 793.00000 / 26 = 30.50000 M y = 30,500000 1.4. Hasonlóképpen számítjuk ki az M xy-t. 1.4.1. Sorban hozzáadjuk az 1. táblázat 6. oszlopának összes elemét 776.16000 + 776.16000 + ... + 794.64000 = 20412.830000 1.4.2. A kapott összeget osszuk el az elemek számával 20412.83000 / 26 = 785.10885 M xy = 785,108846 1.5. Számítsa ki S x 2 értékét az (1.6.) képlet segítségével!. 1.5.1. Sorban hozzáadjuk az 1. táblázat 4. oszlopának összes elemét 635.04000 + 696.96000 + ... + 665.64000 = 17256.910000 1.5.2. A kapott összeget osszuk el az elemek számával 17256.91000 / 26 = 663.72731 1.5.3. Az utolsó számból kivonjuk az M x érték négyzetét, és megkapjuk az S x 2 értékét S x 2 = 663.72731 - 25.75000 2 = 663.72731 - 663.06250 = 0.66481 1.6. Számítsa ki S y 2 értékét az (1.6.) képlettel!. 1.6.1. Sorban hozzáadjuk az 1. táblázat 5. oszlopának összes elemét 948.64000 + 864.36000 + ... + 948.64000 = 24191.840000 1.6.2. A kapott összeget osszuk el az elemek számával 24191.84000 / 26 = 930.45538 1.6.3. Az utolsó számból kivonva M y négyzetét, megkapjuk S y 2 értékét S y 2 = 930.45538 - 30.50000 2 = 930.45538 - 930.25000 = 0.20538 1.7. Számítsuk ki S x 2 és S y 2 szorzatát. S x 2 S y 2 = 0,66481 0,20538 = 0,136541 1.8. Kivonjuk az utolsó szám négyzetgyökét, megkapjuk az S x S y értéket. S x Sy = 0,36951 1.9. Számítsa ki a korrelációs együttható értékét az (1.4.) képlet alapján!. R = (785,10885 - 25,75000 30,50000) / 0,36951 = (785,10885 - 785,37500) / 0,36951 = -0,72028 VÁLASZ: Rx,y = -0,720279 2. Ellenőrizzük a korrelációs együttható szignifikanciáját (ellenőrizzük a függőségi hipotézist). Mivel a korrelációs együttható becslése véges mintán történik, és ezért eltérhet az általános értékétől, szükséges a korrelációs együttható szignifikanciájának ellenőrzése. Az ellenőrzés a t-kritérium segítségével történik: t = Rx,y √ n-2 √ 1 - R 2 x,y (2.1) Véletlenszerű érték t követi a Student-féle t-eloszlást és a t-eloszlás táblázata szerint meg kell találni a kritérium kritikus értékét (t cr.α) adott α szignifikanciaszinten. Ha a (2.1) képlettel számított modulo t kisebbnek bizonyul, mint t cr.α , akkor a közötti függőségek Véletlen változók X és Y nem. Egyébként a kísérleti adatok nem mondanak ellent a valószínűségi változók függésére vonatkozó hipotézisnek. 2.1. Számítsuk ki a t-kritérium értékét a (2.1) képlet alapján, kapjuk: t = -0.72028 √ 26 - 2 √ 1 - (-0.72028) 2 = -5.08680 2.2. Határozzuk meg a t cr.α paraméter kritikus értékét a t-eloszlás táblázatából A kívánt t kr.α érték a szabadságfokok számának megfelelő sor és az adott α szignifikanciaszintnek megfelelő oszlop metszéspontjában található. Esetünkben a szabadsági fokok száma n - 2 = 26 - 2 = 24 és α = 0.05 , amely megfelel a t cr kritérium kritikus értékének.α = 2.064 (lásd a 2. táblázatot) 2. táblázat t-eloszlás A szabadságfokok száma (n - 2) α = 0,1 α = 0,05 α = 0,02 α = 0,01 α = 0,002 α = 0,001 1 6.314 12.706 31.821 63.657 318.31 636.62 2 2.920 4.303 6.965 9.925 22.327 31.598 3 2.353 3.182 4.541 5.841 10.214 12.924 4 2.132 2.776 3.747 4.604 7.173 8.610 5 2.015 2.571 3.365 4.032 5.893 6.869 6 1.943 2.447 3.143 3.707 5.208 5.959 7 1.895 2.365 2.998 3.499 4.785 5.408 8 1.860 2.306 2.896 3.355 4.501 5.041 9 1.833 2.262 2.821 3.250 4.297 4.781 10 1.812 2.228 2.764 3.169 4.144 4.587 11 1.796 2.201 2.718 3.106 4.025 4.437 12 1.782 2.179 2.681 3.055 3.930 4.318 13 1.771 2.160 2.650 3.012 3.852 4.221 14 1.761 2.145 2.624 2.977 3.787 4.140 15 1.753 2.131 2.602 2.947 3.733 4.073 16 1.746 2.120 2.583 2.921 3.686 4.015 17 1.740 2.110 2.567 2.898 3.646 3.965 18 1.734 2.101 2.552 2.878 3.610 3.922 19 1.729 2.093 2.539 2.861 3.579 3.883 20 1.725 2.086 2.528 2.845 3.552 3.850 21 1.721 2.080 2.518 2.831 3.527 3.819 22 1.717 2.074 2.508 2.819 3.505 3.792 23 1.714 2.069 2.500 2.807 3.485 3.767 24 1.711 2.064 2.492 2.797 3.467 3.745 25 1.708 2.060 2.485 2.787 3.450 3.725 26 1.706 2.056 2.479 2.779 3.435 3.707 27 1.703 2.052 2.473 2.771 3.421 3.690 28 1.701 2.048 2.467 2.763 3.408 3.674 29 1.699 2.045 2.462 2.756 3.396 3.659 30 1.697 2.042 2.457 2.750 3.385 3.646 40 1.684 2.021 2.423 2.704 3.307 3.551 60 1.671 2.000 2.390 2.660 3.232 3.460 120 1.658 1.980 2.358 2.617 3.160 3.373 ∞ 1.645 1.960 2.326 2.576 3.090 3.291 2.2. Hasonlítsuk össze a t-kritérium és a t cr abszolút értékét.α A t-kritérium abszolút értéke nem kisebb, mint a kritikus t = 5,08680, tcr.α = 2,064, ezért kísérleti adatok, 0,95 valószínűséggel(1 - α ), ne mondjon ellent a hipotézisnek X és Y valószínűségi változók függéséről. 3. Kiszámoljuk a lineáris regressziós egyenlet együtthatóit. A lineáris regressziós egyenlet egy egyenes egyenlete, amely közelíti (körülbelül leírja) az X és Y valószínűségi változók közötti kapcsolatot. Ha feltételezzük, hogy X szabad és Y függ X-től, akkor a regressziós egyenlet a következőképpen lesz felírva. Y = a + b X (3.1), ahol: b= Rx,y y σ x = Rx,y Sy S x (3.2), a = M y - b M x (3,3) A (3.2) képlettel számított együttható b lineáris regressziós együtthatónak nevezzük. Egyes forrásokban a hívott állandó együttható regresszió és b a változók szerint. Egy adott X értékre vonatkozó Y előrejelzési hibákat a következő képletekkel számítjuk ki: A σ y/x értéket (3.4 képlet) is nevezzük maradék szórása, ez jellemzi Y eltérését a (3.1) egyenlettel leírt regressziós egyenestől X fix (adott) értékénél.

.

S y 2 / S x 2 = 0,20538 / 0,66481 = 0,30894. Kivonjuk a négyzetgyököt az utolsó számból - kapjuk:
Sy/Sx = 0,55582

3.3 Számítsa ki a b együtthatót a (3.2) képlet szerint

b = -0.72028 0.55582 = -0.40035

3.4 Számítsa ki az a együtthatót a (3.3) képlet szerint

a = 30.50000 - (-0.40035 25.75000) = 40.80894

3.5 Becsülje meg a regressziós egyenlet hibáit!.

3.5.1 Kivonjuk a négyzetgyököt S y 2-ből, és megkapjuk:

= 0.31437
3.5.4 Számítsuk ki a relatív hibát a (3.5) képlettel!

δy/x = (0,31437 / 30,50000)100% = 1,03073%

4. Készítsünk egy szórást (korrelációs mezőt) és a regressziós egyenes grafikonját.

A szórásdiagram a megfelelő párok (x k , y k ) grafikus ábrázolása egy síkban lévő pontokként. derékszögű koordináták az X és Y tengellyel.. A korrelációs mező egy kapcsolt (párosított) minta egyik grafikus ábrázolása. Ugyanebben a koordinátarendszerben a regressziós egyenes grafikonja is felrajzolódik. A tengelyeken lévő léptékeket és kiindulási pontokat gondosan meg kell választani, hogy a diagram a lehető legvilágosabb legyen.

4.1. Az X minta minimális és maximális eleme a 18. és 15. elem, x min = 22,10000 és x max = 26,60000.

4.2. Az Y minta minimális és maximális eleme a 2. és a 18. elem, y min = 29,40000 és y max = 31,60000.

4.3. Az abszcissza tengelyen az x 18 = 22,10000 ponttól közvetlenül balra választjuk ki a kezdőpontot, és olyan léptékben, hogy az x 15 = 26,60000 pont illeszkedjen a tengelyre, és a többi pont jól elkülöníthető legyen.

4.4. Az y tengelyen az y 2 = 29,40000 ponttól közvetlenül balra választjuk ki a kezdőpontot, és olyan léptékben, hogy az y 18 = 31,60000 pont illeszkedjen a tengelyre, és a többi pont jól elkülöníthető legyen.

4.5. Az abszcissza tengelyen az x k értékeket, az ordináta tengelyen pedig az y k értékeket helyezzük el.

4.6. Az (x 1, y 1), (x 2, y 2), ..., (x 26, y 26) pontokat a koordinátasíkra helyezzük. Kapunk egy szórást (korrelációs mezőt), amelyet az alábbi ábra mutat.

4.7. Rajzoljunk regressziós egyenest.

Ehhez keresünk két különböző pontot (x r1 , y r1) és (x r2 , y r2) koordinátákkal, amelyek kielégítik a (3.6) egyenletet, tegyük a koordinátasíkra, és húzzunk rajtuk egy vonalat. Vegyük x min = 22,10000 az első pont abszcisszáját. Behelyettesítjük x min értékét a (3.6) egyenletben, megkapjuk az első pont ordinátáját. Így van egy pontunk koordinátákkal (22.10000, 31.96127). Hasonlóképpen megkapjuk a második pont koordinátáit, az x max = 26,60000 értéket állítva abszcisszaként. A második pont a következő lesz: (26.60000, 30.15970).

A regressziós egyenes az alábbi ábrán piros színnel látható

Felhívjuk figyelmét, hogy a regressziós egyenes mindig átmegy X és Y átlagértékeinek pontján, azaz. koordinátákkal (M x , M y).