Ο Τζίμι στο Χάντσβιλ θέλει να σχεδιάσει μια καμπύλη καμπάνας που δείχνει τη μέση βαθμολογία αρκετών ατόμων. Όταν ο Jimmy έκανε την ερώτηση κατά τη διάρκεια του Σεμιναρίου Power Excel, σκέφτηκα ένα από τα πιο δημοφιλή βίντεό μου στο YouTube.
Στο Podcast 1665 - Δημιουργία καμπύλης καμπάνας στο Excel, εξηγώ ότι για να δημιουργήσετε καμπύλη καμπάνας, πρέπει να υπολογίσετε τη μέση και την τυπική απόκλιση. Στη συνέχεια δημιουργώ 30 σημεία κατά μήκος του άξονα Χ που εκτείνονται σε έναν υποθετικό πληθυσμό ανθρώπων. Σε αυτό το βίντεο, δημιούργησα αυτό που εκτείνεται από -3 τυπικές αποκλίσεις σε + 3 τυπικές αποκλίσεις γύρω από ένα μέσο όρο.
Για παράδειγμα, εάν έχετε μέσο όρο 50 και τυπική απόκλιση 10, θα δημιουργούσα έναν άξονα x που κυμαινόταν από 70 έως 130. Το ύψος κάθε σημείου υπολογίζεται χρησιμοποιώντας =NORM.DIST(x,mean,standard deviation,False).
Στην παραπάνω εικόνα, οι αριθμοί στο A10: A40 είναι ουσιαστικά "ψεύτικα σημεία δεδομένων". Δημιουργώ 31 αριθμούς για να δημιουργήσω μια ωραία ομαλή καμπύλη. Εάν θα χρησιμοποιούσα μόνο 7 σημεία δεδομένων, η καμπύλη θα μοιάζει με αυτήν:
Για το σύνολο δεδομένων του Jimmy, οι πραγματικές μέσες βαθμολογίες των υπαλλήλων του είναι ουσιαστικά σημεία κατά μήκος ενός άξονα x. Για να τα τοποθετήσετε σε καμπύλη καμπάνας, πρέπει να υπολογίσετε το ύψος ή την τιμή Y για κάθε υπάλληλο.
Ακολουθήστε αυτά τα βήματα:
-
Ταξινομήστε τα δεδομένα έτσι ώστε οι βαθμολογίες να εμφανίζονται χαμηλότερες έως υψηλότερες.
Ταξινόμηση των δεδομένων - Υπολογίστε ένα μέσο χρησιμοποιώντας τη συνάρτηση AVERAGE.
- Υπολογίστε μια τυπική απόκλιση χρησιμοποιώντας τη συνάρτηση STDEV.
-
Υπολογίστε την τιμή Y στα δεξιά των αποτελεσμάτων χρησιμοποιώντας
=NORM.DIST(L2,$H$2,$H$3,FALSE). Η τιμή Y θα δημιουργήσει ένα ύψος του σημείου κάθε ατόμου κατά μήκος της καμπύλης καμπάνας. Η συνάρτηση NORM.DIST θα φροντίσει να σχεδιάσει άτομα κοντά στο μέσο όρο σε υψηλότερη τοποθεσία από τα άτομα κοντά στην κορυφή ή στο κάτω μέρος.
Δημιουργήστε μια σειρά τιμών Y. - Επιλέξτε τα δεδομένα σας στο L1: M15
-
Ένα περίεργο σφάλμα άρχισε να εμφανίζεται πρόσφατα στο Excel, ώστε να διασφαλιστεί η επιτυχία, επιλέξτε Όλα τα γραφήματα στην καρτέλα Εισαγωγή.
Η εκκίνηση διαλόγου σας μεταφέρει σε όλους τους τύπους γραφημάτων Στο παράθυρο διαλόγου Εισαγωγή γραφήματος, κάντε κλικ στην καρτέλα Όλα τα γραφήματα. Κάντε κλικ στο XY (Scatter) στα αριστερά. Επιλέξτε το δεύτερο εικονίδιο στην κορυφή. Επιλέξτε την προεπισκόπηση στα δεξιά.
Τέσσερα κλικ για να επιλέξετε το γράφημα Η αρχική καμπύλη καμπάνας σας θα έχει την εξής μορφή:
Η καμπύλη καμπάνας
Για να καθαρίσετε την καμπύλη καμπάνας, ακολουθήστε τα εξής βήματα:
- Κάντε κλικ στον τίτλο και πατήστε το πλήκτρο Delete.
- Κάντε διπλό κλικ σε οποιονδήποτε αριθμό κατά μήκος του άξονα Υ στο κάτω μέρος του γραφήματος. Θα εμφανιστεί ο πίνακας Format Axis.
-
Πληκτρολογήστε νέες τιμές για το ελάχιστο και το μέγιστο. Το εύρος εδώ πρέπει να είναι αρκετά ευρύ για να δείξει σε όλους στο γράφημα. Χρησιμοποίησα 50 έως 90.
Αλλάξτε το ελάχιστο και το μέγιστο - Κάντε το διάγραμμα ευρύτερο σύροντας την άκρη του γραφήματος.
- Κάντε κλικ στο εικονίδιο + στα δεξιά του γραφήματος και επιλέξτε Ετικέτες δεδομένων. Μην ανησυχείτε ότι οι ετικέτες δεν έχουν νόημα ακόμη.
- Κάντε διπλό κλικ σε μια ετικέτα για να ανοίξετε τον πίνακα Μορφοποίηση ετικετών.
- Υπάρχουν τέσσερα εικονίδια στο πάνω μέρος του πίνακα. Επιλέξτε το εικονίδιο που δείχνει ένα γράφημα στηλών.
- Κάντε κλικ στο βέλος δίπλα στις Επιλογές ετικέτας για να επεκτείνετε αυτό το τμήμα του πίνακα.
- Επιλέξτε τιμή από κελιά. Θα εμφανιστεί ένα πλαίσιο διαλόγου ζητώντας τη θέση των ετικετών. Επιλέξτε τα ονόματα στο K2: K15.
-
Ακόμα στον πίνακα Format Data Label, αποεπιλέξτε τις τιμές Y Είναι σημαντικό να ολοκληρώσετε το Βήμα 15 πριν κάνετε το Βήμα 16, διαφορετικά θα αφαιρέσετε ακούσια τις ετικέτες.
Λάβετε τις ετικέτες από τα κελιά που περιέχουν ονόματα.
Σημείωση
Η δυνατότητα λήψης ετικετών από κελιά προστέθηκε στο Excel 2013. Εάν χρησιμοποιείτε το Excel 2010 ή παλαιότερη έκδοση, πραγματοποιήστε λήψη του πρόσθετου XY Chart Labeler από τον Rob Bovey. (Το Google για να το βρει).
Σε αυτό το σημείο, δείτε αν έχετε ετικέτες γραφήματος που συντρίβονται μεταξύ τους. Για να τα διορθώσετε, ακολουθήστε προσεκτικά αυτά τα βήματα.
- Κάντε ένα κλικ σε μια ετικέτα γραφήματος. Αυτό επιλέγει όλες τις ετικέτες.
- Κάντε ένα κλικ σε μία από τις ετικέτες που βρίσκεται πάνω από μια άλλη ετικέτα για να επιλέξετε μόνο αυτήν την ετικέτα.
- Τοποθετήστε τον δείκτη του ποντικιού πάνω από διάφορα μέρη της ετικέτας μέχρι να δείτε ένα βέλος με τέσσερα κεφάλια Κάντε κλικ και σύρετε την ετικέτα σε νέα θέση.
-
Μόλις επιλέξετε μόνο μία ετικέτα, μπορείτε να κάνετε ένα μόνο κλικ σε οποιαδήποτε άλλη ετικέτα για να επιλέξετε αυτήν την ετικέτα. Επαναλάβετε για τυχόν άλλες ετικέτες που πρέπει να μετακινηθούν.
Το τελικό γράφημα
Δες το βίντεο
Μεταγραφή βίντεο
Μάθετε το Excel από το Podcast, επεισόδιο 2217: Τοποθετήστε τους ανθρώπους σε καμπύλη καμπάνας.
Γεια σας, καλώς ήλθατε πίσω στο netcast, είμαι ο Bill Jelen. Η σημερινή ερώτηση, από τον Τζίμι στο σεμινάριό μου στο Χάντσβιλ της Αλαμπάμα. Ο Τζίμι έχει δεδομένα, θέλει να συνοψίσει αυτά τα δεδομένα και μετά να σχεδιάσει τα αποτελέσματα σε καμπύλη καμπάνας.
Καλώς? Τώρα, ένα από τα πιο δημοφιλή βίντεο μου στο YouTube είναι αυτό: αριθμός 1663, Δημιουργία καμπύλης καμπάνας στο Excel. Και δεδομένου του μέσου όρου και της τυπικής απόκλισης, κατάλαβα το χαμηλό, που είναι 3 φορές την τυπική απόκλιση λιγότερο από το μέσο όρο, και το υψηλό - 3 φορές την τυπική απόκλιση περισσότερο από το μέσο όρο - όπου το κενό είναι - και μια σειρά τιμών X εδώ και για να υπολογίσετε το ύψος, χρησιμοποιήστε αυτήν τη συνάρτηση: = NORM.DIST της τιμής X, της μέσης και της τυπικής απόκλισης, κόμμα false (= NORM.DIST (A10, $ B $ 2, $ B $ 3, FALSE)).
Και αν το σκεφτείτε, αυτό το βίντεο χρησιμοποιεί πραγματικά μια σειρά από ψεύτικες τιμές Χ εδώ για να αποκτήσετε μια ωραία καμπύλη. Και θα χρησιμοποιήσουμε την ίδια ιδέα εδώ, αλλά αντί για ψεύτικες τιμές Χ, στην πραγματικότητα θα έχουμε τους ανθρώπους κάτω εδώ και τότε το ύψος θα είναι αυτός ο ίδιος ακριβώς τύπος. Καλώς.
Τώρα, ο Τζίμι ήθελε να δημιουργήσει έναν συγκεντρωτικό πίνακα. Έτσι θα εισαγάγουμε, PivotTable, θα το βάλουμε εδώ σε αυτό το φύλλο, κάντε κλικ στο OK Οι άνθρωποι κάτω από την αριστερή πλευρά και στη συνέχεια ο μέσος όρος βαθμολογίας τους. Εντάξει, οπότε ξεκινά με το άθροισμα του σκορ, θα κάνω διπλό κλικ εκεί και θα το αλλάξω σε μέσο όρο. Εξαιρετική. Τώρα, στο κάτω μέρος, δεν θέλω ένα μεγάλο σύνολο - κάντε δεξί κλικ και Κατάργηση Grand Total - και θέλουμε να τακτοποιήσουμε αυτά τα άτομα από ψηλά σε χαμηλά και αυτό είναι εύκολο να γίνει σε έναν συγκεντρωτικό πίνακα. Δεδομένα, Α έως Ω - εξαιρετικό. Καλώς. Τώρα, θα κάνουμε το ίδιο ακριβώς πράγμα που κάναμε πίσω στο Podcast 1663, και αυτό υπολογίζει μια μέση τιμή και μια τυπική απόκλιση. Έτσι, ο μέσος όρος είναι ένας μέσος όρος αυτών των βαθμολογιών και στη συνέχεια ισούται με την τυπική απόκλιση αυτών των βαθμολογιών. Καλώς. Τώρα που το ξέρω, μπορώ να δημιουργήσω την τιμή y.
Εντάξει, οπότε μερικά πράγματα πρόκειται να κάνουμε εδώ. Πρώτα απ 'όλα, δεν μπορείτε να δημιουργήσετε έναν συγκεντρωτικό πίνακα - ένα διάγραμμα διασποράς - από έναν συγκεντρωτικό πίνακα. Θα αντιγράψω λοιπόν όλα αυτά τα δεδομένα και θα το κάνω με = D2. Σημειώστε ότι προσέχω να μην χρησιμοποιήσω το ποντίκι ή τα πλήκτρα βέλους για να το δείξω. Έχουμε λοιπόν τις αξίες μας εδώ. Αυτές θα γίνουν τιμές X, η τιμή Y πρόκειται να γίνει = NORM.DIST, εδώ είναι η τιμή x, κόμμα, για τον μέσο όρο, αυτός ο αριθμός, θα πατήσω το F4 για να το κλειδώσω. για την τυπική απόκλιση είναι αυτός ο αριθμός, πάλι, πατήστε F4 για να το κλειδώσετε και αθροιστικά FALSE. (= NORM.DIST (K2, $ H $ 2, $ H $ 3, FALSE)) Και θα κάνουμε διπλό κλικ για να το αντιγράψουμε. Καλώς. Και τότε, μην επιλέξετε τις ετικέτες,απλώς επιλέξτε το XY και θα εισαγάγουμε ένα διάγραμμα διασποράς με γραμμές - μπορείτε είτε να επιλέξετε αυτό με καμπύλες γραμμές ή λίγες ευθείες. Εδώ, θα πάω με καμπύλες γραμμές σαν αυτό. Και τώρα όλοι οι Άνθρωποί μας έχουν τοποθετηθεί σε καμπύλη καμπάνας.
Καλώς. Τώρα, μερικά πράγματα - κάποια πράγματα τύπου μορφοποίησης - πρόκειται να κάνουμε εδώ: Πρώτα απ 'όλα, κάντε διπλό κλικ κάτω εδώ κατά μήκος της κλίμακας και φαίνεται ότι ο χαμηλότερος αριθμός μας είναι πιθανώς κάπου περίπου 50-- ορίστε ένα ελάχιστο 50 - και ο μεγαλύτερος αριθμός μας - ο μεγαλύτερος αριθμός μας - είναι 88-- οπότε θα ορίσω ένα μέγιστο 90. Εντάξει. Και τώρα, πρέπει να επισημάνουμε αυτά τα σημεία. Εάν βρίσκεστε στο Excel 2013 ή νεότερο, αυτό είναι εύκολο να το κάνετε. αλλά αν βρίσκεστε σε παλαιότερη έκδοση του Excel, θα πρέπει να επιστρέψετε και να χρησιμοποιήσετε το πρόσθετο Rob Bovey Chart Labeler για να έχετε αυτές τις ετικέτες σημείων από κάποιο μέρος που δεν υπάρχει στο γράφημα. Εντάξει, οπότε ξεκινάμε εδώ. Θα προσθέσουμε ετικέτες δεδομένων και προσθέτει αριθμούς και φαίνονται απαίσια. Θα έρθω εδώ και θα πω ότι θέλω Περισσότερες επιλογές, Επιλογές ετικετών,και θέλω να λάβω την τιμή από κελιά - τιμή από κελιά. Καλώς? Έτσι, το εύρος των κελιών είναι εκεί, κάντε κλικ στο OK. Πολύ σημαντικό να χρησιμοποιήσω την τιμή από κελιά προτού καταργήσω την τιμή Υ. Αρχίζει να φαίνεται καλό. Θα το ξεφορτωθώ. Τώρα, ολόκληρο το κλειδί εδώ - επειδή έχετε μερικούς ανθρώπους που αντικαθιστούν ο ένας τον άλλον - είναι να δοκιμάσετε και να κάνετε το γράφημα όσο το δυνατόν μεγαλύτερο. Δεν χρειαζόμαστε μια κατεύθυνση προς τα πάνω. Γιατί; Απλώς διαγράψτε το. Και εξακολουθώ να βλέπω, όπως η Kelly και ο Lou και ο Andy και ο Flo βρίσκονται σχεδόν στο ίδιο μέρος. Τζάρεντ και-- Εντάξει. Τώρα, αυτό θα είναι απογοητευτικό - αυτά που αλληλεπικαλύπτονται. Αλλά όταν κάνουμε κλικ σε μια ετικέτα, επιλέξαμε όλες τις ετικέτες και, στη συνέχεια, κάντε ξανά κλικ σε μια ετικέτα και επιλέγουμε μόνο μία ετικέτα. Καλώς? Ωστε τώρα. πολύ προσεκτικά. δοκιμάστε να κάνετε κλικ στο Andy και απλά σύρετε τον Andy προς τα αριστερά.Φαίνεται ότι ο Jared και ο Ike είναι μαζί, οπότε τώρα που βρίσκομαι σε λειτουργία επιλογής μεμονωμένης ετικέτας, είναι πιο εύκολο. Και έπειτα η Κέλι και ο Λου, σύρεέ τους έτσι. Ίσως υπάρχει ένα καλύτερο μέρος που να μην τρέχει υπερβολικά ο Lou, ή ακόμα και, εδώ, μπορώ να το σύρω και από τις δύο πλευρές. Εντάξει, λοιπόν, τι έχουμε; Ξεκινήσαμε με μια δέσμη δεδομένων, δημιουργήσαμε έναν συγκεντρωτικό πίνακα, καταλάβαμε τη μέση και την τυπική απόκλιση, η οποία μας επιτρέπει μόνο να υπολογίσουμε το ύψος - τη θέση Υ για καθεμία από αυτές τις βαθμολογίες και το ύψος αυτών, ελπίζουμε, θα βάλουμε τους ανθρώπους σε μια ωραία καμπύλη καμπάνας σε σχήμα παραβολής, έτσι.Εντάξει, λοιπόν, τι έχουμε; Ξεκινήσαμε με μια δέσμη δεδομένων, δημιουργήσαμε έναν συγκεντρωτικό πίνακα, καταλάβαμε τη μέση και την τυπική απόκλιση, η οποία μας επιτρέπει μόνο να υπολογίσουμε το ύψος - τη θέση Υ για καθεμία από αυτές τις βαθμολογίες και το ύψος αυτών, ελπίζουμε, θα βάλουμε τους ανθρώπους σε μια ωραία καμπύλη καμπάνας σε σχήμα παραβολής, έτσι.Εντάξει, λοιπόν, τι έχουμε; Ξεκινήσαμε με μια δέσμη δεδομένων, δημιουργήσαμε έναν συγκεντρωτικό πίνακα, καταλάβαμε τη μέση και την τυπική απόκλιση, η οποία μας επιτρέπει μόνο να υπολογίσουμε το ύψος - τη θέση Υ για καθεμία από αυτές τις βαθμολογίες και το ύψος αυτών, ελπίζουμε, θα βάλουμε τους ανθρώπους σε μια ωραία καμπύλη καμπάνας σε σχήμα παραβολής, έτσι.
Λατρεύω αυτήν την ερώτηση από τον Jimmy, αυτή η ερώτηση δεν βρίσκεται σε αυτό το βιβλίο, αλλά θα είναι την επόμενη φορά που θα γράψω αυτό το βιβλίο. Θα πρέπει να προσθέσω αυτό - είναι ένα ωραίο αίτημα και ένα ωραίο μικρό κόλπο. Οι καμπύλες καμπάνας είναι πολύ δημοφιλείς στο Excel.
Αλλά δείτε το βιβλίο μου, LIVe, The 54 Greatest Excel Tips of All Time.
Εντάξει, ολοκληρώστε αυτό το επεισόδιο: Ο Τζίμι από το Χάντσβιλ, θέλει να οργανώσει τους ανθρώπους σε καμπύλη. Χρησιμοποιούμε λοιπόν έναν συγκεντρωτικό πίνακα για να υπολογίσουμε τη μέση βαθμολογία, ταξινομούμε τους συγκεντρωτικούς πίνακες με τις βαθμολογίες - διατεταγμένες από ψηλά σε χαμηλά - απαλλαγούμε από το μεγάλο σύνολο στο κάτω μέρος - αυτές ουσιαστικά πρόκειται να είναι οι τιμές X - και, στη συνέχεια, στο πλάι, υπολογίστε τη μέση και την τυπική απόκλιση αυτών των βαθμολογιών και χρησιμοποιήστε τύπους για να αντιγράψετε τα δεδομένα από τον συγκεντρωτικό πίνακα σε ένα νέο εύρος, επειδή δεν μπορείτε να έχετε ένα γράφημα XY που τέμνεται με έναν συγκεντρωτικό πίνακα. Υπολογίστε μια τιμή y για κάθε άτομο με = NORM.DIST της x-τιμής, τη μέση τιμή, την τυπική απόκλιση, το κόμμα FALSE; δημιουργήστε ένα διάγραμμα διασποράς XY με ομαλές γραμμές - εάν είστε Excel 2010 ή νωρίτερα, θα χρησιμοποιήσετε το πρόσθετο Chart Labeler του Ron Bovey. Θα σας ζητήσω το Google γιατί,σε περίπτωση που ο Rob αλλάξει τη διεύθυνση URL του, δεν θέλω το λάθος URL εδώ. Στο Excel 2013, είχαμε τις ετικέτες δεδομένων, από τα κελιά, να ορίσω τα ονόματα και, στη συνέχεια, ορισμένες προσαρμογές - αλλάξτε την κλίμακα στο κάτω μέρος, τα αλλάζω και το Max και, στη συνέχεια, μετακινώ τις ετικέτες που υπερέχουν μεταξύ τους.
Για να κατεβάσετε το βιβλίο εργασίας από το σημερινό βίντεο, χρησιμοποιήστε τη διεύθυνση URL στην περιγραφή του YouTube. Θέλω να ευχαριστήσω τον Jimmy για αυτήν την φοβερή ερώτηση στο Huntsville και θέλω να σας ευχαριστήσω που σταματήσατε. Θα σας δω την επόμενη φορά για άλλο netcast από.
Λήψη αρχείου Excel
Για να κατεβάσετε το αρχείο excel: place-people-on-bell-curve.xlsx
Χάρη στον Jimmy στο Huntsville για τη σημερινή ερώτηση!
Excel Thought Of the Day
Ζήτησα από τους φίλους μου στο Excel Master τις συμβουλές τους σχετικά με το Excel. Η σημερινή σκέψη να σκεφτούμε:
"Εάν έχετε βάλει το Excel σε μη αυτόματη λειτουργία επανυπολογισμού τον προηγούμενο μήνα, ήρθε η ώρα για περιστροφικό άξονα (δεν θα χρειαστείτε ποτέ ξανά χειροκίνητη λειτουργία)"
Ρομπ Κολί
