ΔΙΑΦΟΡΑ - Excel & Υπολογιστικά φύλλα Google

Αυτό το σεμινάριο δείχνει πώς να χρησιμοποιήσετε το Συνάρτηση Excel VARIANCE στο Excel για εκτίμηση της διακύμανσης με βάση ένα δεδομένο δείγμα.

VARIANCE Επισκόπηση συνάρτησης

Η συνάρτηση VARIANCE Υπολογίζει την εκτίμηση εκτίμησης με βάση ένα δεδομένο δείγμα.

Για να χρησιμοποιήσετε τη συνάρτηση φύλλου εργασίας VARIANCE Excel, επιλέξτε ένα κελί και πληκτρολογήστε:

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

Συνάρτηση VARIANCE Σύνταξη και είσοδοι:

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

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

Πώς να υπολογίσετε τη διακύμανση στο Excel

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

Το Excel σας δίνει μια σειρά συναρτήσεων για τον υπολογισμό της διακύμανσης - VAR.S, VAR.P, VARA, VARPA και δύο παλαιότερες συναρτήσεις, VAR και VARP.

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

Τι είναι η Διακύμανση;

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

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

1	48,49,50,51,52

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

Στη συνέχεια, πάρτε το ακόλουθο σύνολο αποτελεσμάτων δοκιμής:

1	10,25,50,75,90

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

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

Η διακύμανση χρησιμοποιείται επίσης ως σημείο βάσης για μια σειρά πιο σύνθετων στατιστικών διαδικασιών.

Πώς υπολογίζεται η διακύμανση

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

Ας πούμε ότι έχουμε ένα σύνολο δεδομένων που αντιπροσωπεύει τρία χαρτιά, ένα 4, ένα 6 και 8.

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

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

Αρχικά, υπολογίζουμε τη μέση τιμή. Γνωρίζουμε ότι το εύρος δεδομένων μας είναι 4, 6, 8, οπότε ο μέσος όρος θα είναι:

1	(6 + 4 + 8) / 3 = 6

Το έχω επιβεβαιώσει παρακάτω με τη συνάρτηση Excel AVERAGE <>:

1	= ΜΕΣΟΣ (C4: C6)

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

Στη συνέχεια, αφαιρούμε το μέσο όρο από κάθε τιμή μας.

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

1	= C4- $ H $ 4

Ο μέσος όρος αποθηκεύεται στο Η4, οπότε απλώς το αφαιρώ από κάθε τιμή στον πίνακα. Τα σημάδια του δολαρίου εδώ απλά "κλειδώνουν" αυτήν την αναφορά κελιού στο Η4, έτσι ώστε όταν το αντιγράφω στη στήλη, να παραμείνει το ίδιο.

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

Εχουμε:

123	4 - 6 = -26 - 6 = 08 - 6 = 2

Πρέπει να πάρουμε τον μέσο όρο αυτών των διαφορών από το μέσο όρο, αλλά ο μέσος όρος αυτών των τριών τιμών, είναι μηδέν! Πρέπει λοιπόν να τονίσουμε τις διαφορές, τις οποίες κάνουμε τετραγωνίζοντάς τες.

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

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

1

= D4*D4

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

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

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

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

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

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

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

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

Η διακύμανση λοιπόν του πληθυσμού μας είναι 2,666.

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

1	Διακύμανση δείγματος = (4 + 0 + 4) / (3 - 1)

Ή:

1	Διακύμανση δείγματος = 8 /2 = 4

Γιατί να διαιρεθεί με n-1 με δεδομένα δείγματος, αντί για απλά n;

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

Σκεφτείτε το έτσι: αν πάρετε ένα δείγμα δεδομένων από έναν πληθυσμό, αυτές οι τιμές θα τείνουν να είναι πιο κοντά στο μέσο όρο του δείγμα από ό, τι είναι στο μέσο του πληθυσμός.

Αυτό σημαίνει ότι αν διαιρέσετε μόνο με το n, θα υποτιμήσετε λίγο τη διακύμανση του πληθυσμού. Η διαίρεση με n-1 διορθώνει λίγο αυτό.

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

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

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

Στον παρακάτω πίνακα, έχω αναφέρει τα πάντα. Κάθε σειρά του πίνακα είναι διαφορετικό δείγμα και οι στήλες Β και Γ δείχνουν τις δύο κάρτες που επιλέχθηκαν σε κάθε δείγμα. Στη συνέχεια πρόσθεσα δύο ακόμη στήλες: μία όπου υπολόγισα τη διακύμανση αυτού του δείγματος δύο καρτών διαιρώντας με n και μια άλλη όπου διαιρούσα με n - 1.

Ρίξε μια ματιά:

Στα δεξιά του πίνακα, έχω δείξει τους μέσους όρους των στηλών D και E.

Ο μέσος όρος της στήλης D, όταν διαιρείται με n, μας δίνει μια διακύμανση 1,333.

Ο μέσος όρος της στήλης Ε, όταν διαιρείται με n-1, μας δίνει μια διακύμανση 2,666.

Γνωρίζουμε ήδη από το προηγούμενο παράδειγμα μας ότι η διακύμανση του πληθυσμού είναι 2,666. Έτσι, η διαίρεση με n-1 όταν χρησιμοποιούμε δείγματα δεδομένων μας δίνει ακριβέστερες εκτιμήσεις.

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

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

Έχετε πολλές επιλογές εδώ:

• Π επιστρέφει τη διακύμανση για τα δεδομένα του πληθυσμού (χρησιμοποιώντας τη μέθοδο διαίρεση με n)
• μικρό επιστρέφει τη διακύμανση για δείγματα δεδομένων (διαιρείται με n-1)
• VAR είναι μια παλαιότερη λειτουργία που λειτουργεί με τον ίδιο ακριβώς τρόπο όπως το VAR.S
• VARA είναι το ίδιο με το VAR.S, εκτός από το ότι περιλαμβάνει κελιά κειμένου και τιμές Boolean
• ΒΑΡΠΑ είναι το ίδιο με το VAR.P, εκτός από το ότι περιλαμβάνει κελιά κειμένου και τιμές Boolean

Ας τα περάσουμε αυτά ένα-ένα.

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

Το VAR.P υπολογίζει τη διακύμανση για τα δεδομένα του πληθυσμού (χρησιμοποιώντας τη μέθοδο διαίρεσης με n). Χρησιμοποιήστε το ως εξής:

1	= VAR.P (C4: C6)

Ορίζετε μόνο ένα όρισμα στο VAR.P: το εύρος δεδομένων για το οποίο θέλετε να υπολογίσετε τη διακύμανση. Στην περίπτωσή μας εδώ, αυτές είναι οι τιμές της κάρτας στο C4: C6.

Όπως βλέπετε παραπάνω, το VAR.P επιστρέφει 2.666 για το σύνολο των τριών καρτών μας. Αυτή είναι η ίδια τιμή που υπολογίσαμε με το χέρι νωρίτερα.

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

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

Το VAR.S υπολογίζει τη διακύμανση για τα δείγματα δεδομένων (διαιρώντας με n-1). Το χρησιμοποιείς ως εξής:

1	= VAR.S (C4: C6)

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

Σε αυτήν την περίπτωση το VAR.S επιστρέφει 4. Πήραμε τον ίδιο αριθμό στο βήμα 4 όταν κάναμε τον χειροκίνητο υπολογισμό παραπάνω.

Το VAR.S αγνοεί εντελώς τα κελιά που περιέχουν κείμενο ή τιμές Boolean (TRUE/FALSE). Εάν πρέπει να τα συμπεριλάβετε, χρησιμοποιήστε το VARA.

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

Το VAR είναι απολύτως ισοδύναμο με το VAR.S: υπολογίζει τις αποκλίσεις για δείγματα δεδομένων (χρησιμοποιώντας τη μέθοδο n-1). Δείτε πώς να το χρησιμοποιήσετε:

1	= VAR (C4: C6)

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

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

Το VARA επιστρέφει επίσης τη διακύμανση των δειγμάτων δεδομένων, αλλά έχει κάποιες βασικές διαφορές σε VAR και VAR.S. Συγκεκριμένα, περιλαμβάνει τιμές Boolean και κειμένου στον υπολογισμό του:

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

Δείτε πώς το χρησιμοποιείτε:

1	= VARA (C4: C11)

Προσθέσαμε πέντε ακόμη σειρές στον πίνακα: J, Q, K, TRUE και FALSE. Η στήλη D δείχνει πώς το VARA ερμηνεύει αυτές τις τιμές.

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

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

Το VARPA υπολογίζει τη διακύμανση για τα δεδομένα του πληθυσμού. Είναι παρόμοιο με το VAR.P, εκτός από το ότι περιλαμβάνει επίσης τιμές Boolean και συμβολοσειρές κειμένου στον υπολογισμό:

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

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

1	= VARPA (C4: C12)

Προσθέσαμε πέντε ακόμη σειρές στον πίνακα: J, Q, K, TRUE και FALSE. Η στήλη D δείχνει πώς το VARPA ερμηνεύει αυτές τις τιμές.

Ως αποτέλεσμα της προσθήκης αυτής της ομάδας χαμηλότερων τιμών στα δεδομένα, η διακύμανση αυξήθηκε σε 8,984.

Λειτουργία VARIANCE στα Υπολογιστικά φύλλα Google

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