Analiza e korrelacionit në Excel. Një shembull i kryerjes së një analize korrelacioni

Analiza e korrelacionit është një metodë e zakonshme kërkimore e përdorur për të përcaktuar nivelin e varësisë së vlerës së parë nga e dyta. Tabela ka një mjet të veçantë që ju lejon të zbatoni këtë lloj kërkimi.

Thelbi i analizës së korrelacionit

Është e nevojshme të përcaktohet marrëdhënia midis dy sasive të ndryshme. Me fjalë të tjera, ai zbulon se në cilin drejtim (më i vogël / më i madh) vlera ndryshon në varësi të ndryshimeve në të dytin.

Qëllimi i analizës së korrelacionit

Varësia krijohet kur fillon identifikimi i koeficientit të korrelacionit. Kjo metodë ndryshon nga analiza e regresionit, pasi ka vetëm një tregues të llogaritur duke përdorur korrelacion. Intervali ndryshon nga +1 në -1. Nëse është pozitive, atëherë një rritje në vlerën e parë kontribuon në një rritje në 2. Nëse është negative, atëherë një rritje në vlerën e parë kontribuon në një ulje në vlerën e dytë. Sa më i lartë të jetë koeficienti, aq më e fortë një vlerë ndikon në të dytën.

E rëndësishme! Në koeficientin 0, nuk ka lidhje midis sasive.

Llogaritja e koeficientit të korrelacionit

Le të analizojmë llogaritjen në disa mostra. Për shembull, ka të dhëna tabelare, ku shpenzimet për promovimin e reklamave dhe vëllimi i shitjeve përshkruhen sipas muajve në kolona të veçanta. Bazuar në tabelë, do të zbulojmë nivelin e varësisë së vëllimit të shitjeve nga paratë e shpenzuara për promovimin e reklamave.

Metoda 1: Përcaktimi i korrelacionit përmes magjistarit të funksionit

CORREL - një funksion që ju lejon të zbatoni një analizë korrelacioni. Forma e përgjithshme - CORREL(massiv1;massiv2). Udhëzime të hollësishme:

1. Është e nevojshme të zgjidhni qelizën në të cilën është planifikuar të shfaqet rezultati i llogaritjes. Klikoni "Fut Funksionin" që ndodhet në të majtë të fushës së tekstit për të futur formulën.

2. Hapet magjistari i funksionit. Këtu ju duhet të gjeni KORREL, klikoni mbi të, pastaj në "OK".

3. Hapet dritarja e argumenteve. Në rreshtin "Array1" duhet të futni koordinatat e intervaleve të vlerave 1. Në këtë shembull, kjo është kolona Vlera e Shitjes. Thjesht duhet të zgjidhni të gjitha qelizat që janë në këtë kolonë. Në mënyrë të ngjashme, duhet të shtoni koordinatat e kolonës së dytë në rreshtin "Array2". Në shembullin tonë, kjo është kolona Kostot e Reklamimit.

4. Pasi të keni futur të gjitha intervalet, klikoni në butonin "OK".

Koeficienti u shfaq në qelizën që u tregua në fillim të veprimeve tona. Rezultati i marrë është 0,97. Ky tregues pasqyron varësinë e lartë të vlerës së parë nga e dyta.

Metoda 2: Llogaritni korrelacionin duke përdorur Analizën ToolPak

Ekziston një metodë tjetër për përcaktimin e korrelacionit. Këtu përdoret një nga funksionet që gjenden në paketën e analizës. Para se ta përdorni, duhet të aktivizoni mjetin. Udhëzime të hollësishme:

1. Shkoni te seksioni "File".

2. Do të hapet një dritare e re, në të cilën duhet të klikoni në seksionin "Cilësimet".
3. Klikoni në "Shtesa".
4. Ne gjejmë elementin "Menaxhimi" në fund. Këtu ju duhet të zgjidhni "Excel Add-ins" nga menyja e kontekstit dhe klikoni "OK".

5. Është hapur një dritare e veçantë shtesë. Vendosni një shenjë kontrolli pranë elementit "Paketa e analizës". Ne klikojmë "OK".
6. Aktivizimi ishte i suksesshëm. Tani le të shkojmë te Data. U shfaq blloku "Analiza", në të cilin duhet të klikoni "Analiza e të dhënave".
7. Në dritaren e re që shfaqet, zgjidhni elementin "Correlation" dhe klikoni "OK".

8. Dritarja e cilësimeve të analizës u shfaq në ekran. Në rreshtin "Inputi interval" është e nevojshme të futni gamën e absolutisht të gjitha kolonave që marrin pjesë në analizë. Në këtë shembull, këto janë kolonat "Vlera e shitjes" dhe "Kostot e reklamimit". Cilësimet e shfaqjes së daljes janë vendosur fillimisht në Fletë pune e re, që do të thotë se rezultatet do të shfaqen në një fletë tjetër. Opsionale, mund të ndryshoni vendndodhjen e daljes së rezultatit. Pasi të keni bërë të gjitha cilësimet, klikoni "OK".

Rezultatet përfundimtare kanë dalë. Rezultati është i njëjtë si në metodën e parë - 0,97.

Përkufizimi dhe llogaritja e koeficientit të korrelacionit të shumëfishtë në MS Excel

Për të identifikuar nivelin e varësisë së disa sasive, përdoren koeficientë të shumëfishtë. Në të ardhmen, rezultatet përmblidhen në një tabelë të veçantë, të quajtur matrica e korrelacionit.

Udhëzues i hollësishëm:

1. Në seksionin "Të dhënat", gjejmë bllokun tashmë të njohur "Analiza" dhe klikojmë "Analiza e të dhënave".

2. Në dritaren që shfaqet, klikoni në elementin "Correlation" dhe klikoni "OK".
3. Në rreshtin "Input interval" ne kalojmë në intervalin për tre ose më shumë kolona të tabelës burimore. Gama mund të futet manualisht ose thjesht ta zgjidhni atë me LMB dhe do të shfaqet automatikisht në rreshtin e dëshiruar. Në "Grupim" zgjidhni metodën e duhur të grupimit. Në "Parametri i daljes" specifikon vendndodhjen ku do të shfaqen rezultatet e korrelacionit. Ne klikojmë "OK".

4. Gati! U ndërtua matrica e korrelacionit.

Koeficienti i korrelacionit të çiftit në Excel

Le të kuptojmë se si të vizatojmë saktë koeficientin e korrelacionit të çiftit në një spreadsheet Excel.

Llogaritja e koeficientit të korrelacionit të çiftit në Excel

Për shembull, ju keni vlerat x dhe y.

X është ndryshorja e varur dhe y është e pavarur. Është e nevojshme të gjendet drejtimi dhe forca e marrëdhënies ndërmjet këtyre treguesve. Udhëzim hap pas hapi:

1. Le të gjejmë vlerat mesatare duke përdorur funksionin ZEMRA.

2. Le të llogarisim secilin х и xavg, у и mes duke përdorur operatorin «-».

3. Ne i shumëzojmë diferencat e llogaritura.

4. Ne llogarisim shumën e treguesve në këtë kolonë. Numëruesi është rezultati i gjetur.

5. Llogaritni emëruesit e diferencës х и x-mesatare, y и y-mesatare. Për ta bërë këtë, ne do të kryejmë katrorin.

6. Duke përdorur funksionin AUTOSUMMA, gjeni treguesit në kolonat që rezultojnë. Ne bëjmë shumëzim. Duke përdorur funksionin ROOT katrore rezultatin.

7. Ne llogarisim herësin duke përdorur vlerat e emëruesit dhe numëruesit.

8. CORREL është një funksion i integruar që ju lejon të parandaloni llogaritjet komplekse. Shkojmë te "Magjistari i Funksionit", zgjedhim CORREL dhe specifikojmë grupet e treguesve х и у. Ndërtojmë një grafik që shfaq vlerat e marra.

Matrica e koeficientëve të korrelacionit në çift në Excel

Le të analizojmë se si të llogarisim koeficientët e matricave të çiftëzuara. Për shembull, ekziston një matricë prej katër variablash.

Udhëzim hap pas hapi:

1. Shkojmë te "Analiza e të Dhënave", e vendosur në bllokun "Analiza" të skedës "Të dhënat". Zgjidhni Korrelacionin nga lista që shfaqet.
2. Ne vendosëm të gjitha cilësimet e nevojshme. "Input interval" - intervali i të katër kolonave. “Intervali i daljes” – vendi ku duam të shfaqim totalet. Ne klikojmë në butonin "OK".
3. Një matricë korrelacioni u ndërtua në vendin e zgjedhur. Çdo kryqëzim i një rreshti dhe një kolone është një koeficient korrelacioni. Numri 1 shfaqet kur koordinatat përputhen.

Funksioni CORREL për të përcaktuar marrëdhënien dhe korrelacionin në Excel

CORREL - një funksion i përdorur për të llogaritur koeficientin e korrelacionit midis 2 vargjeve. Le të shohim katër shembuj të të gjitha aftësive të këtij funksioni.

Shembuj të përdorimit të funksionit CORREL në Excel

Shembulli i parë. Ekziston një pllakë me informacione për pagat mesatare të punonjësve të kompanisë gjatë njëmbëdhjetë viteve dhe kursin e këmbimit prej $. Është e nevojshme të identifikohet marrëdhënia midis këtyre dy sasive. Tabela duket si kjo:

Algoritmi i llogaritjes duket si ky:

Rezultati i shfaqur është afër 1. Rezultati:

Përcaktimi i koeficientit të korrelacionit të ndikimit të veprimeve në rezultat

Shembulli i dytë. Dy ofertues iu drejtuan dy agjencive të ndryshme për ndihmë me një promovim pesëmbëdhjetëditor. Çdo ditë bëhej një sondazh social, i cili përcaktonte shkallën e mbështetjes për çdo aplikant. Çdo i intervistuar mund të zgjidhte një nga dy aplikantët ose të kundërshtonte të gjithë. Është e nevojshme të përcaktohet se sa ka ndikuar çdo promovim reklamues në shkallën e mbështetjes për aplikantët, cila kompani është më efikase.

Duke përdorur formulat e mëposhtme, ne llogarisim koeficientin e korrelacionit:

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

Results:

Nga rezultatet e marra, bëhet e qartë se shkalla e mbështetjes për aplikantin e parë rritej me çdo ditë të promovimit të reklamave, prandaj, koeficienti i korrelacionit i afrohet 1. Kur reklamimi u lançua, aplikanti tjetër kishte një numër të madh besimi, dhe për 1 ditë ka pasur një trend pozitiv. Më pas shkalla e besimit u ul dhe në ditën e pesëmbëdhjetë ajo ra nën treguesit fillestarë. Rezultatet e ulëta sugjerojnë se promovimi ka ndikuar negativisht në mbështetjen. Mos harroni se faktorë të tjerë shoqërues që nuk merren parasysh në formë tabelare mund të ndikojnë gjithashtu në tregues.

Analiza e popullaritetit të përmbajtjes nga korrelacioni i shikimeve të videos dhe ripostimeve

Shembulli i tretë. Një person për të promovuar videot e veta në pritjen e videove në YouTube përdor rrjetet sociale për të reklamuar kanalin. Ai vëren se ka një lidhje mes numrit të ripostimeve në rrjetet sociale dhe numrit të shikimeve në kanal. A është e mundur të parashikohet performanca e ardhshme duke përdorur mjetet e tabelave? Është e nevojshme të identifikohet arsyeshmëria e aplikimit të ekuacionit të regresionit linear për të parashikuar numrin e shikimeve të videos në varësi të numrit të ripostimeve. Tabela me vlerat:

Tani është e nevojshme të përcaktohet prania e një marrëdhënieje midis 2 treguesve sipas formulës së mëposhtme:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;"Marrëdhënie e fortë e drejtpërdrejtë";"Marrëdhënie e fortë e kundërt");"Marrëdhënie e dobët ose aspak")' class='formula'>

Nëse koeficienti që rezulton është më i lartë se 0,7, atëherë është më e përshtatshme të përdoret funksioni i regresionit linear. Në këtë shembull, ne bëjmë:

Tani po ndërtojmë një grafik:

Ne e zbatojmë këtë ekuacion për të përcaktuar numrin e shikimeve në 200, 500 dhe 1000 aksione: =9,2937*D4-206,12. Ne marrim rezultatet e mëposhtme:

funksion PARASHIKIM ju lejon të përcaktoni numrin e shikimeve në këtë moment, nëse do të kishte, për shembull, dyqind e pesëdhjetë ripostime. Ne aplikojmë: 0,7;PREDICTION(D7;B3:B8;A3:A8);"Vlerat nuk janë të lidhura")' class='formula'>. Ne marrim rezultatet e mëposhtme:

Karakteristikat e përdorimit të funksionit CORREL në Excel

Ky funksion ka karakteristikat e mëposhtme:

1. Qelizat e zbrazëta nuk merren parasysh.
2. Qelizat që përmbajnë informacione të tipit Boolean dhe Text nuk merren parasysh.
3. Negacioni i dyfishtë "-" përdoret për të llogaritur vlerat logjike në formën e numrave.
4. Numri i qelizave në grupet e studiuara duhet të përputhet, përndryshe do të shfaqet mesazhi #N/A.

Vlerësimi i rëndësisë statistikore të koeficientit të korrelacionit

Gjatë testimit të rëndësisë së një koeficienti korrelacioni, hipoteza zero është se treguesi ka një vlerë 0, ndërsa alternativa jo. Formula e mëposhtme përdoret për verifikim:

Përfundim

Analiza e korrelacionit në një spreadsheet është një proces i thjeshtë dhe i automatizuar. Për ta kryer atë, ju vetëm duhet të dini se ku ndodhen mjetet e nevojshme dhe si t'i aktivizoni ato përmes cilësimeve të programit.