Standardafvigelse - Excel og Google Sheets

Denne vejledning viser, hvordan du bruger Excel standardafvigelsesfunktion i Excel for at beregne standardafvigelse for en hel population.

STANDARD DEVIATION Funktionsoversigt

Standardafvigelsesfunktionen beregner beregner standardafvigelse for en hel population.

For at bruge Excel -regnearksfunktionen STANDARD DEVIATION skal du vælge en celle og skrive:

(Bemærk hvordan formelindgangene vises)

STANDARD DEVIATION -funktion Syntaks og input:

1	= STDEV (nummer1, [nummer2],…)

tal- Værdier for at få Standardvariation

Sådan beregnes standardafvigelsen i Excel

Når du beskæftiger dig med data, vil du gerne køre nogle grundlæggende tests for at hjælpe dig med at forstå det. Du starter typisk med at beregne gennemsnittet ved hjælp af Excel GENNEMGANGSFUNKTION <>.

Dette giver dig en idé om, hvor "midten" af dataene er. Og derfra vil du se på, hvor spredt dataene er omkring dette midtpunkt. Det er her, standardafvigelsen kommer ind.

Excel giver dig en række funktioner til at beregne standardafvigelsen - STDEV, STDEV.P, STDEV.S og DSTDEV. Vi kommer til dem alle, men lad os først lære, hvad standardafvigelsen er er, Nemlig.

Hvad er standardafvigelsen?

Standardafvigelsen giver dig en idé om, hvor langt dine datapunkter er fra middelværdien. Tag følgende datasæt af testresultater ud af 100:

1	48,49,50,51,52

Middelværdien af ​​dette datasæt er 50 (læg alle tal sammen, og divider med n, hvor n er antallet af værdier i intervallet).

Se nu på dette næste datasæt:

1	10,25,50,75,90

Middelværdien af ​​dette datasæt er også 50 - men de to intervaller fortæller en meget anderledes historie. Hvis du bare brugte middelværdien, tror du måske, at de to grupper var omtrent lige store i deres evner - og i gennemsnit er de det.

Men i den første gruppe har vi 5 personer, der fik meget ens, meget middelmådige scoringer. Og i den anden gruppe balancerede vi et par high-flyers ud af et par dårlig-scorere, med en person i midten. Det spredning af scores er meget forskellige, hvilket også gør din fortolkning af dataene meget forskellige.

Standardafvigelsen er et mål for denne spredning.

Sådan beregnes standardafvigelsen

For at forstå, hvad standardafvigelsen er, og hvordan den fungerer, kan det hjælpe at gennemgå et eksempel i hånden. På den måde ved du, hvad der foregår "under hætten", når vi kommer til Excel-funktionerne, som du kan bruge.

For at beregne standardafvigelsen arbejder du igennem denne proces:

1) Beregn middelværdien

Lad os tage vores første datasæt ovenfor: 48,49,50,51,52

Vi kender allerede middelværdien (50), som jeg har bekræftet her med Excel AVERAGE -funktionen <>:

1	= Gennemsnit (C4: C8)

2) Træk middelværdien fra hver værdi i datasættet

Jeg har gjort dette med følgende formel:

1	= C4- $ H $ 4

Vores gennemsnit er i H4, og jeg har "låst" cellereferencen ved at sætte dollartegnene foran kolonnen og rækken (ved at trykke på F4). Det betyder, at jeg kan kopiere formlen ned i kolonnen uden at cellereferencen opdateres.

Resultatet:

Lad os nu stoppe her et øjeblik. Hvis du kigger på den nye kolonne - vil du se, at tallene her er op til nul. Middelværdien af ​​disse tal er også nul.

Spredningen af ​​vores data kan naturligvis ikke være nul - vi ved, at der er en vis variation der. Vi har brug for en måde at repræsentere denne variation på, uden at gennemsnittet viser sig at være nul.

3) Kvadratér forskellene

Vi kan opnå dette ved at kvadrere forskellene. Så lad os tilføje en ny kolonne og kvadrere tallene i D -kolonnen:

1

= D4*D4

Det ser bedre ud. Nu har vi en vis variation, og mængden af ​​variation er relateret til, hvor langt hver score er fra middelværdien.

4) Beregn variansen - middelværdien af ​​de kvadrerede forskelle

Det næste trin er at få gennemsnittet af de kvadrerede forskelle. Der er faktisk to måder at gøre dette på ved beregning af standardafvigelsen.

• Hvis du bruger befolkningsdata, du tager simpelthen middelværdien (opsummer værdierne og dividerer med n)
• Hvis du bruger eksempeldata, du tager summen af ​​værdierne og dividerer med n-1

Befolkningsdata betyder, at du har det "fulde sæt" af dine data, for eksempel har du data om hver elev i en given klasse.

Eksempeldata betyder, at du ikke har alle dine data, kun en prøve taget fra en større population. Typisk er dit mål med eksempeldata at lave et skøn over, hvad værdien er i den større befolkning.

En politisk meningsmåling er et godt eksempel på stikprøvedata - forskere undersøger f.eks. 1.000 mennesker for at få en idé om, hvad et helt land eller en stat mener.

Her har vi ikke en prøve. Vi har bare fem statistisk sindede familiemedlemmer, der ønsker at beregne standardafvigelsen for en test, de alle tog. Vi har alle datapunkterne, og vi laver ikke et skøn over en større gruppe mennesker. Dette er befolkningsdata - så vi kan bare tage gennemsnittet her:

1	= Gennemsnit (E4: E8)

OK, så vi har 2. Denne score er kendt som "varians", og det er grundlaget for en masse statistiske tests, herunder standardafvigelsen. Du kan læse mere om variansen på dens hovedside: hvordan man beregner varians i Excel <>.

5) Få kvadratroden af ​​variansen

Vi kvadrerede vores tal tidligere, hvilket naturligvis puster værdierne lidt op. Så for at bringe figuren tilbage på linje med de faktiske forskelle i scoreerne fra middelværdien, skal vi kvadratroden resultatet af trin 4:

1	= SQRT (H4)

Og vi har vores resultat: standardafvigelsen er 1.414

Fordi vi har firkantede vores tidligere kvadrerede tal, er standardafvigelsen givet i de samme enheder som de originale data. Så standardafvigelsen her er 1.414 testpunkter.

Standardafvigelse, når dataene er mere spredte

Tidligere havde vi et andet eksempeldataområde: 10,25,50,75,90

Bare for sjov, lad os se, hvad der sker, når vi beregner standardafvigelsen på disse data:

Alle formlerne er nøjagtig de samme som før (bemærk at det samlede gennemsnit stadig er 50).

Det eneste, der ændrede sig, var spredningen af ​​scoringer i kolonne C. Men nu er vores standardafvigelse meget højere ved 29.832 testpunkter.

Da vi kun har 5 datapunkter, er det selvfølgelig meget let at se, at spredningen af ​​scoringer er forskellig mellem de to sæt. Men når du har 100'er eller 1.000'er datapunkter, kan du ikke fortælle det ved bare hurtigt at scanne dataene. Og det er netop derfor, vi bruger standardafvigelsen.

Excel -funktionerne til beregning af standardafvigelsen

Nu hvor du ved, hvordan standardafvigelsen fungerer, behøver du ikke at gå igennem hele processen for at nå frem til standardafvigelsen. Du kan bare bruge en af ​​Excel's indbyggede funktioner.

Excel har flere funktioner til dette formål:

• P beregner standardafvigelsen for befolkningsdata (ved hjælp af den nøjagtige metode, vi brugte i ovenstående eksempel)
• S beregner standardafvigelsen for eksempeldata (ved hjælp af n-1-metoden, vi berørte tidligere)
• STDEV er nøjagtig det samme som STDEV.S. Dette er en ældre funktion, der er blevet erstattet af STDEV.S og STDEV.P.
• STDEVA ligner meget STDEV.S, bortset fra at den indeholder tekstceller og boolske (SAND/FALSKE) celler, når den beregnes.
• STDEVPA ligner meget STDEV.P, bortset fra at den indeholder tekstceller og boolske (SAND/FALSKE) celler, når den beregnes.

Wow, mange muligheder her! Bliv ikke skræmt - i langt de fleste tilfælde bruger du enten STDEV.P eller STDEV.S.

Lad os gennemgå hver af disse efter tur, begyndende med STDEV.P, da det er den metode, vi lige har arbejdet igennem.

Funktionen Excel STDEV.P

STDEV.P beregner standardafvigelsen for befolkningsdata. Du bruger det sådan her:

1	= STDEV.P (C4: C8)

Du definerer et argument i STDEV.P: dataområdet, som du vil beregne standardafvigelsen for.

Dette er det samme eksempel, vi gik igennem trin for trin ovenfor, da vi beregnede standardafvigelsen i hånden. Og som du kan se ovenfor, får vi nøjagtig det samme resultat - 1.414.

Bemærk STDEV.P ignorerer alle celler, der indeholder tekst eller boolske (TRUE/FALSE) værdier. Hvis du har brug for at inkludere disse, skal du bruge STDEVPA.

Funktionen Excel STDEV.S

STDEV.S beregner standardafvigelsen for eksempeldata. Brug den sådan her:

1	= STDEV.S (C4: C8)

Igen kræver det et argument - det dataområde, som du vil kende standardafvigelsen til.

Inden vi går ind på et eksempel, lad os diskutere forskellen mellem STDEV.S og STDEV.P.

Som vi allerede har diskuteret, skal STDEV.S bruges på eksempeldata - når dine data er en del af et større sæt. Så lad os nu antage, at flere i vores eksempel ovenfor havde taget testen. Vi vil estimere standardafvigelsen for alle, der tog testen, ved hjælp af kun disse fem scoringer. Nu bruger vi eksempeldata.

Nu adskiller beregningen sig fra trin (4) ovenfor, når vi beregner variansen - gennemsnittet af den kvadrerede forskel for hver score fra det samlede gennemsnit.

I stedet for at bruge den normale metode - opsummer alle værdierne, og divider med n, vil vi opsummere alle værdierne og dividere med n-1:

1	= SUM (E4: E8) / (COUNT (E4: E8) -1)

I denne formel:

• SUM får summen af ​​de kvadrerede forskelle
• COUNT returnerer vores n, som vi trækker 1 fra
• Vi deler derefter simpelthen vores sum med vores n-1

Denne gang er gennemsnittet af kvadratiske forskelle 2,5 (du husker måske, at det var 2 tidligere, så det er lidt højere).

Så hvorfor dividerer vi med n-1 i stedet for n, når vi behandler eksempeldata?

Svaret er ret komplekst, og hvis du bare prøver at køre dine tal for at forstå dine data, er det ikke noget, du virkelig skal bekymre dig om. Bare sørg for, at du bruger STDEV.S til eksempeldata og STDEV.P til befolkningsdata, og det går fint.

Hvis du virkelig er nysgerrig efter at vide hvorfor, kan du se hovedsiden om, hvordan du beregner varians i Excel <>.

OK, så vi har nu variansen for prøven, så for at få standardafvigelsen for prøven får vi bare kvadratroden af ​​variansen:

1	= SQRT (H4)

Vi får 1.581.

STDEV.S udfører alle ovenstående beregninger for os og returnerer prøvestandardafvigelsen i kun en celle. Så lad os se, hvad det kommer op med …

1	= STDEV.S (C4: C8)

Jep, 1.581 igen.

Excel STDEV -funktionen

Excels STDEV -funktion fungerer på nøjagtig samme måde som STDEV.S - det vil sige, at den beregner standardafvigelsen for en stikprøve af data.

Du bruger det på samme måde:

1	= STDEV (C4: C8)

Igen får vi det samme resultat.

Vigtig note: STDEV er en "kompatibilitetsfunktion", hvilket dybest set betyder, at Microsoft slipper af med den. Det fungerer stadig for nu, så alle ældre regneark vil fortsat fungere som normalt. Men i fremtidige versioner af Excel kan Microsoft slippe det helt, så du bør bruge STDEV.S i stedet for STDEV, hvor det er muligt.

Excel STDEVA -funktionen

STDEVA bruges også til at beregne standardafvigelsen for en prøve, men den har et par vigtige forskelle, som du skal vide om:

• SANDE værdier tælles som 1
• FALSE værdier tælles som 0
• Tekststrenge tælles som 0

Brug den som følger:

1	= STDEVA (C4: C8)

Yderligere fire venner og familiemedlemmer har givet i deres testresultater. Disse er vist i kolonne C, og kolonne D angiver, hvordan STDEVA fortolker disse data.

Fordi disse celler fortolkes som så lave værdier, skaber dette en meget bredere spredning blandt vores data, end vi så før, hvilket har øget standardafvigelsen stærkt, nu på 26.246.

Excel STDEVPA -funktionen

STDEVPA beregner standardafvigelsen for en population på samme måde som STDEV.P. Det inkluderer imidlertid også boolske værdier og tekststrenge i beregningen, som fortolkes som følger:

• SANDE værdier tælles som 1
• FALSE værdier tælles som 0
• Tekststrenge tælles som 0

Du bruger det sådan her:

1	= STDEVPA (C4: C12)

Filtrering af data før beregning af standardafvigelsen

I den virkelige verden har du ikke altid de nøjagtige data, du har brug for, i et pænt, ryddeligt bord. Ofte har du et stort regneark fuld af data, som du skal filtrere, før du beregner standardafvigelsen.

Du kan gøre dette meget let med Excel’s databasefunktioner: DSTDEV (for prøver) og DSTDEVP (for populationer).

Disse funktioner giver dig mulighed for at oprette en kriterietabel, hvor du kan definere alle de filtre, du har brug for. Funktionerne anvender disse filtre bag kulisserne, før standardafvigelsen returneres. På denne måde behøver du ikke røre ved et autofilter eller trække data ud i et separat ark - DSTDEV og SDTDEVP kan gøre alt det for dig.

Lær mere på hovedsiden for Excel DSTDEV- og DSTDEVP -funktioner <>.

STANDARD DEVIATION Funktion i Google Sheets

STANDARD DEVIATION -funktionen fungerer nøjagtig det samme i Google Sheets som i Excel: