Cum se calculează coeficientul de corelație în Excel (2 moduri ușoare)

Excel este un instrument puternic care are câteva funcții și funcționalități uimitoare atunci când lucrează cu statistici.

Găsirea unei corelații între două serii de date este unul dintre cele mai frecvente calcule statistice atunci când se lucrează cu seturi de date mari,

Lucram ca analist financiar în urmă cu câțiva ani și, deși nu eram foarte implicați în datele statistice, găsirea corelației era ceva ce trebuia să facem destul de des.

În acest tutorial, vă voi arăta două moduri foarte ușoare de a calculați coeficientul de corelație în Excel. Există deja o funcție încorporată pentru a face acest lucru și puteți utiliza, de asemenea, Instrumentul de analiză a datelor.

Asadar, haideti sa începem!

Care este coeficientul de corelație?

Deoarece aceasta nu este o clasă de statistici, permiteți-mi să explic pe scurt care este coeficientul de corelație și apoi vom trece la secțiunea în care calculăm coeficientul de corelație în Excel.

Un coeficient de corelație este o valoare care vă arată cât de strâns sunt legate două serii de date.

Un exemplu frecvent utilizat este greutatea și înălțimea a 10 persoane dintr-un grup. Dacă calculăm coeficientul de corelație pentru datele despre înălțime și greutate pentru aceste persoane, vom obține o valoare între -1 și 1.

O valoare mai mică de zero indică o corelație negativă, ceea ce înseamnă că, dacă înălțimea crește, atunci greutatea scade sau dacă greutatea crește, atunci înălțimea scade.

Și o valoare mai mare de zero indică o corelație pozitivă, ceea ce înseamnă că, dacă înălțimea crește, atunci greutatea crește, iar dacă înălțimea scade, atunci greutatea scade.

Cu cât valoarea este mai aproape de 1, cu atât este mai puternică corelația pozitivă. Deci, o valoare de .8 ar indica faptul că datele despre înălțime și greutate sunt puternic corelate.

Notă: există diferite tipuri de coeficienți de corelație și statistici, dar în acest tutorial ne vom uita la cel mai comun care este coeficientul de corelație Pearson

Acum, să vedem cum să calculăm acest coeficient de corelație în Excel.

Calculul coeficientului de corelație în Excel

După cum am menționat, există câteva moduri în care puteți calcula coeficientul de corelație în Excel.

Folosind formula CORREL

CORREL este o funcție de statistici care a fost introdusă în Excel 2007.

Să presupunem că aveți un set de date așa cum se arată mai jos, unde doriți să calculați coeficientul de corelație dintre înălțimea și greutatea a 10 persoane.

Mai jos este formula care ar face acest lucru:

= CORREL (B2: B12, C2: C12)

Funcția CORREL de mai sus ia două argumente - seria cu punctele de date înălțime și seria cu punctele de date de greutate.

Si asta e!

De îndată ce apăsați Enter, Excel face toate calculele din back-end, oferindu-vă un singur număr de coeficient de corelație Pearson.

În exemplul nostru, această valoare este puțin peste 0,5, ceea ce indică faptul că există o corelație pozitivă destul de puternică.

Această metodă este utilizată cel mai bine dacă aveți două serii și tot ce doriți este coeficientul de corelație.

Dar dacă aveți mai multe serii și doriți să aflați coeficientul de corelație a tuturor acestor serii, atunci puteți lua în considerare și utilizarea pachetului de instrumente de analiză a datelor în Excel (acoperit în continuare)

Folosind Instrumentul de analiză a datelor

Excel are un instrument de analiză a datelor care poate fi utilizat pentru a calcula rapid diferite valori statistice (inclusiv obținerea coeficientului de corelație).

Dar Instrumentul de analiză a datelor este dezactivat în mod implicit în Excel. Deci, primul pas ar fi să activați instrumentul de analiză a datelor înapoi și apoi să îl utilizați pentru a calcula coeficientul de corelație Pearson în Excel.

Activarea Instrumentului de analiză a datelor

Mai jos sunt pașii pentru a activa Instrumentul de analiză a datelor în Excel:

1. Faceți clic pe fila Fișier
2. Faceți clic pe Opțiuni
3. În caseta de dialog Opțiuni Excel care se deschide, faceți clic pe opțiunea Suplimente din panoul barei laterale
4. În meniul drop-down Gestionați, selectați programe de completare Excel
5. Faceți clic pe Go. Aceasta va deschide caseta de dialog a programelor de completare
6. Bifați opțiunea Analysis Toolpak
7. Faceți clic pe Ok

Pașii de mai sus ar adăuga un grup nou în fila Date din panglica Excel numită Analiză. În cadrul acestui grup, veți avea opțiunea de analiză a datelor

Calculul coeficientului de corelație utilizând Instrumentul de analiză a datelor

Acum că aveți instrumentul de analiză disponibil înapoi în panglică, să vedem cum să calculați coeficientul de corelație folosindu-l.

Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să aflați corelația dintre cele trei serii (înălțime și greutate, înălțime și venit și greutate și venit)

Mai jos sunt pașii pentru a face acest lucru:

1. Faceți clic pe fila Date
2. În grupul Analiză, faceți clic pe opțiunea Analiză date
3. În caseta de dialog Analiza datelor care se deschide, faceți clic pe „Corelație”
4. Faceți clic pe OK. Aceasta va deschide caseta de dialog Corelare
5. Pentru intervalul de intrare, selectați cele trei serii - inclusiv anteturile
6. Pentru „Grupat după”, asigurați-vă că este selectat „Coloane”
7. Selectați opțiunea - „Etichetați în primul rând”. Acest lucru vă va asigura că datele rezultate vor avea aceleași anteturi și ar fi mult mai ușor să înțelegeți rezultatele
8. În opțiunile de ieșire, alegeți unde doriți tabelul rezultat. Voi merge cu celula G1 pe aceeași foaie de lucru. De asemenea, puteți alege să obțineți rezultatele într-o nouă foaie de lucru sau într-un nou registru de lucru
9. Faceți clic pe OK

De îndată ce faceți acest lucru, Excel va calcula coeficientul de corelație pentru toate seriile și vă va oferi un tabel așa cum se arată mai jos:

Rețineți că tabelul rezultat este static și nu se va actualiza în cazul în care vreunul dintre punctele de date din tabelul dvs. se modifică. În cazul oricărei modificări, va trebui să repetați din nou pașii de mai sus pentru a genera un nou tabel de coeficienți de corelație.

Deci, acestea sunt două metode rapide și ușoare pentru a calcula coeficientul de corelație în Excel.

Sper că ți s-a părut util acest tutorial!