Οι συγκεντρωτικοί πίνακες προσφέρουν ένα κορυφαίο φίλτρο 10. Ειναι ΕΝΤΑΞΕΙ. Είναι ευέλικτο. Αλλά το μισώ, και θα σας πω γιατί.
Ακολουθεί ένας συγκεντρωτικός πίνακας που δείχνει τα έσοδα ανά πελάτη. Το σύνολο των εσόδων ανέρχεται σε 6,7 εκατομμύρια δολάρια. Σημειώστε ότι ο μεγαλύτερος πελάτης, το Roto-Rooter, είναι το 9% των συνολικών εσόδων.
Τι γίνεται αν ο διευθυντής μου έχει το εύρος προσοχής ενός χρυσόψαρου και θέλει να δει μόνο τους πέντε πρώτους πελάτες; Για να ξεκινήσετε, ανοίξτε το αναπτυσσόμενο μενού στο A3 και επιλέξτε Value Filters, Top 10.
Ο εξαιρετικά ευέλικτος διάλογος Top 10 Filter επιτρέπει το Top / Bottom. Μπορεί να κάνει 10, 5 ή οποιονδήποτε άλλο αριθμό. Μπορείτε να ζητήσετε τα πέντε κορυφαία στοιχεία, το 80% κορυφαίοι ή αρκετούς πελάτες για να φτάσετε τα 5 εκατομμύρια $.
Αλλά εδώ είναι το πρόβλημα: Η αναφορά που προκύπτει δείχνει πέντε πελάτες και το σύνολο από αυτούς τους πελάτες αντί για τα σύνολα από όλους. Ο Roto-Rooter, ο οποίος προηγουμένως ήταν 9% του συνόλου, είναι 23% του νέου συνόλου.
Πρώτον, λίγες σημαντικές λέξεις για το Αυτόματο φίλτρο
Συνειδητοποιώ ότι αυτό μοιάζει με μια απίστευτη ερώτηση Εάν θέλετε να ενεργοποιήσετε τα αναπτυσσόμενα μενού Φίλτρο σε ένα κανονικό σύνολο δεδομένων, πώς το κάνετε; Εδώ είναι τρεις πολύ συνηθισμένοι τρόποι:
- Επιλέξτε ένα κελί στα δεδομένα σας και κάντε κλικ στο εικονίδιο Φίλτρο στην καρτέλα Δεδομένα ή πατήστε Ctrl + Shift + L.
- Επιλέξτε όλα τα δεδομένα σας με Ctrl + * και κάντε κλικ στο εικονίδιο Φίλτρο στην καρτέλα Δεδομένα.
- Πατήστε Ctrl + T για να μορφοποιήσετε τα δεδομένα ως πίνακα.
Αυτοί είναι τρεις πολύ καλοί τρόποι. Εφόσον γνωρίζετε κάποιο από αυτά, δεν υπάρχει καμία ανάγκη να γνωρίζετε άλλο τρόπο. Αλλά εδώ είναι ένας απίστευτα ασαφής αλλά μαγικός τρόπος για να ενεργοποιήσετε το φίλτρο:
- Μεταβείτε στη σειρά κεφαλίδων και, στη συνέχεια, μεταβείτε στο δεξί κελί κεφαλίδας. Μετακινήστε ένα κελί προς τα δεξιά. Για κάποιο άγνωστο λόγο, όταν βρίσκεστε σε αυτό το κελί και κάνετε κλικ στο εικονίδιο Φίλτρο, το Excel φιλτράρει το σύνολο δεδομένων στα αριστερά σας. Δεν έχω ιδέα γιατί λειτουργεί. Δεν αξίζει να μιλήσουμε γιατί υπάρχουν ήδη τρεις πολύ καλοί τρόποι για να ενεργοποιήσετε τα αναπτυσσόμενα φίλτρα. Λέω αυτό το κελί το μαγικό κελί.
Και τώρα, Επιστροφή στους συγκεντρωτικούς πίνακες
Υπάρχει ένας κανόνας που λέει ότι δεν μπορείτε να χρησιμοποιήσετε το Αυτόματο φίλτρο όταν βρίσκεστε σε έναν συγκεντρωτικό πίνακα. Δες παρακάτω? Το εικονίδιο φίλτρου είναι γκρίζο επειδή έχω επιλέξει ένα κελί στον συγκεντρωτικό πίνακα.
Δεν ξέρω γιατί η Microsoft το καταλαβαίνει. Πρέπει να είναι κάτι εσωτερικό που λέει ότι το AutoFilter και ένας συγκεντρωτικός πίνακας δεν μπορούν να συνυπάρχουν. Υπάρχει λοιπόν κάποιος στην ομάδα του Excel που είναι υπεύθυνος για το γκριζάρισμα του εικονιδίου Φίλτρο. Αυτό το άτομο δεν έχει ακούσει ποτέ για το μαγικό κελί. Επιλέξτε ένα κελί στον συγκεντρωτικό πίνακα και το φίλτρο γίνεται γκρι. Κάντε κλικ έξω από τον συγκεντρωτικό πίνακα και το φίλτρο ενεργοποιείται ξανά.
Αλλά περίμενε. Τι γίνεται με το μαγικό κελί που μόλις σας είπα; Εάν κάνετε κλικ στο κελί στα δεξιά της τελευταίας επικεφαλίδας, το Excel ξεχνά να γκρίζει το εικονίδιο φίλτρου
Σίγουρα, το Excel προσθέτει τα αναπτυσσόμενα μενού αυτόματου φίλτρου στην επάνω σειρά του συγκεντρωτικού σας πίνακα. Και το AutoFilter λειτουργεί διαφορετικά από ένα φίλτρο περιστρεφόμενου πίνακα. Μεταβείτε στο αναπτυσσόμενο μενού Έσοδα και επιλέξτε Φίλτρα αριθμού, Κορυφαία 10….
Στο διάλογο Top 10 AutoFilter, επιλέξτε Top 6 Items. Αυτό δεν είναι τυπογραφικό λάθος… αν θέλετε πέντε πελάτες, επιλέξτε 6. Εάν θέλετε 10 πελάτες, επιλέξτε 11.
Στο Αυτόματο Φίλτρο, η μεγάλη συνολική σειρά είναι το μεγαλύτερο στοιχείο στα δεδομένα. Οι πέντε πρώτοι πελάτες καταλαμβάνουν τις θέσεις 2 έως 6 στα δεδομένα.
Προσοχή
Σαφώς, σκίζετε μια τρύπα στο ύφασμα του Excel με αυτό το κόλπο. Εάν αλλάξετε αργότερα τα υποκείμενα δεδομένα και ανανεώσετε τον συγκεντρωτικό σας πίνακα, το Excel δεν θα ανανεώσει το φίλτρο, επειδή, όσο γνωρίζει η Microsoft, δεν υπάρχει τρόπος να εφαρμόσετε ένα φίλτρο σε έναν συγκεντρωτικό πίνακα!
Σημείωση
Στόχος μας είναι να το κρατήσουμε μυστικό από τη Microsoft, επειδή είναι ένα πολύ ωραίο χαρακτηριστικό. Έχει «σπάσει» εδώ και αρκετό καιρό, οπότε υπάρχουν πολλοί άνθρωποι που μπορεί να το βασίζονται μέχρι τώρα.
Μια εντελώς νομική λύση στο Excel 2013+
Εάν θέλετε έναν συγκεντρωτικό πίνακα που σας δείχνει τους πέντε πρώτους πελάτες αλλά το σύνολο από όλους τους πελάτες, πρέπει να μετακινήσετε τα δεδομένα σας εκτός του Excel. Εάν χρησιμοποιείτε το Excel 2013 ή νεότερο στα Windows, υπάρχει ένας πολύ βολικός τρόπος για να το κάνετε αυτό. Για να σας δείξω αυτό, έχω διαγράψει τον αρχικό συγκεντρωτικό πίνακα. Επιλέξτε Εισαγωγή, Συγκεντρωτικός Πίνακας. Πριν κάνετε κλικ στο OK, επιλέξτε το πλαίσιο ελέγχου Προσθήκη αυτών των δεδομένων στο μοντέλο δεδομένων.
Δημιουργήστε τον περιστρεφόμενο πίνακα σας ως συνήθως. Χρησιμοποιήστε το αναπτυσσόμενο μενού στο A3 για να επιλέξετε Value Filters, Top 10 και ζητήστε τους πέντε πρώτους πελάτες. Με επιλεγμένο ένα κελί στον συγκεντρωτικό πίνακα, μεταβείτε στην καρτέλα Σχεδίαση στην Κορδέλα και ανοίξτε το αναπτυσσόμενο μενού Μερικά αθροίσματα. Η τελική επιλογή στο αναπτυσσόμενο μενού είναι Συμπερίληψη φιλτραρισμένων στοιχείων στα σύνολα. Κανονικά, αυτή η επιλογή είναι γκρίζα. Αλλά επειδή τα δεδομένα αποθηκεύονται στο Μοντέλο δεδομένων αντί για μια κανονική συγκεντρωτική κρυφή μνήμη, αυτή η επιλογή είναι πλέον διαθέσιμη.
Επιλέξτε την επιλογή Συμπερίληψη φιλτραρισμένων στοιχείων στα σύνολα και το Grand Total σας περιλαμβάνει τώρα έναν αστερίσκο και το σύνολο όλων των δεδομένων, όπως φαίνεται παρακάτω.
Αυτό το τέχνασμα μαγικών κυττάρων ήρθε αρχικά από τον Dan στο σεμινάριό μου στη Φιλαδέλφεια και επαναλήφθηκε 15 χρόνια αργότερα από διαφορετικό Dan από το σεμινάριό μου στο Σινσινάτι. Ευχαριστούμε τον Miguel Caballero για την πρόταση αυτής της δυνατότητας.
