Slicers er kjempebra for pivottabeller fordi du kan kontrollere flere pivottabeller fra ett sett med snitt. Men - det er liksom en løgn. Du kan kontrollere flere pivottabeller som kommer fra samme datasett. Når du har pivottabeller som kommer fra to forskjellige datasett, er det ganske vanskelig. Jeg vil vise deg noen VBA som lar deg trekke dette.
Se på video
- Hvordan kan du få en skiver til å kjøre to pivottabeller?
- Hvis begge pivottabellene kom fra det samme datasettet: Velg Slicer, Rapporter tilkoblinger, Velg andre pivottabeller
- Men hvis pivottabellene kom fra forskjellige datasett:
- Bruk Lagre som for å endre arbeidsbokutvidelsen til XLSM i stedet for XLSX
- Bruk alt = "" + TMS og endre makrosikkerhet til andre innstilling.
- Alt + F11 for å komme til VBA
- Ctrl + R for å vise prosjektutforskeren
- Finn regnearket som inneholder ditt første pivottabell og skiver
- Sett inn koden for Worksheet_Update
- Skjul den andre skiveapparatet vekk, slik at det forblir eksisterende, men ingen kan noensinne velge mellom det snittet
Videoutskrift
Lær Excel for Podcast, episode 2104: Synkroniser skivere fra forskjellige datasett.
Hei, velkommen tilbake til netcast, jeg er Bill Jelen, og dagens spørsmål handler ikke om hvordan du tar disse to pivottabellene som kom fra ett datasett og får Slicer til å kontrollere alle disse pivottabellene. Det er ikke det dette handler om. Det er en enkel ting å gjøre - Slicer, Tools, Options, enten Report Connections eller Slicer Connections i den gamle versjonen, og sjekk at du vil at denne Slicer skal kontrollere alle disse pivottabellene. Enkelt, ikke sant? Dette spørsmålet handler om dette regnearket, hvor vi har to forskjellige datasett, og vi skal lage en pivottabell fra dette, og fra dette - la meg nå øke hastigheten på videoen mens jeg oppretter disse pivottabellene. Ok, nå, det du kommer til å se er at jeg har to pivottabeller, denne pivottabellen er opprettet fra ett datasett, og det er en skiver som styrer pivottabellen;og så har jeg en andre pivottabell som er opprettet fra et annet datasett, og en skiver som styrer pivottabellen. Men det er absolutt ingen måte å få denne skiveren til å kontrollere både denne pivottabellen og denne pivottabellen som er bygget fra et annet datasett. Ok. Men jeg skal vise deg hvordan du gjør det i dag med en makro.
Dette er vanskelig å gjøre. Da spørsmålet kom inn, sa jeg: "Nå, dette, jeg tror ikke du kan gjøre det." Men jeg har jobbet med det og eksperimentert, og jeg tror jeg endelig fikk det. Jeg må tro at jeg endelig fikk det nede. Ok, så la oss gå gjennom dette. For det første lagres dette som en xlsx-fil. Det er en fin filtype, bortsett fra at den er en fryktelig filtype fordi den er den eneste filtypen som ikke tillater makroer. Du må endre dette fra xlsx til xlsm, ellers vil alt arbeidet ditt til resten av videoen bli kastet ut av vinduet. Lagre som, endre filtypen til xlsm eller, pokker, xlsb, en av disse vil fungere. Det er den som er ødelagt-- xlsx-- og det er standard, gal er det ikke? Xlsm, klikk Lagre. Hvis du aldri har gjort makroer før, Alt + T for Tom, M for makro,S for Security, og du vil kunne lagre alle makroer uten varsel. Må endre det til det andre, slik at makroene dine kan fungere.
OK, nå har vi to snitt. Sats på at du aldri visste dette, men skiver har navn. Vi skal gå til Slicer Tools, Options, Slicer Settings, og se denne heter Slicer_Name. Slik. Gå til den andre, gå til Slicer Tools, Options, Slicer Settings, denne heter Slicer_Name1 - ikke Navn mellomrom 1, Name1. To sånne navn.
Her er hva vi skal gjøre. Vi skal bytte til VBA-- Alt + F11. I VBA, hvis du aldri har gjort VBA, vil du ha denne store grå skjermen. Vi kommer hit og sier View, Project Explorer, i Project Explorer, finn filen din - gruven heter Podcast 2104. Åpne Microsoft Excel-objekter, og arket der jeg vil at dette skal fungere, heter Dashboard. Jeg skal høyreklikke der og si View Code. Denne koden som vi skriver kan ikke gå i en modul som i en vanlig makro - dette må være på dette regnearket. Åpne rullegardinmenyen øverst til venstre, Regneark, og i rullegardinmenyen øverst til høyre skal vi si Pivot Table Update. OK, så det er her koden vår kommer til å gå nå. Jeg har allerede forhåndsbakt denne koden. La oss ta en titt på koden her i notisblokken. Så vikommer til å ha to Slicer-cacher - SC1 og SC2 - ett Slicer-element, og så, her, er det her du må tilpasse det. Så mine to skiver ble kalt Navn og Navn1. Greit, du blir nødt til å legge skivernavnet ditt der inne. Application.Screenupdating = False, Application.EnableEvents = False, og deretter Slicer Cache 2 - vi skal tømme filteret, og deretter for hvert element SI1 og sc1.SlicerItems, hvis det er valgt, så skal vi lage det samme elementet i Slicer Cache som skal velges. Dette er en liten sløyfe som vil gå gjennom, men mange ting tilfeldigvis er i den skiveren. I mitt tilfelle har jeg 11 eller 12; i ditt tilfelle kan du ha mer.Så mine to skiver ble kalt Navn og Navn1. Greit, du blir nødt til å legge skivernavnet ditt der inne. Application.Screenupdating = False, Application.EnableEvents = False, og deretter Slicer Cache 2 - vi skal tømme filteret, og deretter for hvert element SI1 og sc1.SlicerItems, hvis det er valgt, så skal vi lage det samme elementet i Slicer Cache som skal velges. Dette er en liten sløyfe som vil gå gjennom, men mange ting tilfeldigvis er i den skiveren. I mitt tilfelle har jeg 11 eller 12; i ditt tilfelle kan du ha mer.Så mine to skiver ble kalt Navn og Navn1. Greit, du blir nødt til å legge skivernavnet ditt der inne. Application.Screenupdating = False, Application.EnableEvents = False, og deretter Slicer Cache 2 - vi skal tømme filteret, og deretter for hvert element SI1 og sc1.SlicerItems, hvis det er valgt, så skal vi lage det samme elementet i Slicer Cache som skal velges. Dette er en liten sløyfe som vil gå gjennom, men mange ting tilfeldigvis er i den skiveren. I mitt tilfelle har jeg 11 eller 12; i ditt tilfelle kan du ha mer.skal lage det samme elementet i Slicer Cache som skal velges. Dette er en liten sløyfe som vil gå gjennom, men mange ting tilfeldigvis er i den skiveren. I mitt tilfelle har jeg 11 eller 12; i ditt tilfelle kan du ha mer.skal lage det samme elementet i Slicer Cache som skal velges. Dette er en liten sløyfe som vil gå gjennom, men mange ting tilfeldigvis er i den skiveren. I mitt tilfelle har jeg 11 eller 12; i ditt tilfelle kan du ha mer.
Når vi er ferdige med det, slår du på aktivere hendelser igjen, slår på skjermoppdatering igjen. Ok. Så vi tar denne koden, kopierer denne koden og limer den inn her i makroen slik. Ok, la oss bare sørge for at jeg kommer til å trykke på Ctrl + G og spør om applikasjon. Aktiver hendelser, på eller av - så,? Application.EnableEvents-- og det er sant. Hvis din kommer opp som falsk, vil du komme tilbake hit og si at det er = Sant-- så du slår på hendelsene. Ok. Her er hva som kommer til å skje. Så treneren vår burde jobbe her, den ligger på høyre regneark. Vi er lagret i en xlxm-fil, og jeg slo på makroer, og det vi skal se, er at når jeg velger fra venstre Slicer, at Slicer Cache 1-- I 'Jeg velger Andy gjennom Della - den andre Slicer kommer også til å oppdatere. Greit, og selv om jeg bare ville velge Gloria-- bare Gloria-- ser det ut til at det fungerer veldig bra. Selv om jeg vil CTRL + klikke, når jeg slipper Ctrl, oppdateres de alle tre.
Men her er gotcha-- det er alltid en gotcha-- denne Slicer, den må eksistere, men du kan ikke bruke denne Slicer-- vent, jeg mener du kan, du kan bruke en Slicer, men det kommer til å forvirre pokker ut av ting . Fordi det som kommer til å skje, skal jeg endre dette til Hank, og de kommer til å gå tilbake til det som er i Slicer Cache 1, fordi jeg endret pivottabellen på dette arket. Nå, i det virkelige liv, skal du ha to pivottabeller på samme ark? Jeg vet ikke om du er det eller ikke, greit, men ting kommer til å bli litt sprø.
La oss bare se på dette. Det første jeg vil gjøre er at jeg skal sette inn et nytt regneark - Alt + IW for å sette inn regnearket - og jeg skal kalle dette en DarkCave. Du kan kalle det hva du vil. Jeg skal ta det dashbordet som ikke kommer til å fungere. Jeg skal kopiere dashbordet og komme hit til den mørke hulen og lime det inn der, og deretter høyreklikke og skjule det arket slik at ingen noen gang ser den Slicer. Og så, herfra, skal vi kunne slette det. Fint, ok. Og vi skal bare sjekke at de fortsatt jobber - velg Charlie gjennom Eddie, og de oppdaterer fortsatt. Hva skjer nå? Slicer som vi ikke kan se, den vi har gjemt bort, den oppdateres også, men vi bryr oss ikke om at den oppdateres.
Nå, hva om du vil ha tingene dine på forskjellige ark? Jeg setter inn et nytt regneark her - Alt + IW-- og jeg tar en av disse pivottabellene - kanskje den andre pivottabellen - og flytter den til det andre arket - så, Ctrl + C for å kopiere pivottabellen, Ctrl + V for å lime inn pivottabellen her. Og hvis jeg trenger å ha en skiver her - ikke sett inn en skive fra dette pivottabellen - må vi komme tilbake til dashbordet vårt, ta skiveren som er den kontrollerende skiver, Ctrl + C for å lage en kopi av den, og lim den inn her - Ctrl + V. Ok? Nå har vi ingen kode på dette arket-- det er ingen kode på Sheet4-- og jeg tenkte at jeg måtte legge til litt kode i Sheet4, men her er det vakre: Når jeg endrer denne skiveren, er det som skjer, på dashbordet som pivottabellen 's oppdatering selv om pivottabellen på det arket som ikke er aktiv, oppdateres, vil de kjøre koden, og denne oppdateres også. Ganske utrolig utrolig 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 deg for at du var innom, vi sees neste gang for nok en netcast fra.
Last ned fil
Last ned eksempelfilen her: Podcast2104.xlsm
