Rezači su izvrsni za zaokretne tablice jer možete kontrolirati više zaokretnih tablica iz jednog seta rezača. Ali - to je vrsta laži. Možete kontrolirati više zaokretnih tablica koje dolaze iz istog skupa podataka. Kada imate pivot tablice koje su potekle iz dva različita skupa podataka, to je prilično nezgodno. Pokazat ću vam neki VBA koji će vam omogućiti da to izvedete.
Gledaj video
- Kako možete imati rezalicu za pogon dvije stožerne tablice?
- Ako su obje zaokretne tablice došle iz istog skupa podataka: odaberite Slicer, Report Connections, Select Other Pivot Tables
- Ali ako su pivot tablice dolazile iz različitih skupova podataka:
- Upotrijebite Spremi kao da biste proširenje radne knjige promijenili u XLSM umjesto XLSX
- Upotrijebite alt = "" + TMS i promijenite sigurnost makronaredbe na drugu postavku.
- Alt + F11 da biste došli do VBA
- Ctrl + R za prikaz istraživača projekta
- Pronađite radni list koji sadrži vašu prvu zaokretnu tablicu i rezač
- Umetnite kod za Worksheet_Update
- Sakrijte drugu rezač tako da nastavi postojati, ali nitko nikada ne može odabrati tu rezač
Prijepis videozapisa
Naučite Excel za podcast, epizoda 2104: Sinkronizirajte rezače iz različitih skupova podataka.
Hej, dobrodošao natrag u netcast, ja sam Bill Jelen, a današnje pitanje nije o tome kako uzeti ove dvije stožerne tablice koje su proizašle iz jednog skupa podataka i natjerati Slicer da kontrolira sve te pivot tablice. Nije stvar u tome. To je lako učiniti - Slicer, Tools, Options, Report Connections ili Slicer Connections u staroj verziji i provjerite želite li da ovaj Slicer kontrolira sve te stožerne tablice. Lako, zar ne? Ovo se pitanje odnosi na ovaj radni list, gdje imamo dva različita skupa podataka i iz toga ćemo stvoriti zaokretnu tablicu, a od ovoga - sad mi dopustite da ubrzam videozapis dok stvaram ove zaokretne tablice. Dobro, ono što ćete vidjeti je, imam dvije zaokretne tablice, ova zaokretna tablica kreirana je iz jednog skupa podataka, a tu je i rezalica koja kontrolira tu zaokretnu tablicu;a zatim imam drugu zaokretnu tablicu koja je stvorena iz drugog skupa podataka i rezalicu koja kontrolira tu zaokretnu tablicu. Ali apsolutno ne postoji način da ovaj rezač kontrolira i ovu zaokretnu tablicu i ovu zaokretnu tablicu izrađenu iz drugog skupa podataka. U redu. Ali pokazat ću vam kako to danas učiniti s makronaredbom.
Sad je ovo nezgodno za napraviti. Kad je pitanje ušlo, rekao sam, "Sad, ovo, mislim da to ne možete učiniti." Ali radio sam na tome i eksperimentirao i mislim da sam ga napokon dobio. Moram misliti da sam ga napokon donio. Dobro, prođimo kroz ovo. Prvo se sprema kao xlsx datoteka. To je dobar tip datoteke, osim što je užasnog tipa, jer to je jedina vrsta datoteke koja ne dopušta makronaredbe. Morate to promijeniti iz xlsx u xlsm, ili će sav vaš rad u ostatku videozapisa biti izbačen kroz prozor. Spremi kao, promijeni vrstu datoteke u xlsm ili, dovraga, xlsb, bilo koja od njih će raditi. To je onaj koji je slomljen - xlsx - i to je zadani zadatak, zar ne? Xlsm, kliknite Spremi. Ako nikada prije niste radili makronaredbe, Alt + T za Toma, M za makronaredbe,S for Security i moći ćete spremiti sve makronaredbe bez obavijesti. Trebate to promijeniti u drugi, koji će omogućiti vašim makronaredbama da rade.
U redu, sada imamo dvije rezačice. Kladim se da ovo nikada niste znali, ali rezači imaju imena. Idemo na Alat za rezanje, Mogućnosti, Postavke rezača i vidjet ćemo da se ovaj zove Slicer_Name. Onako. Idite na drugu, idite na Slicer Tools, Options, Slicer Settings, ovaj se zove Slicer_Name1 - ne Name space 1, Name1. Dva takva imena.
Evo što ćemo učiniti. Prebacit ćemo se na VBA-- Alt + F11. Ako nikada niste radili VBA u VBA-u, imat ćete ovaj veliki sivi zaslon. Doći ćemo ovdje i reći View, Project Explorer, u Project Exploreru pronađite svoju datoteku - moja se zove Podcast 2104. Otvorite Microsoft Excel Objects, a list na kojem želim da ovo radi zove se Nadzorna ploča. Idem tamo kliknuti desnom tipkom i reći View Code. Ovaj kod koji pišemo ne može ići u modulu kao u uobičajenoj makronaredbi - to mora biti na ovom radnom listu. Otvorite gornji lijevi padajući meni, Radni list, a zatim u gornjem desnom padajućem izborniku reći ćemo Ažuriranje zaokretne tablice. U redu, dakle, ovdje će ići naš kod. Već sam ispekao ovaj kod. Pogledajmo kod ovdje u notepadu. Dakle, mi "imat ćete dvije predmemorije Slicer-a - SC1 i SC2-- jednu stavku Slicer-a, a ovdje ćete je ovdje morati prilagoditi. Tako su se moja dva rezača zvala Ime i Ime1. U redu, morat ćete tamo staviti imena svojih rezača. Application.Screenupdating = False, Application.EnableEvents = False, a zatim predmemorija Slicer 2 - očistit ćemo filtar, a zatim za svaku stavku SI1 i sc1.SlicerItems, ako je odabrana, tada ćemo napraviti ista stavka u predmemoriji rezača koja će se odabrati. Ovo je mala petlja koja će se provući bez obzira na to što se puno predmeta nalazi u tom rezaču. U mom slučaju imam 11 ili 12; u vašem biste slučaju mogli imati i više.Tako su se moja dva rezača zvala Ime i Ime1. U redu, morat ćete tamo staviti imena svojih rezača. Application.Screenupdating = False, Application.EnableEvents = False, a zatim Slicer Cache 2 - očistit ćemo filtar, a zatim za svaku stavku SI1 i sc1.SlicerItems, ako je odabrana, napravit ćemo ista stavka u predmemoriji rezača koja će se odabrati. Ovo je mala petlja koja će se provući bez obzira na to što se puno predmeta nalazi u tom rezaču. U mom slučaju imam 11 ili 12; u vašem biste slučaju mogli imati i više.Tako su se moja dva rezača zvala Ime i Ime1. U redu, morat ćete tamo staviti imena svojih rezača. Application.Screenupdating = False, Application.EnableEvents = False, a zatim Slicer Cache 2 - očistit ćemo filtar, a zatim za svaku stavku SI1 i sc1.SlicerItems, ako je odabrana, napravit ćemo ista stavka u predmemoriji rezača koja će se odabrati. Ovo je mala petlja koja će se provući bez obzira na to što se puno predmeta nalazi u tom rezaču. U mom slučaju imam 11 ili 12; u vašem biste slučaju mogli imati i više.izradit ćete istu stavku u predmemoriji rezača koja će biti izabrana. Ovo je mala petlja koja će se provući bez obzira na to što se puno predmeta nalazi u tom rezaču. U mom slučaju imam 11 ili 12; u vašem biste slučaju mogli imati i više.izradit ćete istu stavku u predmemoriji rezača koja će biti izabrana. Ovo je mala petlja koja će se provući bez obzira na to što se puno predmeta nalazi u tom rezaču. U mom slučaju imam 11 ili 12; u vašem biste slučaju mogli imati i više.
Kad završimo s tim, ponovo uključite omogućenje događaja, ponovno uključite Ažuriranje zaslona. U redu. Dakle, uzet ćemo ovaj kod, kopirati ga i zalijepiti ovdje u sredinu naše makronaredbe tako. Dobro, sada, samo pripazimo da pritisnem Ctrl + G i da tražim Application.EnableEvents, uključeno ili isključeno - pa,? Application.EnableEvents - i to je istina. Ako se vaše ispostavi kao lažno, onda se želite vratiti ovdje i reći da je = Istina - pa, onda, uključujete te događaje. U redu. Evo što će se dogoditi. Dakle, naš trener bi trebao raditi ovdje, na desnom je radnom listu. Spremljeni smo u datoteku xlxm, a ja sam uključio makronaredbe i ono što ćemo vidjeti je da, kada odaberem s lijevog rezača, taj predmemorija rezača 1-- ja 'Izabrat ću Andyja preko Delle - ažurirat će se i drugi Slicer. U redu. Čak i kad bih odabrala samo Gloriju - samo Gloriju - izgleda da to stvarno, jako dobro djeluje. Čak i ako bih pritisnuo CTRL + klik, kad pustim Ctrl, sve tri će se ažurirati.
Ali ovdje je problem - uvijek postoji problem - ovaj Slicer mora postojati, ali ne možete koristiti ovaj Slicer - pričekajte, mislim, možete, možete koristiti Slicer, ali to će zbuniti stvari . Jer ono što će se dogoditi jest da ću ovo promijeniti u Hanka, a oni će se vratiti na ono što je u Slicer Cache 1, jer sam promijenio stožernu tablicu na ovom listu. Sad ćete, u stvarnom životu, imati dvije stožerne tablice na istom listu? Ne znam jeste li ili niste, u redu, ali stvari će postati pomalo lude.
Ajmo sada samo pogledati ovo. Prvo što želim učiniti je da ubacim novi radni list - Alt + IW za umetanje radnog lista - i nazvat ću ovo DarkCave. Možete ga nazvati kako god želite. Uzeti ću onu nadzornu ploču koja neće raditi, kopirat ću tu nadzornu ploču i doći ovdje u mračnu špilju i zalijepiti je tamo, a zatim desnom tipkom miša sakriti taj list da nitko nikad ne vidi taj rezač. A onda bismo ga odavde trebali moći izbrisati. Lijepo, u redu. A mi ćemo samo provjeriti jesu li još uvijek zaposleni - odaberite Charlieja preko Eddieja i obojica se i dalje ažuriraju. Što se sada događa? Rezač koji ne možemo vidjeti, onaj koji smo sakrili, također se ažurira, ali nije nas briga što se ažurira.
Što ako želite imati svoje stvari na različitim listovima? Ovdje ću umetnuti novi radni list - Alt + IW - i uzeti ću jednu od ovih zaokretnih tablica - možda drugu zaokretnu tablicu - i premjestiti je na onaj drugi list - pa, Ctrl + C za kopiranje stožernu tablicu, Ctrl + V da biste ovdje zalijepili pivot tablicu. A ako ovdje trebam imati rezač - nemojte umetati presjek iz ove zaokretne tablice - moramo se vratiti na našu nadzornu ploču, uzeti rezač koji je kontrolni rezač, Ctrl + C da ga napravimo kopijom, i zalijepite ovdje-- Ctrl + V. U redu? Na ovom listu nemamo koda - na Sheet4 nema koda - i razmišljao sam da ću morati dodati neki kôd u Sheet4, ali evo lijepog: Kad promijenim ovaj rezač, ono što se događa je, na nadzornoj ploči ta zaokretna tablica 's ažuriranje iako se ažurira ona zaokretna tablica na onom listu koja nije aktivna, oni će pokrenuti kod i to će se također ažurirati. Prilično prokleto nevjerojatno da to djeluje.
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.
Želimo vam zahvaliti što ste navratili, vidimo se sljedeći put za još jedno emitiranje od.
Preuzmi datoteku
Preuzmite datoteku uzorka ovdje: Podcast2104.xlsm
