Filter tabel pivot reguler menawarkan halaman Perlihatkan Semua Filter Laporan, tetapi Pemotong tidak mendukung fungsionalitas ini. Saat ini, beberapa VBA melakukan loop melalui semua kemungkinan kombinasi slicer.
Menonton video
Transkrip Video
Pelajari Excel Dari, Podcast Episode 2106: Buat PDF Dari Setiap Kombinasi 3 Pemotong.
Betapa besar pertanyaan yang kita miliki hari ini. Seseorang menulis, ingin tahu apakah itu mungkin. Saat ini, mereka memiliki 3 pemotong yang menjalankan tabel pivot. Saya tidak tahu seperti apa tabel pivot itu. Ini rahasia. Saya tidak diizinkan untuk melihatnya jadi saya hanya menebak-nebak, bukan? Jadi, yang mereka lakukan adalah memilih satu item dari setiap pemotong dan kemudian membuat PDF, lalu pergi dan memilih item berikutnya dan membuat PDF, lalu item berikutnya, dan item berikutnya, dan Anda bisa bayangkan, dengan 400 kombinasi pemotong, ini bisa memakan waktu lama, dan mereka berkata, adakah cara agar program melewati dan mengulang semua opsi?
Saya berkata, baiklah, ini beberapa pertanyaan kualifikasi. Nomor satu, kita tidak menggunakan Mac, bukan? Bukan Android, bukan Excel untuk iPhone. Ini adalah Excel untuk Windows. Ya, kata mereka. Bagus. Saya berkata, pertanyaan kedua yang sangat penting adalah, kami ingin memilih satu item dari slicer, dan kemudian item lainnya dari slicer, dan kemudian item lainnya dari slicer. Kita tidak butuh kombinasi seperti ANDY, lalu ANDY dan BETTY, lalu ANDY dan CHARLIE, bukan? Itu keluar. Saya hanya akan melakukan satu item dari setiap alat pengiris. Ya ya ya. Begitulah caranya. Sempurna, kataku. Jadi di sini, beri tahu saya ini, pilih setiap alat pengiris, buka ALAT PENGIRIM, OPSI, dan buka SETELAN SLICER. Kami baru saja melakukan ini 2 episode yang lalu. Bukankah ini gila? NAMA YANG AKAN DIGUNAKAN DALAM FORMULAS dan saya tahu itu SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,baik? Jadi, saya pikir saya sudah mendapatkannya.
Sekarang, kita akan beralih ke VBA di sini, dan, omong-omong, pastikan Anda disimpan sebagai xlsm dan pastikan keamanan makro Anda disetel untuk mengizinkan makro. Jika disimpan sebagai xlsx, percayalah, Anda harus melakukan FILE, SAVE AS, Anda akan kehilangan semua pekerjaan Anda jika Anda membiarkannya sebagai xlsx. Ya, 99,9% spreadsheet yang Anda gunakan adalah xlsx tetapi yang ini dengan makro tidak akan berfungsi. ALT + F11. Baiklah, ini kodenya.
Kita akan menemukan tiga cache pengiris, satu item pengiris, dan 3 rentang. Untuk setiap cache pengiris, kami akan mengaturnya ke nama yang digunakan dalam rumus yang baru saja saya tunjukkan di kotak dialog PENGATURAN SLICER. Jadi, kami memiliki tiga di antaranya. Saya ingin menghapus semua itu untuk memastikan bahwa kita kembali ke semua yang dipilih. Penghitung ini akan digunakan dalam nama file nanti.
Baik. Sekarang, bagian selanjutnya di sini, DI ATAS KANAN, MEMBANGUN TIGA DAFTAR STATIK DARI SEMUA ITEM SLICER. Lihat hasil # 2 untuk melihat mengapa kegilaan ini harus terjadi. Jadi saya akan mencari tahu di mana kolom berikutnya yang tersedia, jenis pergi ke 2 dari kolom terakhir, ingat itu agar saya dapat menghapus barang-barang nanti, dan kemudian, untuk setiap SI, item pengiris, DI SC1.SLICERITEMS, kami akan menulis keterangan alat pengiris itu ke spreadsheet. Ketika kita selesai dengan semua item pemotong itu, cari tahu berapa banyak baris yang kita miliki hari ini, lalu beri nama kisaran itu sebagai SLICERITEMS1. Kami akan mengulangi semuanya untuk slicer cache 2, membahas 1 kolom, SLICERITEMS2, dan SLICERITEMS3.
Mari saya tunjukkan seperti apa saat ini. Jadi, saya akan meletakkan breakpoint di sini dan kami akan menjalankan kode ini. Baik. Cepat sekali. Kami akan beralih ke VBA, dan jauh di sini ke sisi kanan, saya akan mendapatkan 3 daftar baru. Daftar ini adalah semua yang ada di pemotong, dan Anda melihatnya disebut SLICERITEMS1, SLICERITEMS2, dan SLICERITEMS3, oke? Kita akan menyingkirkan itu pada akhirnya, tapi itu memberi kita sesuatu untuk dilalui. Kembali ke VBA.
Baik. Kami akan mengulang semua item di SLICERITEMS1, menghapus filter untuk slicer cache 1, dan kemudian kami akan melalui, satu per satu, melalui setiap item slicer dan melihat apakah item slicer ini = untuk ini CELL1.VALUE, dan, sekali lagi, kita mengulang setiap nilai. Jadi, pertama kali lewat, itu akan menjadi ANDY dan kemudian BETTY dan, Anda tahu, dan seterusnya.
Ini membuat frustasi. Saya tidak dapat menemukan cara untuk mematikan semua pemotong sekaligus. Saya bahkan mencoba merekam kode dan memilih satu pemotong, dan kode yang direkam mengembalikan 9 pemotong dan menyalakan satu pemotong, oke? Sangat membuat frustrasi sehingga saya tidak dapat menemukan yang lebih baik dari itu tetapi saya tidak dapat menemukan yang lebih baik dari itu.
Jadi, kami menyetel alat pengiris pertama = ke ANDY. Kemudian kita lanjutkan, dan untuk alat pengiris kedua, kita akan mengaturnya = ke item pertama. Untuk pemotong ketiga, atur itu = ke item pertama.
Baik. Kemudian, di sini, PUTUSKAN JIKA INI ADALAH KOMBINASI YANG VALID. Saya harus menjelaskan kepada Anda mengapa itu penting. Jika kita, sebagai manusia kita melakukan ini, ANDY, kita tidak akan memilih A52 karena jelas berwarna abu-abu, tetapi makro akan menjadi terlalu bodoh dan itu akan memilih A52 lalu 104, dan itu akan membuat ini kosong tabel pivot. Jadi, ada seribu kemungkinan kombinasi di sini. Saya tahu bahwa hanya ada 400 kemungkinan laporan. Itulah yang dikatakan orang itu kepada saya, jadi kita akan mendapatkan 600 kali di mana kita akan membuat PDF dari laporan ini (jelek - 04:45).
Jadi, yang akan saya lakukan adalah melihat di sini pada tab ANALISIS - ini disebut OPSI pada tahun 2010 - dan melihat apa nama tabel pivot ini, dan saya ingin melihat berapa banyak baris kita mendapatkan. Dalam kasus saya, jika saya mendapatkan 2 baris, saya tahu itu adalah laporan yang tidak ingin saya ekspor. Jika saya mendapatkan lebih dari 2 baris, 3, 4, 5, 6, maka saya tahu itu adalah laporan yang ingin saya ekspor. Anda harus mencari tahu dalam situasi Anda yang mana.
Baik. Jadi, itulah mengapa kami memeriksa untuk melihat apakah tabel pivot 2 dan, itulah nama yang ada di belakang pita, .TABLERANGE2.ROWS.COUNT adalah> 2. Jika tidak> 2, kami tidak ingin buat PDF, oke? Jadi, pernyataan IF hingga END IF ini mengatakan kita hanya akan membuat PDF untuk kombinasi laporan yang memiliki nilai. MYFILENAME, saya membuat folder bernama C: REPORTS. Itu hanya folder kosong. C: LAPORAN. Anda memastikan bahwa Anda memiliki folder dan menggunakan nama folder yang sama di makro. C: REPORTS / dan nama file akan menjadi REPORT001.PDF. Sekarang, penghitung yang kita inisialisasi kembali ada 1 menggunakan FORMAT, yang setara di Excel untuk mengatakan teks penghitung, dan 000. Dengan begitu, saya akan mendapatkan 001, lalu 002, lalu 003, dan kemudian 004. Mereka akan menyortir dengan benar.Jika saya baru saja menamai REPORT1 ini, dan kemudian saya memiliki REPORT10 dan 11, dan kemudian REPORT100, semuanya akan disortir ketika mereka tidak menjadi satu, oke? Jadi, membuat nama file jika file tersebut ada dari terakhir kali kami menjalankan ini, kami akan membunuhnya. Dengan kata lain, hapus saja. Tentu saja, jika Anda mencoba dan mematikan file yang tidak ada di sana, mereka akan membuat kesalahan. Jadi, jika kita mendapatkan kesalahan di baris berikutnya, tidak apa-apa. Lanjutkan saja, tetapi kemudian saya mengatur ulang pemeriksaan kesalahan ON ERROR GOTO 0.Tentu saja, jika Anda mencoba dan mematikan file yang tidak ada di sana, mereka akan membuat kesalahan. Jadi, jika kita mendapatkan kesalahan di baris berikutnya, tidak apa-apa. Lanjutkan saja, tetapi kemudian saya mengatur ulang pemeriksaan kesalahan ON ERROR GOTO 0.Tentu saja, jika Anda mencoba dan mematikan file yang tidak ada di sana, mereka akan membuat kesalahan. Jadi, jika kita mendapatkan kesalahan di baris berikutnya, tidak apa-apa. Lanjutkan saja, tetapi kemudian saya mengatur ulang pemeriksaan kesalahan ON ERROR GOTO 0.
Ini LEMBAR AKTIF, EKSPOR SEBAGAI FORMAT TETAP, sebagai PDF, ada nama file, semua pilihan itu, dan kemudian saya menaikkan penghitungnya, jadi, jika lain kali kita menemukan yang memiliki catatan, kita akan membuat REPORT002.PDF . Selesaikan ketiga loop tersebut dan kemudian HAPUS DAFTAR STATIK. Jadi, saya akan mengingat kolom mana kami, mengubah ukuran 1 baris, 3 kolom, ENTIRECOLUMN.CLEAR, dan kemudian kotak pesan kecil yang bagus di sana untuk menunjukkan bahwa sesuatu telah dibuat. Baik. Ayo kita jalankan.
Baik. Sekarang, yang seharusnya terjadi di sini adalah jika kita pergi dan melihat di Windows Explorer, itu dia. Baik. Itu menciptakan… seperti, setiap detik, kita mendapatkan 2 atau 3 atau 4 atau lebih. Saya akan menghentikan ini dan membiarkannya berjalan. Baik. Itu dia. 326 laporan telah dibuat. Itu mengulang semua 1000 kemungkinan dan hanya menyimpan yang ada hasil sebenarnya. Baiklah, dari 9:38 sampai 9:42, 4 menit untuk melakukan semua itu, tapi masih lebih cepat daripada melakukan 400, oke?
Baik. Jadi, itulah cara makro untuk melakukan ini. Hal lain yang mengejutkan saya di sini yang mungkin berhasil atau tidak. Sangat sulit untuk mengatakannya. Mari ambil data kita dan saya akan memindahkan data ke workbook baru. PINDAH ATAU SALIN, BUAT SALINAN, ke BUKU BARU, klik OK, dan kami akan menggunakan trik di sini yang pertama kali saya pelajari dari Szilvia Juhasz - konsultan Excel hebat di California Selatan - dan kami akan melakukannya tambahkan bidang KUNCI di sini. Bidang KUNCI adalah = REVIEWER & ANTENA & DISCIPLINE. Kami akan menyalinnya dan kami akan memasukkan tabel pivot baru. Klik OK, dan kita akan mengambil bidang itu, bidang KUNCI, dan memindahkannya ke FILTER model lama, lalu mari kita lihat. (Mari hilangkan sedikit report disini dengan - 08:30) REVIEWER, ANTENNA, DISCIPLINE, and REVENUE, seperti itu.
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.
Dan pengambilan ketiga, oke? Ini yang gila. Jika saya ingin merekam makro, jika saya ingin (menulis makro - 13:35) untuk memilih hanya satu item, cari tahu cara melakukannya dengan menggunakan DEVELOPER, RECORD MACRO, HOWTOCHOOSEONEITEMFROMSLICER, klik OK, dan kami cukup memilih satu barang. FLO. Klik STOP RECORDING, lalu kita pergi ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER, EDIT itu, dan, tentu saja, mereka membuat FLO TRUE dan kemudian semua orang FLASE. Artinya jika saya memiliki alat pengiris dengan 100 item di dalamnya, mereka harus meletakkan 100 baris kode di sana untuk membatalkan pilihan lainnya. Tampaknya sangat tidak efisien tetapi begitulah.
Unduh berkas
Unduh file contoh di sini: Podcast2106.xlsx
