VARIANTIE - Excel & Google Spreadsheets

Deze zelfstudie laat zien hoe u de Excel VARIANCE-functie in Excel om de variantie te schatten op basis van een bepaalde steekproef.

VARIANCE Functieoverzicht

De VARIANCE-functie Berekent de geschatte variantie op basis van een gegeven steekproef.

Om de VARIANCE Excel-werkbladfunctie te gebruiken, selecteert u een cel en typt u:

(Let op hoe de formule-invoer verschijnt)

VARIANCE-functie Syntaxis en invoer:

1	=VAR(getal1,[getal2],… )

nummers- Waarden om variantie te krijgen

Hoe de variantie in Excel te berekenen

De variantie vertelt u hoe verspreid de waarden in een gegevensset zijn ten opzichte van het gemiddelde. Wiskundig gesproken is de variantie het gemiddelde van het gekwadrateerde verschil van elke score van het gemiddelde (maar daar komen we binnenkort op terug).

Excel geeft je een aantal functies om de variantie te berekenen - VAR.S, VAR.P, VARA, VARPA en twee oudere functies, VAR en VARP.

Voordat we dieper ingaan op deze functies en leren hoe ze te gebruiken, laten we het hebben over de variantie en hoe deze wordt berekend.

Wat is de variantie?

Bij het analyseren van gegevens is een gebruikelijke eerste stap het berekenen van het gemiddelde. Dit is natuurlijk een handige statistiek om te berekenen, maar het geeft je niet het volledige beeld van wat er met je gegevens gebeurt.

Neem de volgende gegevensset, die een groep testresultaten kan zijn die op 100 worden gescoord:

1	48,49,50,51,52

Het gemiddelde van dit bereik is 50 (de getallen optellen en delen door n, waarbij n het aantal waarden is).

Neem vervolgens de volgende set testresultaten:

1	10,25,50,75,90

Het gemiddelde van dit bereik is ook 50 - maar het is duidelijk dat we hier twee heel verschillende gegevensbereiken hebben.

Op zichzelf kan het gemiddelde je niets vertellen over hoe verspreid de scores zijn. Het vertelt je niet of de waarden allemaal gebundeld zijn zoals in de eerste voorbeelden, of ver uit elkaar zoals de tweede. De variantie kan je helpen dit te leren.

De variantie wordt ook gebruikt als basis voor een reeks complexere statistische procedures.

Hoe de variantie wordt berekend

Laten we een eenvoudig voorbeeld doornemen en de variantie met de hand berekenen. Op deze manier weet u wat er achter de schermen gebeurt wanneer u de variantiefuncties van Excel daadwerkelijk gaat gebruiken.

Stel dat we een dataset hebben die drie speelkaarten vertegenwoordigt, een 4, een 6 en 8.

Om de variantie te berekenen, doorloopt u dit proces:

1) Bereken het gemiddelde

Eerst berekenen we het gemiddelde. We weten dat ons gegevensbereik 4, 6, 8 is, dus het gemiddelde wordt:

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

Ik heb dit hieronder bevestigd met de Excel GEMIDDELDE Functie<>:

1	=GEMIDDELDE(C4:C6)

2) Trek het gemiddelde af van elke waarde in de gegevensset

Vervolgens trekken we het gemiddelde van elk van onze waarden af.

Ik heb dit gedaan met de volgende formule:

1

=C4-$H$4

Het gemiddelde wordt opgeslagen in H4, dus ik trek dat gewoon af van elke waarde in de tabel. De dollartekens hier "vergrendelen" die celverwijzing naar H4, zodat wanneer ik deze in de kolom kopieer, deze hetzelfde blijft.

De resultaten:

Wij hebben:

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

We moeten het gemiddelde van deze verschillen uit het gemiddelde halen, maar het gemiddelde van deze drie waarden is nul! Dus we moeten de verschillen benadrukken, wat we doen door ze te kwadrateren.

3) Vier de verschillen

Laten we een nieuwe kolom toevoegen en de getallen in kolom D kwadrateren:

1

=D4*D4

Oké, dat is beter. Nu de verschillen niet gemiddeld tot nul uitkomen, kunnen we de variantie berekenen.

4) Bereken het gemiddelde van de gekwadrateerde verschillen

Hier ontmoeten we een splitsing in de weg. Er zijn twee manieren om de variantie te berekenen, en de methode die u gebruikt, hangt af van het type gegevens dat u heeft.

• Als u gebruik maakt van bevolkingsgegevens, je neemt gewoon het gemiddelde als normaal (som de waarden op en deel je door n)
• Als u gebruik maakt van voorbeeldgegevens, tel je de waarden op en deel je door n-1

Bevolkingsgegevens betekent dat u alle gegevens hebt die u nodig hebt, bijvoorbeeld als u de gemiddelde leeftijd van leraren op een bepaalde school wilt en u hebt de leeftijdsgegevens voor elke leraar op die school, u hebt bevolkingsgegevens.

Voorbeeldgegevens betekent dat u niet al uw gegevens hebt, alleen een steekproef uit een grotere populatie. Dus als je de gemiddelde leeftijd van leraren in het hele land wilt, en je hebt alleen gegevens over leraren op één school, dan heb je voorbeeldgegevens.

In ons voorbeeld hebben we bevolkingsgegevens. We zijn alleen geïnteresseerd in onze drie kaarten - dat is de populatie en we hebben er geen steekproef van genomen. We kunnen dus gewoon het gemiddelde nemen van de gekwadrateerde verschillen op de normale manier:

1	=GEMIDDELDE(E4:E8)

Dus de variantie van onze populatie is 2.666.

Als dit was voorbeeldgegevens (misschien hadden we deze drie kaarten uit een grotere set gehaald), zouden we het gemiddelde als volgt berekenen:

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

Of:

1	Steekproefvariantie = 8 / 2 = 4

Waarom delen door n-1 met voorbeeldgegevens, in plaats van alleen n?

Het korte antwoord op deze vraag is “Omdat het het juiste antwoord geeft”. Maar ik kan me voorstellen dat je iets meer wilt dan dat! Dit is een complex onderwerp, dus ik zal hier een kort overzicht geven.

Zie het als volgt: als u een steekproef van gegevens uit een populatie neemt, zullen die waarden meestal dichter bij het gemiddelde van de steekproef dan ze zijn tot het gemiddelde van de bevolking.

Dit betekent dat als je gewoon deelt door n, je de populatievariantie een beetje onderschat. Delen door n-1 corrigeert hiervoor een beetje.

Met onze set van drie kaarten zijn we op een goede plek om deze theorie te testen. Omdat er maar drie kaarten zijn, is er een klein aantal monsters dat we eventueel kunnen nemen.

Laten we voorbeelden nemen van twee kaarten. We kiezen een kaart, leggen deze terug, schudden en kiezen dan een andere kaart. Dat betekent dat we negen combinaties van twee kaarten kunnen kiezen.

Met slechts negen mogelijke steekproeven kunnen we met beide methoden alle mogelijke steekproefvariantie berekenen (delen door n en delen door n-1), het gemiddelde nemen en zien welke ons het juiste antwoord geeft.

In onderstaande tabel heb ik alles op een rijtje gezet. Elke rij van de tafel is een ander voorbeeld en de kolommen B en C tonen de twee kaarten die in elk voorbeeld zijn gekozen. Daarna heb ik nog twee kolommen toegevoegd: een waarin ik de variantie van die steekproef van twee kaarten heb berekend door te delen door n, en een andere waarin ik heb gedeeld door n - 1.

Kijk eens:

Rechts van de tabel heb ik de gemiddelden van de kolommen D en E weergegeven.

Het gemiddelde van kolom D, gedeeld door n, geeft ons een variantie van 1,333.

Het gemiddelde van kolom E, gedeeld door n-1, geeft ons een variantie van 2.666.

Uit ons vorige voorbeeld weten we al dat de variantie van de populatie 2.666 is. Dus delen door n-1 bij het gebruik van voorbeeldgegevens geeft ons nauwkeurigere schattingen.

De Excel-functies om de variantie te berekenen

Nu u een voorbeeld hebt gezien van hoe de variantie wordt berekend, gaan we verder met de Excel-functies.

Je hebt hier verschillende opties:

• P retourneert de variantie voor populatiegegevens (met behulp van de methode delen door n)
• S retourneert de variantie voor voorbeeldgegevens (gedeeld door n-1)
• VAR is een oudere functie die op precies dezelfde manier werkt als VAR.S
• VARA is hetzelfde als VAR.S, behalve dat het tekstcellen en Booleaanse waarden bevat
• VARPA is hetzelfde als VAR.P, behalve dat het tekstcellen en Booleaanse waarden bevat

Laten we deze één voor één doornemen.

De Excel VAR.P-functie

VAR.P berekent de variantie voor populatiegegevens (met behulp van de methode van delen door n). Gebruik het als volgt:

1	=VAR.P(C4:C6)

U definieert slechts één argument in VAR.P: het gegevensbereik waarvoor u de variantie wilt berekenen. In ons geval zijn dat de kaartwaarden in C4:C6.

Zoals je hierboven ziet, geeft VAR.P 2.666 terug voor onze set van drie kaarten. Dit is dezelfde waarde die we eerder met de hand hebben berekend.

Merk op dat VAR.P cellen met tekst of Booleaanse (TRUE/FALSE) waarden volledig negeert. Als u deze moet opnemen, gebruikt u in plaats daarvan VARPA.

De Excel VAR.S-functie

VAR.S berekent de variantie voor steekproefgegevens (delen door n-1). Je gebruikt het als volgt:

1	=VAR.S(C4:C6)

Nogmaals, er is maar één argument: uw gegevensbereik.

In dit geval retourneert VAR.S 4. We kregen hetzelfde cijfer in stap 4 toen we de handmatige berekening hierboven deden.

VAR.S negeert volledig cellen die tekst of Booleaanse (TRUE/FALSE) waarden bevatten. Als u deze moet opnemen, gebruikt u in plaats daarvan VARA.

De Excel VAR-functie

VAR is volledig gelijk aan VAR.S: het berekent de varianties voor steekproefgegevens (met behulp van de n-1-methode). Hier is hoe het te gebruiken:

1	=VAR(C4:C6)

VAR is een "compatibiliteitsfunctie". Dit betekent dat Microsoft bezig is deze functie uit Excel te verwijderen. Op dit moment is het nog steeds beschikbaar voor gebruik, maar u moet in plaats daarvan VAR.S gebruiken, zodat uw spreadsheets compatibel blijven met toekomstige versies van Excel.

De Excel VARA-functie

VARA retourneert ook de variantie van voorbeeldgegevens, maar er zijn enkele belangrijke verschillen met VAR en VAR.S. Het bevat namelijk Booleaanse en tekstwaarden in de berekening:

• TRUE-waarden worden geteld als 1
• FALSE-waarden worden geteld als 0
• Tekstreeksen worden geteld als 0

Zo gebruik je het:

1	=VARA(C4:C11)

We hebben nog vijf rijen aan de tabel toegevoegd: J, Q, K, TRUE en FALSE. Kolom D laat zien hoe VARA deze waarden interpreteert.

Omdat we nu een nieuwe reeks lage waarden in onze tabel hebben, is de variantie toegenomen tot 10,268.

De Excel VARPA-functie

VARPA berekent de variantie voor populatiegegevens. Het is vergelijkbaar met VAR.P, behalve dat het ook Booleaanse waarden en tekstreeksen in de berekening opneemt:

• TRUE-waarden worden geteld als 1
• FALSE-waarden worden geteld als 0
• Tekstreeksen worden geteld als 0

Je gebruikt het als volgt:

1	=VARPA(C4:C12)

We hebben nog vijf rijen aan de tabel toegevoegd: J, Q, K, TRUE en FALSE. Kolom D laat zien hoe VARPA deze waarden interpreteert.

Door deze groep lagere waarden aan de data toe te voegen, is de variantie toegenomen tot 8,984.

VARIANCE-functie in Google Spreadsheets

De CORREL-functie werkt in Google Spreadsheets precies hetzelfde als in Excel: