Standardavvikelse - Excel och Google Kalkylark

Denna handledning visar hur du använder Excel standardavvikelse funktion i Excel för att beräkna standardavvikelse för en hel population.

STANDARD AVVIKNING Funktionsöversikt

STANDARD DEVIATION -funktionen beräknar beräkna standardavvikelse för en hel population.

Om du vill använda Excel -kalkylbladsfunktionen STANDARD DEVIATION väljer du en cell och skriver:

(Lägg märke till hur formelinmatningarna ser ut)

STANDARD DEVIATION -funktion Syntax och ingångar:

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

tal- Värden för att få standardvarians

Hur man beräknar standardavvikelsen i Excel

När du har att göra med data vill du köra några grundläggande tester för att hjälpa dig att förstå det. Du börjar vanligtvis med att beräkna medelvärdet med hjälp av Excel AVERAGE -funktionen <>.

Detta ger dig en uppfattning om var "mitten" av data är. Och därifrån vill du titta på hur spridda data är kring denna mittpunkt. Det är här standardavvikelsen kommer in.

Excel ger dig ett antal funktioner för att beräkna standardavvikelsen - STDEV, STDEV.P, STDEV.S och DSTDEV. Vi kommer till dem alla, men låt oss först lära oss vad standardavvikelsen är är, exakt.

Vad är standardavvikelsen?

Standardavvikelsen ger dig en uppfattning om hur långt dina datapunkter är från medelvärdet. Ta följande datauppsättning av testresultat av 100:

1	48,49,50,51,52

Medelvärdet för denna datamängd är 50 (lägg ihop alla siffror och dividera med n, där n är antalet värden i intervallet).

Titta nu på nästa uppsättning data:

1	10,25,50,75,90

Medelvärdet för denna datamängd är också 50 - men de två serierna berättar en mycket annorlunda historia. Om du bara använde medelvärdet kanske du tror att de två grupperna var ungefär lika i sin förmåga - och i genomsnitt är de det.

Men i den första gruppen har vi 5 personer som fick väldigt lika, mycket mediokra poäng. Och i den andra gruppen balanserade vi ett par high-fliers ut av ett par stackars poäng, med en person i mitten. De spridning av poängen är väldigt olika, vilket gör din tolkning av data också väldigt annorlunda.

Standardavvikelsen är ett mått på denna spridning.

Hur beräknas standardavvikelsen

För att förstå vad standardavvikelsen är och hur den fungerar kan det hjälpa att arbeta igenom ett exempel för hand. På så sätt vet du vad som händer "under huven" när vi kommer till Excel-funktionerna som du kan använda.

För att beräkna standardavvikelsen arbetar du igenom denna process:

1) Beräkna medelvärdet

Låt oss ta vår första datamängd ovan: 48,49,50,51,52

Vi vet redan medelvärdet (50), vilket jag har bekräftat här med Excel AVERAGE -funktionen <>:

1	= MEDEL (C4: C8)

2) subtrahera medelvärdet från varje värde i datamängden

Jag har gjort detta med följande formel:

1	= C4- $ H $ 4

Vår medelvärde är i H4, och jag har "låst" cellreferensen genom att sätta dollartecknen före kolumnen och raden (genom att trycka på F4). Det betyder att jag kan kopiera formeln ner i kolumnen utan att cellreferensen uppdateras.

Resultatet:

Nu, låt oss pausa här en sekund. Om du tittar på den nya kolumnen - ser du att siffrorna här blir upp till noll. Medelvärdet för dessa siffror är också noll.

Naturligtvis kan spridningen av våra data inte vara noll - vi vet att det finns en viss variation där. Vi behöver ett sätt att representera denna variation, utan att genomsnittet visar sig vara noll.

3) Kvadrera skillnaderna

Vi kan uppnå detta genom att kvadrera skillnaderna. Så, låt oss lägga till en ny kolumn och kvadrera siffrorna i kolumnen D:

1

= D4*D4

Det här ser bättre ut. Nu har vi en viss variation, och mängden variation är relaterad till hur långt varje poäng är från medelvärdet.

4) Beräkna variansen - medelvärdet för de kvadrerade skillnaderna

Nästa steg är att få genomsnittet av de kvadrerade skillnaderna. Det finns faktiskt två sätt att göra detta vid beräkning av standardavvikelsen.

• Om du använder befolkningsdata, du tar helt enkelt medelvärdet (summera värdena och dividera med n)
• Om du använder stickprov, du tar summan av värdena och dividerar med n-1

Befolkningsdata betyder att du har "hela uppsättningen" av dina data, till exempel har du data om varje elev i en given klass.

Provdata innebär att du inte har all din data, bara ett urval från en större befolkning. Vanligtvis är ditt mål med provdata att göra en uppskattning av vad värdet är i den större befolkningen.

En politisk opinionsundersökning är ett bra exempel på provdata - forskare undersöker, säg, 1 000 människor för att få en uppfattning om vad ett helt land eller en stat tänker.

Här har vi inget prov. Vi har bara fem statistiskt inriktade familjemedlemmar som vill beräkna standardavvikelsen för ett test som de alla tog. Vi har alla datapunkter och vi gör ingen uppskattning av en större grupp människor. Detta är befolkningsdata - så vi kan bara ta genomsnittet här:

1	= MEDEL (E4: E8)

OK, så vi har 2. Denna poäng är känd som "varians", och det är basen för många statistiska tester, inklusive standardavvikelsen. Du kan läsa mer om variansen på dess huvudsida: hur man beräknar varians i Excel <>.

5) Få variansens fyrkantiga rot

Vi kvadrerade våra siffror tidigare, vilket uppenbarligen blåser upp värdena lite. Så för att få figuren tillbaka i linje med de faktiska skillnaderna mellan poängen från medelvärdet måste vi kvadratrota resultatet av steg 4:

1	= SQRT (H4)

Och vi har vårt resultat: standardavvikelsen är 1.414

Eftersom vi har fyrkantiga våra tidigare kvadrerade siffror, ges standardavvikelsen i samma enheter som originaldatan. Så standardavvikelsen här är 1.414 testpunkter.

Standardavvikelse när data är mer spridda

Tidigare hade vi ett andra exempeldataintervall: 10,25,50,75,90

Bara för skojs skull, låt oss se vad som händer när vi beräknar standardavvikelsen för dessa data:

Alla formlerna är exakt desamma som tidigare (observera att det övergripande medelvärdet fortfarande är 50).

Det enda som förändrades var spridningen av poängen i kolumn C. Men nu är vår standardavvikelse mycket högre, 29,832 testpunkter.

Naturligtvis, eftersom vi bara har 5 datapunkter, är det väldigt lätt att se att spridningen av poängen är olika mellan de två uppsättningarna. Men när du har 100- eller 1000 -tal datapunkter kan du inte se det genom att bara skanna data snabbt. Och det är just därför vi använder standardavvikelsen.

Excel -funktionerna för att beräkna standardavvikelsen

Nu när du vet hur standardavvikelsen fungerar behöver du inte gå igenom hela processen för att nå standardavvikelsen. Du kan bara använda en av Excel: s inbyggda funktioner.

Excel har flera funktioner för detta ändamål:

• P beräknar standardavvikelsen för befolkningsdata (med exakt metod som vi använde i exemplet ovan)
• S beräknar standardavvikelsen för provdata (med hjälp av n-1-metoden vi berörde tidigare)
• STDEV är exakt samma som STDEV.S. Detta är en äldre funktion som har ersatts av STDEV.S och STDEV.P.
• STDEVA är mycket lik STDEV.S, förutom att den innehåller textceller och booleska (TRUE/FALSE) -celler vid beräkningen.
• STDEVPA är mycket lik STDEV.P, förutom att den innehåller textceller och booleska (TRUE/FALSE) -celler vid beräkningen.

Wow, många alternativ här! Låt dig inte skrämmas - i de allra flesta fall använder du antingen STDEV.P eller STDEV.S.

Låt oss gå igenom var och en av dessa i tur och ordning, med början med STDEV.P, eftersom det är metoden vi just arbetat igenom.

Funktionen Excel STDEV.P

STDEV.P beräknar standardavvikelsen för befolkningsdata. Du använder det så här:

1	= STDEV.P (C4: C8)

Du definierar ett argument i STDEV.P: dataområdet som du vill beräkna standardavvikelsen för.

Detta är samma exempel som vi gick igenom steg för steg ovan när vi beräknade standardavvikelsen för hand. Och som du kan se ovan får vi exakt samma resultat - 1.414.

Obs! STDEV.P ignorerar alla celler som innehåller text eller booleska (TRUE/FALSE) -värden. Om du behöver inkludera dessa, använd STDEVPA.

Excel STDEV.S -funktionen

STDEV.S beräknar standardavvikelsen för provdata. Använd det så här:

1	= STDEV.S (C4: C8)

Återigen krävs det ett argument - det dataområde som du vill veta standardavvikelsen för.

Innan vi går in på ett exempel, låt oss diskutera skillnaden mellan STDEV.S och STDEV.P.

Som vi redan har diskuterat bör STDEV.S användas på exempeldata - när dina data är en del av en större uppsättning. Så låt oss nu anta att fler i vårt exempel ovan hade tagit testet. Vi vill uppskatta standardavvikelsen för alla som gjorde testet, med hjälp av bara dessa fem poäng. Nu använder vi exempeldata.

Nu skiljer sig beräkningen från steg (4) ovan, när vi beräknar variansen - genomsnittet av den kvadrerade skillnaden för varje poäng från det totala medelvärdet.

Istället för att använda den normala metoden - summera alla värden och dela med n, skulle vi summera alla värden och dela med n-1:

1	= SUMMA (E4: E8) / (RÄKNA (E4: E8) -1)

I denna formel:

• SUM får summan av de kvadrerade skillnaderna
• COUNT returnerar vårt n, som vi subtraherar 1 från
• Vi delar sedan vår summa helt enkelt med vår n-1

Den här gången är medelvärdet för kvadratiska skillnader 2,5 (du kanske minns att det var 2 tidigare, så det är lite högre).

Så varför delar vi med n-1 istället för n när vi behandlar provdata?

Svaret är ganska komplext, och om du bara försöker köra dina siffror för att förstå dina data är det inte något du verkligen behöver bry dig om. Se bara till att du använder STDEV.S för provdata och STDEV.P för befolkningsdata, så går det bra.

Om du verkligen är nyfiken på att veta varför, se huvudsidan om hur man beräknar varians i Excel <>.

OK så vi har fått variansen för urvalet nu, så för att få standardavvikelsen för provet får vi bara kvadratroten av variansen:

1	= SQRT (H4)

Vi får 1,581.

STDEV.S gör alla ovanstående beräkningar för oss och returnerar provets standardavvikelse i bara en cell. Så låt oss se vad det kommer fram till …

1	= STDEV.S (C4: C8)

Japp, 1.581 igen.

Excel STDEV -funktionen

Excels STDEV -funktion fungerar på exakt samma sätt som STDEV.S - det vill säga den beräknar standardavvikelsen för ett urval av data.

Du använder det på samma sätt:

1	= STDEV (C4: C8)

Återigen får vi samma resultat.

Viktig notering: STDEV är en "kompatibilitetsfunktion", vilket i princip betyder att Microsoft blir av med det. Det fungerar fortfarande för närvarande, så alla äldre kalkylblad fortsätter att fungera som vanligt. Men i framtida versioner av Excel kan Microsoft släppa det helt, så du bör använda STDEV.S istället för STDEV där det är möjligt.

Excel STDEVA -funktionen

STDEVA används också för att beräkna standardavvikelsen för ett prov, men det har ett par viktiga skillnader som du behöver veta om:

• Sanna värden räknas som 1
• FALSKA värden räknas som 0
• Textsträngar räknas som 0

Använd den enligt följande:

1	= STDEVA (C4: C8)

Ytterligare fyra vänner och familjemedlemmar har gett i sina testresultat. Dessa visas i kolumn C och kolumn D anger hur STDEVA tolkar dessa data.

Eftersom dessa celler tolkas som så låga värden skapar detta en mycket bredare spridning bland våra data än vi såg tidigare, vilket har ökat standardavvikelsen kraftigt, nu på 26.246.

Excel STDEVPA -funktionen

STDEVPA beräknar standardavvikelsen för en population på samma sätt som STDEV.P. Den innehåller dock också booleska värden och textsträngar i beräkningen, vilka tolkas enligt följande:

• Sanna värden räknas som 1
• FALSKA värden räknas som 0
• Textsträngar räknas som 0

Du använder det så här:

1	= STDEVPA (C4: C12)

Filtrera data innan du beräknar standardavvikelsen

I den verkliga världen kommer du inte alltid att ha de exakta uppgifterna du behöver i ett snyggt städat bord. Ofta har du ett stort kalkylblad fullt med data, som du måste filtrera innan du beräknar standardavvikelsen.

Du kan göra detta mycket enkelt med Excel -databasfunktioner: DSTDEV (för prover) och DSTDEVP (för populationer).

Dessa funktioner gör att du kan skapa en kriterietabell där du kan definiera alla filter du behöver. Funktionerna tillämpar dessa filter bakom kulisserna innan standardavvikelsen returneras. På så sätt behöver du inte röra vid en autofilter eller dra ut data i ett separat ark - DSTDEV och SDTDEVP kan göra allt för dig.

Läs mer på huvudsidan för Excel DSTDEV- och DSTDEVP -funktioner <>.

STANDARD AVVIKNING Funktion i Google Kalkylark

STANDARD DEVIATION -funktionen fungerar exakt samma sak i Google Kalkylark som i Excel: