Průřezy jsou úžasné pro kontingenční tabulky, protože z jedné sady průřezů můžete ovládat více kontingenčních tabulek. Ale - to je nějaká lež. Můžete ovládat více kontingenčních tabulek, které pocházejí ze stejné datové sady. Pokud máte kontingenční tabulky, které pocházejí ze dvou různých datových sad, je to docela složité. Ukážu vám nějaký VBA, který vám umožní to zvládnout.
Sledovat video
- Jak můžete mít kráječ pohonu dvou kontingenčních tabulek?
- Pokud obě kontingenční tabulky pocházely ze stejné datové sady: Vyberte průřez, připojení sestavy, zvolte jiné kontingenční tabulky
- Pokud však kontingenční tabulky pocházely z různých datových sad:
- Pomocí Uložit jako změňte rozšíření sešitu na XLSM místo XLSX
- Použijte alt = "" + TMS a změňte zabezpečení maker na druhé nastavení.
- Alt + F11 se dostanete do VBA
- Ctrl + R pro zobrazení průzkumníka projektu
- Najděte list, který obsahuje první kontingenční tabulku a průřez
- Vložte kód pro Worksheet_Update
- Skrýt druhý průřez, aby zůstal existující, ale nikdo si z něj nikdy nemůže vybrat
Přepis videa
Naučte se Excel pro Podcast, epizoda 2104: Synchronizace průřezů z různých datových sad.
Ahoj, vítejte zpět na netcastu, jsem Bill Jelen a dnešní otázka není o tom, jak vzít tyto dvě kontingenční tabulky, které pocházejí z jedné datové sady, a zajistit, aby Slicer ovládal všechny tyto kontingenční tabulky. O tom to není. To je snadné - Slicer, Tools, Options, buď Report Connections nebo Slicer Connections ve staré verzi a zkontrolujte, zda chcete, aby tento Slicer ovládal všechny tyto kontingenční tabulky. Snadné, že? Tato otázka se týká tohoto listu, kde máme dvě různé datové sady a z toho vytvoříme kontingenční tabulku a z této - nyní mi dovolte zrychlit video, když vytvářím tyto kontingenční tabulky. Dobře, teď uvidíte, že mám dvě kontingenční tabulky, tato kontingenční tabulka je vytvořena z jedné datové sady a je zde průřez, který tuto kontingenční tabulku ovládá;a pak mám druhou kontingenční tabulku, která je vytvořena z jiné datové sady, a průřez, který tuto kontingenční tabulku ovládá. Ale neexistuje absolutně žádný způsob, jak přimět tento průřez k ovládání jak této kontingenční tabulky, tak této kontingenční tabulky, která je vytvořena z jiné datové sady. V pořádku. Ale ukážu vám, jak to dnes udělat pomocí makra.
Nyní je to složité. Když přišla otázka, řekl jsem: „No, nemyslím si, že to zvládneš.“ Ale pracoval jsem na tom a experimentoval a myslím, že jsem to konečně dostal. Musím si myslet, že jsem to konečně dostal dolů. Dobře, tak to projdeme. Nejprve se to uloží jako soubor xlsx. To je skvělý typ souboru, až na to, že je to hrozný typ souboru, protože je to jediný typ souboru, který neumožňuje makra. Musíte to změnit z xlsx na xlsm, jinak bude veškerá vaše práce se zbytkem videa vyhozena z okna. Uložit jako, změnit typ souboru na xlsm nebo sakra xlsb, jeden z nich bude fungovat. To je ten, který je rozbitý - xlsx - a je to výchozí, šílené, že? Xlsm, klikněte na Uložit. Pokud jste ještě nikdy makra nedělali, Alt + T pro Toma, M pro Makro,S pro zabezpečení a budete moci uložit všechna makra bez upozornění. Musíte to změnit na druhé, což umožní, aby vaše makra fungovala.
Dobře, teď máme dva kráječe. Vsadím se, že jste to nikdy nevěděli, ale kráječe mají jména. Půjdeme do Nástroje pro krájení, Možnosti, Nastavení kráječe a uvidíme, že tenhle se jmenuje Slicer_Name. Takhle. Přejděte na druhou, přejděte na Nástroje pro krájení, Možnosti, Nastavení kráječe, tento se jmenuje Slicer_Name1 - ne Název prostoru 1, Název1. Dvě taková jména.
Tady budeme dělat. Přepneme na VBA-- Alt + F11. Ve VBA, pokud jste nikdy neudělali VBA, budete mít tuto velkou šedou obrazovku. Pojdeme sem a řekneme View, Project Explorer, v Project Explorer najděte svůj soubor - můj se jmenuje Podcast 2104. Otevřete Microsoft Excel Objects a list, kde chci, aby to fungovalo, se jmenuje Dashboard. Kliknu tam pravým tlačítkem a řeknu Zobrazit kód. Tento kód, který píšeme, nelze použít v modulu jako v běžném makru - toto musí být na tomto listu. Otevřete rozevírací seznam vlevo nahoře, list, pak v rozevíracím seznamu vpravo nahoře řekneme aktualizaci kontingenční tabulky. Dobře, tak tady teď náš kód půjde. Tento kód jsem již předpečel. Podívejme se na kód zde v poznámkovém bloku. Takže mybudete mít dvě mezipaměti Slicer - SC1 a SC2 - jednu položku Slicer a pak právě tady ji budete muset přizpůsobit. Takže moje dva kráječe se jmenovaly Jméno a Jméno1. Dobře, budete tam muset vložit jména krájení. Application.Screenupdating = False, Application.EnableEvents = False a poté mezipaměť Slicer 2 - vymažeme filtr a poté pro každou položku SI1 a sc1.SlicerItems, pokud je vybrána, uděláme vyberte stejnou položku v mezipaměti Slicer Cache. Jedná se o malou smyčku, která proběhne, ale v tomto průřezu je mnoho položek. V mém případě mám 11 nebo 12; ve vašem případě můžete mít více.Takže moje dva kráječe se jmenovaly Jméno a Jméno1. Dobře, budete tam muset vložit jména krájení. Application.Screenupdating = False, Application.EnableEvents = False a poté mezipaměť Slicer 2 - vymažeme filtr a poté pro každou položku SI1 a sc1.SlicerItems, pokud je vybrána, pak uděláme vyberte stejnou položku v mezipaměti Slicer Cache. Jedná se o malou smyčku, která proběhne, ale v tomto průřezu je mnoho položek. V mém případě mám 11 nebo 12; ve vašem případě můžete mít více.Takže moje dva kráječe se jmenovaly Jméno a Jméno1. Dobře, budete tam muset vložit jména krájení. Application.Screenupdating = False, Application.EnableEvents = False a poté mezipaměť Slicer 2 - vymažeme filtr a poté pro každou položku SI1 a sc1.SlicerItems, pokud je vybrána, pak uděláme vyberte stejnou položku v mezipaměti Slicer Cache. Jedná se o malou smyčku, která proběhne, ale v tomto průřezu je mnoho položek. V mém případě mám 11 nebo 12; ve vašem případě můžete mít více.se chystáte udělat stejnou položku v mezipaměti Slicer, kterou chcete vybrat. Jedná se o malou smyčku, která proběhne, ale v tomto průřezu je mnoho položek. V mém případě mám 11 nebo 12; ve vašem případě můžete mít více.se chystáte udělat stejnou položku v mezipaměti Slicer, kterou chcete vybrat. Jedná se o malou smyčku, která proběhne, ale v tomto průřezu je mnoho položek. V mém případě mám 11 nebo 12; ve vašem případě můžete mít více.
Až to uděláme, znovu zapněte události povolení a znovu zapněte aktualizaci obrazovky. V pořádku. Vezmeme tedy tento kód, zkopírujeme jej a vložíme jej sem uprostřed takového makra. Dobře, pojďme se jen ujistit, že stisknu Ctrl + G a moje žádost o je Application.EnableEvents, zapnuto nebo vypnuto - ano,? Application.EnableEvents - a je to pravda. Pokud se váš objeví jako falešný, pak se sem chcete vrátit a říct, že je to = Pravda - takže tedy tyto události zapínáte. V pořádku. Tady je to, co se stane. Náš trenér by zde tedy měl pracovat, je to na správném listu. Jsme uloženi v souboru xlxm a zapnul jsem makra a to, co uvidíme, je, že když si vyberu z levého Sliceru, ten mezipaměť Slicer 1 - I 'Vyberu Andyho prostřednictvím Delly - aktualizuje se také druhý Slicer. Dobře. A i kdybych si vybral jen Gloria - jen Gloria - vypadá to, že to funguje opravdu, opravdu dobře. I kdybych CTRL + klikl, když pustím Ctrl, všechny tři se aktualizují.
Ale tady je gotcha-- vždy existuje gotcha-- tento Slicer, musí existovat, ale nemůžete použít tento Slicer-- počkejte, myslím, že můžete, můžete použít Slicer, ale bude to matoucí sakra z věcí . Protože to, co se stane, je, že to změním na Hanka a oni se vrátí zpět na cokoli v Slicer Cache 1, protože jsem změnil kontingenční tabulku na tomto listu. Nyní, v reálném životě, budete mít na jednom listu dvě kontingenční tabulky? Nevím, jestli jste, nebo nejste, dobře, ale věci se stanou trochu šílenými.
Pojďme se na to podívat. První věc, kterou chci udělat, je vložit nový list - Alt + IW pro vložení listu - a já tomu budu říkat DarkCave. Můžete to nazvat jak chcete. Vezmu si palubní desku, která nebude fungovat, zkopíruji tu palubní desku a pojedu sem do temné jeskyně a vložím ji tam a pak kliknu pravým tlačítkem a skryji ten list, takže ten Slicer nikdy nikdo nevidí. A odtud bychom to měli mít možnost smazat. Dobře, dobře. A my jen zkontrolujeme, abychom se ujistili, že stále fungují - vyberte Charlieho prostřednictvím Eddieho a oba se stále aktualizují. Co se děje? Slicer, který nevidíme, ten, který jsme skryli, se také aktualizuje, ale je nám jedno, že se aktualizuje.
Co když chcete mít své věci na různých listech? Vložím sem nový list - Alt + IW - a vezmu jednu z těchto kontingenčních tabulek - možná druhou kontingenční tabulku - a přesunu ji na tento další list - takže Ctrl + C zkopírujte kontingenční tabulku, Ctrl + V sem vložíte kontingenční tabulku. A pokud zde potřebuji mít průřez - nevkládejte výřez z této kontingenční tabulky - musíme se vrátit na naši palubní desku, vzít průřez, který je ovládající průřez, Ctrl + C a vytvořit jeho kopii, a vložte jej sem - Ctrl + V. V pořádku? Nyní na tomto listu nemáme žádný kód - na Sheet4 žádný kód - a myslel jsem si, že budu muset přidat nějaký kód do Sheet4, ale tady je ta krásná věc: Když změním tento průřez, co se děje, na palubní desce, která otáčí stůl 's aktualizace, i když se tato kontingenční tabulka na tomto listu, který není aktivní, aktualizuje, spustí kód a také se aktualizuje. Docela zatraceně úžasné, že to funguje.
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.
Chtěli bychom vám poděkovat, že jste se zastavili, uvidíme se příště pro další netcast z.
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2104.xlsm
