Τα κανονικά φίλτρα συγκεντρωτικού πίνακα προσφέρουν τις σελίδες Εμφάνιση όλων των φίλτρων αναφοράς, αλλά οι Slicers δεν υποστηρίζουν αυτήν τη λειτουργικότητα. Σήμερα, κάποιο VBA μπορεί να βρει όλους τους πιθανούς συνδυασμούς slicer.
Δες το βίντεο
Μεταγραφή βίντεο
Μάθετε το Excel από, Podcast Episode 2106: Δημιουργήστε ένα PDF κάθε συνδυασμού 3 Slicers.
Τι μεγάλη ερώτηση έχουμε σήμερα. Κάποιος έγραψε, ήθελε να μάθει αν ήταν δυνατό. Αυτή τη στιγμή, έχουν 3 slicers που τρέχουν έναν περιστρεφόμενο πίνακα. Δεν ξέρω πώς φαίνεται ο περιστρεφόμενος πίνακας. Είναι εμπιστευτικό. Δεν μου επιτρέπεται να το δω, έτσι απλά υποθέτω, σωστά; Έτσι, αυτό που κάνουν είναι να επιλέγουν ένα στοιχείο από κάθε slicer και στη συνέχεια να δημιουργούν ένα PDF, και μετά να επιλέγουν το επόμενο στοιχείο και να δημιουργούν ένα PDF, και στη συνέχεια το επόμενο στοιχείο και το επόμενο στοιχείο και μπορείτε Φανταστείτε, με 400 συνδυασμούς slicers, αυτό θα μπορούσε να διαρκέσει για πάντα, και είπαν, υπάρχει κάποιος τρόπος για να περάσει ένα πρόγραμμα και να περάσει από όλες τις επιλογές;
Είπα, εντάξει, εδώ είναι μερικές ειδικές ερωτήσεις. Νούμερο ένα, δεν είμαστε σε Mac, σωστά; Όχι Android, όχι Excel για iPhone. Αυτό είναι το Excel για Windows. Ναι, είπαν. Εξαιρετική. Είπα, η δεύτερη πραγματικά σημαντική ερώτηση είναι, θέλουμε να επιλέξουμε ένα στοιχείο από ένα slicer, και τελικά τελικά το άλλο στοιχείο από το slicer, και μετά το άλλο στοιχείο από το slicer. Δεν χρειαζόμαστε συνδυασμούς όπως ANDY, και μετά ANDY και BETTY, και τότε ANDY και CHARLIE, σωστά; Αυτό είναι έξω. Απλώς πρόκειται να κάνω ένα στοιχείο από κάθε slicer. Ναι ναι ναι. Αυτός είναι ο τρόπος που θα πάει. Τέλεια, είπα. Εδώ λοιπόν, πείτε μου αυτό, επιλέξτε κάθε slicer, μεταβείτε στα SLICER TOOLS, OPTIONS και πηγαίνετε στο SLICER SETTINGS. Το κάναμε μόλις 2 επεισόδια πριν. Δεν είναι τρελό; ΟΝΟΜΑ ΠΟΥ ΧΡΗΣΙΜΟΠΟΙΕΙΤΑΙ ΣΤΙΣ ΦΟΡΜΟΥΛΕΣ και ξέρω ότι είναι SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,καλώς? Λοιπόν, νομίζω ότι το έχω.
Τώρα, θα μεταβούμε στο VBA εδώ και, παρεμπιπτόντως, βεβαιωθείτε ότι έχετε αποθηκευτεί ως xlsm και βεβαιωθείτε ότι η μακροεντολή ασφαλείας σας έχει ρυθμιστεί ώστε να επιτρέπει μακροεντολές. Εάν αποθηκευτεί ως xlsx, πιστέψτε με, πρέπει να κάνετε ένα FILE, SAVE AS, θα χάσετε όλη τη δουλειά σας εάν την αφήσετε ως xlsx. Ναι, το 99,9% των υπολογιστικών φύλλων που χρησιμοποιείτε είναι xlsx, αλλά αυτό με μακροεντολή δεν θα λειτουργήσει. ALT + F11. Εντάξει, οπότε εδώ είναι ο κωδικός.
Θα βρούμε τρεις κρυφές μνήμες slicer, ένα στοιχείο slicer και 3 εύρη. Για καθεμία από τις κρυφές μνήμες slicer, θα την ορίσουμε στο όνομα που χρησιμοποιείται στον τύπο που μόλις σας έδειξα στο παράθυρο διαλόγου SLICER SETTINGS. Έτσι, έχουμε τα τρία από αυτά. Θέλω να διαγράψω όλα αυτά για να βεβαιωθώ ότι επιστρέφουμε σε όλα όσα επιλέγονται. Αυτός ο μετρητής πρόκειται να χρησιμοποιηθεί στο όνομα του αρχείου αργότερα.
Καλώς. Τώρα, αυτή η επόμενη ενότητα εδώ, ΑΠΕΝΕΡΓΟΠΟΙΗΣΤΕ ΤΑ ΔΙΚΑΙΩΜΑΤΑ, ΚΑΤΑΣΚΕΥΗΣΤΕ ΤΡΙΤΕΣ ΣΤΑΤΙΣΤΙΚΕΣ ΛΙΣΤΕΣ ΟΛΩΝ ΤΩΝ ΕΙΔΩΝ ΤΟΥ SLICER. Δείτε την εκτέλεση # 2 για να δείτε γιατί έπρεπε να συμβεί αυτή η τρέλα. Οπότε θα καταλάβω πού βρίσκεται η επόμενη διαθέσιμη στήλη, το είδος της μετάβασης 2 από την τελευταία στήλη, θυμηθείτε ότι μπορώ να διαγράψω τα πράγματα αργότερα και, στη συνέχεια, για κάθε SI, slicer στοιχείο, IN SC1 πρόκειται να γράψουμε αυτήν την πιο λεπτή λεζάντα στο υπολογιστικό φύλλο. Όταν τελειώσουμε με όλα αυτά τα αντικείμενα slicer, υπολογίστε πόσες σειρές είχαμε σήμερα και, στη συνέχεια, ονομάστε το εύρος ως SLICERITEMS1. Θα επαναλάβουμε αυτό το όλο πράγμα για το slicer cache 2, ξεπερνώντας 1 στήλη, SLICERITEMS2 και SLICERITEMS3.
Επιτρέψτε μου να σας δείξω πώς φαίνεται σε αυτό το σημείο. Έτσι, θα βάλω ένα σημείο διακοπής εδώ και θα τρέξουμε αυτόν τον κώδικα. Καλώς. Αυτό ήταν γρήγορο. Θα μεταβούμε στο VBA, και πολύ μακριά στη δεξιά πλευρά, θα πάρω 3 νέες λίστες. Αυτές οι λίστες είναι οτιδήποτε υπάρχει στο slicer και βλέπετε ονομάζεται SLICERITEMS1, SLICERITEMS2 και SLICERITEMS3, εντάξει; Θα το ξεφορτωθούμε στο τέλος, αλλά αυτό μας δίνει κάτι για να περάσουμε. Επιστροφή στο VBA.
Καλώς. Θα βρούμε όλα τα στοιχεία του SLICERITEMS1, θα καθαρίσουμε το φίλτρο για την κρυφή μνήμη slicer 1 και μετά θα περάσουμε, ένα κάθε φορά, μέσω κάθε αντικειμένου slicer και θα δούμε αν αυτό το στοιχείο slicer είναι = σε αυτό CELL1.VALUE και, πάλι, περνάμε κάθε μία από τις τιμές. Έτσι, την πρώτη φορά, θα είναι ANDY και μετά BETTY και, ξέρετε, και ούτω καθεξής.
Είναι απογοητευτικό. Δεν μπορούσα να βρω τρόπο να απενεργοποιήσω όλους τους slicers ταυτόχρονα. Προσπάθησα ακόμη και να εγγράψω τον κωδικό και να επιλέξω έναν slicer, και ο εγγεγραμμένος κώδικας επέστρεψε 9 slicers και ενεργοποίησα τον slicer, εντάξει; Τόσο απογοητευτικό που δεν μπορούσα να βρω κάτι καλύτερο από αυτό, αλλά δεν μπορούσα να βρω κάτι καλύτερο από αυτό.
Έτσι, ορίσαμε το πρώτο slicer = σε ANDY. Στη συνέχεια περνάμε, και για το δεύτερο slicer, θα το θέσουμε = στο πρώτο στοιχείο. Για τον τρίτο slicer, ορίστε το = στο πρώτο στοιχείο.
Καλώς. Στη συνέχεια, εδώ, ΑΠΟΦΑΣΙΣΤΕ ΕΑΝ ΕΙΝΑΙ ΑΚΟΛΟΣ ΣΥΝΔΥΑΣΜΟΣ Πρέπει να σας εξηγήσω γιατί είναι σημαντικό. Αν εμείς, ως άνθρωποι το κάνουμε αυτό, ANDY, δεν θα επιλέξαμε το A52 γιατί είναι σαφές ότι είναι γκρίζο, αλλά η μακροεντολή θα είναι πολύ ηλίθια και θα επιλέξει το A52 και μετά το 104, και θα δημιουργήσει αυτό συγκεντρωτικός πίνακας. Έτσι, υπάρχουν χιλιάδες πιθανοί συνδυασμοί εδώ. Γνωρίζω ότι υπάρχουν μόνο 400 πιθανές αναφορές. Αυτό μου είπε το άτομο, και έτσι θα φτάσουμε 600 φορές όπου θα δημιουργήσουμε ένα PDF αυτής της (άσχημης - 04:45) αναφοράς.
Λοιπόν, αυτό που πρόκειται να κάνω είναι να κοιτάξω εδώ στην καρτέλα ANALYZE - ονομάστηκε OPTIONS το 2010 - και να δω ποιο είναι το όνομα αυτού του συγκεντρωτικού πίνακα και θέλω να δω πόσες σειρές παίρνουμε. Στην περίπτωσή μου, εάν λάβω 2 σειρές, ξέρω ότι είναι μια αναφορά που δεν θέλω να εξαγάγω. Αν λάβω περισσότερες από 2 σειρές, 3, 4, 5, 6, τότε ξέρω ότι είναι μια αναφορά που θέλω να εξαγάγω. Θα πρέπει να καταλάβετε στην περίπτωσή σας ποια είναι.
Καλώς. Έτσι, γι 'αυτό ελέγχουμε για να δούμε αν ο συγκεντρωτικός πίνακας 2 και, αυτό είναι το όνομα που βρισκόταν εκεί πίσω στην κορδέλα, .TABLERANGE2.ROWS.COUNT είναι> 2. Εάν δεν είναι> 2, δεν θέλουμε να δημιουργία PDF, εντάξει; Έτσι, αυτή η δήλωση IF έως αυτό το END IF λέει ότι θα δημιουργήσουμε μόνο τα PDF για συνδυασμούς αναφορών που έχουν τιμές. MYFILENAME, δημιούργησα ένα φάκελο με το όνομα C: REPORTS. Είναι απλώς ένας άδειος φάκελος. Γ: ΕΚΘΕΣΕΙΣ. Βεβαιωθείτε ότι έχετε έναν φάκελο και χρησιμοποιείτε το ίδιο όνομα φακέλου στη μακροεντολή. C: REPORTS / και το όνομα του αρχείου θα είναι REPORT001.PDF. Τώρα, μετρητής αρχικοποιήσαμε αντίγραφα ασφαλείας υπάρχει 1 χρησιμοποιώντας το FORMAT, το οποίο ισοδυναμεί στο Excel με το να λέει το κείμενο του μετρητή, και 000. Με αυτόν τον τρόπο, θα πάρω 001, μετά 002, έπειτα 003 και έπειτα 004. πρόκειται να ταξινομήσετε σωστά.Αν μόλις το είχα ονομάσει REPORT1, και αργότερα έχω ένα REPORT10 και 11, και αργότερα στο REPORT100, όλα αυτά θα τακτοποιηθούν όταν δεν ανήκουν μαζί, εντάξει; Έτσι, δημιουργώντας το όνομα του αρχείου σε περίπτωση που το αρχείο υπάρχει από την τελευταία φορά που το τρέξαμε, θα το σκοτώσουμε. Με άλλα λόγια, διαγράψτε το. Φυσικά, αν προσπαθήσετε να σκοτώσετε ένα αρχείο που δεν είναι εκεί, θα ρίξει ένα σφάλμα. Έτσι, εάν λάβουμε ένα λάθος στην επόμενη γραμμή, αυτό είναι εντάξει. Απλώς συνεχίστε, αλλά στη συνέχεια επαναφέραμε το σφάλμα ελέγχοντας το ON ERROR GOTO 0.Φυσικά, αν προσπαθήσετε να σκοτώσετε ένα αρχείο που δεν είναι εκεί, θα ρίξει ένα σφάλμα. Έτσι, εάν λάβουμε ένα λάθος στην επόμενη γραμμή, αυτό είναι εντάξει. Απλώς συνεχίστε, αλλά στη συνέχεια επαναφέραμε το σφάλμα ελέγχοντας το ON ERROR GOTO 0.Φυσικά, αν προσπαθήσετε να σκοτώσετε ένα αρχείο που δεν είναι εκεί, θα ρίξει ένα σφάλμα. Έτσι, εάν λάβουμε ένα λάθος στην επόμενη γραμμή, αυτό είναι εντάξει. Απλώς συνεχίστε, αλλά στη συνέχεια επαναφέραμε το σφάλμα ελέγχοντας το ON ERROR GOTO 0.
Εδώ είναι το ΕΝΕΡΓΟ ΦΥΛΛΟ, ΕΞΑΓΩΓΗ ΩΣ ΔΙΟΡΘΩΜΕΝΗ ΜΟΡΦΗ, ως PDF, υπάρχει το όνομα του αρχείου, όλες αυτές οι επιλογές και μετά αυξάνω τον μετρητή, έτσι ώστε, την επόμενη φορά που θα βρούμε ένα που έχει εγγραφές, θα δημιουργήσουμε το REPORT002.PDF . Ολοκληρώστε αυτούς τους τρεις βρόχους και, στη συνέχεια, ΚΑΘΑΡΙΣΤΕ ΤΙΣ ΣΤΑΤΙΚΕΣ ΛΙΣΤΕΣ. Θα θυμάμαι λοιπόν ποια στήλη ήμασταν, αλλάξτε το μέγεθος 1 σειράς, 3 στήλες, ENTIRECOLUMN.CLEAR και έπειτα ένα ωραίο μικρό πλαίσιο μηνυμάτων εκεί για να δείξετε ότι τα πράγματα έχουν δημιουργηθεί. Εντάξει. Ας το τρέξουμε.
Καλώς. Τώρα, αυτό που πρέπει να συμβαίνει εδώ είναι αν πάμε και κοιτάξουμε στην Εξερεύνηση των Windows, αυτό είναι. Εντάξει. Δημιουργεί… όπως, κάθε δευτερόλεπτο, παίρνουμε 2 ή 3 ή 4 ή περισσότερα. Θα το σταματήσω και θα το αφήσω να τρέξει. Καλώς. Εδώ είμαστε. Δημιουργήθηκαν 326 αναφορές. Περιέλαβε και τις 1000 δυνατότητες και κράτησε μόνο εκείνες όπου υπήρχε πραγματικό αποτέλεσμα. Εντάξει, από τις 9:38 έως τις 9:42, 4 λεπτά για να κάνουμε όλα αυτά, αλλά ακόμα πιο γρήγορα από ό, τι το 400, εντάξει;
Καλώς. Έτσι, αυτός είναι ο μακροοικονομικός τρόπος για να γίνει αυτό. Το άλλο πράγμα που με χτύπησε εδώ ότι μπορεί να λειτουργήσει ή όχι. Είναι πολύ δύσκολο να το πούμε. Ας πάρουμε τα δεδομένα μας και θα μεταφέρω τα δεδομένα σε ένα ολοκαίνουργιο βιβλίο εργασίας. ΚΙΝΗΣΗ Ή ΑΝΤΙΓΡΑΦΗ, ΔΗΜΙΟΥΡΓΙΑ ΑΝΤΙΓΡΑΦΗΣ, σε ΝΕΟ ΒΙΒΛΙΟ, κάντε κλικ στο OK και εδώ θα χρησιμοποιήσουμε ένα κόλπο που έμαθα για πρώτη φορά από τη Szilvia Juhasz - έναν σπουδαίο σύμβουλο του Excel στη Νότια Καλιφόρνια - και πρόκειται να προσθέστε ένα πεδίο KEY εδώ. Το πεδίο ΚΛΕΙΔΙ είναι = ΕΡΕΥΝΗΤΗ & ΚΕΝΤΡΟ & ΠΕΙΘΑΡΙΣΜΟΣ. Θα το αντιγράψουμε και θα εισάγουμε έναν νέο συγκεντρωτικό πίνακα. Κάντε κλικ στο OK και θα πάρουμε αυτό το πεδίο, το πεδίο KEY, και θα το μεταφέρουμε στα παλιομοδίτικα ΦΙΛΤΡΑ και, στη συνέχεια, ας το δούμε. (Ας διαλύσουμε μια μικρή αναφορά εδώ με - 08:30) ΕΛΕΓΧΟΣ, ΚΕΝΤΡΟ, ΠΕΙΘΑΡΙΣΜΟΣ και ΕΣΟΔΑ, έτσι.
Alright, now, normally what we would do here is would come open this filter and choose one item from the filter, but the trick from Szilvia is that we can take this pivot table and go to either the ANALYZE tab in ’13 or ’16, or the OPTIONS tab in 2010, open the OPTIONS dropdown, say SHOW REPORT FILTER PAGES, SHOW ALL PAGES OF KEY, and what it's doing right now is it’s inserting a new worksheet for every unique combination of the KEY, probably 300 and some files, alright? Now, how many worksheets can you have in a workbook? Well, that number is different on every computer and it depends on how complicated the workbook is because it's limited by available memory, but here we start on ANDY B37 112. I’m going to press CONTROL and this arrow down to JOE, like that.
The beautiful advantage here is, when I do FILE, EXPORT, CREATE A PDF, and then ALLREPORTS, we’re going to end up with a single PDF with all 326 reports in it. Now, we could have created a single PDF using Adobe Acrobat, select all of these reports, right click, and COMBINE FILES IN ACROBAT, but that requires you have a full version of Acrobat, not just Acrobat Reader.
So, this great trick using SHOW REPORT FILTER PAGES from Szilvia might be a great, great alternative if you have enough memory to create all the versions.
Alright. To learn more about VBA, check out this book Excel 2016 VBA And Macros by Bill Jelen and Tracy Syrstad. That will get you up the VBA learning curve.
Alright. The goal is to loop through all combinations in 3 slicers and generate a PDF for each. Used a little VBA to loop through those slicers. Save as PDF using VBA. The alternate solution there at the end is Szilvia Juhasz’s SHOW REPORT FILTER PAGES and then export the whole thing as PDF.
Hey. I want to thank you for stopping by. We'll see you next time for another netcast from.
Well, this will be an outtake. First time I ran this darn thing, I got a 1000 of them, and every darn one of them was Andy A52 104. I'm like what the heck is going on? Except I didn’t say heck.
Alright. So, here, watch this code. This was the code I had. I said I'm going to go through all of the filters FOR EACH SI IN SC1.SLICERITEMS and then I set it = to FALSE, and then the one that I want, I'll set = to TRUE, right? Sound like a great, great bit of code, alright?
So, here's what happens. The first one is Andy, goes away. Betty goes away. Charlie goes away. Dale. Here, I'll just keep pressing F8, F8, F8, F8. I'm down to the last one. This is JOE. I'm about to set JOE = to FALSE and watch what happens over there in Excel. Bam. Once you turn JOE off, it turns them all back on. I mean, that stinks, Excel, and then I would try and turn, what is it, ANDY back on and turning ANDY back on when everybody else is already on. So, it ran through… it created a 1000 of the PDFs, every stinking one. It was ANDY A52 104. It's funny now. It wasn't then.
Alright. Here’s another outtake. Why did I go to the trouble of building the list, the static list, off to the right hand side so I can loop through that static list? Well, originally, I was looping through all of the items in the slicers themselves and it was causing some wrong results. See, here, Andy A52 112 should be 0, but when I actually ran the loop, ANDY A52 112 is showing up with six rows. I’m like, well, that can't be. So, over here, my code, ALT+F11, I put a thing, if SI1.CAPTION=ANDY, SI2.CAPTION=A52, SI3 CAPTION=112, THEN STOP, right? So let's run this code, then stop.
There we are, and I will come back. We should have ANDY A52 112, but when I look, ANDY, it’s not A52, it’s D33. What the heck is going on, and then I come back here, ALT+F11, and I right-click and say that I want to ADD A WATCH, and when I look at this, it claims that the caption is A52 but, very clearly, it's D33. So, is this a bug or am I just violating some weird rule by looping through a collection of 10 items when the order of those 10 items is constantly being reordered? It seems like that must be the problem. Hence, we went with the static list off to the right.
Και το τρίτο αποτέλεσμα, εντάξει; Αυτό είναι τρελό. Αν θέλω να εγγράψω μια μακροεντολή, αν θέλω (γράψτε μια μακροεντολή - 13:35) για να επιλέξετε μόνο ένα στοιχείο, μάθετε πώς να το κάνετε χρησιμοποιώντας DEVELOPER, RECORD MACRO, HOWTOCHOOSEONEITEMFROMSLICER, κάντε κλικ στο OK και απλά επιλέγουμε ένα είδος. FLO. Κάντε κλικ στο STOP RECORDING, μετά πηγαίνουμε ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER, Επεξεργαστείτε αυτό και, βεβαίως, κάνουν το FLO TRUE και μετά σε όλους τους άλλους FLASE. Αυτό σημαίνει ότι αν είχα ένα slicer με 100 αντικείμενα σε αυτό, θα έπρεπε να βάλουν 100 γραμμές κώδικα εκεί για να αποεπιλέξουν τα υπόλοιπα. Φαίνεται απίστευτα αναποτελεσματικό, αλλά είστε.
Λήψη αρχείου
Κατεβάστε το δείγμα αρχείου εδώ: Podcast2106.xlsx
