Hoe een Z-Score te berekenen met behulp van Microsoft Excel

Een Z-score is een statistische waarde die u vertelt hoeveel standaardafwijkingen een bepaalde waarde is die afkomstig is van het gemiddelde van de volledige gegevensset. U kunt de formules AVERAGE en STDEV.S of STDEV.P gebruiken om het gemiddelde en de standaarddeviatie van uw gegevens te berekenen en die resultaten vervolgens te gebruiken om de Z-score van elke waarde te bepalen.

Wat is een Z-score en wat doen de functies GEMIDDELD, STDEV.S en STDEV.P?

Een Z-score is een eenvoudige manier om waarden uit twee verschillende gegevenssets te vergelijken. Het wordt gedefinieerd als het aantal standaardafwijkingen weg van het gemiddelde dat een gegevenspunt liegt. De algemene formule ziet er als volgt uit:

= (DataPoint-GEMIDDELDE (dataset)) / STDEV (dataset)

Hier is een voorbeeld om te helpen verduidelijken. Stel dat je de testresultaten van twee Algebra-studenten van verschillende docenten wilde vergelijken. Je weet dat de eerste student een 95% behaalde op het eindexamen in een klas, en de student in de andere klas scoorde 87%.

Op het eerste gezicht is de score van 95% indrukwekkender, maar wat als de docent van de tweede klas een moeilijker examen aflegde? U kunt de Z-score van de score van elke student berekenen op basis van de gemiddelde scores in elke klas en de standaarddeviatie van de scores in elke klas. Het vergelijken van de Z-scores van de twee studenten zou kunnen aantonen dat de student met de 87% -score het beter deed dan de rest van zijn klas dan de student met de 98% -score deed in vergelijking met de rest van zijn klas.

De eerste statistische waarde die u nodig hebt, is de 'gemiddelde' en Excel's 'GEMIDDELDE' functie berekent die waarde. Het telt eenvoudig alle waarden in een celbereik op en verdeelt die som door het aantal cellen dat numerieke waarden bevat (het negeert lege cellen).

De andere statistische waarde die we nodig hebben is de 'standaardafwijking' 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 terwijl de gegevens worden behandeld als een 'steekproef' van een populatie. Excel 2010 heeft dat in twee functies gebroken die de standaarddeviatie berekenen:

• STDEV.S: Deze functie is identiek aan de vorige "STDEV" -functie. Het berekent de standaardafwijking 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 zijn gereserveerd en worden gebruikt voor het testen van botsveiligheid.
• STDEV.P: Deze functie berekent de standaarddeviatie terwijl de gegevens worden behandeld als de gehele 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 zal meestal klein zijn, 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 is.

Laten we naar een voorbeeld kijken

Voor ons voorbeeld hebben we twee kolommen ("Waarden" en "Z-score") en drie "hulp" -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 de kolom "Z-score" is waar we de Z-score berekenen met behulp van de resultaten die zijn opgeslagen in de cellen van de helper..

Eerst zullen we het gemiddelde van de waarden berekenen met behulp van de "GEMIDDELDE" functie. Selecteer de cel waarin u het resultaat van de "GEMIDDELDE" -functie zult opslaan.

Typ de volgende formule in en druk op invoeren -of - gebruik het menu "Formules".

= GEMIDDELDE (E2: E13)

Als u de functie wilt openen via het menu 'Formules', selecteert u de vervolgkeuzelijst 'Meer functies', selecteert u de optie 'Statistiek' en klikt u vervolgens op 'GEMIDDELDE'.

Selecteer in het venster Functieargumenten alle cellen in de kolom "Waarden" als de invoer voor het veld "Nummer1". U hoeft zich geen zorgen te maken over het veld "Number2".

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 waar het resultaat zal worden opgeslagen.

Om de standaarddeviatie te berekenen met behulp van de functie "STDEV.S", typt u deze formule en drukt u op Enter (of opent u het via het menu "Formules").

= STDEV.S (E3: E12)

Om de functie te openen via het menu "Formules", selecteert u de vervolgkeuzelijst "Meer functies", selecteert u de optie "Statistiek", schuift u een beetje omlaag en klikt u vervolgens op de opdracht "STDEV.S"..

Selecteer in het venster Functieargumenten alle cellen in de kolom "Waarden" als de invoer voor het veld "Nummer1". U hoeft zich hier ook geen zorgen te maken over het veld "Number2".

Druk nu op "OK".

Vervolgens berekenen we de standaarddeviatie met behulp van de functie "STDEV.P". Selecteer de cel waar het resultaat zal worden opgeslagen.

Om de standaarddeviatie te berekenen met behulp van de functie "STDEV.P", typt u deze formule en drukt u op Enter (of opent u het via het menu "Formules").

= STDEV.P (E3: E12)

Om de functie te openen via het menu "Formules", selecteert u de vervolgkeuzelijst "Meer functies", selecteert u de optie "Statistiek", schuift 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 de invoer voor het veld "Nummer1". Nogmaals, u hoeft zich geen zorgen te maken over het veld "Number2".

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 van de functies "GEMIDDELD" en "STDEV.S" of "STDEV.P" bevatten.

Selecteer de eerste cel in de kolom "Z-score". We zullen het resultaat van de "STDEV.S" -functie gebruiken voor dit voorbeeld, maar je zou ook het resultaat van "STDEV.P."

Typ de volgende formule in en druk op Enter:

= (N3- $ G $ 3) / $ H $ 3

U kunt ook de volgende stappen uitvoeren om de formule in te voeren in plaats van te typen:

1. Klik op cel F3 en typ = (
2. Selecteer cel E3. (U kunt op de drukken pijl naar links-toets een keer of gebruik de muis)
3. Typ het minteken -
4. Selecteer cel G3 en druk vervolgens op F4 om de "$" tekens toe te voegen om een ​​'absolute' verwijzing naar de cel te maken (hij zal door "G3"> "$G$3 ">" G$3 ">"$G3 ">" G3 "als u doorgaat met drukken F4)
5. Type ) /
6. Selecteer cel H3 (of I3 als u "STDEV.P" gebruikt) en druk op F4 om de twee "$" tekens toe te voegen.
7. druk op Enter

De Z-score is berekend voor de eerste waarde. Het zijn 0.15945 standaardafwijkingen onder het gemiddelde. Om de resultaten te controleren, kunt u de standaardafwijking 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' die begint met de cel met de formule.

Druk op Ctrl + D, waarmee de formule in de bovenste cel naar beneden wordt gekopieerd door alle andere geselecteerde cellen.

Nu is de formule 'opgevuld' voor alle cellen, en elk zal altijd verwijzen naar de juiste "GEMIDDELDE" en "STDEV.S" of "STDEV.P" cellen vanwege de "$" karakters. Als u fouten krijgt, gaat u terug en zorgt u ervoor dat de "$" -tekens zijn opgenomen in de formule die u hebt ingevoerd.

De Z-score berekenen zonder de 'Helper'-cellen te gebruiken

Helpercellen slaan een resultaat op, zoals degenen die de resultaten van de "GEMIDDELDE", "STDEV.S" en "STDEV.P" -functies opslaan. Ze kunnen nuttig zijn maar zijn niet altijd noodzakelijk. 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:

= (Waarde-GEMIDDELDE (Values)) / STDEV.S (Values)

En een die de functie "STEV.P" gebruikt:

= (Waarde-GEMIDDELDE (Values)) / STDEV.P (Values)

Wanneer u de celbereiken voor de "Waarden" in de functies invoert, moet u absolute verwijzingen toevoegen ("$" met behulp van F4), zodat u bij het "invullen" niet het gemiddelde of de standaardafwijking van een ander bereik berekent van cellen in elke formule.

Als u een grote gegevensset hebt, kan het efficiënter zijn om helpercellen te gebruiken omdat niet elke keer het resultaat van de functies "GEMIDDELDE" en "STDEV.S" of "STDEV.P" wordt berekend, waardoor processorbronnen worden bespaard en versnellen van de tijd die het kost om de resultaten te berekenen.

Bovendien kost "$ G $ 3" ​​minder bytes om op te slaan en minder RAM om te laden dan "GEMIDDELDE ($ 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 beperkingen voor hoeveel RAM kan worden gebruikt).