Τυπική απόκλιση - Excel και Φύλλα Google

Αυτό το σεμινάριο δείχνει πώς να χρησιμοποιήσετε το Λειτουργία τυπικής απόκλισης του Excel στο Excel για τον υπολογισμό της τυπικής απόκλισης για ολόκληρο τον πληθυσμό.

ΤΥΠΙΚΗ ΑΠΟΚΛΕΙΣΗ Επισκόπηση λειτουργίας

Η συνάρτηση STANDARD DEVIATION Υπολογίζει τον υπολογισμό της τυπικής απόκλισης για έναν ολόκληρο πληθυσμό.

Για να χρησιμοποιήσετε τη συνάρτηση ΦΥΛΛΟ ΕΡΓΑΣΙΑΣ ΤΥΠΙΚΗΣ ΔΙΑΔΟΣΗΣ, επιλέξτε ένα κελί και πληκτρολογήστε:

(Παρατηρήστε πώς εμφανίζονται οι εισαγωγές τύπου)

ΤΥΠΙΚΗ ΛΕΙΤΟΥΡΓΙΑ ΣΥΝΔΕΣΗΣ Σύνταξη και είσοδοι:

1	= STDEV (αριθμός1, [αριθμός2],…)

αριθμούς- Τιμές για να λάβετε την τυπική διακύμανση

Πώς να υπολογίσετε την τυπική απόκλιση στο Excel

Όποτε ασχολείστε με δεδομένα, θα θέλετε να εκτελέσετε κάποιες βασικές δοκιμές για να σας βοηθήσουν να τα κατανοήσετε. Συνήθως θα ξεκινήσετε υπολογίζοντας το μέσο όρο, χρησιμοποιώντας τη συνάρτηση Excel AVERAGE <>.

Αυτό σας δίνει μια ιδέα για το πού βρίσκεται η «μέση» των δεδομένων. Και από εκεί, θα θέλετε να δείτε πόσο διασκορπισμένα είναι τα δεδομένα σε αυτό το μεσαίο σημείο. Εδώ έρχεται η τυπική απόκλιση.

Το Excel σας δίνει μια σειρά συναρτήσεων για τον υπολογισμό της τυπικής απόκλισης - STDEV, STDEV.P, STDEV.S και DSTDEV. Θα φτάσουμε σε όλα αυτά, αλλά πρώτα, ας μάθουμε ποια είναι η τυπική απόκλιση είναι, ακριβώς.

Τι είναι η τυπική απόκλιση;

Η τυπική απόκλιση σάς δίνει μια ιδέα για το πόσο μακριά είναι τα σημεία δεδομένων σας από το μέσο όρο. Πάρτε το ακόλουθο σύνολο δεδομένων βαθμολογιών δοκιμής από 100:

1	48,49,50,51,52

Ο μέσος όρος αυτού του συνόλου δεδομένων είναι 50 (αθροίστε όλους τους αριθμούς και διαιρέστε με n, όπου n είναι ο αριθμός των τιμών στο εύρος).

Τώρα κοιτάξτε αυτό το επόμενο σύνολο δεδομένων:

1	10,25,50,75,90

Ο μέσος όρος αυτού του συνόλου δεδομένων είναι επίσης 50 - αλλά οι δύο περιοχές λένε μια πολύ διαφορετική ιστορία. Εάν χρησιμοποιήσατε μόνο τον μέσο όρο, ίσως πιστεύετε ότι οι δύο ομάδες ήταν περίπου ίσες στην ικανότητά τους - και κατά μέσο όρο, είναι.

Αλλά στην πρώτη ομάδα, έχουμε 5 άτομα που πήραν πολύ παρόμοιες, πολύ μέτριες βαθμολογίες. Και στο δεύτερο γκρουπ, εμείς κάναμε δύο ψηλά ιπτάμενα που ισορροπήσαμε από μερικούς κακούς σκόρερ, με ένα άτομο στη μέση. ο εξάπλωση η βαθμολογία είναι πολύ διαφορετική, καθιστώντας την ερμηνεία των δεδομένων πολύ διαφορετική.

Η τυπική απόκλιση είναι ένα μέτρο αυτής της απόδοσης.

Πώς υπολογίζεται η τυπική απόκλιση

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

Για να υπολογίσετε την τυπική απόκλιση, ακολουθείτε αυτήν τη διαδικασία:

1) Υπολογίστε το μέσο όρο

Ας πάρουμε το πρώτο μας σύνολο δεδομένων παραπάνω: 48,49,50,51,52

Γνωρίζουμε ήδη τη μέση τιμή (50), την οποία έχω επιβεβαιώσει εδώ με τη συνάρτηση Excel AVERAGE <>:

1	= ΜΕΣΗ (C4: C8)

2) Αφαιρέστε το μέσο όρο από κάθε τιμή στο σύνολο δεδομένων

Το έκανα με τον ακόλουθο τύπο:

1	= C4- $ H $ 4

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

Το αποτέλεσμα:

Τώρα, ας σταματήσουμε εδώ για ένα δευτερόλεπτο. Αν ρίξετε μια ματιά στη νέα στήλη - θα δείτε ότι οι αριθμοί εδώ αθροίζονται στο μηδέν. Ο μέσος όρος αυτών των αριθμών είναι επίσης μηδενικός.

Φυσικά, η διάδοση των δεδομένων μας δεν μπορεί να είναι μηδενική - γνωρίζουμε ότι υπάρχει κάποια παραλλαγή εκεί. Χρειαζόμαστε έναν τρόπο να αναπαραστήσουμε αυτήν τη διακύμανση, χωρίς ο μέσος όρος να είναι μηδενικός.

3) Τετραγωνίστε τις διαφορές

Αυτό μπορούμε να το πετύχουμε με τον τετραγωνισμό των διαφορών. Ας προσθέσουμε λοιπόν μια νέα στήλη και τετραγωνίζουμε τους αριθμούς στη στήλη D:

1

= D4*D4

Αυτό φαίνεται καλύτερα. Τώρα έχουμε κάποια παραλλαγή και το ποσό της διακύμανσης σχετίζεται με το πόσο απέχει κάθε βαθμολογία από τον μέσο όρο.

4) Υπολογίστε τη διακύμανση - το μέσο όρο των τετραγωνισμένων διαφορών

Το επόμενο βήμα, είναι να πάρετε τον μέσο όρο αυτών των τετραγωνισμένων διαφορών. Υπάρχουν δύο τρόποι για να το κάνετε αυτό κατά τον υπολογισμό της τυπικής απόκλισης.

• Εάν χρησιμοποιείτε πληθυσμιακά δεδομένα, παίρνετε απλά το μέσο (αθροίστε τις τιμές και διαιρέστε με n)
• Εάν χρησιμοποιείτε δειγμα δεδομένων, παίρνετε το άθροισμα των τιμών και διαιρείτε με n-1

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

Δείγματα δεδομένων σημαίνει ότι δεν έχετε όλα τα δεδομένα σας, μόνο ένα δείγμα που λαμβάνεται από μεγαλύτερο πληθυσμό. Συνήθως, ο στόχος σας με δείγματα δεδομένων είναι να κάνετε μια εκτίμηση της αξίας του μεγαλύτερου πληθυσμού.

Μια δημοσκόπηση πολιτικής γνώμης είναι ένα καλό παράδειγμα δειγμάτων δεδομένων - οι ερευνητές πραγματοποίησαν έρευνα, ας πούμε, 1.000 άτομα για να πάρουν μια ιδέα για το τι σκέφτεται μια ολόκληρη χώρα ή κράτος.

Εδώ δεν έχουμε δείγμα. Έχουμε απλώς πέντε μέλη της οικογένειας με στατιστικά προσανατολισμό που θέλουν να υπολογίσουν την τυπική απόκλιση ενός τεστ που έκαναν όλοι. Έχουμε όλα τα σημεία δεδομένων και δεν κάνουμε εκτίμηση για μια μεγαλύτερη ομάδα ανθρώπων. Αυτά είναι δεδομένα πληθυσμού - οπότε μπορούμε να πάρουμε τον μέσο όρο εδώ:

1	= ΜΕΣΟΣ (Ε4: Ε8)

Εντάξει, έτσι έχουμε 2. Αυτή η βαθμολογία είναι γνωστή ως "διακύμανση" και είναι το σημείο βάσης για πολλές στατιστικές δοκιμές, συμπεριλαμβανομένης της τυπικής απόκλισης. Μπορείτε να διαβάσετε περισσότερα για τη διακύμανση στην κύρια σελίδα της: πώς να υπολογίσετε τη διακύμανση στο Excel <>.

5) Αποκτήστε την τετραγωνική ρίζα της διακύμανσης

Τετραγωνίσαμε τους αριθμούς μας νωρίτερα, γεγονός που προφανώς διογκώνει λίγο τις τιμές. Έτσι, για να επαναφέρουμε το σχήμα στις πραγματικές διαφορές των βαθμολογιών από το μέσο όρο, πρέπει να τετραγωνίσουμε το αποτέλεσμα του βήματος 4:

1	= SQRT (H4)

Και έχουμε το αποτέλεσμα: η τυπική απόκλιση είναι 1,414

Επειδή έχουμε τετραγωνική ρίζα στους προηγούμενους τετραγωνισμένους αριθμούς μας, η τυπική απόκλιση δίνεται στις ίδιες μονάδες με τα αρχικά δεδομένα. Άρα, η τυπική απόκλιση εδώ είναι 1,414 σημεία δοκιμής.

Τυπική απόκλιση όταν τα δεδομένα είναι πιο εκτεταμένα

Νωρίτερα είχαμε ένα δεύτερο παράδειγμα εύρους δεδομένων: 10,25,50,75,90

Απλώς για διασκέδαση, ας δούμε τι συμβαίνει όταν υπολογίζουμε την τυπική απόκλιση σε αυτά τα δεδομένα:

Όλοι οι τύποι είναι ακριβώς οι ίδιοι με πριν (σημειώστε ότι ο Συνολικός Μέσος Όρος είναι ακόμα 50).

Το μόνο που άλλαξε ήταν η διάδοση των βαθμολογιών στη στήλη C. Αλλά τώρα, η τυπική απόκλιση είναι πολύ υψηλότερη, στα 29.832 σημεία δοκιμής.

Φυσικά, δεδομένου ότι έχουμε μόνο 5 σημεία δεδομένων, είναι πολύ εύκολο να δούμε ότι η κατανομή των βαθμολογιών είναι διαφορετική μεταξύ των δύο σετ. Αλλά όταν διαθέτετε 100 ή 1.000 σημεία δεδομένων, δεν μπορείτε να το πείτε αυτό απλά σαρώνοντας γρήγορα τα δεδομένα. Και αυτός ακριβώς είναι ο λόγος που χρησιμοποιούμε την τυπική απόκλιση.

Οι λειτουργίες του Excel για τον υπολογισμό της τυπικής απόκλισης

Τώρα που γνωρίζετε πώς λειτουργεί η τυπική απόκλιση, δεν χρειάζεται να περάσετε ολόκληρη τη διαδικασία για να φτάσετε στην τυπική απόκλιση. Μπορείτε απλά να χρησιμοποιήσετε μία από τις ενσωματωμένες λειτουργίες του Excel.

Το Excel έχει πολλές λειτουργίες για το σκοπό αυτό:

• Π υπολογίζει την τυπική απόκλιση για τα δεδομένα του πληθυσμού (χρησιμοποιώντας την ακριβή μέθοδο που χρησιμοποιήσαμε στο παραπάνω παράδειγμα)
• μικρό υπολογίζει την τυπική απόκλιση για δείγματα δεδομένων (χρησιμοποιώντας τη μέθοδο n-1 που αγγίξαμε νωρίτερα)
• STDEV είναι ακριβώς το ίδιο με το STDEV.S. Αυτή είναι μια παλαιότερη λειτουργία που έχει αντικατασταθεί από STDEV.S και STDEV.P.
• STDEVA είναι πολύ παρόμοιο με το STDEV.S, εκτός από το ότι περιλαμβάνει κελιά κειμένου και κελιά Boolean (TRUE/FALSE) κατά τον υπολογισμό του.
• STDEVPA είναι πολύ παρόμοιο με το STDEV.P, εκτός από το ότι περιλαμβάνει κελιά κειμένου και κελιά Boolean (TRUE/FALSE) κατά τον υπολογισμό του.

Πω πω, πολλές επιλογές εδώ! Μην τρομάζετε - στη συντριπτική πλειοψηφία των περιπτώσεων, θα χρησιμοποιείτε είτε STDEV.P είτε STDEV.S.

Ας περάσουμε καθένα από αυτά με τη σειρά, ξεκινώντας από το STDEV.P, αφού αυτή είναι η μέθοδος που μόλις δουλέψαμε.

Η συνάρτηση Excel STDEV.P

Το STDEV.P υπολογίζει την τυπική απόκλιση για δεδομένα πληθυσμού. Το χρησιμοποιείς ως εξής:

1	= STDEV.P (C4: C8)

Ορίζετε ένα όρισμα στο STDEV.P: το εύρος δεδομένων για το οποίο θέλετε να υπολογίσετε την τυπική απόκλιση.

Αυτό είναι το ίδιο παράδειγμα που ακολουθήσαμε βήμα-βήμα παραπάνω όταν υπολογίσαμε την τυπική απόκλιση με το χέρι. Και όπως μπορείτε να δείτε παραπάνω, έχουμε ακριβώς το ίδιο αποτέλεσμα - 1.414.

Σημείωση Το STDEV.P αγνοεί τυχόν κελιά που περιέχουν κείμενο ή τιμές Boolean (TRUE/FALSE). Εάν πρέπει να τα συμπεριλάβετε, χρησιμοποιήστε το STDEVPA.

Η συνάρτηση Excel STDEV.S

Το STDEV.S υπολογίζει την τυπική απόκλιση για δείγματα δεδομένων. Χρησιμοποιήστε το ως εξής:

1	= STDEV.S (C4: C8)

Και πάλι, χρειάζεται ένα επιχείρημα - το εύρος δεδομένων για το οποίο θέλετε να γνωρίζετε την τυπική απόκλιση.

Πριν μπούμε σε ένα παράδειγμα, ας συζητήσουμε τη διαφορά μεταξύ STDEV.S και STDEV.P.

Όπως έχουμε ήδη συζητήσει, το STDEV.S πρέπει να χρησιμοποιείται σε δείγματα δεδομένων - όταν τα δεδομένα σας αποτελούν τμήμα ενός μεγαλύτερου συνόλου. Ας υποθέσουμε, λοιπόν, ότι στο παραπάνω παράδειγμά μας, περισσότεροι άνθρωποι είχαν κάνει το τεστ. Θέλουμε να υπολογίσουμε την τυπική απόκλιση όλων όσων έκαναν το τεστ, χρησιμοποιώντας μόνο αυτές τις πέντε βαθμολογίες. Τώρα χρησιμοποιούμε δείγματα δεδομένων.

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

Αντί να χρησιμοποιήσουμε την κανονική μέθοδο - αθροίσουμε όλες τις τιμές και διαιρούμε με n, θα αθροίσουμε όλες τις τιμές και θα διαιρέσουμε με n-1:

1	= SUM (E4: E8) / (COUNT (E4: E8) -1)

Σε αυτόν τον τύπο:

• Το SUM παίρνει το άθροισμα των τετραγωνισμένων διαφορών
• Ο COUNT επιστρέφει το n μας, από το οποίο αφαιρούμε το 1
• Στη συνέχεια, απλά διαιρούμε το άθροισμά μας με το n-1

Αυτή τη φορά, ο μέσος όρος των τετραγωνικών διαφορών είναι 2,5 (μπορείτε να θυμηθείτε ότι ήταν 2 προηγουμένως, οπότε είναι λίγο υψηλότερος).

Γιατί λοιπόν διαιρούμε με n-1 αντί για n όταν ασχολούμαστε με δείγματα δεδομένων;

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

Εάν είστε πραγματικά περίεργοι να μάθετε γιατί, δείτε την κύρια σελίδα για τον τρόπο υπολογισμού της διακύμανσης στο Excel <>.

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

1	= SQRT (H4)

Παίρνουμε 1.581.

Το STDEV.S κάνει όλους τους παραπάνω υπολογισμούς για εμάς και επιστρέφει την τυπική απόκλιση δείγματος σε ένα μόνο κελί. Ας δούμε λοιπόν τι θα καταλήξει…

1	= STDEV.S (C4: C8)

Ναι, πάλι 1.581.

Η συνάρτηση Excel STDEV

Η συνάρτηση STDEV του Excel λειτουργεί με τον ίδιο ακριβώς τρόπο όπως το STDEV.S - δηλαδή υπολογίζει την τυπική απόκλιση για ένα δείγμα δεδομένων.

Το χρησιμοποιείτε με τον ίδιο τρόπο:

1	= STDEV (C4: C8)

Και πάλι έχουμε το ίδιο αποτέλεσμα.

Σημαντική σημείωση: Το STDEV είναι μια "Λειτουργία συμβατότητας", πράγμα που σημαίνει ότι η Microsoft απαλλάσσεται από αυτό. Λειτουργεί ακόμη προς το παρόν, οπότε τυχόν παλαιότερα υπολογιστικά φύλλα θα συνεχίσουν να λειτουργούν κανονικά. Αλλά σε μελλοντικές εκδόσεις του Excel, η Microsoft ενδέχεται να το εγκαταλείψει εντελώς, οπότε θα πρέπει να χρησιμοποιήσετε το STDEV.S αντί του STDEV όπου είναι δυνατόν.

Η συνάρτηση Excel STDEVA

Το STDEVA χρησιμοποιείται επίσης για τον υπολογισμό της τυπικής απόκλισης για ένα δείγμα, αλλά έχει μερικές σημαντικές διαφορές που πρέπει να γνωρίζετε:

• Οι ΑΛΗΘΙΝΕΣ τιμές υπολογίζονται ως 1
• ΛΑΘΟΣ τιμές υπολογίζονται ως 0
• Οι συμβολοσειρές κειμένου υπολογίζονται ως 0

Χρησιμοποιήστε το ως εξής:

1	= STDEVA (C4: C8)

Τέσσερις ακόμη φίλοι και μέλη της οικογένειας έδωσαν στις βαθμολογίες τους. Αυτά εμφανίζονται στη στήλη C και η στήλη D υποδεικνύει πώς η STDEVA ερμηνεύει αυτά τα δεδομένα.

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

Η συνάρτηση Excel STDEVPA

Το STDEVPA υπολογίζει την τυπική απόκλιση για έναν πληθυσμό με τον ίδιο τρόπο όπως το STDEV.P. Ωστόσο, περιλαμβάνει επίσης τιμές Boolean και συμβολοσειρές κειμένου στον υπολογισμό, οι οποίες ερμηνεύονται ως εξής:

• Οι ΑΛΗΘΙΝΕΣ τιμές υπολογίζονται ως 1
• ΛΑΘΟΣ τιμές υπολογίζονται ως 0
• Οι συμβολοσειρές κειμένου υπολογίζονται ως 0

Το χρησιμοποιείς ως εξής:

1	= STDEVPA (C4: C12)

Φιλτράρισμα δεδομένων πριν τον υπολογισμό της τυπικής απόκλισης

Στον πραγματικό κόσμο, δεν θα έχετε πάντα τα ακριβή δεδομένα που χρειάζεστε σε έναν όμορφο τακτοποιημένο πίνακα. Συχνά, θα έχετε ένα μεγάλο υπολογιστικό φύλλο γεμάτο δεδομένα, τα οποία θα πρέπει να φιλτράρετε πριν υπολογίσετε την τυπική απόκλιση.

Μπορείτε να το κάνετε αυτό πολύ εύκολα με τις λειτουργίες βάσης δεδομένων του Excel: DSTDEV (για δείγματα) και DSTDEVP (για πληθυσμούς).

Αυτές οι λειτουργίες σάς επιτρέπουν να δημιουργήσετε έναν πίνακα κριτηρίων, στον οποίο μπορείτε να ορίσετε όλα τα φίλτρα που χρειάζεστε. Οι λειτουργίες εφαρμόζουν αυτά τα φίλτρα στο παρασκήνιο πριν επιστρέψουν την τυπική απόκλιση. Με αυτόν τον τρόπο δεν χρειάζεται να αγγίξετε ένα Αυτόματο φίλτρο ή να τραβήξετε δεδομένα σε ξεχωριστό φύλλο - το DSTDEV και το SDTDEVP μπορούν να κάνουν όλα αυτά για εσάς.

Μάθετε περισσότερα στην κύρια σελίδα για τις λειτουργίες Excel DSTDEV και DSTDEVP <>.

ΤΥΠΙΚΗ ΛΕΙΤΟΥΡΓΙΑ ΛΕΙΤΟΥΡΓΙΑΣ στα Υπολογιστικά φύλλα Google

Η συνάρτηση STANDARD DEVIATION λειτουργεί ακριβώς το ίδιο στα Υπολογιστικά φύλλα Google όπως και στο Excel: