Kaip apskaičiuoti koreliacijos koeficientą „Excel“ (2 paprasti būdai)

„Excel“ yra galingas įrankis, turintis nuostabių funkcijų ir funkcijų dirbant su statistika.

Dviejų duomenų eilučių koreliacijos radimas yra vienas iš labiausiai paplitusių statistinių skaičiavimų dirbant su dideliais duomenų rinkiniais,

Prieš kelerius metus dirbau finansų analitiku ir nors mes nedalyvavome statistiniuose duomenyse, koreliacijos nustatymas vis tiek turėjo būti daromas gana dažnai.

Šioje pamokoje parodysiu du tikrai paprastus būdus apskaičiuoti koreliacijos koeficientą „Excel“. Tam jau yra integruota funkcija, taip pat galite naudoti duomenų analizės įrankių paketą.

Taigi pradėkime!

Koks yra koreliacijos koeficientas?

Kadangi tai nėra statistikos klasė, leiskite trumpai paaiškinti, koks yra koreliacijos koeficientas, o tada pereisime prie skyriaus, kuriame apskaičiuojame koreliacijos koeficientą „Excel“.

Koreliacijos koeficientas yra reikšmė, nurodanti, kaip glaudžiai susijusios dvi duomenų serijos.

Dažniausiai naudojamas pavyzdys yra 10 žmonių svoris ir ūgis grupėje. Jei apskaičiuosime šių žmonių ūgio ir svorio duomenų koreliacijos koeficientą, gausime reikšmę nuo -1 iki 1.

Reikšmė, mažesnė už nulį, rodo neigiamą koreliaciją, o tai reiškia, kad jei aukštis didėja, svoris mažėja arba svoris didėja, tada aukštis mažėja.

O reikšmė daugiau nei nulis rodo teigiamą koreliaciją, o tai reiškia, kad jei ūgis didėja, svoris didėja, o jei aukštis mažėja, svoris mažėja.

Kuo arčiau reikšmė yra 1, tuo stipresnė teigiama koreliacija. Taigi reikšmė .8 reikštų, kad ūgio ir svorio duomenys yra glaudžiai susiję.

Pastaba: Yra įvairių tipų koreliacijos koeficientų ir statistikos, tačiau šioje pamokoje apžvelgsime dažniausiai pasitaikančią, kuri yra Pearsono koreliacijos koeficientas

Dabar pažiūrėkime, kaip apskaičiuoti šį koreliacijos koeficientą „Excel“.

Koreliacijos koeficiento apskaičiavimas „Excel“

Kaip minėjau, „Excel“ koreliacijos koeficientą galite apskaičiuoti keliais būdais.

Naudojant CORREL formulę

„CORREL“ yra statistikos funkcija, įdiegta „Excel 2007“.

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, kur norite apskaičiuoti koreliacijos koeficientą tarp 10 žmonių ūgio ir svorio.

Žemiau yra formulė, kaip tai padaryti:

= CORREL (B2: B12, C2: C12)

Aukščiau pateikta CORREL funkcija turi du argumentus - seriją su aukščio duomenų taškais ir seriją su svorio duomenų taškais.

Štai ir viskas!

Kai tik paspausite „Enter“, „Excel“ atliks visus galutinius skaičiavimus ir gaus vieną Pearsono koreliacijos koeficiento skaičių.

Mūsų pavyzdyje ši vertė yra šiek tiek didesnė nei .5, o tai rodo, kad yra gana stipri teigiama koreliacija.

Šį metodą geriausia naudoti, jei turite dvi serijas ir viskas, ko norite, yra koreliacijos koeficientas.

Bet jei turite kelias serijas ir norite sužinoti visų šių serijų koreliacijos koeficientą, taip pat galite apsvarstyti galimybę naudoti „Excel“ duomenų analizės įrankių paketą (aptariamas toliau)

Duomenų analizės įrankių paketo naudojimas

„Excel“ turi duomenų analizės įrankių paketą, kuriuo galima greitai apskaičiuoti įvairias statistines vertes (įskaitant koreliacijos koeficiento gavimą).

Tačiau duomenų analizės įrankių paketas „Excel“ pagal nutylėjimą yra išjungtas. Taigi pirmasis žingsnis būtų įjungti duomenų analizės įrankį ir tada jį naudoti norint apskaičiuoti „Pearson“ koreliacijos koeficientą „Excel“.

Duomenų analizės įrankių paketo įgalinimas

Toliau pateikiami žingsniai, kaip įgalinti duomenų analizės įrankių paketą „Excel“:

1. Spustelėkite skirtuką Failas
2. Spustelėkite Parinktys
3. Atsidariusiame dialogo lange „Excel“ parinktys spustelėkite šoninės juostos parinktį Papildiniai
4. Išskleidžiamajame meniu Tvarkyti pasirinkite „Excel“ priedai
5. Spustelėkite Eiti. Tai atidarys priedų dialogo langą
6. Patikrinkite „Analysis Toolpak“ parinktį
7. Spustelėkite Gerai

Pirmiau minėti veiksmai pridėtų naują grupę „Excel“ juostos skirtuke „Duomenys“, pavadintą „Analizė“. Šioje grupėje turėsite duomenų analizės parinktį

Koreliacijos koeficiento apskaičiavimas naudojant duomenų analizės įrankių paketą

Dabar, kai juostelėje vėl rasite analizės įrankį, pažiūrėkime, kaip naudojant ją apskaičiuoti koreliacijos koeficientą.

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite sužinoti šių trijų serijų (ūgis ir svoris, aukštis ir pajamos bei svoris ir pajamos) koreliaciją.

Žemiau pateikiami žingsniai, kaip tai padaryti:

1. Spustelėkite skirtuką Duomenys
2. Grupėje Analizė spustelėkite parinktį Duomenų analizė
3. Atsidariusiame dialogo lange Duomenų analizė spustelėkite „Koreliacija“
4. Spustelėkite Gerai. Bus atidarytas dialogo langas Koreliacija
5. Norėdami įvesti diapazoną, pasirinkite tris serijas, įskaitant antraštes
6. Pasirinkę „Grupuoti pagal“ įsitikinkite, kad pasirinkta „Stulpeliai“
7. Pasirinkite parinktį - „Etiketė pirmoje eilutėje“. Tai užtikrins, kad gautų duomenų antraštės būtų vienodos ir būtų daug lengviau suprasti rezultatus
8. Išvesties parinktyse pasirinkite, kur norite gauti lentelę. Aš eisiu su langeliu G1 tame pačiame darbalapyje. Taip pat galite pasirinkti gauti rezultatus naujame darbalapyje arba naujoje darbaknygėje
9. Spustelėkite Gerai

Kai tik tai padarysite, „Excel“ apskaičiuos visų serijų koreliacijos koeficientą ir pateiks lentelę, kaip parodyta žemiau:

Atminkite, kad gauta lentelė yra statiška ir nebus atnaujinta, jei pasikeistų bet kuris jūsų lentelės duomenų taškas. Jei pasikeisite, turėsite dar kartą pakartoti aukščiau nurodytus veiksmus, kad sukurtumėte naują koreliacijos koeficientų lentelę.

Taigi tai yra du greiti ir paprasti metodai koreliacijos koeficientui apskaičiuoti „Excel“.

Tikiuosi, kad ši pamoka jums buvo naudinga!