Standaarddeviatie berekenen in Excel

Verschillende belangrijke statistische concepten en methoden, zoals variantie, coëfficiënt van variatie en betrouwbaarheidsintervallen, zijn afhankelijk van de standaarddeviatie als basismaat voor spreiding. Daardoor is het een van de belangrijkste tools in de statistiek die data­professionals moeten beheersen.

Als je tijdens het lezen merkt dat je je statistiekkennis wilt opfrissen, volg dan onze cursus Introduction to Statistics, die veel basisonderwerpen behandelt zonder dat je hoeft te coderen.

Standaarddeviatie berekenen in Excel

Om de standaarddeviatie in Excel te berekenen, zet je je data in een kolom en gebruik je de functie STDEV.P() voor een populatie of de functie STDEV.S() voor een steekproef.

Het belangrijkste verschil tussen deze functies is dat STDEV.P() deelt door het totale aantal datapunten, terwijl STDEV.S() deelt door het aantal datapunten min één (n-1) om rekening te houden met steekproefvariabiliteit.

Snel standaarddeviatie berekenen in Excel. Afbeelding door de auteur.

Staat je data bijvoorbeeld in de cellen B2 tot en met B31, typ dan =STDEV.P(B2:B31) om de populatiestandaarddeviatie te berekenen of =STDEV.S(B2:B31) voor de steekproefstandaarddeviatie. Excel berekent en toont automatisch de standaarddeviatie voor je dataset.

Populatie vs. steekproefstandaarddeviatie in Excel

Standaarddeviatie is een statistische maat die de mate van variatie of spreiding in een reeks numerieke waarden kwantificeert. Het geeft aan hoeveel de waarden in een dataset afwijken van het gemiddelde (mean) van de dataset. 

Het helpt de mate van variabiliteit binnen data te begrijpen: een lage standaarddeviatie geeft aan dat de waarden dicht bij het gemiddelde liggen en dus consistent zijn, terwijl een hoge standaarddeviatie wijst op grotere variatie, oftewel meer verspreide data.

STDEV.P() voor populatiestandaarddeviatie

De formule voor standaarddeviatie hangt ervan af of we te maken hebben met een populatie of een steekproef. De formule voor de populatiestandaarddeviatie (σ) is als volgt:

Populatiestandaarddeviatie. Afbeelding door de auteur.

Waarbij:

• N het totale aantal datapunten in de populatie is.

• x_i​ elk individueel datapunt voorstelt.

• μ het gemiddelde (mean) van de populatie is.

Deze formule berekent het gemiddelde van de gekwadrateerde afwijkingen van het gemiddelde en neemt vervolgens de vierkantswortel van dit gemiddelde.

De functie STDEV.P() in Excel berekent de standaarddeviatie voor een volledige populatie. Deze functie gaat ervan uit dat de meegegeven argumenten de volledige populatie omvatten. De syntaxis van de functie STDEV.P() is als volgt:

STDEV.P(number1, [number2], ...)

De parameters zijn:

• number1: Het eerste getalargument correspondeert met een populatie.

• [number2], ...: Optionele extra getallen of verwijzingen die corresponderen met een populatie, tot maximaal 254 argumenten.

STDEV.P() berekent de standaarddeviatie met de formule voor populatiestandaarddeviatie die we eerder hebben gezien. Dit zorgt ervoor dat de berekening de kenmerken van de volledige populatie weerspiegelt.

We gebruiken STDEV.P() wanneer we data hebben die de volledige populatie vertegenwoordigt. Als we bijvoorbeeld de toetscijfers van alle leerlingen op een specifieke school analyseren, is STDEV.P() geschikt, omdat de dataset de volledige relevante populatie omvat.

STDEV.S() voor steekproefstandaarddeviatie

De formule voor de steekproefstandaarddeviatie (s) is als volgt. Let op dat we voor de steekproefstandaarddeviatie conventioneel een kleine n gebruiken.

Vergelijking voor steekproefstandaarddeviatie. Afbeelding door de auteur.

Waarbij:

• n het totale aantal datapunten in de steekproef is.

• x_i​ elk individueel datapunt voorstelt.

• xˉ het gemiddelde (mean) van de steekproef is.

De formule lijkt op die van de populatiestandaarddeviatie, maar gebruikt n - 1 (de correctie van Bessel) in de noemer om ervoor te zorgen dat een steekproef wordt gebruikt om de populatieparameter te schatten. Deze correctie maakt de steekproefstandaarddeviatie een niet-vertekenende schatter van de populatiestandaarddeviatie.​​

De functie STDEV.S() in Excel berekent de standaarddeviatie voor een steekproef uit de populatie. Deze functie gaat ervan uit dat de meegegeven argumenten een steekproef van de populatie zijn en niet de volledige populatie. De syntaxis van de functie STDEV.S() is als volgt:

STDEV.S(number1, [number2], ...)

De parameters zijn:

• number1: Het eerste getalargument correspondeert met een steekproef uit de populatie.

• [number2], ...: Optionele extra getallen of verwijzingen die corresponderen met een steekproef, tot maximaal 254 argumenten.

STDEV.S() berekent de standaarddeviatie met de formule voor de steekproefstandaarddeviatie, waarbij rekening wordt gehouden met de correctie van Bessel, zoals we eerder zagen.

In de praktijk gebruiken we STDEV.S() wanneer we een steekproef aan data hebben om de standaarddeviatie van de volledige populatie te schatten. Stel dat we de toetscijfers analyseren van een subset van willekeurig geselecteerde leerlingen uit een school. STDEV.S() is dan passend, omdat onze dataset slechts een steekproef is van de totale leerlingenpopulatie.

Nu we de functies in Excel voor het berekenen van de standaarddeviatie kennen, gaan we onze kennis toepassen met een voorbeeld.

Een voorbeeld uitwerken van standaarddeviatie in Excel 

Standaarddeviatie berekenen in Excel 

Stel je een situatie voor waarin je als data-analist de verkoopprestaties van een kleine winkel moet analyseren. De eigenaar wil de variabiliteit in de dagelijkse omzet van de afgelopen maand begrijpen om de voorraad en bezetting beter te kunnen plannen. 

Je hebt de dagelijkse omzetgegevens over 30 dagen verzameld en moet de standaarddeviatie berekenen om deze variabiliteit te kwantificeren. 

Begin met het in een kolom zetten van de gegevens, bijvoorbeeld B2 tot en met B31, in een Excel-werkblad.

Dataset met winkelverkopen. Afbeelding door de auteur.

We kunnen de populatiestandaarddeviatie in cel E6 berekenen door de volgende vergelijking te typen:

=STDEV.P(B2:B31)

Je ziet de berekende populatiestandaarddeviatie in cel E6 als volgt:

Populatiestandaarddeviatie berekenen. Afbeelding door de auteur.

We gebruikten de functie STDEV.P() omdat in ons scenario onze volledige populatie voor de analyse de omzetdata van de afgelopen maand is.

Stel dat we een analyse voor een heel jaar uitvoeren en de data van de laatste 30 dagen als steekproef gebruiken; dan gebruiken we de functie STDEV.S() zoals hieronder in de vergelijking getoond:

=STDEV.S(B2:B31)

En dan zien we de berekende steekproefstandaarddeviatie hieronder:

Steekproefstandaarddeviatie berekenen. Afbeelding door de auteur.

Na het berekenen van de standaarddeviatie is de volgende stap het resultaat interpreteren in een zakelijke context. 

Standaarddeviatiewaarden interpreteren

Voor een betere interpretatie van de standaarddeviatiewaarden en als aanvulling op onze analyse kunnen we de gemiddelde waarde van onze omzetdata berekenen met de volgende vergelijking:

=AVERAGE(B2:B31)

De gemiddelde waarde kan als $975 worden berekend in cel E7, zoals hieronder te zien is:

Het gemiddelde van de dataset berekenen. Afbeelding door de auteur.

Samen met de verkregen gemiddelde waarde kunnen we de standaarddeviatiewaarden als volgt interpreteren:

• Populatiestandaarddeviatie: De populatiestandaarddeviatie van ongeveer 432,77 geeft aan dat de dagelijkse omzet gemiddeld ongeveer $432,77 afwijkt van de gemiddelde omzet van $975. Deze waarde vertegenwoordigt de spreiding van de omzetdata, aangezien we de hele maand als complete dataset beschouwen.
• Steekproefstandaarddeviatie: De steekproefstandaarddeviatie van ongeveer 440,17 is iets hoger dan de populatiestandaarddeviatie. Deze waarde is relevant als we de 30 dagen aan data beschouwen als een steekproef uit een grotere populatie (bijv. alle mogelijke toekomstige maanden).
• Hogere steekproefstandaarddeviatie: De iets hogere waarde bij de steekproefstandaarddeviatie houdt rekening met de onzekerheid bij het gebruik van een steekproef om de algemene variabiliteit in de omzet te schatten. Dit suggereert dat als we een andere maand zouden bemonsteren, we een vergelijkbare variabiliteit in de dagelijkse omzet kunnen verwachten.
• Hoge variabiliteit in omzet: Beide standaarddeviatiewaarden wijzen op een hoge variabiliteit in de dagelijkse omzet, wat betekent dat de cijfers sterk schommelen rond het gemiddelde van $975. Dit niveau van variabiliteit kan gevolgen hebben voor voorraadbeheer en personeelsplanning. Op sommige dagen kan de omzet veel hoger of lager zijn dan gemiddeld, wat tot over- of onderbevoorrading kan leiden als dit niet zorgvuldig wordt beheerd.

Aangezien de standaarddeviatiewaarden aangeven dat de dagelijkse omzet inconsistent is en sterk varieert, kan verdere analyse of een machinelearningmodel worden gebouwd om de omzet op een bepaalde dag te schatten.

De winkeleigenaar moet met deze variabiliteit rekening houden bij het plannen van de voorraad en de bezetting om problemen door overbevoorrading of onderbezetting te voorkomen.

Standaarddeviatiebalken toevoegen in Excel

Standaarddeviatiebalken toevoegen aan onze grafieken in Excel is een handige manier om de variabiliteit van onze data visueel weer te geven. 

Selecteer het databereik B2 tot en met B31 uit ons voorbeeld en ga naar het tabblad Invoegen. Kies het gewenste grafiektype zoals in de afbeelding hieronder (bijv. lijngrafiek) en voeg de grafiek in.

Een grafiek invoegen voor de winkelomzet. Afbeelding door de auteur.

Je ziet hieronder een eenvoudige lijngrafiek:

Lijngrafiek voor winkelomzetdata. Afbeelding door de auteur.

Klik op de gegevensreeks in je grafiek om deze te markeren. Deze stap is nodig om foutbalken toe te voegen aan de geselecteerde gegevensreeks. Ga naar het tabblad Grafiek ontwerpen dat verschijnt wanneer je de grafiek selecteert. Klik op Grafiekelement toevoegen (te vinden in de groep Grafiekindelingen).

Beweeg vervolgens over Fout balken en selecteer Standaard afwijking om de standaarddeviatiebalken toe te voegen aan de lijngrafiek.

Standaarddeviatiebalken toevoegen aan de lijngrafiek. Afbeelding door de auteur.

Je kunt ook over Fout balken bewegen en vervolgens klikken op Meer opties voor foutbalken om de foutbalken verder op te maken en de kleur, breedte en lijndikte aan te passen aan het ontwerp van je grafiek. Dit kan in hetzelfde paneel onder de opties Foutbalken opmaken. Speel gerust met de grafiek tot je het gewenste plot hebt om te visualiseren.

Standaarddeviatiebalken opmaken. Afbeelding door de auteur.

Standaarddeviatiebalken toevoegen aan onze grafieken in Excel geeft een duidelijke visuele weergave van de variabiliteit in de data, wat helpt bij effectieve data-analyse en besluitvorming.

Conclusie

Standaarddeviatie is een fundamenteel en waardevol hulpmiddel dat ons helpt de variabiliteit van een dataset te begrijpen. Daarnaast wordt het gebruikt in verschillende andere analyses, zoals coëfficiënt van variatie, betrouwbaarheidsintervallen en ANOVA, waardoor het een onmisbare techniek is voor alle dataprofessionals.

We raden je aan onze uitgebreide, praktijkgerichte skill track Excel Fundamentals te bekijken om je Excel- en data-analysekills verder uit te breiden.