Korrelációelemzés Excelben. Példa korrelációs elemzés elvégzésére

A korrelációelemzés egy elterjedt kutatási módszer, amellyel meghatározható az 1. érték függésének mértéke a 2. értéktől. A táblázatnak van egy speciális eszköze, amely lehetővé teszi az ilyen típusú kutatások végrehajtását.

A korrelációelemzés lényege

Meg kell határozni a kapcsolatot két különböző mennyiség között. Más szóval, megmutatja, hogy a másodperc változásaitól függően melyik irányban (kisebb/nagyobb) változik az érték.

A korrelációelemzés célja

A függőség akkor jön létre, amikor a korrelációs együttható azonosítása megkezdődik. Ez a módszer eltér a regressziós elemzéstől, mivel csak egy mutatót számítanak ki korrelációval. Az intervallum +1-ről -1-re változik. Ha pozitív, akkor az első érték növekedése hozzájárul a 2. érték növekedéséhez. Ha negatív, akkor az 1. érték növekedése hozzájárul a 2. érték csökkenéséhez. Minél nagyobb az együttható, annál erősebb az egyik érték befolyásolja a másodikat.

Fontos! A 0. együtthatónál nincs kapcsolat a mennyiségek között.

A korrelációs együttható számítása

Elemezzük a számítást több mintán. Például vannak táblázatos adatok, ahol a hirdetési promócióra fordított kiadások és az értékesítési volumen hónaponként külön oszlopokban vannak leírva. A táblázat alapján megtudjuk, hogy az értékesítési volumen milyen mértékben függ a reklámozásra fordított pénztől.

1. módszer: Korreláció meghatározása a Függvényvarázslóval

CORREL – egy függvény, amely lehetővé teszi a korrelációs elemzés végrehajtását. Általános forma - CORREL(massiv1;massiv2). Részletes utasítások:

1. Ki kell választani azt a cellát, amelyben a számítás eredményét tervezzük megjeleníteni. A képlet beírásához kattintson a szövegmező bal oldalán található „Funkció beszúrása” gombra.

2. Megnyílik a Funkcióvarázsló. Itt meg kell találni CORREL, kattintson rá, majd az „OK” gombra.

3. Megnyílik az argumentumok ablaka. A „Tömb1” sorban meg kell adni az 1. érték intervallumainak koordinátáit. Ebben a példában ez az Értékesítési érték oszlop. Csak ki kell jelölnie az összes cellát, amely ebben az oszlopban található. Hasonlóképpen hozzá kell adni a második oszlop koordinátáit a „Tömb2” sorhoz. Példánkban ez a hirdetési költségek oszlop.

4. Az összes tartomány megadása után kattintson az „OK” gombra.

Az együttható abban a cellában jelent meg, amelyet a műveleteink elején jeleztünk. A kapott eredmény 0,97. Ez a mutató az első érték nagymértékű függőségét tükrözi a másodiktól.

2. módszer: Számítsa ki a korrelációt az Analysis ToolPak segítségével

Van egy másik módszer a korreláció meghatározására. Itt az elemzési csomagban található egyik funkciót használjuk. Használat előtt aktiválnia kell az eszközt. Részletes utasítások:

1. Lépjen a „Fájl” szakaszba.

2. Megnyílik egy új ablak, amelyben a „Beállítások” szakaszra kell kattintania.
3. Kattintson a „Kiegészítők” elemre.
4. Alul találjuk a „Management” elemet. Itt ki kell választania az „Excel-bővítmények” elemet a helyi menüből, és kattintson az „OK” gombra.

5. Megnyílt egy speciális kiegészítő ablak. Jelölje be az „Analysis Package” elemet. Kattintson az „OK” gombra.
6. Az aktiválás sikeres volt. Most menjünk a Data-hoz. Megjelent az „Elemzés” blokk, amelyben az „Adatelemzés” gombra kell kattintania.
7. A megjelenő új ablakban válassza ki a „Korreláció” elemet, és kattintson az „OK” gombra.

8. A képernyőn megjelent az elemzési beállítások ablak. A „Beviteli intervallum” sorban meg kell adni az elemzésben részt vevő abszolút összes oszlop tartományát. Ebben a példában ezek az „Értékesítési érték” és a „Hirdetési költségek” oszlopok. A kimeneti megjelenítési beállítások kezdetben Új munkalap értékre vannak állítva, ami azt jelenti, hogy az eredmények egy másik lapon jelennek meg. Opcionálisan módosíthatja az eredmény kimeneti helyét. Az összes beállítás elvégzése után kattintson az „OK” gombra.

Megjelentek a végső pontszámok. Az eredmény ugyanaz, mint az első módszernél – 0,97.

Többszörös korrelációs együttható meghatározása és számítása MS Excelben

Több mennyiség függőségi szintjének azonosítására több együtthatót használnak. A jövőben az eredményeket egy külön táblázatban, az úgynevezett korrelációs mátrixban összegzik.

Részletes útmutató:

1. Az „Adatok” részben keressük meg a már ismert „Elemzés” blokkot, és kattintsunk az „Adatelemzés” gombra.

2. A megjelenő ablakban kattintson a „Korreláció” elemre, majd kattintson az „OK” gombra.
3. A „Beviteli intervallum” sorban a forrástábla három vagy több oszlopának intervallumát hajtjuk be. A tartomány manuálisan megadható, vagy egyszerűen kiválasztható az LMB-vel, és automatikusan megjelenik a kívánt sorban. A „Csoportosítás” részben válassza ki a megfelelő csoportosítási módot. Az „Output Parameter” mezőben megadja azt a helyet, ahol a korrelációs eredmények megjelennek. Kattintson az „OK” gombra.

4. Kész! Felépítettük a korrelációs mátrixot.

Párkorrelációs együttható Excelben

Nézzük meg, hogyan kell helyesen rajzolni a pár korrelációs együtthatót egy Excel-táblázatban.

Párkorrelációs együttható számítása Excelben

Például van x és y értéke.

X a függő változó és y a független változó. Meg kell találni ezen mutatók közötti kapcsolat irányát és erősségét. Lépésről-lépésre utasítás:

1. Keressük meg az átlagos értékeket a függvény segítségével SZÍV.

2. Számoljuk ki mindegyiket х и xavg, у и avg a «-» operátor használatával.

3. A számított különbségeket megszorozzuk.

4. Ebben az oszlopban a mutatók összegét számítjuk ki. A számláló a talált eredmény.

5. Számítsa ki a különbség nevezőit! х и x-átlag, y и y-közepes. Ehhez a négyzetre emelést végezzük.

6. A funkció használata AUTOSUMMA, keresse meg a mutatókat a kapott oszlopokban. Szorzást végzünk. A funkció használata שורש négyzetre emeli az eredményt.

7. A hányadost a nevező és a számláló értékéből számítjuk ki.

8. A CORREL egy integrált funkció, amely lehetővé teszi az összetett számítások elkerülését. Lépjünk a „Funkcióvarázslóba”, válasszuk a CORREL lehetőséget, és adjuk meg az indikátortömböket х и у. Készítünk egy grafikont, amely megjeleníti a kapott értékeket.

Páronkénti korrelációs együtthatók mátrixa Excelben

Elemezzük, hogyan számítsuk ki a páros mátrixok együtthatóit. Például van egy négy változóból álló mátrix.

Lépésről lépésre szóló utasítás:

1. Megyünk az „Adatelemzés” részhez, amely az „Adatok” lap „Elemzés” blokkjában található. A megjelenő listából válassza a Korreláció lehetőséget.
2. Minden szükséges beállítást megadunk. „Beviteli intervallum” – mind a négy oszlop intervalluma. „Kimeneti intervallum” – az a hely, ahol a végösszegeket szeretnénk megjeleníteni. Kattintson az „OK” gombra.
3. A kiválasztott helyen korrelációs mátrixot építettünk fel. Egy sor és egy oszlop minden metszéspontja korrelációs együttható. Az 1-es szám jelenik meg, ha a koordináták egyeznek.

CORREL függvény kapcsolat és korreláció meghatározásához Excelben

CORREL – a 2 tömb közötti korrelációs együttható kiszámítására használt függvény. Nézzünk négy példát ennek a függvénynek az összes képességére.

Példák a CORREL függvény használatára az Excelben

Első példa. Van egy tábla a cég alkalmazottainak tizenegy év alatti átlagfizetéséről és a dollár árfolyamáról. Meg kell határozni e két mennyiség közötti kapcsolatot. A táblázat így néz ki:

A számítási algoritmus így néz ki:

A megjelenített pontszám közel 1. Eredmény:

A cselekvések eredményre gyakorolt ​​hatásának korrelációs együtthatójának meghatározása

Második példa. Két ajánlattevő két különböző ügynökséghez fordult segítségért egy tizenöt napos promócióhoz. Minden nap társadalmi közvélemény-kutatást végeztek, amely minden pályázó esetében meghatározta a támogatottság mértékét. Bármely interjúalany választhat egyet a két pályázó közül, vagy ellenezte az összeset. Meg kell határozni, hogy az egyes reklámakciók mennyiben befolyásolták a pályázók támogatásának mértékét, melyik cég a hatékonyabb.

Az alábbi képletekkel kiszámítjuk a korrelációs együtthatót:

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

Eredmények:

A kapott eredményekből kitűnik, hogy az 1. pályázó támogatottsága minden reklámpromóciós nappal nőtt, ezért a korrelációs együttható az 1-hez közelít. 5 napon belül pozitív tendencia volt megfigyelhető. Ezután a bizalom mértéke csökkent, és a tizenötödik napra a kezdeti mutatók alá süllyedt. Az alacsony pontszámok arra utalnak, hogy az előléptetés negatívan befolyásolta a támogatást. Ne feledje, hogy más, táblázatos formában nem figyelembe vett kísérő tényezők is befolyásolhatják a mutatókat.

A tartalom népszerűségének elemzése a videómegtekintések és az újraküldések korrelációja alapján

Harmadik példa. Azok a személyek, akik saját videóikat reklámozzák a YouTube-videótárhelyen, közösségi hálózatokat használnak a csatorna hirdetésére. Észreveszi, hogy van némi kapcsolat a közösségi hálózatokon megjelenő újraküldések száma és a csatorna megtekintései között. Megjósolható-e a jövőbeni teljesítmény táblázatkezelő eszközökkel? Meg kell határozni a lineáris regressziós egyenlet alkalmazásának ésszerűségét a videomegtekintések számának előrejelzésére az újraküldések számától függően. Táblázat értékekkel:

Most meg kell határozni a kapcsolat jelenlétét 2 mutató között az alábbi képlet szerint:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;"Erős közvetlen kapcsolat";"Erős inverz kapcsolat");"Gyenge vagy nincs kapcsolat"' class='formula'>

Ha a kapott együttható nagyobb, mint 0,7, akkor célszerűbb a lineáris regressziós függvény alkalmazása. Ebben a példában a következőket tesszük:

Most egy grafikont készítünk:

Ezt az egyenletet alkalmazzuk a megtekintések számának meghatározására 200, 500 és 1000 megosztás esetén: =9,2937*D4-206,12. A következő eredményeket kapjuk:

Funkció ELŐREJELZÉS lehetővé teszi az aktuális megtekintések számának meghatározását, ha például kétszázötven ismétlés volt. Jelentkezünk: 0,7;PREDICTION(D7;B3:B8;A3:A8);"Az értékek nincsenek összefüggésben"' class='formula'>. A következő eredményeket kapjuk:

A CORREL függvény használatának jellemzői az Excelben

Ez a funkció a következő tulajdonságokkal rendelkezik:

1. Az üres cellákat a rendszer nem veszi figyelembe.
2. A logikai és szöveg típusú információkat tartalmazó cellákat a rendszer nem veszi figyelembe.
3. A „-” kettős negációt a logikai értékek számok formájában történő figyelembevételére használják.
4. A vizsgált tömbök celláinak számának egyeznie kell, ellenkező esetben a #N/A üzenet jelenik meg.

A korrelációs együttható statisztikai szignifikanciájának értékelése

A korrelációs együttható szignifikanciájának tesztelésekor a nullhipotézis az, hogy a mutató értéke 0, míg az alternatíva nem. Az ellenőrzéshez a következő képletet használjuk:

Következtetés

A táblázatos korrelációelemzés egyszerű és automatizált folyamat. Ennek végrehajtásához csak azt kell tudni, hogy a szükséges eszközök hol találhatók, és hogyan aktiválhatók a programbeállításokon keresztül.