Krájače sú úžasné pre kontingenčné tabuľky, pretože z jednej sady krájačov môžete ovládať viac kontingenčných tabuliek. Ale - to je klamstvo. Môžete ovládať viac kontingenčných tabuliek, ktoré pochádzajú z rovnakej množiny údajov. Ak máte kontingenčné tabuľky, ktoré pochádzajú z dvoch rôznych množín údajov, je to dosť zložité. Ukážem vám niekoľko VBA, ktoré vám umožnia toto pretiahnuť.
Pozeraj video
- Ako môžete mať krájač, ktorý poháňa dva kontingenčné tabuľky?
- Ak obe kontingenčné tabuľky pochádzali z tej istej množiny údajov: Vyberte Slicer, Správa spojov, Vyberte Iné kontingenčné tabuľky
- Ak však kontingenčné tabuľky pochádzali z rôznych súborov údajov:
- Pomocou príkazu Uložiť ako zmeňte rozšírenie zošita na XLSM namiesto XLSX
- Použite alt = "" + TMS a zmeňte zabezpečenie makra na druhé nastavenie.
- Alt + F11 sa dostanete do VBA
- Ctrl + R na zobrazenie prieskumníka projektu
- Nájdite pracovný hárok, ktorý obsahuje vašu prvú kontingenčnú tabuľku a krájač
- Vložte kód pre Worksheet_Update
- Skryte druhý krájač, aby zostal existujúci, ale nikto si z neho nikdy nemôže vybrať
Prepis videa
Naučte sa Excel pre Podcast, epizóda 2104: Synchronizujte krájače z rôznych množín údajov.
Ahoj, vitaj naspäť na netcast, som Bill Jelen a dnešná otázka sa netýka toho, ako vziať tieto dve kontingenčné tabuľky, ktoré pochádzajú z jednej množiny údajov, a umožniť Sliceru ovládať všetky tieto kontingenčné tabuľky. O to tu nejde. Je to jednoduchá vec - Slicer, Nástroje, Možnosti, buď Správa pripojení alebo Pripojenie slicerov v starej verzii, a skontrolujte, či chcete, aby tento Slicer ovládal všetky tieto kontingenčné tabuľky. Ľahké, že? Táto otázka sa týka tohto pracovného hárka, kde máme dve rozdielne množiny údajov a z tohto vytvoríme kontingenčnú tabuľku a z tohto-- teraz mi dovoľte zrýchliť video, kým vytváram tieto kontingenčné tabuľky. Dobre, teraz uvidíte, že mám dve kontingenčné tabuľky, táto kontingenčná tabuľka je vytvorená z jednej množiny údajov a existuje posuvník, ktorý ovláda túto kontingenčnú tabuľku;a potom mám druhú kontingenčnú tabuľku, ktorá je vytvorená z inej množiny údajov, a výrez, ktorý túto kontingenčnú tabuľku ovláda. Neexistuje však absolútne žiadny spôsob, ako dosiahnuť, aby tento krájač ovládal túto kontingenčnú tabuľku aj túto kontingenčnú tabuľku, ktorá je zostavená z inej množiny údajov. Dobre. Ale ukážem ti, ako to dnes urobiť pomocou makra.
Teraz je to zložité. Keď prišla otázka, povedal som: „No, nemyslím si, že to zvládneš.“ Ale pracoval som na tom a experimentoval a myslím si, že som to konečne dostal. Musím si myslieť, že som to nakoniec dostal dole. Dobre, tak poďme na to. Najskôr sa to uloží ako súbor xlsx. Je to vynikajúci typ súboru, až na to, že je to hrozný typ súborov, pretože je to jediný typ súborov, ktorý neumožňuje makrá. Musíte to zmeniť z xlsx na xlsm, inak bude všetka vaša práca po zvyšok videa vyhodená z okna. Uložiť ako, zmeniť typ súboru na xlsm alebo sakra xlsb, jeden z nich bude fungovať. To je ten, ktorý je pokazený-- xlsx-- a je to predvolené, šialené, však? XLSM, kliknite na tlačidlo Uložiť. Ak ste ešte nikdy nerobili makra, Alt + T pre Toma, M pre Makro,S pre zabezpečenie a budete môcť uložiť všetky makrá bez upozornenia. Je potrebné zmeniť to na druhé, čo umožní fungovanie vašich makier.
Dobre, teraz máme dva krájače. Stavím sa, že ste to nikdy nevedeli, ale krájače majú mená. Pôjdeme do nástrojov Slicer, Options, Slicer Settings a uvidíme, že tento sa volá Slicer_Name. Ako to. Prejdite na druhú, choďte na Nástroje na krájanie, Možnosti, Nastavenia krájača, tento sa volá Slicer_Name1 - nie Menný priestor 1, Meno1. Takéto dve mená.
Tu budeme robiť. Prepneme na VBA-- Alt + F11. Ak ste vo VBA nikdy neurobili VBA, budete mať túto veľkú sivú obrazovku. Pôjdeme sem a povieme View, Project Explorer, v Project Explorer nájdeme váš súbor - ten môj sa volá Podcast 2104. Otvorte Microsoft Excel Objects a list, kde chcem, aby to fungovalo, sa volá Dashboard. Kliknem tam pravým tlačidlom myši a poviem Zobraziť kód. Tento kód, ktorý píšeme, nemôže ísť do modulu ako v bežnom makre - musí to byť v tomto pracovnom hárku. Otvorte ľavú hornú rozbaľovaciu ponuku, pracovný hárok, a potom v pravom hornom rozbaľovacom zozname povieme Aktualizácia kontingenčnej tabuľky. Dobre, takže to je miesto, kam teraz náš kód smeruje. Tento kód som už predpečel. Pozrime sa na kód tu v poznámkovom bloku. Takže,budete mať dve kešky Slicer - SC1 a SC2 - jednu položku Slicer a potom práve tu ich budete musieť prispôsobiť. Takže moje dva krájače sa volali Meno a Meno1. Dobre, budete tam musieť vložiť svoje názvy krájačov. Application.Screenupdating = False, Application.EnableEvents = False, a potom Slicer Cache 2 - vyčistíme filter a potom pre každú položku SI1 a sc1.SlicerItems, ak je vybratá, potom urobíme rovnaká položka v pamäti Slicer Cache, ktorá sa má vybrať. Toto je malá slučka, ktorá prebehne bez ohľadu na to, koľko položiek sa v tomto krájači stalo. V mojom prípade ich mám 11 alebo 12; vo vašom prípade ich môžete mať viac.Takže moje dva krájače sa volali Meno a Meno1. Dobre, budete tam musieť vložiť svoje názvy krájačov. Application.Screenupdating = False, Application.EnableEvents = False, a potom Slicer Cache 2 - vyčistíme filter a potom pre každú položku SI1 a sc1.SlicerItems, ak je vybratá, potom urobíme rovnaká položka v pamäti Slicer Cache, ktorá sa má vybrať. Toto je malá slučka, ktorá prebehne bez ohľadu na to, koľko položiek sa v tomto krájači stalo. V mojom prípade ich mám 11 alebo 12; vo vašom prípade ich môžete mať viac.Takže moje dva krájače sa volali Meno a Meno1. Dobre, budete tam musieť vložiť svoje názvy krájačov. Application.Screenupdating = False, Application.EnableEvents = False, a potom Slicer Cache 2 - vyčistíme filter a potom pre každú položku SI1 a sc1.SlicerItems, ak je vybratá, potom urobíme rovnaká položka v pamäti Slicer Cache, ktorá sa má vybrať. Toto je malá slučka, ktorá prebehne bez ohľadu na to, koľko položiek sa v tomto krájači stalo. V mojom prípade ich mám 11 alebo 12; vo vašom prípade ich môžete mať viac.chystáme vytvoriť rovnakú položku v medzipamäti Slicer, ktorú chcete vybrať. Toto je malá slučka, ktorá prebehne bez ohľadu na to, koľko položiek sa v tomto krájači stalo. V mojom prípade ich mám 11 alebo 12; vo vašom prípade ich môžete mať viac.chystáme vytvoriť rovnakú položku v medzipamäti Slicer, ktorú chcete vybrať. Toto je malá slučka, ktorá prebehne bez ohľadu na to, koľko položiek sa v tomto krájači stalo. V mojom prípade ich mám 11 alebo 12; vo vašom prípade ich môžete mať viac.
Keď to skončíme, znova zapnite udalosti povolenia a znova zapnite aktualizáciu obrazovky. Dobre. Vezmeme tento kód, skopírujeme ho a vložíme ho sem tak uprostred nášho makra. Dobre, teraz sa len uisti, že stlačím Ctrl + G a moja požiadavka je Application.EnableEvents, zapnutá alebo vypnutá - teda,? Application.EnableEvents - a je to pravda. Ak sa váš jav javí ako nepravdivý, chcete sa sem vrátiť a povedať, že je to = pravda-- takže tieto udalosti zapínate. Dobre. Teraz je tu to, čo sa stane. Takže náš tréner by tu mal pracovať, je to na správnom pracovnom liste. Sme uložení v súbore xlxm a zapol som Makrá a čo uvidíme, je to, že keď si vyberiem zľava Slicer, ten Slicer Cache 1 - I 'Vyberiem Andyho prostredníctvom Delly - aktualizovať sa bude aj druhý Slicer. Dobre. A aj keby som si vybral iba Gloria-- iba Gloria--, vyzerá to, že to funguje naozaj, naozaj dobre. Aj keby som CTRL + klikal, keď pustím Ctrl, všetky tri sa aktualizujú.
Ale tu je gotcha-- vždy existuje gotcha-- tento krájač, musí existovať, ale tento krájač nemôžete používať-- počkajte, myslím, že môžete, môžete použiť krájač, ale bude to zmätené sakra z vecí . Pretože to, čo sa stane, bude, že to zmením na Hanka a oni sa vrátia späť k čomukoľvek v Slicer Cache 1, pretože som zmenil kontingenčnú tabuľku na tomto hárku. Teraz, v reálnom živote, budete mať dva kontingenčné tabuľky na rovnakom hárku? Neviem, či ste, alebo nie ste, v poriadku, ale veci sa začnú trochu zblázniť.
Teraz sa na to pozrime. Prvá vec, ktorú chcem urobiť, je vložiť nový pracovný hárok - Alt + IW na vloženie pracovného hárka - a budem ho nazývať DarkCave. Môžete to nazvať ako chcete. Vezmem si tú palubnú dosku, ktorá nebude fungovať, skopírujem tú palubnú dosku a prídem sem do temnej jaskyne a prilepím ju tam a potom pravým tlačidlom myši skryjem ten hárok, aby ten Slicer nikdy nikto nevidel. A potom by sme to mali mať možnosť odstrániť. Pekné, dobre. A my len skontrolujeme, či stále fungujú - vyberte si Charlieho cez Eddieho a obaja sa stále aktualizujú. Čo sa deje? Slicer, ktorý nevidíme, ten, ktorý sme schovali, sa tiež aktualizuje, ale je nám jedno, že sa aktualizuje.
Čo teraz, keď chcete mať svoje veci na rôznych listoch? Vložím sem nový pracovný hárok - Alt + IW-- a vezmem jednu z týchto kontingenčných tabuliek-- možno druhú kontingenčnú tabuľku-- a presuniem ju na tento ďalší hárok-- takže Ctrl + C skopírujete kontingenčnej tabuľky, Ctrl + V sem vložte kontingenčnú tabuľku. A ak tu potrebujem mať krájač - nevkladajte výrez z tejto kontingenčnej tabuľky - musíme sa vrátiť na našu palubnú dosku, vziať ten krájač, ktorý ovláda Slicer, Ctrl + C a vytvoriť jeho kópiu, a vložte ich sem-- Ctrl + V. Dobre? Teraz v tomto hárku nemáme žiadny kód - v Sheet4 nie je žiadny kód - a myslel som si, že budem musieť pridať nejaký kód do Sheet4, ale tu je tá krásna vec: Keď zmením tento krájač, čo sa deje, na palubnej doske kontingenčnej tabuľky “s sa aktualizuje, hoci sa kontingenčná tabuľka na tomto hárku, ktorý nie je aktívny, aktualizuje, spustí kód a aktualizuje sa tiež. Docela sakra úž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.
Chcem vám poďakovať za návštevu, uvidíme sa nabudúce pri ďalšom netcaste z.
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2104.xlsm
