Πώς να υπολογίσετε μια βαθμολογία Z χρησιμοποιώντας το Microsoft Excel

Το Z-Score είναι μια στατιστική τιμή που σας λέει πόσες τυπικές αποκλίσεις συμβαίνει μια συγκεκριμένη τιμή από τον μέσο όρο ολόκληρου του συνόλου δεδομένων. Μπορείτε να χρησιμοποιήσετε τους τύπους AVERAGE και STDEV.S ή STDEV.P για να υπολογίσετε τη μέση και την τυπική απόκλιση των δεδομένων σας και, στη συνέχεια, να χρησιμοποιήσετε αυτά τα αποτελέσματα για να προσδιορίσετε τη βαθμολογία Z κάθε τιμής.

Τι είναι το Z-Score και τι κάνουν οι λειτουργίες AVERAGE, STDEV.S και STDEV.P;

Το Z-Score είναι ένας απλός τρόπος σύγκρισης τιμών από δύο διαφορετικά σύνολα δεδομένων. Ορίζεται ως ο αριθμός των τυπικών αποκλίσεων μακριά από τη μέση τιμή ενός σημείου δεδομένων. Ο γενικός τύπος μοιάζει με αυτό:

= (DataPoint-AVERAGE (DataSet)) / STDEV (DataSet)

Ακολουθεί ένα παράδειγμα για να διευκρινιστεί. Ας πούμε ότι θέλετε να συγκρίνετε τα αποτελέσματα των δοκιμών δύο μαθητών άλγεβρας που διδάσκονται από διαφορετικούς εκπαιδευτικούς. Γνωρίζετε ότι ο πρώτος μαθητής έλαβε 95% στην τελική εξέταση σε μια τάξη και ο μαθητής στην άλλη τάξη σημείωσε 87%.

Με την πρώτη ματιά, ο βαθμός 95% είναι πιο εντυπωσιακός, αλλά τι γίνεται αν ο δάσκαλος της δεύτερης τάξης έδωσε μια πιο δύσκολη εξέταση; Θα μπορούσατε να υπολογίσετε τη βαθμολογία Z της βαθμολογίας κάθε μαθητή με βάση το μέσο όρο βαθμολογίας σε κάθε τάξη και την τυπική απόκλιση των βαθμολογιών σε κάθε τάξη. Η σύγκριση των βαθμολογιών Z των δύο μαθητών θα μπορούσε να αποκαλύψει ότι ο μαθητής με τη βαθμολογία 87% έκανε καλύτερα σε σύγκριση με την υπόλοιπη τάξη από ότι ο μαθητής με τη βαθμολογία 98% σε σύγκριση με την υπόλοιπη τάξη.

Η πρώτη στατιστική τιμή που χρειάζεστε είναι η συνάρτηση «μέσος όρος» και «AVERAGE» του Excel υπολογίζει αυτήν την τιμή. Απλώς προσθέτει όλες τις τιμές σε μια περιοχή κελιών και διαιρεί αυτό το άθροισμα με τον αριθμό των κελιών που περιέχουν αριθμητικές τιμές (αγνοεί τα κενά κελιά).

Η άλλη στατιστική τιμή που χρειαζόμαστε είναι η «τυπική απόκλιση» και το Excel έχει δύο διαφορετικές λειτουργίες για τον υπολογισμό της τυπικής απόκλισης με ελαφρώς διαφορετικούς τρόπους.

Οι προηγούμενες εκδόσεις του Excel είχαν μόνο τη συνάρτηση "STDEV", η οποία υπολογίζει την τυπική απόκλιση ενώ αντιμετωπίζει τα δεδομένα ως "δείγμα" ενός πληθυσμού. Το Excel 2010 το έσπασε σε δύο συναρτήσεις που υπολογίζουν την τυπική απόκλιση:

  • STDEV.S: Αυτή η συνάρτηση είναι ίδια με την προηγούμενη συνάρτηση "STDEV". Υπολογίζει την τυπική απόκλιση ενώ αντιμετωπίζει τα δεδομένα ως «δείγμα» ενός πληθυσμού. Ένα δείγμα ενός πληθυσμού μπορεί να είναι κάτι σαν τα συγκεκριμένα κουνούπια που συλλέχθηκαν για ένα ερευνητικό έργο ή αυτοκίνητα που είχαν παραμεριστεί και χρησιμοποιήθηκαν για δοκιμές ασφάλειας κατά της σύγκρουσης.
  • STDEV.P: Αυτή η συνάρτηση υπολογίζει την τυπική απόκλιση ενώ αντιμετωπίζει τα δεδομένα ως ολόκληρο τον πληθυσμό. Ένας ολόκληρος πληθυσμός θα ήταν κάτι σαν όλα τα κουνούπια στη Γη ή κάθε αυτοκίνητο σε μια παραγωγή ενός συγκεκριμένου μοντέλου.

Η επιλογή σας βασίζεται στο σύνολο δεδομένων σας. Η διαφορά συνήθως θα είναι μικρή, αλλά το αποτέλεσμα της συνάρτησης "STDEV.P" θα είναι πάντα μικρότερο από το αποτέλεσμα της συνάρτησης "STDEV.S" για το ίδιο σύνολο δεδομένων. Είναι μια πιο συντηρητική προσέγγιση για να υποθέσουμε ότι υπάρχει μεγαλύτερη μεταβλητότητα στα δεδομένα.

Ας δούμε ένα παράδειγμα

Για το παράδειγμά μας, έχουμε δύο στήλες ("Τιμές" και "Z-Score") και τρία κελιά "βοηθού" για την αποθήκευση των αποτελεσμάτων των λειτουργιών "AVERAGE", "STDEV.S" και "STDEV.P". Η στήλη "Τιμές" περιέχει δέκα τυχαίους αριθμούς με κέντρο περίπου 500 και η στήλη "Z-Score" είναι όπου θα υπολογίσουμε τη βαθμολογία Z χρησιμοποιώντας τα αποτελέσματα που είναι αποθηκευμένα στα κελιά "βοηθός"

Αρχικά, θα υπολογίσουμε τον μέσο όρο των τιμών χρησιμοποιώντας τη συνάρτηση "AVERAGE". Επιλέξτε το κελί όπου θα αποθηκεύσετε το αποτέλεσμα της λειτουργίας «ΜΕΣΟ».

Πληκτρολογήστε τον ακόλουθο τύπο και πατήστε enter -ή- χρησιμοποιήστε το μενού "Τύποι".

= ΜΕΣΟΣ (E2: E13)

Για πρόσβαση στη λειτουργία μέσω του μενού "Τύποι", επιλέξτε το αναπτυσσόμενο μενού "Περισσότερες λειτουργίες", επιλέξτε "Στατιστική" και, στη συνέχεια, κάντε κλικ στο "ΜΕΣΟ".

Στο παράθυρο Function Arguments, επιλέξτε όλα τα κελιά στη στήλη "Τιμές" ως είσοδο για το πεδίο "Number1". Δεν χρειάζεται να ανησυχείτε για το πεδίο "Number2".

Τώρα πατήστε "OK".

Στη συνέχεια, πρέπει να υπολογίσουμε την τυπική απόκλιση των τιμών χρησιμοποιώντας είτε τη συνάρτηση "STDEV.S" είτε "STDEV.P". Σε αυτό το παράδειγμα, θα σας δείξουμε πώς να υπολογίσετε και τις δύο τιμές, ξεκινώντας από το "STDEV.S". Επιλέξτε το κελί όπου θα αποθηκευτεί το αποτέλεσμα.

Για να υπολογίσετε την τυπική απόκλιση χρησιμοποιώντας τη συνάρτηση "STDEV.S", πληκτρολογήστε αυτόν τον τύπο και πατήστε Enter (ή αποκτήστε πρόσβαση σε αυτό μέσω του μενού "Τύποι").

= STDEV.S (E3: E12)

Για να αποκτήσετε πρόσβαση στη λειτουργία μέσω του μενού "Τύποι", επιλέξτε το αναπτυσσόμενο μενού "Περισσότερες λειτουργίες", επιλέξτε την επιλογή "Στατιστική", κάντε κύλιση προς τα κάτω και, στη συνέχεια, κάντε κλικ στην εντολή "STDEV.S".

Στο παράθυρο Function Arguments, επιλέξτε όλα τα κελιά στη στήλη "Τιμές" ως είσοδο για το πεδίο "Number1". Δεν χρειάζεται να ανησυχείτε για το πεδίο "Number2" εδώ.

Τώρα πατήστε "OK".

Στη συνέχεια, θα υπολογίσουμε την τυπική απόκλιση χρησιμοποιώντας τη συνάρτηση "STDEV.P". Επιλέξτε το κελί όπου θα αποθηκευτεί το αποτέλεσμα.

Για να υπολογίσετε την τυπική απόκλιση χρησιμοποιώντας τη συνάρτηση "STDEV.P", πληκτρολογήστε αυτόν τον τύπο και πατήστε Enter (ή αποκτήστε πρόσβαση σε αυτό μέσω του μενού "Τύποι").

= STDEV.P (E3: E12)

Για να αποκτήσετε πρόσβαση στη λειτουργία μέσω του μενού "Τύποι", επιλέξτε το αναπτυσσόμενο μενού "Περισσότερες λειτουργίες", επιλέξτε την επιλογή "Στατιστική", κάντε κύλιση προς τα κάτω και, στη συνέχεια, κάντε κλικ στον τύπο "STDEV.P".

Στο παράθυρο Function Arguments, επιλέξτε όλα τα κελιά στη στήλη "Τιμές" ως είσοδο για το πεδίο "Number1". Και πάλι, δεν θα χρειαστεί να ανησυχείτε για το πεδίο "Number2".

Τώρα πατήστε "OK".

Τώρα που έχουμε υπολογίσει τη μέση και τυπική απόκλιση των δεδομένων μας, έχουμε ό, τι χρειαζόμαστε για να υπολογίσουμε τη βαθμολογία Z. Μπορούμε να χρησιμοποιήσουμε έναν απλό τύπο που αναφέρει τα κελιά που περιέχουν τα αποτελέσματα των συναρτήσεων "AVERAGE" και "STDEV.S" ή "STDEV.P".

Επιλέξτε το πρώτο κελί στη στήλη "Z-Score". Θα χρησιμοποιήσουμε το αποτέλεσμα της λειτουργίας "STDEV.S" για αυτό το παράδειγμα, αλλά θα μπορούσατε επίσης να χρησιμοποιήσετε το αποτέλεσμα από το "STDEV.P".

Πληκτρολογήστε τον παρακάτω τύπο και πατήστε Enter:

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

Εναλλακτικά, θα μπορούσατε να χρησιμοποιήσετε τα ακόλουθα βήματα για να εισαγάγετε τον τύπο αντί να πληκτρολογήσετε:

  1. Κάντε κλικ στο κελί F3 και πληκτρολογήστε =(
  2. Επιλέξτε το κελί E3. (Μπορείτε να πατήσετε το αριστερό πλήκτρο βέλους μία φορά ή να χρησιμοποιήσετε το ποντίκι)
  3. Πληκτρολογήστε το σύμβολο μείον -
  4. Επιλέξτε το κελί G3 και, στη συνέχεια, πατήστε το πλήκτρο F4 για να προσθέσετε τους χαρακτήρες "$" για να κάνετε μια "απόλυτη" αναφορά στο κελί (θα κυλήσει στο "G3"> " $ G $ 3 ″>" G $ 3 ″> " $ G3 ″> "G3" εάν συνεχίσετε να πατάτε το F4 )
  5. Τύπος )/
  6. Επιλέξτε το κελί H3 (ή I3 εάν χρησιμοποιείτε το "STDEV.P") και πατήστε F4 για να προσθέσετε τους δύο χαρακτήρες "$".
  7. πατήστε Enter

Το Z-Score υπολογίστηκε για την πρώτη τιμή. Είναι 0,15945 τυπικές αποκλίσεις κάτω από το μέσο όρο. Για να ελέγξετε τα αποτελέσματα, μπορείτε να πολλαπλασιάσετε την τυπική απόκλιση με αυτό το αποτέλεσμα (6.271629 * -0.15945) και να ελέγξετε ότι το αποτέλεσμα είναι ίσο με τη διαφορά μεταξύ της τιμής και του μέσου όρου (499-500). Και τα δύο αποτελέσματα είναι ίδια, οπότε η τιμή έχει νόημα.

Ας υπολογίσουμε τις βαθμολογίες Z των υπόλοιπων τιμών. Επισημάνετε ολόκληρη τη στήλη "Z-Score" ξεκινώντας από το κελί που περιέχει τον τύπο.

Πατήστε Ctrl + D, το οποίο αντιγράφει τον τύπο στο επάνω κελί κάτω από όλα τα άλλα επιλεγμένα κελιά.

Τώρα ο τύπος έχει «συμπληρωθεί» σε όλα τα κελιά και το καθένα θα αναφέρεται πάντα στα σωστά κελιά «ΜΕΣΟ» και «STDEV.S» ή «STDEV.P» λόγω των χαρακτήρων «$». Εάν λάβετε σφάλματα, επιστρέψτε και βεβαιωθείτε ότι οι χαρακτήρες "$" περιλαμβάνονται στον τύπο που εισαγάγατε.

Υπολογισμός της βαθμολογίας Z χωρίς τη χρήση κελιών «Βοηθού»

Τα βοηθητικά κελιά αποθηκεύουν ένα αποτέλεσμα, όπως αυτά που αποθηκεύουν τα αποτελέσματα των λειτουργιών "AVERAGE", "STDEV.S" και "STDEV.P". Μπορούν να είναι χρήσιμα αλλά δεν είναι πάντα απαραίτητα. Μπορείτε να τα παραλείψετε εντελώς κατά τον υπολογισμό μιας βαθμολογίας Z χρησιμοποιώντας τους ακόλουθους γενικευμένους τύπους, αντ 'αυτού.

Εδώ είναι ένα που χρησιμοποιεί τη λειτουργία "STDEV.S":

= (Τιμή-ΜΕΣΟ (τιμές)) / STDEV.S (τιμές)

Και ένα χρησιμοποιώντας τη λειτουργία "STEV.P":

= (Τιμή-ΜΕΣΟ (τιμές)) / STDEV.P (τιμές)

Όταν εισάγετε τις περιοχές κελιών για τις "Τιμές" στις συναρτήσεις, φροντίστε να προσθέσετε απόλυτες αναφορές ("$" χρησιμοποιώντας το F4) έτσι ώστε όταν "συμπληρώνετε" να μην υπολογίζετε τη μέση ή τυπική απόκλιση διαφορετικού εύρους κυττάρων σε κάθε τύπο.

Εάν έχετε ένα μεγάλο σύνολο δεδομένων, μπορεί να είναι πιο αποτελεσματικό να χρησιμοποιείτε βοηθητικά κελιά, επειδή δεν υπολογίζει το αποτέλεσμα των λειτουργιών "AVERAGE" και "STDEV.S" ή "STDEV.P" κάθε φορά, εξοικονομώντας πόρους επεξεργαστή και επιτάχυνση του χρόνου που απαιτείται για τον υπολογισμό των αποτελεσμάτων.

Επίσης, το "$ G $ 3" ​​χρειάζεται λιγότερα byte για αποθήκευση και λιγότερη μνήμη RAM για φόρτωση από το "AVERAGE ($ E $ 3: $ E $ 12).". Αυτό είναι σημαντικό επειδή η τυπική έκδοση του 32-bit του Excel περιορίζεται σε 2 GB μνήμης RAM (η έκδοση 64-bit δεν έχει περιορισμούς σχετικά με το πόσο RAM μπορεί να χρησιμοποιηθεί).