Οι Slicers είναι καταπληκτικοί για συγκεντρωτικούς πίνακες, επειδή μπορείτε να ελέγχετε πολλούς συγκεντρωτικούς πίνακες από ένα σύνολο slicers. Αλλά - αυτό είναι ένα ψέμα. Μπορείτε να ελέγξετε πολλούς συγκεντρωτικούς πίνακες που προέρχονταν από το ίδιο σύνολο δεδομένων. Όταν έχετε συγκεντρωτικούς πίνακες που προέρχονται από δύο διαφορετικά σύνολα δεδομένων, είναι αρκετά δύσκολο. Θα σας δείξω κάποια VBA που θα σας αφήσει να το κάνετε.
Δες το βίντεο
- Πώς μπορείτε να έχετε ένα slicer drive δύο περιστρεφόμενους πίνακες;
- Εάν και οι δύο συγκεντρωτικοί πίνακες προέρχονταν από το ίδιο σύνολο δεδομένων: Επιλέξτε Slicer, Report Connections, Select Other Pivot Tables
- Αλλά αν οι συγκεντρωτικοί πίνακες προέρχονταν από διαφορετικά σύνολα δεδομένων:
- Χρησιμοποιήστε το Save As για να αλλάξετε την επέκταση του βιβλίου εργασίας σε XLSM αντί για το XLSX
- Χρησιμοποιήστε το alt = "" + TMS και αλλάξτε την ασφάλεια μακροεντολών σε δεύτερη ρύθμιση.
- Alt + F11 για να φτάσετε στο VBA
- Ctrl + R για εμφάνιση του προγράμματος εξερεύνησης
- Βρείτε το φύλλο εργασίας που περιέχει τον πρώτο συγκεντρωτικό πίνακα και το slicer
- Εισαγάγετε τον κωδικό για το Worksheet_Update
- Απόκρυψη του δεύτερου slicer μακριά, έτσι ώστε να υπάρχει ήδη, αλλά κανείς δεν μπορεί ποτέ να επιλέξει από αυτό το slicer
Μεταγραφή βίντεο
Μάθετε το Excel για Podcast, επεισόδιο 2104: Συγχρονισμός Slicers από διαφορετικά σύνολα δεδομένων.
Γεια σας, καλώς ήλθατε πίσω στο netcast, είμαι ο Bill Jelen και η σημερινή ερώτηση δεν αφορά πώς να πάρετε αυτούς τους δύο συγκεντρωτικούς πίνακες που προέρχονταν από ένα σύνολο δεδομένων και να κάνετε τον Slicer να ελέγχει όλους αυτούς τους συγκεντρωτικούς πίνακες. Δεν είναι αυτό. Αυτό είναι εύκολο να κάνετε - Slicer, Tools, Options, είτε Report Connections ή Slicer Connections στην παλιά έκδοση και ελέγξτε ότι θέλετε αυτός ο Slicer να ελέγχει όλους αυτούς τους συγκεντρωτικούς πίνακες. Εύκολο, σωστά; Αυτή η ερώτηση αφορά αυτό το φύλλο εργασίας, όπου έχουμε δύο διαφορετικά σύνολα δεδομένων και πρόκειται να δημιουργήσουμε έναν συγκεντρωτικό πίνακα από αυτό και από αυτό - τώρα επιτρέψτε μου να επιταχύνω το βίντεο ενώ δημιουργώ αυτούς τους συγκεντρωτικούς πίνακες. Εντάξει, τώρα, αυτό που πρόκειται να δείτε είναι ότι έχω δύο συγκεντρωτικούς πίνακες, αυτός ο συγκεντρωτικός πίνακας δημιουργήθηκε από ένα σύνολο δεδομένων και υπάρχει ένα slicer που ελέγχει αυτόν τον συγκεντρωτικό πίνακα.και έπειτα έχω έναν δεύτερο συγκεντρωτικό πίνακα που έχει δημιουργηθεί από ένα διαφορετικό σύνολο δεδομένων και έναν slicer που ελέγχει αυτόν τον συγκεντρωτικό πίνακα. Αλλά δεν υπάρχει απολύτως τρόπος να αποκτήσετε αυτό το slicer για να ελέγξετε τόσο αυτόν τον συγκεντρωτικό πίνακα όσο και αυτόν τον συγκεντρωτικό πίνακα που έχει δημιουργηθεί από ένα διαφορετικό σύνολο δεδομένων. Καλώς. Αλλά θα σας δείξω πώς να το κάνετε αυτό σήμερα με μια μακροεντολή.
Τώρα, αυτό είναι δύσκολο να το κάνετε. Όταν ήρθε η ερώτηση, είπα, "Τώρα, αυτό, δεν νομίζω ότι μπορείτε να το κάνετε." Αλλά το έχω εργαστεί και πειραματίζομαι και πιστεύω ότι τελικά το κατάλαβα. Πρέπει να σκεφτώ ότι τελικά το κατέβασα. Εντάξει, οπότε ας περάσουμε από αυτό. Πρώτα απ 'όλα, αυτό αποθηκεύεται ως αρχείο xlsx. Αυτός είναι ένας καλός τύπος αρχείου, εκτός από το ότι είναι ένας φρικτός τύπος αρχείου επειδή είναι ο μόνος τύπος αρχείου που δεν επιτρέπει μακροεντολές. Πρέπει να το αλλάξετε από xlsx σε xlsm, ή όλη η δουλειά σας στο υπόλοιπο βίντεο θα πεταχτεί έξω από το παράθυρο. Αποθηκεύστε ως, αλλάξτε τον τύπο αρχείου σε xlsm ή, heck, xlsb, οποιοδήποτε από αυτά θα λειτουργήσει. Αυτό είναι σπασμένο - xlsx - και είναι η προεπιλογή, τρελό έτσι δεν είναι; Xlsm, κάντε κλικ στην Αποθήκευση. Εάν δεν έχετε κάνει ποτέ μακροεντολές, Alt + T για Tom, M για Macro,S για Ασφάλεια και θα μπορείτε να αποθηκεύσετε όλες τις μακροεντολές χωρίς ειδοποίηση. Πρέπει να το αλλάξετε σε δεύτερο, που θα επιτρέψει στις μακροεντολές σας να λειτουργήσουν.
Εντάξει, τώρα έχουμε δύο slicers. Σίγουρα δεν το ξέρατε ποτέ, αλλά οι slicers έχουν ονόματα. Θα μεταβούμε στα Slicer Tools, Options, Slicer Settings και θα δούμε αυτό που ονομάζεται Slicer_Name. Σαν αυτό. Μεταβείτε στο δεύτερο, μεταβείτε στα Εργαλεία Slicer, Επιλογές, Ρυθμίσεις Slicer, αυτό ονομάζεται Slicer_Name1 - όχι Όνομα space 1, Name1. Δύο ονόματα σαν αυτό.
Να τι θα κάνουμε. Θα μεταβούμε στο VBA - Alt + F11. Στο VBA, εάν δεν έχετε κάνει ποτέ VBA, θα έχετε αυτήν τη μεγάλη γκρι οθόνη. Θα έρθουμε εδώ και θα πούμε Προβολή, Project Explorer, στον Project Explorer να βρει το αρχείο σας - το δικό μου ονομάζεται Podcast 2104. Ανοίξτε το Microsoft Excel Objects και το φύλλο στο οποίο θέλω να λειτουργεί αυτό ονομάζεται Dashboard Θα κάνω δεξί κλικ εκεί και θα πω Προβολή κώδικα. Αυτός ο κώδικας που γράφουμε δεν μπορεί να πάει σε μια ενότητα όπως σε μια κανονική μακροεντολή - αυτό πρέπει να βρίσκεται σε αυτό το φύλλο εργασίας. Ανοίξτε το επάνω αριστερό αναπτυσσόμενο μενού, φύλλο εργασίας και, στη συνέχεια, στο επάνω δεξί αναπτυσσόμενο μενού, θα πούμε Pivot Table Update. Εντάξει, οπότε εδώ θα πάει ο κώδικας μας. Έχω ήδη προ-ψηφίσει αυτόν τον κωδικό. Ας ρίξουμε μια ματιά στον κώδικα εδώ στο σημειωματάριο. Λοιπόν, εμείςθα έχουμε δύο κρυφές μνήμες Slicer - SC1 και SC2 - ένα στοιχείο Slicer και στη συνέχεια, εδώ, εδώ θα πρέπει να το προσαρμόσετε. Έτσι, οι δύο Slicers μου ονομάστηκαν Όνομα και Όνομα1. Εντάξει, θα πρέπει να βάλετε εκεί τα slicer ονόματά σας. Application.Screenupdating = False, Application.EnableEvents = False και, στη συνέχεια, Slicer Cache 2 - θα διαγράψουμε το φίλτρο και, στη συνέχεια, για κάθε στοιχείο SI1 και sc1.SlicerItems, εάν είναι επιλεγμένο, τότε θα κάνουμε το ίδιο στοιχείο στο Slicer Cache που θα επιλεγεί. Αυτός είναι ένας μικρός βρόχος που θα τρέξει, ωστόσο πολλά αντικείμενα τυχαίνει να βρίσκονται σε αυτό το slicer. Στην περίπτωσή μου, έχω 11 ή 12. στην περίπτωσή σας, μπορεί να έχετε περισσότερα.Έτσι, οι δύο Slicers μου ονομάστηκαν Όνομα και Όνομα1. Εντάξει, θα πρέπει να βάλετε τα ονόματά σας. Application.Screenupdating = False, Application.EnableEvents = False και, στη συνέχεια, Slicer Cache 2 - θα διαγράψουμε το φίλτρο και, στη συνέχεια, για κάθε στοιχείο SI1 και sc1.SlicerItems, εάν είναι επιλεγμένο, τότε θα κάνουμε το ίδιο αντικείμενο στο Slicer Cache που θα επιλεγεί. Αυτός είναι ένας μικρός βρόχος που θα τρέξει, ωστόσο πολλά αντικείμενα τυχαίνει να βρίσκονται σε αυτό το slicer. Στην περίπτωσή μου, έχω 11 ή 12. στην περίπτωσή σας, μπορεί να έχετε περισσότερα.Έτσι, οι δύο Slicers μου ονομάστηκαν Όνομα και Όνομα1. Εντάξει, θα πρέπει να βάλετε τα ονόματά σας. Application.Screenupdating = False, Application.EnableEvents = False και, στη συνέχεια, Slicer Cache 2 - θα διαγράψουμε το φίλτρο και, στη συνέχεια, για κάθε στοιχείο SI1 και sc1. το ίδιο αντικείμενο στο Slicer Cache που θα επιλεγεί. Αυτός είναι ένας μικρός βρόχος που θα τρέξει, ωστόσο πολλά αντικείμενα τυχαίνει να βρίσκονται σε αυτό το slicer. Στην περίπτωσή μου, έχω 11 ή 12. στην περίπτωσή σας, μπορεί να έχετε περισσότερα.πρόκειται να φτιάξετε το ίδιο αντικείμενο στο Slicer Cache που θα επιλεγεί. Αυτός είναι ένας μικρός βρόχος που θα τρέξει, ωστόσο πολλά αντικείμενα τυχαίνει να βρίσκονται σε αυτό το slicer. Στην περίπτωσή μου, έχω 11 ή 12. στην περίπτωσή σας, μπορεί να έχετε περισσότερα.πρόκειται να φτιάξετε το ίδιο στοιχείο στο Slicer Cache που θα επιλεγεί. Αυτός είναι ένας μικρός βρόχος που θα τρέξει, ωστόσο πολλά αντικείμενα τυχαίνει να βρίσκονται σε αυτό το slicer. Στην περίπτωσή μου, έχω 11 ή 12. στην περίπτωσή σας, μπορεί να έχετε περισσότερα.
Όταν τελειώσουμε με αυτό, ενεργοποιήστε ξανά τα συμβάντα ενεργοποίησης, ενεργοποιήστε ξανά την ενημέρωση οθόνης. Καλώς. Έτσι, θα πάρουμε αυτόν τον κώδικα, θα αντιγράψουμε αυτόν τον κώδικα και θα τον επικολλήσουμε εδώ στη μέση της μακροεντολής μας έτσι. Εντάξει, τώρα, ας βεβαιωθούμε ότι θα πατήσω το Ctrl + G και το ερώτημά μου είναι Εφαρμογή. Ενεργοποίηση συμβάντων, ενεργοποίηση ή απενεργοποίηση - έτσι,; Application.EnableEvents - και είναι αλήθεια. Εάν η δική σας εμφανιστεί ως ψευδής, τότε θέλετε να επιστρέψετε εδώ και να πείτε ότι είναι = Αλήθεια - έτσι, λοιπόν, ενεργοποιείτε αυτά τα συμβάντα Καλώς. Τώρα, εδώ θα συμβεί. Οπότε ο προπονητής μας πρέπει να εργάζεται εδώ, είναι στο σωστό φύλλο εργασίας. Είμαστε αποθηκευμένοι σε ένα αρχείο xlxm και ενεργοποίησα τις μακροεντολές και αυτό που πρόκειται να δούμε, είναι ότι όταν επιλέγω από το αριστερό Slicer, αυτό το Slicer Cache 1-- I "Θα επιλέξω τον Andy μέσω της Della - το άλλο Slicer πρόκειται να ενημερώσει επίσης. Εντάξει Και ακόμη κι αν θα επέλεγα μόνο Gloria - απλά Gloria - φαίνεται ότι λειτουργεί πολύ καλά. Ακόμα κι αν κάνω CTRL + κλικ, όταν αφήνω το Ctrl, και οι τρεις θα ενημερώσουν.
Αλλά εδώ είναι το gotcha - υπάρχει πάντα ένα gotcha - αυτό το Slicer, πρέπει να υπάρχει, αλλά δεν μπορείτε να χρησιμοποιήσετε αυτό το Slicer - περιμένετε, εννοώ ότι μπορείτε, μπορείτε να χρησιμοποιήσετε ένα Slicer, αλλά πρόκειται να μπερδέψετε τα πράγματα . Επειδή αυτό που πρόκειται να συμβεί είναι να το αλλάξω σε Hank και πρόκειται να επιστρέψουν σε ό, τι υπάρχει στο Slicer Cache 1, γιατί άλλαξα τον συγκεντρωτικό πίνακα σε αυτό το φύλλο. Τώρα, στην πραγματική ζωή, θα έχετε δύο περιστρεφόμενους πίνακες στο ίδιο φύλλο; Δεν ξέρω αν είστε ή αν δεν είστε, εντάξει, αλλά τα πράγματα θα γίνουν λίγο τρελά.
Now, let's just take a look at this. First thing I want to do, is I'm going to insert a new worksheet-- Alt+IW for inserting the worksheet-- and I'm going to call this a DarkCave. You can call it whatever you want. I'm going to take that dashboard that's not going to work, I'm going to copy that dashboard and come here to the dark cave and paste it there and then right click and hide that sheet so no one ever sees that Slicer. And then, from here, we should be able to delete it. Nice, alright. And we're going to just check to make sure they're still working-- choose Charlie through Eddie and they're both still updating. Now, what's happening? The Slicer that we can't see, the one that we've hidden away, it's updating as well, but we don't care that it's updating.
Now, what if you want to have your things on different sheets? I'll insert a new worksheet here--Alt+IW-- and I'll take one of these pivot tables-- maybe the second pivot table-- and move it to that other sheet-- so, Ctrl+C to copy the pivot table, Ctrl+V to paste the pivot table here. And if I need to have a slicer here-- don't insert a slice from this pivot table-- we have to come back to our dashboard, take the slicer that's the controlling Slicer, Ctrl+C to make a copy of it, and paste it here-- Ctrl+V. Alright? Now, we have no code on this sheet-- there's no code on Sheet4-- and I was thinking I was going to have to add some code to Sheet4, but here's the beautiful thing: When I change this slicer, what's happening is, on the dashboard that pivot table's updating even though that pivot table on that sheet that's not active is updating, they will run the code and this will update as well. Pretty darn amazing that that works.
Now, the whole key to this is, you can never use the slicer tied to the second pivot table. You have to have the slicer that's tied to the second pivot table but you cannot use it-- you have to use this slicer tied to the first pivot table. Alright? But in general, I think this is working fairly well.
Alright, now hey, Sal, the person who asks this question, wrote in and said, "Look, I have a disconnected pivot table-- disconnected slicer in the second pivot table only." So let's just add a new field here called Region, East, West, we'll refresh our second pivot table, cool, and I'll insert a slicer that is disconnected-- in other words, it's only in the second data set, not in the first data set, Alright, now, this is going to be tricky because when I choose East from here, we're not going to have anybody selected. Alright? So the pivot table goes away. I would have to clear this slicer on the left hand side and then East remains selected. And now things have gone to hell, right? So, you are choosing from a slicer tied to the second data set and, while the second one is updating, the first one is not going to respect that because it has no idea there's no region filled back in the other field. This is only going to work when you have the same field in both data sets. If you have some other situation like this, then it will not fly.
So here's what you're going to have to do: You're going to have to insert that field-- the Region field-- back in your original data set, refresh this pivot table, insert a new slicer that will control that first pivot table. Alright? Now, we have two different slicers now, and because I built them backwards their names are backwards-- this one's Slicer_Region 1, and the one that's going to be the controlling one is called Slicer_Region2. If I would have planned differently, we would have had a different result, but here we are. Alt+F11, I want to take a lot of cutting and pasting. I'm going to take those first three lines and paste them, change it to SlicerCache3, SlicerCache4, SlicerItem3. I'll initialize SlicerCache3, ClicerCache4 to be Region2, Region1, clear the manual filter on SC4-- so that was a copy and paste, take this entire loop here and paste it. There are a lot of places you have to change-- your SI3, SC3 and then SC4, SI3. SI3-- don't miss that one, I missed that one-- next SI3. Alright, so now this set of code will hopefully control two sets of slicers. If you had a third set of slicers you're going to do the same changes I just made, copying and pasting and changing things carefully. Carefully. And again, now, this this guy is the one that we will never see-- we never want to see that one work-- because the ones on the Pivot Table 1 are the controlling ones. So this, we have to copy this-- Ctrl+C-- go to our sheet where we're hiding things away-- so Home, Format, Hide and Unhide, Unhide that sheet (the DarkCave), Paste so it continues to exist, it has to live somewhere, and then once I know it's back there on the DarkCave I can delete it and then hide this sheet here. Alright, and so now we should have on our dashboard, one set of slicers, we choose Central, they both update; we choose Just Flow, they both update; I clear the filter and Central stays. That's actually good. I'm glad that works-- clear this filter and everybody comes back. But these all have to be driving off that first pivot table. What if you have a field in the second data set that's not in your first data set? Then all bets are off. We'll go back to "I don't know how to solve that".
Well, hey, Macros came to the solution today and Macros are amazing and awesome. If you want to learn all about Macros, Tracy Syestad and I have written this great book, "Excel 2016, VBA and Macros." Check that out, Click the "I" on the top right hand corner to get to a page where you can buy that book.
Alright, Episode recap. How can you have a slicer drive two pivot tables? If they both came from the data set it's simple-- Slicer, Report Connections, Choose Other pivot tables. But if a pivot table came from two data sets, lots of steps change-- xlsx to xlsm, change your macro security setting, Alt+F11 to get the VBA, Ctrl+R to display the Project Explorer, find the worksheet name that contains your first pivot table and slicer, right-click and say View Code, and then Insert code for worksheet, Update, then, really important, Hide that second slicer away on a hidden worksheet or far out to the right so no one can ever choose from that slicer. By the way, don't cut that slicer-- you have to copy it and paste and then delete the first one in order to get it to work.
Θέλετε να σας ευχαριστήσω που σταματήσατε, θα σας δούμε την επόμενη φορά για ένα άλλο netcast από.
Λήψη αρχείου
Κατεβάστε το δείγμα αρχείου εδώ: Podcast2104.xlsm
