Redovni filtri zaokretne tablice nude stranice Prikaži sve filtre izvješća, ali rezači ne podržavaju ovu funkciju. Danas neki VBA prolaze kroz sve moguće kombinacije rezača.
Gledaj video
Prijepis videozapisa
Learn Excel From, Podcast Epizoda 2106: Stvorite PDF svake kombinacije 3 rezača.
Kakvo sjajno pitanje imamo danas. Netko se javio i želio znati je li to moguće. Trenutno imaju 3 rezača koji vode pivot tablicu. Ne znam kako izgleda stožerna tablica. Povjerljivo je. Ne smijem ga vidjeti pa samo pretpostavljam, zar ne? Dakle, ono što rade je da odaberu po jednu stavku iz svakog rezača, a zatim stvore PDF, a zatim izaberu sljedeću stavku i kreiraju PDF, a zatim sljedeću stavku i sljedeću stavku, a možete i zamislite, s 400 kombinacija rezača, ovo bi moglo potrajati zauvijek, i rekli su, postoji li neki način da se program prođe i prođe kroz sve opcije?
Rekao sam, u redu, evo nekoliko kvalifikacijskih pitanja. Prvo, nismo na Macu, zar ne? Ni Android, ni Excel za iPhone. Ovo je Excel za Windows. Da, rekli su. Sjajno. Rekao sam, drugo jako važno pitanje je, želimo odabrati jedan predmet iz rezača, a zatim na kraju drugi predmet iz rezača, a zatim drugi predmet iz rezača. Ne trebaju nam kombinacije poput ANDY, pa ANDY i BETTY, pa ANDY i CHARLIE, zar ne? To je vani. Samo ću napraviti po jedan predmet iz svake rezačice. Da da da. Tako će to ići. Savršeno, rekao sam. Dakle, evo, recite mi ovo, odaberite svaku rezač, idite na SLICER ALATI, OPCIJE i idite na SLICER SETTINGS. Upravo smo ovo radili prije 2 epizode. Nije li ovo ludo? IME ZA KORIŠTENJE U FORMULAMA i znam da je SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,u redu? Dakle, mislim da sam ga dobio.
Sada ćemo ovdje prijeći na VBA i, usput, osigurati da ste spremljeni kao xlsm i provjerite je li vaša sigurnost makronaredbe postavljena tako da dopušta makronaredbe. Ako je spremljen kao xlsx, vjerujte mi, morate otići napraviti FILE, SPREMITI KAO, izgubit ćete sav svoj posao ako ga ostavite kao xlsx. Da, 99,9% proračunskih tablica koje koristite su xlsx, ali ova s makronaredbom neće raditi. ALT + F11. U redu, ovdje je kod.
Pronaći ćemo tri predmemorije rezača, jednu stavku rezača i 3 raspona. Za svaku od predmemorija rezača postavit ćemo je na ime koje se koristi u formuli koje sam vam upravo pokazao u dijaloškom okviru SLICER SETTINGS. Dakle, imamo ih troje. Želim očistiti sve one kako bih bio siguran da smo se vratili svemu što je odabrano. Ovaj brojač će se kasnije koristiti u nazivu datoteke.
U redu. Sada, ovaj sljedeći odjeljak ovdje, DESNO DESNO, IZGRADITE TRI STATIČKA POPISA SVIH SLIKARIH STAVKI. Pogledajte izlaz 2 da biste vidjeli zašto se ta ludost morala dogoditi. Dakle, shvatit ću gdje je sljedeći dostupni stupac, nekako prijeđite preko 2 iz posljednjeg stupca, sjetite se toga, da bih kasnije mogao izbrisati stvari, a zatim, za svaku stavku rezanja SI, U SC1.SLICERITEMS, napisat ćemo taj rezarski natpis u proračunsku tablicu. Kad završimo sa svim tim stavkama za rezanje, shvatite koliko smo redova imali danas, a zatim tom rasponu dajte naziv SLICERITEMS1. Ponovit ćemo cijelu tu stvar za predmemoriju rezača 2, prelazeći preko 1 stupca, SLICERITEMS2 i SLICERITEMS3.
Dopustite mi da vam pokažem kako to izgleda u ovom trenutku. Dakle, stavit ću točku prijelaza ovdje i pokrenut ćemo ovaj kod. U redu. To je bilo brzo. Prijeći ćemo na VBA, a daleko ovdje s desne strane dobit ću 3 nova popisa. Ovi su popisi sve što je u rezaču, a vidite da se zove SLICERITEMS1, SLICERITEMS2 i SLICERITEMS3, u redu? Riješit ćemo se toga na kraju, ali to nam daje nešto za prolaz. Povratak na VBA.
U redu. Provući ćemo petlju kroz sve stavke u SLICERITEMS1, očistiti filtar za predmemoriju rezača 1, a zatim ćemo proći, jednu po jednu, kroz svaku stavku rezača i provjeriti je li ova stavka rezača = tome CELL1.VALUE i opet ponavljamo svaku od vrijednosti. Dakle, prvi put to će biti ANDY, a zatim BETTY i, znate, i tako dalje.
Frustrirajuće je. Nisam uspio pronaći način da odjednom isključim sve rezače. Čak sam pokušao snimiti kod i odabrati jedan rezač, a snimljeni kod vraćao je 9 rezača i uključivao jedan rezač, u redu? Toliko frustrirajuće da nisam mogao pronaći ništa bolje od toga, ali nisam mogao pronaći ništa bolje od toga.
Dakle, prvi rezač = postavili smo na ANDY. Zatim prolazimo, a za drugi rezač postavit ćemo = na prvu stavku. Za treći rezač postavite = na prvu stavku.
U redu. Zatim, ovdje dolje, ODLUČITE AKO JE TO VELIKA KOMBINA. Moram ti objasniti zašto je to važno. Ako mi, kao ljudi ovo radimo, ANDY, ne bismo odabrali A52 jer je očito zasivljen, ali makronaredba će biti preglupa i odabrat će A52, a zatim 104 i stvorit će ovo prazno stožerna tablica. Dakle, ovdje postoji tisuću mogućih kombinacija. Znam da postoji samo 400 mogućih izvještaja. To mi je osoba rekla, pa ćemo 600 puta doći do mjesta gdje ćemo stvoriti PDF ovog (ružnog - 04:45) izvještaja.
Dakle, ono što ću učiniti je da pogledam ovdje na kartici ANALIZA - zvala se OPCIJE 2010. - i vidim kako se zove ova zaokretna tablica, a želim vidjeti i koliko redaka dobivamo. U mom slučaju, ako dobijem 2 retka, znam da je to izvješće koje ne želim izvesti. Ako dobijem više od 2 retka, 3, 4, 5, 6, onda znam da je to izvješće koje želim izvesti. Morat ćete shvatiti u svojoj situaciji koja je to.
U redu. Dakle, zato provjeravamo je li zaokretna tablica 2 i, to je ime koje je bilo tamo u vrpci, .TABLERANGE2.ROWS.COUNT> 2. Ako nije> 2, ne želimo stvoriti PDF, u redu? Dakle, ova izjava IF do ove END IF kaže da ćemo stvoriti PDF-ove samo za kombinacije izvješća koje imaju vrijednosti. MYFILENAME, stvorio sam mapu pod nazivom C: REPORTS. To je samo prazna mapa. C: IZVJEŠĆA. Obavezno imate mapu i koristite isto ime mape u makronaredbi. C: IZVJEŠĆA / i naziv datoteke bit će REPORT001.PDF. Sada, brojač koji smo inicijalizirali natrag postoji 1 pomoću FORMAT-a, što je u Excelu ekvivalentno izgovaranju teksta brojača i 000. Na taj ću način dobiti 001, zatim 002, pa 003, pa 004. Oni sortirat ću ispravno.Ako sam upravo nazvao ovo IZVJEŠĆE1, a zatim kasnije imam IZVJEŠĆE10 i 11, a kasnije i IZVJEŠĆE100, to će se sve sortirati kad ne pripadaju zajedno, u redu? Dakle, kreirajući ime datoteke u slučaju da datoteka postoji od zadnjeg pokretanja, ubit ćemo je. Drugim riječima, izbrišite ga. Naravno, ako pokušate ubiti datoteku koja nije tamo, izbacit će pogrešku. Dakle, ako u sljedećem retku dobijemo pogrešku, to je u redu. Samo nastavite, ali onda resetiram provjeru pogreške ON ERROR GOTO 0.Naravno, ako pokušate ubiti datoteku koja nije tamo, izbacit će pogrešku. Dakle, ako u sljedećem retku dobijemo pogrešku, to je u redu. Samo nastavite, ali onda resetiram provjeru pogreške ON ERROR GOTO 0.Naravno, ako pokušate ubiti datoteku koja nije tamo, izbacit će pogrešku. Dakle, ako u sljedećem retku dobijemo pogrešku, to je u redu. Samo nastavite, ali onda resetiram provjeru pogreške ON ERROR GOTO 0.
Here’s the ACTIVE SHEET, EXPORT AS FIXED FORMAT, as a PDF, there's the file name, all those choices, and then I increment the counter, so that way, next time we find one that has records, we will be creating REPORT002.PDF. Finish those three loops and then CLEAR OUT THE STATIC LISTS. So, I'll remember which column we were, resize 1 row, 3 columns, ENTIRECOLUMN.CLEAR, and then a nice little message box there to show that things have been created. Okay. Let's run it.
U redu. Sad, ono što bi se ovdje trebalo dogoditi je ako odemo pogledati Windows Explorer, eto ga. U redu. Stvara… kao, svake sekunde dobivamo 2 ili 3 ili 4 ili više. Pauzirat ću ovo i pustiti da se pokrene. U redu. Evo nas. Stvoreno je 326 izvještaja. Prolistao je svih 1000 mogućnosti i zadržao samo one gdje postoji stvarni rezultat. U redu, od 9:38 do 9:42, 4 minute da sve to napravim, ali još brže nego 400, u redu?
Alright. So, that's the macro way to do this. The other thing that struck me here that it may or may not work. It's really tough to say. Let's take our data and I'm going to move the data to a brand new workbook. MOVE OR COPY, CREATE A COPY, to a NEW BOOK, click OK, and we’re going to use a trick here that I first learned from Szilvia Juhasz - a great Excel consultant out in Southern California -- and we're going to add a KEY field here. The KEY field is = REVIEWER & ANTENNA & DISCIPLINE. We'll copy that down and we'll insert a new pivot table. Click OK, and we're going to take that field, the KEY field, and move it up to the old-fashioned FILTERS, and then let's see. (Let's dispel a little report here with - 08:30) REVIEWER, ANTENNA, DISCIPLINE, and REVENUE, like that.
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.
I treći izlaz, u redu? Ovo je ludo. Ako želim snimiti makronaredbu, ako želim (napišem makronaredbu - 13:35) odabrati samo jednu stavku, shvatite kako to učiniti pomoću RAZVOJITELJA, SNIMI MAKRO, HOWTOCHOOSEONEITEMFROMSLICER, kliknite U redu i mi jednostavno odaberemo jedan artikal. FLO. Kliknite STOP SNIMANJU, a zatim idemo ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER, UREĐUJEMO i, zasigurno, čine FLO TRUE, a zatim svi ostali FLASE. Znači da bih imao rezač sa 100 predmeta, morali bi tamo staviti 100 redaka koda da ponište odabir svega ostalog. Čini se nevjerojatno neučinkovitim, ali tu ste.
Preuzmi datoteku
Preuzmite datoteku uzorka ovdje: Podcast2106.xlsx
