Slicers zijn geweldig voor draaitabellen omdat u meerdere draaitabellen kunt bedienen vanuit één set slicers. Maar - dat is een soort leugen. U kunt meerdere draaitabellen beheren die uit dezelfde gegevensset kwamen. Als u draaitabellen hebt die uit twee verschillende gegevenssets afkomstig zijn, is dat behoorlijk lastig. Ik zal je wat VBA laten zien waarmee je dit kunt doen.
Bekijk video
- Hoe kun je een snijmachine twee draaitabellen laten besturen?
- Als beide draaitabellen uit dezelfde gegevensset kwamen: selecteer slicer, rapportverbindingen, kies andere draaitabellen
- Maar als de draaitabellen uit verschillende gegevenssets kwamen:
- Gebruik Opslaan als om de werkmapextensie te wijzigen in XLSM in plaats van XLSX
- Gebruik alt = "" + TMS en verander macrobeveiliging naar de tweede instelling.
- Alt + F11 om naar VBA te gaan
- Ctrl + R om de projectverkenner weer te geven
- Zoek het werkblad met uw eerste draaitabel en snijmachine
- Voeg de code voor Worksheet_Update in
- Verberg de tweede snijmachine zodat hij blijft bestaan, maar niemand kan ooit uit die snijmachine kiezen
Videotranscriptie
Leer Excel voor podcast, aflevering 2104: synchroniseer slicers uit verschillende datasets.
Hallo, welkom terug bij de netcast, ik ben Bill Jelen, en de vraag van vandaag gaat niet over hoe je deze twee draaitabellen die uit één gegevensset kwamen, moet nemen en de Slicer al die draaitabellen moet laten besturen. Daar gaat dit niet over. Dat is gemakkelijk te doen: Slicer, Tools, Options, ofwel Report Connections of Slicer Connections in de oude versie, en controleer of u wilt dat deze Slicer al die draaitabellen bestuurt. Makkelijk toch? Deze vraag gaat over dit werkblad, waar we twee verschillende gegevenssets hebben en we gaan hiervan een draaitabel maken, en van hieruit - laat me nu de video versnellen terwijl ik deze draaitabellen maak. Oké, wat je nu gaat zien is dat ik twee draaitabellen heb, deze draaitabel is gemaakt op basis van één gegevensset en er is een slicer die die draaitabel bestuurt;en dan heb ik een tweede draaitabel die is gemaakt op basis van een andere gegevensset, en een slicer die die draaitabel bestuurt. Maar er is absoluut geen manier om deze slicer zowel deze draaitabel als deze draaitabel te laten besturen die is opgebouwd uit een andere gegevensset. Oké. Maar ik ga je laten zien hoe je dat vandaag doet met een macro.
Nu, dit is lastig om te doen. Toen de vraag binnenkwam, zei ik: "Nu, dit, ik denk niet dat je het kunt." Maar ik heb eraan gewerkt en geëxperimenteerd en ik denk dat ik het eindelijk heb. Ik moet denken dat ik het eindelijk onder de knie heb. Oké, dus laten we dit doornemen. Ten eerste wordt dit opgeslagen als een xlsx-bestand. Dat is een prima bestandstype, behalve dat het een vreselijk bestandstype is, omdat dit het enige bestandstype is dat geen macro's toestaat. Je moet dit veranderen van xlsx naar xlsm, anders wordt al je werk naar de rest van de video uit het raam gegooid. Opslaan als, verander het bestandstype in xlsm of, ach, xlsb, een van beide zal werken. Dat is degene die kapot is - xlsx - en het is de standaard, gek is het niet? Xlsm, klik op Opslaan. Als je nog nooit macro's hebt gedaan, Alt + T voor Tom, M voor Macro,S voor beveiliging en u kunt alle macro's zonder kennisgeving opslaan. U moet dat wijzigen in de tweede, zodat uw macro's kunnen werken.
Oké, nu hebben we twee snijmachines. Ik wed dat je dit nooit wist, maar snijmachines hebben namen. We gaan naar Slicer Tools, Options, Slicer Settings, en zien dat deze Slicer_Name wordt genoemd. Zoals dat. Ga naar de tweede, ga naar Slicer Tools, Options, Slicer Settings, deze heet Slicer_Name1 - niet Name space 1, Name1. Twee van die namen.
Dit is wat we gaan doen. We gaan overschakelen naar VBA - Alt + F11. Als je in VBA nog nooit VBA hebt gedaan, heb je dit grote grijze scherm. We komen hier en zeggen View, Project Explorer, in de Project Explorer vind je je bestand - het mijne heet Podcast 2104. Open Microsoft Excel Objects, en het blad waarop ik wil dat dit werkt heet Dashboard. Ik klik daar met de rechtermuisknop en zeg Bekijk code. Deze code die we aan het schrijven zijn, kan niet in een module zoals in een gewone macro worden gebruikt - dit moet op dit werkblad staan. Open de vervolgkeuzelijst linksboven, Werkblad, en vervolgens in de vervolgkeuzelijst rechtsboven gaan we Pivot Table Update zeggen. Oké, dus dit is waar onze code nu naartoe gaat. Ik heb deze code al voorgebakken. Laten we de code hier in Kladblok eens bekijken. Zodat we'We zullen twee Slicer-caches hebben - SC1 en SC2 - één Slicer-item en dan, hier, moet je het hier aanpassen. Dus mijn twee Slicers heetten Name en Name1. Oké, je zult je slicernamen erin moeten zetten. Application.Screenupdating = False, Application.EnableEvents = False, en dan Slicer Cache 2 - we gaan het filter wissen, en dan voor elk item SI1 en sc1.SlicerItems, als het is geselecteerd, dan gaan we maken hetzelfde item in de slicercache dat moet worden geselecteerd. Dit is een kleine lus die doorloopt, hoeveel items er zich ook in die snijmachine bevinden. In mijn geval heb ik er 11 of 12; in jouw geval heb je misschien meer.Dus mijn twee Slicers heetten Name en Name1. Oké, je zult je slicernamen erin moeten zetten. Application.Screenupdating = False, Application.EnableEvents = False, en dan Slicer Cache 2 - we gaan het filter wissen, en dan voor elk item SI1 en sc1.SlicerItems, als het is geselecteerd, dan gaan we maken hetzelfde item in de slicercache dat moet worden geselecteerd. Dit is een kleine lus die doorloopt, hoeveel items er zich ook in die snijmachine bevinden. In mijn geval heb ik er 11 of 12; in jouw geval heb je misschien meer.Dus mijn twee Slicers heetten Name en Name1. Oké, je zult je slicernamen erin moeten zetten. Application.Screenupdating = False, Application.EnableEvents = False, en dan Slicer Cache 2 - we gaan het filter wissen, en dan voor elk item SI1 en sc1.SlicerItems, als het is geselecteerd, dan gaan we maken hetzelfde item in de slicercache dat moet worden geselecteerd. Dit is een kleine lus die doorloopt, hoeveel items er zich ook in die snijmachine bevinden. In mijn geval heb ik er 11 of 12; in jouw geval heb je misschien meer.opnieuw gaan maken dat hetzelfde item in Slicer Cache wordt geselecteerd. Dit is een kleine lus die doorloopt, hoeveel items er zich ook in die snijmachine bevinden. In mijn geval heb ik 11 of 12; in jouw geval heb je misschien meer.opnieuw gaan maken dat hetzelfde item in Slicer Cache wordt geselecteerd. Dit is een kleine lus die doorloopt, hoeveel items er zich ook in die snijmachine bevinden. In mijn geval heb ik er 11 of 12; in jouw geval heb je misschien meer.
Als we daarmee klaar zijn, schakel gebeurtenissen inschakelen weer in en schakel Schermupdate weer in. Oké. Dus we nemen deze code, kopiëren deze code en plakken deze zo in het midden van onze macro. Oké, laten we er nu voor zorgen dat ik op Ctrl + G druk en mijn vraag is Application.EnableEvents, aan of uit-- dus? Application.EnableEvents - en het is waar. Als de jouwe vals blijkt te zijn, dan wil je hier terugkomen en zeggen dat het = Waar is - dus zet je die gebeurtenissen aan. Oké. Nu, hier is wat er gaat gebeuren. Dus onze coach zou hier moeten werken, het staat op het rechter werkblad. We zijn opgeslagen in een xlxm-bestand, en ik heb macro's aangezet en wat we gaan zien, is dat wanneer ik kies uit de linker Slicer, die Slicer Cache 1-- I 'Ik kies Andy via Della - de andere Slicer gaat ook updaten. Oké. En zelfs als ik alleen Gloria zou kiezen - alleen Gloria - het lijkt erop dat het echt heel goed werkt. Zelfs als ik CTRL + klik, als ik Ctrl loslaat, worden ze alle drie bijgewerkt.
Maar hier is de gotcha - er is altijd een gotcha - deze snijmachine, hij moet bestaan, maar je kunt deze snijmachine niet gebruiken - wacht, ik bedoel, je kunt het, je kunt een snijmachine gebruiken, maar het zal de dingen in de war brengen . Want wat er gaat gebeuren, is dat ik dit in Hank ga veranderen en ze gaan terug naar wat er in Slicer Cache 1 staat, omdat ik de draaitabel op dit blad heb gewijzigd. Gaat u nu in het echt twee draaitabellen op hetzelfde blad hebben? Ik weet niet of je het bent of niet, oké, maar de dingen gaan een beetje gek worden.
Laten we hier nu eens naar kijken. Het eerste wat ik wil doen, is dat ik een nieuw werkblad ga invoegen - Alt + IW voor het invoegen van het werkblad - en ik ga dit een DarkCave noemen. U kunt het noemen wat u maar wilt. Ik ga dat dashboard nemen dat niet zal werken, ik ga dat dashboard kopiëren en kom hier naar de donkere grot en plak het daar en klik dan met de rechtermuisknop en verberg dat blad zodat niemand die Slicer ooit ziet. En dan zouden we het vanaf hier moeten kunnen verwijderen. Leuk, oké. En we gaan gewoon controleren of ze nog steeds werken - kies Charlie via Eddie en ze zijn allebei nog aan het updaten. Wat gebeurt er nu? De Slicer die we niet kunnen zien, degene die we hebben weggestopt, wordt ook bijgewerkt, maar het kan ons niet schelen dat deze wordt bijgewerkt.
Nu, wat als u uw spullen op verschillende vellen wilt hebben? Ik zal hier een nieuw werkblad invoegen - Alt + IW - en ik neem een van deze draaitabellen - misschien de tweede draaitabel - en verplaats het naar dat andere blad - dus Ctrl + C om te kopiëren de draaitabel, Ctrl + V om de draaitabel hier te plakken. En als ik hier een snijmachine nodig heb - voeg geen plak uit deze draaitabel in - we moeten terug naar ons dashboard, neem de snijmachine die de besturende snijmachine is, Ctrl + C om er een kopie van te maken, en plak het hier - Ctrl + V. Oké? Nu, we hebben geen code op dit blad - er is geen code op blad4 - en ik dacht dat ik wat code aan blad4 zou moeten toevoegen, maar hier is het mooie: wanneer ik deze snijmachine verander, is wat er gebeurt, op het dashboard die draaitafel 's updaten, hoewel die draaitabel op dat blad dat niet actief is, wordt bijgewerkt, zullen ze de code uitvoeren en dit zal ook worden bijgewerkt. Behoorlijk verbazingwekkend dat dat werkt.
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.
Ik wil je bedanken voor het langskomen, we zien je de volgende keer voor een nieuwe netcast van.
Download bestand
Download het voorbeeldbestand hier: Podcast2104.xlsm
