VARIANCE - Excel og Google Sheets

Denne vejledning viser, hvordan du bruger Excel VARIANCE -funktion i Excel for at estimere varians baseret på en given prøve.

VARIANCE Funktionsoversigt

VARIANCE -funktionen Beregner estimatvariansen baseret på en given prøve.

Hvis du vil bruge funktionen VARIANCE Excel -regneark, skal du vælge en celle og skrive:

(Bemærk hvordan formelindgangene vises)

VARIANCE -funktion Syntaks og input:

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

tal- Værdier for at få variation

Sådan beregnes variationen i Excel

Variansen fortæller dig, hvor spredt værdierne i et datasæt er fra middelværdien. Matematisk set er variansen gennemsnittet af den kvadrerede forskel for hver score fra middelværdien (men vi kommer snart til det).

Excel giver dig en række funktioner til at beregne variansen - VAR.S, VAR.P, VARA, VARPA og to ældre funktioner, VAR og VARP.

Inden vi graver ind i disse funktioner og lærer at bruge dem, lad os tale om variansen, og hvordan den beregnes.

Hvad er variationen?

Ved analyse af data er et almindeligt første trin at beregne middelværdien. Dette er naturligvis en nyttig statistik at beregne, men det giver dig ikke det fulde billede af, hvad der foregår med dine data.

Tag følgende datasæt, som kan være en gruppe af testresultater scoret ud af 100:

1	48,49,50,51,52

Middelværdien af ​​dette område er 50 (sum tallene og divider med n, hvor n er antallet af værdier).

Tag derefter følgende sæt testresultater:

1	10,25,50,75,90

Middelværdien af ​​dette område er også 50 - men vi har naturligvis to meget forskellige dataområder her.

I sig selv kan middelværdien ikke fortælle dig noget om, hvor spredt scorerne er. Det fortæller dig ikke, om værdierne alle er samlet som i de første eksempler eller er vidt forskellige som det andet. Afvigelsen kan hjælpe dig med at lære dette.

Variansen bruges også som udgangspunkt for en række mere komplekse statistiske procedurer.

Sådan beregnes variansen

Lad os gennemgå et grundeksempel og beregne variansen i hånden. På denne måde ved du, hvad der foregår bag kulisserne, når du rent faktisk begynder at sætte Excel's variansfunktioner i aktion.

Sig, at vi har et datasæt, der repræsenterer tre spillekort, et 4, et 6 og 8.

For at beregne variansen arbejder du igennem denne proces:

1) Beregn middelværdien

Først beregner vi middelværdien. Vi ved, at vores dataområde er 4, 6, 8, så gennemsnittet bliver:

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

Jeg har bekræftet dette nedenfor med Excel GENNEMGANGSFunktionen <>:

1	= Gennemsnit (C4: C6)

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

Dernæst trækker vi middelværdien fra hver af vores værdier.

Jeg har gjort dette med følgende formel:

1	= C4- $ H $ 4

Middelværdien er gemt i H4, så jeg trækker det bare fra hver værdi i tabellen. Dollaren underskriver her bare "lås" den cellehenvisning til H4, så når jeg kopierer den ned i kolonnen, forbliver den den samme.

Resultaterne:

Vi har fået:

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

Vi er nødt til at få gennemsnittet af disse forskelle fra middelværdien, men middelværdien af ​​disse tre værdier er nul! Så vi skal understrege forskellene, som vi gør ved at kvadrere dem.

3) Kvadratér forskellene

Lad os tilføje en ny kolonne og kvadrere tallene i kolonnen D:

1

= D4*D4

Okay, det er bedre. Nu hvor forskellene ikke er gennemsnitlige til nul, kan vi beregne variansen.

4) Beregn gennemsnittet af de kvadrerede forskelle

Her møder vi en gaffel i vejen. Der er to måder at beregne variansen på, og den du bruger afhænger af den type data, du har.

• Hvis du bruger befolkningsdata, du tager simpelthen middelværdien som normal (opsummer værdierne og divider med n)
• Hvis du bruger eksempeldata, summerer du værdierne og dividerer med n-1

Befolkningsdata betyder, at du har totaliteten af ​​de data, du har brug for, for eksempel hvis du vil have gennemsnitsalderen for lærere på en bestemt skole, og du har aldersdata for hver enkelt lærer på den skole, har du befolkningsdata.

Eksempeldata betyder, at du ikke har alle dine data, kun en prøve taget fra en større population. Så hvis du vil have gennemsnitsalderen for lærere i hele landet, og du kun har data om lærere på en skole, har du eksempeldata.

I vores eksempel har vi befolkningsdata. Vi er kun interesserede i vores tre kort - det er befolkningen, og vi har ikke taget en prøve fra dem. Så vi kan bare tage gennemsnittet af de kvadrerede forskelle på den normale måde:

1	= Gennemsnit (E4: E8)

Så variansen af ​​vores befolkning er 2.666.

Hvis dette var eksempeldata (måske havde vi trukket disse tre kort ud af et større sæt), ville vi beregne gennemsnittet som følger:

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

Eller:

1	Prøvevarians = 8 /2 = 4

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

Det korte svar på dette spørgsmål er "Fordi det giver det rigtige svar". Men jeg forestiller mig, at du vil have lidt mere end det! Dette er et komplekst emne, så jeg vil lige give et kort overblik her.

Tænk på det sådan: Hvis du tager en stikprøve af data fra en befolkning, vil disse værdier have en tendens til at være tættere på middelværdien af prøve end de er til middelværdien af befolkning.

Det betyder, at hvis du bare dividerer med n, vil du undervurdere befolkningsvariansen lidt. Deling med n-1 korrigerer lidt for dette.

Med vores sæt med tre kort er vi et godt sted at teste denne teori. Fordi der kun er tre kort, er der et lille antal prøver, vi muligvis kan tage.

Lad os tage prøver af to kort. Vi vælger et kort, lægger det tilbage, blander og vælger derefter et andet kort. Det betyder, at der er ni kombinationer af to kort, vi kan vælge.

Med kun ni mulige prøver kan vi beregne alle mulige prøvevarianser ved hjælp af begge metoder (dividere med n og dividere med n-1), tage gennemsnittet af dem og se, hvilken der giver os det rigtige svar.

I tabellen herunder har jeg lagt alt ud. Hver række i tabellen er en anden prøve, og kolonne B og C viser de to kort, der blev plukket i hver prøve. Derefter har jeg tilføjet yderligere to kolonner: en, hvor jeg beregnede variansen for denne prøve på to kort ved at dividere med n, og en anden, hvor jeg delte med n - 1.

Tag et kig:

Til højre for tabellen har jeg vist gennemsnitsværdierne for kolonne D og E.

Gennemsnittet af kolonne D, når vi dividerer med n, giver os en varians på 1,333.

Gennemsnittet af kolonne E, når vi dividerer med n-1, giver os en varians på 2,666.

Vi ved allerede fra vores tidligere eksempel, at variationen af ​​befolkningen er 2.666. Så dividere med n-1 ved brug af prøvedata giver os mere præcise estimater.

Excel -funktionerne til beregning af variationen

Nu hvor du har set et eksempel på, hvordan variansen beregnes, lad os gå videre til Excel -funktionerne.

Du har flere muligheder her:

• P returnerer variansen for befolkningsdata (ved hjælp af metoden divider med n)
• S returnerer variansen for eksempeldata (divideret med n-1)
• VAR er en ældre funktion, der fungerer på nøjagtig samme måde som VAR.S
• VARA er det samme som VAR.S, bortset fra at det indeholder tekstceller og boolske værdier
• VARPA er det samme som VAR.P, bortset fra at det indeholder tekstceller og boolske værdier

Lad os gå igennem disse en-for-en.

Funktionen Excel VAR.P

VAR.P beregner variansen for populationsdata (ved hjælp af metoden divider med n). Brug den sådan her:

1	= VAR.P (C4: C6)

Du definerer kun et argument i VAR.P: dataområdet, som du vil beregne variansen for. I vores tilfælde her er det kortværdierne i C4: C6.

Som du ser ovenfor returnerer VAR.P 2.666 for vores sæt med tre kort. Det er den samme værdi, som vi tidligere har beregnet i hånden.

Bemærk, at VAR.P helt ignorerer celler, der indeholder tekst eller boolske (TRUE/FALSE) værdier. Hvis du har brug for at inkludere disse, skal du bruge VARPA i stedet.

Funktionen Excel VAR.S

VAR.S beregner variansen for eksempeldata (divideret med n-1). Du bruger det sådan her:

1	= VAR.S (C4: C6)

Igen er der kun ét argument - dit dataområde.

I dette tilfælde returnerer VAR.S 4. Vi fik det samme tal i trin 4, da vi lavede den manuelle beregning ovenfor.

VAR.S ignorerer fuldstændigt celler, der indeholder tekst eller boolske (TRUE/FALSE) værdier. Hvis du har brug for at inkludere disse, skal du bruge VARA i stedet.

Excel VAR -funktionen

VAR er fuldstændig ækvivalent med VAR.S: det beregner variationerne for eksempeldata (ved hjælp af n-1-metoden). Sådan bruges det:

1	= VAR (C4: C6)

VAR er en "kompatibilitetsfunktion". Det betyder, at Microsoft er i gang med at fjerne denne funktion fra Excel. I øjeblikket er den stadig tilgængelig til brug, men du bør bruge VAR.S i stedet, så dine regneark forbliver kompatible med fremtidige versioner af Excel.

Excel VARA -funktionen

VARA returnerer også variansen af ​​prøvedata, men det har nogle vigtige forskelle til VAR og VAR.S. Det inkluderer nemlig boolske og tekstværdier i beregningen:

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

Sådan bruger du det:

1	= VARA (C4: C11)

Vi har tilføjet fem rækker mere til tabellen: J, Q, K, TRUE og FALSE. Kolonne D viser, hvordan VARA fortolker disse værdier.

Fordi vi har en ny batch med lave værdier i vores tabel nu, er variansen steget til 10.268.

Excel VARPA -funktionen

VARPA beregner variansen for befolkningsdata. Det ligner VAR.P, bortset fra at det også inkluderer boolske værdier og tekststrenge i beregningen:

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

Vi har tilføjet fem rækker mere til tabellen: J, Q, K, TRUE og FALSE. Kolonne D viser, hvordan VARPA fortolker disse værdier.

Som et resultat af tilføjelse af denne gruppe af lavere værdier til dataene er variansen steget til 8,984.

VARIANCE -funktion i Google Sheets

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