Slicers er fantastiske til pivottabeller, fordi du kan styre flere pivottabeller fra et sæt snit. Men - det er en slags løgn. Du kan kontrollere flere pivottabeller, der kommer fra det samme datasæt. Når du har pivottabeller, der kommer fra to forskellige datasæt, er det ret vanskeligt. Jeg vil vise dig nogle VBA, der lader dig trække dette ud.
Se video
- Hvordan kan du få en skiver til at køre to drejetabeller?
- Hvis begge pivottabeller kom fra det samme datasæt: Vælg Slicer, Rapportforbindelser, Vælg andre pivottabeller
- Men hvis pivottabellerne kom fra forskellige datasæt:
- Brug Gem som til at ændre udvidelsen til projektmappen til XLSM i stedet for XLSX
- Brug alt = "" + TMS, og skift makrosikkerhed til anden indstilling.
- Alt + F11 for at komme til VBA
- Ctrl + R for at få vist projektudforskeren
- Find regnearket, der indeholder din første drejetabel og udsnit
- Indsæt koden til Worksheet_Update
- Skjul den anden skærebord væk, så den forbliver eksisterende, men ingen kan nogensinde vælge mellem den skiver
Videoudskrift
Lær Excel til Podcast, afsnit 2104: Synkroniser udsnit fra forskellige datasæt.
Hej, velkommen tilbage til netcast, jeg er Bill Jelen, og dagens spørgsmål handler ikke om, hvordan man tager disse to pivottabeller, der kom fra et datasæt, og får Slicer til at kontrollere alle disse pivottabeller. Det er ikke det, det handler om. Det er en nem ting at gøre - Slicer, Tools, Options, enten Report Connections eller Slicer Connections i den gamle version, og kontroller at du vil have denne Slicer til at kontrollere alle disse pivottabeller. Let, ikke? Dette spørgsmål handler om dette regneark, hvor vi har to forskellige datasæt, og vi skal oprette en pivottabel ud fra dette, og ud fra dette - lad mig nu fremskynde videoen, mens jeg opretter disse pivottabeller. Okay, nu, hvad du vil se er, at jeg har to pivottabeller, denne pivottabel er oprettet ud fra et datasæt, og der er en udsnit, der styrer pivottabellen;og så har jeg en anden pivottabel, der er oprettet ud fra et andet datasæt, og en udsnitter, der styrer pivottabellen. Men der er absolut ingen måde at få denne skiver til at styre både denne pivottabel og denne pivottabel, der er bygget fra et andet datasæt. I orden. Men jeg vil vise dig, hvordan du gør det i dag med en makro.
Dette er vanskeligt at gøre. Da spørgsmålet kom ind, sagde jeg: "Nu, dette, jeg tror ikke du kan gøre det." Men jeg har arbejdet på det og eksperimenteret, og jeg tror, jeg endelig fik det. Jeg er nødt til at tro, at jeg endelig fik det nede. Okay, så lad os gå igennem dette. Først gemmes dette som en xlsx-fil. Det er en fin filtype, bortset fra at den er en forfærdelig filtype, fordi det er den eneste filtype, der ikke tillader makroer. Du er nødt til at ændre dette fra xlsx til xlsm, ellers vil alt dit arbejde til resten af videoen blive kastet ud af vinduet. Gem som, skift filtypen til xlsm eller, pokker, xlsb, en af dem fungerer. Det er den der er brudt - xlsx-- og det er standard, skør er det ikke? Xlsm, klik på Gem. Hvis du aldrig har lavet makroer før, Alt + T til Tom, M til makro,S for sikkerhed, og du vil være i stand til at gemme alle makroer uden underretning. Nødvendigt at ændre det til det andet, så dine makroer kan fungere.
Okay, nu har vi to skiver. Sats på, at du aldrig vidste dette, men skiver har navne. Vi skal gå til Slicer Tools, Options, Slicer Settings og se denne kaldes Slicer_Name. Sådan. Gå til den anden, gå til Slicer Tools, Options, Slicer Settings, denne hedder Slicer_Name1 - ikke Navn plads 1, Name1. To navne sådan.
Her er hvad vi skal gøre. Vi skifter til VBA-- Alt + F11. I VBA, hvis du aldrig har lavet VBA, vil du have denne store grå skærm. Vi kommer her og siger View, Project Explorer, i Project Explorer find din fil - mine hedder Podcast 2104. Åbn Microsoft Excel-objekter, og det ark, hvor jeg vil have dette til at arbejde, hedder Dashboard. Jeg skal højreklikke der og sige Vis kode. Denne kode, som vi skriver, kan ikke gå i et modul som i en almindelig makro - dette skal være på dette regneark. Åbn rullemenuen øverst til venstre, Arbejdsark, og derefter i rullemenuen øverst til højre vil vi sige Pivot Table Update. Okay, så det er her vores kode vil gå nu. Jeg har allerede forbagt denne kode. Lad os se på koden her i notesblok. Så viskal have to Slicer-cacher - SC1 og SC2 - en Slicer-vare, og så er det her, hvor du bliver nødt til at tilpasse det. Så mine to skiver blev kaldt Navn og Navn1. Okay, du bliver nødt til at lægge dine skivernavne derinde. Application.Screenupdating = Falsk, Application.EnableEvents = Falsk, og derefter Slicer Cache 2 - vi vil rydde filteret, og derefter for hvert element SI1 og sc1.SlicerItems, hvis det er valgt, så laver vi det samme element i Slicer Cache skal vælges. Dette er en lille sløjfe, der løber igennem, men mange ting tilfældigvis er i den skiver. I mit tilfælde har jeg 11 eller 12; i dit tilfælde har du måske mere.Så mine to skiver blev kaldt Navn og Navn1. Okay, du bliver nødt til at lægge dine skivernavne derinde. Application.Screenupdating = False, Application.EnableEvents = False og derefter Slicer Cache 2 - vi vil rydde filteret og derefter for hvert emne SI1 og sc1.SlicerItems, hvis det er valgt, så laver vi det samme element i Slicer Cache skal vælges. Dette er en lille sløjfe, der løber igennem, men mange ting tilfældigvis er i den skiver. I mit tilfælde har jeg 11 eller 12; i dit tilfælde har du måske mere.Så mine to skiver blev kaldt Navn og Navn1. Okay, du bliver nødt til at sætte dine skivernavne derinde. Application.Screenupdating = Falsk, Application.EnableEvents = Falsk, og derefter Slicer Cache 2 - vi vil rydde filteret, og derefter for hvert element SI1 og sc1.SlicerItems, hvis det er valgt, så laver vi det samme element i Slicer Cache skal vælges. Dette er en lille løkke, der løber igennem, men mange ting tilfældigvis er i den skiver. I mit tilfælde har jeg 11 eller 12; i dit tilfælde har du måske mere.vil gøre det samme element i Slicer Cache, der skal vælges. Dette er en lille sløjfe, der løber igennem, men mange ting tilfældigvis er i den skiver. I mit tilfælde har jeg 11 eller 12; i dit tilfælde har du måske mere.vil gøre det samme element i Slicer Cache, der skal vælges. Dette er en lille løkke, der løber igennem, men mange ting tilfældigvis er i den skiver. I mit tilfælde har jeg 11 eller 12; i dit tilfælde har du måske mere.
Når vi er færdige med det, skal du aktivere begivenheder igen, tænde skærmopdatering igen. I orden. Så vi tager denne kode, kopierer denne kode og indsætter den her midt i vores makro sådan. Okay, lad os bare sørge for, at jeg trykker på Ctrl + G, og min anmodning om er Application. EnableEvents, on or off-- so,? Application.EnableEvents-- og det er sandt. Hvis din kommer op som falsk, så vil du komme tilbage herover og sige, at det er = Sandt - så du tænder for disse begivenheder. I orden. Her er hvad der skal ske. Så vores coach skal arbejde her, det er på det rigtige regneark. Vi er gemt i en xlxm-fil, og jeg tændte makroer, og hvad vi skal se, er, at når jeg vælger fra venstre Slicer, den Slicer Cache 1 - I 'Jeg vælger Andy gennem Della - den anden Slicer vil også opdatere. Okay Og selvom jeg bare ville vælge Gloria-- bare Gloria-- det ser ud til at det fungerer rigtig, rigtig godt. Selvom jeg ville CTRL + klikke, når jeg slipper Ctrl, opdateres de alle tre.
Men her er gotcha-- der er altid en gotcha-- denne Slicer, den skal eksistere, men du kan ikke bruge denne Slicer-- vent, jeg mener, du kan, du kan bruge en Slicer, men det vil forvirre pokker ud af ting . Fordi hvad der vil ske, skal jeg ændre dette til Hank, og de vil gå tilbage til hvad der er i Slicer Cache 1, fordi jeg ændrede drejetabellen på dette ark. I det virkelige liv skal du have to drejetabeller på samme ark? Jeg ved ikke, om du er, eller om du ikke er det, okay, men tingene bliver lidt vanvittige.
Lad os bare se på dette. Den første ting, jeg vil gøre, er at jeg indsætter et nyt regneark - Alt + IW til indsættelse af regnearket - og jeg vil kalde dette en DarkCave. Du kan kalde det, hvad du vil. Jeg tager det dashboard, der ikke fungerer, jeg kopierer dashboardet og kommer her til den mørke hule og indsætter det der, og højreklik og skjul det ark, så ingen nogensinde ser den skiver. Og så herfra skal vi være i stand til at slette det. Dejligt, okay. Og vi skal bare kontrollere, om de stadig arbejder-- vælg Charlie gennem Eddie, og de opdaterer begge stadig. Hvad sker der nu? Den skæremaskine, som vi ikke kan se, den, som vi har gemt væk, opdateres også, men vi er ligeglad med, at den opdateres.
Hvad nu, hvis du vil have dine ting på forskellige ark? Jeg indsætter et nyt regneark her - Alt + IW-- og jeg tager en af disse pivottabeller - måske den anden pivottabel - og flytter den til det andet ark - så Ctrl + C for at kopiere pivottabellen, Ctrl + V for at indsætte pivottabellen her. Og hvis jeg har brug for en skiver her - indsæt ikke en skive fra denne drejetabel - vi er nødt til at komme tilbage til vores instrumentbræt, tage den skiver, der er den kontrollerende skiver, Ctrl + C for at lave en kopi af det, og indsæt det her - Ctrl + V. I orden? Nu har vi ingen kode på dette ark - der er ingen kode på Sheet4 - og jeg tænkte, at jeg skulle tilføje noget kode til Sheet4, men her er den smukke ting: Når jeg ændrer denne skiver, er hvad der sker, på instrumentbrættet, der drejer bordet 's opdatering, selvom den pivottabel på det ark, der ikke er aktiv, opdateres, kører de koden, og denne opdateres også. Temmelig darn fantastisk, at det fungerer.
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.
Vil du takke dig, fordi du kom forbi, vi ses næste gang til endnu en netcast fra.
Download fil
Download eksempelfilen her: Podcast2104.xlsm
