Dilimleyiciler, pivot tablolar için harikadır çünkü birden çok pivot tabloyu tek bir dilimleyici setinden kontrol edebilirsiniz. Ama - bu bir çeşit yalan. Aynı veri kümesinden gelen birden çok pivot tabloyu kontrol edebilirsiniz. İki farklı veri kümesinden gelen pivot tablolarınız olduğunda, bu oldukça zordur. Size bunu başarmanıza izin verecek bir VBA göstereceğim.
Video izle
- Bir dilimleyicinin iki pivot tabloyu çalıştırmasına nasıl sahip olabilirsiniz?
- Her iki pivot tablo da aynı veri kümesinden geldiyse: Dilimleyici Seç, Bağlantıları Raporla, Diğer Pivot Tabloları Seç
- Ancak pivot tablolar farklı veri kümelerinden geldiyse:
- Çalışma kitabı uzantısını XLSX yerine XLSM olarak değiştirmek için Farklı Kaydet'i kullanın
- Alt = "" + TMS kullanın ve makro güvenliğini ikinci ayara değiştirin.
- VBA'ya ulaşmak için Alt + F11
- Proje gezginini görüntülemek için Ctrl + R
- İlk pivot tablonuzu ve dilimleyicinizi içeren çalışma sayfasını bulun
- Worksheet_Update kodunu girin
- İkinci dilimleyiciyi saklayın, böylece varlığını korur, ancak kimse o dilimleyiciden seçim yapamaz
Video Metni
Podcast için Excel'i Öğrenin, Bölüm 2104: Dilimleyicileri Farklı Veri Kümelerinden Eşitleyin.
Hey, ağa tekrar hoş geldiniz, ben Bill Jelen ve bugünün sorusu, bir veri kümesinden gelen bu iki pivot tablonun nasıl alınacağı ve Dilimleyicinin tüm bu pivot tabloları nasıl kontrol edeceği ile ilgili değil. Konu bu değil. Bunu yapmak kolay bir şey: Dilimleyici, Araçlar, Seçenekler, eski sürümde Rapor Bağlantıları veya Dilimleyici Bağlantıları ve bu Dilimleyicinin tüm bu pivot tabloları kontrol etmesini istediğinizi kontrol edin. Kolay değil mi? Bu soru, iki farklı veri kümemizin olduğu ve bundan bir pivot tablo oluşturacağımız bu çalışma sayfasıyla ilgili ve bundan - şimdi bu pivot tabloları oluştururken videoyu hızlandırmama izin verin. Pekala, şimdi göreceğiniz şey, iki pivot tablom var, bu pivot tablo bir veri kümesinden oluşturuldu ve bu pivot tabloyu kontrol eden bir dilimleyici var;ve sonra farklı bir veri kümesinden oluşturulmuş ikinci bir pivot tablom ve bu pivot tabloyu kontrol eden bir dilimleyicim var. Ancak bu dilimleyicinin hem bu pivot tabloyu hem de farklı bir veri kümesinden oluşturulan bu pivot tabloyu kontrol etmesini sağlamanın kesinlikle bir yolu yoktur. Peki. Ama bugün bunu bir makro ile nasıl yapacağınızı göstereceğim.
Şimdi, bunu yapmak zor. Soru geldiğinde, "Şimdi, bunu yapabileceğini sanmıyorum" dedim. Ama üzerinde çalışıyorum ve deniyorum ve sanırım sonunda anladım. Sonunda anladığımı düşünmek zorundayım. Pekala, hadi bunun üzerinden geçelim. Öncelikle, bu bir xlsx dosyası olarak kaydedilir. Makrolara izin vermeyen tek dosya türü olduğu için korkunç bir dosya türü olması dışında bu iyi bir dosya türü. Bunu xlsx'ten xlsm'ye değiştirmelisiniz, yoksa tüm işiniz videonun geri kalanına kadar pencereden atılacaktır. Farklı kaydedin, dosya türünü xlsm veya heck, xlsb olarak değiştirin, bunlardan herhangi biri çalışacaktır. Kırık olan bu - xlsx-- ve bu varsayılan, çılgın değil mi? Xlsm, Kaydet'i tıklayın. Daha önce hiç makro yapmadıysanız, Tom için Alt + T, Makro için M,S for Security ve bildirimde bulunmaksızın tüm makroları kaydedebileceksiniz. Bunu ikincisiyle değiştirmeniz gerekiyor, bu da makrolarınızın çalışmasına izin verecek.
Pekala, şimdi iki dilimleyicimiz var. Bahse girerim bunu hiç bilmiyordun, ama dilimleyicilerin isimleri var. Dilimleyici Araçları, Seçenekler, Dilimleyici Ayarlarına gideceğiz ve bunun Dilimleyici_Adı olarak adlandırıldığını göreceğiz. Bunun gibi. İkinciye gidin, Dilimleyici Araçları, Seçenekler, Dilimleyici Ayarları'na gidin, buna Dilimleyici_Adı1 denir - Ad alanı 1, Ad1 değil. Bunun gibi iki isim.
İşte yapacağımız şey. VBA - Alt + F11'e geçeceğiz. VBA'da, hiç VBA yapmadıysanız, bu büyük gri ekrana sahip olacaksınız. Buraya gelip Görünüm, Proje Gezgini diyeceğiz, Proje Gezgini'nde dosyanızı bulun - benimki Podcast 2104 olarak adlandırılır. Microsoft Excel Nesneleri'ni açın ve bunun çalışmasını istediğim sayfaya Gösterge Tablosu adı verilir. Oraya sağ tıklayıp Kodu Görüntüle diyeceğim. Yazdığımız bu kod, normal bir makrodaki gibi bir modüle giremez - bu, bu çalışma sayfasında olmalı. Sol üst açılır listeyi, Çalışma Sayfasını açın, ardından sağ üst açılır menüde Pivot Tablo Güncellemesi diyeceğiz. Pekala, kodumuzun şimdi gideceği yer burası. Bu kodu önceden pişirmiştim. Buradaki not defterine bir göz atalım. Yani bizİki Dilimleyici önbelleğine sahip olacaksınız - SC1 ve SC2 - bir Dilimleyici öğesi ve sonra, tam burada, onu özelleştirmeniz gereken yer burası. Bu yüzden iki Dilimleyicime Ad ve Ad1 adı verildi. Pekala, dilimleyici adlarınızı oraya yazmanız gerekecek. Application.Screenupdating = False, Application.EnableEvents = False ve ardından Dilimleyici Önbelleği 2 - filtreyi temizleyeceğiz ve sonra her öğe için SI1 ve sc1.SlicerItems, seçilmişse, o zaman yapacağız Dilimleyici Önbelleğindeki aynı öğe seçilecektir. Bu, o dilimleyicide birçok öğe olsa da geçecek küçük bir döngüdür. Benim durumumda 11 veya 12 var; senin durumunda, daha fazlasına sahip olabilirsin.Bu yüzden iki Dilimleyicime Ad ve Ad1 adı verildi. Pekala, dilimleyici adlarınızı oraya yazmanız gerekecek. Application.Screenupdating = False, Application.EnableEvents = False ve ardından Dilimleyici Önbelleği 2 - filtreyi temizleyeceğiz ve sonra her öğe için SI1 ve sc1.SlicerItems, seçilmişse, o zaman yapacağız Dilimleyici Önbelleğindeki aynı öğe seçilecektir. Bu, o dilimleyicide birçok öğe olsa da geçecek küçük bir döngüdür. Benim durumumda 11 veya 12 var; senin durumunda, daha fazlasına sahip olabilirsin.Yani iki Dilimleyicime Ad ve Ad1 adı verildi. Pekala, dilimleyici adlarınızı oraya yazmanız gerekecek. Application.Screenupdating = False, Application.EnableEvents = False ve ardından Dilimleyici Önbelleği 2 - filtreyi temizleyeceğiz ve sonra her öğe için SI1 ve sc1.SlicerItems, seçilmişse, o zaman yapacağız Dilimleyici Önbelleğindeki aynı öğe seçilecektir. Bu, o dilimleyicide birçok öğe olsa da geçecek küçük bir döngüdür. Benim durumumda 11 veya 12 var; senin durumunda, daha fazlasına sahip olabilirsin.Dilimleyici Önbelleğinde aynı öğeyi seçilecek hale getireceğiz. Bu, o dilimleyicide birçok öğe olsa da geçecek küçük bir döngüdür. Benim durumumda 11 veya 12 var; senin durumunda, daha fazlasına sahip olabilirsin.Dilimleyici Önbelleğinde aynı öğeyi seçilecek hale getireceğiz. Bu, o dilimleyicide birçok öğe olsa da geçecek küçük bir döngüdür. Benim durumumda 11 veya 12 var; senin durumunda, daha fazlasına sahip olabilirsin.
Bununla işimiz bittiğinde, etkinlikleri tekrar etkinleştirin, Ekran Güncellemesini tekrar açın. Peki. Öyleyse, bu kodu alacağız, bu kodu kopyalayıp buraya, makromuzun ortasına yapıştıracağız. Pekala, şimdi Ctrl + G tuşlarına basacağımdan emin olalım ve istediğim Application.EnableEvents, açık mı yoksa kapalı mı? Application.EnableEvents - ve bu doğru. Eğer seninki yanlış olarak ortaya çıkarsa, o zaman buraya geri gelmek ve bunun = Doğru olduğunu söylemek istiyorsun - yani, o zaman, bu olayları açıyorsun. Peki. Şimdi, olacak olan şey burada. Yani koçumuz burada çalışıyor olmalı, doğru çalışma kağıdında. Bir xlxm dosyasına kaydedildik ve Makroları açtım ve göreceğimiz şey, sol Dilimleyiciden seçtiğimde Dilimleyici Önbelleği 1-- I 'Andy'yi Della aracılığıyla seçeceğim - diğer Dilimleyici de güncellenecek. Tamam Ve sadece Gloria'yı seçsem bile-- sadece Gloria-- gerçekten çok iyi çalışıyor gibi görünüyor. CTRL + tıklama yapsam bile, Ctrl'yi bıraktığımda, üçü de güncellenecek.
Ama işte burada - her zaman bir aldatmaca var - bu Dilimleyici, var olmak zorunda, ama bu Dilimleyiciyi kullanamazsınız - bekle, yani yapabilirsin, bir Dilimleyici kullanabilirsin ama bu işin içini karıştıracak . Çünkü olacak olan, bunu Hank olarak değiştireceğim ve Dilimleyici Önbelleği 1'deki her şeye geri dönecekler, çünkü bu sayfadaki pivot tabloyu değiştirdim. Şimdi, gerçek hayatta aynı sayfada iki pivot tablonuz olacak mı? Öyle mi yoksa değil misin bilmiyorum, tamam, ama işler biraz çılgınca olacak.
Şimdi, şuna bir bakalım. Yapmak istediğim ilk şey, yeni bir çalışma sayfası ekleyeceğim - çalışma sayfasını eklemek için Alt + IW - ve buna DarkCave diyeceğim. Ona ne istersen diyebilirsin. Çalışmayacak olan o panoyu alacağım, o panoyu kopyalayacağım ve buraya karanlık mağaraya geleceğim ve oraya yapıştıracağım ve sonra o sayfayı sağ tıklayıp saklayacağım, böylece kimse o Dilimleyiciyi görmesin. Ve sonra, buradan, onu silebilmeliyiz. Güzel, tamam. Ve hala çalıştıklarından emin olmak için kontrol edeceğiz - Eddie aracılığıyla Charlie'yi seçin ve ikisi de hala güncelleniyor. Şimdi ne oluyor? Göremediğimiz, gizlediğimiz Dilimleyici de güncelleniyor, ancak güncellenmesi umurumuzda değil.
Şimdi, ya eşyalarınızın farklı sayfalarda olmasını istiyorsanız? Buraya yeni bir çalışma sayfası ekleyeceğim - Alt + IW-- ve bu pivot tablolardan birini alacağım - belki ikinci pivot tablo-- ve onu diğer sayfaya taşıyacağım - yani Ctrl + C kopyalamak pivot tabloya, pivot tabloyu buraya yapıştırmak için Ctrl + V'ye basın. Ve burada bir dilimleyiciye ihtiyacım olursa - bu pivot tablodan bir dilim eklemeyin - kontrol panelimize geri dönmeliyiz, kontrol eden Dilimleyici olan dilimleyiciyi almalıyız, Ctrl + C bir kopyasını almak için, ve buraya yapıştırın - Ctrl + V. Peki? Şimdi, bu sayfada hiçbir kodumuz yok - Sayfa4'te kod yok - ve Sayfa4'e biraz kod eklemem gerektiğini düşünüyordum, ama işte güzel olan: Bu dilimleyiciyi değiştirdiğimde, olan şey, pivot tablonun bulunduğu kontrol panelinde 'o sayfadaki etkin olmayan pivot tablo güncelleniyor olsa bile güncelleniyor, kodu çalıştıracak ve bu da güncellenecektir. Bunun işe yaraması oldukça şaşırtıcı.
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.
Uğradığınız için teşekkür etmek isteriz, bir dahaki sefere başka bir internet yayını için görüşürüz.
Dosyayı indir
Örnek dosyayı buradan indirin: Podcast2104.xlsm
