Regelmæssige pivottabelfiltre tilbyder siderne Vis alle rapportfilter, men Slicers understøtter ikke denne funktionalitet. I dag løber nogle VBA gennem alle mulige slicer-kombinationer.
Se video
Videoudskrift
Lær Excel fra, Podcast-afsnit 2106: Opret en PDF af hver kombination af 3 udskærere.
Hvilket stort spørgsmål har vi i dag. Nogen skrev ind, ville vide, om det var muligt. Lige nu har de 3 snittere, der kører et drejetabel. Jeg ved ikke, hvordan drejetabellen ser ud. Det er fortroligt. Jeg har ikke lov til at se det, så jeg gætter bare, ikke? Så hvad de laver, er at de vælger et emne fra hver udskæring og derefter opretter en PDF og derefter går og vælger det næste emne og opretter en PDF og derefter det næste emne og det næste emne, og du kan forestil dig, med 400 kombinationer af skiver, kan dette tage for evigt, og de sagde, er der en eller anden måde at få et program igennem og løbe igennem alle muligheder?
Jeg sagde okay, her er nogle kvalificerende spørgsmål. Nummer et, vi er ikke på en Mac, ikke? Ikke Android, ikke Excel til iPhone. Dette er Excel til Windows. Ja, sagde de. Store. Jeg sagde, det andet meget vigtige spørgsmål er, at vi vil vælge et emne fra en udskæring, og derefter til sidst det andet emne fra udskæreren og derefter det andet emne fra udskæreren. Vi har ikke brug for kombinationer som ANDY og derefter ANDY og BETTY og derefter ANDY og CHARLIE, ikke? Det er ude. Jeg skal bare lave et emne fra hver udskæring. Ja ja Ja. Sådan vil det gå. Perfekt, sagde jeg. Så her, fortæl mig dette, vælg hver skiver, gå til SKÆRERVÆRKTØJER, INDSTILLINGER, og gå til SKÆRERINDSTILLINGER. Vi har lige gjort dette for 2 episoder siden. Er det ikke vanvittigt? NAVN, DER SKAL ANVENDES I FORMULER, og jeg ved, at det er SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,i orden? Så jeg tror, jeg har det.
Nu skal vi skifte til VBA her og forresten sørge for at du er gemt som xlsm og sørg for at din makrosikkerhed er indstillet til at tillade makroer. Hvis det er gemt som xlsx, stol på mig, skal du lave en FIL, GEM SOM, du mister alt dit arbejde, hvis du lader det være som xlsx. Ja, 99,9% af de regneark, du bruger, er xlsx, men denne med en makro fungerer ikke. ALT + F11. Okay, så her er koden.
Vi finder tre skivecacher, en skiveartikel og 3 intervaller. For hver af skivecacherne vil vi indstille det til det navn, der blev brugt i formlen, som jeg lige viste dig i dialogboksen SLICER SETTINGS. Så vi har de tre af dem. Jeg vil rydde alle dem for at sikre, at vi er tilbage til alt, hvad der vælges. Denne tæller bruges senere i filnavnet.
I orden. Nu, dette næste afsnit her, FRA TIL HØJRE, BYG TRE STATISKE LISTER AF ALLE SKÆREPUNKTER. Se udtag nr. 2 for at se, hvorfor denne skørhed måtte ske. Så jeg skal finde ud af, hvor den næste tilgængelige kolonne er, slags gå over 2 fra den sidste kolonne, husk det, så jeg kan slette tingene senere, og derefter for hver SI, skiver, IN SC1.SLICERITEMS, vi skriver den skivere billedtekst til regnearket. Når vi er færdige med alle disse skiveartikler, skal du finde ud af, hvor mange rækker vi har i dag, og derefter navngive området som SLICERITEMS1. Vi gentager det hele for slicer cache 2, går over 1 kolonne, SLICERITEMS2 og SLICERITEMS3.
Lad mig vise dig, hvordan det ser ud på dette tidspunkt. Så jeg sætter et brudpunkt lige her, og vi kører denne kode. I orden. Det var hurtigt. Vi skifter til VBA, og langt herfra til højre får jeg 3 nye lister. Disse lister er alt, hvad der er i udskæreren, og ser du, at det hedder SLICERITEMS1, SLICERITEMS2 og SLICERITEMS3, okay? Vi slipper af med det i slutningen, men det giver os noget at løbe igennem. Tilbage til VBA.
I orden. Vi går igennem alle elementerne i SLICERITEMS1, rydder filteret til skærebuffer 1, og så går vi igennem, en ad gangen, gennem hvert skiverobjekt og se om dette skiverobjekt er = til dette CELL1.VALUE, og igen løber vi gennem hver af værdierne. Så første gang igennem bliver det ANDY og derefter BETTY, og du ved osv.
Det er frustrerende. Jeg kunne ikke finde nogen måde at slukke for alle skiverne på én gang. Jeg prøvede endda at optage koden og vælge en skæreudstyr, og den indspillede kode returnerede 9 skiver og tændte den ene skiver, okay? Så frustrerende, at jeg ikke kunne finde noget bedre end det, men jeg kunne ikke finde noget bedre end det.
Så vi indstiller den første skiver = til ANDY. Derefter går vi igennem, og for den anden skiver vil vi indstille det = til det første element. For den tredje udskæring skal du indstille det = til det første element.
I orden. Derefter beslutter du her nede, om dette er en gyldig kombination. Jeg må forklare dig, hvorfor det er vigtigt. Hvis vi, som mennesker, gør dette, ANDY, ville vi ikke vælge A52, fordi det tydeligvis er nedtonet, men makroen bliver for dum, og den vælger A52 og derefter 104, og den vil skabe dette tomme drejebord. Så der er tusind mulige kombinationer her. Jeg ved, at der kun er 400 mulige rapporter. Det er det, personen fortalte mig, og så kommer vi til 600 gange, hvor vi skal oprette en PDF af denne (grimme - 04:45) rapport.
Så hvad jeg skal gøre er at jeg vil se her på fanen ANALYSER - den blev kaldt OPTIONS i 2010 - og se, hvad navnet på denne drejetabel er, og jeg vil se, hvor mange rækker vi får. I mit tilfælde, hvis jeg får 2 rækker, ved jeg, at det er en rapport, jeg ikke vil eksportere. Hvis jeg får mere end 2 rækker, 3, 4, 5, 6, så ved jeg, at det er en rapport, som jeg ønsker at eksportere. Du bliver nødt til at finde ud af i din situation, hvilken situation det er.
I orden. Så det er derfor, vi kontrollerer for at se, om pivottabellen 2 og, det er det navn, der var derude i båndet .TABLERANGE2.ROWS.COUNT er> 2. Hvis det ikke er> 2, vil vi ikke oprette en PDF, okay? Så denne IF-erklæring ned til denne END IF siger, at vi kun opretter PDF-filer til de rapportkombinationer, der har værdier. MYFILENAME, jeg oprettede en mappe kaldet C: RAPPORTER. Det er bare en tom mappe. C: RAPPORTER. Du sørger for, at du har en mappe og bruger det samme mappenavn i makroen. C: RAPPORTER / og navnet på filen bliver REPORT001.PDF. Nu, tæller, vi initialiserede tilbage, er der 1 ved hjælp af FORMAT, hvilket svarer i Excel til at sige tællerens tekst og 000. På den måde får jeg 001, så 002, derefter 003 og derefter 004. De vi skal sortere korrekt.Hvis jeg lige havde kaldt denne BETÆNKNING1, og senere har jeg BETÆNKNING10 og 11 og senere BETÆNKELSE100, vil de alle sortere sammen, når de ikke hører sammen, okay? Så ved at oprette navnet på filen, hvis filen eksisterer fra sidste gang, vi kørte det, dræber vi det. Slet det med andre ord. Selvfølgelig, hvis du prøver at dræbe en fil, der ikke er der, vil de kaste en fejl. Så hvis vi får en fejl i næste linje, er det fint. Bare fortsæt, men så nulstiller jeg fejlkontrollen PÅ FEJL GOTO 0.Selvfølgelig, hvis du prøver at dræbe en fil, der ikke er der, kaster de en fejl. Så hvis vi får en fejl i næste linje, er det fint. Bare fortsæt, men så nulstiller jeg fejlkontrollen PÅ FEJL GOTO 0.Selvfølgelig, hvis du prøver at dræbe en fil, der ikke er der, kaster de en fejl. Så hvis vi får en fejl i næste linje, er det fint. Bare fortsæt, men så nulstiller jeg fejlkontrollen PÅ FEJL GOTO 0.
Her er det AKTIVE ARK, EKSPORT SOM FAST FORMAT, som en PDF, der er filnavnet, alle disse valg, og så øger jeg tælleren, så den måde, næste gang vi finder en, der har poster, opretter vi REPORT002.PDF . Afslut disse tre sløjfer, og RYD derefter de STATISKE LISTER. Så jeg kan huske, hvilken kolonne vi var, ændre størrelse på 1 række, 3 kolonner, ENTIRECOLUMN.CLEAR, og derefter en dejlig lille meddelelsesboks der for at vise, at ting er blevet oprettet. Okay. Lad os køre det.
I orden. Hvad der skal ske her er nu, hvis vi går og kigger i Windows Stifinder, der er det. Okay. Det skaber … ligesom hvert sekund får vi 2 eller 3 eller 4 eller mere. Jeg sætter dette på pause og lader det køre. I orden. Der er vi. 326 rapporter er oprettet. Det løb gennem alle 1000 muligheder og holdt kun dem, hvor der var et faktisk resultat. Okay, fra 9:38 til 9:42, 4 minutter til at gøre alt det, men stadig hurtigere end at gøre 400, okay?
I orden. Så det er den makro måde at gøre dette på. Den anden ting, der slog mig her, at det måske eller måske ikke fungerer. Det er virkelig svært at sige. Lad os tage vores data, og jeg vil flytte dataene til en helt ny projektmappe. FLYT ELLER KOPIER, OPRET EN KOPI, til en NY BOG, klik på OK, så skal vi bruge et trick her, som jeg først lærte fra Szilvia Juhasz - en stor Excel-konsulent i det sydlige Californien - og vi skal tilføj et KEY-felt her. KEY-feltet er = REVIEWER & ANTENNA & DISCIPLINE. Vi kopierer det ned, og vi indsætter en ny pivottabel. Klik på OK, og vi tager feltet, KEY-feltet, og flytter det op til de gammeldags FILTER, og lad os så se. (Lad os fjerne en lille rapport her med - 08:30) ANMELDELSE, ANTENNE, DISCIPLINER og INDTÆGTER, sådan.
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 udtag, okay? Dette er den der er skør. Hvis jeg vil optage en makro, hvis jeg vil (skriv en makro - 13:35) for kun at vælge et element, skal du finde ud af, hvordan du gør det ved hjælp af DEVELOPER, RECORD MACRO, HOWTOCHOOSEONEITEMFROMSLICER, klik på OK, og vi vælger simpelthen en vare. FLO. Klik på STOP OPTAGELSE, så går vi ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER, REDIGER det, og helt sikkert gør de FLO TRUE og derefter alle andre FLASE. Det betyder, at hvis jeg havde en skiver med 100 genstande i, ville de være nødt til at lægge 100 linjer kode derinde for at fravælge alt andet. Virker utrolig ineffektiv, men der er du.
Download fil
Download eksempelfilen her: Podcast2106.xlsx
