Korelācijas analīze programmā Excel. Korelācijas analīzes veikšanas piemērs

Korelācijas analīze ir izplatīta pētījuma metode, ko izmanto, lai noteiktu 1. vērtības atkarības līmeni no 2. vērtības. Izklājlapā ir īpašs rīks, kas ļauj īstenot šāda veida pētījumus.

Korelācijas analīzes būtība

Ir nepieciešams noteikt attiecības starp diviem dažādiem lielumiem. Citiem vārdiem sakot, tas atklāj, kādā virzienā (mazāks / lielāks) vērtība mainās atkarībā no izmaiņām sekundē.

Korelācijas analīzes mērķis

Atkarība tiek noteikta, kad sākas korelācijas koeficienta noteikšana. Šī metode atšķiras no regresijas analīzes, jo ir tikai viens rādītājs, kas aprēķināts, izmantojot korelāciju. Intervāls mainās no +1 uz -1. Ja tas ir pozitīvs, tad pirmās vērtības pieaugums veicina otrās vērtības pieaugumu. Ja negatīvs, tad 2. vērtības pieaugums veicina 1. vērtības samazināšanos. Jo lielāks koeficients, jo spēcīgāka viena vērtība ietekmē otro.

Svarīgi! Pie 0. koeficienta nav attiecības starp daudzumiem.

Korelācijas koeficienta aprēķins

Analizēsim aprēķinus vairākos paraugos. Piemēram, ir tabulas dati, kur tēriņi reklāmas veicināšanai un pārdošanas apjoms ir aprakstīti pa mēnešiem atsevišķās ailēs. Pamatojoties uz tabulu, noskaidrosim pārdošanas apjoma atkarības līmeni no reklāmas veicināšanai iztērētās naudas.

1. metode: korelācijas noteikšana, izmantojot funkciju vedni

CORREL – funkcija, kas ļauj īstenot korelācijas analīzi. Vispārējā forma - CORREL(massiv1;massiv2). Sīki izstrādāti norādījumi:

1. Ir jāizvēlas šūna, kurā plānots parādīt aprēķina rezultātu. Noklikšķiniet uz "Ievietot funkciju", kas atrodas pa kreisi no teksta lauka, lai ievadītu formulu.

2. Tiek atvērts funkciju vednis. Šeit jums ir jāatrod KORREĻA, noklikšķiniet uz tā un pēc tam uz “OK”.

3. Tiek atvērts argumentu logs. Rindā “Masīvs1” jāievada 1. vērtību intervālu koordinātas. Šajā piemērā šī ir sleja Pārdošanas vērtība. Jums vienkārši jāatlasa visas šūnas, kas atrodas šajā kolonnā. Tāpat rindai “Array2” jāpievieno otrās kolonnas koordinātas. Mūsu piemērā šī ir sleja Reklāmas izmaksas.

4. Pēc visu diapazonu ievadīšanas noklikšķiniet uz pogas “OK”.

Koeficients tika parādīts šūnā, kas tika norādīta mūsu darbību sākumā. Iegūtais rezultāts ir 0,97. Šis rādītājs atspoguļo pirmās vērtības lielo atkarību no otrās.

2. metode: aprēķiniet korelāciju, izmantojot analīzes rīkkopu

Ir arī cita metode korelācijas noteikšanai. Šeit tiek izmantota viena no analīzes pakotnē atrodamajām funkcijām. Pirms tā izmantošanas jums ir jāaktivizē rīks. Sīki izstrādātas instrukcijas:

1. Dodieties uz sadaļu "Fails".

2. Tiks atvērts jauns logs, kurā jums jānoklikšķina uz sadaļas “Iestatījumi”.
3. Noklikšķiniet uz "Papildinājumi".
4. Apakšā atrodam elementu “Management”. Šeit konteksta izvēlnē atlasiet “Excel pievienojumprogrammas” un noklikšķiniet uz “OK”.

5. Ir atvērts īpašs papildinājumu logs. Atzīmējiet atzīmi blakus elementam “Analīzes pakotne”. Mēs noklikšķinām uz "OK".
6. Aktivizācija bija veiksmīga. Tagad pāriesim uz Dati. Parādījās bloks “Analīze”, kurā jānoklikšķina uz “Datu analīze”.
7. Parādītajā jaunajā logā atlasiet elementu “Korelācija” un noklikšķiniet uz “OK”.

8. Ekrānā parādījās analīzes iestatījumu logs. Rindā “Ievades intervāls” ir jāievada absolūti visu analīzē iesaistīto kolonnu diapazons. Šajā piemērā tās ir slejas “Pārdošanas vērtība” un “Reklāmas izmaksas”. Izvades displeja iestatījumi sākotnēji ir iestatīti uz Jauna darblapa, kas nozīmē, ka rezultāti tiks parādīti citā lapā. Pēc izvēles varat mainīt rezultāta izvades vietu. Pēc visu iestatījumu veikšanas noklikšķiniet uz "OK".

Galīgie rezultāti ir zināmi. Rezultāts ir tāds pats kā pirmajā metodē – 0,97.

Vairāku korelācijas koeficientu definēšana un aprēķināšana programmā MS Excel

Lai noteiktu vairāku lielumu atkarības līmeni, tiek izmantoti vairāki koeficienti. Nākotnē rezultāti tiks apkopoti atsevišķā tabulā, ko sauc par korelācijas matricu.

Detalizēts ceļvedis:

1. Sadaļā “Dati” atrodam jau zināmo “Analīzes” bloku un noklikšķiniet uz “Datu analīze”.

2. Parādītajā logā noklikšķiniet uz elementa "Korelācija" un noklikšķiniet uz "OK".
3. Rindā “Ievades intervāls” tiek ievadīts intervāls trim vai vairākām avota tabulas kolonnām. Diapazonu var ievadīt manuāli vai vienkārši atlasīt to ar LMB, un tas automātiski parādīsies vēlamajā rindā. Sadaļā “Grupēšana” atlasiet atbilstošo grupēšanas metodi. Sadaļā “Izvades parametrs” tiek norādīta vieta, kur tiks parādīti korelācijas rezultāti. Mēs noklikšķinām uz "OK".

4. Gatavs! Tika izveidota korelācijas matrica.

Pāru korelācijas koeficients programmā Excel

Izdomāsim, kā pareizi uzzīmēt pāra korelācijas koeficientu Excel izklājlapā.

Pāru korelācijas koeficienta aprēķināšana programmā Excel

Piemēram, jums ir x un y vērtības.

X ir atkarīgais mainīgais un y ir neatkarīgais. Ir jāatrod šo rādītāju attiecības virziens un stiprums. Soli pa solim instrukcija:

1. Atradīsim vidējās vērtības, izmantojot funkciju SIRDS.

2. Aprēķināsim katru х и xavg, у и avg izmantojot operatoru «-».

3. Mēs reizinām aprēķinātās atšķirības.

4. Mēs aprēķinām šīs kolonnas rādītāju summu. Skaitītājs ir atrastais rezultāts.

5. Aprēķiniet starpības saucējus х и x-vidējais, y и y-vidēja. Lai to izdarītu, mēs veiksim kvadrātu.

6. Izmantojot funkciju AUTOSUMMA, atrodiet rādītājus iegūtajās kolonnās. Mēs veicam reizināšanu. Izmantojot funkciju ROOT kvadrātā rezultātu.

7. Mēs aprēķinām koeficientu, izmantojot saucēja un skaitītāja vērtības.

8. CORREL ir integrēta funkcija, kas ļauj novērst sarežģītus aprēķinus. Mēs ejam uz “Funkciju vedni”, atlasiet CORREL un norādiet indikatoru masīvus х и у. Mēs izveidojam grafiku, kas parāda iegūtās vērtības.

Pāru korelācijas koeficientu matrica programmā Excel

Analizēsim, kā aprēķināt sapāroto matricu koeficientus. Piemēram, ir četru mainīgo matrica.

Soli pa solim instrukcija:

1. Mēs ejam uz "Datu analīzi", kas atrodas cilnes "Dati" blokā "Analīze". Parādītajā sarakstā atlasiet Korelācija.
2. Mēs iestatām visus nepieciešamos iestatījumus. “Ievades intervāls” – visu četru kolonnu intervāls. “Izvades intervāls” – vieta, kur vēlamies parādīt kopsummas. Mēs noklikšķinām uz pogas "OK".
3. Izvēlētajā vietā tika izveidota korelācijas matrica. Katrs rindas un kolonnas krustpunkts ir korelācijas koeficients. Kad koordinātas sakrīt, tiek parādīts skaitlis 1.

Funkcija CORREL, lai programmā Excel noteiktu attiecības un korelāciju

CORREL – funkcija, ko izmanto, lai aprēķinātu korelācijas koeficientu starp 2 masīviem. Apskatīsim četrus visu šīs funkcijas spēju piemērus.

Funkcijas CORREL izmantošanas piemēri programmā Excel

Pirmais piemērs. Ir plāksne ar informāciju par uzņēmuma darbinieku vidējām algām vienpadsmit gadu laikā un USD kursu. Ir nepieciešams noteikt saistību starp šiem diviem lielumiem. Tabula izskatās šādi:

Aprēķinu algoritms izskatās šādi:

Parādītais rezultāts ir tuvu 1. Rezultāts:

Darbību ietekmes uz rezultātu korelācijas koeficienta noteikšana

Otrais piemērs. Divi pretendenti vērsās pie divām dažādām aģentūrām, lai saņemtu palīdzību piecpadsmit dienu laikā. Katru dienu tika veikta sociālā aptauja, kas noteica atbalsta pakāpi katram pretendentam. Jebkurš intervējamais varēja izvēlēties vienu no diviem pretendentiem vai iebilst pret visiem. Jānoskaidro, cik ļoti katra reklāmas akcija ietekmēja atbalsta pakāpi pretendentiem, kurš uzņēmums ir efektīvāks.

Izmantojot tālāk norādītās formulas, mēs aprēķinām korelācijas koeficientu:

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

rezultāti:

No iegūtajiem rezultātiem kļūst skaidrs, ka atbalsta pakāpe 1.pretendentam pieauga ar katru reklāmas veicināšanas dienu, līdz ar to korelācijas koeficients tuvojas 1. Reklāmas palaišanas brīdī otram pretendentam bija liela uzticēšanās, un 5 dienas bija pozitīva tendence. Pēc tam uzticības pakāpe samazinājās un līdz piecpadsmitajai dienai noslīdēja zem sākotnējiem rādītājiem. Zemi rādītāji liecina, ka paaugstināšana ir negatīvi ietekmējusi atbalstu. Neaizmirstiet, ka rādītājus var ietekmēt arī citi blakus faktori, kas nav aplūkoti tabulas veidā.

Satura popularitātes analīze pēc video skatījumu un atkārtotu publicēšanas korelācijas

Trešais piemērs. Persona, kas reklamē savus videoklipus YouTube video mitināšanā, kanāla reklamēšanai izmanto sociālos tīklus. Viņš ievēro, ka pastāv zināma saistība starp atkārtoto ierakstu skaitu sociālajos tīklos un skatījumu skaitu kanālā. Vai ir iespējams paredzēt turpmāko veiktspēju, izmantojot izklājlapu rīkus? Jāidentificē lineārās regresijas vienādojuma piemērošanas saprātīgums, lai prognozētu video skatījumu skaitu atkarībā no atkārtojumu skaita. Tabula ar vērtībām:

Tagad ir jānosaka attiecības esamība starp 2 rādītājiem saskaņā ar zemāk esošo formulu:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;"Spēcīga tiešā saistība";"Spēcīga apgrieztā saistība");"Vāja vai nav saistība"' class='formula'>

Ja iegūtais koeficients ir lielāks par 0,7, tad piemērotāk ir izmantot lineārās regresijas funkciju. Šajā piemērā mēs darām:

Tagad mēs veidojam grafiku:

Mēs izmantojam šo vienādojumu, lai noteiktu skatījumu skaitu pie 200, 500 un 1000 kopīgošanas reizēm: =9,2937*D4-206,12. Mēs iegūstam šādus rezultātus:

funkcija PROGNOZE ļauj noteikt skatījumu skaitu uz doto brīdi, ja būtu, piemēram, divi simti piecdesmit atkārtotas ziņas. Mēs piesakāmies: 0,7;PREDICTION(D7;B3:B8;A3:A8);"Vērtības nav saistītas"" class='formula'>. Mēs iegūstam šādus rezultātus:

Funkcijas CORREL izmantošanas iespējas programmā Excel

Šai funkcijai ir šādas funkcijas:

1. Tukšās šūnas netiek ņemtas vērā.
2. Šūnas, kas satur Būla un teksta tipa informāciju, netiek ņemtas vērā.
3. Dubultā noliegums “-” tiek izmantots, lai ņemtu vērā loģiskās vērtības skaitļu veidā.
4. Šūnu skaitam pētītajos masīvos ir jāsakrīt, pretējā gadījumā tiks parādīts ziņojums #N/A.

Korelācijas koeficienta statistiskā nozīmīguma novērtējums

Pārbaudot korelācijas koeficienta nozīmīgumu, nulles hipotēze ir tāda, ka indikatoram ir vērtība 0, bet alternatīvam nav. Verifikācijai tiek izmantota šāda formula:

Secinājumi

Korelācijas analīze izklājlapā ir vienkāršs un automatizēts process. Lai to veiktu, jums tikai jāzina, kur atrodas nepieciešamie rīki un kā tos aktivizēt, izmantojot programmas iestatījumus.