Een Z-score is een statistische waarde die aangeeft hoeveel standaarddeviaties een bepaalde waarde toevallig afkomstig is van het gemiddelde van de gehele gegevensset. U kunt de AVERAGE- en STDEV.S- of STDEV.P-formules gebruiken om het gemiddelde en de standaarddeviatie van uw gegevens te berekenen en die resultaten vervolgens gebruiken om de Z-score van elke waarde te bepalen.
Wat is een Z-score en wat doen de functies AVERAGE, STDEV.S en STDEV.P?
Een Z-score is een eenvoudige manier om waarden uit twee verschillende datasets te vergelijken. Het wordt gedefinieerd als het aantal standaarddeviaties verwijderd van het gemiddelde van een datapunt. De algemene formule ziet er als volgt uit:
=(DataPoint-AVERAGE(DataSet))/STDEV(DataSet)
Hier is een voorbeeld om te verduidelijken. Stel dat u de testresultaten van twee algebra-studenten wilt vergelijken die door verschillende docenten worden gegeven. Je weet dat de eerste student 95% behaalde op het eindexamen in de ene klas, en de student in de andere klas 87%.
Op het eerste gezicht is het 95% -cijfer indrukwekkender, maar wat als de docent van de tweede klas een moeilijker examen aflegt? U kunt de Z-score van de score van elke leerling berekenen op basis van de gemiddelde scores in elke klas en de standaarddeviatie van de scores in elke klas. Door de Z-scores van de twee leerlingen te vergelijken, zou kunnen blijken dat de leerling met de 87% -score het beter deed in vergelijking met de rest van de klas dan de leerling met de 98% -score deed in vergelijking met de rest van de klas.
De eerste statistische waarde die u nodig hebt, is het ‘gemiddelde’ en de functie “GEMIDDELDE” van Excel berekent die waarde. Het telt eenvoudig alle waarden in een celbereik op en deelt die som door het aantal cellen dat numerieke waarden bevat (lege cellen worden genegeerd).
De andere statistische waarde die we nodig hebben, is de ‘standaarddeviatie’ en Excel heeft twee verschillende functies om de standaarddeviatie op enigszins verschillende manieren te berekenen.
Eerdere versies van Excel hadden alleen de functie “STDEV”, die de standaarddeviatie berekent en de gegevens behandelt als een ‘steekproef’ van een populatie. Excel 2010 heeft dat opgesplitst in twee functies die de standaarddeviatie berekenen:
- STDEV.S: Deze functie is identiek aan de vorige “STDEV” -functie. Het berekent de standaarddeviatie terwijl de gegevens worden behandeld als een ‘steekproef’ van een populatie. Een steekproef van een populatie kan zoiets zijn als de specifieke muggen die zijn verzameld voor een onderzoeksproject of auto’s die opzij zijn gezet en worden gebruikt voor crashveiligheidstests.
- STDEV.P: Deze functie berekent de standaarddeviatie terwijl de gegevens worden behandeld als de volledige populatie. Een hele populatie zou zoiets zijn als alle muggen op aarde of elke auto in een productierun van een specifiek model.
Welke u kiest, is gebaseerd op uw dataset. Het verschil is meestal klein, maar het resultaat van de “STDEV.P” -functie zal altijd kleiner zijn dan het resultaat van de “STDEV.S” -functie voor dezelfde dataset. Het is een meer conservatieve benadering om aan te nemen dat er meer variabiliteit in de gegevens zit.
Laten we naar een voorbeeld kijken
Voor ons voorbeeld hebben we twee kolommen (“Waarden” en “Z-Score”) en drie “helper” -cellen voor het opslaan van de resultaten van de “GEMIDDELDE”, “STDEV.S” en “STDEV.P” -functies. De kolom “Waarden” bevat tien willekeurige getallen gecentreerd rond 500, en in de kolom “Z-score” zullen we de Z-score berekenen met behulp van de resultaten die zijn opgeslagen in de “helper” -cellen.
Eerst zullen we het gemiddelde van de waarden berekenen met de functie “GEMIDDELD”. Selecteer de cel waarin u het resultaat van de “GEMIDDELDE” -functie wilt opslaan.
Typ de volgende formule en druk op invoeren -of- gebruik het menu “Formules”.
=AVERAGE(E2:E13)
Om toegang te krijgen tot de functie via het menu “Formules”, selecteert u de vervolgkeuzelijst “Meer functies”, selecteert u de optie “Statistisch” en klikt u vervolgens op “GEMIDDELD”.
Selecteer in het venster Functieargumenten alle cellen in de kolom “Waarden” als invoer voor het veld “Getal1”. U hoeft zich geen zorgen te maken over het veld “Nummer2”.
Druk nu op “OK”.
Vervolgens moeten we de standaarddeviatie van de waarden berekenen met behulp van de functie “STDEV.S” of “STDEV.P”. In dit voorbeeld laten we u zien hoe u beide waarden kunt berekenen, te beginnen met ‘STDEV.S’. Selecteer de cel waarin het resultaat wordt opgeslagen.
Om de standaarddeviatie te berekenen met de functie “STDEV.S”, typ je deze formule in en druk je op Enter (of open je deze via het menu “Formules”).
=STDEV.S(E3:E12)
Om toegang te krijgen tot de functie via het menu “Formules”, selecteert u de vervolgkeuzelijst “Meer functies”, selecteert u de optie “Statistisch”, scrolt u een beetje naar beneden en klikt u vervolgens op het commando “STDEV.S”.
Selecteer in het venster Functieargumenten alle cellen in de kolom “Waarden” als invoer voor het veld “Getal1”. U hoeft zich hier ook geen zorgen te maken over het veld “Nummer2”.
Druk nu op “OK”.
Vervolgens berekenen we de standaarddeviatie met behulp van de functie “STDEV.P”. Selecteer de cel waarin het resultaat wordt opgeslagen.
Om de standaarddeviatie te berekenen met de functie “STDEV.P”, typ je deze formule in en druk je op Enter (of open je deze via het menu “Formules”).
= STDEV.P (E3: E12)
Om toegang te krijgen tot de functie via het menu “Formules”, selecteert u de vervolgkeuzelijst “Meer functies”, selecteert u de optie “Statistisch”, scrolt u een beetje naar beneden en klikt u vervolgens op de formule “STDEV.P”.
Selecteer in het venster Functieargumenten alle cellen in de kolom “Waarden” als invoer voor het veld “Getal1”. Nogmaals, u hoeft zich geen zorgen te maken over het veld “Nummer2”.
Druk nu op “OK”.
Nu we het gemiddelde en de standaarddeviatie van onze gegevens hebben berekend, hebben we alles wat we nodig hebben om de Z-score te berekenen. We kunnen een eenvoudige formule gebruiken die verwijst naar de cellen die de resultaten bevatten van de functies “AVERAGE” en “STDEV.S” of “STDEV.P”.
Selecteer de eerste cel in de kolom “Z-Score”. We gebruiken het resultaat van de functie “STDEV.S” voor dit voorbeeld, maar u kunt ook het resultaat van “STDEV.P” gebruiken.
Typ de volgende formule en druk op Enter:
=(E3-$G$3)/$H$3
U kunt ook de volgende stappen gebruiken om de formule in te voeren in plaats van te typen:
- Klik op cel F3 en typ
=( - Selecteer cel E3. (U kunt op de linker pijltoets een keer of gebruik de muis)
- Typ het minteken
- - Selecteer cel G3 en druk op F4 om de “$” -tekens toe te voegen om een ’absolute’ verwijzing naar de cel te maken (het zal door “G3″> “$G$3 ″> “G$3 ″> “$G3 ″> “G3” als u blijft drukken F4)
- Type
)/ - Selecteer cel H3 (of I3 als u “STDEV.P” gebruikt) en druk op F4 om de twee “$” -tekens toe te voegen.
- druk op Enter
Voor de eerste waarde is de Z-Score berekend. Het is 0,15945 standaarddeviaties onder het gemiddelde. Om de resultaten te controleren, kunt u de standaarddeviatie vermenigvuldigen met dit resultaat (6,271629 * -0,15945) en controleren of het resultaat gelijk is aan het verschil tussen de waarde en het gemiddelde (499-500). Beide resultaten zijn gelijk, dus de waarde is logisch.
Laten we de Z-scores van de rest van de waarden berekenen. Markeer de hele kolom ‘Z-Score’, te beginnen met de cel met de formule.
Druk op Ctrl + D, waarmee de formule in de bovenste cel naar alle andere geselecteerde cellen wordt gekopieerd.
Nu is de formule ‘ingevuld’ in alle cellen, en elk zal altijd verwijzen naar de juiste “GEMIDDELDE” en “STDEV.S” of “STDEV.P” -cellen vanwege de “$” -tekens. Als u fouten krijgt, ga dan terug en zorg ervoor dat de “$” -tekens zijn opgenomen in de formule die u hebt ingevoerd.
Berekening van de Z-score zonder gebruik van ‘Helper’-cellen
Helpercellen slaan een resultaat op, zoals degene die de resultaten opslaan van de functies “AVERAGE”, “STDEV.S” en “STDEV.P”. Ze kunnen nuttig zijn, maar zijn niet altijd nodig. U kunt ze helemaal overslaan bij het berekenen van een Z-score door in plaats daarvan de volgende gegeneraliseerde formules te gebruiken.
Hier is er een die de functie “STDEV.S” gebruikt:
=(Value-AVERAGE(Values))/STDEV.S(Values)
En een die de functie “STEV.P” gebruikt:
=(Value-AVERAGE(Values))/STDEV.P(Values)
Zorg ervoor dat u bij het invoeren van de celbereiken voor de “Waarden” in de functies absolute verwijzingen toevoegt (“$” met F4), zodat u bij het ‘invullen’ niet het gemiddelde of de standaarddeviatie van een ander bereik berekent van cellen in elke formule.
Als u een grote gegevensset heeft, kan het efficiënter zijn om helpercellen te gebruiken omdat het niet elke keer het resultaat van de functies ‘AVERAGE’ en ‘STDEV.S’ of ‘STDEV.P’ berekent, waardoor processorbronnen en het versnellen van de tijd die nodig is om de resultaten te berekenen.
Ook kost “$ G $ 3” minder bytes om op te slaan en minder RAM om te laden dan “AVERAGE ($ E $ 3: $ E $ 12).”. Dit is belangrijk omdat de standaard 32-bits versie van Excel beperkt is tot 2 GB RAM (de 64-bits versie heeft geen enkele beperking voor hoeveel RAM kan worden gebruikt).
