Slicers ovat mahtavia pivot-taulukoille, koska voit hallita useita pivot-taulukoita yhdestä viipaleista. Mutta - se on eräänlainen valhe. Voit hallita useita pivot-taulukoita, jotka ovat peräisin samasta tietojoukosta. Kun sinulla on pivot-taulukoita, jotka ovat peräisin kahdesta eri tietojoukosta, se on melko hankalaa. Näytän sinulle jonkin verran VBA: ta, jonka avulla voit vetää tämän pois.
Katso video
- Kuinka voit leikkauslaitteen käyttää kahta kääntöpöytää?
- Jos molemmat kääntötaulukot tulivat samasta tietojoukosta: Valitse Slicer, Report Connections, Choose Other Pivot Taulukot
- Mutta jos pivot-taulukot ovat peräisin eri tietojoukoista:
- Käytä Tallenna nimellä vaihtaaksesi työkirjan laajennuksen XLSM: ään XLSX: n sijaan
- Käytä alt = "" + TMS ja muuta makroturva toiseen asetukseen.
- Alt + F11 päästäksesi VBA: lle
- Ctrl + R näyttääksesi projektinhallinnan
- Etsi laskentataulukko, joka sisältää ensimmäisen kääntötaulukon ja osittajan
- Lisää Worksheet_Update-koodi
- Piilota toinen viipalointilaite pois niin, että se pysyy olemassa, mutta kukaan ei voi koskaan valita viipaleesta
Videon transkriptio
Opi Excel for Podcast, jakso 2104: Synkronoi viipaleet eri tietojoukoista.
Hei, tervetuloa takaisin netcastiin, olen Bill Jelen, ja tämän päivän kysymys ei ole siitä, kuinka ottaa nämä kaksi yhdestä tietojoukosta peräisin olevaa pivot-taulukkoa ja saada Slicer hallitsemaan kaikkia näitä pivot-taulukoita. Siitä ei ole kyse. Se on helppo tehdä - Slicer, Tools, Options, joko Report Connections tai Slicer Connections vanhassa versiossa, ja tarkista, että haluat, että Slicer ohjaa kaikkia näitä pivot-taulukoita. Helppo, eikö? Tämä kysymys koskee tätä laskentataulukkoa, jossa meillä on kaksi erilaista tietojoukkoa ja aiomme luoda tästä pivot-taulukon ja tästä - anna minun nyt nopeuttaa videota, kun luon näitä pivot-taulukoita. Selvä, nyt aiot nähdä, että minulla on kaksi pivot-taulukkoa, tämä pivot-taulukko on luotu yhdestä tietojoukosta, ja siellä on leikkuri, joka ohjaa pivot-taulukkoa;ja sitten minulla on toinen pivot-taulukko, joka on luotu eri tietojoukosta, ja slicer, joka ohjaa kyseistä pivot-taulukkoa. Mutta ei ole mitään tapaa saada tämä viipale ohjaamaan sekä tätä pivot-taulukkoa että tätä pivot-taulukkoa, joka on rakennettu eri tietojoukosta. Hyvä on. Mutta aion näyttää, miten se tehdään tänään makrolla.
Nyt tämä on hankala tehdä. Kun kysymys tuli, sanoin: "Nyt, en usko, että voit tehdä sen." Mutta olen työskennellyt sen kanssa ja kokeillut ja luulen, että sain sen vihdoin. Minun täytyy ajatella, että sain sen vihdoin. Selvä, joten käydään tämä läpi. Ensinnäkin tämä tallennetaan xlsx-tiedostona. Se on hieno tiedostotyyppi, paitsi että se on kamala tiedostotyyppi, koska se on ainoa tiedostotyyppi, joka ei salli makroja. Sinun on vaihdettava tämä xlsx: stä xlsm: ään, tai kaikki työsi muuhun videoon heitetään ulos ikkunasta. Tallenna nimellä, vaihda tiedostotyypiksi xlsm tai heck, xlsb, jompikumpi niistä toimii. Se on rikki - xlsx - ja se on oletusarvo, hullu eikö niin? Xlsm, napsauta Tallenna. Jos et ole koskaan aiemmin tehnyt makroja, Alt + T Tomille, M Makrolle,S for Security ja voit tallentaa kaikki makrot ilman ilmoitusta. Sinun on vaihdettava se toiseen, jolloin makrosi toimivat.
Selvä, nyt meillä on kaksi viipaleita. Et voi koskaan tiennyt tätä, mutta viipaleilla on nimiä. Menemme Slicer Tools, Options, Slicer Settings ja näemme tämän olevan Slicer_Name. Niin. Siirry toiseen, mene Slicer Tools, Options, Slicer Settings, tätä kutsutaan Slicer_Name1 - ei nimitila 1, Name1. Kaksi sellaista nimeä.
Tässä mitä aiomme tehdä. Aiomme siirtyä käyttämään VBA - Alt + F11. Jos et ole koskaan tehnyt VBA: ta VBA: ssa, sinulla on tämä iso harmaa näyttö. Aiomme tulla tänne ja sanoa Näytä, Project Explorer, Project Explorerissa, etsi tiedosto - minun nimi Podcast 2104. Avaa Microsoft Excel -objektit, ja taulukko, jossa haluan tämän toimivan, on Dashboard. Napsautan sitä hiiren kakkospainikkeella ja sanon Näytä koodi. Tätä kirjoittamaamme koodia ei voi käyttää moduulissa kuten tavallisessa makrossa - tämän on oltava tässä laskentataulukossa. Avaa vasemmassa yläkulmassa oleva laskentataulukko, sitten oikeassa yläkulmassa olevassa pudotusvalikossa sanotaan Pivot-taulukon päivitys. Selvä, joten koodimme menee nyt. Olen jo paistanut tämän koodin. Katsotaanpa koodia täällä muistilehdessä. Joten meSinulla on kaksi Slicer-välimuistia - SC1 ja SC2 - yksi Slicer-kohde ja sitten juuri täällä, sinun on mukautettava se. Joten kahta viipalettaani kutsuttiin nimellä Nimi ja Nimi1. Selvä, sinun on laitettava viipaleesi nimesi sinne. Application.Screenupdating = False, Application.EnableEvents = False ja sitten Slicer Cache 2 - tyhjennämme suodattimen ja sitten jokaiselle SI1 ja sc1.SlicerItems-kohteelle, jos se on valittu, teemme sitten sama kohde Slicer-välimuistissa. Tämä on pieni silmukka, joka kulkee läpi, vaikka monia esineitä sattuu olemaan tuossa viipalointilaitteessa. Minun tapauksessani minulla on 11 tai 12; sinun tapauksessasi sinulla saattaa olla enemmän.Joten kahta viipalettaani kutsuttiin nimellä Nimi ja Nimi1. Selvä, sinun on laitettava viipaleesi nimesi sinne. Application.Screenupdating = False, Application.EnableEvents = False ja sitten Slicer Cache 2 - tyhjennämme suodattimen ja sitten jokaiselle SI1 ja sc1.SlicerItems-kohteelle, jos se on valittu, teemme sitten sama kohde Slicer-välimuistissa. Tämä on pieni silmukka, joka kulkee läpi, vaikka monia esineitä sattuu olemaan tuossa viipalointilaitteessa. Minun tapauksessani minulla on 11 tai 12; sinun tapauksessasi sinulla saattaa olla enemmän.Joten kahta viipalettaani kutsuttiin nimellä Nimi ja Nimi1. Selvä, sinun on laitettava viipaleesi nimesi sinne. Application.Screenupdating = False, Application.EnableEvents = False ja sitten Slicer Cache 2 - tyhjennämme suodattimen ja sitten jokaiselle SI1 ja sc1.SlicerItems-kohteelle, jos se on valittu, teemme sitten sama kohde Slicer-välimuistissa. Tämä on pieni silmukka, joka kulkee läpi, vaikka monia esineitä sattuu olemaan tuossa viipalointilaitteessa. Minun tapauksessani minulla on 11 tai 12; sinun tapauksessasi sinulla saattaa olla enemmän.aion tehdä saman kohteen Slicer-välimuistissa valittavaksi. Tämä on pieni silmukka, joka kulkee läpi, vaikka monia esineitä sattuu olemaan tuossa viipalointilaitteessa. Minun tapauksessani minulla on 11 tai 12; sinun tapauksessasi sinulla saattaa olla enemmän.aion tehdä saman kohteen Slicer-välimuistissa valittavaksi. Tämä on pieni silmukka, joka kulkee läpi, vaikka monia esineitä sattuu olemaan tuossa viipalointilaitteessa. Minun tapauksessani minulla on 11 tai 12; sinun tapauksessasi sinulla saattaa olla enemmän.
Kun tämä on valmis, kytke aktivointitapahtumat takaisin päälle, ota näytön päivitys uudelleen käyttöön. Hyvä on. Joten otamme tämän koodin, kopioimme tämän koodin ja liitämme sen tähän keskelle makroamme. Selvä, nyt vain varmistetaan, että aion painaa Ctrl + G ja pyytäni on Application.EnableEvents, päällä tai pois - niin,? Application.EnableEvents-- ja se on totta. Jos sinun on väärä, niin haluat palata tänne ja sanoa, että se on = Tosi - niin, sitten otat nuo tapahtumat käyttöön. Hyvä on. Nyt, mitä tapahtuu. Joten valmentajamme pitäisi työskennellä täällä, se on oikealla laskentataulukolla. Meidät on tallennettu xlxm-tiedostoon, ja otin makrot käyttöön ja mitä aiomme nähdä, on se, että kun valitsen vasemman Slicerin, Slicer Cache 1 - I 'Valitsen Andyn Dellan kautta - myös toinen Slicer päivittyy. Okei Ja vaikka valitsisin vain Gloria - vain Gloria - näyttää siltä, että se toimii todella, todella hyvin. Vaikka haluaisin CTRL + napsauttaa, kun päästän Ctrl: n irti, ne kaikki kolme päivittyvät.
Mutta tässä on gotcha - siellä on aina gotcha - tämä Slicer, sen on oltava olemassa, mutta et voi käyttää tätä Sliceria - odota, tarkoitan, että voit, voit käyttää Sliceria, mutta se hämmentää asioita . Koska mitä tapahtuu, vaihdan tämän Hankiksi ja he palaavat mihin tahansa Slicer Cache 1: een, koska muutin tämän taulukon pivot-taulukkoa. Onko sinulla tosielämässä nyt kaksi kääntötaulukkoa samalla arkilla? En tiedä, oletko vai et, okei, mutta asiat muuttuvat hieman hulluiksi.
Katsotaanpa nyt vain tätä. Ensimmäinen asia, jonka haluan tehdä, aion lisätä uuden laskentataulukon - Alt + IW laskentataulukon lisäämiseksi - ja aion kutsua tätä DarkCaveiksi. Voit kutsua sitä millä tahansa haluat. Otan sen kojelaudan, joka ei toimi, aion kopioida kojelaudan ja tulla tänne pimeään luolaan ja liittää sen sinne ja napsauttaa hiiren kakkospainikkeella ja piilottaa kyseisen arkin, jotta kukaan ei koskaan näe sitä Sliceria. Ja sitten täältä meidän pitäisi pystyä poistamaan se. Hyvä, okei. Tarkistamme vain, että he työskentelevät edelleen - valitse Charlie Eddien kautta ja he molemmat päivittyvät edelleen. Mitä nyt tapahtuu? Slicer, jota emme näe, joka olemme piilottaneet pois, se päivittyy myös, mutta emme välitä siitä, että se päivitetään.
Entä jos haluat, että omasi ovat eri arkkeissa? Lisään uuden laskentataulukon tähän - Alt + IW - ja otan yhden näistä pivot-taulukoista - ehkä toisen pivot-taulukon - ja siirrän sen toiseen taulukkoon - joten kopioi Ctrl + C pivot-taulukko, Ctrl + V liittää pivot-taulukko tähän. Ja jos minun tarvitsee olla leikkuri täällä - älä lisää viipaleita tästä pivot-taulukosta - meidän on palattava kojelautaan, ottamalla viipale, joka on hallitseva Slicer, Ctrl + C, jotta voit tehdä siitä kopion, ja liitä se tähän - Ctrl + V. Hyvä on? Meillä ei ole koodia tällä arkilla - Sheet4: ssä ei ole koodia - ja ajattelin, että minun on lisättävä koodi Sheet4: een, mutta tässä on kaunis asia: Kun vaihdan tätä viipaletta, mitä tapahtuu, kojelaudassa kyseinen kääntöpöytä ''s päivittää, vaikka kyseisen taulukon pivot-taulukko, joka ei ole aktiivinen, päivittyy, he suorittavat koodin ja myös tämä päivittyy. Melko hämmästyttävä, että se toimii.
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.
Haluamme kiittää sinua käymisestäsi, näemme sinut ensi kerralla uudesta netcastista.
Lataa tiedosto
Lataa esimerkkitiedosto täältä: Podcast2104.xlsm
