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

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

Τι είναι το 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" για το ίδιο σύνολο δεδομένων. Είναι μια πιο συντηρητική προσέγγιση για να υποθέσουμε ότι υπάρχει μεγαλύτερη μεταβλητότητα στα δεδομένα.

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

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

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

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

= ΜΕΣΟ (E2: E13)

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

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

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

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

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

= STDEV.S (Ε3: Ε12)

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

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

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

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

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

= STDEV.P (Ε3: Ε12)

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

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

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

Τώρα που υπολογίσαμε τη μέση και τυπική απόκλιση των δεδομένων μας, έχουμε ό, τι πρέπει να υπολογίσουμε το Z-Score. Μπορούμε να χρησιμοποιήσουμε έναν απλό τύπο που αναφέρει τα κελιά που περιέχουν τα αποτελέσματα των λειτουργιών "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"> "$σολ$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-Score" ξεκινώντας με το κελί που περιέχει τον τύπο.

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

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

Υπολογίζοντας το Z-Score χωρίς να χρησιμοποιήσετε τα «Helper» Cells

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

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

= (Τιμή-AVERAGE (τιμές)) / STDEV.S (τιμές)

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

= (Τιμή-AVERAGE (τιμές)) / STDEV.P (τιμές)

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

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

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