Običajni filtri vrtilne tabele ponujajo strani Prikaži vse filtre poročil, toda rezalniki te funkcije ne podpirajo. Danes nekaj VBA zaokroži skozi vse možne kombinacije rezalnikov.
Oglejte si video
Video zapis
Learn Excel From, Podcast Episode 2106: Ustvarite PDF iz vsake kombinacije 3 rezalnikov.
Kako lepo vprašanje imamo danes. Nekdo je pisal in hotel vedeti, ali je to mogoče. Trenutno imajo 3 rezalnike, ki vodijo vrtilno tabelo. Ne vem, kako izgleda vrtilna tabela. Zaupno je. Ne smem ga videti, zato samo ugibam, kajne? Torej, kar počnejo, je, da izberejo en element iz vsakega rezalnika in nato ustvarijo PDF, nato pa izberejo naslednji element in ustvarijo PDF, nato pa naslednji element in naslednji element, in lahko predstavljajte si, s 400 kombinacijami rezalnikov bi to lahko trajalo večno, in rekli so, ali obstaja kakšen način, da program preide in prečka vse možnosti?
V redu, rekel sem, tukaj je nekaj vprašanj, ki izpolnjujejo pogoje. Številka ena, nismo na Macu, kajne? Ne Android, ne Excel za iPhone. To je Excel za Windows. Ja, so rekli. Super. Rekel sem, drugo resnično pomembno vprašanje je, da želimo izbrati en element med rezalnikom in nato na koncu drugi element iz rezalnika, nato pa drugi element iz rezalnika. Ne potrebujemo kombinacij, kot sta ANDY, nato pa ANDY in BETTY, in potem ANDY in CHARLIE, kajne? To je zunaj. Naredil bom samo en predmet iz vsakega rezalnika. Da da Da. Tako bo šlo. Odlično, sem rekel. Torej, tukaj mi povejte to, izberite vsak rezalnik, pojdite na SLICER TOOLS, MOŽNOSTI in pojdite na SLICER SETTINGS. To smo storili pred dvema epizodama. Ali ni to noro? IME ZA UPORABO V FORMULAH in vem, da so SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,vredu? Torej, mislim, da ga imam.
Zdaj bomo tukaj prešli na VBA in, mimogrede, poskrbeli, da ste shranjeni kot xlsm, in poskrbite, da je vaša varnost makra nastavljena tako, da omogoča makre. Če je shranjen kot xlsx, verjemite mi, da morate narediti DATOTEKO, SHRANITI KOT, izgubili boste vse svoje delo, če ga pustite kot xlsx. Da, 99,9% preglednic, ki jih uporabljate, je xlsx, toda ta z makrom ne bo delovala. ALT + F11. Torej, tukaj je koda.
Poiskali bomo tri predpomnilnike rezalnikov, en element rezalnika in 3 obsege. Za vsak predpomnilnik rezalnika ga bomo nastavili na ime, uporabljeno v formuli, ki sem vam jo pravkar pokazal v pogovornem oknu SLICER SETTINGS. Torej, imamo tri. Želim počistiti vse, da se prepričam, da smo spet pri vsem, kar smo izbrali. Ta števec bo pozneje uporabljen v imenu datoteke.
Vredu. Zdaj, ta naslednji odsek tukaj, NA DESNO, ZGRADITE TRI STATIČNI SEZNAMI VSEH KLASIČNIH POSTAVK. Oglejte si izhod št. 2, da vidite, zakaj se je to noro moralo zgoditi. Torej bom ugotovil, kje je naslednji razpoložljivi stolpec, nekako premaknem 2 iz zadnjega stolpca, ne pozabite, da bom lahko stvari pozneje izbrisal, nato pa za vsak element rezalnika SI, IN SC1.SLICERITEMS, ta napis rezalnika bomo zapisali v preglednico. Ko končamo z vsemi temi elementi za rezanje, ugotovimo, koliko vrstic smo imeli danes, in nato poimenujte to območje kot SLICERITEMS1. Vse to bomo ponovili za predpomnilnik rezalnika 2, preko 1 stolpca, SLICERITEMS2 in SLICERITEMS3.
Naj vam pokažem, kako je videti v tem trenutku. Torej, tukaj bom postavil mejno točko in zagnali bomo to kodo. Vredu. To je bilo hitro. Prešli bomo na VBA in daleč tu na desni strani bom dobil 3 nove sezname. Ti seznami so vse, kar je v rezalniku, in vidite, da se imenuje SLICERITEMS1, SLICERITEMS2 in SLICERITEMS3, v redu? Tega se bomo na koncu znebili, a to nam daje nekaj, do česar se lahko zapeljemo. Nazaj na VBA.
Vredu. Zanimili bomo vse elemente v SLICERITEMS1, očistili filter za predpomnilnik rezalnika 1, nato pa bomo po vrsti prešli vsak element rezalnika in videli, ali je ta element rezalnika = temu CELL1.VALUE in spet pregledujemo vsako vrednost. Torej, prvič bo to ANDY in nato BETTY in, veste, itd.
Neprijetno je. Nisem našel nobenega načina, da bi naenkrat izključil vse rezalnike. Poskušal sem celo snemati kodo in izbrati enega rezalnika, posneta koda pa je vrnila 9 rezalnikov in vklopila enega rezalnika, v redu? Tako frustrirajoče, da nisem našel ničesar boljšega od tega, vendar nisem našel ničesar boljšega od tega.
Torej, prvi rezalnik = nastavimo na ANDY. Nato gremo skozi in za drugi rezalnik ga nastavimo = na prvi element. Za tretji rezalnik nastavite = na prvi element.
Vredu. Potem, tu spodaj, ODLOČITE, ČE JE TO VELJAVNA KOMBINACIJA. Moram ti razložiti, zakaj je to pomembno. Če kot ljudje to počnemo, ANDY, ne bi izbrali A52, ker je očitno zatemnjen, makro pa bo preveč neumen in bo izbral A52 in nato 104 in ustvaril bo to prazno vrteča miza. Torej, tukaj je tisoč možnih kombinacij. Vem, da obstaja samo 400 možnih poročil. Tako mi je rekla oseba in tako bomo 600-krat prišli do tega, kje bomo ustvarili PDF tega poročila (grdo - 04:45).
Torej, kar bom naredil, bom pogledal tukaj na zavihku ANALIZA - leta 2010 se je imenoval OPTIONS - in videl, kako je ime te vrtilne tabele, in želim videti, koliko vrstic dobimo. Če dobim dve vrstici, vem, da gre za poročilo, ki ga ne želim izvoziti. Če dobim več kot 2 vrstici, 3, 4, 5, 6, potem vem, da gre za poročilo, ki ga želim izvoziti. Morali boste ugotoviti, v kakšni situaciji je.
Vredu. Torej, zato preverjamo, ali je vrtilna tabela 2 in, to je ime, ki je bilo tam na traku, .TABLERANGE2.ROWS.COUNT> 2. Če ni> 2, ne želimo ustvariti PDF, v redu? Torej, ta izjava IF do tega END IF pravi, da bomo ustvarili PDF samo za kombinacije poročil, ki imajo vrednosti. MYFILENAME, ustvaril sem mapo z imenom C: POROČILA. To je samo prazna mapa. C: POROČILA. Poskrbite, da imate mapo in v makru uporabite isto ime mape. C: POROČILA / in ime datoteke bo REPORT001.PDF. Zdaj je števec, ki smo ga inicializirali nazaj, ena 1 z uporabo FORMAT, kar je v Excelu enako besedilu števca in 000. Tako bom dobil 001, nato 002, nato 003 in nato 004. pravilno razvrščal.Če bi pravkar poklical to POROČILO1, nato pa imam še POROČILO10 in 11 ter kasneje POROČILO100, se bodo vsi skupaj razvrstili, ko ne spadajo skupaj, prav? Torej, ustvarjanje imena datoteke v primeru, da datoteka obstaja od zadnjega, ko smo jo zagnali, jo bomo ubili. Z drugimi besedami, izbrišite ga. Če poskusite ubiti datoteko, ki je ni, bodo vrgli napako. Torej, če dobimo napako v naslednji vrstici, je v redu. Samo nadaljujte, potem pa ponastavim preverjanje napak ON ERROR GOTO 0.Če poskusite ubiti datoteko, ki je ni, bodo vrgli napako. Torej, če dobimo napako v naslednji vrstici, je v redu. Samo nadaljujte, potem pa ponastavim preverjanje napak ON ERROR GOTO 0.Če poskusite ubiti datoteko, ki je ni, bodo vrgli napako. Torej, če dobimo napako v naslednji vrstici, je v redu. Samo nadaljujte, potem pa ponastavim preverjanje napak ON ERROR GOTO 0.
Tukaj je AKTIVNI LIST, IZVOZ KOT FIKSNI FORMAT, kot PDF, tam je ime datoteke, vse te možnosti, nato pa povečavam števec, tako da bomo naslednjič, ko bomo našli tisto, ki ima zapise, ustvarili REPORT002.PDF . Končajte te tri zanke in nato POČISTITE STATIČNE SEZNAME. Torej, zapomnil si se bom, kateri stolpec smo bili, spremenil velikost 1 vrstice, 3 stolpcev, ENTIRECOLUMN.CLEAR in nato lepo okence s sporočilom, ki kaže, da so stvari ustvarjene. V redu. Začnimo.
Vredu. Zdaj, kaj bi se moralo tukaj dogajati, je, če gremo pogledat v Raziskovalec Windows, tam je. V redu. Ustvarja … na primer, vsako sekundo dobimo 2 ali 3 ali 4 ali več. To bom zaustavil in pustil, da teče. Vredu. Tu smo. Ustvarjenih je bilo 326 poročil. Prelistil je vseh 1000 možnosti in obdržal le tiste, kjer je bil dejanski rezultat. V redu, od 9:38 do 9:42, 4 minute, da naredim vse to, vendar še vedno hitreje kot 400, v redu?
Vredu. Torej, to je makro način za to. Druga stvar, ki me je tu zadela, je, da lahko deluje ali pa tudi ne. Res težko je reči. Vzemimo svoje podatke in podatke bom preselil v povsem nov delovni zvezek. PREMAKNITE ALI KOPIRAJ, Ustvarite kopijo v NOVO KNJIGO, kliknite V redu in tukaj bomo uporabili trik, ki sem se ga prvič naučil od Szilvie Juhasz - odlične svetovalke za Excel v Južni Kaliforniji - in bomo tukaj dodajte KLJUČNO polje. Polje KLJUČ je = PREGLEDNIK & ANTENA & DISCIPLINA. To bomo kopirali in vstavili novo vrtilno tabelo. Kliknite V redu in zavzeli bomo to polje, KLJUČNO polje, in ga premaknili navzgor na staromodne FILTRE, nato pa poglejmo. (Odpravimo malo poročilo tukaj z - 08:30) PREGLEDNIK, ANTENA, DISCIPLINA in PRIHODKI, tako.
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.
In tretji izpust, v redu? Ta je noro. Če želim posneti makro, če želim (napišem makro - 13:35) izbrati samo en element, ugotovite, kako to storiti z uporabo RAZVOJALCA, SNEMI MAKRO, HOWTOCHOOSEONEITEMFROMSLICER, kliknite V redu in mi preprosto izberemo enega element. FLO. Kliknite STOP SNIMENJU, nato pa ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER, EDIT, in, seveda dovolj, da FLO TRUE in nato vsi ostali FLASE. Pomeni, če bi imel rezalnik s 100 elementi, bi morali vanj vstaviti 100 vrstic kode, da bi odstranili vse ostalo. Zdi se neverjetno neučinkovito, ampak tu ste.
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Podcast2106.xlsx
