Срезы отлично подходят для сводных таблиц, потому что вы можете управлять несколькими сводными таблицами из одного набора срезов. Но - это своего рода ложь. Вы можете управлять несколькими сводными таблицами, полученными из одного набора данных. Когда у вас есть сводные таблицы, полученные из двух разных наборов данных, это довольно сложно. Я покажу вам VBA, который позволит вам это сделать.
Смотреть видео
- Как сделать так, чтобы слайсер управлял двумя сводными таблицами?
- Если обе сводные таблицы взяты из одного и того же набора данных: выберите срез, подключения к отчету, выберите другие сводные таблицы.
- Но если сводные таблицы взяты из разных наборов данных:
- Используйте "Сохранить как", чтобы изменить расширение книги на XLSM вместо XLSX.
- Используйте alt = "" + TMS и измените безопасность макросов на второй параметр.
- Alt + F11, чтобы перейти к VBA
- Ctrl + R, чтобы открыть проводник проекта
- Найдите рабочий лист, содержащий вашу первую сводную таблицу и срез
- Вставьте код для Worksheet_Update
- Скройте второй слайсер, чтобы он продолжал существовать, но никто не мог выбрать из этого слайсера
Стенограмма видео
Изучите Excel для подкастов, эпизод 2104: синхронизация срезов из разных наборов данных.
Привет, добро пожаловать обратно в сетевую трансляцию, я Билл Джелен, и вопрос сегодня не в том, как взять эти две сводные таблицы, полученные из одного набора данных, и сделать так, чтобы слайсер контролировал все эти сводные таблицы. Дело не в этом. Это легко сделать - срез, Инструменты, Параметры, либо Подключения к отчету, либо Подключения срезов в старой версии, и убедитесь, что вы хотите, чтобы этот срез управлял всеми этими сводными таблицами. Легко, правда? Этот вопрос касается этого рабочего листа, где у нас есть два разных набора данных, и мы собираемся создать сводную таблицу из этого, и из этого - теперь позвольте мне ускорить видео, пока я создаю эти сводные таблицы. Хорошо, теперь вы увидите, что у меня есть две сводные таблицы, эта сводная таблица создана из одного набора данных, и есть срез, который управляет этой сводной таблицей;а затем у меня есть вторая сводная таблица, созданная из другого набора данных, и срез, который управляет этой сводной таблицей. Но нет абсолютно никакого способа заставить этот слайсер управлять как этой сводной таблицей, так и этой сводной таблицей, построенной из другого набора данных. Хорошо. Но сегодня я покажу вам, как это сделать с помощью макроса.
Теперь это сложно сделать. Когда возник вопрос, я сказал: «Вот это, я не думаю, что вы сможете это сделать». Но я работал над этим и экспериментировал, и я думаю, что наконец понял. Я должен думать, что наконец понял это. Хорошо, давай пройдемся через это. Во-первых, он сохраняется как файл xlsx. Это хороший тип файла, за исключением того, что это ужасный тип файла, потому что это единственный тип файла, который не поддерживает макросы. Вы должны изменить это с xlsx на xlsm, иначе вся ваша работа с остальной частью видео будет выброшена в окно. Сохраните как, измените тип файла на xlsm или, черт возьми, xlsb, любой из них будет работать. Это тот, который сломан - xlsx - и он по умолчанию, безумие, не так ли? Xlsm, нажмите Сохранить. Если вы никогда раньше не использовали макросы, Alt + T для Тома, M для макроса,S для безопасности, и вы сможете сохранить все макросы без уведомления. Необходимо изменить его на второй, который позволит вашим макросам работать.
Хорошо, теперь у нас есть два слайсера. Спорим, вы никогда этого не знали, но у слайсеров есть имена. Мы собираемся перейти к инструментам слайсера, параметрам, настройкам слайсера и увидеть, что этот называется Slicer_Name. Как это. Перейдите ко второму, перейдите в Инструменты слайсера, Параметры, Настройки слайсера, этот называется Slicer_Name1, а не Name space 1, Name1. Два таких имени.
Вот что мы собираемся делать. Собираемся перейти на VBA - Alt + F11. В VBA, если вы никогда не работали с VBA, у вас будет большой серый экран. Мы собираемся прийти сюда и сказать View, Project Explorer, в Project Explorer найти ваш файл - мой называется Podcast 2104. Откройте Microsoft Excel Objects, и лист, на котором я хочу, чтобы это работало, называется Dashboard. Я собираюсь щелкнуть там правой кнопкой мыши и сказать «Просмотреть код». Этот код, который мы пишем, не может входить в модуль, как в обычный макрос - это должно быть на этом листе. Откройте верхний левый раскрывающийся список, Рабочий лист, затем в верхнем правом раскрывающемся списке мы скажем «Обновление сводной таблицы». Хорошо, вот куда теперь пойдет наш код. Я уже запек этот код заранее. Давайте посмотрим на код здесь, в блокноте. Итак, мыУ вас будет два кэша слайсера - SC1 и SC2 - один элемент слайсера, а затем, прямо здесь, вам придется его настроить. Итак, два моих слайсера назывались Name и Name1. Хорошо, вам нужно будет указать имена ваших слайсеров. Application.Screenupdating = False, Application.EnableEvents = False, а затем Slicer Cache 2 - мы собираемся очистить фильтр, а затем для каждого элемента SI1 и sc1.SlicerItems, если он выбран, мы собираемся сделать должен быть выбран тот же элемент в кэше слайсера. Это небольшой цикл, который будет выполняться независимо от того, сколько элементов окажется в этом слайсере. В моем случае у меня 11 или 12; в вашем случае у вас может быть больше.Итак, два моих слайсера назывались Name и Name1. Хорошо, вам нужно будет указать там имена ваших слайсеров. Application.Screenupdating = False, Application.EnableEvents = False, а затем Slicer Cache 2 - мы собираемся очистить фильтр, а затем для каждого элемента SI1 и sc1.SlicerItems, если он выбран, мы собираемся сделать должен быть выбран тот же элемент в кэше слайсера. Это небольшой цикл, который будет выполняться независимо от того, сколько элементов окажется в этом слайсере. В моем случае у меня 11 или 12; в вашем случае у вас может быть больше.Итак, два моих слайсера назывались Name и Name1. Хорошо, вам нужно будет указать имена ваших слайсеров. Application.Screenupdating = False, Application.EnableEvents = False, а затем Slicer Cache 2 - мы собираемся очистить фильтр, а затем для каждого элемента SI1 и sc1.SlicerItems, если он выбран, мы собираемся сделать должен быть выбран тот же элемент в кэше слайсера. Это небольшой цикл, который будет выполняться независимо от того, сколько элементов окажется в этом слайсере. В моем случае у меня 11 или 12; в вашем случае у вас может быть больше.Вы собираетесь сделать так, чтобы тот же элемент был выбран в Slicer Cache. Это небольшой цикл, который будет выполняться независимо от того, сколько элементов окажется в этом слайсере. В моем случае у меня 11 или 12; в вашем случае у вас может быть больше.Вы собираетесь сделать так, чтобы тот же элемент в Slicer Cache был выбран. Это небольшой цикл, который будет выполняться независимо от того, сколько элементов окажется в этом слайсере. В моем случае у меня 11 или 12; в вашем случае у вас может быть больше.
Когда мы закончим с этим, снова включите Enable Events, снова включите Screen Update. Хорошо. Итак, мы возьмем этот код, скопируем его и вставим вот так, в середину нашего макроса. Хорошо, теперь давайте просто убедимся, что я собираюсь нажать Ctrl + G, и моя просьба - Application.EnableEvents, включен или выключен - так,? Application.EnableEvents - и это правда. Если ваше обнаруживается как ложное, тогда вы хотите вернуться сюда и сказать, что оно = Истина - так что вы включаете эти события. Хорошо. Теперь вот что произойдет. Так что наш тренер должен работать здесь, это на правильном листе. Мы сохранены в файле xlxm, и я включил макросы, и мы увидим, что когда я выбираю из левого слайсера, этот Slicer Cache 1 - I 'Выберем Энди через Деллу - другой слайсер тоже обновится. Хорошо, и даже если бы я выбрал только Глорию - только Глорию - похоже, это работает очень, очень хорошо. Даже если я нажму CTRL + щелчок, когда я отпущу Ctrl, все три обновятся.
But here's the gotcha-- there's always a gotcha-- this Slicer, it has to exist, but you cannot use this Slicer-- wait, I mean you can, you can use a Slicer but it's going to confuse the heck out of things. Because what's going to happen is I'm going to change this to Hank and they're going to go back to whatever is in Slicer Cache 1, because I changed the pivot table on this sheet. Now, in real life, are you going to have two pivot tables on the same sheet? I don't know if you are or if you aren't, alright, but things are going to become a little crazy.
Now, let's just take a look at this. First thing I want to do, is I'm going to insert a new worksheet-- Alt+IW for inserting the worksheet-- and I'm going to call this a DarkCave. You can call it whatever you want. I'm going to take that dashboard that's not going to work, I'm going to copy that dashboard and come here to the dark cave and paste it there and then right click and hide that sheet so no one ever sees that Slicer. And then, from here, we should be able to delete it. Nice, alright. And we're going to just check to make sure they're still working-- choose Charlie through Eddie and they're both still updating. Now, what's happening? The Slicer that we can't see, the one that we've hidden away, it's updating as well, but we don't care that it's updating.
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
