Vanlige pivottabelfiltre tilbyr sidene Vis alle rapportfilter, men Slicers støtter ikke denne funksjonaliteten. I dag, noen VBA å løpe gjennom alle mulige slicer kombinasjoner.
Se på video
Videoutskrift
Lær Excel fra, Podcast-episode 2106: Lag en PDF av hver kombinasjon av 3 skiver.
For et flott spørsmål vi har i dag. Noen skrev inn, ville vite om det var mulig. Akkurat nå har de 3 snitt som kjører et pivottabell. Jeg vet ikke hvordan svingbordet ser ut. Det er konfidensielt. Jeg har ikke lov til å se det, så jeg gjetter bare, ikke sant? Så det de gjør er at de velger ett element fra hver skiver og deretter lager en PDF, og deretter går og velger neste element og lager en PDF, og deretter neste element og neste element, og du kan forestill deg, med 400 kombinasjoner av snitt, kan dette ta evig tid, og de sa, er det en måte å få et program til å gå gjennom og løpe gjennom alle alternativene?
Jeg sa, ok, her er noen kvalifiserende spørsmål. Nummer én, vi er ikke på Mac, ikke sant? Ikke Android, ikke Excel for iPhone. Dette er Excel for Windows. Ja, sa de. Flott. Jeg sa, det andre veldig viktige spørsmålet er at vi ønsker å velge ett element fra en skiver, og deretter til slutt det andre fra skiver, og deretter det andre fra skiver. Vi trenger ikke kombinasjoner som ANDY, og deretter ANDY og BETTY, og deretter ANDY og CHARLIE, ikke sant? Det er ute. Jeg skal bare gjøre ett element fra hver skiver. Ja, ja, ja. Det er slik det kommer til å gå. Perfekt, sa jeg. Så her, fortell meg dette, velg hver skiver, gå til SKIVERVERKTØY, ALTERNATIVER, og gå til SKIVERINNSTILLINGER. Vi gjorde akkurat dette for 2 episoder siden. Er ikke dette gal? NAVN Å BRUKE I FORMULER, og jeg vet at det er SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,ok? Så jeg tror jeg har fått det.
Nå skal vi bytte til VBA her, og forresten, sørg for at du er lagret som xlsm og sørg for at makrosikkerheten din er satt til å tillate makroer. Hvis det er lagret som xlsx, stol på meg, må du gjøre en fil, lagre som, du kommer til å miste alt arbeidet ditt hvis du lar det være som xlsx. Ja, 99,9% av regnearkene du bruker er xlsx, men denne med makro fungerer ikke. ALT + F11. OK, så her er koden.
Vi skal finne tre skivecacher, en skiveartikkel og 3 serier. For hver av skivecachene, skal vi sette den til navnet som ble brukt i formelen som jeg nettopp viste deg i dialogboksen SLISTERINNSTILLINGER. Så vi har de tre. Jeg vil fjerne alle dem for å forsikre oss om at vi er tilbake til alt som blir valgt. Denne telleren skal brukes i filnavnet senere.
Ok. Nå, dette neste avsnittet her, BYGG TIL HØYRE, TRE STATISKE LISTER AV ALLE SLICER-VARER. Se uttak # 2 for å se hvorfor denne galskapen måtte skje. Så jeg skal finne ut hvor den neste tilgjengelige kolonnen er, litt over 2 fra den siste kolonnen, husk det slik at jeg kan slette ting senere, og deretter, for hver SI, skiver, IN SC1.SLICERITEMS, vi skal skrive den skiverteksten til regnearket. Når vi er ferdige med alle disse skiveartiklene, kan du finne ut hvor mange rader vi hadde i dag, og deretter kalle det området som SLICERITEMS1. Vi kommer til å gjenta det hele for slicer cache 2, går over 1 kolonne, SLICERITEMS2 og SLICERITEMS3.
La meg vise deg hvordan det ser ut på dette tidspunktet. Så jeg legger et bruddpunkt her, og vi kjører denne koden. Ok. Det var raskt. Vi skal bytte over til VBA, og langt borte her til høyre vil jeg få 3 nye lister. Disse listene er alt som er i skiveren, og ser du at den heter SLICERITEMS1, SLICERITEMS2 og SLICERITEMS3, ok? Vi blir kvitt det på slutten, men det gir oss noe å løpe gjennom. Tilbake til VBA.
Ok. Vi kommer til å gå gjennom alle elementene i SLICERITEMS1, rydde filteret for snittbuffer 1, og så skal vi gå gjennom, en om gangen, gjennom hvert skiverobjekt og se om dette skiverobjektet er = til dette CELL1.VALUE, og igjen går vi gjennom hver av verdiene. Så, første gang, blir det ANDY og deretter BETTY, og du vet, og så videre.
Det er frustrerende. Jeg kunne ikke finne noen måte å slå av alle snittene på en gang. Jeg prøvde til og med å registrere koden og velge en skiver, og den innspilte koden ga 9 skiver av og slå på den ene skiveren, ok? Så frustrerende at jeg ikke kunne finne noe bedre enn det, men jeg kunne ikke finne noe bedre enn det.
Så vi satte den første skiveren = til ANDY. Så går vi igjennom, og for den andre skiveren skal vi sette den = til det første elementet. For den tredje skiveren, sett den = til det første elementet.
Ok. Så, her nede, BESTEM HVIS DETTE ER Gyldig KOMBINASJON. Jeg må forklare deg hvorfor det er viktig. Hvis vi, som mennesker vi gjør dette, ANDY, ville vi ikke velge A52 fordi det tydelig er gråtonet, men makroen kommer til å bli for dum, og den vil velge A52 og deretter 104, og den vil skape dette tomme dreiebord. Så det er tusen mulige kombinasjoner her. Jeg vet at det bare er 400 mulige rapporter. Det er det personen fortalte meg, og så kommer vi til å få 600 ganger der vi skal lage en PDF av denne (stygg - 04:45) rapporten.
Så, det jeg skal gjøre er at jeg kommer til å se her i ANALYSE-fanen - den het OPTIONS i 2010 - og se hva navnet på denne pivottabellen er, og jeg vil se hvor mange rader vi får. I mitt tilfelle, hvis jeg får to rader, vet jeg at det er en rapport jeg ikke vil eksportere. Hvis jeg får mer enn 2 rader, 3, 4, 5, 6, så vet jeg at det er en rapport jeg vil eksportere. Du må finne ut av situasjonen din hvilken situasjon det er.
Ok. Så det er derfor vi sjekker for å se om pivottabellen 2 og, det er navnet som var der bak i båndet .TABLERANGE2.ROWS.COUNT er> 2. Hvis det ikke er> 2, vil vi ikke lage en PDF, ok? Så denne IF-setningen ned til denne END IF sier at vi bare skal lage PDF-filer for rapportkombinasjonene som har verdier. MYFILENAME, jeg opprettet en mappe som heter C: RAPPORTER. Det er bare en tom mappe. C: RAPPORTER. Du sørger for at du har en mappe og bruker samme mappenavn i makroen. C: RAPPORTER / og navnet på filen blir REPORT001.PDF. Nå, teller vi initialiserte tilbake, er det 1 ved hjelp av FORMAT, som i Excel tilsvarer å si teksten til telleren og 000. På den måten skal jeg få 001, så 002, deretter 003 og deretter 004. De kommer til å sortere riktig.Hvis jeg nettopp hadde kalt denne RAPPORT1, og senere har jeg RAPPORT10 og 11, og senere på RAPPORT100, kommer de alle til å sortere sammen når de ikke hører sammen, ok? Så når vi oppretter navnet på filen i tilfelle filen eksisterer fra forrige gang vi kjørte denne, skal vi drepe den. Med andre ord, slett den. Selvfølgelig, hvis du prøver å drepe en fil som ikke er der, vil de kaste en feil. Så hvis vi får en feil i neste linje, er det greit. Bare fortsett, men så tilbakestiller jeg feilkontrollen PÅ FEIL GOTO 0.Selvfølgelig, hvis du prøver å drepe en fil som ikke er der, vil de kaste en feil. Så hvis vi får en feil i neste linje, er det greit. Bare fortsett, men så tilbakestiller jeg feilkontrollen PÅ FEIL GOTO 0.Selvfølgelig, hvis du prøver å drepe en fil som ikke er der, vil de kaste en feil. Så hvis vi får en feil i neste linje, er det greit. Bare fortsett, men så tilbakestiller jeg feilkontrollen PÅ FEIL GOTO 0.
Her er det AKTIVE ARKET, EKSPORT SOM FAST FORMAT, som en PDF, det er filnavnet, alle disse valgene, og så øker jeg telleren, slik at neste gang vi finner en som har poster, lager vi REPORT002.PDF . Fullfør de tre løkkene, og Tøm deretter de statiske listene. Så jeg vil huske hvilken kolonne vi var, endre størrelse på en rad, 3 kolonner, HELE KOLONN.KLAR, og så en fin liten meldingsboks der for å vise at ting er opprettet. Greit. La oss kjøre det.
Ok. Nå, hva som skal skje her er hvis vi går og ser i Windows Utforsker, der er det. Greit. Det skaper … som hvert sekund, vi får 2 eller 3 eller 4 eller mer. Jeg stopper dette og lar det gå. Ok. Der er vi. 326 rapporter er opprettet. Den gikk gjennom alle 1000 muligheter og holdt bare de der det var et faktisk resultat. Greit, fra 9:38 til 9:42, 4 minutter å gjøre alt det, men fortsatt raskere enn å gjøre 400, ok?
Ok. Så det er den makro måten å gjøre dette på. Den andre tingen som slo meg her at det kan fungere eller ikke. Det er veldig tøft å si. La oss ta dataene våre, og jeg skal flytte dataene til en helt ny arbeidsbok. FLYTTE ELLER KOPIERE, OPPRETT ET KOPI, til en NY BOK, klikk OK, så skal vi bruke et triks her som jeg først lærte fra Szilvia Juhasz - en flott Excel-konsulent i Sør-California - og vi skal legg til et Nøkkelfelt her. Nøkkelfeltet er = ANMELDELSE & ANTENNE & DISSIPLIN. Vi kopierer det ned og setter inn en ny pivottabell. Klikk på OK, så tar vi det feltet, Nøkkelfeltet, og flytter det opp til de gammeldagse FILTRE, og så får vi se. (La oss fjerne en liten rapport her med - 08:30) ANMELDELSE, ANTENNE, DISSIPLIN og INNTEKTER, slik.
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.
Og det tredje uttaket, ok? Dette er den som er gal. Hvis jeg vil spille inn en makro, hvis jeg vil (skrive en makro - 13:35) for å velge bare ett element, finn ut hvordan du gjør det ved å bruke DEVELOPER, RECORD MACRO, HOWTOCHOOSEONEITEMFROMSLICER, klikk OK, og vi velger bare en punkt. FLO. Klikk STOPP OPPTAK, så går vi ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER, REDIGER det, og sikkert gjør de FLO TRUE og deretter alle andre FLASE. Det betyr at hvis jeg hadde en skiver med 100 gjenstander i, måtte de legge 100 linjer med kode der for å fjerne merket for alt annet. Virker utrolig ineffektiv, men der er du.
Last ned fil
Last ned eksempelfilen her: Podcast2106.xlsx
