Korrelasjonsanalyse i Excel. Et eksempel på å utføre en korrelasjonsanalyse

Korrelasjonsanalyse er en vanlig forskningsmetode som brukes til å bestemme avhengighetsnivået til den første verdien på den andre. Regnearket har et spesielt verktøy som lar deg implementere denne typen forskning.

Essensen av korrelasjonsanalyse

Det er nødvendig å bestemme forholdet mellom to forskjellige mengder. Den avslører med andre ord i hvilken retning (mindre / større) verdien endres avhengig av endringer i den andre.

Formål med korrelasjonsanalyse

Avhengighet etableres når identifiseringen av korrelasjonskoeffisienten begynner. Denne metoden skiller seg fra regresjonsanalyse, da det kun er én indikator beregnet ved bruk av korrelasjon. Intervallet endres fra +1 til -1. Hvis den er positiv, bidrar en økning i den første verdien til en økning i den andre. Hvis negativ, bidrar en økning i den første verdien til en nedgang i den andre. Jo høyere koeffisient, jo sterkere en verdi påvirker den andre.

Viktig! Ved 0. koeffisient er det ingen sammenheng mellom mengdene.

Beregning av korrelasjonskoeffisienten

La oss analysere beregningen på flere prøver. For eksempel er det tabelldata, der utgifter til reklamekampanjer og salgsvolum er beskrevet etter måneder i separate kolonner. Basert på tabellen vil vi finne ut graden av avhengighet av salgsvolum på pengene brukt på reklamekampanjer.

Metode 1: Bestemme korrelasjon gjennom funksjonsveiviseren

CORREL – en funksjon som lar deg implementere en korrelasjonsanalyse. Generell form – CORREL(massiv1;massiv2). Detaljerte instruksjoner:

1. Det er nødvendig å velge cellen der det er planlagt å vise resultatet av beregningen. Klikk "Sett inn funksjon" til venstre for tekstfeltet for å angi formelen.

2. Funksjonsveiviseren åpnes. Her må du finne CORREL, klikk på den og deretter på "OK".

3. Argumentvinduet åpnes. I linjen "Array1" må du angi koordinatene til intervallene til den første av verdiene. I dette eksemplet er dette kolonnen Salgsverdi. Du trenger bare å velge alle cellene som er i denne kolonnen. På samme måte må du legge til koordinatene til den andre kolonnen til linjen "Array1". I vårt eksempel er dette kolonnen Annonseringskostnader.

4. Etter å ha angitt alle områdene, klikk på "OK"-knappen.

Koeffisienten ble vist i cellen som ble angitt i begynnelsen av våre handlinger. Det oppnådde resultatet er 0,97. Denne indikatoren gjenspeiler den høye avhengigheten til den første verdien av den andre.

Metode 2: Beregn korrelasjon ved å bruke Analysis ToolPak

Det er en annen metode for å bestemme korrelasjon. Her brukes en av funksjonene som finnes i analysepakken. Før du bruker det, må du aktivere verktøyet. Detaljerte instruksjoner:

1. Gå til "Fil"-delen.

2. Et nytt vindu åpnes, der du må klikke på "Innstillinger"-delen.
3. Klikk på "Tillegg".
4. Vi finner elementet "Management" nederst. Her må du velge "Excel-tillegg" fra hurtigmenyen og klikke "OK".

5. Et spesielt tilleggsvindu har åpnet seg. Sett et hake ved siden av elementet "Analysepakke". Vi klikker "OK".
6. Aktiveringen var vellykket. La oss nå gå til Data. "Analyse" -blokken dukket opp, der du må klikke på "Dataanalyse".
7. I det nye vinduet som vises, velg "Korrelasjon"-elementet og klikk på "OK".

8. Analyseinnstillingsvinduet dukket opp på skjermen. I linjen "Inndataintervall" er det nødvendig å angi området for absolutt alle kolonner som deltar i analysen. I dette eksemplet er dette kolonnene "Salgsverdi" og "Annonsekostnader". Utgangsvisningsinnstillingene er i utgangspunktet satt til Nytt regneark, noe som betyr at resultatene vil vises på et annet ark. Eventuelt kan du endre utdataplasseringen for resultatet. Etter å ha gjort alle innstillingene, klikk på "OK".

Sluttresultatene er ute. Resultatet er det samme som i den første metoden – 0,97.

Definisjon og beregning av multiple korrelasjonskoeffisienter i MS Excel

For å identifisere avhengighetsnivået til flere mengder, brukes flere koeffisienter. I fremtiden oppsummeres resultatene i en egen tabell, kalt korrelasjonsmatrisen.

Detaljert guide:

1. I «Data»-delen finner vi den allerede kjente «Analyse»-blokken og klikker på «Dataanalyse».

2. I vinduet som vises, klikk på "Korrelasjon"-elementet og klikk på "OK".
3. I linjen "Input interval" kjører vi i intervallet for tre eller flere kolonner i kildetabellen. Området kan legges inn manuelt eller ganske enkelt velge det med LMB, og det vil automatisk vises på ønsket linje. I "Gruppering" velger du riktig grupperingsmetode. I "Output Parameter" spesifiserer stedet hvor korrelasjonsresultatene skal vises. Vi klikker "OK".

4. Klar! Korrelasjonsmatrisen ble bygget.

Parkorrelasjonskoeffisient i Excel

La oss finne ut hvordan du riktig tegner parkorrelasjonskoeffisienten i et Excel-regneark.

Beregning av parkorrelasjonskoeffisient i Excel

For eksempel har du x- og y-verdier.

X er den avhengige variabelen og y er den uavhengige. Det er nødvendig å finne retningen og styrken til forholdet mellom disse indikatorene. Trinn-for-steg instruksjon:

1. La oss finne gjennomsnittsverdiene ved å bruke funksjonen HJERTE.

2. La oss beregne hver х и xavg, у и avg ved å bruke «-»-operatoren.

3. Vi multipliserer de beregnede forskjellene.

4. Vi beregner summen av indikatorene i denne kolonnen. Telleren er resultatet som ble funnet.

5. Regn ut nevnerne til differansen х и x-gjennomsnitt, y и y-medium. For å gjøre dette vil vi utføre kvadreringen.

6. Bruke funksjonen AUTOSUMMA, finn indikatorene i de resulterende kolonnene. Vi multipliserer. Bruker funksjonen ROOT kvadrat resultatet.

7. Vi beregner kvotienten ved å bruke verdiene til nevneren og telleren.

8. CORREL er en integrert funksjon som lar deg forhindre komplekse beregninger. Vi går til "Funksjonsveiviseren", velger CORREL og spesifiserer matrisene med indikatorer х и у. Vi bygger en graf som viser de oppnådde verdiene.

Matrise av parvise korrelasjonskoeffisienter i Excel

La oss analysere hvordan du beregner koeffisientene til sammenkoblede matriser. For eksempel er det en matrise med fire variabler.

Trinnvis instruksjon:

1. Vi går til "Dataanalyse", som ligger i "Analyse" -blokken i "Data" -fanen. Velg Korrelasjon fra listen som vises.
2. Vi setter alle nødvendige innstillinger. "Inndataintervall" – intervallet til alle fire kolonnene. "Utdataintervall" - stedet der vi ønsker å vise totalene. Vi klikker på "OK"-knappen.
3. En korrelasjonsmatrise ble bygget på det valgte stedet. Hvert skjæringspunkt mellom en rad og en kolonne er en korrelasjonskoeffisient. Tallet 1 vises når koordinatene stemmer overens.

CORREL-funksjon for å bestemme sammenheng og korrelasjon i Excel

CORREL – en funksjon som brukes til å beregne korrelasjonskoeffisienten mellom 2 arrays. La oss se på fire eksempler på alle egenskapene til denne funksjonen.

Eksempler på bruk av CORREL-funksjonen i Excel

Første eksempel. Det er en plate med informasjon om gjennomsnittslønnen til selskapets ansatte i løpet av elleve år og valutakursen på $. Det er nødvendig å identifisere forholdet mellom disse to mengdene. Tabellen ser slik ut:

Beregningsalgoritmen ser slik ut:

Den viste poengsummen er nær 1. Resultat:

Bestemmelse av korrelasjonskoeffisienten for virkningen av handlinger på resultatet

Andre eksempel. To budgivere henvendte seg til to forskjellige byråer for å få hjelp med en femten dager lang kampanje. Hver dag ble det gjennomført en sosial meningsmåling, som bestemte graden av støtte for hver enkelt søker. Enhver intervjuobjekt kan velge en av de to søkerne eller motsette seg alle. Det er nødvendig å bestemme hvor mye hver reklamekampanje påvirket graden av støtte til søkere, hvilket selskap som er mer effektivt.

Ved å bruke formlene nedenfor beregner vi korrelasjonskoeffisienten:

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

resultater:

Fra de oppnådde resultatene blir det klart at graden av støtte til 1. søker økte for hver dag med annonseopprykk, derfor nærmer korrelasjonskoeffisienten seg 1. Da annonseringen ble lansert hadde den andre søkeren et stort antall tillit, og for 5 dager var det en positiv trend. Så falt graden av tillit, og innen den femtende dagen falt den under de opprinnelige indikatorene. Lave skårer tyder på at promotering har påvirket støtten negativt. Ikke glem at andre samtidige faktorer som ikke vurderes i tabellform også kan påvirke indikatorene.

Analyse av innholdspopularitet ved korrelasjon av videovisninger og reposter

Tredje eksempel. En person for å markedsføre sine egne videoer på YouTube-videovert bruker sosiale nettverk for å annonsere for kanalen. Han merker at det er en viss sammenheng mellom antall reposter i sosiale nettverk og antall visninger på kanalen. Er det mulig å forutsi fremtidig ytelse ved å bruke regnearkverktøy? Det er nødvendig å identifisere rimeligheten av å bruke den lineære regresjonsligningen for å forutsi antall videovisninger avhengig av antall reposter. Tabell med verdier:

Nå er det nødvendig å bestemme tilstedeværelsen av et forhold mellom 2 indikatorer i henhold til formelen nedenfor:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Sterk direkte sammenheng”;”Sterk invers sammenheng”);”Svak eller ingen sammenheng”)' class='formula'>

Hvis den resulterende koeffisienten er høyere enn 0,7, er det mer hensiktsmessig å bruke den lineære regresjonsfunksjonen. I dette eksemplet gjør vi:

Nå bygger vi en graf:

Vi bruker denne ligningen for å bestemme antall visninger ved 200, 500 og 1000 delinger: =9,2937*D4-206,12. Vi får følgende resultater:

Funksjon PROGNOSE lar deg bestemme antall visninger for øyeblikket, hvis det for eksempel var to hundre og femti reposter. Vi bruker: 0,7;PREDICTION(D7;B3:B8;A3:A8);”Verdiene er ikke relatert”)' class='formula'>. Vi får følgende resultater:

Funksjoner ved bruk av CORREL-funksjonen i Excel

Denne funksjonen har følgende funksjoner:

1. Det tas ikke hensyn til tomme celler.
2. Celler som inneholder informasjon om boolsk og teksttype tas ikke i betraktning.
3. Dobbel negasjon "-" brukes til å gjøre rede for logiske verdier i form av tall.
4. Antall celler i de studerte matrisene må samsvare, ellers vil #N/A-meldingen vises.

Vurdering av den statistiske signifikansen til korrelasjonskoeffisienten

Når man tester betydningen av en korrelasjonskoeffisient, er nullhypotesen at indikatoren har en verdi på 0, mens alternativet ikke har det. Følgende formel brukes for verifisering:

konklusjonen

Korrelasjonsanalyse i et regneark er en enkel og automatisert prosess. For å utføre det trenger du bare å vite hvor de nødvendige verktøyene er plassert og hvordan du aktiverer dem gjennom programinnstillingene.