Excel ir spēcīgs rīks, kuram ir dažas pārsteidzošas funkcijas un funkcijas, strādājot ar statistiku.
Korelācijas atrašana starp divām datu sērijām ir viens no visizplatītākajiem statistikas aprēķiniem, strādājot ar lielām datu kopām,
Pirms dažiem gadiem es strādāju par finanšu analītiķi, un, lai gan mēs statistikas datos nebijām ļoti iesaistīti, korelācijas atrašana mums bija jādara diezgan bieži.
Šajā apmācībā es jums parādīšu divus patiešām vienkāršus veidus aprēķiniet korelācijas koeficientu programmā Excel. Lai to izdarītu, jau ir iebūvēta funkcija, un jūs varat arī izmantot datu analīzes rīku komplektu.
Tātad, sāksim!
Kāds ir korelācijas koeficients?
Tā kā šī nav statistikas klase, ļaujiet man īsi paskaidrot, kāds ir korelācijas koeficients, un tad mēs pāriesim pie sadaļas, kurā aprēķinām korelācijas koeficientu programmā Excel.
Korelācijas koeficients ir vērtība, kas norāda, cik cieši ir saistītas divas datu sērijas.
Parasti izmantots piemērs ir 10 cilvēku svars un augums grupā. Ja mēs aprēķinām korelācijas koeficientu šo cilvēku auguma un svara datiem, mēs iegūsim vērtību no -1 līdz 1.
Vērtība, kas mazāka par nulli, norāda uz negatīvu korelāciju, kas nozīmē, ka, ja augums palielinās, svars samazinās vai ja svars palielinās, tad augstums samazinās.
Un vērtība, kas lielāka par nulli, norāda uz pozitīvu korelāciju, kas nozīmē, ka, ja augums palielinās, svars palielinās, un, ja augstums samazinās, svars samazinās.
Jo tuvāk vērtība ir 1, jo spēcīgāka ir pozitīvā korelācija. Tātad vērtība .8 norādītu, ka auguma un svara dati ir cieši saistīti.
Piezīme. Pastāv dažāda veida korelācijas koeficienti un statistika, taču šajā apmācībā mēs aplūkosim visizplatītāko, kas ir Pīrsona korelācijas koeficientsTagad redzēsim, kā aprēķināt šo korelācijas koeficientu programmā Excel.
Korelācijas koeficienta aprēķināšana programmā Excel
Kā jau minēju, ir vairāki veidi, kā aprēķināt korelācijas koeficientu programmā Excel.
Izmantojot CORREL formulu
CORREL ir statistikas funkcija, kas tika ieviesta programmā Excel 2007.
Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, lai aprēķinātu korelācijas koeficientu starp 10 cilvēku augumu un svaru.
Zemāk ir formula, kas to darītu:
= CORREL (B2: B12, C2: C12)
Iepriekš minētajai funkcijai CORREL ir divi argumenti - sērija ar augstuma datu punktiem un sērija ar svara datu punktiem.
Un tas arī viss!
Tiklīdz jūs nospiežat taustiņu Enter, programma Excel veic visus aprēķinus, kas nodrošina vienu Pīrsona korelācijas koeficienta skaitli.
Mūsu piemērā šī vērtība ir nedaudz virs .5, kas norāda, ka pastāv diezgan spēcīga pozitīva korelācija.
Šo metodi vislabāk izmantot, ja jums ir divas sērijas un viss, ko vēlaties, ir korelācijas koeficients.
Bet, ja jums ir vairākas sērijas un vēlaties uzzināt visu šo sēriju korelācijas koeficientu, varat arī apsvērt iespēju izmantot datu analīzes rīku pakotni programmā Excel (aplūkota tālāk)
Izmantojot datu analīzes rīku komplektu
Programmai Excel ir datu analīzes rīkkopa, ko var izmantot, lai ātri aprēķinātu dažādas statistikas vērtības (ieskaitot korelācijas koeficienta iegūšanu).
Bet datu analīzes rīku pakotne programmā Excel pēc noklusējuma ir atspējota. Tātad pirmais solis būtu iespējot datu analīzes rīku un pēc tam to izmantot, lai aprēķinātu Pīrsona korelācijas koeficientu programmā Excel.
Datu analīzes rīku pakotnes iespējošana
Tālāk ir norādītas darbības, lai programmā Excel iespējotu datu analīzes rīku pakotni.
- Noklikšķiniet uz cilnes Fails
- Noklikšķiniet uz Opcijas
- Atvērtajā dialoglodziņā Excel opcijas sānjoslas rūtī noklikšķiniet uz opcijas Papildinājumi
- Nolaižamajā izvēlnē Pārvaldīt atlasiet Excel pievienojumprogrammas
- Noklikšķiniet uz Iet. Tas atvērs pievienojumprogrammu dialoglodziņu
- Pārbaudiet opciju Analīzes rīkkopa
- Noklikšķiniet uz Labi
Iepriekš minētās darbības Excel lentes cilnē Dati pievienotu jaunu grupu ar nosaukumu Analīze. Šajā grupā jums būs iespēja Datu analīze
Korelācijas koeficienta aprēķināšana, izmantojot datu analīzes rīku komplektu
Tagad, kad lentē atkal ir pieejams analīzes rīks, redzēsim, kā aprēķināt korelācijas koeficientu, izmantojot to.
Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties noskaidrot korelāciju starp trim sērijām (augums un svars, augums un ienākumi, svars un ienākumi).
Tālāk ir norādītas darbības, kā to izdarīt:
- Noklikšķiniet uz cilnes Dati
- Grupā Analīze noklikšķiniet uz opcijas Datu analīze
- Atvērtajā dialoglodziņā Datu analīze noklikšķiniet uz “Korelācija”
- Noklikšķiniet uz Labi. Tiks atvērts dialoglodziņš Korelācija
- Ievades diapazonam atlasiet trīs sērijas, ieskaitot galvenes
- Sadaļai “Grupēt pēc” pārliecinieties, vai ir atlasīta opcija “Kolonnas”
- Atlasiet opciju - “Iezīme pirmajā rindā”. Tas nodrošinās, ka iegūtajiem datiem būs vienādas galvenes un būs daudz vieglāk saprast rezultātus
- Izvades opcijās izvēlieties, kur vēlaties iegūt tabulu. Es turpināšu ar šūnu G1 tajā pašā darblapā. Varat arī izvēlēties iegūt rezultātus jaunā darblapā vai jaunā darbgrāmatā
- Noklikšķiniet uz Labi
Tiklīdz jūs to izdarīsit, Excel aprēķinās korelācijas koeficientu visām sērijām un sniegs jums tabulu, kā parādīts zemāk:
Ņemiet vērā, ka iegūtā tabula ir statiska un netiks atjaunināta, ja mainīsies kāds no jūsu tabulas datu punktiem. Jebkuru izmaiņu gadījumā jums būs vēlreiz jāatkārto iepriekš minētās darbības, lai izveidotu jaunu korelācijas koeficientu tabulu.
Tātad šīs ir divas ātras un vienkāršas metodes, lai aprēķinātu korelācijas koeficientu programmā Excel.
Es ceru, ka šī apmācība jums šķita noderīga!
