Analiza corelației în Excel. Un exemplu de realizare a unei analize de corelație

Analiza corelației este o metodă obișnuită de cercetare utilizată pentru a determina nivelul de dependență al primei valori de a 1-a. Foaia de calcul are un instrument special care vă permite să implementați acest tip de cercetare.

Esența analizei corelației

Este necesar să se determine relația dintre două cantități diferite. Cu alte cuvinte, dezvăluie în ce direcție (mai mică/mai mare) valoarea se modifică în funcție de schimbările din secunda.

Scopul analizei corelației

Dependenţa se stabileşte atunci când începe identificarea coeficientului de corelaţie. Această metodă diferă de analiza de regresie, deoarece există un singur indicator calculat folosind corelația. Intervalul se modifică de la +1 la -1. Dacă este pozitivă, atunci o creștere a primei valori contribuie la o creștere a celei de-a 2-a. Dacă este negativ, atunci o creștere a primei valori contribuie la o scădere a celei de-a doua. Cu cât este mai mare coeficientul, cu atât o valoare mai puternică o afectează pe a doua.

Important! La coeficientul 0, nu există nicio relație între cantități.

Calculul coeficientului de corelare

Să analizăm calculul pe mai multe mostre. De exemplu, există date tabelare, unde cheltuielile pentru promovarea publicității și volumul vânzărilor sunt descrise pe luni în coloane separate. Pe baza tabelului vom afla nivelul de dependență al volumului vânzărilor de banii cheltuiți pentru promovarea publicității.

Metoda 1: Determinarea corelației prin expertul pentru funcții

CORREL – o funcție care vă permite să implementați o analiză de corelație. forma generala - CORREL(masiv1;masiv2). Instructiuni detaliate:

1. Este necesar să selectați celula în care este planificat să afișați rezultatul calculului. Faceți clic pe „Insert Function” situat în stânga câmpului de text pentru a introduce formula.

2. Se deschide Expertul pentru funcții. Aici trebuie să găsești CORREL, faceți clic pe el, apoi pe „OK”.

3. Se deschide fereastra de argumente. În linia „Matrice1” trebuie să introduceți coordonatele intervalelor primei valori. În acest exemplu, aceasta este coloana Valoarea vânzărilor. Trebuie doar să selectați toate celulele care se află în această coloană. În mod similar, trebuie să adăugați coordonatele celei de-a doua coloane la linia „Matrice1”. În exemplul nostru, aceasta este coloana Costuri de publicitate.

4. După ce ați introdus toate intervalele, faceți clic pe butonul „OK”.

Coeficientul a fost afișat în celula care a fost indicată la începutul acțiunilor noastre. Rezultatul obtinut este 0,97. Acest indicator reflectă dependența ridicată a primei valori de a doua.

Metoda 2: Calculați corelația utilizând pachetul de instrumente de analiză

Există o altă metodă pentru a determina corelația. Aici se folosește una dintre funcțiile găsite în pachetul de analiză. Înainte de a-l folosi, trebuie să activați instrumentul. Instructiuni detaliate:

1. Accesați secțiunea „Fișier”.

2. Se va deschide o nouă fereastră, în care trebuie să faceți clic pe secțiunea „Setări”.
3. Faceți clic pe „Suplimente”.
4. Găsim elementul „Management” în partea de jos. Aici trebuie să selectați „Suplimente Excel” din meniul contextual și să faceți clic pe „OK”.

5. S-a deschis o fereastră specială de suplimente. Puneți o bifă lângă elementul „Pachet de analiză”. Facem clic pe „OK”.
6. Activarea a avut succes. Acum să trecem la Date. A apărut blocul „Analiză”, în care trebuie să faceți clic pe „Analiza datelor”.
7. În noua fereastră care apare, selectați elementul „Corelație” și faceți clic pe „OK”.

8. Pe ecran a apărut fereastra de setări de analiză. În linia „Interval de intrare” este necesar să introduceți intervalul absolut tuturor coloanelor care participă la analiză. În acest exemplu, acestea sunt coloanele „Valoarea vânzărilor” și „Costurile de publicitate”. Setările de afișare a ieșirii sunt setate inițial la Foaie de lucru nouă, ceea ce înseamnă că rezultatele vor fi afișate pe o altă foaie. Opțional, puteți modifica locația de ieșire a rezultatului. După ce ați făcut toate setările, faceți clic pe „OK”.

Scorurile finale sunt eliminate. Rezultatul este același ca în prima metodă – 0,97.

Definirea și calculul coeficientului de corelație multiplă în MS Excel

Pentru a identifica nivelul de dependență al mai multor mărimi, se folosesc coeficienți multipli. În viitor, rezultatele sunt rezumate într-un tabel separat, numit matrice de corelație.

Ghid detaliat:

1. În secțiunea „Date”, găsim blocul „Analiză” deja cunoscut și faceți clic pe „Analiza datelor”.

2. În fereastra care apare, faceți clic pe elementul „Corelație” și faceți clic pe „OK”.
3. În linia „Interval de intrare” conducem în intervalul pentru trei sau mai multe coloane din tabelul sursă. Intervalul poate fi introdus manual sau pur și simplu selectați-l cu LMB și va apărea automat în linia dorită. În „Grupare”, selectați metoda de grupare adecvată. În „Parametru de ieșire” se specifică locația în care vor fi afișate rezultatele corelației. Facem clic pe „OK”.

4. Gata! A fost construită matricea de corelație.

Coeficientul de corelație de pereche în Excel

Să ne dăm seama cum să desenăm corect coeficientul de corelare a perechii într-o foaie de calcul Excel.

Calculul coeficientului de corelare a perechilor în Excel

De exemplu, aveți valorile x și y.

X este variabila dependentă și y este independentă. Este necesar să se găsească direcția și puterea relației dintre acești indicatori. Instrucțiuni pas cu pas:

1. Să găsim valorile medii folosind funcția INIMĂ.

2. Să calculăm fiecare х и xavg, у и avg folosind operatorul «-».

3. Înmulțim diferențele calculate.

4. Calculăm suma indicatorilor din această coloană. Numătorul este rezultatul găsit.

5. Calculați numitorii diferenței х и medie x, y и y-mediu. Pentru a face acest lucru, vom efectua pătratul.

6. Folosind funcția AUTOSUMMA, găsiți indicatorii în coloanele rezultate. Facem înmulțiri. Folosind funcția ROOT pătratează rezultatul.

7. Calculăm câtul folosind valorile numitorului și numărătorului.

8. CORREL este o funcție integrată care vă permite să preveniți calculele complexe. Mergem la „Function Wizard”, selectăm CORREL și specificăm matricele de indicatori х и у. Construim un grafic care afișează valorile obținute.

Matricea coeficienților de corelație perechi în Excel

Să analizăm cum să calculăm coeficienții matricilor pereche. De exemplu, există o matrice de patru variabile.

Instrucțiuni pas cu pas:

1. Mergem la „Analiza datelor”, aflată în blocul „Analiză” din fila „Date”. Selectați Corelație din lista care apare.
2. Setăm toate setările necesare. „Interval de intrare” – intervalul tuturor celor patru coloane. „Interval de ieșire” – locul în care dorim să afișăm totalurile. Facem clic pe butonul „OK”.
3. În locul ales a fost construită o matrice de corelație. Fiecare intersecție a unui rând și a unei coloane este un coeficient de corelație. Numărul 1 este afișat când coordonatele se potrivesc.

Funcția CORREL pentru a determina relația și corelația în Excel

CORREL – o funcție folosită pentru a calcula coeficientul de corelație între 2 tablouri. Să ne uităm la patru exemple ale tuturor abilităților acestei funcții.

Exemple de utilizare a funcției CORREL în Excel

Primul exemplu. Există o placă cu informații despre salariile medii ale angajaților companiei pe parcursul a unsprezece ani și cursul de schimb de dolari. Este necesar să se identifice relația dintre aceste două mărimi. Tabelul arată astfel:

Algoritmul de calcul arată astfel:

Scorul afișat este aproape de 1. Rezultat:

Determinarea coeficientului de corelare a impactului acțiunilor asupra rezultatului

Al doilea exemplu. Doi ofertanți au abordat două agenții diferite pentru ajutor cu o promovare de cincisprezece zile. În fiecare zi a fost efectuat un sondaj social, care a determinat gradul de sprijin pentru fiecare solicitant. Orice intervievat putea să aleagă unul dintre cei doi solicitanți sau să se opună tuturor. Este necesar să se stabilească cât de mult a influențat fiecare promovare publicitară gradul de sprijin al solicitanților, care companie este mai eficientă.

Folosind formulele de mai jos, calculăm coeficientul de corelație:

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

Rezultate:

Din rezultatele obținute reiese că gradul de susținere pentru primul solicitant a crescut cu fiecare zi de promovare publicitară, prin urmare, coeficientul de corelare se apropie de 1. La lansarea publicității, celălalt solicitant a avut un număr mare de încredere, iar pentru 1 zile a existat o tendință pozitivă. Apoi gradul de încredere a scăzut și până în a cincisprezecea zi a scăzut sub indicatorii inițiali. Scorurile scăzute sugerează că promovarea a avut un impact negativ asupra suportului. Nu uitați că alți factori concomitenți care nu sunt luați în considerare sub formă de tabel ar putea afecta și indicatorii.

Analiza popularității conținutului prin corelarea vizionărilor și repostărilor video

Al treilea exemplu. O persoană pentru a-și promova propriile videoclipuri pe găzduirea video YouTube utilizează rețelele sociale pentru a face publicitate canalului. El observă că există o oarecare relație între numărul de repostări în rețelele sociale și numărul de vizualizări de pe canal. Este posibil să preziceți performanța viitoare folosind instrumente pentru foi de calcul? Este necesar să se identifice caracterul rezonabil al aplicării ecuației de regresie liniară pentru a prezice numărul de vizualizări video în funcție de numărul de repostări. Tabel cu valori:

Acum este necesar să se determine prezența unei relații între 2 indicatori conform formulei de mai jos:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Relație directă puternică”;”Relație inversă puternică”);”Relație slabă sau deloc”)' class='formula'>

Dacă coeficientul rezultat este mai mare de 0,7, atunci este mai adecvat să se utilizeze funcția de regresie liniară. În acest exemplu, facem:

Acum construim un grafic:

Aplicăm această ecuație pentru a determina numărul de vizualizări la 200, 500 și 1000 de acțiuni: =9,2937*D4-206,12. Obtinem urmatoarele rezultate:

Funcţie PREVIZIUNI vă permite să determinați numărul de vizualizări în acest moment, dacă au existat, de exemplu, două sute cincizeci de repostări. Aplicam: 0,7;PREDICTION(D7;B3:B8;A3:A8);”Valorile nu sunt legate”)' class='formula'>. Obtinem urmatoarele rezultate:

Caracteristici de utilizare a funcției CORREL în Excel

Această funcție are următoarele caracteristici:

1. Celulele goale nu sunt luate în considerare.
2. Celulele care conțin informații de tip boolean și text nu sunt luate în considerare.
3. Negația dublă „-” este folosită pentru a explica valorile logice sub formă de numere.
4. Numărul de celule din matricele studiate trebuie să se potrivească, altfel va fi afișat mesajul #N/A.

Evaluarea semnificației statistice a coeficientului de corelație

Când se testează semnificația unui coeficient de corelație, ipoteza nulă este că indicatorul are valoarea 0, în timp ce alternativa nu o are. Pentru verificare se folosește următoarea formulă:

Concluzie

Analiza corelației într-o foaie de calcul este un proces simplu și automat. Pentru a o efectua, trebuie doar să știți unde se află instrumentele necesare și cum să le activați prin setările programului.