Slicer rất tuyệt vời cho bảng tổng hợp vì bạn có thể điều khiển nhiều bảng tổng hợp từ một bộ dao cắt. Nhưng - đó là một loại nói dối. Bạn có thể kiểm soát nhiều bảng tổng hợp đến từ cùng một tập dữ liệu. Khi bạn có các bảng tổng hợp đến từ hai tập dữ liệu khác nhau, nó khá phức tạp. Tôi sẽ chỉ cho bạn một số VBA sẽ cho phép bạn thực hiện điều này.
Xem video
- Làm thế nào bạn có thể có một máy cắt ổ đĩa hai bảng xoay?
- Nếu cả hai bảng tổng hợp đến từ cùng một tập dữ liệu: Chọn Bộ cắt, Kết nối báo cáo, Chọn Bảng tổng hợp khác
- Nhưng nếu bảng tổng hợp đến từ các tập dữ liệu khác nhau:
- Sử dụng Save As để thay đổi phần mở rộng sổ làm việc thành XLSM thay vì XLSX
- Sử dụng alt = "" + TMS và thay đổi bảo mật macro thành cài đặt thứ hai.
- Alt + F11 để đến VBA
- Ctrl + R để hiển thị trình khám phá dự án
- Tìm trang tính có chứa bảng tổng hợp và bộ cắt đầu tiên của bạn
- Chèn mã cho Worksheet_Update
- Ẩn máy cắt thứ hai đi để nó tiếp tục tồn tại nhưng không ai có thể chọn từ máy cắt đó
Bản ghi video
Học Excel cho Podcast, Tập 2104: Đồng bộ hóa các Slice từ các Tập dữ liệu khác nhau.
Chào mừng bạn quay trở lại netcast, tôi là Bill Jelen, và câu hỏi của ngày hôm nay không phải là làm thế nào để lấy hai bảng xoay này đến từ một tập dữ liệu và làm cho Slicer kiểm soát tất cả các bảng xoay đó. Đó không phải là những gì đây là về. Đó là một điều dễ dàng thực hiện-- Trình cắt, Công cụ, Tùy chọn, Kết nối Báo cáo hoặc Kết nối Trình cắt trong phiên bản cũ và kiểm tra xem bạn có muốn Trình cắt này kiểm soát tất cả các bảng tổng hợp đó hay không. Dễ dàng, phải không? Câu hỏi này là về trang tính này, nơi chúng ta có hai tập dữ liệu khác nhau và chúng ta sẽ tạo một bảng tổng hợp từ cái này và từ cái này-- bây giờ hãy để tôi tăng tốc độ video trong khi tạo các bảng tổng hợp này. Được rồi, bây giờ, những gì bạn sẽ thấy là, tôi có hai bảng tổng hợp, bảng tổng hợp này được tạo từ một tập dữ liệu và có một bộ cắt điều khiển bảng tổng hợp đó;và sau đó tôi có một bảng tổng hợp thứ hai được tạo từ một tập dữ liệu khác và một bộ cắt điều khiển bảng tổng hợp đó. Nhưng hoàn toàn không có cách nào để bộ cắt này kiểm soát cả bảng tổng hợp này và bảng tổng hợp này được xây dựng từ một tập dữ liệu khác. Ổn thỏa. Nhưng hôm nay tôi sẽ chỉ cho bạn cách thực hiện điều đó với macro.
Bây giờ, điều này rất khó thực hiện. Khi câu hỏi được đưa ra, tôi nói, "Bây giờ, điều này, tôi không nghĩ bạn có thể làm được." Nhưng tôi đã làm việc trên nó và thử nghiệm và tôi nghĩ rằng cuối cùng tôi đã đạt được nó. Tôi phải nghĩ rằng cuối cùng tôi đã nhận được nó. Được rồi, chúng ta hãy xem xét vấn đề này. Trước hết, tệp này được lưu dưới dạng tệp xlsx. Đó là một loại tệp tốt, ngoại trừ nó là một loại tệp khủng khiếp vì nó là loại tệp duy nhất không cho phép macro. Bạn phải thay đổi cài đặt này từ xlsx thành xlsm, nếu không tất cả công việc của bạn đối với phần còn lại của video sẽ bị ném ra ngoài cửa sổ. Lưu dưới dạng, thay đổi loại tệp thành xlsm hoặc, heck, xlsb, một trong hai loại đó sẽ hoạt động. Đó là cái bị hỏng-- xlsx-- và nó là cái mặc định, thật điên rồ phải không? Xlsm, nhấp vào Lưu. Nếu bạn chưa từng thực hiện macro trước đây, hãy Alt + T cho Tom, M cho Macro,S for Security và bạn sẽ có thể lưu tất cả các macro mà không cần thông báo. Cần thay đổi điều đó thành thứ hai, điều đó sẽ cho phép các macro của bạn hoạt động.
Được rồi, bây giờ chúng ta có hai máy thái. Cá là bạn chưa bao giờ biết điều này, nhưng máy thái có tên. Chúng ta sẽ đi tới Công cụ Slicer, Tùy chọn, Cài đặt Slicer và thấy cái này có tên là Slicer_Name. Như vậy đó. Đi tới cái thứ hai, vào Slicer Tools, Options, Slicer Settings, cái này được gọi là Slicer_Name1-- không phải Name space 1, Name1. Hai cái tên như vậy.
Đây là những gì chúng tôi sẽ làm. Chúng ta sẽ chuyển sang VBA-- Alt + F11. Trong VBA, nếu bạn chưa từng làm VBA, bạn sẽ có màn hình xám lớn này. Chúng ta sẽ đến đây và nói View, Project Explorer, trong Project Explorer, hãy tìm tệp của bạn-- của tôi có tên là Podcast 2104. Mở Microsoft Excel Objects và trang tính mà tôi muốn nó hoạt động được gọi là Bảng điều khiển. Tôi sẽ nhấp chuột phải vào đó và nói Mã Chế độ xem. Mã mà chúng tôi đang viết này không thể đi trong một mô-đun như trong một macro thông thường - mã này phải có trên trang tính này. Mở menu thả xuống trên cùng bên trái, Trang tính, sau đó trong menu thả xuống trên cùng bên phải, chúng ta sẽ nói Cập nhật bảng tổng hợp. Được rồi, đây là nơi mã của chúng ta sẽ chuyển đến bây giờ. Tôi đã nướng trước mã này. Hãy xem mã ở đây trong notepad. Vì vậy, chúng tôi 'sẽ có hai bộ nhớ đệm của Slicer - SC1 và SC2-- một mục của Slicer và sau đó, ngay tại đây, đây là nơi bạn sẽ phải tùy chỉnh nó. Vì vậy, hai Slipper của tôi được gọi là Tên và Tên1. Được rồi, bạn sẽ phải đặt tên máy cắt của mình vào đó. Application.Screenupdating = False, Application.EnableEvents = False, và sau đó là Slicer Cache 2-- chúng ta sẽ xóa bộ lọc, sau đó đối với từng mục SI1 và sc1.SlicerItems, nếu nó được chọn, thì chúng ta sẽ thực hiện cùng một mục trong Slicer Cache sẽ được chọn. Đây là một vòng lặp nhỏ sẽ chạy qua tuy nhiên nhiều mục tình cờ nằm trong máy cắt đó. Trong trường hợp của tôi, tôi có 11 hoặc 12; trong trường hợp của bạn, bạn có thể có nhiều hơn.Vì vậy, hai Slipper của tôi được gọi là Tên và Tên1. Được rồi, bạn sẽ phải đặt tên máy cắt của mình vào đó. Application.Screenupdating = False, Application.EnableEvents = False, và sau đó là Slicer Cache 2-- chúng ta sẽ xóa bộ lọc, sau đó đối với từng mục SI1 và sc1.SlicerItems, nếu nó được chọn, thì chúng ta sẽ thực hiện cùng một mục trong Slicer Cache sẽ được chọn. Đây là một vòng lặp nhỏ sẽ chạy qua tuy nhiên nhiều mục tình cờ nằm trong máy cắt đó. Trong trường hợp của tôi, tôi có 11 hoặc 12; trong trường hợp của bạn, bạn có thể có nhiều hơn.Vì vậy, hai Slipper của tôi được gọi là Tên và Tên1. Được rồi, bạn sẽ phải đặt tên máy cắt của mình vào đó. Application.Screenupdating = False, Application.EnableEvents = False, và sau đó là Slicer Cache 2-- chúng ta sẽ xóa bộ lọc, sau đó đối với từng mục SI1 và sc1.SlicerItems, nếu nó được chọn, thì chúng ta sẽ thực hiện cùng một mục trong Slicer Cache sẽ được chọn. Đây là một vòng lặp nhỏ sẽ chạy qua tuy nhiên nhiều mục tình cờ nằm trong máy cắt đó. Trong trường hợp của tôi, tôi có 11 hoặc 12; trong trường hợp của bạn, bạn có thể có nhiều hơn.sẽ làm cho cùng một mục trong Slicer Cache được chọn. Đây là một vòng lặp nhỏ sẽ chạy qua tuy nhiên nhiều mục tình cờ nằm trong máy cắt đó. Trong trường hợp của tôi, tôi có 11 hoặc 12; trong trường hợp của bạn, bạn có thể có nhiều hơn.sẽ làm cho cùng một mục trong Slicer Cache được chọn. Đây là một vòng lặp nhỏ sẽ chạy qua tuy nhiên nhiều mục tình cờ nằm trong máy cắt đó. Trong trường hợp của tôi, tôi có 11 hoặc 12; trong trường hợp của bạn, bạn có thể có nhiều hơn.
Khi chúng ta hoàn thành việc đó, hãy bật lại sự kiện, bật lại Cập nhật màn hình. Ổn thỏa. Vì vậy, chúng tôi sẽ lấy mã này, sao chép mã này và dán nó vào đây ở giữa macro của chúng tôi như vậy. Được rồi, bây giờ, hãy đảm bảo rằng tôi sẽ nhấn Ctrl + G và yêu cầu của tôi là Application.EnableEvents, đang bật hoặc tắt-- vậy,? Application.EnableEvents-- và đó là sự thật. Nếu kết quả của bạn là sai, thì bạn muốn quay lại đây và nói rằng nó = Đúng-- vì vậy, bạn đang bật những sự kiện đó. Ổn thỏa. Bây giờ, đây là những gì sẽ xảy ra. Vì vậy, huấn luyện viên của chúng tôi nên làm việc ở đây, nó trên bảng tính phù hợp. Chúng tôi được lưu trong một tệp xlxm, và tôi đã bật Macro và những gì chúng ta sẽ thấy, đó là khi tôi chọn từ Bộ cắt bên trái, Bộ đệm Slicer đó 1-- Tôi 'Tôi sẽ chọn Andy thông qua Della - Slicer khác cũng sẽ được cập nhật. Được rồi Và ngay cả khi tôi chỉ chọn Gloria - chỉ Gloria - có vẻ như nó đang hoạt động rất, rất tốt. Ngay cả khi tôi nhấn CTRL + nhấp chuột, khi tôi buông Ctrl, cả ba nút sẽ cập nhật.
Nhưng đây là gotcha-- luôn luôn có gotcha-- cái này Slicer, nó phải tồn tại, nhưng bạn không thể sử dụng cái này Slicer-- khoan đã, ý tôi là bạn có thể, bạn có thể sử dụng Slicer nhưng nó sẽ khiến mọi thứ nhầm lẫn . Bởi vì những gì sắp xảy ra là tôi sẽ thay đổi điều này thành Hank và họ sẽ quay lại những gì có trong Slicer Cache 1, bởi vì tôi đã thay đổi bảng xoay trên trang tính này. Bây giờ, trong cuộc sống thực, bạn sẽ có hai bảng xoay trên cùng một trang tính? Tôi không biết bạn có hay không, được rồi, nhưng mọi thứ sẽ trở nên điên rồ một chút.
Bây giờ, chúng ta hãy nhìn vào điều này. Điều đầu tiên tôi muốn làm là tôi sẽ chèn một trang tính mới-- Alt + IW để chèn trang tính-- và tôi sẽ gọi đây là DarkCave. Bạn có thể gọi nó bất cứ điều gì bạn muốn. Tôi sẽ lấy bảng điều khiển đó sẽ không hoạt động, tôi sẽ sao chép bảng điều khiển đó và đến đây vào hang tối và dán nó vào đó, sau đó nhấp chuột phải và ẩn trang tính đó để không ai nhìn thấy Slicer đó. Và sau đó, từ đây, chúng ta sẽ có thể xóa nó. Tốt, được rồi. Và chúng tôi sẽ chỉ kiểm tra để đảm bảo rằng chúng vẫn đang hoạt động - chọn Charlie thông qua Eddie và cả hai đều đang cập nhật. Bây giờ, chuyện gì đang xảy ra? Slicer mà chúng tôi không thể nhìn thấy, cái mà chúng tôi đã ẩn đi, nó cũng đang cập nhật, nhưng chúng tôi không quan tâm rằng nó đang cập nhật.
Bây giờ, nếu bạn muốn để những thứ của mình trên các trang tính khác nhau thì sao? Tôi sẽ chèn một trang tính mới ở đây - Alt + IW-- và tôi sẽ lấy một trong các bảng tổng hợp này - có thể là bảng tổng hợp thứ hai-- và di chuyển nó sang trang tính khác-- vì vậy, Ctrl + C để sao chép bảng tổng hợp, Ctrl + V để dán bảng tổng hợp vào đây. Và nếu tôi cần có một máy cắt ở đây - không chèn một lát từ bảng tổng hợp này - chúng ta phải quay lại trang tổng quan của mình, lấy bộ cắt đó là Bộ cắt điều khiển, Ctrl + C để tạo một bản sao của nó, và dán nó vào đây-- Ctrl + V. Ổn thỏa? Bây giờ, chúng tôi không có mã nào trên trang tính này - không có mã nào trên Sheet4-- và tôi đã nghĩ rằng mình sẽ phải thêm một số mã vào Sheet4, nhưng đây là điều tuyệt vời: Khi tôi thay đổi bộ cắt này, điều đang xảy ra là, trên bảng điều khiển mà bảng xoay 'đang cập nhật mặc dù bảng tổng hợp trên trang tính không hoạt động đó đang cập nhật, chúng sẽ chạy mã và điều này cũng sẽ cập nhật. Khá tuyệt vời mà nó hoạt động.
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.
Muốn cảm ơn bạn đã ghé qua, chúng tôi sẽ gặp bạn lần sau với một netcast khác từ.
Tải tập tin
Tải file mẫu tại đây: Podcast2104.xlsm
