Skivare är fantastiska för pivottabeller eftersom du kan styra flera pivottabeller från en uppsättning skivor. Men - det är en sorts lögn. Du kan styra flera pivottabeller som kommer från samma datamängd. När du har pivottabeller som kommer från två olika datamängder är det ganska knepigt. Jag kommer att visa dig några VBA som låter dig ta fram det här.
Kolla på video
- Hur kan du få en skivare att köra två pivottabeller?
- Om båda pivottabellerna kommer från samma datauppsättning: Välj Slicer, Rapportera anslutningar, Välj andra pivottabeller
- Men om pivottabellerna kom från olika datamängder:
- Använd Save As för att ändra arbetsbokstillägget till XLSM istället för XLSX
- Använd alt = "" + TMS och ändra makrosäkerhet till andra inställning.
- Alt + F11 för att komma till VBA
- Ctrl + R för att visa projektutforskaren
- Hitta kalkylbladet som innehåller din första pivottabell och skärare
- Infoga koden för Worksheet_Update
- Dölj bort den andra skivaren så att den håller kvar men ingen kan någonsin välja mellan den skivaren
Videoutskrift
Lär dig Excel för Podcast, avsnitt 2104: Synkronisera skivor från olika datasatser.
Hej, välkommen tillbaka till netcast, jag heter Bill Jelen, och dagens fråga handlar inte om hur man tar dessa två pivottabeller som kommer från en datauppsättning och får Slicer att kontrollera alla dessa pivottabeller. Det är inte vad det handlar om. Det är en enkel sak att göra - Slicer, Tools, Options, antingen Report Connections eller Slicer Connections i den gamla versionen, och kontrollera att du vill att den här Slicer ska kontrollera alla dessa pivottabeller. Enkelt, eller hur? Den här frågan handlar om det här kalkylbladet, där vi har två olika datamängder och vi kommer att skapa en pivottabell från detta, och från detta - låt mig nu påskynda videon medan jag skapar dessa pivottabeller. Okej, nu, vad du kommer att se är att jag har två pivottabeller, den här pivottabellen skapas från en datauppsättning och det finns en skärare som styr den pivottabellen;och sedan har jag en andra pivottabell som skapats från en annan datamängd och en skivare som styr den pivottabellen. Men det finns absolut inget sätt att få den här skivaren att styra både denna pivottabell och denna pivottabell som är byggd från en annan datamängd. OK. Men jag ska visa dig hur man gör det idag med ett makro.
Nu är det svårt att göra. När frågan kom in sa jag: "Nu, det här, jag tror inte att du kan göra det." Men jag har jobbat med det och experimenterat och jag tror att jag äntligen fick det. Jag måste tro att jag äntligen fick det. Okej, så låt oss gå igenom det här. För det första sparas detta som en xlsx-fil. Det är en bra filtyp, förutom att den är en hemsk filtyp eftersom den är den enda filtypen som inte tillåter makron. Du måste ändra detta från xlsx till xlsm, annars kommer allt ditt arbete till resten av videon att kastas ut genom fönstret. Spara som, ändra filtypen till xlsm eller, heck, xlsb, någon av dessa fungerar. Det är den som är trasig-- xlsx-- och det är standard, galen är det inte? Xlsm, klicka på Spara. Om du aldrig har gjort makron förut, Alt + T för Tom, M för makro,S för säkerhet och du kommer att kunna spara alla makron utan avisering. Måste ändra det till det andra, så att dina makron kan fungera.
Okej, nu har vi två skivor. Satsa att du aldrig visste det här, men skivor har namn. Vi ska gå till Slicer Tools, Options, Slicer Settings och se att den här heter Slicer_Name. Sådär. Gå till den andra, gå till Slicer Tools, Options, Slicer Settings, den här heter Slicer_Name1 - inte Namnutrymme 1, Name1. Två namn som det.
Här är vad vi ska göra. Vi ska byta till VBA-- Alt + F11. I VBA, om du aldrig har gjort VBA, kommer du att ha den här stora grå skärmen. Vi kommer hit och säger View, Project Explorer, i Project Explorer, hitta din fil - gruvan heter Podcast 2104. Öppna Microsoft Excel-objekt, och arket där jag vill att det ska fungera heter Dashboard. Jag ska högerklicka där och säga Visa kod. Den här koden som vi skriver kan inte gå i en modul som i ett vanligt makro - det måste finnas på det här kalkylbladet. Öppna rullgardinsmenyn längst upp till vänster, Kalkylblad, sedan i rullgardinsmenyn längst upp till höger ska vi säga Uppdatering av pivottabell. Okej, så det är här vår kod kommer att gå nu. Jag har redan förbakat den här koden. Låt oss ta en titt på koden här i anteckningar. Så vikommer att ha två Slicer-cacher - SC1 och SC2 - ett Slicer-objekt och sedan, här, kommer du att behöva anpassa det. Så mina två skivor hette Namn och Namn1. Okej, du måste sätta dina skivnamn där. Application.Screenupdating = False, Application.EnableEvents = False, och sedan Slicer Cache 2 - vi kommer att rensa filtret, och sedan för varje objekt SI1 och sc1.SlicerItems, om det är valt, kommer vi att göra samma objekt i Slicer Cache ska väljas. Det här är en liten slinga som går igenom hur många saker som helst finns i den skivaren. I mitt fall har jag 11 eller 12; i ditt fall kanske du har mer.Så mina två skivor hette Namn och Namn1. Okej, du måste sätta dina skivnamn där. Application.Screenupdating = False, Application.EnableEvents = False, och sedan Slicer Cache 2 - vi kommer att rensa filtret, och sedan för varje objekt SI1 och sc1.SlicerItems, om det är valt, kommer vi att göra samma objekt i Slicer Cache ska väljas. Det här är en liten slinga som går igenom hur många saker som helst finns i den skivaren. I mitt fall har jag 11 eller 12; i ditt fall kanske du har mer.Så mina två skivor hette Namn och Namn1. Okej, du måste sätta dina skivnamn där. Application.Screenupdating = False, Application.EnableEvents = False, och sedan Slicer Cache 2 - vi kommer att rensa filtret och sedan för varje objekt SI1 och sc1.SlicerItems, om det är valt, kommer vi att göra samma objekt i Slicer Cache ska väljas. Det här är en liten slinga som går igenom hur många saker som helst finns i den skivaren. I mitt fall har jag 11 eller 12; i ditt fall kanske du har mer.kommer att göra samma objekt i Slicer Cache som ska väljas. Det här är en liten slinga som går igenom hur många saker som helst finns i den skivaren. I mitt fall har jag 11 eller 12; i ditt fall kanske du har mer.kommer att göra samma objekt i Slicer Cache som ska väljas. Det här är en liten slinga som går igenom hur många saker som helst finns i den skivaren. I mitt fall har jag 11 eller 12; i ditt fall kanske du har mer.
När vi är klara med det aktiverar du aktivera händelser igen, aktiverar skärmuppdatering igen. OK. Så vi tar den här koden, kopierar den och klistrar in den här mitt i vårt makro så. Okej, nu, låt oss bara se till att jag kommer att trycka på Ctrl + G och min begäran är Application. EnableEvents, on or off-- so,? Application.EnableEvents-- och det är sant. Om din kommer upp som falsk, vill du komma tillbaka upp hit och säga att det är = sant-- så du aktiverar dessa händelser. OK. Här är vad som kommer att hända. Så vår tränare borde arbeta här, det ligger på rätt arbetsblad. Vi sparas i en xlxm-fil och jag slår på makron och det vi ska se är att när jag väljer från vänster Slicer, att Slicer Cache 1-- I 'Välj Andy genom Della - den andra Slicer kommer också att uppdatera. Okej Och även om jag bara skulle välja Gloria-- bara Gloria-- det ser ut som att det fungerar riktigt, riktigt bra. Även om jag skulle CTRL + klicka, när jag släpper Ctrl, uppdateras de alla tre.
Men här är gotcha-- det finns alltid en gotcha-- denna Slicer, den måste finnas, men du kan inte använda den här Slicer-- vänta, jag menar att du kan, du kan använda en Slicer men det kommer att förvirra heck ur saker . Eftersom det som kommer att hända är att jag kommer att ändra detta till Hank och de kommer att gå tillbaka till vad som finns i Slicer Cache 1, för jag ändrade pivottabellen på det här arket. Nu, i verkligheten, ska du ha två pivottabeller på samma ark? Jag vet inte om du är eller om du inte är det, okej, men saker kommer att bli lite galna.
Nu, låt oss bara titta på det här. Det första jag vill göra är att jag ska infoga ett nytt kalkylblad - Alt + IW för att infoga kalkylbladet - och jag kommer att kalla detta en DarkCave. Du kan kalla det vad du vill. Jag ska ta den instrumentpanelen som inte kommer att fungera, jag kommer att kopiera den instrumentpanelen och komma hit till den mörka grottan och klistra in den där och högerklicka och dölj det arket så att ingen någonsin ser den skivaren. Och sedan, härifrån, borde vi kunna ta bort det. Trevligt, okej. Och vi ska bara kontrollera att de fortfarande arbetar - välj Charlie genom Eddie och de uppdaterar fortfarande båda. Vad händer nu? Skivaren som vi inte kan se, den som vi har gömt bort, den uppdateras också, men vi bryr oss inte om att den uppdateras.
Tänk nu om du vill ha dina saker på olika ark? Jag ska infoga ett nytt kalkylblad här - Alt + IW-- och ta en av dessa pivottabeller - kanske den andra pivottabellen - och flytta den till det andra arket - så, Ctrl + C för att kopiera pivottabellen, Ctrl + V för att klistra in pivottabellen här. Och om jag behöver ha en skivare här - sätt inte in en skiva från det här pivottabellen - vi måste komma tillbaka till vår instrumentpanel, ta skivaren som är den kontrollerande skivaren, Ctrl + C för att göra en kopia av den, och klistra in den här - Ctrl + V. OK? Nu har vi ingen kod på det här arket-- det finns ingen kod på Sheet4 - och jag tänkte att jag skulle behöva lägga till lite kod i Sheet4, men här är det vackra: När jag byter den här skivaren är det som händer, på instrumentpanelen det pivottabell 's uppdatering trots att pivottabellen på det arket som inte är aktiv uppdateras, kommer de att köra koden och den kommer också att uppdateras. Ganska darn fantastiskt att det fungerar.
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.
Vill du tacka dig för att du kom förbi, vi ses nästa gång för en ny netcast från.
Nedladdning fil
Ladda ner exempelfilen här: Podcast2104.xlsm
