Слайсерите са страхотни за обобщените таблици, тъй като можете да контролирате множество обобщени таблици от един набор от нарязващи машини. Но - това е нещо като лъжа. Можете да контролирате множество обобщени таблици, получени от един и същ набор от данни. Когато имате обобщени таблици, дошли от два различни набора от данни, това е доста сложно. Ще ви покажа някои VBA, които ще ви позволят да направите това.
Гледам видео
- Как можете да накарате слайсър да управлява две осеви таблици?
- Ако и двете обобщени таблици идват от един и същ набор от данни: изберете Slicer, Report Connections, Select Other Pivot Tables
- Но ако обобщените таблици идват от различни набори от данни:
- Използвайте Save As, за да промените разширението на работната книга на 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 Tools, Options, Slicer Settings и ще видим, че този се нарича Slicer_Name. По този начин. Отидете на втория, отидете на Slicer Tools, Options, Slicer Settings, този се нарича Slicer_Name1 - не Име пространство 1, Име1. Две имена като това.
Ето какво ще направим. Ще преминем към VBA - Alt + F11. Във VBA, ако никога не сте правили VBA, ще имате този голям сив екран. Ще дойдем тук и ще кажем View, Project Explorer, в Project Explorer намерете вашия файл - моят се нарича Podcast 2104. Отворете Microsoft Excel Objects и листът, където искам това да работи, се нарича Dashboard. Ще щракна с десния бутон там и ще кажа Преглед на кода. Този код, който пишем, не може да влиза в модул като в обикновен макрос - това трябва да е на този работен лист. Отворете горното ляво падащо меню, Работен лист, след което в горното дясно падащо меню ще кажем Актуализация на обобщена таблица. Добре, така че тук ще отиде нашият код сега. Вече предварително изпекох този код. Нека да разгледаме кода тук в бележника. И така, ниеще имате два кеша на Slicer - SC1 и SC2 - един елемент на Slicer и след това, ето тук, ще трябва да го персонализирате. Така че моите два Slicers бяха наречени Name and Name1. Добре, ще трябва да поставите имената на вашите резачки там. Application.Screenupdating = False, Application.EnableEvents = False и след това Slicer Cache 2 - ще изчистим филтъра и след това за всеки елемент SI1 и sc1.SlicerItems, ако е избран, тогава ще направим същият елемент в Slicer Cache, който ще бъде избран. Това е малък цикъл, който ще премине през колкото и много елементи да се намират в тази машина за нарязване. В моя случай имам 11 или 12; във вашия случай може да имате повече.Така че моите два Slicers бяха наречени Name and Name1. Добре, ще трябва да поставите имената на вашите резачки там. Application.Screenupdating = False, Application.EnableEvents = False и след това Slicer Cache 2 - ще изчистим филтъра и след това за всеки елемент SI1 и sc1.SlicerItems, ако е избран, тогава ще направим същият елемент в Slicer Cache, който ще бъде избран. Това е малък цикъл, който ще премине през колкото и много елементи да се намират в тази машина за нарязване. В моя случай имам 11 или 12; във вашия случай може да имате повече.Така че моите два Slicers бяха наречени Name and Name1. Добре, ще трябва да поставите имената на вашите резачки там. Application.Screenupdating = False, Application.EnableEvents = False и след това Slicer Cache 2 - ще изчистим филтъра и след това за всеки елемент SI1 и sc1.SlicerItems, ако е избран, тогава ще направим същият елемент в Slicer Cache, който ще бъде избран. Това е малък цикъл, който ще премине през колкото и много елементи да се намират в тази машина за нарязване. В моя случай имам 11 или 12; във вашия случай може да имате повече.ще направите същия елемент в Slicer Cache, който ще бъде избран. Това е малък цикъл, който ще премине през колкото и много елементи да се намират в тази машина за нарязване. В моя случай имам 11 или 12; във вашия случай може да имате повече.ще направите същия елемент в Slicer Cache, който ще бъде избран. Това е малък цикъл, който ще премине през колкото и много елементи да се намират в тази машина за нарязване. В моя случай имам 11 или 12; във вашия случай може да имате повече.
Когато приключим с това, включете отново активирането на събитията, включете отново Актуализацията на екрана. Добре. Така че, ние ще вземем този код, ще го копираме и ще го поставим тук в средата на нашия макрос така. Добре, нека просто се уверим, че ще натисна Ctrl + G и моето искане е Application.EnableEvents, включен или изключен - така,? Application.EnableEvents - и това е вярно. Ако вашият се окаже фалшив, тогава искате да се върнете тук и да кажете, че е = Вярно - така че, тогава включвате тези събития. Добре. Ето какво ще се случи. Така че нашият треньор трябва да работи тук, той е в десния работен лист. Запазени сме в xlxm файл и аз включих макроси и това, което ще видим, е, че когато избера от левия Slicer, този Slicer Cache 1 - I 'Ще избера Анди чрез Della - другият Slicer също ще се актуализира. Добре И дори да избера само Глория - само Глория - изглежда, че работи наистина много добре. Дори ако щях да натисна CTRL + щракване, когато пусна Ctrl, и трите ще се актуализират.
Но ето го - винаги има проблем - този Slicer трябва да съществува, но не можете да използвате този Slicer - изчакайте, искам да кажа, можете да използвате Slicer, но това ще обърка нещата от нещата . Защото това, което ще се случи, е, че ще променя това на Ханк и те ще се върнат към всичко, което е в Slicer Cache 1, защото промених обобщената таблица на този лист. Сега в реалния живот ще имате ли две осеви таблици на един и същ лист? Не знам дали сте или не, добре, но нещата ще станат малко луди.
Сега нека просто да разгледаме това. Първото нещо, което искам да направя, е да вмъкна нов работен лист - Alt + IW за вмъкване на работния лист - и ще нарека това DarkCave. Можете да го наречете както искате. Ще взема това табло, което няма да работи, ще го копирам и ще дойда тук в тъмната пещера и ще го поставя там и след това щракнете с десния бутон и ще скриете този лист, така че никой никога да не види този Slicer. И тогава, от тук, бихме могли да го изтрием. Хубаво, добре. И просто ще проверим дали все още работят - изберете Чарли чрез Еди и двамата все още се актуализират. Сега какво се случва? Slicer, който не можем да видим, този, който сме скрили, също се актуализира, но не ни интересува, че се актуализира.
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
