Een Z-Score is een statistische waarde die je vertelt hoeveel standaardafwijkingen een bepaalde waarde gebeurt te zijn van het gemiddelde van de gehele data-set. U kunt gebruik maken van GEMIDDELDE en STDEV.S of STDEV.P formules voor het berekenen van het gemiddelde en de standaardafwijking van uw gegevens en gebruik vervolgens de resultaten weer om te bepalen van de Z-Score van elke waarde.
Wat is een Z-Score en wat de GEMIDDELDE, STDEV.S, en STDEV.P functies doen?
Een Z-Score is een eenvoudige manier van het vergelijken van de waarden van twee verschillende datasets. Het is gedefinieerd als het aantal standaarddeviaties verwijderd van het gemiddelde van een data-punt ligt. De algemene formule kijkt als deze:
=(Gegevenspunt-GEMIDDELDE(DataSet))/STDEV(DataSet)
Hier is een voorbeeld om te verduidelijken. Zeggen dat je wilde vergelijken met de testresultaten van twee Algebra studenten les van verschillende leerkrachten. U weet dat de eerste leerling een 95% op het examen in een klasse, en de student in de andere klasse scoorde 87%.
Op het eerste gezicht, de 95% kwaliteit is meer indrukwekkend, maar wat als de onderwijzer van de tweede klas gaf een moeilijker examen? Je kan het berekenen van de Z-Score van elke student de score is gebaseerd op de gemiddelde scores in elke klasse en de standaardafwijking van de scores van elke klasse. Het vergelijken van de Z-Scores van de twee studenten kunnen onthullen dat de student met de 87% score deed het beter in vergelijking tot de rest van hun klasse dan de student met de 98% score heeft in vergelijking tot de rest van hun klasse.
De eerste statistische waarde je nodig hebt is de ‘mean’ en Excel is een “GEMIDDELD” functie berekent de waarde. Het voegt gewoon up te maken van alle waarden in een celbereik op en deelt de som door het aantal cellen met numerieke waarden (het negeert lege cellen).
De andere statistische waarde die we nodig hebben is de ‘standaard deviatie’ en Excel heeft twee verschillende functies voor het berekenen van de standaardafwijking op een iets andere manier.
Eerdere versies van Excel alleen had de “STDEV” – functie, die berekent de standaarddeviatie tijdens de behandeling van de gegevens als een ‘monster’ van een bevolking. Excel 2010 brak in twee functies die het berekenen van de standaarddeviatie:
- STDEV.S: Deze functie is identiek aan de vorige “STDEV” functie. Het berekent de standaarddeviatie tijdens de behandeling van de gegevens als een ‘monster’ van een bevolking. Een voorbeeld van een bevolking kan iets zijn in de zin van het bepaalde muggen verzameld voor een onderzoek, project of auto ‘ s die waren gereserveerd en gebruikt voor de crash-testen van de veiligheid.
- STDEV.P: Deze functie berekent de standaarddeviatie tijdens de behandeling van de gegevens van de gehele populatie. Een hele bevolking zou wat zijn als alle muggen op Aarde of iedere auto in een productie van een specifiek model.
Welke u kiest is gebaseerd op uw gegevens. Het verschil zal meestal klein, maar het resultaat van de “STDEV.P” functie zal altijd kleiner zijn dan het resultaat van de “STDEV.S” – functie voor dezelfde data set. Het is een meer conservatieve aanpak te gaan ervan uit dat er meer variatie in de gegevens.
Laten we eens Kijken naar een Voorbeeld
Voor ons voorbeeld hebben we twee kolommen (“Waarden” en “Z-Score”)en drie “helper” cellen voor het opslaan van de resultaten van de “GEMIDDELD”, “STDEV.S,” en “STDEV.P” – functies. De “Waarden” kolom bevat tien willekeurige nummers gecentreerd rond de 500, en de ‘Z-Score’ kolom is waar wij berekenen de Z-Score met behulp van de resultaten die opgeslagen zijn in het ‘helper-cellen.
Eerst berekenen we het gemiddelde van de waarden met behulp van de ‘GEMIDDELDE’ functie. Selecteer de cel waar u het opslaan van het resultaat van de ‘GEMIDDELDE’ functie.
Typ de volgende formule en druk op de enter -of – gebruik de “Formules” menu.
=GEMIDDELDE(E2:E13)
Om de functie te openen via de “Formules” menu, selecteer dan “Meer Functies” drop-down, selecteer de “Statistische” optie, en klik vervolgens op de “GEMIDDELDE.”
In de Functie Argumenten venster, selecteert u alle cellen in de “Waarden” – kolom als de input voor de “Getal1” veld. U hoeft niet zorgen te maken over de “Getal2” veld.
Druk nu op “OK”.
We moeten voor het berekenen van de standaardafwijking van de waarden met behulp van de “STDEV.S” of “STDEV.P” – functie. In dit voorbeeld zullen we laten zien hoe je het berekenen van zowel de waarden, te beginnen met “STDEV.S.” Selecteer de cel waarin het resultaat zal worden opgeslagen.
Voor het berekenen van de standaarddeviatie met de “STDEV.S” functie, in deze formule en druk op de Enter (of de toegang via de “Formules” in het menu).
=STDEVA.S(E3:E12)
Om de functie te openen via de “Formules” menu, selecteer dan “Meer Functies” drop-down, selecteer de “Statistische” optie, blader naar beneden en klik vervolgens op de “STDEV.S” – opdracht.
In de Functie Argumenten venster, selecteert u alle cellen in de “Waarden” – kolom als de input voor de “Getal1” veld. U hoeft niet zorgen te maken over de “Getal2” veld hier.
Druk nu op “OK”.
Vervolgens berekenen we de standaarddeviatie met de “STDEV.P” – functie. Selecteer de cel waarin het resultaat zal worden opgeslagen.
Voor het berekenen van de standaarddeviatie met de “STDEV.P” functie, in deze formule en druk op de Enter (of de toegang via de “Formules” in het menu).
=STDEVA.P(E3:E12)
Om de functie te openen via de “Formules” menu, selecteer dan “Meer Functies” drop-down, selecteer de “Statistische” optie, blader naar beneden en klik vervolgens op de “STDEV.P” formule.
In de Functie Argumenten venster, selecteert u alle cellen in de “Waarden” – kolom als de input voor de “Getal1” veld. Nogmaals, je geen zorgen hoeft te maken over de “Getal2” veld.
Druk nu op “OK”.
Nu dat we hebben berekend wat de gemiddelde en de standaardafwijking van onze gegevens, hebben we alles wat we nodig hebben voor het berekenen van de Z-Score. We kunnen gebruik maken van een eenvoudige formule die verwijst naar de cellen met de resultaten van de “GEMIDDELDE” en “STDEV.S” of “STDEV.P” – functies.
Selecteer de eerste cel in de “Z-Score” – kolom. We zullen gebruik maken van het resultaat van de “STDEV.S” – functie voor dit voorbeeld, maar je kan ook het gevolg zijn van het “STDEV.P.”
Typ de volgende formule en druk op Enter:
=(E3-$G$3)/$H$3
U kunt ook gebruik maken van de volgende stappen voor het invoeren van de formule in plaats van te typen:
- Klik in cel F3 en type =(
- Selecteer cel E3. (U kunt ook op de pijl-links-toets of de muis gebruiken)
- Typ het minteken –
- Selecteer cel G3 en druk vervolgens op F4 om het toevoegen van de “$” tekens om een ‘absolute’ verwijzing naar de cel (het fietst door “G3” > “$G$3″ > “G$3″ > “$G3″ > “G3” als u doorgaat met het drukken op F4)
- Typ )/
- Selecteer cel H3 (of I3 als u “STDEV.P”) en druk op F4 om het toevoegen van de twee “$” tekens.
- Druk Op Enter
De Z-Score is berekend over de eerste waarde. Het is 0.15945 standaarddeviaties onder het gemiddelde. Om de resultaten te zien, kunt u vermenigvuldigen van de standaarddeviatie op basis van dit resultaat 6.271629 * -0.15945) en controleer dat het resultaat gelijk is aan het verschil tussen de waarde en het gemiddelde (499-500). Beide resultaten zijn gelijk, dus de waarde zinvol is.
Bereken de Z-Scores van de rest van de waarden. Markeer de hele ‘Z-Score’ kolom beginnend met de cel die de formule bevat.
Druk op Ctrl+D hiermee kopieert u de formule in de bovenste cel naar beneden door alle andere geselecteerde cellen.
Nu de formule is ‘gevuld-down’ voor alle cellen, en elk zal altijd naar de juiste “GEMIDDELDE” en “STDEV.S” of “STDEV.P” cellen als gevolg van de “$” tekens. Als u fouten krijgt, ga dan terug en zorg ervoor dat het “$” tekens zijn opgenomen in de formule die u hebt ingevoerd.
De berekening van de Z-Score, zonder het gebruik van ‘Helper’ Cellen
Helper cellen opslaan van een resultaat, zoals het opslaan van de resultaten van de “GEMIDDELD”, “STDEV.S,” en “STDEV.P” – functies. Ze kunnen nuttig zijn, maar zijn niet altijd nodig. U kunt ze overslaan helemaal bij het berekenen van een Z-Score met behulp van de volgende gegeneraliseerde formules, in plaats van.
Hier is met behulp van de “STDEV.S” functie:
=(Waarde-GEMIDDELDE(Waarden))/STDEV.S(Waarden)
En met behulp van de “STEV.P” functie:
=(Waarde-GEMIDDELDE(Waarden))/STDEV.P(Waarden)
Bij het betreden van de cel varieert voor de “Waarden” in de functies, moet u absolute verwijzingen (“$” met F4), zodat wanneer u ‘fill-down’ u niet in de berekening van de gemiddelde of de standaarddeviatie van de verschillende cellen in elke formule.
Als u een grote set gegevens, kan het efficiënter zijn om gebruik helper cellen, omdat het niet berekenen van het resultaat van de “GEMIDDELDE” en “STDEV.S” of “STDEV.P” functies elk moment opslaan processor middelen en het versnellen van de tijd die het duurt om de resultaten te berekenen.
Ook, “$G$3” duurt minder bytes op te slaan en minder RAM-geheugen te laden dan de “GEMIDDELDE($E$3:$E$12).”. Dit is belangrijk omdat de standaard 32-bits versie van Excel is beperkt tot 2 gb RAM (64-bits versie heeft geen beperkingen op hoeveel RAM-geheugen kan worden gebruikt).