Penapis jadual pangsi biasa menawarkan halaman Tunjukkan Semua Penapis Laporan, tetapi Slicers tidak menyokong fungsi ini. Hari ini, beberapa VBA dapat melengkapkan semua kemungkinan kombinasi slicer.
Tonton Video
Transkrip Video
Belajar Excel Dari, Podcast Episode 2106: Buat PDF Setiap Gabungan 3 Slicers.
Soalan hebat yang kita ada hari ini. Seseorang menulis, mahu tahu apakah itu mungkin. Sekarang, mereka mempunyai 3 alat pemotong yang menjalankan jadual pangsi. Saya tidak tahu seperti apa jadual pangsi. Itu rahsia. Saya tidak dibenarkan melihatnya jadi saya hanya meneka, bukan? Jadi, apa yang mereka lakukan ialah mereka memilih satu item dari setiap alat pemotong dan kemudian membuat PDF, dan kemudian pergi dan memilih item seterusnya dan membuat PDF, dan kemudian item seterusnya, dan item seterusnya, dan anda boleh bayangkan, dengan 400 kombinasi alat pemotong, ini dapat berlangsung selamanya, dan mereka berkata, adakah cara untuk menjalani program dan melengkapkan semua pilihan?
Saya berkata, baiklah, berikut adalah beberapa soalan yang memenuhi syarat. Nombor satu, kita tidak menggunakan Mac, bukan? Bukan Android, bukan Excel untuk iPhone. Ini adalah Excel untuk Windows. Ya, kata mereka. Hebat. Saya berkata, soalan kedua yang sangat penting ialah, kita mahu memilih satu item dari slicer, dan akhirnya item lain dari slicer, dan kemudian item lain dari slicer. Kami tidak memerlukan kombinasi seperti ANDY, dan kemudian ANDY dan BETTY, dan kemudian ANDY dan CHARLIE, bukan? Sudah keluar. Saya hanya akan membuat satu item dari setiap alat pemotong. Ya ya ya. Itulah cara yang akan dilalui. Sempurna, kata saya. Jadi di sini, beritahu saya ini, pilih setiap alat pemotong, pergi ke ALAT SLICER, PILIHAN, dan pergi ke PENGATURAN SLICER. Kami baru sahaja melakukan 2 episod ini. Bukankah ini gila? NAMA UNTUK DIGUNAKAN DI FORMULAS dan saya tahu bahawa SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,baiklah? Jadi, saya rasa saya sudah mendapatnya.
Sekarang, kita akan beralih ke VBA di sini, dan, omong-omong, pastikan bahawa anda disimpan sebagai xlsm dan pastikan keselamatan makro anda ditetapkan untuk membenarkan makro. Sekiranya ia disimpan sebagai xlsx, percayalah, anda perlu melakukan FILE, JIMAT SEBAGAI, anda akan kehilangan semua kerja anda jika anda meninggalkannya sebagai xlsx. Ya, 99.9% hamparan yang anda gunakan adalah xlsx tetapi yang satu ini dengan makro tidak akan berfungsi. ALT + F11. Baiklah, inilah kodnya.
Kami akan menjumpai tiga cache slicer, satu item slicer, dan 3 range. Untuk setiap cache pemotong, kita akan menetapkannya ke nama yang digunakan dalam formula yang baru saya tunjukkan pada kotak dialog PENGATURAN SLICER. Oleh itu, kita mempunyai tiga daripadanya. Saya ingin membersihkan semua perkara untuk memastikan bahawa kita kembali kepada semua yang dipilih. Kaunter ini akan digunakan dalam nama fail kemudian.
Baiklah. Sekarang, bahagian seterusnya di sini, MATI KE KANAN, BANGUN TIGA SENARAI STATIK SEMUA ITEM SLICER. Lihat jalan keluar # 2 untuk melihat mengapa kegilaan ini berlaku. Oleh itu, saya akan mengetahui di mana lajur yang tersedia seterusnya, jenis lebih dari 2 dari lajur terakhir, ingatlah supaya saya dapat memadam barang itu kemudian, dan kemudian, untuk setiap SI, item pemotong, DI SC1 kita akan menulis kapsyen pemotong ke hamparan. Setelah selesai dengan semua item slicer tersebut, ketahui berapa banyak baris yang kita ada hari ini, dan kemudian namakan julatnya sebagai SLICERITEMS1 Kami akan mengulangi keseluruhan perkara untuk slicer cache 2, akan melebihi 1 lajur, SLICERITEMS2, dan SLICERITEMS3.
Izinkan saya menunjukkan kepada anda seperti apa ketika ini Jadi, saya akan meletakkan breakpoint di sini dan kami akan menjalankan kod ini. Baiklah. Itu pantas. Kami akan beralih ke VBA, dan jauh di sebelah kanan, saya akan mendapat 3 senarai baru. Senarai ini adalah semua yang ada di slicer, dan anda melihatnya disebut SLICERITEMS1, SLICERITEMS2, dan SLICERITEMS3, baiklah? Kami akan menyingkirkannya pada akhirnya, tetapi itu memberi kita sesuatu untuk dilalui. Kembali ke VBA.
Baiklah. Kami akan melengkapkan semua item dalam SLICERITEMS1, membersihkan penapis untuk slicer cache 1, dan kemudian kami akan melalui, satu demi satu, melalui setiap item slicer dan melihat apakah item slicer ini = hingga ini CELL1.VALUE, dan, sekali lagi, kita mencari setiap nilai. Jadi, pertama kali, ia akan menjadi ANDY dan kemudian BETTY dan, anda tahu, dan seterusnya.
Ia mengecewakan. Saya tidak dapat mencari cara untuk mematikan semua alat pemotong sekaligus. Saya juga cuba merakam kod dan memilih satu alat pemotong, dan kod yang dirakam itu mengembalikan 9 alat pemotong dan mematikan alat pemotong yang satu itu, ok? Sungguh mengecewakan bahawa saya tidak dapat mencari sesuatu yang lebih baik daripada itu tetapi saya tidak dapat mencari sesuatu yang lebih baik daripada itu.
Oleh itu, kami menetapkan slicer pertama = ke ANDY. Kemudian kita pergi, dan untuk alat pemotong kedua, kita akan menetapkannya = ke item pertama. Untuk alat pemotong ketiga, tetapkan = ke item pertama.
Baiklah. Kemudian, di sini, TAHNIAH JIKA INI ADALAH KOMBINASI YANG SALAH. Saya mesti menerangkan kepada anda mengapa itu penting. Sekiranya kita, sebagai manusia kita melakukan ini, ANDY, kita tidak akan memilih A52 kerana jelas ia berwarna kelabu, tetapi makro akan menjadi terlalu bodoh dan akan memilih A52 dan kemudian 104, dan ini akan membuat ini kosong jadual pangsi. Jadi, terdapat seribu kemungkinan kombinasi di sini. Saya tahu bahawa hanya ada 400 kemungkinan laporan. Itulah yang diberitahu oleh orang itu, dan kami akan mendapat 600 kali untuk membuat PDF laporan ini (jelek - 04:45).
Oleh itu, apa yang akan saya lakukan ialah saya akan melihat di sini di tab ANALISA - ia dipanggil PILIHAN pada tahun 2010 - dan lihat apa nama jadual pangsi ini, dan saya ingin melihat berapa banyak baris kita mendapatkan. Sekiranya saya mendapat 2 baris, saya tahu ia adalah laporan yang tidak mahu saya eksport. Sekiranya saya mendapat lebih daripada 2 baris, 3, 4, 5, 6, maka saya tahu ia adalah laporan yang ingin saya eksport. Anda mesti mengetahui keadaan anda seperti apa keadaannya.
Baiklah. Oleh itu, itulah sebabnya kami memeriksa untuk melihat apakah jadual pangsi 2 dan, itulah nama yang ada di sana di pita, .TABLERANGE2.ROWS.COUNT adalah> 2. Sekiranya tidak> 2, kami tidak mahu buat PDF, baiklah? Oleh itu, pernyataan IF hingga AKHIR AKHIR ini mengatakan bahawa kita hanya akan membuat PDF untuk kombinasi laporan yang mempunyai nilai. MYFILENAME, saya membuat folder bernama C: LAPORAN. Ia hanya folder kosong. C: LAPORAN. Anda memastikan bahawa anda mempunyai folder dan menggunakan nama folder yang sama dalam makro. C: LAPORAN / dan nama fail akan menjadi LAPORAN001.PDF. Sekarang, kaunter kami menginisialisasi kembali ada 1 menggunakan FORMAT, yang setara dalam Excel untuk mengatakan teks pembilang, dan 000. Dengan cara itu, saya akan mendapat 001, kemudian 002, kemudian 003, dan kemudian 004. Mereka akan menyusun dengan betul.Sekiranya saya baru saja memanggil LAPORAN1 ini, dan kemudian saya mempunyai LAPORAN10 dan 11, dan kemudian pada LAPORAN100, semuanya akan disusun bersama apabila mereka tidak tergolong bersama, ok? Oleh itu, membuat nama fail sekiranya fail itu wujud dari kali terakhir kita menjalankannya, kita akan membunuhnya. Dengan kata lain, padamkannya. Sudah tentu, jika anda mencuba dan membunuh fail yang tidak ada, mereka akan membuat kesalahan. Jadi, jika kita mendapat kesilapan pada baris seterusnya, tidak mengapa. Terus saja, tetapi kemudian saya menetapkan semula ralat ketika memeriksa ON ERROR GOTO 0.Sudah tentu, jika anda mencuba dan membunuh fail yang tidak ada, mereka akan membuat kesalahan. Jadi, jika kita mendapat kesilapan pada baris seterusnya, tidak mengapa. Teruskan saja, tetapi kemudian saya menetapkan semula ralat ketika memeriksa ON ERROR GOTO 0.Sudah tentu, jika anda mencuba dan membunuh fail yang tidak ada, mereka akan membuat kesalahan. Jadi, jika kita mendapat kesilapan pada baris seterusnya, tidak mengapa. Terus saja, tetapi kemudian saya menetapkan semula ralat ketika memeriksa ON ERROR GOTO 0.
Berikut adalah LEMBARAN AKTIF, EKSPOR SEBAGAI FORMAT TETAP, sebagai PDF, ada nama fail, semua pilihan itu, dan kemudian saya menambah kaunter, jadi dengan cara itu, lain kali kita dapati yang mempunyai rekod, kita akan membuat LAPORAN002.PDF . Selesaikan ketiga-tiga gelung tersebut dan kemudian HILANGKAN DAFTAR STATIK. Jadi, saya akan ingat lajur mana yang kita ada, ubah saiz 1 baris, 3 lajur, ENTIRECOLUMN.CLEAR, dan kemudian kotak pesanan kecil yang bagus untuk menunjukkan bahawa semuanya telah dibuat. Baik. Mari jalankannya.
Baiklah. Sekarang, apa yang harus berlaku di sini adalah jika kita pergi dan melihat di Windows Explorer, ada. Baik. Ini mencipta … seperti, setiap saat, kita mendapat 2 atau 3 atau 4 atau lebih. Saya akan berhenti sebentar dan membiarkannya berjalan. Baiklah. Kita ada. 326 laporan telah dibuat. Ia menggunakan semua 1000 kemungkinan dan hanya menyimpan kemungkinan yang ada hasilnya. Baiklah, dari jam 9:38 hingga 9:42, 4 minit untuk melakukan semua itu, tetapi masih lebih pantas daripada melakukan 400, baiklah?
Baiklah. Jadi, itulah kaedah makro untuk melakukan ini. Perkara lain yang melanda saya di sini bahawa ia mungkin atau tidak berfungsi. Sungguh sukar untuk diperkatakan. Mari ambil data kami dan saya akan memindahkan data ke buku kerja baru. Pindahkan atau salin, BUAT SALINAN, ke BUKU BARU, klik OK, dan kami akan menggunakan tipu muslihat di sini yang pertama kali saya pelajari dari Szilvia Juhasz - seorang perunding Excel yang hebat di California Selatan - dan kami akan tambah medan KEY di sini. Medan KUNCI adalah = PENYEMAK & ANTENNA & TATATERTIB. Kami akan menyalinnya dan akan memasukkan jadual pangsi baru. Klik OK, dan kami akan mengambil medan itu, medan KEY, dan memindahkannya ke FILTERS kuno, dan kemudian mari kita lihat. (Mari kita hapus sedikit laporan di sini dengan - 08:30) PENYEMAK, ANTENNA, DISIPLIN, dan PENDAPATAN, 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 keputusan ketiga, baiklah? Inilah yang gila. Sekiranya saya mahu merakam makro, jika saya mahu (tulis makro - 13:35) untuk memilih satu item sahaja, cari cara untuk melakukannya dengan menggunakan DEVELOPER, RECORD MACRO, HOWTOCHOOSEONEITEMFROMSLICER, klik OK, dan kami hanya memilih satu barang. FLO. Klik STOP RECORDING, kemudian kita pergi ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER, EDIT itu, dan, cukup pasti, mereka menjadikan FLO TRUE dan kemudian orang lain FLASE. Ini bermaksud jika saya mempunyai alat pemotong dengan 100 item di dalamnya, mereka harus memasukkan 100 baris kod di sana untuk tidak memilih yang lain. Nampaknya tidak cekap tetapi anda pasti ada.
Muat turun fail
Muat turun fail contoh di sini: Podcast2106.xlsx
