Hvordan beregne korrelasjonskoeffisient i Excel (2 enkle måter)

Excel er et kraftig verktøy som har noen fantastiske funksjoner og funksjoner når du arbeider med statistikk.

Å finne en sammenheng mellom to dataserier er en av de vanligste statistiske beregningene når du arbeider med store datasett,

Jeg jobbet som finansanalytiker for noen år siden, og selv om vi ikke var sterkt involvert i statistiske data, var det å finne korrelasjon noe vi fortsatt måtte gjøre ganske ofte.

I denne opplæringen vil jeg vise deg to veldig enkle måter å beregne korrelasjonskoeffisienten i Excel. Det er allerede en innebygd funksjon for å gjøre dette, og du kan også bruke Data Analysis Toolpak.

Så la oss komme i gang!

Hva er korrelasjonskoeffisienten?

Siden dette ikke er en statistikkklasse, la meg kort forklare hva som er korrelasjonskoeffisienten, og så går vi videre til delen der vi beregner korrelasjonskoeffisienten i Excel.

En korrelasjonskoeffisient er en verdi som forteller deg hvor nært to dataserier er relatert.

Et vanlig eksempel er vekten og høyden til 10 personer i en gruppe. Hvis vi beregner korrelasjonskoeffisienten for høyde- og vektdataene for disse menneskene, får vi en verdi mellom -1 og 1.

En verdi mindre enn null indikerer en negativ korrelasjon, noe som betyr at hvis høyden øker, reduseres vekten, eller hvis vekten øker deretter, reduseres høyden.

Og en verdi mer enn null indikerer en positiv korrelasjon, noe som betyr at hvis høyden øker, øker vekten, og hvis høyden synker, reduseres vekten.

Jo nærmere verdien er 1, desto sterkere er den positive korrelasjonen. Så en verdi på .8 indikerer at høyde- og vektdataene er sterkt korrelert.

Merk: Det finnes forskjellige typer korrelasjonskoeffisienter og statistikk, men i denne opplæringen ser vi på den vanligste som er Pearson -korrelasjonskoeffisienten

La oss nå se hvordan vi beregner denne korrelasjonskoeffisienten i Excel.

Beregning av korrelasjonskoeffisient i Excel

Som jeg nevnte, er det et par måter du kan beregne korrelasjonskoeffisienten i Excel.

Bruker CORREL Formula

CORREL er en statistikkfunksjon som ble introdusert i Excel 2007.

Anta at du har et datasett som vist nedenfor der du vil beregne korrelasjonskoeffisienten mellom høyden og vekten til 10 personer.

Nedenfor er formelen som gjør dette:

= CORREL (B2: B12, C2: C12)

Funksjonen ovenfor CORREL tar to argumenter - serien med høyde datapunkter og serien med vekt datapunkter.

Og det er det!

Så snart du trykker på enter, gjør Excel alle beregningene i back-end, det gir deg ett enkelt Pearson-korrelasjonskoeffisientnummer.

I vårt eksempel er denne verdien litt over .5, noe som indikerer at det er en ganske sterk positiv korrelasjon.

Denne metoden brukes best hvis du har to serier, og alt du ønsker er korrelasjonskoeffisienten.

Men hvis du har flere serier og du vil finne ut korrelasjonskoeffisienten for alle disse seriene, kan du også vurdere å bruke dataanalyseverktøypakken i Excel (dekket neste)

Bruke Tool Analysis Toolpak

Excel har et Data Analysis Toolpak som kan brukes til raskt å beregne ulike statistikkverdier (inkludert å få korrelasjonskoeffisienten).

Men Data Analysis Toolpak er som standard deaktivert i Excel. Så det første trinnet ville være å aktivere dataanalyseverktøyet tilbake og deretter bruke det til å beregne Pearson -korrelasjonskoeffisienten i Excel.

Aktiverer Data Analysis Toolpak

Nedenfor er trinnene for å aktivere Data Analysis Toolpak i Excel:

1. Klikk kategorien Fil
2. Klikk på Alternativer
3. I dialogboksen Alternativer for Excel, som åpnes, klikker du på tilleggsprogrammet i sidefeltruten
4. I rullegardinmenyen Administrer velger du Excel-tillegg
5. Klikk på Gå. Dette vil åpne dialogboksen for tillegg
6. Sjekk alternativet Analysis Toolpak
7. Klikk på Ok

De ovennevnte trinnene vil legge til en ny gruppe i Data -fanen i Excel -båndet kalt Analyse. I denne gruppen vil du ha alternativet Dataanalyse

Beregning av korrelasjonskoeffisienten ved hjelp av Data Analysis Toolpak

Nå som du har analyseverktøyet tilbake tilgjengelig på båndet, la oss se hvordan vi beregner korrelasjonskoeffisienten ved hjelp av det.

Anta at du har et datasett som vist nedenfor, og du vil finne ut av sammenhengen mellom de tre seriene (høyde og vekt, høyde og inntekt og vekt og inntekt)

Nedenfor er trinnene for å gjøre dette:

1. Klikk kategorien Data
2. Klikk på alternativet Dataanalyse i gruppen Analyse
3. Klikk på "Korrelasjon" i dialogboksen Dataanalyse som åpnes.
4. Klikk OK. Dette åpner dialogboksen Korrelasjon
5. For inndataområde, velg de tre seriene - inkludert overskriftene
6. For "Gruppert etter" må du kontrollere at "Kolonner" er valgt
7. Velg alternativet - 'Etikett i første rad'. Dette vil sikre at dataene i de resulterende dataene vil ha de samme overskriftene, og det ville være mye lettere å forstå resultatene
8. I Output -alternativene velger du hvor du vil ha den resulterende tabellen. Jeg kommer til å gå med celle G1 på det samme regnearket. Du kan også velge å få resultatene i et nytt regneark eller en ny arbeidsbok
9. Klikk OK

Så snart du gjør dette, vil Excel beregne korrelasjonskoeffisienten for alle seriene og gi deg en tabell som vist nedenfor:

Vær oppmerksom på at den resulterende tabellen er statisk, og at den ikke oppdateres hvis noen av datapunktene i tabellen endres. I tilfelle endringer må du gjenta trinnene ovenfor igjen for å generere en ny tabell med korrelasjonskoeffisienter.

Så dette er to raske og enkle metoder for å beregne korrelasjonskoeffisient i Excel.

Jeg håper du synes denne opplæringen var nyttig!