Standaarddeviatie - Excel en Google Spreadsheets

Deze zelfstudie laat zien hoe u de Excel Standaarddeviatie Functie in Excel om de standaarddeviatie voor een hele populatie te berekenen.

STANDAARD AFWIJKING Functieoverzicht

De functie STANDAARDDEVIATIE Berekent de standaarddeviatie voor een hele populatie.

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

(Let op hoe de formule-invoer verschijnt)

STANDARD DEVIATION functie Syntaxis en ingangen:

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

nummers- Waarden om standaardvariantie te krijgen

Hoe de standaarddeviatie in Excel te berekenen

Wanneer u met gegevens te maken hebt, wilt u enkele basistests uitvoeren om u te helpen deze te begrijpen. U begint meestal met het berekenen van het gemiddelde met behulp van de Excel GEMIDDELDE functie<>.

Dit geeft u een idee van waar het "midden" van de gegevens zich bevindt. En van daaruit wilt u kijken hoe verspreid de gegevens zich rond dit middelpunt bevinden. Hier komt de standaarddeviatie om de hoek kijken.

Excel geeft u een aantal functies om de standaarddeviatie te berekenen - STDEV, STDEV.P, STDEV.S en DSTDEV. We zullen ze allemaal bekijken, maar laten we eerst eens kijken wat de standaarddeviatie is is, precies.

Wat is de standaarddeviatie?

De standaarddeviatie geeft u een idee van hoe ver uw gegevenspunten van het gemiddelde verwijderd zijn. Neem de volgende dataset met testscores van de 100:

1	48,49,50,51,52

Het gemiddelde van deze dataset is 50 (tel alle getallen bij elkaar op en deel ze door n, waarbij n het aantal waarden in het bereik is).

Kijk nu naar deze volgende set gegevens:

1	10,25,50,75,90

Het gemiddelde van deze dataset is ook 50 - maar de twee reeksen vertellen een heel ander verhaal. Als je alleen het gemiddelde zou gebruiken, zou je kunnen denken dat de twee groepen ongeveer gelijk waren in hun aanleg - en gemiddeld zijn ze dat ook.

Maar in de eerste groep hebben we 5 mensen die zeer vergelijkbare, zeer matige scores behaalden. En in de tweede groep, een paar hoogvliegers die werden gecompenseerd door een paar slechte scoorders, met één persoon in het midden. De spreiding van de scores is heel verschillend, waardoor uw interpretatie van de gegevens ook heel anders is.

De standaarddeviatie is een maat voor deze spreiding.

Hoe de standaarddeviatie wordt berekend

Om te begrijpen wat de standaarddeviatie is en hoe deze werkt, kan het helpen om een ​​voorbeeld met de hand door te nemen. Op die manier weet u wat er "onder de motorkap" gebeurt zodra we bij de Excel-functies komen die u kunt gebruiken.

Om de standaarddeviatie te berekenen, doorloopt u dit proces:

1) Bereken het gemiddelde

Laten we onze eerste dataset hierboven nemen: 48,49,50,51,52

We kennen het gemiddelde (50) al, wat ik hier heb bevestigd met de Excel GEMIDDELDE functie<>:

1	=GEMIDDELDE(C4:C8)

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

Ik heb dit gedaan met de volgende formule:

1

=C4-$H$4

Ons gemiddelde is in H4 en ik heb de celverwijzing "vergrendeld" door de dollartekens voor de kolom en rij te plaatsen (door op F4) te drukken. Dit betekent dat ik de formule in de kolom kan kopiëren zonder dat de celverwijzing wordt bijgewerkt.

Het resultaat:

Laten we hier even pauzeren. Als u naar de nieuwe kolom kijkt, ziet u dat de getallen hier optellen tot nul. Het gemiddelde van deze getallen is ook nul.

Natuurlijk kan de spreiding van onze gegevens niet nul zijn - we weten dat er enige variatie is. We hebben een manier nodig om deze variatie weer te geven, zonder dat het gemiddelde nul blijkt te zijn.

3) Vier de verschillen

Dit kunnen we bereiken door de verschillen te kwadrateren. Laten we dus een nieuwe kolom toevoegen en de getallen in de D-kolom kwadrateren:

1

=D4*D4

Dit ziet er beter uit. Nu hebben we wat variatie, en de hoeveelheid variatie is gerelateerd aan hoe ver elke score van het gemiddelde verwijderd is.

4) Bereken de variantie - het gemiddelde van de gekwadrateerde verschillen

De volgende stap is om het gemiddelde van die gekwadrateerde verschillen te krijgen. Er zijn eigenlijk twee manieren om dit te doen bij het berekenen van de standaarddeviatie.

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

Bevolkingsgegevens betekent dat je de "volledige set" van je gegevens hebt, bijvoorbeeld, je hebt gegevens over elke leerling in een bepaalde klas.

Voorbeeldgegevens betekent dat u niet al uw gegevens hebt, alleen een steekproef uit een grotere populatie. Doorgaans is uw doel met voorbeeldgegevens om een ​​schatting te maken van wat de waarde is in de grotere populatie.

Een politieke opiniepeiling is een goed voorbeeld van voorbeeldgegevens - onderzoekers onderzoeken bijvoorbeeld 1.000 mensen om een ​​idee te krijgen van wat een heel land of staat denkt.

Hier hebben we geen voorbeeld. We hebben slechts vijf statistisch ingestelde familieleden die de standaarddeviatie willen berekenen van een test die ze allemaal hebben gedaan. We hebben alle datapunten en we maken geen schatting van een grotere groep mensen. Dit zijn bevolkingsgegevens - dus we kunnen hier het gemiddelde nemen:

1	=GEMIDDELDE(E4:E8)

Oké, dus we hebben er 2. Deze score staat bekend als de 'variantie' en is het basispunt voor veel statistische tests, inclusief de standaarddeviatie. U kunt meer lezen over de variantie op de hoofdpagina: variantie berekenen in Excel<>.

5) Verkrijg de vierkantswortel van de variantie

We hebben onze getallen eerder gekwadrateerd, wat de waarden duidelijk een beetje opdrijft. Dus om het cijfer weer in lijn te brengen met de werkelijke verschillen van de scores met het gemiddelde, moeten we het resultaat van stap 4 worteltrekken:

1	=WORTEL(H4)

En we hebben ons resultaat: de standaarddeviatie is 1,414

Omdat we onze eerder gekwadrateerde getallen vierkantswortel hebben gemaakt, wordt de standaarddeviatie gegeven in dezelfde eenheden als de oorspronkelijke gegevens. Dus onze standaarddeviatie is hier 1.414 testpunten.

Standaarddeviatie wanneer de gegevens meer verspreid zijn

Eerder hadden we een tweede voorbeeldgegevensbereik: 10,25,50,75,90

Laten we voor de lol eens kijken wat er gebeurt als we de standaarddeviatie op deze gegevens berekenen:

Alle formules zijn precies hetzelfde als voorheen (merk op dat het totale gemiddelde nog steeds 50 is).

Het enige dat veranderde was de spreiding van de scores in kolom C. Maar nu is onze standaarddeviatie veel hoger, namelijk 29.832 testpunten.

Omdat we maar 5 datapunten hebben, is het natuurlijk heel gemakkelijk om te zien dat de spreiding van de scores tussen de twee sets verschilt. Maar als je honderden of duizenden datapunten hebt, kun je dat niet zien door de gegevens snel te scannen. En dat is precies waarom we de standaarddeviatie gebruiken.

De Excel-functies om de standaarddeviatie te berekenen

Nu je weet hoe de standaarddeviatie werkt, hoef je niet dat hele proces te doorlopen om tot de standaarddeviatie te komen. U kunt gewoon een van de ingebouwde functies van Excel gebruiken.

Excel heeft hiervoor verschillende functies:

• P berekent de standaarddeviatie voor populatiegegevens (met behulp van de exacte methode die we in het bovenstaande voorbeeld hebben gebruikt)
• S berekent de standaarddeviatie voor voorbeeldgegevens (met behulp van de n-1-methode die we eerder hebben besproken)
• STDEV is precies hetzelfde als STDEV.S. Dit is een oudere functie die is vervangen door STDEV.S en STDEV.P.
• STDEVA lijkt erg op STDEV.S, behalve dat het tekstcellen en Booleaanse (TRUE/FALSE) cellen bevat bij het maken van de berekening.
• STDEVPA lijkt erg op STDEV.P, behalve dat het tekstcellen en Booleaanse (TRUE/FALSE) cellen bevat bij het maken van de berekening.

Wauw, veel opties hier! Laat u niet intimideren - in de overgrote meerderheid van de gevallen gebruikt u STDEV.P of STDEV.S.

Laten we elk van deze achtereenvolgens doornemen, te beginnen met STDEV.P, want dat is de methode die we zojuist hebben doorlopen.

De Excel STDEV.P-functie

STDEV.P berekent de standaarddeviatie voor populatiegegevens. Je gebruikt het als volgt:

1	=STDEV.P(C4:C8)

In STDEV.P definieert u één argument: het gegevensbereik waarvoor u de standaarddeviatie wilt berekenen.

Dit is hetzelfde voorbeeld dat we hierboven stap voor stap hebben doorlopen toen we de standaarddeviatie met de hand berekenden. En zoals je hierboven kunt zien, krijgen we precies hetzelfde resultaat - 1.414.

Opmerking STDEV.P negeert alle cellen die tekst of Booleaanse (TRUE/FALSE) waarden bevatten. Als u deze moet opnemen, gebruikt u STDEVPA.

De Excel STDEV.S-functie

STDEV.S berekent de standaarddeviatie voor steekproefgegevens. Gebruik het als volgt:

1	=STDEV.S(C4:C8)

Nogmaals, er is één argument nodig: het gegevensbereik waarvoor u de standaarddeviatie wilt weten.

Voordat we ingaan op een voorbeeld, laten we het verschil tussen STDEV.S en STDEV.P bespreken.

Zoals we al hebben besproken, moet STDEV.S worden gebruikt voor voorbeeldgegevens - wanneer uw gegevens deel uitmaken van een grotere set. Dus laten we nu aannemen dat in ons voorbeeld hierboven meer mensen de test hadden gedaan. We willen de standaarddeviatie schatten van iedereen die de test heeft gedaan, met alleen deze vijf scores. Nu gebruiken we voorbeeldgegevens.

Nu verschilt de berekening van stap (4) hierboven, wanneer we de variantie berekenen - het gemiddelde van het gekwadrateerde verschil van elke score van het algemene gemiddelde.

In plaats van de normale methode te gebruiken - alle waarden optellen en delen door n, zouden we alle waarden optellen en delen door n-1:

1	=SOM(E4:E8) / (AANTAL(E4:E8)-1)

In deze formule:

• SOM krijgt de som van de gekwadrateerde verschillen
• COUNT retourneert onze n, waarvan we 1 aftrekken
• We delen dan eenvoudig onze som door onze n-1

Deze keer is het gemiddelde van de gekwadrateerde verschillen 2,5 (je herinnert je misschien dat het eerder 2 was, dus het is iets hoger).

Dus waarom delen we door n-1 in plaats van n als we te maken hebben met voorbeeldgegevens?

Het antwoord is vrij complex, en als u alleen maar probeert uw cijfers te gebruiken om uw gegevens te begrijpen, hoeft u zich daar niet echt zorgen over te maken. Zorg ervoor dat u STDEV.S gebruikt voor voorbeeldgegevens en STDEV.P voor populatiegegevens, en het komt goed.

Als je echt benieuwd bent waarom, bekijk dan de hoofdpagina over het berekenen van variantie in Excel<>.

OK, dus we hebben nu de variantie voor de steekproef, dus om de standaarddeviatie voor de steekproef te krijgen, krijgen we gewoon de vierkantswortel van de variantie:

1	=WORTEL(H4)

We krijgen 1.581.

STDEV.S voert alle bovenstaande berekeningen voor ons uit en retourneert de standaarddeviatie van het monster in slechts één cel. Dus eens kijken wat het oplevert…

1	=STDEV.S(C4:C8)

Ja, 1.581 alweer.

De Excel STDEV-functie

De STDEV-functie van Excel werkt op precies dezelfde manier als STDEV.S - dat wil zeggen, het berekent de standaarddeviatie voor een steekproef van gegevens.

Je gebruikt het op dezelfde manier:

1	=STDEV(C4:C8)

We krijgen weer hetzelfde resultaat.

Belangrijke notitie: STDEV is een "compatibiliteitsfunctie", wat in feite betekent dat Microsoft er vanaf komt. Het werkt nog steeds voor nu, dus alle oudere spreadsheets blijven normaal werken. Maar in toekomstige versies van Excel kan Microsoft het volledig laten vallen, dus u moet waar mogelijk STDEV.S gebruiken in plaats van STDEV.

De Excel STDEVA-functie

STDEVA wordt ook gebruikt om de standaarddeviatie voor een steekproef te berekenen, maar er zijn een aantal belangrijke verschillen die u moet weten:

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

Gebruik het als volgt:

1	=STDEVA(C4:C8)

Nog vier vrienden en familieleden hebben hun testscores gegeven. Deze worden weergegeven in kolom C en kolom D geeft aan hoe STDEVA deze gegevens interpreteert.

Omdat deze cellen als zulke lage waarden worden geïnterpreteerd, ontstaat er een veel grotere spreiding over onze gegevens dan we eerder zagen, waardoor de standaarddeviatie, nu op 26,246, aanzienlijk is toegenomen.

De Excel STDEVPA-functie

STDEVPA berekent de standaarddeviatie voor een populatie op dezelfde manier als STDEV.P. Het bevat echter ook Booleaanse waarden en tekenreeksen in de berekening, die als volgt worden geïnterpreteerd:

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

Je gebruikt het als volgt:

1	=STDEVPA(C4:C12)

Gegevens filteren voordat de standaarddeviatie wordt berekend

In de echte wereld heb je niet altijd de exacte gegevens die je nodig hebt in een mooie opgeruimde tabel. Vaak heb je een grote spreadsheet vol gegevens, die je moet filteren voordat je de standaarddeviatie berekent.

U kunt dit heel eenvoudig doen met de databasefuncties van Excel: DSTDEV (voor steekproeven) en DSTDEVP (voor populaties).

Met deze functies kunt u een criteriatabel maken waarin u alle benodigde filters kunt definiëren. De functies passen deze filters achter de schermen toe voordat de standaarddeviatie wordt geretourneerd. Op deze manier hoeft u geen autofilter aan te raken of gegevens naar een apart blad te trekken - DSTDEV en SDDTDEVP kunnen dat allemaal voor u doen.

Lees meer op de hoofdpagina voor de Excel DSTDEV- en DSTDEVP-functies<>.

STANDAARD DEVIATION Functie in Google Spreadsheets

De STANDAARD DEVIATION Functie werkt precies hetzelfde in Google Spreadsheets als in Excel: