Πώς (και γιατί) να χρησιμοποιήσετε τη λειτουργία Outliers στο Excel

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

Ένα γρήγορο παράδειγμα

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

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

Πώς να βρείτε Outliers στα δεδομένα σας

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

1. Υπολογίστε το 1ο και 3ο τεταρτημόριο (θα μιλάμε για αυτά που είναι λίγο).
2. Εκτιμήστε το εύρος των διακαρτήτων (θα εξηγήσουμε και αυτά κάπως πιο κάτω).
3. Επιστρέψτε τα άνω και κάτω όρια του εύρους δεδομένων μας.
4. Χρησιμοποιήστε αυτά τα όρια για να εντοπίσετε τα απομακρυσμένα σημεία δεδομένων.

Η περιοχή κυψελών στα δεξιά του συνόλου δεδομένων που φαίνεται στην παρακάτω εικόνα θα χρησιμοποιηθεί για την αποθήκευση αυτών των τιμών.

Ας αρχίσουμε.

Βήμα πρώτο: Υπολογίστε τα τεταρτημόρια

Εάν διαιρείτε τα δεδομένα σας σε τεταρτημόρια, κάθε ένα από αυτά τα σύνολα ονομάζεται τεταρτημόριο. Το χαμηλότερο 25% των αριθμών στην περιοχή αποτελούν το 1ο τεταρτημόριο, το επόμενο 25% το 2ο τεταρτημόριο κ.ο.κ. Πραγματοποιούμε αυτό το πρώτο βήμα επειδή ο πιο ευρέως χρησιμοποιούμενος ορισμός ενός εξωλέμβιου είναι ένα σημείο δεδομένων που είναι περισσότερο από 1,5 interquartile (IQRs) κάτω από το 1ο τεταρτημόριο και 1,5 interquartile κυμαίνεται πάνω από το 3ο τεταρτημόριο. Για να προσδιορίσουμε αυτές τις τιμές, πρέπει πρώτα να καταλάβουμε τι είναι τα τεταρτημόρια.

Το Excel παρέχει μια λειτουργία QUARTILE για τον υπολογισμό των τεταρτημορίων. Απαιτεί δύο στοιχεία πληροφοριών: τον πίνακα και το quart.

= QUARTILE (πίνακας, quart)

ο παράταξη είναι το φάσμα τιμών που αξιολογείτε. Και το τέταρτο γαλονιού είναι ένας αριθμός που αντιπροσωπεύει το τεταρτημόριο που θέλετε να επιστρέψετε (π.χ. 1 για το 1^st τεταρτημόριο, 2 για το 2ο τεταρτημόριο και ούτω καθεξής).

Σημείωση: Στο Excel 2010, η Microsoft κυκλοφόρησε τις λειτουργίες QUARTILE.INC και QUARTILE.EXC ως βελτιώσεις στη λειτουργία QUARTILE. Το QUARTILE είναι πιο συμβατό προς τα πίσω όταν εργάζεστε σε πολλές εκδόσεις του Excel.

Ας επιστρέψουμε στον πίνακα παραδειγμάτων μας.

Για να υπολογίσετε το 1^st Στο τεταρτημόριο μπορούμε να χρησιμοποιήσουμε τον ακόλουθο τύπο στο κελί F2.

= QUARTILE (Β2: Β14,1)

Καθώς εισάγετε τον τύπο, το Excel παρέχει μια λίστα επιλογών για το όρισμα quart.

Για να υπολογίσετε το 3^rd τεταρτημόριο, μπορούμε να εισάγουμε έναν τύπο όπως ο προηγούμενος στο κελί F3, αλλά χρησιμοποιώντας ένα τρία αντί για ένα.

= QUARTILE (Β2: Β14,3)

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

Βήμα δεύτερο: Αξιολογήστε τη διατρακτυλενική εμβέλεια

Το εύρος μεταξύ τεταρτοταγών (ή IQR) είναι το μεσαίο 50% των τιμών στα δεδομένα σας. Υπολογίζεται ως η διαφορά μεταξύ της τιμής του 1ου τεταρτημορίου και της τιμής του 3ου τεταρτημορίου.

Θα χρησιμοποιήσουμε μια απλή φόρμουλα στο κελί F4 που αφαιρεί το 1^st τεταρτημόριο από τις 3^rd τεταρτημόριο:

= F3-F2

Τώρα, μπορούμε να δούμε την περιοχή διατρακτυλενίων που εμφανίζεται.

Βήμα τρίτο: Επιστρέψτε τα κατώτερα και ανώτερα όρια

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

Θα υπολογίσουμε το όριο κατώτερου ορίου στο κελί F5 πολλαπλασιάζοντας την τιμή IQR κατά 1,5 και στη συνέχεια αφαιρώντας την από το σημείο δεδομένων Q1:

= F2- (1,5 * F4)

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

Για να υπολογίσουμε το ανώτερο όριο στο κελί F6, πολλαπλασιάζουμε πάλι το IQR κατά 1,5, αλλά αυτή τη φορά προσθέτω στο σημείο δεδομένων Q3:

= F3 + (1,5 * F4)

Βήμα τέσσερα: Προσδιορίστε τα Outliers

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

Θα χρησιμοποιήσουμε τη λειτουργία OR για να εκτελέσουμε αυτή τη λογική δοκιμή και θα δείξουμε τις τιμές που πληρούν αυτά τα κριτήρια εισάγοντας τον ακόλουθο τύπο στο κελί C2:

= OR (B2 $ F $ 6)

Στη συνέχεια, θα αντιγράψουμε αυτήν την τιμή στα κύτταρα C3-C14. Μια ΑΛΗΘΙΝΗ τιμή υποδεικνύει μια απόκλιση και, όπως μπορείτε να δείτε, έχουμε δύο στα δεδομένα μας.

Παραβλέποντας τα Outliers κατά τον υπολογισμό του μέσου όρου μέσου όρου

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

Η συνάρτηση που χρειαζόμαστε ονομάζεται TRIMMEAN και μπορείτε να δείτε τη σύνταξη για αυτό παρακάτω:

= TRIMMEAN (πίνακας, ποσοστό)

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

Εισήγαμε τον παρακάτω τύπο στο κελί D3 στο παράδειγμά μας για να υπολογίσουμε τον μέσο όρο και να αποκλείσουμε το 20% των υπερβολικών τιμών.

= TRIMMEAN (Β2: Β14, 20%)

---

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