Fragmentatory są świetne w przypadku tabel przestawnych, ponieważ można kontrolować wiele tabel przestawnych z jednego zestawu fragmentatorów. Ale - to trochę kłamstwo. Możesz kontrolować wiele tabel przestawnych pochodzących z tego samego zestawu danych. Jeśli masz tabele przestawne, które pochodzą z dwóch różnych zestawów danych, jest to dość trudne. Pokażę ci trochę VBA, który pozwoli ci to zrobić.
Obejrzyj wideo
- Jak możesz mieć krajalnicę sterującą dwiema tabelami przestawnymi?
- Jeśli obie tabele przestawne pochodzą z tego samego zestawu danych: wybierz fragmentator, połączenia raportów, wybierz inne tabele przestawne
- Ale jeśli tabele przestawne pochodzą z różnych zestawów danych:
- Użyj opcji Zapisz jako, aby zmienić rozszerzenie skoroszytu na XLSM zamiast XLSX
- Użyj alt = "" + TMS i zmień zabezpieczenia makr na drugie ustawienie.
- Alt + F11, aby przejść do VBA
- Ctrl + R, aby wyświetlić eksplorator projektu
- Znajdź arkusz zawierający pierwszą tabelę przestawną i fragmentator
- Wstaw kod dla Worksheet_Update
- Ukryj drugą krajalnicę, aby nadal istniała, ale nikt nie może wybrać tego fragmentatora
Transkrypcja wideo
Naucz się programu Excel dla podcastów, odcinek 2104: Synchronizuj fragmentatory z różnych zestawów danych.
Hej, witaj ponownie w netcastie, jestem Bill Jelen, a dzisiejsze pytanie nie dotyczy tego, jak wziąć te dwie tabele przestawne, które pochodzą z jednego zestawu danych, i sprawić, by Slicer kontrolował wszystkie te tabele przestawne. Nie o to chodzi. To łatwa rzecz - fragmentator, narzędzia, opcje, połączenia raportów lub połączenia fragmentatora w starej wersji i sprawdź, czy chcesz, aby ten fragmentator kontrolował wszystkie te tabele przestawne. Łatwe, prawda? To pytanie dotyczy tego arkusza roboczego, w którym mamy dwa różne zestawy danych i na tej podstawie utworzymy tabelę przestawną - a teraz pozwolę sobie przyspieszyć wideo podczas tworzenia tych tabel przestawnych. W porządku, teraz zobaczysz, że mam dwie tabele przestawne, ta tabela przestawna jest utworzona z jednego zestawu danych i jest fragmentator, który kontroluje tę tabelę przestawną;a następnie mam drugą tabelę przestawną utworzoną na podstawie innego zestawu danych oraz fragmentator kontrolujący tę tabelę przestawną. Ale nie ma absolutnie żadnego sposobu, aby ten fragmentator kontrolował zarówno tę tabelę przestawną, jak i tę tabelę przestawną zbudowaną na podstawie innego zestawu danych. W porządku. Ale pokażę ci, jak to zrobić dzisiaj za pomocą makra.
Teraz jest to trudne. Kiedy pojawiło się pytanie, powiedziałem: „Otóż, to chyba nie dasz rady”. Ale pracowałem nad tym, eksperymentowałem i myślę, że w końcu to dostałem. Muszę pomyśleć, że w końcu to zrozumiałem. W porządku, więc przejdźmy przez to. Po pierwsze, jest to zapisywane jako plik xlsx. To dobry typ pliku, z wyjątkiem tego, że jest to okropny typ pliku, ponieważ jest to jedyny typ pliku, który nie zezwala na makra. Musisz zmienić to z xlsx na xlsm, albo cała twoja praca do reszty wideo zostanie wyrzucona przez okno. Zapisz jako, zmień typ pliku na xlsm lub, do diabła, xlsb, jeden z nich zadziała. To ten, który jest zepsuty - xlsx - i to jest domyślne, szalone, prawda? XLSM, kliknij Zapisz. Jeśli nigdy wcześniej nie robiłeś makr, Alt + T dla Toma, M dla makra,S for Security i będziesz mógł zapisać wszystkie makra bez powiadomienia. Musisz to zmienić na drugą, która pozwoli działać twoim makrom.
W porządku, teraz mamy dwa krajalnice. Założę się, że nigdy tego nie wiedziałeś, ale krajalnice mają imiona. Przejdziemy do narzędzi, opcji, ustawień fragmentatora i zobaczymy, że nazywa się Slicer_Name. Tak. Przejdź do drugiego, przejdź do Slicer Tools, Options, Slicer Settings, ten nazywa się Slicer_Name1 - nie Name space 1, Name1. Dwa takie nazwiska.
Oto, co zamierzamy zrobić. Zamierzamy przejść na VBA - Alt + F11. W VBA, jeśli nigdy nie robiłeś VBA, będziesz mieć ten duży szary ekran. Przyjdziemy tutaj i powiemy View, Project Explorer, w Project Explorer znajdź swój plik - mój nazywa się Podcast 2104. Otwórz Microsoft Excel Objects, a arkusz, w którym chcę, żeby to działało, nazywa się Dashboard. Zamierzam kliknąć tam prawym przyciskiem myszy i powiedzieć Wyświetl kod. Ten kod, który piszemy, nie może znajdować się w module, tak jak w zwykłym makrze - musi to być w tym arkuszu. Otwórz menu rozwijane w lewym górnym rogu, Arkusz, a następnie w menu rozwijanym w prawym górnym rogu powiemy Aktualizacja tabeli przestawnej. W porządku, więc w tym miejscu będzie teraz prowadzony nasz kod. Ten kod został już wstępnie przygotowany. Spójrzmy na kod w notatniku. Więc mybędziemy mieć dwie pamięci podręczne Slicer - SC1 i SC2 - jeden przedmiot Slicer, a następnie, tutaj, tutaj będziesz musiał go dostosować. Więc moje dwa fragmentatory nazywały się Name i Name1. W porządku, będziesz musiał umieścić tam swoje nazwy fragmentatorów. Application.Screenupdating = False, Application.EnableEvents = False, a następnie Slicer Cache 2 - wyczyścimy filtr, a następnie dla każdego elementu SI1 i sc1.SlicerItems, jeśli jest zaznaczony, zrobimy ten sam element w pamięci podręcznej fragmentatora do wybrania. To jest mała pętla, która będzie przebiegać przez dowolną liczbę elementów znajdujących się w tym fragmencie. W moim przypadku mam 11 lub 12; w twoim przypadku możesz mieć więcej.Więc moje dwa fragmentatory nazywały się Name i Name1. W porządku, będziesz musiał umieścić tam swoje nazwy fragmentatorów. Application.Screenupdating = False, Application.EnableEvents = False, a następnie Slicer Cache 2 - wyczyścimy filtr, a następnie dla każdego elementu SI1 i sc1.SlicerItems, jeśli jest zaznaczony, zrobimy ten sam element w pamięci podręcznej fragmentatora do wybrania. To jest mała pętla, która będzie przebiegać przez dowolną liczbę elementów znajdujących się w tym fragmencie. W moim przypadku mam 11 lub 12; w twoim przypadku możesz mieć więcej.Więc moje dwa fragmentatory nazywały się Name i Name1. W porządku, będziesz musiał umieścić tam swoje nazwy fragmentatorów. Application.Screenupdating = False, Application.EnableEvents = False, a następnie Slicer Cache 2 - wyczyścimy filtr, a następnie dla każdego elementu SI1 i sc1.SlicerItems, jeśli jest zaznaczony, zrobimy ten sam element w pamięci podręcznej fragmentatora do wybrania. To jest mała pętla, która będzie przebiegać przez dowolną liczbę elementów znajdujących się w tym fragmencie. W moim przypadku mam 11 lub 12; w twoim przypadku możesz mieć więcej.ponownie sprawi, że ten sam element w pamięci podręcznej fragmentatora zostanie wybrany. To jest mała pętla, która będzie przebiegać przez dowolną liczbę elementów znajdujących się w tym fragmencie. W moim przypadku mam 11 lub 12; w twoim przypadku możesz mieć więcej.ponownie sprawi, że ten sam element w pamięci podręcznej fragmentatora zostanie wybrany. To jest mała pętla, która będzie przebiegać przez dowolną liczbę elementów znajdujących się w tym fragmencie. W moim przypadku mam 11 lub 12; w twoim przypadku możesz mieć więcej.
Kiedy skończymy, włącz ponownie włączanie wydarzeń, włącz ponownie aktualizację ekranu. W porządku. Więc weźmiemy ten kod, skopiujemy ten kod i wkleimy go tutaj w środku naszego makra w ten sposób. W porządku, teraz po prostu upewnijmy się, że mam zamiar nacisnąć Ctrl + G, a moje pytanie to Application.EnableEvents, włączone lub wyłączone - więc? Application.EnableEvents - i to prawda. Jeśli twoje okaże się fałszywe, to chcesz wrócić tutaj i powiedzieć, że to jest = Prawda - więc więc włączasz te zdarzenia. W porządku. A teraz oto, co się wydarzy. Więc nasz trener powinien tu pracować, jest na odpowiednim arkuszu. Jesteśmy zapisani w pliku xlxm i włączyłem makra i zobaczymy, że kiedy wybiorę z lewego fragmentatora, ta pamięć podręczna fragmentatora 1 …Wybieram Andy'ego przez Della - inny Krajalnica również się zaktualizuje. W porządku I nawet gdybym wybrał tylko Glorię - tylko Glorię - wygląda na to, że działa naprawdę, bardzo dobrze. Nawet gdybym zrobił CTRL + kliknięcie, kiedy puszczę Ctrl, wszystkie trzy zostaną zaktualizowane.
Ale tu jest haczyk - zawsze jest coś złego - ten krajalnica, musi istnieć, ale nie możesz użyć tego krajalnicy - czekaj, mam na myśli, że możesz, możesz użyć krajalnicy, ale to do cholery zmyli wszystko . Ponieważ zmienię to na Hanka, a oni wrócą do tego, co jest w pamięci podręcznej Slicer 1, ponieważ zmieniłem tabelę przestawną w tym arkuszu. Czy w prawdziwym życiu będziesz mieć dwie tabele przestawne na tym samym arkuszu? Nie wiem, czy tak jest, czy nie, w porządku, ale sprawy staną się trochę szalone.
Spójrzmy teraz na to. Pierwszą rzeczą, którą chcę zrobić, jest wstawienie nowego arkusza roboczego - Alt + IW do wstawienia arkusza roboczego - i zamierzam nazwać to DarkCave. Możesz to nazwać, jak chcesz. Mam zamiar wziąć ten pulpit nawigacyjny, który nie zadziała, skopiuję ten pulpit nawigacyjny i przyjdę do ciemnej jaskini i wkleję go tam, a następnie kliknij prawym przyciskiem myszy i ukryję ten arkusz, aby nikt nigdy nie widział tego krajalnicy. A potem powinniśmy być w stanie go usunąć. Dobrze, w porządku. Sprawdzimy tylko, czy nadal działają - wybierz Charliego przez Eddiego i obaj wciąż się aktualizują. A teraz co się dzieje? Fragmentator, którego nie widzimy, ten, który ukryliśmy, również się aktualizuje, ale nie obchodzi nas, że się aktualizuje.
Now, what if you want to have your things on different sheets? I'll insert a new worksheet here--Alt+IW-- and I'll take one of these pivot tables-- maybe the second pivot table-- and move it to that other sheet-- so, Ctrl+C to copy the pivot table, Ctrl+V to paste the pivot table here. And if I need to have a slicer here-- don't insert a slice from this pivot table-- we have to come back to our dashboard, take the slicer that's the controlling Slicer, Ctrl+C to make a copy of it, and paste it here-- Ctrl+V. Alright? Now, we have no code on this sheet-- there's no code on Sheet4-- and I was thinking I was going to have to add some code to Sheet4, but here's the beautiful thing: When I change this slicer, what's happening is, on the dashboard that pivot table's updating even though that pivot table on that sheet that's not active is updating, they will run the code and this will update as well. Pretty darn amazing that that works.
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.
Chcę podziękować za zatrzymanie się, do zobaczenia następnym razem z kolejnym netcastem od.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2104.xlsm
