VARIANCE - Excel och Google Kalkylark

Denna handledning visar hur du använder Excel VARIANCE -funktion i Excel för att uppskatta varians baserat på ett givet urval.

VARIANCE Funktionsöversikt

VARIANCE -funktionen beräknar uppskattningsvariansen baserat på ett givet urval.

Om du vill använda funktionen VARIANCE Excel -kalkylblad väljer du en cell och skriver:

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

VARIANCE -funktion Syntax och ingångar:

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

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

Hur man beräknar variationen i Excel

Variansen berättar hur spridna värdena i en datamängd är från medelvärdet. Matematiskt sett är variansen genomsnittet av den kvadratiska skillnaden för varje poäng från medelvärdet (men vi kommer till det snart).

Excel ger dig ett antal funktioner för att beräkna variansen - VAR.S, VAR.P, VARA, VARPA och två äldre funktioner, VAR och VARP.

Innan vi gräver in oss i dessa funktioner och lär oss hur vi använder dem, låt oss prata om variansen och hur den beräknas.

Vad är variationen?

Vid analys av data är ett vanligt första steg att beräkna medelvärdet. Detta är naturligtvis en användbar statistik att beräkna, men det ger dig inte hela bilden av vad som händer med dina data.

Ta följande datauppsättning, som kan vara en grupp testresultat med 100 poäng:

1	48,49,50,51,52

Medelvärdet för detta intervall är 50 (summera siffrorna och dividera med n, där n är antalet värden).

Ta därefter följande uppsättning testresultat:

1	10,25,50,75,90

Medelvärdet för detta intervall är också 50 - men uppenbarligen har vi två väldigt olika dataområden här.

I sig kan medelvärdet inte berätta något om hur spridda poängen är. Det berättar inte om värdena är alla sammanslagna som i de första exemplen, eller långt ifrån varandra som det andra. Variansen kan hjälpa dig att lära dig detta.

Variansen används också som bas för en rad mer komplexa statistiska procedurer.

Hur variansen beräknas

Låt oss arbeta igenom ett grundläggande exempel och beräkna variansen för hand. På så sätt vet du vad som händer bakom kulisserna när du faktiskt börjar sätta igång Excels variansfunktioner.

Säg att vi har en datamängd som representerar tre spelkort, en 4, en 6 och 8.

För att beräkna variansen arbetar du igenom den här processen:

1) Beräkna medelvärdet

Först beräknar vi medelvärdet. Vi vet att vårt dataintervall är 4, 6, 8, så medelvärdet kommer att vara:

1	(6 + 4 + 8) / 3 = 6

Jag har bekräftat detta nedan med Excel AVERAGE -funktionen <>:

1	= MEDEL (C4: C6)

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

Därefter subtraherar vi medelvärdet från var och en av våra värden.

Jag har gjort detta med följande formel:

1	= C4- $ H $ 4

Medelvärdet lagras i H4, så jag drar det bara från varje värde i tabellen. Dollaren tecknar här bara "låser" den cellreferensen till H4, så att när jag kopierar ner den i kolumnen förblir den densamma.

Resultaten:

Vi har:

123	4 - 6 = -26 - 6 = 08 - 6 = 2

Vi måste få genomsnittet av dessa skillnader från medelvärdet, men medelvärdet för dessa tre värden är noll! Så vi måste betona skillnaderna, vilket vi gör genom att kvadrera dem.

3) Kvadrera skillnaderna

Låt oss lägga till en ny kolumn och kvadrera siffrorna i kolumnen D:

1

= D4*D4

Okej, det är bättre. Nu när skillnaderna inte är genomsnittliga till noll kan vi beräkna variansen.

4) Beräkna genomsnittet av de kvadrerade skillnaderna

Här möter vi en gaffel i vägen. Det finns två sätt att beräkna variansen, och det du använder beror på vilken typ av data du har.

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

Befolkningsdata betyder att du har totala data som du behöver, till exempel om du vill ha medelåldern för lärare i en viss skola och du har åldersdata för varje enskild lärare på den skolan, har du befolkningsdata.

Provdata innebär att du inte har all din data, bara ett urval från en större befolkning. Så om du vill ha medelåldern för lärare i hela landet, och du bara har data om lärare på en skola, har du provdata.

I vårt exempel har vi befolkningsdata. Vi är bara intresserade av våra tre kort - det är befolkningen, och vi har inte tagit ett prov från dem. Så vi kan bara ta genomsnittet av de kvadrerade skillnaderna på normalt sätt:

1	= MEDEL (E4: E8)

Så variansen i vår befolkning är 2.666.

Om det här var exempeldata (kanske vi hade dragit ut dessa tre kort ur en större uppsättning), skulle vi räkna ut genomsnittet enligt följande:

1	Provvarians = (4 + 0 + 4) / (3 - 1)

Eller:

1	Provvarians = 8 /2 = 4

Varför dividera med n-1 med provdata, istället för bara n?

Det korta svaret på denna fråga är "Eftersom det ger rätt svar". Men jag föreställer mig att du vill ha lite mer än så! Detta är ett komplext ämne så jag ger bara en kort översikt här.

Tänk på det så här: om du tar ett urval av data från en befolkning tenderar dessa värden att vara närmare medelvärdet för prov än de är till medelvärdet av befolkning.

Det betyder att om du bara dividerar med n kommer du att underskatta befolkningsvariansen lite. Att dividera med n-1 korrigerar lite för detta.

Med vår uppsättning av tre kort är vi på ett bra ställe att testa denna teori. Eftersom det bara finns tre kort finns det ett litet antal prover vi kan ta.

Låt oss ta prov på två kort. Vi väljer ett kort, lägger tillbaka det, blandar och väljer ett annat kort. Det betyder att det finns nio kombinationer av två kort vi kan välja.

Med bara nio möjliga prover kan vi beräkna varje möjlig provvarians med båda metoderna (dividera med n och dividera med n-1), ta genomsnittet av dem och se vilket som ger oss rätt svar.

I tabellen nedan har jag lagt upp allt. Varje rad i tabellen är ett annat urval, och kolumnerna B och C visar de två kort som plockades i varje prov. Sedan har jag lagt till ytterligare två kolumner: en där jag beräknade variansen för det exemplet på två kort genom att dividera med n, och en annan där jag delade med n - 1.

Ta en titt:

Till höger om tabellen har jag visat medelvärdena för kolumnerna D och E.

Medelvärdet för kolumn D, när vi dividerar med n, ger oss en varians på 1,333.

Medelvärdet för kolumn E, när vi dividerar med n-1, ger oss en varians på 2,666.

Vi vet redan från vårt tidigare exempel att variationen i befolkningen är 2.666. Så att dela med n-1 när vi använder provdata ger oss mer exakta uppskattningar.

Excel -funktionerna för att beräkna variansen

Nu när du har sett ett exempel på hur variansen beräknas, låt oss gå vidare till Excel -funktionerna.

Du har flera alternativ här:

• P returnerar variansen för befolkningsdata (med hjälp av metoden dividera med n)
• S returnerar variansen för provdata (divideras med n-1)
• VAR är en äldre funktion som fungerar på exakt samma sätt som VAR.S
• VARA är samma som VAR.S, förutom att den innehåller textceller och booleska värden
• VARPA är samma som VAR.P, förutom att den innehåller textceller och booleska värden

Låt oss gå igenom dessa en efter en.

Funktionen Excel VAR.P

VAR.P beräknar variansen för befolkningsdata (med hjälp av metoden dividera med n). Använd det så här:

1	= VAR.P (C4: C6)

Du definierar bara ett argument i VAR.P: dataområdet som du vill beräkna variansen för. I vårt fall här är det kortvärdena i C4: C6.

Som du ser ovan returnerar VAR.P 2.666 för vår uppsättning med tre kort. Detta är samma värde som vi beräknade för hand tidigare.

Observera att VAR.P helt ignorerar celler som innehåller text eller booleska (TRUE/FALSE) -värden. Om du behöver inkludera dessa, använd VARPA istället.

Excel VAR.S -funktionen

VAR.S beräknar variansen för provdata (dividerat med n-1). Du använder det så här:

1	= VAR.S (C4: C6)

Återigen finns det bara ett argument - ditt dataintervall.

I det här fallet returnerar VAR.S 4. Vi fick samma siffra i steg 4 när vi gjorde den manuella beräkningen ovan.

VAR.S ignorerar helt celler som innehåller text eller booleska (TRUE/FALSE) -värden. Om du behöver inkludera dessa, använd VARA istället.

Excel VAR -funktionen

VAR är helt ekvivalent med VAR.S: det beräknar varianserna för provdata (med n-1-metoden). Så här använder du det:

1	= VAR (C4: C6)

VAR är en "kompatibilitetsfunktion". Det betyder att Microsoft håller på att ta bort den här funktionen från Excel. För närvarande är det fortfarande tillgängligt för användning, men du bör använda VAR.S istället så att dina kalkylblad förblir kompatibla med framtida versioner av Excel.

Excel VARA -funktionen

VARA returnerar också variansen av provdata, men den har några viktiga skillnader mot VAR och VAR.S. Det innehåller nämligen booleska och textvärden i beräkningen:

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

Så här använder du det:

1	= VARA (C4: C11)

Vi har lagt till fem rader till i tabellen: J, Q, K, TRUE och FALSE. Kolumn D visar hur VARA tolkar dessa värden.

Eftersom vi har en ny sats med låga värden i vår tabell nu har variansen ökat till 10.268.

Excel VARPA -funktionen

VARPA beräknar variansen för befolkningsdata. Det liknar VAR.P, förutom att det också innehåller booleska värden och textsträngar i beräkningen:

• 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	= VARPA (C4: C12)

Vi har lagt till fem rader till i tabellen: J, Q, K, TRUE och FALSE. Kolumn D visar hur VARPA tolkar dessa värden.

Som ett resultat av att denna grupp lägre värden läggs till i data har variansen ökat till 8,984.

VARIANCE -funktion i Google Kalkylark

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