Ο συγκεντρωτικός πίνακας Top 10 Filter παρέχει ένα σύνολο των ορατών σειρών
Οι συγκεντρωτικοί πίνακες προσφέρουν ένα κορυφαίο φίλτρο 10. Ειναι ΕΝΤΑΞΕΙ. Είναι ευέλικτο. Αλλά το μισώ, και θα σας πω γιατί.
Ακολουθεί ένας συγκεντρωτικός πίνακας που δείχνει τα έσοδα ανά πελάτη. Το σύνολο των εσόδων ανέρχεται σε 6,7 εκατομμύρια δολάρια.
Τι γίνεται αν ο διευθυντής μου έχει το εύρος προσοχής ενός χρυσόψαρου και θέλει να δει μόνο τους πέντε πρώτους πελάτες;
Για να ξεκινήσετε, ανοίξτε το αναπτυσσόμενο μενού στο A3 και επιλέξτε Value Filters, Top 10.
Ο εξαιρετικά ευέλικτος διάλογος Top 10 Filter επιτρέπει το Top / Bottom. Μπορεί να κάνει 10, 5 ή οποιονδήποτε άλλο αριθμό. Μπορείτε να ζητήσετε τα πέντε κορυφαία στοιχεία, το 80% κορυφαίοι ή αρκετούς πελάτες για να φτάσετε τα 5 εκατομμύρια $.
Αλλά εδώ είναι το πρόβλημα: Η αναφορά που προκύπτει δείχνει πέντε πελάτες και το σύνολο από αυτούς τους πελάτες αντί για τα σύνολα από όλους.
Πρώτον, λίγες σημαντικές λέξεις για το Αυτόματο φίλτρο
Συνειδητοποιώ ότι αυτό μοιάζει με μια απίστευτη ερώτηση Εάν θέλετε να ενεργοποιήσετε τα αναπτυσσόμενα μενού Φίλτρο σε ένα κανονικό σύνολο δεδομένων, πώς το κάνετε; Εδώ είναι τρεις πολύ συνηθισμένοι τρόποι:
- Επιλέξτε ένα κελί στα δεδομένα σας και κάντε κλικ στο εικονίδιο Φίλτρο στην καρτέλα Δεδομένα.
- Επιλέξτε όλα τα δεδομένα σας με 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 ή 2016, υπάρχει ένας πολύ βολικός τρόπος για να το κάνετε αυτό. Για να σας δείξω αυτό, έχω διαγράψει τον αρχικό συγκεντρωτικό πίνακα. Επιλέξτε Εισαγωγή, Συγκεντρωτικός Πίνακας. Πριν κάνετε κλικ στο OK, επιλέξτε το πλαίσιο που λέει Προσθήκη αυτών των δεδομένων στο μοντέλο δεδομένων.
Δημιουργήστε τον περιστρεφόμενο πίνακα σας ως συνήθως. Χρησιμοποιήστε το αναπτυσσόμενο μενού στο A3 για να επιλέξετε Value Filters, Top 10 και ζητήστε τους πέντε πρώτους πελάτες. Με επιλεγμένο ένα κελί στον συγκεντρωτικό πίνακα, μεταβείτε στην καρτέλα Σχεδίαση στην κορδέλα και ανοίξτε το αναπτυσσόμενο μενού Μερικά αθροίσματα. Η τελική επιλογή στο αναπτυσσόμενο μενού είναι Συμπερίληψη φιλτραρισμένων στοιχείων στα σύνολα. Κανονικά, αυτή η επιλογή είναι γκρίζα. Αλλά επειδή τα δεδομένα αποθηκεύονται στο Μοντέλο δεδομένων αντί για μια κανονική συγκεντρωτική κρυφή μνήμη, αυτή η επιλογή είναι πλέον διαθέσιμη.
Επιλέξτε την επιλογή Συμπερίληψη φιλτραρισμένων στοιχείων στο σύνολο και το Grand Total περιλαμβάνει τώρα έναν αστερίσκο και το σύνολο όλων των δεδομένων.
Αυτό το κόλπο ήρθε αρχικά από τον Dan στο σεμινάριό μου στη Φιλαδέλφεια. Ευχαριστούμε τον Miguel Caballero για την πρόταση αυτής της δυνατότητας.
Δες το βίντεο
- Ο συγκεντρωτικός πίνακας Top 10 Filter παρέχει ένα σύνολο των ορατών σειρών
- Συμπερίληψη φιλτραρισμένων στοιχείων στα σύνολα είναι γκρι
- Παράξενος τρόπος να επικαλεσθεί το φίλτρο δεδομένων από το μαγικό κελί
- Τα φίλτρα δεδομένων δεν επιτρέπονται σε συγκεντρωτικούς πίνακες
- Το Excel αποτυγχάνει να γκρίζει το φίλτρο δεδομένων από το μαγικό κελί
- Ζητήστε τους πρώτους 6 για να πάρετε το top 5 συν Grand Total
- Χρήσιμο για φιλτράρισμα από ένα συγκεκριμένο περιστρεφόμενο στοιχείο
- Excel 2013 ή νεότερο: Διαφορετικός τρόπος για να λάβετε το πραγματικό σύνολο
- Στείλτε τα δεδομένα σας μέσω του Μοντέλου Δεδομένων
- Συμπερίληψη φιλτραρισμένων στοιχείων στα σύνολα θα είναι διαθέσιμα
- Λάβετε Σύνολο με αστερίσκο
- Έμαθα αυτό το κόλπο πριν από 10+ χρόνια από τον Dan στη Φιλαδέλφεια
Μεταγραφή βίντεο
Μάθετε το Excel για Podcast, Episode 1999 - Συγκεντρωτικός πίνακας True Top Five
Μεταδίδω ολόκληρο το βιβλίο. Υπάρχει μια λίστα αναπαραγωγής, κάντε κλικ στο I στην επάνω δεξιά γωνία για να ακολουθήσετε αυτήν τη λίστα αναπαραγωγής. Καλώς ήλθατε πίσω στο netcast. Είμαι ο Μπιλ Τζέλεν.
Εντάξει, οπότε πρόκειται να δημιουργήσουμε έναν συγκεντρωτικό πίνακα και θέλουμε να δείξουμε, όχι όλους τους πελάτες, αλλά μόνο τους πέντε πρώτους πελάτες. ΕΙΣΑΓΩΓΗ, Συγκεντρωτικός Πίνακας. Εντάξει, θα βάλω τον Πελάτη στην αριστερή πλευρά και τα Έσοδα. Εντάξει, λοιπόν, εδώ είναι ολόκληρη η λίστα πελατών με 6,7 εκατομμύρια δολάρια. Το Excel, καθιστά εύκολο να κάνεις τις πρώτες πέντε. Μεταβείτε στις ετικέτες σειράς, φίλτρα τιμής, κορυφή 10. Δεν χρειάζεται να είστε κορυφαίοι. Μπορεί να είναι πάνω ή κάτω. Δεν χρειάζεται να είναι πέντε. Μπορεί να είναι είκοσι, σαράντα, μπορεί να είναι οτιδήποτε. Κορυφαία ογδόντα τοις εκατό, δώστε μου αρκετά ρεκόρ για να φτάσω σε τρία εκατομμύρια δολάρια ή τέσσερα εκατομμύρια δολάρια, αλλά εδώ πηγαίνουμε. Κορυφαία πέντε αντικείμενα. Τώρα θυμηθείτε 6,7 εκατομμύρια δολάρια, κάντε κλικ στο OK και το μεγάλο πρόβλημα εδώ, είναι ότι αυτό το συνολικό ποσό δεν είναι τα 6,7 εκατομμύρια. Όταν το δώσω στο VP των πωλήσεων, θα φρικάρει, λέγοντας, περιμένετε ένα δευτερόλεπτο,Ξέρω ότι έκανα περισσότερα από 3,3 εκατομμύρια δολάρια. Σωστά, οπότε πρόκειται να αναιρέσουμε, να αναιρέσουμε αυτό και να επιστρέψουμε στα αρχικά δεδομένα.
Τώρα αυτό το επόμενο κόλπο έμαθα κατά τη διάρκεια ενός από τα σεμινάρια Power Excel στη Φιλαδέλφεια. Ένας τύπος που ονομάζεται Dan στη δεύτερη σειρά, μου έδειξε αυτό. Πριν από περισσότερα από δέκα χρόνια μου έδειξε αυτό το τέχνασμα και πρώτα πρέπει να μιλήσουμε για τα φίλτρα. Έτσι, κανονικά, εάν πρόκειται να χρησιμοποιήσετε το κανονικό φίλτρο, αυτό το φίλτρο εδώ, επιλέγετε οποιοδήποτε κελί στο σύνολο δεδομένων σας και κάντε κλικ στο εικονίδιο φίλτρου ή ορισμένα άτομα επιλέγουν ολόκληρο το σύνολο δεδομένων, CONTROL * και κάντε κλικ στο εικονίδιο φίλτρου, αλλά υπάρχει ένας τρίτος τρόπος. Ένας τρόπος που κανείς δεν νοιάζεται. Αν πάτε στο τελευταίο κελί επικεφαλίδας, στην περίπτωσή μου, αυτό είναι το κόστος σε L1 και πηγαίνετε ένα κελί προς τα δεξιά. Το αποκαλώ αυτό το μαγικό κελί, δεν έχω ιδέα γιατί, αλλά για κάποιο άγνωστο λόγο, από αυτό το κελί, μπορώ να φιλτράρω το διπλανό σύνολο δεδομένων. Εντάξει, είναι σαν παράξενος τρόπος και κανείς δεν νοιάζεται για αυτό.
Σωστά, επειδή υπάρχουν δύο άλλοι πραγματικά καλοί τρόποι για να επικαλεστεί ένα Φίλτρο, κανείς δεν πρέπει να ξέρει για το μαγικό κελί, αλλά εδώ είναι το πράγμα, δείτε μέσα σε έναν Συγκεντρωτικό Πίνακα, είναι γκρίζο. Δεν επιτρέπεται να χρησιμοποιείτε αυτά τα φίλτρα. Είναι αντίθετο με τους κανόνες. Τώρα, αν βγαίνω εδώ, είμαι πολύ ευπρόσδεκτος να χρησιμοποιήσω το Φίλτρο, αλλά μέσα στο βαθμό που είναι έξω. Δεν ξέρω ποιο είναι το άτομο που το καταλαβαίνει, αλλά δεν έχουν ακούσει ποτέ τη μικρή μου συζήτηση για το μαγικό κελί, γιατί αν πάω στο τελευταίο Κεφάλαιο Κεφαλίδας και πάω ένα κελί προς τα δεξιά, κοίτα αυτό, ξεχνούν να γκρίζω το φίλτρο και τώρα μόλις πρόσθεσα τα παλιά αυτόματα φίλτρα στον συγκεντρωτικό πίνακα. Έρχομαι λοιπόν, πηγαίνω στα Φίλτρα αριθμού, που είναι διαφορετικό από τα Φίλτρα τιμών. Ονομάζεται ακόμα Top Ten. Λίγο διαφορετικό, πρόκειται να ζητήσω τους πρώτους πέντε, όχι τους πρώτους έξι.Οι πρώτοι έξι επειδή σε αυτό το φίλτρο το Grand Total είναι απλώς μια άλλη σειρά και το Grand Total είναι το μεγαλύτερο στοιχείο και, στη συνέχεια, όταν μου ζητούνται τα στοιχεία 2 έως 6, παίρνω τα πέντε κορυφαία στοιχεία.
Εντάξει, έτσι είμαστε. Ένα δροσερό φίλτρο, που μας δίνει τα πέντε κορυφαία στοιχεία και το αληθινό σύνολο όλων. Εντάξει τώρα, μερικά πράγματα. Μην ξεχάσετε το μαγικό κελί. Εντάξει δεν υπάρχει τρόπος να απενεργοποιήσετε αυτό το φίλτρο, εκτός αν επιστρέψετε στο μαγικό κελί. Εντάξει, έτσι πρέπει να θυμάστε το μαγικό κελί. Επίσης, εάν αλλάξετε τα υποκείμενα δεδομένα και ανανεώσετε τον Συγκεντρωτικό Πίνακα, δεν πρόκειται να ανανεώσουν το Φίλτρο γιατί, όσο γνωρίζει η Microsoft, δεν επιτρέπεται να έχετε Φίλτρο.
Αυτό είναι χρήσιμο για άλλα πράγματα. Μερικές φορές έχουμε προϊόντα που ξεπερνούν την κορυφή. Ας πάμε εδώ σε μορφή πίνακα. Δεν είναι απαραίτητο, θέλω απλώς να λάβω πραγματικές επικεφαλίδες. Gizmo, Widget, Gadgets, Doodads. Εντάξει και ίσως είστε ο διαχειριστής του Doodads και πρέπει να δείτε μόνο τους πελάτες που είχαν μια συγκεκριμένη αξία και το Doodads. Πηγαίνω λοιπόν στο μαγικό κελί, ενεργοποιώ το φίλτρο και έπειτα κάτω από το Doodads μπορώ να ζητήσω στοιχεία που είναι μεγαλύτερα από το μηδέν. Κάντε κλικ στο OK. Εντάξει, αυτός ο τύπος φιλτραρίσματος δεν θα ήταν δυνατός σε έναν κανονικό συγκεντρωτικό πίνακα, αλλά είναι δυνατό χρησιμοποιώντας το μαγικό κελί.
Εντάξει τώρα ας αναιρέσουμε τη λίστα. Ας απενεργοποιήσουμε αυτό το φίλτρο και να αφαιρέσουμε τον συγκεντρωτικό πίνακα και αν είστε στο Excel 2013 ή καινούργιος, θα σας δείξω έναν εντελώς νόμιμο τρόπο για να πάρετε το σωστό σύνολο στο κάτω μέρος. Εισαγάγετε τον συγκεντρωτικό πίνακα, εδώ στο κάτω μέρος, ξεκινώντας από το Excel 2013 αυτό το πολύ αβλαβές πλαίσιο, δεν ακούγεται πολύ συναρπαστικό, προσθέστε αυτά τα δεδομένα στο μοντέλο δεδομένων. Αυτό στέλνει τα δεδομένα, πίσω από τα παρασκήνια, στο Power Pivot Engine. Δημιουργήστε την ίδια ακριβώς αναφορά. Πελάτες κάτω από την αριστερή πλευρά. Έσοδα στην καρδιά του συγκεντρωτικού πίνακα. Στη συνέχεια, μεταβείτε στα κανονικά φίλτρα, στην κορυφή 10. Τα φίλτρα τιμής. Ζητήστε τα πέντε πρώτα. Παρατηρήστε ξανά ότι έχουμε 6,7 εκατομμύρια δολάρια αφού το κάνω αυτό, 3,3 εκατομμύρια δολάρια, αλλά εδώ είναι η διαφορά. Όταν μεταβαίνω στην καρτέλα σχεδίασης, στην ενότητα Μερικά αθροίσματα, αυτή η λειτουργία ονομάζεται Συμπερίληψη φιλτραρισμένων στοιχείων σε σύνολα,δεν είναι πλέον γκρίζο. Σε έναν κανονικό συγκεντρωτικό πίνακα δεν είναι διαθέσιμος. Παίρνουμε λίγο αστερίσκο εκεί και είναι το σύνολο των πάντων. Εντάξει, τώρα φυσικά που λειτουργεί μόνο στο Excel 2013 ή νεότερο.
Εντάξει θα χρειαστούν έξι εβδομάδες για να βγάλω ολόκληρο το βιβλίο εδώ στο YouTube. Υπάρχουν τόσες πολλές καλές συμβουλές εδώ. Συμβουλές που θα μπορούσαν να αρχίσουν να σας εξοικονομούν χρόνο, αμέσως. Αγοράστε ολόκληρο το βιβλίο τώρα και θα έχετε πρόσβαση και στα 40, στην πραγματικότητα είναι πολύ περισσότερες από 40 συμβουλές. Πλήκτρα συντόμευσης Excel. Όλα τα είδη εξαιρετικών στοιχείων σε αυτό το βιβλίο.
Εντάξει, ανακεφαλαιώστε. Έτσι, όταν κάνουμε ένα φίλτρο Pivot Table top 10, μας δίνει το σύνολο αλλά μόνο τις ορατές σειρές, όχι τα πράγματα που φιλτράρεται. Ναι, αν μεταβούμε στη δεύτερη καρτέλα και αναζητήσουμε αθροίσματα, φιλτραρισμένα στοιχεία και σύνολα, είναι γκριζαρισμένο, αλλά υπάρχει ένας περίεργος τρόπος να επικαλεστούμε το παλιό φίλτρο δεδομένων από το μαγικό κελί. Το τελευταίο κελί επικεφαλίδας, πηγαίνετε ένα κελί προς τα δεξιά, δεν μπορείτε να χρησιμοποιήσετε φίλτρα και συγκεντρωτικούς πίνακες, αλλά αν πάτε στο μαγικό κελί ξεχνούν να το γκριζάρουν. Τώρα στο Φίλτρο αριθμών, ζητάτε τους πρώτους έξι για να λάβετε τους πέντε πρώτους, συν το συνολικό συνολικό. Επίσης χρήσιμο για φιλτράρισμα σε ένα συγκεκριμένο Pivot Item: Doodads, οτιδήποτε είχε περισσότερα από 0 στο Doodads ή τα κορυφαία 5 Doodads. Excel 2013 ή νεότερο, υπάρχει ένας διαφορετικός τρόπος για να λάβετε το True Total.Επιλέξτε αυτό το πλαίσιο για το μοντέλο δεδομένων και, στη συνέχεια, συμπεριλάβετε τα φιλτραρισμένα στοιχεία στα σύνολα. Παίρνετε το σύνολο με έναν αστερίσκο. Και χάρη στον Dan στη Φιλαδέλφεια που μου έδειξε σε ένα από τα σεμινάρια Power Excel, πριν από περισσότερα από δέκα χρόνια, και μου έδωσε αυτό το υπέροχο μικρό κόλπο. Ένας τρόπος για το φίλτρο να γλιστρήσει μέσα από το Club Pivot Table Wall. Συνήθως δεν επιτρέπουν αυτό το αυτόματο φίλτρο.
Γεια, θέλω να σας ευχαριστήσω που σταματήσατε. Θα σας δούμε την επόμενη φορά, για ένα άλλο netcast από το MRExcel.
Λήψη αρχείου
Κάντε λήψη του δείγματος αρχείου εδώ: Podcast1999.xlsx
