Korrelationsanalyse i Excel. Et eksempel på udførelse af en korrelationsanalyse

Korrelationsanalyse er en almindelig forskningsmetode, der bruges til at bestemme niveauet af afhængighed af 1. værdi på 2. værdi. Regnearket har et særligt værktøj, der giver dig mulighed for at implementere denne type forskning.

Essensen af ​​korrelationsanalyse

Det er nødvendigt at bestemme forholdet mellem to forskellige mængder. Med andre ord afslører den i hvilken retning (mindre / større) værdien ændres afhængigt af ændringer i anden.

Formål med korrelationsanalyse

Afhængighed etableres, når identifikationen af ​​korrelationskoefficienten begynder. Denne metode adskiller sig fra regressionsanalyse, da der kun er én indikator beregnet ved hjælp af korrelation. Intervallet skifter fra +1 til -1. Hvis den er positiv, så bidrager en stigning i den første værdi til en stigning i den 2. Hvis negativ, så bidrager en stigning i 1. værdi til et fald i 2.. Jo højere koefficient, jo stærkere en værdi påvirker den anden.

Vigtigt! Ved 0. koefficient er der ingen sammenhæng mellem mængderne.

Beregning af korrelationskoefficienten

Lad os analysere beregningen på flere prøver. For eksempel er der tabeldata, hvor udgifter til reklamekampagner og salgsvolumen er beskrevet efter måneder i separate kolonner. Baseret på tabellen vil vi finde ud af niveauet for afhængighed af salgsvolumen af ​​de penge, der bruges på reklamefremme.

Metode 1: Bestemmelse af korrelation gennem funktionsguiden

CORREL – en funktion, der giver dig mulighed for at implementere en korrelationsanalyse. Generel form – CORREL(massiv1;massiv2). Detaljerede instruktioner:

1. Det er nødvendigt at vælge den celle, hvor det er planlagt at vise resultatet af beregningen. Klik på "Indsæt funktion" til venstre for tekstfeltet for at indtaste formlen.

2. Funktionsguiden åbnes. Her skal du finde KORREL, klik på den og derefter på "OK".

3. Argumentvinduet åbnes. I linjen "Array1" skal du indtaste koordinaterne for intervallerne for den 1. af værdierne. I dette eksempel er dette kolonnen Salgsværdi. Du skal blot markere alle de celler, der er i denne kolonne. På samme måde skal du tilføje koordinaterne for den anden kolonne til linjen "Array2". I vores eksempel er dette kolonnen Annonceomkostninger.

4. Når du har indtastet alle områder, skal du klikke på knappen "OK".

Koefficienten blev vist i cellen, der blev angivet i begyndelsen af ​​vores handlinger. Det opnåede resultat er 0,97. Denne indikator afspejler den høje afhængighed af den første værdi af den anden.

Metode 2: Beregn korrelation ved hjælp af Analysis ToolPak

Der er en anden metode til at bestemme korrelation. Her bruges en af ​​de funktioner, der findes i analysepakken. Før du bruger det, skal du aktivere værktøjet. Detaljerede instruktioner:

1. Gå til afsnittet "Filer".

2. Et nyt vindue åbnes, hvor du skal klikke på sektionen "Indstillinger".
3. Klik på "Tilføjelser".
4. Vi finder elementet "Management" nederst. Her skal du vælge "Excel-tilføjelser" fra kontekstmenuen og klikke på "OK".

5. Et særligt tilføjelsesvindue er åbnet. Sæt et flueben ved siden af ​​elementet "Analysepakke". Vi klikker på "OK".
6. Aktiveringen lykkedes. Lad os nu gå til Data. Blokken "Analyse" dukkede op, hvor du skal klikke på "Dataanalyse".
7. I det nye vindue, der vises, skal du vælge "Korrelation"-elementet og klikke på "OK".

8. Analyseindstillingsvinduet dukkede op på skærmen. I linjen "Input interval" er det nødvendigt at indtaste intervallet for absolut alle kolonner, der deltager i analysen. I dette eksempel er det kolonnerne "Salgsværdi" og "Annonceomkostninger". Indstillingerne for outputvisning er oprindeligt indstillet til Nyt regneark, hvilket betyder, at resultaterne vil blive vist på et andet ark. Du kan eventuelt ændre outputplaceringen af ​​resultatet. Når du har foretaget alle indstillinger, skal du klikke på "OK".

Slutresultaterne er ude. Resultatet er det samme som i den første metode – 0,97.

Definition og beregning af multiple korrelationskoefficienter i MS Excel

For at identificere niveauet af afhængighed af flere mængder, bruges flere koefficienter. I fremtiden opsummeres resultaterne i en separat tabel, kaldet korrelationsmatrixen.

Detaljeret vejledning:

1. I afsnittet "Data" finder vi den allerede kendte "Analyse"-blok og klikker på "Dataanalyse".

2. Klik på "Korrelation"-elementet i vinduet, der vises, og klik på "OK".
3. I linjen "Input interval" kører vi i intervallet for tre eller flere kolonner i kildetabellen. Området kan indtastes manuelt eller blot vælges med LMB, og det vises automatisk i den ønskede linje. I "Gruppering" skal du vælge den relevante grupperingsmetode. I "Output Parameter" angiver det sted, hvor korrelationsresultaterne vil blive vist. Vi klikker på "OK".

4. Parat! Korrelationsmatricen blev bygget.

Par korrelationskoefficient i Excel

Lad os finde ud af, hvordan man korrekt tegner parkorrelationskoefficienten i et Excel-regneark.

Beregning af parkorrelationskoefficient i Excel

For eksempel har du x- og y-værdier.

X er den afhængige variabel og y er den uafhængige. Det er nødvendigt at finde retningen og styrken af ​​forholdet mellem disse indikatorer. Trin-for-trin instruktion:

1. Lad os finde gennemsnitsværdierne ved hjælp af funktionen HJERTE.

2. Lad os beregne hver х и xavg, у и gns ved at bruge «-»-operatoren.

3. Vi multiplicerer de beregnede forskelle.

4. Vi beregner summen af ​​indikatorerne i denne kolonne. Tælleren er det fundne resultat.

5. Beregn nævnerne af forskellen х и x-gennemsnit, y и y-medium. For at gøre dette vil vi udføre kvadreringen.

6. Brug af funktionen AUTOSUMMA, find indikatorerne i de resulterende kolonner. Vi multiplicerer. Brug af funktionen ROOT kvadrat resultatet.

7. Vi beregner kvotienten ved hjælp af værdierne af nævneren og tælleren.

8. CORREL er en integreret funktion, der giver dig mulighed for at forhindre komplekse beregninger. Vi går til "Function Wizard", vælger CORREL og specificerer arrays af indikatorer х и у. Vi bygger en graf, der viser de opnåede værdier.

Matrix af parvise korrelationskoefficienter i Excel

Lad os analysere, hvordan man beregner koefficienterne for parrede matricer. For eksempel er der en matrix med fire variable.

Trin-for-trin instruktion:

1. Vi går til "Dataanalyse", der er placeret i blokken "Analyse" på fanen "Data". Vælg Korrelation på listen, der vises.
2. Vi indstiller alle de nødvendige indstillinger. "Input interval" – intervallet for alle fire kolonner. "Output interval" - det sted, hvor vi ønsker at vise totalerne. Vi klikker på knappen "OK".
3. Der blev bygget en korrelationsmatrix på det valgte sted. Hvert skæringspunkt mellem en række og en kolonne er en korrelationskoefficient. Tallet 1 vises, når koordinaterne stemmer overens.

CORREL funktion til at bestemme relation og korrelation i Excel

CORREL – en funktion, der bruges til at beregne korrelationskoefficienten mellem 2 arrays. Lad os se på fire eksempler på alle denne funktions evner.

Eksempler på brug af CORREL-funktionen i Excel

Første eksempel. Der er en plade med oplysninger om de gennemsnitlige lønninger for virksomhedens ansatte i løbet af elleve år og kursen på $. Det er nødvendigt at identificere forholdet mellem disse to mængder. Tabellen ser således ud:

Beregningsalgoritmen ser således ud:

Den viste score er tæt på 1. Resultat:

Bestemmelse af korrelationskoefficienten for virkningen af ​​handlinger på resultatet

Andet eksempel. To tilbudsgivere henvendte sig til to forskellige bureauer for at få hjælp til en femten dage lang kampagne. Hver dag blev der gennemført en social meningsmåling, som fastlagde graden af ​​støtte for hver ansøger. Enhver interviewperson kunne vælge en af ​​de to ansøgere eller modsætte sig alle. Det er nødvendigt at bestemme, hvor meget hver reklamefremstød påvirkede graden af ​​støtte til ansøgere, hvilket firma er mere effektivt.

Ved hjælp af formlerne nedenfor beregner vi korrelationskoefficienten:

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

resultater:

Ud fra de opnåede resultater bliver det klart, at graden af ​​støtte til den 1. ansøger steg med hver dag med reklamefremstød, derfor nærmer korrelationskoefficienten sig 1. Da annoncering blev lanceret, havde den anden ansøger et stort antal tillid, og for 5 dage var der en positiv tendens. Derefter faldt graden af ​​tillid, og på den femtende dag faldt den under de oprindelige indikatorer. Lav score tyder på, at forfremmelse har påvirket støtten negativt. Glem ikke, at andre samtidige faktorer, der ikke tages i betragtning i tabelform, også kan påvirke indikatorerne.

Analyse af indholdets popularitet ved korrelation af videovisninger og reposts

Tredje eksempel. En person til at promovere deres egne videoer på YouTube-videohosting bruger sociale netværk til at annoncere for kanalen. Han bemærker, at der er en vis sammenhæng mellem antallet af reposts i sociale netværk og antallet af visninger på kanalen. Er det muligt at forudsige fremtidig ydeevne ved hjælp af regnearksværktøjer? Det er nødvendigt at identificere rimeligheden af ​​at anvende den lineære regressionsligning til at forudsige antallet af videovisninger afhængigt af antallet af reposts. Tabel med værdier:

Nu er det nødvendigt at bestemme tilstedeværelsen af ​​et forhold mellem 2 indikatorer i henhold til formlen nedenfor:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Stærk direkte relation”;”Stærk omvendt sammenhæng”);”Svag eller ingen sammenhæng”)' class='formula'>

Hvis den resulterende koefficient er højere end 0,7, er det mere hensigtsmæssigt at bruge den lineære regressionsfunktion. I dette eksempel gør vi:

Nu bygger vi en graf:

Vi anvender denne ligning til at bestemme antallet af visninger ved 200, 500 og 1000 delinger: =9,2937*D4-206,12. Vi får følgende resultater:

Funktion VEJRUDSIGT giver dig mulighed for at bestemme antallet af visninger i øjeblikket, hvis der for eksempel var to hundrede og halvtreds reposts. Vi anvender: 0,7;PREDICTION(D7;B3:B8;A3:A8);”Værdierne er ikke relaterede”)' class='formula'>. Vi får følgende resultater:

Funktioner ved at bruge CORREL-funktionen i Excel

Denne funktion har følgende funktioner:

1. Tomme celler tages ikke i betragtning.
2. Celler, der indeholder oplysninger om boolesk og teksttype, tages ikke i betragtning.
3. Dobbelt negation "-" bruges til at tage højde for logiske værdier i form af tal.
4. Antallet af celler i de undersøgte arrays skal matche, ellers vil #N/A-meddelelsen blive vist.

Vurdering af den statistiske signifikans af korrelationskoefficienten

Når man tester betydningen af ​​en korrelationskoefficient, er nulhypotesen, at indikatoren har en værdi på 0, mens alternativet ikke har. Følgende formel bruges til verifikation:

Konklusion

Korrelationsanalyse i et regneark er en enkel og automatiseret proces. For at udføre det behøver du kun at vide, hvor de nødvendige værktøjer er placeret, og hvordan du aktiverer dem gennem programindstillingerne.