Korrelatsioonikoefitsiendi arvutamine Excelis (2 lihtsat viisi)

Excel on võimas tööriist, millel on statistikaga töötamisel hämmastavaid funktsioone.

Korrelatsiooni leidmine kahe andmesarja vahel on suurte andmekogumitega töötamisel üks levinumaid statistilisi arvutusi,

Töötasin mõned aastad tagasi finantsanalüütikuna ja kuigi me statistiliste andmetega tihedalt ei tegelenud, tuli korrelatsiooni leidmist siiski üsna sageli teha.

Selles õpetuses näitan teile kahte väga lihtsat viisi arvutage Excelis korrelatsioonikoefitsient. Selleks on juba sisseehitatud funktsioon ja saate kasutada ka andmeanalüüsi tööriistakomplekti.

Nii et alustame!

Mis on korrelatsioonikoefitsient?

Kuna see ei ole statistikatund, lubage mul lühidalt selgitada, mis on korrelatsioonikoefitsient, ja siis liigume edasi jaotisse, kus arvutame Excelis korrelatsioonikoefitsiendi.

Korrelatsioonikordaja on väärtus, mis näitab teile, kui tihedalt on kaks andmesarja omavahel seotud.

Tavaliselt kasutatav näide on 10 inimese kaal ja pikkus rühmas. Kui arvutame nende inimeste pikkuse ja kaalu andmete korrelatsioonikoefitsiendi, saame väärtuse vahemikus -1 kuni 1.

Väärtus, mis on väiksem kui null, näitab negatiivset korrelatsiooni, mis tähendab, et kui kõrgus suureneb, siis kaal väheneb või kui kaal suureneb, siis kõrgus väheneb.

Ja väärtus üle nulli näitab positiivset korrelatsiooni, mis tähendab, et kui kõrgus suureneb, siis kaal suureneb ja kui kõrgus väheneb, siis kaal väheneb.

Mida lähemal on väärtus 1, seda tugevam on positiivne korrelatsioon. Seega näitab väärtus 0,8, et pikkuse ja kaalu andmed on tugevas korrelatsioonis.

Märkus. Korrelatsioonikoefitsiente ja statistikat on erinevat tüüpi, kuid selles õpetuses vaatleme kõige tavalisemat, mis on Pearsoni korrelatsioonikordaja

Nüüd vaatame, kuidas seda korrelatsioonikoefitsienti Excelis arvutada.

Korrelatsioonikordaja arvutamine Excelis

Nagu ma mainisin, on Excelis korrelatsioonikoefitsiendi arvutamiseks paar võimalust.

Kasutades CORREL valemit

CORREL on statistikafunktsioon, mis võeti kasutusele programmis Excel 2007.

Oletame, et teil on allpool näidatud andmekogum, kus soovite arvutada korrelatsioonikoefitsiendi 10 inimese pikkuse ja kaalu vahel.

Allpool on valem, mis seda teeks:

= TÕRGE (B2: B12, C2: C12)

Ülaltoodud funktsioon CORREL võtab kaks argumenti - seeria kõrguse andmepunktidega ja seeria kaalupunktidega.

Ja see ongi kõik!

Niipea kui vajutate sisestusklahvi, teeb Excel kõik taustal olevad arvutused ja annab teile ühe Pearsoni korrelatsioonikoefitsiendi numbri.

Meie näites on see väärtus veidi üle .5, mis näitab, et positiivne korrelatsioon on üsna tugev.

Seda meetodit on kõige parem kasutada, kui teil on kaks seeriat ja soovite ainult korrelatsioonikoefitsienti.

Aga kui teil on mitu seeriat ja soovite teada kõigi nende seeriate korrelatsioonikoefitsienti, võite kaaluda ka Exceli andmeanalüüsi tööriistapaketi kasutamist (kaetud järgmiselt)

Andmeanalüüsi tööriistakomplekti kasutamine

Excelil on andmeanalüüsi tööriistakomplekt, mida saab kasutada erinevate statistiliste väärtuste (sealhulgas korrelatsioonikoefitsiendi) kiireks arvutamiseks.

Kuid andmeanalüüsi tööriistakomplekt on Excelis vaikimisi keelatud. Nii et esimene samm oleks lubada andmeanalüüsi tööriist tagasi ja seejärel kasutada seda Excelis Pearsoni korrelatsioonikoefitsiendi arvutamiseks.

Andmeanalüüsi tööriistakomplekti lubamine

Andmeanalüüsi tööriistakomplekti lubamiseks Excelis toimige järgmiselt.

1. Klõpsake vahekaarti Fail
2. Klõpsake suvandil Valikud
3. Avanevas dialoogiboksis Exceli suvandid klõpsake külgriba paanil valikut Lisandmoodulid
4. Valige rippmenüüst Halda, Exceli lisandmoodulid
5. Klõpsake nuppu Mine. See avab lisandmoodulite dialoogiboksi
6. Kontrollige valikut Analysis Toolpak
7. Klõpsake nuppu OK

Ülaltoodud sammud lisavad Exceli lindi vahekaardile Andmed uue rühma nimega Analüüs. Selles rühmas oleks teil valik Andmete analüüs

Korrelatsioonikordaja arvutamine andmeanalüüsi tööriistakomplekti abil

Nüüd, kui analüüsitööriist on lindil tagasi saadaval, vaatame, kuidas selle abil korrelatsioonikoefitsienti arvutada.

Oletame, et teil on allpool näidatud andmekogum ja soovite teada saada kolme seeria (pikkus ja kaal, pikkus ja sissetulek ning kaal ja sissetulek) vahelist seost.

Allpool on toodud sammud selle tegemiseks.

1. Klõpsake vahekaarti Andmed
2. Klõpsake rühmas Analüüs valikut Andmeanalüüs
3. Avanevas dialoogiboksis Andmeanalüüs klõpsake nuppu "Korrelatsioon"
4. Klõpsake nuppu OK. See avab dialoogiboksi Korrelatsioon
5. Sisestusvahemiku jaoks valige kolm seeriat - sealhulgas päised
6. Veenduge, et jaotises „Rühmitatud” oleks valitud „Veerud”
7. Valige suvand - „Silt esimesel real”. See tagab, et saadud andmetel on samad päised ja tulemustest on palju lihtsam aru saada
8. Valige suvandist Väljund suvand, kuhu soovite saada tabeli. Ma lähen sama töölehe lahtriga G1. Samuti saate valida, kas tulemused kuvatakse uuel töölehel või uuel töövihikul
9. Klõpsake nuppu OK

Niipea kui seda teete, arvutab Excel kõigi seeriate korrelatsioonikoefitsiendi ja annab teile tabeli, nagu allpool näidatud:

Pange tähele, et saadud tabel on staatiline ja seda ei värskendata, kui mõni teie tabeli andmepunkt muutub. Muutuste korral peate uue korrelatsioonikoefitsientide tabeli loomiseks ülaltoodud samme uuesti korrata.

Seega on need kaks kiiret ja lihtsat meetodit korrelatsioonikoefitsiendi arvutamiseks Excelis.

Loodan, et sellest õpetusest oli teile kasu!