Όταν εργάζεστε με δεδομένα στο Excel, συχνά θα αντιμετωπίζετε προβλήματα χειρισμού εξόδων στο σύνολο δεδομένων σας.
Η απόκλιση είναι αρκετά συχνή σε όλα τα είδη δεδομένων και είναι σημαντικό να εντοπίσετε και να αντιμετωπίσετε αυτές τις ακραίες τιμές για να βεβαιωθείτε ότι η ανάλυσή σας είναι σωστή και πιο ουσιαστική.
Σε αυτό το σεμινάριο, θα σας δείξω πώς να βρείτε απομακρυσμένες στο Excel, και μερικές από τις τεχνικές που έχω χρησιμοποιήσει στη δουλειά μου για να χειριστώ αυτές τις ακραίες τιμές.
Τι είναι τα Outliers και γιατί είναι σημαντικό να τα βρίσκουμε;
Ένα ακραίο σημείο είναι ένα σημείο δεδομένων που υπερβαίνει κατά πολύ τα άλλα σημεία δεδομένων στο σύνολο δεδομένων. Όταν έχετε ένα υπερβολικό στα δεδομένα, μπορεί να στραβώσει τα δεδομένα σας, γεγονός που μπορεί να οδηγήσει σε λανθασμένα συμπεράσματα.
Επιτρέψτε μου να σας δώσω ένα απλό παράδειγμα.
Ας υποθέσουμε ότι 30 άτομα ταξιδεύουν με λεωφορείο από τον προορισμό Α στον προορισμό Β. Όλοι οι άνθρωποι ανήκουν σε παρόμοια ομάδα βάρους και ομάδα εισοδήματος. Για τους σκοπούς αυτού του σεμιναρίου, ας θεωρήσουμε ότι το μέσο βάρος είναι 220 λίβρες και το μέσο ετήσιο εισόδημα είναι 70.000 δολάρια.
Τώρα κάπου στη μέση της διαδρομής μας, το λεωφορείο σταματάει και μπαίνει ο Μπιλ Γκέιτς.
Τώρα, τι πιστεύετε ότι θα έκανε αυτό στο μέσο βάρος και το μέσο εισόδημα των ατόμων στο λεωφορείο.
Ενώ το μέσο βάρος δεν είναι πιθανό να αλλάξει πολύ, το μέσο εισόδημα των ατόμων στο λεωφορείο πρόκειται να εκτοξευθεί σε μεγάλο βαθμό.
Αυτό συμβαίνει επειδή το εισόδημα του Μπιλ Γκέιτς είναι ένα υπερβολικό στην ομάδα μας και αυτό μας δίνει μια λανθασμένη ερμηνεία των δεδομένων. Το μέσο εισόδημα για κάθε άτομο στο λεωφορείο θα είναι μερικά δισεκατομμύρια δολάρια, το οποίο υπερβαίνει κατά πολύ την πραγματική αξία.
Όταν εργάζεστε με πραγματικά σύνολα δεδομένων στο Excel, μπορείτε να έχετε ακραίες τιμές προς οποιαδήποτε κατεύθυνση (δηλαδή, ένα θετικό ή ένα αρνητικό).
Και για να βεβαιωθείτε ότι η ανάλυσή σας είναι σωστή, πρέπει με κάποιο τρόπο να εντοπίσετε αυτές τις ακραίες τιμές και στη συνέχεια να αποφασίσετε πώς να τις αντιμετωπίσετε καλύτερα.
Τώρα ας δούμε μερικούς τρόπους για να βρείτε απομακρυσμένα στο Excel.
Βρείτε Outliers ταξινομώντας τα δεδομένα
Με μικρά σύνολα δεδομένων, ένας γρήγορος τρόπος για να εντοπίσετε τις υπερβολικές τιμές είναι να ταξινομήσετε απλά τα δεδομένα και να περάσετε χειροκίνητα από μερικές από τις τιμές που βρίσκονται στην κορυφή αυτών των ταξινομημένων δεδομένων.
Και δεδομένου ότι θα μπορούσαν να υπάρχουν υπερβολικές τιμές και προς τις δύο κατευθύνσεις, βεβαιωθείτε ότι ταξινομείτε πρώτα τα δεδομένα κατά αύξουσα σειρά και στη συνέχεια σε φθίνουσα σειρά και, στη συνέχεια, περνάτε από τις κορυφαίες τιμές.
Επιτρέψτε μου να σας δείξω ένα παράδειγμα.
Παρακάτω έχω ένα σύνολο δεδομένων όπου έχω διάρκεια κλήσεων (σε δευτερόλεπτα) για 15 κλήσεις εξυπηρέτησης πελατών.
Παρακάτω είναι τα βήματα για να ταξινομήσετε αυτά τα δεδομένα, έτσι ώστε να μπορούμε να προσδιορίσουμε τις υπερβολικές τιμές στο σύνολο δεδομένων:
- Επιλέξτε την κεφαλίδα στήλης της στήλης που θέλετε να ταξινομήσετε (κελί Β1 σε αυτό το παράδειγμα)
- Κάντε κλικ στην καρτέλα Αρχική σελίδα
- Στην ομάδα Επεξεργασία, κάντε κλικ στο εικονίδιο Ταξινόμηση & Φίλτρο.
- Κάντε κλικ στην Προσαρμοσμένη Ταξινόμηση
- Στο παράθυρο διαλόγου Ταξινόμηση, επιλέξτε «Διάρκεια» στο αναπτυσσόμενο μενού Ταξινόμηση και «Μεγαλύτερο σε μικρότερο» στο αναπτυσσόμενο μενού Παραγγελία
- Κάντε κλικ στο κουμπί Ok
Τα παραπάνω βήματα θα ταξινομήσουν τη στήλη διάρκειας κλήσης με τις υψηλότερες τιμές στο επάνω μέρος. Τώρα μπορείτε να σαρώσετε με μη αυτόματο τρόπο τα δεδομένα και να δείτε αν υπάρχουν υπερβολικές τιμές.
Στο παράδειγμά μας, μπορώ να δω ότι οι δύο πρώτες τιμές είναι πολύ υψηλότερες από τις υπόλοιπες τιμές (και οι δύο κάτω είναι πολύ χαμηλότερες).
Σημείωση: Αυτή η μέθοδος λειτουργεί με μικρά σύνολα δεδομένων όπου μπορείτε να σαρώσετε τα δεδομένα με μη αυτόματο τρόπο. Δεν είναι επιστημονική μέθοδος αλλά λειτουργεί καλά
Εύρεση απομακρυσμένων με τη χρήση συναρτήσεων τεταρτημορίου
Τώρα ας μιλήσουμε για μια πιο επιστημονική λύση που μπορεί να σας βοηθήσει να προσδιορίσετε αν υπάρχουν υπερβολικές τιμές ή όχι.
Στις στατιστικές, ένα τέταρτο είναι το ένα τέταρτο του συνόλου δεδομένων. Για παράδειγμα, εάν έχετε 12 σημεία δεδομένων, τότε το πρώτο τεταρτημόριο θα είναι τα τρία κάτω σημεία δεδομένων, το δεύτερο τέταρτο θα είναι τα επόμενα τρία σημεία δεδομένων κ.ο.κ.
Παρακάτω είναι το σύνολο δεδομένων όπου θέλω να βρω τις υπερβολικές τιμές. Για να γίνει αυτό, θα πρέπει να υπολογίσω το 1ο και το 3ο τεταρτημόριο και στη συνέχεια χρησιμοποιώντας αυτό να υπολογίσω το ανώτερο και το κάτω όριο.
Παρακάτω είναι ο τύπος για τον υπολογισμό του πρώτου τεταρτημορίου στο κελί Ε2:
= QUARTILE.INC ($ B $ 2: $ B $ 15,1)
και εδώ είναι αυτό που υπολογίζει το τρίτο τεταρτημόριο στο κελί Ε3:
= QUARTILE.INC ($ B $ 2: $ B $ 15,3)
Τώρα, μπορώ να χρησιμοποιήσω τους δύο παραπάνω υπολογισμούς για να αποκτήσω το εύρος των τεταρτημορίων (το οποίο είναι το 50% των δεδομένων μας εντός του 1ου και του 3ου τεταρτημορίου)
= F3-F2
Τώρα θα χρησιμοποιήσουμε το διατμηματικό εύρος για να βρούμε το κατώτερο και το ανώτερο όριο που θα περιείχε τα περισσότερα από τα δεδομένα μας.
Οτιδήποτε είναι εκτός αυτών των κατώτερων και ανώτερων ορίων θα θεωρείται τότε εξωφρενικό.
Παρακάτω είναι ο τύπος για τον υπολογισμό του κατώτερου ορίου:
= Τεταρτημόριο 1 - 1.5*(Inter Quartile Range)
που στο παράδειγμά μας γίνεται:
= F2-1.5*F4
Και ο τύπος για τον υπολογισμό του άνω ορίου είναι:
= Τεταρτημόριο 3 + 1,5*(Inter Quartile Range)
που στο παράδειγμά μας γίνεται:
= F3+1,5*F4
Τώρα που έχουμε το ανώτερο και το κάτω όριο στο σύνολο δεδομένων μας, μπορούμε να επιστρέψουμε στα αρχικά δεδομένα και να εντοπίσουμε γρήγορα αυτές τις τιμές που δεν βρίσκονται σε αυτό το εύρος.
Ένας γρήγορος τρόπος για να γίνει αυτό θα ήταν να ελέγξετε κάθε τιμή και να επιστρέψετε ένα ΣΩΣΤΟ ή ΛΑΘΟΣ σε μια νέα στήλη.
Έχω χρησιμοποιήσει τον παρακάτω τύπο OR για να λάβω την ΑΛΗΘΕΙΑ για αυτές τις τιμές που είναι πολύ υψηλές.
= OR (B2 $ F $ 6)
Τώρα μπορείτε να φιλτράρετε τη στήλη Outlier και να εμφανίσετε μόνο τις εγγραφές όπου η τιμή είναι TRUE.
Εναλλακτικά, μπορείτε επίσης να χρησιμοποιήσετε μορφοποίηση υπό όρους για να επισημάνετε όλα τα κελιά όπου η τιμή είναι TRUE
Σημείωση: Ενώ αυτή είναι μια πιο αποδεκτή μέθοδος για τον εντοπισμό απομακρυσμένων στατιστικών. Θεωρώ ότι αυτή η μέθοδος είναι λίγο άχρηστη σε σενάρια πραγματικής ζωής. Στο παραπάνω παράδειγμα, το κατώτερο όριο που υπολογίζεται από τον τύπο είναι -103, ενώ το σύνολο δεδομένων που έχουμε μπορεί να είναι μόνο θετικό. Έτσι, αυτή η μέθοδος μπορεί να μας βοηθήσει να βρούμε απομακρυσμένες προς μία κατεύθυνση (υψηλές τιμές), είναι άχρηστη στον εντοπισμό ακραίων προς την άλλη κατεύθυνση.Εύρεση των ακραίων χρηστών χρησιμοποιώντας τις συναρτήσεις ΜΕΓΑΛΕΣ/ΜΙΚΡΕΣ
Εάν εργάζεστε με πολλά δεδομένα (τιμές σε πολλαπλές στήλες), μπορείτε να εξαγάγετε τις μεγαλύτερες και τις μικρότερες 5 ή 7 τιμές και να δείτε αν υπάρχουν υπερβολικές τιμές σε αυτό.
Εάν υπάρχουν ακραίες τιμές, θα μπορείτε να τα προσδιορίσετε χωρίς να χρειαστεί να περάσετε από όλα τα δεδομένα και προς τις δύο κατευθύνσεις.
Ας υποθέσουμε ότι έχουμε το παρακάτω σύνολο δεδομένων και θέλουμε να μάθουμε αν υπάρχουν υπερβολικές τιμές.
Παρακάτω είναι ο τύπος που θα σας δώσει τη μεγαλύτερη τιμή στο σύνολο δεδομένων:
= ΜΕΓΑΛΟ ($ B $ 2: $ B $ 16,1)
Ομοίως, η δεύτερη μεγαλύτερη τιμή θα δοθεί από
= ΜΕΓΑΛΟ ($ B $ 2: $ B $ 16,1)
Εάν δεν χρησιμοποιείτε το Microsoft 365, το οποίο διαθέτει δυναμικούς πίνακες, μπορείτε να χρησιμοποιήσετε τον παρακάτω τύπο και θα σας δώσει τις πέντε μεγαλύτερες τιμές από το σύνολο δεδομένων με έναν μόνο τύπο:
= ΜΕΓΑΛΟ ($ B $ 2: $ B $ 16, ROW ($ 1: 5))
Ομοίως, εάν θέλετε τις 5 μικρότερες τιμές, χρησιμοποιήστε τον παρακάτω τύπο:
= ΜΙΚΡΟ ($ B $ 2: $ B $ 16, ROW ($ 1: 5))
ή τα ακόλουθα σε περίπτωση που δεν έχετε δυναμικούς πίνακες:
= ΜΙΚΡΟ ($ B $ 2: $ B $ 16,1)
Μόλις έχετε αυτές τις τιμές, είναι πραγματικά εύκολο να βρείτε τυχόν υπερβολικές τιμές στο σύνολο δεδομένων.
Ενώ έχω επιλέξει να εξάγω τις μεγαλύτερες και μικρότερες 5 τιμές, μπορείτε να επιλέξετε να λάβετε 7 ή 10 με βάση το πόσο μεγάλο είναι το σύνολο δεδομένων σας.
Δεν είμαι σίγουρος αν αυτή είναι μια αποδεκτή μέθοδος για την εξεύρεση υψηλών τιμών στο Excel ή όχι, αλλά αυτή είναι η μέθοδος που χρησιμοποίησα όταν έπρεπε να δουλέψω με πολλά οικονομικά δεδομένα στη δουλειά μου πριν από μερικά χρόνια. Σε σύγκριση με όλες τις άλλες μεθόδους που καλύπτονται σε αυτό το σεμινάριο, βρήκα ότι αυτή είναι η πιο αποτελεσματική.
Πώς να χειριστείτε τα Outlier με τον σωστό τρόπο
Μέχρι στιγμής, έχουμε δει τις μεθόδους που θα μας βοηθήσουν να βρούμε τις ακραίες τιμές στο σύνολο δεδομένων μας. Τι να κάνετε όμως μόλις μάθετε ότι υπάρχουν ακραίες τιμές.
Ακολουθούν μερικές μέθοδοι που μπορείτε να χρησιμοποιήσετε για να χειριστείτε τα πολύ μακριά, έτσι ώστε η ανάλυση των δεδομένων σας να είναι σωστή.
Διαγράψτε τα Outliers
Ο ευκολότερος τρόπος για να αφαιρέσετε τις υπερβολικές τιμές από το σύνολο δεδομένων σας είναι απλά να τις διαγράψετε. Με αυτόν τον τρόπο δεν θα επηρεάσει την ανάλυσή σας.
Είναι μια πιο βιώσιμη λύση όταν έχετε μεγάλα σύνολα δεδομένων και η διαγραφή μερικών ακραίων χαρακτηριστικών δεν θα επηρεάσει τη συνολική ανάλυση. Και φυσικά, πριν διαγράψετε τα δεδομένα, βεβαιωθείτε ότι έχετε δημιουργήσει ένα αντίγραφο και ερευνήσετε τι προκαλεί αυτές τις υπερβολικές τιμές.
Κανονικοποίηση των υπερβολικών (Προσαρμόστε την τιμή)
Η κανονικοποίηση των υπερβολικών είναι αυτό που έκανα όταν ήμουν στη δουλειά μου πλήρους απασχόλησης. Για όλες τις απομακρυσμένες τιμές, απλώς θα τις άλλαζα σε μια τιμή που είναι ελαφρώς υψηλότερη από τη μέγιστη τιμή στο σύνολο δεδομένων.
Αυτό διασφάλισε ότι δεν διαγράφω τα δεδομένα, αλλά ταυτόχρονα δεν το αφήνω να στραβώσει τα δεδομένα μου.
Για να σας δώσω ένα πραγματικό παράδειγμα, αν αναλύετε το καθαρό περιθώριο κέρδους των εταιρειών, όπου οι περισσότερες από τις εταιρείες βρίσκονται στο -10%έως 30%, και υπάρχουν μερικές τιμές που είναι πάνω από 100%, I απλώς θα άλλαζε αυτές τις ακραίες τιμές σε 30% ή 35%.
Αυτές είναι λοιπόν μερικές από τις μεθόδους στις οποίες μπορείτε να χρησιμοποιήσετε Excel για να βρείτε απομακρυσμένα.
Αφού εντοπίσετε τα υπερβολικά, μπορείτε να εμβαθύνετε στα δεδομένα και να αναζητήσετε τι τα προκαλεί, επιλέγοντας ταυτόχρονα μία από τις τεχνικές για τον χειρισμό αυτών των υπερβολικών τιμών (που θα μπορούσαν να τα καταργήσουν ή να τα εξομαλύνουν προσαρμόζοντας την τιμή)
Ελπίζω να βρήκατε χρήσιμο αυτό το σεμινάριο.
