Slicer eignen sich hervorragend für Pivot-Tabellen, da Sie mehrere Pivot-Tabellen von einem Slicer-Satz aus steuern können. Aber - das ist eine Art Lüge. Sie können mehrere Pivot-Tabellen steuern, die aus demselben Datensatz stammen. Wenn Sie Pivot-Tabellen haben, die aus zwei verschiedenen Datensätzen stammen, ist dies ziemlich schwierig. Ich werde Ihnen einige VBA zeigen, mit denen Sie dies durchziehen können.
Schau Video
- Wie kann ein Slicer zwei Pivot-Tische antreiben?
- Wenn beide Pivot-Tabellen aus demselben Datensatz stammen: Wählen Sie Slicer, Berichtsverbindungen, Andere Pivot-Tabellen auswählen
- Aber wenn die Pivot-Tabellen aus verschiedenen Datensätzen stammen:
- Verwenden Sie Speichern unter, um die Arbeitsmappenerweiterung in XLSM anstelle von XLSX zu ändern
- Verwenden Sie alt = "" + TMS und ändern Sie die Makrosicherheit in die zweite Einstellung.
- Alt + F11, um zu VBA zu gelangen
- Strg + R, um den Projektexplorer anzuzeigen
- Suchen Sie das Arbeitsblatt, das Ihre erste Pivot-Tabelle und Ihren Slicer enthält
- Geben Sie den Code für Worksheet_Update ein
- Verstecken Sie den zweiten Slicer, damit er vorhanden bleibt, aber niemand kann jemals aus diesem Slicer auswählen
Video-Transkript
Lernen Sie Excel für Podcast, Episode 2104: Synchronisieren Sie Slicer aus verschiedenen Datensätzen.
Hey, willkommen zurück im Netcast, ich bin Bill Jelen, und in der heutigen Frage geht es nicht darum, wie diese beiden Pivot-Tabellen, die aus einem Datensatz stammen, vom Slicer alle diese Pivot-Tabellen steuern können. Darum geht es nicht. Dies ist ganz einfach: Slicer, Tools, Optionen, entweder Berichtsverbindungen oder Slicer-Verbindungen in der alten Version, und überprüfen Sie, ob dieser Slicer alle diese Pivot-Tabellen steuern soll. Einfach richtig? Diese Frage bezieht sich auf dieses Arbeitsblatt, in dem wir zwei verschiedene Datensätze haben und daraus eine Pivot-Tabelle erstellen. Daraus kann ich nun das Video beschleunigen, während ich diese Pivot-Tabellen erstelle. Okay, jetzt werden Sie sehen, ich habe zwei Pivot-Tabellen, diese Pivot-Tabelle wird aus einem Datensatz erstellt, und es gibt einen Slicer, der diese Pivot-Tabelle steuert.und dann habe ich eine zweite Pivot-Tabelle, die aus einem anderen Datensatz erstellt wurde, und einen Slicer, der diese Pivot-Tabelle steuert. Es gibt jedoch absolut keine Möglichkeit, diesen Slicer dazu zu bringen, sowohl diese Pivot-Tabelle als auch diese Pivot-Tabelle zu steuern, die aus einem anderen Datensatz erstellt wurden. In Ordung. Aber ich werde Ihnen heute zeigen, wie das mit einem Makro geht.
Das ist schwierig. Als die Frage kam, sagte ich: "Nun, ich glaube nicht, dass du es schaffen kannst." Aber ich habe daran gearbeitet und experimentiert und ich glaube, ich habe es endlich verstanden. Ich muss denken, dass ich es endlich geschafft habe. Okay, also lass uns das durchgehen. Zunächst wird dies als XLSX-Datei gespeichert. Das ist ein guter Dateityp, außer dass es ein schrecklicher Dateityp ist, weil es der einzige Dateityp ist, der keine Makros zulässt. Sie müssen dies von xlsx in xlsm ändern, oder Ihre gesamte Arbeit für den Rest des Videos wird aus dem Fenster geworfen. Speichern unter, ändern Sie den Dateityp in xlsm oder, zum Teufel, xlsb, einer davon funktioniert. Das ist derjenige, der kaputt ist - xlsx - und es ist die Standardeinstellung, verrückt, nicht wahr? Xlsm, klicken Sie auf Speichern. Wenn Sie noch nie Makros erstellt haben, Alt + T für Tom, M für Makro,S for Security und Sie können alle Makros ohne Benachrichtigung speichern. Sie müssen dies in das zweite ändern, damit Ihre Makros funktionieren.
Okay, jetzt haben wir zwei Slicer. Ich wette, Sie haben das nie gewusst, aber Slicer haben Namen. Wir gehen zu Slicer-Tools, Optionen, Slicer-Einstellungen und sehen, dass dieser Slicer_Name heißt. So wie das. Gehen Sie zum zweiten, gehen Sie zu Slicer-Tools, Optionen, Slicer-Einstellungen, diese heißt Slicer_Name1 - nicht Namensraum 1, Name1. Zwei solche Namen.
Folgendes werden wir tun. Wir werden auf VBA - Alt + F11 umschalten. Wenn Sie in VBA noch nie VBA durchgeführt haben, wird dieser große graue Bildschirm angezeigt. Wir werden hierher kommen und sagen, Ansicht, Projektexplorer, im Projektexplorer finden Sie Ihre Datei - meine heißt Podcast 2104. Öffnen Sie Microsoft Excel-Objekte, und das Blatt, in dem dies funktionieren soll, heißt Dashboard. Ich werde dort mit der rechten Maustaste klicken und Code anzeigen sagen. Dieser Code, den wir schreiben, kann nicht wie in einem normalen Makro in ein Modul eingefügt werden - dies muss in diesem Arbeitsblatt enthalten sein. Öffnen Sie das Dropdown-Menü oben links, Arbeitsblatt, und im Dropdown-Menü oben rechts wird Pivot Table Update angezeigt. Okay, hier wird unser Code jetzt hingehen. Ich habe diesen Code bereits vorgebacken. Werfen wir einen Blick auf den Code hier im Editor. Also, wir 'Sie werden zwei Slicer-Caches haben - SC1 und SC2 - ein Slicer-Element, und genau hier müssen Sie es anpassen. Also hießen meine beiden Slicer Name und Name1. Okay, Sie müssen Ihre Slicer-Namen dort eintragen. Application.Screenupdating = False, Application.EnableEvents = False und dann Slicer Cache 2 - Wir werden den Filter löschen und dann für jedes Element SI1 und sc1.SlicerItems, wenn es ausgewählt ist, machen wir das gleiche Element im Slicer-Cache, das ausgewählt werden soll. Dies ist eine kleine Schleife, die durchlaufen wird, wie viele Elemente sich gerade in diesem Slicer befinden. In meinem Fall habe ich 11 oder 12; In Ihrem Fall könnten Sie mehr haben.Also hießen meine beiden Slicer Name und Name1. Okay, Sie müssen Ihre Slicer-Namen dort eintragen. Application.Screenupdating = False, Application.EnableEvents = False und dann Slicer Cache 2 - Wir werden den Filter löschen und dann für jedes Element SI1 und sc1.SlicerItems, wenn es ausgewählt ist, werden wir machen das gleiche Element im Slicer-Cache, das ausgewählt werden soll. Dies ist eine kleine Schleife, die durchlaufen wird, wie viele Elemente sich gerade in diesem Slicer befinden. In meinem Fall habe ich 11 oder 12; In Ihrem Fall könnten Sie mehr haben.Also hießen meine beiden Slicer Name und Name1. Okay, Sie müssen dort Ihre Slicer-Namen eintragen. Application.Screenupdating = False, Application.EnableEvents = False und dann Slicer Cache 2 - Wir werden den Filter löschen und dann für jedes Element SI1 und sc1.SlicerItems, wenn es ausgewählt ist, werden wir machen das gleiche Element im Slicer-Cache, das ausgewählt werden soll. Dies ist eine kleine Schleife, die durchlaufen wird, wie viele Elemente sich gerade in diesem Slicer befinden. In meinem Fall habe ich 11 oder 12; In Ihrem Fall könnten Sie mehr haben.Das gleiche Element im Slicer-Cache wird ausgewählt. Dies ist eine kleine Schleife, die durchlaufen wird, wie viele Elemente sich gerade in diesem Slicer befinden. In meinem Fall habe ich 11 oder 12; In Ihrem Fall könnten Sie mehr haben.Das gleiche Element im Slicer-Cache wird ausgewählt. Dies ist eine kleine Schleife, die durchlaufen wird, wie viele Elemente sich gerade in diesem Slicer befinden. In meinem Fall habe ich 11 oder 12; In Ihrem Fall könnten Sie mehr haben.
Wenn Sie damit fertig sind, aktivieren Sie die Aktivierungsereignisse wieder und die Bildschirmaktualisierung wieder. In Ordung. Also nehmen wir diesen Code, kopieren diesen Code und fügen ihn hier in der Mitte unseres Makros ein. Okay, jetzt stellen wir einfach sicher, dass ich Strg + G drücke und nach Application.EnableEvents frage, ein oder aus - also? Application.EnableEvents-- und es ist wahr. Wenn deins als falsch erscheint, möchten Sie hierher zurückkehren und sagen, dass es = wahr ist - also schalten Sie diese Ereignisse ein. In Ordung. Hier ist, was passieren wird. Unser Coach sollte also hier arbeiten, es steht auf dem richtigen Arbeitsblatt. Wir werden in einer xlxm-Datei gespeichert, und ich habe Makros aktiviert. Wenn ich aus dem linken Slicer auswähle, wird der Slicer-Cache 1 - I 'angezeigt.Ich werde Andy über Della auswählen - der andere Slicer wird ebenfalls aktualisiert. Okay und selbst wenn ich nur Gloria wählen würde - nur Gloria - sieht es so aus, als würde es wirklich sehr, sehr gut funktionieren. Selbst wenn ich STRG + Klick machen würde, wenn ich Strg loslasse, werden alle drei aktualisiert.
Aber hier ist das Gotcha - es gibt immer ein Gotcha - diesen Slicer, es muss existieren, aber Sie können diesen Slicer nicht verwenden - warten Sie, ich meine, Sie können, Sie können einen Slicer verwenden, aber es wird die Dinge verwirren . Weil was passieren wird, werde ich dies in Hank ändern und sie werden zu dem zurückkehren, was sich in Slicer Cache 1 befindet, weil ich die Pivot-Tabelle auf diesem Blatt geändert habe. Werden Sie im wirklichen Leben zwei Pivot-Tabellen auf demselben Blatt haben? Ich weiß nicht, ob du es bist oder nicht, okay, aber die Dinge werden ein bisschen verrückt.
Schauen wir uns das jetzt an. Als erstes möchte ich ein neues Arbeitsblatt einfügen - Alt + IW zum Einfügen des Arbeitsblatts - und dies als DarkCave bezeichnen. Sie können es so nennen, wie Sie wollen. Ich werde das Dashboard nehmen, das nicht funktionieren wird. Ich werde das Dashboard kopieren und hierher in die dunkle Höhle kommen und es dort einfügen. Dann mit der rechten Maustaste klicken und das Blatt ausblenden, damit niemand diesen Slicer jemals sieht. Und dann sollten wir es von hier aus löschen können. Schön, in Ordnung. Und wir werden nur überprüfen, ob sie noch funktionieren - wählen Sie Charlie durch Eddie und beide aktualisieren noch. Was ist nun los? Der Slicer, den wir nicht sehen können, der, den wir versteckt haben, wird ebenfalls aktualisiert, aber es ist uns egal, dass er aktualisiert wird.
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.
Ich möchte mich bei Ihnen für Ihren Besuch bedanken. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2104.xlsm
