Το 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-Score της βαθμολογίας κάθε μαθητή με βάση τους μέσους όρους βαθμολογίας σε κάθε τάξη και την τυπική απόκλιση των βαθμολογιών σε κάθε τάξη. Η σύγκριση των βαθμολογιών Z των δύο μαθητών θα μπορούσε να αποκαλύψει ότι ο μαθητής με βαθμολογία 87% τα πήγε καλύτερα σε σύγκριση με την υπόλοιπη τάξη του από τον μαθητή με το σκορ 98% σε σύγκριση με την υπόλοιπη τάξη του.
Η πρώτη στατιστική τιμή που χρειάζεστε είναι η «μέση» και η συνάρτηση «AVERAGE» του Excel υπολογίζει αυτήν την τιμή. Απλώς αθροίζει όλες τις τιμές σε μια περιοχή κελιών και διαιρεί αυτό το άθροισμα με τον αριθμό των κελιών που περιέχουν αριθμητικές τιμές (αγνοεί τα κενά κελιά).
Η άλλη στατιστική τιμή που χρειαζόμαστε είναι η «τυπική απόκλιση» και το Excel έχει δύο διαφορετικές συναρτήσεις για τον υπολογισμό της τυπικής απόκλισης με ελαφρώς διαφορετικούς τρόπους.
Οι προηγούμενες εκδόσεις του Excel είχαν μόνο τη συνάρτηση «STDEV», η οποία υπολογίζει την τυπική απόκλιση ενώ αντιμετωπίζει τα δεδομένα ως «δείγμα» ενός πληθυσμού. Το Excel 2010 το χώρισε σε δύο συναρτήσεις που υπολογίζουν την τυπική απόκλιση:
STDEV.S: Αυτή η λειτουργία είναι πανομοιότυπη με την προηγούμενη λειτουργία “STDEV”. Υπολογίζει την τυπική απόκλιση ενώ αντιμετωπίζει τα δεδομένα ως «δείγμα» ενός πληθυσμού. Ένα δείγμα ενός πληθυσμού μπορεί να είναι κάτι σαν τα συγκεκριμένα κουνούπια που συλλέχθηκαν για ένα ερευνητικό έργο ή αυτοκίνητα που παραμερίστηκαν και χρησιμοποιήθηκαν για δοκιμές ασφάλειας σύγκρουσης.
STDEV.P: Αυτή η συνάρτηση υπολογίζει την τυπική απόκλιση ενώ αντιμετωπίζει τα δεδομένα ως ολόκληρο τον πληθυσμό. Ένας ολόκληρος πληθυσμός θα ήταν κάτι σαν όλα τα κουνούπια στη Γη ή κάθε αυτοκίνητο σε μια σειρά παραγωγής ενός συγκεκριμένου μοντέλου.
Αυτό που επιλέγετε βασίζεται στο σύνολο δεδομένων σας. Η διαφορά θα είναι συνήθως μικρή, αλλά το αποτέλεσμα της συνάρτησης “STDEV.P” θα είναι πάντα μικρότερο από το αποτέλεσμα της συνάρτησης “STDEV.S” για το ίδιο σύνολο δεδομένων. Είναι μια πιο συντηρητική προσέγγιση να υποθέσουμε ότι υπάρχει μεγαλύτερη μεταβλητότητα στα δεδομένα.
Ας δούμε ένα παράδειγμα
Για το παράδειγμά μας, έχουμε δύο στήλες (“Τιμές” και “Ζ-Βαθμολογία”) και τρία “βοηθητικά” κελιά για την αποθήκευση των αποτελεσμάτων των συναρτήσεων “AVERAGE”, “STDEV.S” και “STDEV.P”. Η στήλη “Τιμές” περιέχει δέκα τυχαίους αριθμούς με κέντρο το 500 και η στήλη “Βαθμολογία Z” είναι όπου θα υπολογίσουμε τη Βαθμολογία Z χρησιμοποιώντας τα αποτελέσματα που είναι αποθηκευμένα στα κελιά “βοηθός”.
Αρχικά, θα υπολογίσουμε τον μέσο όρο των τιμών χρησιμοποιώντας τη συνάρτηση «AVERAGE». Επιλέξτε το κελί όπου θα αποθηκεύσετε το αποτέλεσμα της συνάρτησης «ΜΕΣΟΣ».
Πληκτρολογήστε τον παρακάτω τύπο και πατήστε enter -ή- χρησιμοποιήστε το μενού “Formulas”.
=AVERAGE(E2:E13)
Για να αποκτήσετε πρόσβαση στη συνάρτηση μέσω του μενού «Τύποι», επιλέξτε το αναπτυσσόμενο μενού «Περισσότερες λειτουργίες», επιλέξτε «Στατιστικά» και, στη συνέχεια, κάντε κλικ στο «ΜΕΣΟΣ».
Στο παράθυρο Επιχειρήματα συνάρτησης, επιλέξτε όλα τα κελιά στη στήλη “Τιμές” ως είσοδο για το πεδίο “Αριθμός1”. Δεν χρειάζεται να ανησυχείτε για το πεδίο “Αριθμός2”.
Τώρα πατήστε “OK”.
Στη συνέχεια, πρέπει να υπολογίσουμε την τυπική απόκλιση των τιμών χρησιμοποιώντας είτε τη συνάρτηση «STDEV.S» ή «STDEV.P». Σε αυτό το παράδειγμα, θα σας δείξουμε πώς να υπολογίσετε και τις δύο τιμές, ξεκινώντας με “STDEV.S”. Επιλέξτε το κελί όπου θα αποθηκευτεί το αποτέλεσμα.
Για να υπολογίσετε την τυπική απόκλιση χρησιμοποιώντας τη συνάρτηση «STDEV.S», πληκτρολογήστε αυτόν τον τύπο και πατήστε Enter (ή αποκτήστε πρόσβαση μέσω του μενού «Τύποι»).
=STDEV.S(E3:E12)
Για να αποκτήσετε πρόσβαση στη συνάρτηση μέσω του μενού “Formulas”, επιλέξτε το αναπτυσσόμενο μενού “More Functions”, επιλέξτε “Statistical”, κάντε κύλιση προς τα κάτω και, στη συνέχεια, κάντε κλικ στην εντολή “STDEV.S”.
Στο παράθυρο Επιχειρήματα συνάρτησης, επιλέξτε όλα τα κελιά στη στήλη “Τιμές” ως είσοδο για το πεδίο “Αριθμός1”. Δεν χρειάζεται να ανησυχείτε για το πεδίο “Αριθμός2” εδώ, επίσης.
Τώρα πατήστε “OK”.
Στη συνέχεια, θα υπολογίσουμε την τυπική απόκλιση χρησιμοποιώντας τη συνάρτηση «STDEV.P». Επιλέξτε το κελί όπου θα αποθηκευτεί το αποτέλεσμα.
Για να υπολογίσετε την τυπική απόκλιση χρησιμοποιώντας τη συνάρτηση «STDEV.P», πληκτρολογήστε αυτόν τον τύπο και πατήστε Enter (ή αποκτήστε πρόσβαση μέσω του μενού «Τύποι»).
=STDEV.P(E3:E12)
Για να αποκτήσετε πρόσβαση στη συνάρτηση μέσω του μενού “Τύποι”, επιλέξτε το αναπτυσσόμενο μενού “Περισσότερες λειτουργίες”, επιλέξτε “Στατιστικά”, κάντε κύλιση προς τα κάτω και, στη συνέχεια, κάντε κλικ στον τύπο “STDEV.P”.
Στο παράθυρο Επιχειρήματα συνάρτησης, επιλέξτε όλα τα κελιά στη στήλη “Τιμές” ως είσοδο για το πεδίο “Αριθμός1”. Και πάλι, δεν θα χρειάζεται να ανησυχείτε για το πεδίο “Αριθμός2”.
Τώρα πατήστε “OK”.
Τώρα που έχουμε υπολογίσει τον μέσο όρο και την τυπική απόκλιση των δεδομένων μας, έχουμε όλα όσα χρειαζόμαστε για να υπολογίσουμε το Z-Score. Μπορούμε να χρησιμοποιήσουμε έναν απλό τύπο που αναφέρεται στα κελιά που περιέχουν τα αποτελέσματα των συναρτήσεων “AVERAGE” και “STDEV.S” ή “STDEV.P”.
Επιλέξτε το πρώτο κελί στη στήλη “Z-Score”. Θα χρησιμοποιήσουμε το αποτέλεσμα της συνάρτησης “STDEV.S” για αυτό το παράδειγμα, αλλά θα μπορούσατε επίσης να χρησιμοποιήσετε το αποτέλεσμα από το “STDEV.P”.
Πληκτρολογήστε τον παρακάτω τύπο και πατήστε Enter:
=(E3-$G$3)/$H$3
Εναλλακτικά, μπορείτε να χρησιμοποιήσετε τα παρακάτω βήματα για να εισαγάγετε τον τύπο αντί να πληκτρολογήσετε:
Κάντε κλικ στο κελί F3 και πληκτρολογήστε =(
Επιλέξτε το κελί E3. (Μπορείτε να πατήσετε το αριστερό βέλος μία φορά ή να χρησιμοποιήσετε το ποντίκι)
Πληκτρολογήστε το σύμβολο μείον –
Επιλέξτε το κελί G3 και, στη συνέχεια, πατήστε F4 για να προσθέσετε τους χαρακτήρες “$” για να κάνετε μια “απόλυτη” αναφορά στο κελί (θα περάσει από το “G3” > “$G$3″ > “G$3” > “$G3” > “G3” “αν συνεχίσετε να πατάτε το F4)
Τύπος )/
Επιλέξτε το κελί H3 (ή I3 εάν χρησιμοποιείτε το “STDEV.P”) και πατήστε F4 για να προσθέσετε τους δύο χαρακτήρες “$”.
πατήστε Enter
Το Z-Score έχει υπολογιστεί για την πρώτη τιμή. Είναι 0,15945 τυπικές αποκλίσεις κάτω από το μέσο όρο. Για να ελέγξετε τα αποτελέσματα, μπορείτε να πολλαπλασιάσετε την τυπική απόκλιση με αυτό το αποτέλεσμα (6,271629 * -0,15945) και να ελέγξετε ότι το αποτέλεσμα είναι ίσο με τη διαφορά μεταξύ της τιμής και του μέσου όρου (499-500). Και τα δύο αποτελέσματα είναι ίσα, επομένως η τιμή είναι λογική.
Ας υπολογίσουμε τα Z-Scores των υπόλοιπων τιμών. Επισημάνετε ολόκληρη τη στήλη “Z-Score” ξεκινώντας με το κελί που περιέχει τον τύπο.
Πατήστε Ctrl+D, το οποίο αντιγράφει τον τύπο στο επάνω κελί προς τα κάτω σε όλα τα άλλα επιλεγμένα κελιά.
Τώρα ο τύπος έχει «γεμιστεί» σε όλα τα κελιά και το καθένα θα αναφέρει πάντα τα σωστά κελιά «AVERAGE» και «STDEV.S» ή «STDEV.P» λόγω των χαρακτήρων «$». Εάν λάβετε σφάλματα, επιστρέψτε και βεβαιωθείτε ότι οι χαρακτήρες “$” περιλαμβάνονται στον τύπο που εισαγάγατε.
Υπολογισμός του Z-Score χωρίς τη χρήση κελιών «Βοηθητικά».
Τα βοηθητικά κελιά αποθηκεύουν ένα αποτέλεσμα, όπως αυτά που αποθηκεύουν τα αποτελέσματα των συναρτήσεων “AVERAGE”, “STDEV.S” και “STDEV.P”. Μπορεί να είναι χρήσιμα αλλά δεν είναι πάντα απαραίτητα. Μπορείτε να τα παραλείψετε εντελώς κατά τον υπολογισμό ενός Z-Score, χρησιμοποιώντας τους ακόλουθους γενικευμένους τύπους.
Εδώ είναι ένα που χρησιμοποιεί τη συνάρτηση «STDEV.S»:
=(Value-AVERAGE(Values))/STDEV.S(Values)
Και ένα που χρησιμοποιεί τη λειτουργία «STEV.P»:
=(Value-AVERAGE(Values))/STDEV.P(Values)
Όταν εισάγετε τις περιοχές κελιών για τις “Τιμές” στις συναρτήσεις, φροντίστε να προσθέσετε απόλυτες αναφορές (“$” χρησιμοποιώντας το F4), ώστε όταν “συμπληρώσετε” να μην υπολογίζετε τον μέσο όρο ή την τυπική απόκλιση διαφορετικής περιοχής των κυττάρων σε κάθε τύπο.
Εάν διαθέτετε μεγάλο σύνολο δεδομένων, μπορεί να είναι πιο αποτελεσματικό να χρησιμοποιείτε βοηθητικά κελιά, επειδή δεν υπολογίζει το αποτέλεσμα των λειτουργιών “AVERAGE” και “STDEV.S” ή “STDEV.P” κάθε φορά, εξοικονομώντας πόρους του επεξεργαστή και επιτάχυνση του χρόνου που απαιτείται για τον υπολογισμό των αποτελεσμάτων.
Επίσης, το “$G$3” απαιτεί λιγότερα byte για αποθήκευση και λιγότερη RAM για φόρτωση από το “AVERAGE($E$3:$E$12).”. Αυτό είναι σημαντικό επειδή η τυπική έκδοση 32 bit του Excel περιορίζεται σε 2 GB μνήμης RAM (η έκδοση 64 bit δεν έχει περιορισμούς σχετικά με το πόση μνήμη RAM μπορεί να χρησιμοποιηθεί).