Normal pivot tablo filtreleri, Tüm Rapor Filtresini Göster sayfalarını sunar, ancak Dilimleyiciler bu işlevi desteklemez. Bugün, bazı VBA'lar tüm olası dilimleyici kombinasyonlarında döngü oluşturuyor.
Video izle
Video Metni
Excel'i Podcast Bölüm 2106'dan Öğrenin: 3 Dilimleyicinin Her Kombinasyonundan Bir PDF Oluşturun.
Bugün ne harika bir sorumuz var. Birisi yazdı, bunun mümkün olup olmadığını bilmek istedi. Şu anda, bir pivot tablo çalıştıran 3 dilimleyici var. Pivot tablonun neye benzediğini bilmiyorum. Gizlidir. Görme iznim yok, bu yüzden sadece tahmin ediyorum, değil mi? Yani, yaptıkları şey, her dilimleyiciden bir öğe seçip ardından bir PDF oluşturuyorlar ve sonra gidip bir sonraki öğeyi seçip bir PDF oluşturuyorlar ve sonra bir sonraki öğeyi ve sonraki öğeyi, 400 dilimleyici kombinasyonuyla bu sonsuza kadar sürebilir ve dediler ki, bir programın tüm seçenekleri gözden geçirip döngüye sokmanın bir yolu var mı?
Tamam dedim, işte bazı uygun sorular. Birincisi, biz Mac'te değiliz, değil mi? Android değil, iPhone için Excel değil. Bu Windows için Excel'dir. Evet dediler. Harika. Dedim ki, gerçekten önemli olan ikinci soru, bir dilimleyiciden bir öğeyi ve sonunda dilimleyiciden diğerini ve ardından dilimleyiciden diğer öğeyi seçmek istiyoruz. ANDY gibi kombinasyonlara ihtiyacımız yok, sonra ANDY ve BETTY ve sonra ANDY ve CHARLIE, değil mi? Çıktı. Her dilimleyiciden sadece bir parça yapacağım. Evet evet evet. İşte böyle gidecek. Mükemmel dedim. Öyleyse burada, bana şunu söyleyin, her dilimleyiciyi seçin, DİLİMLEME ARAÇLARI, SEÇENEKLER'e gidin ve DİLİMLEME AYARLARI'na gidin. Bunu 2 bölüm önce yaptık. Bu çılgınlık değil mi? FORMÜLLERDE KULLANILACAK ADIM ve SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE olduğunu biliyorum.Peki? Yani, bende olduğunu düşünüyorum.
Şimdi, burada VBA'ya geçeceğiz ve bu arada, xlsm olarak kaydettiğinizden ve makro güvenliğinizin makrolara izin verecek şekilde ayarlandığından emin olacağız. Eğer xlsx olarak kaydedilmişse, güven bana, gidip bir DOSYA yapmalısın, OLARAK KAYDET, xlsx olarak bırakırsan tüm işini kaybedeceksin. Evet, kullandığınız e-tabloların% 99.9'u xlsx'tir ancak bu makro ile çalışmayacaktır. ALT + F11. Pekala, işte kod.
Üç dilimleyici önbelleği, bir dilimleyici öğesi ve 3 aralık bulacağız. Dilimleyici önbelleklerinin her biri için, onu Dilimleyici AYARLARI iletişim kutusunda az önce gösterdiğim formülde kullanılan ada göre ayarlayacağız. Yani, bunlardan üçüne sahibiz. Seçilen her şeye geri döndüğümüzden emin olmak için hepsini temizlemek istiyorum. Bu sayaç daha sonra dosya adında kullanılacaktır.
Peki. Şimdi, buradaki sonraki bölüm, SAĞA DOĞRU, TÜM DİLİMLEME ÖĞELERİNİN ÜÇ STATİK LİSTESİ OLUŞTURUN. Bu çılgınlığın neden olması gerektiğini görmek için 2. çıkışı görün. Bu yüzden, bir sonraki kullanılabilir sütunun nerede olduğunu bulacağım, son sütundan 2'nin üzerine gidiyorum, bunları daha sonra silebilmek için hatırlayın ve sonra her SI, dilimleyici öğesi için SC1.SLICERITEMS, bu dilimleyici başlığını elektronik tabloya yazacağız. Tüm bu dilimleyici öğelerle işimiz bittiğinde, bugün kaç satırımız olduğunu bulun ve ardından bu aralığı SLICERITEMS1 olarak adlandırın. Tüm bunları dilimleyici önbellek 2 için, 1 sütun, SLICERITEMS2 ve SLICERITEMS3 üzerinden tekrarlayacağız.
Bu noktada neye benzediğini size göstereyim. Bu yüzden buraya bir kesme noktası koyacağım ve bu kodu çalıştıracağız. Peki. Bu kadar hızlı oldu. VBA'ya geçeceğiz ve buradan çok uzakta sağ tarafta, 3 yeni liste alacağım. Bu listeler dilimleyicideki her şeydir ve bunun adı SLICERITEMS1, SLICERITEMS2 ve SLICERITEMS3, tamam mı? Sonunda bundan kurtulacağız, ama bu bize döngüye girecek bir şey veriyor. VBA'ya geri dönün.
Peki. SLICERITEMS1'deki tüm öğelerin üzerinden geçeceğiz, dilimleyici önbelleği 1 için filtreyi temizleyeceğiz ve ardından her bir dilimleyici öğesinden birer birer geçeceğiz ve bu dilimleyici öğesinin buna = olup olmadığına bakacağız. CELL1.VALUE ve yine, değerlerin her birinde döngü yapıyoruz. Yani, ilk seferinde ANDY olacak ve sonra BETTY ve biliyorsunuz, vb.
Sinir bozucu. Tüm dilimleyicileri aynı anda kapatmanın bir yolunu bulamadım. Kodu kaydetmeyi ve bir dilimleyici seçmeyi bile denedim ve kaydedilen kod 9 dilimleyiciyi geri döndürüyor ve bir dilimleyiciyi açıyordu, tamam mı? O kadar sinir bozucu ki bundan daha iyisini bulamadım ama bundan daha iyisini bulamadım.
Böylece, ilk dilimleyiciyi = ANDY olarak ayarladık. Sonra geçiyoruz ve ikinci dilimleyici için, onu = ilk öğeye ayarlayacağız. Üçüncü dilimleyici için, onu = ilk öğeye ayarlayın.
Peki. Sonra, burada, BUNUN GEÇERLİ BİR KOMBİNASYON OLUP OLMADIĞINA KARAR VERİN. Bunun neden önemli olduğunu size açıklamalıyım. Biz insanlar olarak bunu yapıyorsak, ANDY, A52'yi seçmezdik çünkü açıkça gri görünüyor, ancak makro çok aptal olacak ve A52'yi ve sonra 104'ü seçecek ve bu boşluğu yaratacak Pivot tablo. Yani, burada binlerce olası kombinasyon var. Sadece 400 olası rapor olduğunu biliyorum. Kişinin bana söylediği buydu ve bu yüzden bu (çirkin - 04:45) raporun PDF'sini oluşturacağımız yerde 600 kez alacağız.
Öyleyse yapacağım şey, buraya ANALİZ sekmesine bakacağım - buna 2010'da SEÇENEKLER adı verildi - ve bu pivot tablonun adının ne olduğuna bakacağım ve kaç satır olduğunu görmek istiyorum biz alırız. Benim durumumda, 2 satır alırsam, bunun dışa aktarmak istemediğim bir rapor olduğunu biliyorum. 2 satırdan (3, 4, 5, 6) daha fazlasını alırsam, bunun dışa aktarmak istediğim bir rapor olduğunu biliyorum. Durumunuzda bunun ne olduğunu anlamanız gerekecek.
Peki. Bu nedenle, pivot tablo 2'nin ve şeritte bulunan adın .TABLERANGE2.ROWS.COUNT> 2 olup olmadığını kontrol etmemizin nedeni budur.> 2 değilse, PDF oluştur, tamam mı? Öyleyse, bu END IF'ye kadar olan bu IF ifadesi, yalnızca değerleri olan rapor kombinasyonları için PDF'ler oluşturacağımızı söylüyor. MYFILENAME, C: REPORTS adında bir klasör oluşturdum. Bu sadece boş bir klasör. C: RAPORLAR. Bir klasörünüz olduğundan ve makroda aynı klasör adını kullandığınızdan emin olun. C: REPORTS / ve dosyanın adı REPORT001.PDF olacaktır. Şimdi, tekrar başlattığımız sayaç, FORMAT kullanarak 1 var, bu da Excel'de sayaç metnini söylemeye eşdeğer ve 000. Bu şekilde 001, sonra 002, sonra 003 ve sonra 004 elde edeceğim. doğru sıralayacağız.Bu REPORT1'i aradıysam ve daha sonra bir REPORT10 ve 11'im olsaydı ve daha sonra REPORT100'de, hepsi birbirine ait olmadıklarında bir araya gelecekler, tamam mı? Bu nedenle, dosyanın adını son çalıştırdığımızdan beri var olması durumunda dosyanın adını oluşturarak onu öldüreceğiz. Başka bir deyişle, silin. Elbette, orada olmayan bir dosyayı öldürmeye çalışırsanız, bir hata atarlar. Yani, sonraki satırda bir hata alırsak sorun değil. Sadece devam edin, ama sonra 0 HATA GİTMESİNDE hatayı kontrol ederek hatayı sıfırlarım.Elbette, orada olmayan bir dosyayı öldürmeye çalışırsanız, bir hata atarlar. Yani, sonraki satırda bir hata alırsak sorun değil. Sadece devam edin, ama sonra 0 HATA GİTMESİNDE hatayı kontrol ederek hatayı sıfırlarım.Elbette, orada olmayan bir dosyayı öldürmeye çalışırsanız, bir hata atarlar. Yani, sonraki satırda bir hata alırsak sorun değil. Sadece devam edin, ama sonra 0 HATA GİTMESİNDE hatayı kontrol ederek hatayı sıfırlarım.
İşte AKTİF SAYFA, SABİT BİÇİM OLARAK İHRACAT, bir PDF olarak, dosya adı, tüm bu seçenekler var ve sonra sayacı artırıyorum, böylece, bir dahaki sefere kayıtları olan birini bulduğumuzda, REPORT002.PDF'yi oluşturacağız. . Bu üç döngüyü tamamlayın ve ardından STATİK LİSTELERİ TEMİZLEYİN. Böylece, hangi sütun olduğumuzu hatırlayacağım, 1 satırı, 3 sütunu, ENTIRECOLUMN.CLEAR'ı ve sonra orada şeylerin yaratıldığını göstermek için güzel bir mesaj kutusu. Tamam. Hadi çalıştıralım.
Peki. Şimdi, burada olması gereken şey, gidip Windows Gezgini'ne bakarsak, işte orada. Tamam. Yaratmaktır… Sanki her saniye 2 veya 3 veya 4 veya daha fazlasını alıyoruz. Bunu duraklatacağım ve çalışmasına izin vereceğim. Peki. İşte oradayız. 326 rapor oluşturuldu. Tüm 1000 olasılığın içinden geçti ve yalnızca gerçek bir sonucun olduğu durumları korudu. Pekala, 9: 38'den 9: 42'ye kadar, hepsini yapmak için 4 dakika, ama yine de 400'den daha hızlı, tamam mı?
Peki. Yani, bunu yapmanın makro yolu budur. Burada beni etkileyen diğer şey işe yarayabilir ya da çalışmayabilir. Söylemesi gerçekten zor. Verilerimizi alalım ve verileri yepyeni bir çalışma kitabına taşıyacağım. YENİ KİTABA TAŞIYIN VEYA KOPYALAYIN, BİR KOPYA OLUŞTURUN, TAMAM'a tıklayın ve burada ilk kez Güney Kaliforniya'da harika bir Excel danışmanı olan Szilvia Juhasz'dan öğrendiğim bir numara kullanacağız ve biz de buraya bir ANAHTAR alanı ekleyin. ANAHTAR alanı = REVIEWER & ANTENNA & DISCIPLINE şeklindedir. Bunu kopyalayıp yeni bir pivot tablo ekleyeceğiz. Tamam'a tıklayın ve bu alanı, ANAHTAR alanını alıp eski moda FİLTRELER'e taşıyacağız ve sonra bakalım. (Buraya 08:30 ile küçük bir rapor verelim) DEĞERLENDİRİCİ, ANTEN, DİSİPLİN ve GELİR, işte böyle.
Alright, now, normally what we would do here is would come open this filter and choose one item from the filter, but the trick from Szilvia is that we can take this pivot table and go to either the ANALYZE tab in ’13 or ’16, or the OPTIONS tab in 2010, open the OPTIONS dropdown, say SHOW REPORT FILTER PAGES, SHOW ALL PAGES OF KEY, and what it's doing right now is it’s inserting a new worksheet for every unique combination of the KEY, probably 300 and some files, alright? Now, how many worksheets can you have in a workbook? Well, that number is different on every computer and it depends on how complicated the workbook is because it's limited by available memory, but here we start on ANDY B37 112. I’m going to press CONTROL and this arrow down to JOE, like that.
The beautiful advantage here is, when I do FILE, EXPORT, CREATE A PDF, and then ALLREPORTS, we’re going to end up with a single PDF with all 326 reports in it. Now, we could have created a single PDF using Adobe Acrobat, select all of these reports, right click, and COMBINE FILES IN ACROBAT, but that requires you have a full version of Acrobat, not just Acrobat Reader.
So, this great trick using SHOW REPORT FILTER PAGES from Szilvia might be a great, great alternative if you have enough memory to create all the versions.
Alright. To learn more about VBA, check out this book Excel 2016 VBA And Macros by Bill Jelen and Tracy Syrstad. That will get you up the VBA learning curve.
Alright. The goal is to loop through all combinations in 3 slicers and generate a PDF for each. Used a little VBA to loop through those slicers. Save as PDF using VBA. The alternate solution there at the end is Szilvia Juhasz’s SHOW REPORT FILTER PAGES and then export the whole thing as PDF.
Hey. I want to thank you for stopping by. We'll see you next time for another netcast from.
Well, this will be an outtake. First time I ran this darn thing, I got a 1000 of them, and every darn one of them was Andy A52 104. I'm like what the heck is going on? Except I didn’t say heck.
Alright. So, here, watch this code. This was the code I had. I said I'm going to go through all of the filters FOR EACH SI IN SC1.SLICERITEMS and then I set it = to FALSE, and then the one that I want, I'll set = to TRUE, right? Sound like a great, great bit of code, alright?
So, here's what happens. The first one is Andy, goes away. Betty goes away. Charlie goes away. Dale. Here, I'll just keep pressing F8, F8, F8, F8. I'm down to the last one. This is JOE. I'm about to set JOE = to FALSE and watch what happens over there in Excel. Bam. Once you turn JOE off, it turns them all back on. I mean, that stinks, Excel, and then I would try and turn, what is it, ANDY back on and turning ANDY back on when everybody else is already on. So, it ran through… it created a 1000 of the PDFs, every stinking one. It was ANDY A52 104. It's funny now. It wasn't then.
Alright. Here’s another outtake. Why did I go to the trouble of building the list, the static list, off to the right hand side so I can loop through that static list? Well, originally, I was looping through all of the items in the slicers themselves and it was causing some wrong results. See, here, Andy A52 112 should be 0, but when I actually ran the loop, ANDY A52 112 is showing up with six rows. I’m like, well, that can't be. So, over here, my code, ALT+F11, I put a thing, if SI1.CAPTION=ANDY, SI2.CAPTION=A52, SI3 CAPTION=112, THEN STOP, right? So let's run this code, then stop.
There we are, and I will come back. We should have ANDY A52 112, but when I look, ANDY, it’s not A52, it’s D33. What the heck is going on, and then I come back here, ALT+F11, and I right-click and say that I want to ADD A WATCH, and when I look at this, it claims that the caption is A52 but, very clearly, it's D33. So, is this a bug or am I just violating some weird rule by looping through a collection of 10 items when the order of those 10 items is constantly being reordered? It seems like that must be the problem. Hence, we went with the static list off to the right.
Ve üçüncü çıkış, tamam mı? Deli olan bu. Bir makro kaydetmek istiyorsam, sadece bir öğe seçmek (bir makro yazmak - 13:35) istersem, DEVELOPER, RECORD MACRO, HOWTOCHOOSEONEITEMFROMSLICER kullanarak bunu nasıl yapacağımı öğrenin, Tamam'a tıklayın ve sadece birini seçelim öğe. FLO. KAYDI DURDUR'a tıklayın, sonra ALT + F8'e gidiyoruz, HOWTOCHOOSEONEITEMFROMSLICER, bunu DÜZENLE ve elbette FLO'yu DOĞRU yapıyorlar ve sonra diğer herkes FLASE. Bu, içinde 100 öğe olan bir dilimleyicim olsaydı, diğer her şeyin seçimini kaldırmak için oraya 100 satır kod koymaları gerektiği anlamına gelir. İnanılmaz derecede verimsiz görünüyor ama işte buradasın.
Dosyayı indir
Örnek dosyayı buradan indirin: Podcast2106.xlsx
