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

Αυτό το σεμινάριο θα δείξει πώς να δημιουργήσετε μια κανονική καμπύλη καμπάνας διανομής σε όλες τις εκδόσεις του Excel: 2007, 2010, 2013, 2016 και 2022.

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

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

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

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

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

Για να σχεδιάσετε μια καμπύλη Gauss, πρέπει να γνωρίζετε δύο πράγματα:

• Το νόημα (επίσης γνωστή ως τυπική μέτρηση). Αυτό καθορίζει το κέντρο της καμπύλης-το οποίο, με τη σειρά του, χαρακτηρίζει τη θέση της καμπύλης.
• Η τυπική απόκλιση (SD) των μετρήσεων. Αυτό καθορίζει την εξάπλωση των δεδομένων σας στην κανονική διανομή-ή σε απλά Αγγλικά, πόσο μεγάλη πρέπει να είναι η καμπύλη. Για παράδειγμα, στην καμπύλη καμπάνας που φαίνεται παραπάνω, μία τυπική απόκλιση του μέσου όρου αντιπροσωπεύει το εύρος μεταξύ των βαθμών των εξετάσεων 53 και 85.

Όσο χαμηλότερο είναι το SD, τόσο μεγαλύτερη είναι η καμπύλη και τόσο λιγότερο τα δεδομένα σας θα εξαπλωθούν και αντίστροφα.

Αξίζει να αναφερθεί ο κανόνας 68-95-99.7 που μπορεί να εφαρμοστεί σε οποιαδήποτε κανονική καμπύλη διανομής, πράγμα που σημαίνει ότι περίπου το 68% των δεδομένων σας θα τοποθετηθούν εντός ενός SD μακριά από το μέσο όρο, 95% εντός δύο SD και 99,7% εντός τρία SD.

Τώρα που γνωρίζετε τα βασικά, ας περάσουμε από τη θεωρία στην πράξη.

Ξεκινώντας

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

Βήμα #1: Βρείτε τη μέση τιμή.

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

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

Πληκτρολογήστε τον ακόλουθο τύπο σε οποιοδήποτε κενό κελί (F1 σε αυτό το παράδειγμα) δίπλα στα πραγματικά δεδομένα σας (στήλες Α και σι) για τον υπολογισμό του μέσου όρου των βαθμολογιών των εξετάσεων στο σύνολο δεδομένων:

1	= ΜΕΣΗ (Β2: Β201)

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

1	= ΓΥΡΟΣ (ΜΕΣΟΣ (Μ2: Β201), 0)

Βήμα #2: Βρείτε την τυπική απόκλιση.

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

1	= STDEV.P (B2: B201)

Και πάλι, ο τύπος επιλέγει όλες τις τιμές από το καθορισμένο εύρος κελιών (Β2: Β201) και υπολογίζει την τυπική απόκλιση-απλά μην ξεχάσετε να στρογγυλοποιήσετε επίσης την έξοδο.

1	= ROUND (STDEV.P (B2: B201), 0)

Βήμα #3: Ρυθμίστε τις τιμές του άξονα x για την καμπύλη.

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

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

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

Ας ξεκινήσουμε μια καταμέτρηση στο ένα (καθώς δεν υπάρχει περίπτωση ένας μαθητής να πάρει αρνητική βαθμολογία εξετάσεων) και να φτάσουμε μέχρι το 150-δεν έχει σημασία αν είναι 150 ή 1500-για να φτιάξουμε ένα άλλο βοηθητικό τραπέζι.

1. Επιλέξτε οποιοδήποτε κενό κελί κάτω από τα δεδομένα του γραφήματος (όπως π Ε4) και τον τύπο “1,” την τιμή που ορίζει το πρώτο διάστημα.
2. Πλοηγηθείτε στο Σπίτι αυτί.
3. Στο Επεξεργασία ομάδα, επιλέξτε "Γέμισμα.”
4. Υπό "Σειρά σε,"Επιλέξτε"Στήλη.”
5. Για "Τιμή βημάτων,”Τύπου “1.” Αυτή η τιμή καθορίζει τις αυξήσεις που θα προστεθούν αυτόματα έως ότου το Excel φτάσει στο τελευταίο διάστημα.
6. Για "Διακοπή τιμής,"Τύπος"150,” την τιμή που αντιστοιχεί στο τελευταίο διάστημα και κάντε κλικ στο "ΕΝΤΑΞΕΙ.”

Ως εκ θαύματος, 149 κύτταρα στη στήλη Ε (Ε5: Ε153) έχουν συμπληρωθεί με τις τιμές που κυμαίνονται από 2 έως 150.

ΣΗΜΕΙΩΣΗ: Μην αποκρύπτετε τα αρχικά κελιά δεδομένων όπως φαίνεται στα στιγμιότυπα οθόνηςΤο Διαφορετικά, η τεχνική δεν θα λειτουργήσει.

Βήμα #4: Υπολογίστε τις κανονικές τιμές κατανομής για κάθε τιμή άξονα x.

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

Πληκτρολογήστε τον ακόλουθο τύπο στο κελί προς τα δεξιά (F4) του πρώτου σας διαστήματος (Ε4):

1	= NORM.DIST (E4, $ F $ 1, $ F $ 2, FALSE)

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

1	= NORM.DIST ([το πρώτο διάστημα], [ο μέσος όρος (απόλυτη αναφορά)], [η τυπική απόκλιση (απόλυτη αναφορά), FALSE)

Κλειδώνετε τις μέσες τιμές και τις τιμές SD, ώστε να μπορείτε να εκτελέσετε αβίαστα τον τύπο για τα υπόλοιπα διαστήματα (Ε5: Ε153).

Τώρα, κάντε διπλό κλικ στη λαβή πλήρωσης για να αντιγράψετε τον τύπο στα υπόλοιπα κελιά (F5: F153).

Βήμα #5: Δημιουργήστε ένα διάγραμμα διασποράς με ομαλές γραμμές.

Τέλος, ήρθε η ώρα να χτιστεί η καμπύλη καμπάνας:

1. Επιλέξτε οποιαδήποτε τιμή στον βοηθητικό πίνακα που περιέχει τις τιμές του άξονα x και y (Ε4: F153).
2. μεταβείτε στο Εισάγετε αυτί.
3. Κάντε κλικ στο «Εισαγωγή Scatter (X, Y) ή Bubble ChartΚουμπί ”.
4. Επιλέξτε "Σκόρπισε με ομαλές γραμμές. »

Βήμα #6: Ρυθμίστε τον πίνακα ετικετών.

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

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

Για αυτό, δημιουργήστε έναν ακόμη πίνακα βοηθών ως εξής:

Αρχικά, αντιγράψτε τη Μέση τιμή (F1) δίπλα στο αντίστοιχο κελί στη στήλη X-Value (I5).

Στη συνέχεια, υπολογίστε τις τυπικές τιμές απόκλισης κάτω από το μέσο εισάγοντας αυτόν τον απλό τύπο κελί Ι4:

1	= I5- $ F $ 2

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

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

1	= I5+$ F $ 2

Με τον ίδιο τρόπο, εκτελέστε τον τύπο για τα άλλα δύο κελιά (I7: I8).

Τέλος, συμπληρώστε τις τιμές της ετικέτας του άξονα y (J2: J8) με μηδενικά όπως θέλετε οι δείκτες δεδομένων να τοποθετούνται στον οριζόντιο άξονα.

Βήμα #7: Εισαγάγετε τα δεδομένα της ετικέτας στο γράφημα.

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

Στο παράθυρο διαλόγου που εμφανίζεται, επιλέξτε "Προσθήκη.”

Επισημάνετε τις αντίστοιχες περιοχές κελιών από τον πίνακα βοηθού-I2: I8 Για "Σειρά Χ τιμές" και J2: J8 Για "Τιμές σειράς Υ "-και κάντε κλικ στο "ΕΝΤΑΞΕΙ.”

Βήμα #8: Αλλάξτε τον τύπο γραφήματος της σειράς ετικετών.

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

Στη συνέχεια, σχεδιάστε ένα σύνθετο γράφημα:

1. Πλοηγηθείτε στο Combo αυτί.
2. Για Σειρά "Series2", αλλαγή "Τύπος γραφήματος" προς το "Σκορπίζω.”
   • Σημείωση: Βεβαιωθείτε ότι "Σειρά 1"Παραμένει ως"Scatter with Smooth Lines.. " Μερικές φορές το Excel θα το αλλάξει όταν κάνετε ένα Combo Βεβαιωθείτε επίσης "Σειρά 1”Δεν πιέζεται στον δευτερεύοντα άξονα-το πλαίσιο ελέγχου δίπλα στον τύπο γραφήματος δεν πρέπει να επισημαίνεται.
3. Κάντε κλικ "Εντάξει.”

Βήμα #9: Τροποποιήστε την κλίμακα του οριζόντιου άξονα.

Κεντράρετε το γράφημα στην καμπύλη καμπάνας προσαρμόζοντας την κλίμακα του οριζόντιου άξονα. Κάντε δεξί κλικ στον οριζόντιο άξονα και επιλέξτε "Μορφοποίηση άξονα"Από το μενού.

Μόλις εμφανιστεί το παράθυρο εργασιών, κάντε τα εξής:

• μεταβείτε στο Επιλογές Άξονα αυτί.
• Ορίστε το Ελάχιστα όρια αξία σε "15.”
• Ορίστε το Μέγιστα όρια αξία σε "125.”

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

Βήμα #10: Εισαγάγετε και τοποθετήστε τις προσαρμοσμένες ετικέτες δεδομένων.

Καθώς γυαλίζετε το γράφημα, φροντίστε να προσθέσετε τις προσαρμοσμένες ετικέτες δεδομένων. Πρώτον, κάντε δεξί κλικ σε οποιαδήποτε τελεία που αντιπροσωπεύει Σειρά "Series2" και επιλέξτε "Προσθήκη ετικετών δεδομένων.”

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

1. Κάντε δεξί κλικ σε οποιοδήποτε Σειρά "Series2" ετικέτα δεδομένων.
2. Επιλέξτε "Μορφοποίηση ετικετών δεδομένων.”
3. Στο παράθυρο εργασιών, μεταβείτε στο Επιλογές ετικέτας αυτί.
4. Ελεγξε το "Χ τιμή”Κουτί.
5. Αποεπιλέξτε το "Τιμή Y”Κουτί.
6. Υπό "Θέση ετικέτας,"Επιλέξτε"Πάνω από.”

Επίσης, μπορείτε τώρα να καταργήσετε τις γραμμές πλέγματος (κάντε δεξί κλικ σε αυτές> Διαγραφή).

Βήμα #11: Ξαναχρωματίστε τους δείκτες δεδομένων (προαιρετικά).

Τέλος, ξαναχρωματίστε τις τελείες για να τις βοηθήσετε να ταιριάζουν στο στυλ του γραφήματός σας.

1. Κάντε δεξί κλικ σε οποιοδήποτε Σειρά "Series2" ετικέτα δεδομένων.
2. Κάντε κλικ στο «ΓέμισμαΚουμπί ”.
3. Επιλέξτε το χρώμα σας από την παλέτα που εμφανίζεται.

Επίσης, αφαιρέστε τα περιγράμματα γύρω από τις τελείες:

1. Κάντε ξανά δεξί κλικ στον ίδιο δείκτη δεδομένων και επιλέξτε "Περίγραμμα.”
2. Επιλέξτε "Χωρίς περίγραμμα.”

Βήμα #12: Προσθέστε κάθετες γραμμές (προαιρετικά).

Ως τελική προσαρμογή, μπορείτε να προσθέσετε κάθετες γραμμές στο γράφημα για να δώσετε έμφαση στις τιμές SD.

• Επιλέξτε το διάγραμμα γραφήματος (με αυτόν τον τρόπο, οι γραμμές θα εισαχθούν απευθείας στο γράφημα).
• μεταβείτε στο Εισάγετε αυτί.
• Κάντε κλικ στο «ΣχήματαΚουμπί ”.
• Επιλέξτε "Γραμμή.”

Κρατήστε πατημένο το "ΜΕΤΑΤΟΠΙΣΗ" πατώντας το ποντίκι για να τραβήξετε τέλεια κάθετες γραμμές από κάθε τελεία στο σημείο όπου κάθε γραμμή συναντά την καμπύλη καμπάνας.

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

Και έτσι το κάνεις. Τώρα μπορείτε να επιλέξετε οποιοδήποτε σύνολο δεδομένων και να δημιουργήσετε μια κανονική καμπύλη καμπάνας διανομής ακολουθώντας αυτά τα απλά βήματα!