VARIANCE - Excel og Google Sheets

Denne opplæringen viser hvordan du bruker Excel VARIANCE -funksjon i Excel for å estimere varians basert på en gitt prøve.

VARIANCE Funksjonsoversikt

VARIANCE -funksjonen Beregner estimatvariansen basert på en gitt prøve.

For å bruke funksjonen VARIANCE Excel -regneark, velg en celle og skriv:

(Legg merke til hvordan formelinngangene vises)

VARIANCE -funksjon Syntaks og innganger:

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

tall- Verdier for å få Variance

Hvordan beregne variansen i Excel

Variansen forteller deg hvor spredt verdiene i et datasett er fra gjennomsnittet. Matematisk sett er variansen gjennomsnittet av den kvadratiske forskjellen for hver poengsum fra gjennomsnittet (men vi kommer til det snart).

Excel gir deg en rekke funksjoner for å beregne variansen - VAR.S, VAR.P, VARA, VARPA og to eldre funksjoner, VAR og VARP.

Før vi graver oss inn i disse funksjonene og lærer hvordan vi bruker dem, la oss snakke om variansen og hvordan den beregnes.

Hva er variasjonen?

Når du analyserer data, er et vanlig første trinn å beregne gjennomsnittet. Dette er selvfølgelig en nyttig statistikk å beregne, men det gir deg ikke det fulle bildet av hva som skjer med dataene dine.

Ta følgende datasett, som kan være en gruppe testresultater scoret av 100:

1	48,49,50,51,52

Gjennomsnittet for dette området er 50 (summer tallene og divider med n, hvor n er antall verdier).

Ta deretter følgende sett med testresultater:

1	10,25,50,75,90

Gjennomsnittet for dette området er også 50 - men vi har åpenbart to veldig forskjellige dataområder her.

I seg selv kan gjennomsnittet ikke fortelle deg noe om hvor spredt resultatene er. Det forteller deg ikke om verdiene er samlet sammen som i de første eksemplene, eller langt fra hverandre som det andre. Variasjonen kan hjelpe deg med å lære dette.

Variansen brukes også som et utgangspunkt for en rekke mer komplekse statistiske prosedyrer.

Slik beregnes variansen

La oss gå gjennom et grunneksempel, og beregne variansen for hånd. På denne måten vet du hva som skjer bak kulissene når du faktisk begynner å sette Excel-variansfunksjonene i verk.

Si at vi har et datasett som representerer tre spillekort, et 4, et 6 og 8.

For å beregne variansen, arbeider du gjennom denne prosessen:

1) Beregn gjennomsnittet

Først beregner vi gjennomsnittet. Vi vet at dataområdet vårt er 4, 6, 8, så gjennomsnittet kommer til å være:

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

Jeg har bekreftet dette nedenfor med Excel AVERAGE -funksjonen <>:

1	= GJENNOMSNITT (C4: C6)

2) Trekk gjennomsnittet fra hver verdi i datasettet

Deretter trekker vi gjennomsnittet fra hver av våre verdier.

Jeg har gjort dette med følgende formel:

1	= C4- $ H $ 4

Gjennomsnittet er lagret i H4, så jeg trekker det bare fra hver verdi i tabellen. Dollaren signerer her bare "lås" den cellehenvisningen til H4, slik at når jeg kopierer den ned i kolonnen, forblir den den samme.

Resultatene:

Vi har fått:

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

Vi må få gjennomsnittet av disse forskjellene fra gjennomsnittet, men gjennomsnittet av disse tre verdiene er null! Så vi må understreke forskjellene, som vi gjør ved å kvadrere dem.

3) Kvadrer forskjellene

La oss legge til en ny kolonne og kvadrere tallene i kolonnen D:

1

= D4*D4

Ok, det er bedre. Nå som forskjellene ikke er gjennomsnittlig null, kan vi beregne variansen.

4) Beregn gjennomsnittet av de kvadrerte forskjellene

Her møter vi en gaffel i veien. Det er to måter å beregne variansen på, og den du bruker avhenger av hvilken type data du har.

• Hvis du bruker befolkningsdata, du tar bare gjennomsnittet som normalt (oppsummer verdiene og divider med n)
• Hvis du bruker eksempeldata, summerer du verdiene og deler med n-1

Befolkningsdata betyr at du har totaliteten av dataene du trenger, for eksempel hvis du vil ha gjennomsnittsalderen til lærere på en bestemt skole, og du har aldersdata for hver enkelt lærer på den skolen, har du befolkningsdata.

Eksempeldata betyr at du ikke har alle dataene dine, bare et utvalg tatt fra en større befolkning. Så hvis du vil ha gjennomsnittsalderen for lærere i hele landet, og du bare har data om lærere på en skole, har du eksempeldata.

I vårt eksempel har vi befolkningsdata. Vi er bare interessert i våre tre kort - det er befolkningen, og vi har ikke tatt et utvalg av dem. Så vi kan bare ta gjennomsnittet av de kvadrerte forskjellene på normal måte:

1	= GJENNOMSNITT (E4: E8)

Så variansen i befolkningen vår er 2.666.

Hvis dette var eksempeldata (kanskje vi hadde trukket disse tre kortene ut av et større sett), ville vi regnet ut gjennomsnittet slik:

1	Prøvevarians = (4 + 0 + 4) / (3 - 1)

Eller:

1	Prøvevarians = 8 /2 = 4

Hvorfor dele med n-1 med prøvedata, i stedet for bare n?

Det korte svaret på dette spørsmålet er "Fordi det gir det riktige svaret". Men jeg forestiller meg at du vil ha litt mer enn det! Dette er et komplekst tema, så jeg vil bare gi en kort oversikt her.

Tenk på det slik: Hvis du tar et utvalg av data fra en befolkning, vil disse verdiene ha en tendens til å være nærmere gjennomsnittet av prøve enn de er til gjennomsnittet av befolkning.

Dette betyr at hvis du bare deler med n, vil du undervurdere populasjonsvariansen litt. Deling med n-1 korrigerer litt for dette.

Med vårt sett med tre kort er vi et godt sted å teste denne teorien. Fordi det bare er tre kort, er det et lite antall prøver vi muligens kan ta.

La oss ta prøver av to kort. Vi velger ett kort, legger det tilbake, blander, og velger deretter et annet kort. Det betyr at det er ni kombinasjoner av to kort vi kan velge.

Med bare ni mulige prøver kan vi beregne alle mulige utvalgsvarianser ved å bruke begge metodene (dividere med n og dele med n-1), ta gjennomsnittet av dem og se hvilken som gir oss det riktige svaret.

I tabellen nedenfor har jeg lagt alt ut. Hver rad i tabellen er en annen prøve, og kolonne B og C viser de to kortene som ble plukket i hver prøve. Så har jeg lagt til ytterligere to kolonner: en der jeg beregnet variansen til prøven på to kort ved å dividere med n, og en annen hvor jeg delte med n - 1.

Ta en titt:

Til høyre for tabellen har jeg vist gjennomsnittet av kolonnene D og E.

Gjennomsnittet for kolonne D, når vi deler med n, gir oss en varians på 1,333.

Gjennomsnittet i kolonne E, når vi deler med n-1, gir oss en varians på 2,666.

Vi vet allerede fra vårt tidligere eksempel at variansen av befolkningen er 2.666. Så dividere med n-1 ved bruk av eksempeldata gir oss mer nøyaktige estimater.

Excel -funksjonene for å beregne variansen

Nå som du har sett et eksempel på hvordan variansen beregnes, la oss gå videre til Excel -funksjonene.

Du har flere alternativer her:

• P returnerer variansen for populasjonsdata (ved å dele metoden med n)
• S returnerer variansen for eksempeldata (divideres med n-1)
• VAR er en eldre funksjon som fungerer på nøyaktig samme måte som VAR.S
• VARA er det samme som VAR.S, bortsett fra at den inneholder tekstceller og boolske verdier
• VARPA er det samme som VAR.P, bortsett fra at den inneholder tekstceller og boolske verdier

La oss gå gjennom disse en etter en.

Excel VAR.P -funksjonen

VAR.P beregner variansen for populasjonsdata (ved å dele metoden n). Bruk den slik:

1	= VAR.P (C4: C6)

Du definerer bare ett argument i VAR.P: dataområdet du vil beregne variansen for. I vårt tilfelle her er det kortverdiene i C4: C6.

Som du ser ovenfor returnerer VAR.P 2.666 for vårt sett med tre kort. Dette er den samme verdien som vi beregnet for hånd tidligere.

Vær oppmerksom på at VAR.P helt ignorerer celler som inneholder tekst eller boolske (TRUE/FALSE) verdier. Hvis du trenger å inkludere disse, bruker du VARPA i stedet.

Excel VAR.S -funksjonen

VAR.S beregner variansen for eksempeldata (dividert med n-1). Du bruker det slik:

1	= VAR.S (C4: C6)

Igjen er det bare ett argument - dataområdet ditt.

I dette tilfellet returnerer VAR.S 4. Vi fikk det samme tallet i trinn 4 da vi gjorde den manuelle beregningen ovenfor.

VAR.S ignorerer helt celler som inneholder tekst eller boolske (TRUE/FALSE) verdier. Hvis du trenger å inkludere disse, bruker du VARA i stedet.

Excel VAR -funksjonen

VAR er helt ekvivalent med VAR.S: det beregner avvikene for eksempeldata (ved bruk av n-1-metoden). Slik bruker du det:

1	= VAR (C4: C6)

VAR er en "kompatibilitetsfunksjon". Dette betyr at Microsoft er i ferd med å fjerne denne funksjonen fra Excel. For øyeblikket er det fortsatt tilgjengelig for bruk, men du bør bruke VAR.S i stedet, slik at regnearkene dine forblir kompatible med fremtidige versjoner av Excel.

Excel VARA -funksjonen

VARA returnerer også variansen av eksempeldata, men den har noen viktige forskjeller til VAR og VAR.S. Det inkluderer nemlig boolske og tekstverdier i beregningen:

• Sanne verdier regnes som 1
• FALSE verdier regnes som 0
• Tekststrenger telles som 0

Slik bruker du det:

1	= VARA (C4: C11)

Vi har lagt til fem rader til i tabellen: J, Q, K, TRUE og FALSE. Kolonne D viser hvordan VARA tolker disse verdiene.

Fordi vi har en ny gruppe med lave verdier i tabellen vår nå, har variansen økt til 10.268.

Excel VARPA -funksjonen

VARPA beregner variansen for populasjonsdata. Det ligner på VAR.P, bortsett fra at det også inkluderer boolske verdier og tekststrenger i beregningen:

• Sanne verdier regnes som 1
• FALSE verdier regnes som 0
• Tekststrenger telles som 0

Du bruker det slik:

1	= VARPA (C4: C12)

Vi har lagt til fem rader til i tabellen: J, Q, K, TRUE og FALSE. Kolonne D viser hvordan VARPA tolker disse verdiene.

Som et resultat av å legge denne gruppen med lavere verdier til dataene, har variansen økt til 8,984.

VARIANCE -funksjon i Google Regneark

CORREL -funksjonen fungerer nøyaktig det samme i Google Sheets som i Excel: