Зрізи є чудовими для зведених таблиць, оскільки ви можете керувати кількома зведеними таблицями з одного набору зрізів. Але - це якась брехня. Ви можете керувати кількома зведеними таблицями, що надходять з одного набору даних. Коли у вас є зведені таблиці, які походять із двох різних наборів даних, це досить складно. Я покажу вам кілька VBA, які дозволять вам це зробити.
Переглянути відео
- Як ви можете мати нарізний привід, який керує двома зведеними таблицями?
- Якщо обидві зведені таблиці походять з одного набору даних: Виберіть Slicer, Report Connections, Select Other Pivot Tables
- Але якщо зведені таблиці походять з різних наборів даних:
- Використовуйте Зберегти як, щоб змінити розширення книги на XLSM замість XLSX
- Використовуйте alt = "" + TMS і змініть захист макросів на друге налаштування.
- Alt + F11, щоб дістатися до VBA
- Ctrl + R, щоб відобразити дослідник проекту
- Знайдіть аркуш, який містить вашу першу зведену таблицю та нарізку
- Вставте код для Worksheet_Update
- Сховайте другу нарізку, щоб вона продовжувала існувати, але ніхто ніколи не може вибрати з неї нарізку
Стенограма відео
Дізнайтеся Excel для подкасту, епізод 2104: Синхронізуйте слайсери з різних наборів даних.
Привіт, ласкаво просимо до мережі, я Білл Джелен, і сьогоднішнє питання не в тому, як взяти ці дві зведені таблиці, що походять з одного набору даних, і змусити Slicer контролювати всі ці зведені таблиці. Не про це йдеться. Це легко зробити - Slicer, Tools, Options, або Report Connections, або Slicer Connections у старій версії, і переконайтеся, що ви хочете, щоб цей Slicer контролював усі ці зведені таблиці. Легко, правда? Це питання стосується цього аркуша, де ми маємо два різних набори даних, і ми збираємося створити зведену таблицю з цього, і з цього - тепер дозвольте мені пришвидшити відео, поки я створюю ці зведені таблиці. Добре, зараз, що ви побачите, це те, що у мене є дві зведені таблиці, ця зведена таблиця створена з одного набору даних, і є зріз, який контролює цю зведену таблицю;а потім у мене є друга зведена таблиця, яка створена з іншого набору даних, і зріз, який керує цією зведеною таблицею. Але немає абсолютно жодного способу змусити цей зріз керувати як цією зведеною таблицею, так і зведеною таблицею, яка побудована з іншого набору даних. Гаразд Але я покажу вам, як сьогодні це зробити за допомогою макросу.
Зараз це складно зробити. Коли запитання надійшло, я сказав: "Зараз, це, я не думаю, що ти можеш це зробити". Але я працював над цим і експериментував, і думаю, що нарешті зрозумів. Я повинен думати, що я нарешті зрозумів це. Добре, так давайте пройдемо це. По-перше, це зберігається як файл xlsx. Це чудовий тип файлу, за винятком того, що це жахливий тип файлу, оскільки це єдиний тип файлу, який не дозволяє макроси. Вам доведеться змінити це з xlsx на xlsm, або вся ваша робота на решту відео буде викинута з вікна. Збережіть як, змініть тип файлу на xlsm або, чорт візьміть, xlsb, будь-який із них буде працювати. Це той, який зламаний - xlsx - і це за замовчуванням, божевільний, чи не так? Xlsm, натисніть Зберегти. Якщо ви ніколи раніше не робили макросів, Alt + T для Тома, M для макросу,S для безпеки, і ви зможете зберегти всі макроси без попередження. Потрібно змінити це на друге, що дозволить вашим макросам працювати.
Добре, тепер у нас є дві нарізки. Б'юсь об заклад, ви ніколи цього не знали, але нарізки мають імена. Ми перейдемо до Інструментів для нарізки, Параметри, Налаштування нарізки, і побачимо, що цей називається Slicer_Name. Щось схоже на те. Перейдіть до другого, перейдіть до Slicer Tools, Options, Slicer Settings, цей називається Slicer_Name1 - не простір імен 1, Name1. Два таких імена.
Ось що ми будемо робити. Ми перейдемо до VBA - Alt + F11. У VBA, якщо ви ніколи не робили VBA, у вас буде цей великий сірий екран. Ми приїдемо сюди і скажемо Перегляд, Провідник проектів, у Провіднику проектів знайдіть свій файл - мій називається Podcast 2104. Відкрийте об’єкти Microsoft Excel, а аркуш, де я хочу, щоб це працювало, називається Інформаційна панель. Я збираюся клацнути правою кнопкою миші там і сказати Переглянути код. Цей код, який ми пишемо, не може входити в модуль, як у звичайний макрос - він повинен бути на цьому аркуші. Відкрийте верхній лівий випадаючий список, Робочий аркуш, а потім у верхньому правому спадному меню ми скажемо Оновлення зведеної таблиці. Гаразд, отже, саме сюди буде йти наш код. Я вже заздалегідь запекти цей код. Давайте подивимось на код тут, у блокноті. Отже, миу вас буде два кеші Slicer - SC1 і SC2 - один елемент Slicer, а потім, ось тут, вам доведеться його налаштувати. Тож два мої Слайсери називали Ім'я та Ім'я1. Добре, вам доведеться помістити туди свої імена слайсерів. Application.Screenupdating = False, Application.EnableEvents = False, а потім Slicer Cache 2 - ми очистимо фільтр, а потім для кожного елемента SI1 і sc1.SlicerItems, якщо він вибраний, тоді ми зробимо вибраний той самий елемент у кеші Slicer. Це маленький цикл, який буде проходити, проте багато елементів, що трапляються в цьому різаку. У моєму випадку у мене 11 або 12; у вашому випадку ви можете мати більше.Тож два мої Слайсери називали Ім'я та Ім'я1. Добре, вам доведеться помістити туди свої імена слайсерів. Application.Screenupdating = False, Application.EnableEvents = False, а потім кеш Slicer 2 - ми очистимо фільтр, а потім для кожного елемента SI1 і sc1.SlicerItems, якщо він вибраний, тоді ми зробимо вибраний той самий елемент у кеші Slicer. Це невеликий цикл, який буде проходити, проте багато елементів, що трапляються в цьому різаку. У моєму випадку у мене 11 або 12; у вашому випадку ви можете мати більше.Тож два мої Слайсери називали Ім'я та Ім'я1. Добре, вам доведеться помістити туди свої імена слайсерів. Application.Screenupdating = False, Application.EnableEvents = False, а потім Slicer Cache 2 - ми очистимо фільтр, а потім для кожного елемента SI1 і sc1.SlicerItems, якщо він вибраний, тоді ми зробимо вибраний той самий елемент у кеші Slicer. Це невеликий цикл, який буде проходити, проте багато елементів, що трапляються в цьому різаку. У моєму випадку у мене 11 або 12; у вашому випадку ви можете мати більше.збираєтеся зробити той самий елемент, який буде вибрано в кеші Slicer. Це невеликий цикл, який буде проходити, проте багато елементів, що трапляються в цьому різаку. У моєму випадку у мене 11 або 12; у вашому випадку ви можете мати більше.збираєтеся зробити той самий елемент, який буде вибрано в кеші Slicer. Це невеликий цикл, який буде проходити, проте багато елементів, що трапляються в цьому різаку. У моєму випадку у мене 11 або 12; у вашому випадку ви можете мати більше.
Коли ми закінчимо з цим, знову увімкніть активацію подій, знову ввімкніть оновлення екрана. Гаразд Отже, ми візьмемо цей код, скопіюємо цей код і вставимо його сюди посередині нашого макросу таким чином. Гаразд, давайте просто переконайтеся, що я збираюся натиснути Ctrl + G і моє запитання - Application.EnableEvents, увімкнути чи вимкнути - так,? Application.EnableEvents - і це правда. Якщо ваш виглядає як помилковий, ви хочете повернутися сюди і сказати, що це = True - отже, ви включаєте ці події. Гаразд Зараз, ось що буде відбуватися. Тож наш тренер повинен працювати тут, це на правильному аркуші. Ми зберігаємось у файлі xlxm, і я ввімкнув макроси, і те, що ми побачимо, це те, що коли я вибираю з лівого слайсера, цей кеш-слайсер 1-- я 'Я виберу Енді через Della - інший Slicer також буде оновлений. Гаразд І навіть якби я вибрав саме Глорію - просто Глорію - схоже, це працює дуже, дуже добре. Навіть якби я натиснув CTRL + клацання, коли я відпущу Ctrl, вони всі три оновляться.
Але тут є - завжди є - це завжди - цей слайсер, він повинен існувати, але ви не можете використовувати цей слайсер - почекайте, я маю на увазі, що можете, ви можете використовувати слайсер, але це заплутає речі . Тому що те, що відбудеться, це те, що я зміню це на Хенка, а вони повернуться до того, що є в Slicer Cache 1, тому що я змінив зведену таблицю на цьому аркуші. Тепер у реальному житті ви збираєтеся мати дві зведені таблиці на одному аркуші? Не знаю, чи є ти, чи ні, добре, але все стане трохи божевільним.
Тепер давайте просто подивимось на це. Перше, що я хочу зробити, це те, що я збираюся вставити новий аркуш - Alt + IW для вставки аркуша - і я буду називати це DarkCave. Ви можете назвати це як завгодно. Я збираюся взяти ту панель інструментів, яка не працюватиме, я скопіюю цю панель і прийду сюди в темну печеру і вставлю її туди, а потім клацніть правою кнопкою миші та сховайте цей аркуш, щоб ніхто ніколи не бачив цей слайсер. А потім, звідси, ми зможемо його видалити. Приємно, добре. І ми просто перевіримо, чи вони все ще працюють - оберіть Чарлі через Едді, і вони обоє все ще оновлюються. Що тепер? Слайсер, який ми не бачимо, той, який ми сховали, він також оновлюється, але нам байдуже, що він оновлюється.
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.
Хочемо подякувати вам за заїзд, ми побачимось наступного разу для чергової трансляції від.
Завантажити файл
Завантажте зразок файлу тут: Podcast2104.xlsm
