Excel er et kraftfuldt værktøj, der har nogle fantastiske funktioner og funktioner, når du arbejder med statistik.
At finde en sammenhæng mellem to dataserier er en af de mest almindelige statistiske beregninger, når man arbejder med store datasæt,
Jeg arbejdede som finansanalytiker for et par år siden, og selvom vi ikke var stærkt involveret i statistiske data, var det stadig nødvendigt at finde sammenhæng ganske ofte.
I denne vejledning viser jeg dig to virkelig nemme måder beregne korrelationskoefficient i Excel. Der er allerede en indbygget funktion til at gøre dette, og du kan også bruge Data Analysis Toolpak.
Så lad os komme i gang!
Hvad er korrelationskoefficienten?
Da dette ikke er en statistikklasse, lad mig kort forklare, hvad der er korrelationskoefficienten, og derefter går vi videre til det afsnit, hvor vi beregner korrelationskoefficienten i Excel.
En korrelationskoefficient er en værdi, der fortæller dig, hvor tæt to dataserier er relateret.
Et almindeligt brugt eksempel er vægten og højden af 10 personer i en gruppe. Hvis vi beregner korrelationskoefficienten for data om højde og vægt for disse mennesker, får vi en værdi mellem -1 og 1.
En værdi mindre end nul angiver en negativ korrelation, hvilket betyder, at hvis højden stiger, falder vægten, eller hvis vægten stiger, så falder højden.
Og en værdi mere end nul angiver en positiv korrelation, hvilket betyder, at hvis højden stiger, stiger vægten, og hvis højden falder, falder vægten.
Jo tættere værdien er på 1, desto stærkere er den positive korrelation. Så en værdi på .8 ville indikere, at data om højde og vægt er stærkt korreleret.
Bemærk: Der er forskellige typer korrelationskoefficienter og statistikker, men i denne vejledning ser vi på den mest almindelige, som er Pearson -korrelationskoefficientenLad os nu se, hvordan vi beregner denne korrelationskoefficient i Excel.
Beregning af korrelationskoefficient i Excel
Som jeg nævnte, er der et par måder, du kan beregne korrelationskoefficienten i Excel.
Brug af CORREL Formula
CORREL er en statistikfunktion, der blev introduceret i Excel 2007.
Antag, at du har et datasæt som vist herunder, hvor du vil beregne korrelationskoefficienten mellem højden og vægten af 10 personer.
Nedenfor er formlen, der ville gøre dette:
= HØJRE (B2: B12, C2: C12)
Ovenstående CORREL -funktion tager to argumenter - serien med højdedatapunkterne og serien med vægtdatapunkterne.
Og det er det!
Så snart du trykker på enter, foretager Excel alle beregningerne i bagenden, det giver dig et enkelt Pearson-korrelationskoefficientnummer.
I vores eksempel er denne værdi lidt over .5, hvilket indikerer, at der er en ret stærk positiv korrelation.
Denne metode bruges bedst, hvis du har to serier, og alt du ønsker er korrelationskoefficienten.
Men hvis du har flere serier, og du vil finde ud af korrelationskoefficienten for alle disse serier, kan du også overveje at bruge dataanalyseværktøjspakken i Excel (dækket næste)
Brug af Data Analysis Toolpak
Excel har et Data Analysis Toolpak, der kan bruges til hurtigt at beregne forskellige statistiske værdier (herunder at få korrelationskoefficienten).
Men Data Analysis Toolpak er som standard deaktiveret i Excel. Så det første trin ville være at aktivere dataanalyseværktøjet tilbage og derefter bruge det til at beregne Pearson -korrelationskoefficienten i Excel.
Aktivering af dataanalyseværktøjspakken
Nedenfor er trinene til aktivering af dataanalyseværktøjspakken i Excel:
- Klik på fanen Filer
- Klik på Indstillinger
- I dialogboksen Excel-indstillinger, der åbnes, skal du klikke på tilføjelsesprogrammet i sidepanelruden
- Vælg Excel-tilføjelsesprogrammer i rullemenuen Administrer
- Klik på Gå. Dette åbner tilføjelsesdialogboksen
- Marker indstillingen Analysis Toolpak
- Klik på Ok
Ovenstående trin tilføjer en ny gruppe på fanen Data i Excel -båndet kaldet Analyse. Inden for denne gruppe ville du have muligheden Dataanalyse
Beregning af korrelationskoefficienten ved hjælp af Data Analysis Toolpak
Nu hvor du har analyseværktøjet tilbage på båndet, lad os se, hvordan du beregner korrelationskoefficienten ved hjælp af det.
Antag, at du har et datasæt som vist herunder, og du vil finde ud af sammenhængen mellem de tre serier (højde og vægt, højde og indkomst og vægt og indkomst)
Nedenfor er trinene for at gøre dette:
- Klik på fanen Data
- Klik på indstillingen Dataanalyse i gruppen Analyse
- I dialogboksen Dataanalyse, der åbnes, skal du klikke på 'Korrelation'
- Klik på OK. Dette åbner dialogboksen Korrelation
- For inputområde skal du vælge de tre serier - inklusive overskrifterne
- For 'Grupperet efter' skal du sørge for, at 'Kolonner' er valgt
- Vælg indstillingen - 'Etiket i første række'. Dette vil sikre, at de resulterende data ville have de samme overskrifter, og det ville være meget lettere at forstå resultaterne
- I Output -indstillingerne skal du vælge, hvor du vil have den resulterende tabel. Jeg vil gå med celle G1 på det samme regneark. Du kan også vælge at få dine resultater i et nyt regneark eller en ny projektmappe
- Klik på OK
Så snart du gør dette, beregner Excel korrelationskoefficienten for alle serierne og giver dig en tabel som vist herunder:
Bemærk, at den resulterende tabel er statisk og ikke opdateres, hvis nogen af datapunkterne i din tabel ændres. I tilfælde af ændringer skal du gentage ovenstående trin igen for at generere en ny tabel med korrelationskoefficienter.
Så det er to hurtige og lette metoder til at beregne korrelationskoefficient i Excel.
Jeg håber, at du fandt denne vejledning nyttig!
