Normale draaitabelfilters bieden de pagina's Alle rapportfilter weergeven, maar Slicers ondersteunen deze functionaliteit niet. Tegenwoordig is er wat VBA om door alle mogelijke slicercombinaties te lopen.
Bekijk video
Videotranscriptie
Leer Excel van, Podcast Episode 2106: maak een PDF van elke combinatie van 3 Slicers.
Wat een geweldige vraag hebben we vandaag. Iemand schreef in, wilde weten of het mogelijk was. Op dit moment hebben ze 3 snijmachines die een draaitafel draaien. Ik weet niet hoe de draaitabel eruitziet. Het is vertrouwelijk. Ik mag het niet zien, dus ik gok het, toch? Dus wat ze doen is dat ze een item uit elke slicer kiezen en dan een pdf maken, en dan het volgende item kiezen en een pdf maken, en dan het volgende item, en het volgende item, en je kunt stel je voor dat dit met 400 combinaties van slicers een eeuwigheid zou kunnen duren, en ze zeiden: is er een manier om een programma te laten doorlopen en alle opties te doorlopen?
Ik zei: oké, hier zijn enkele kwalificerende vragen. Nummer één, we gebruiken toch geen Mac? Geen Android, geen Excel voor de iPhone. Dit is Excel voor Windows. Ja, zeiden ze. Super goed. Ik zei, de tweede echt belangrijke vraag is: we willen het ene item uit een snijmachine kiezen en dan uiteindelijk het andere item uit de snijmachine en dan het andere item uit de snijmachine. We hebben geen combinaties nodig zoals ANDY, en dan ANDY en BETTY, en dan ANDY en CHARLIE, toch? Dat is uit. Ik ga gewoon één item uit elke snijmachine maken. Ja ja ja. Dat is de manier waarop het zal gaan. Perfect, zei ik. Dus hier, vertel me dit, kies elke slicer, ga naar de SLICER TOOLS, OPTIONS, en ga naar SLICER SETTINGS. We hebben dit net 2 afleveringen geleden gedaan. Is dit niet gek? NAAM TE GEBRUIKEN IN FORMULES en ik weet dat het SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,oké? Dus ik denk dat ik het heb.
Nu gaan we hier overschakelen naar VBA en zorgen we er trouwens voor dat u wordt opgeslagen als xlsm en dat uw macrobeveiliging is ingesteld om macro's toe te staan. Als het is opgeslagen als xlsx, geloof me, je moet een FILE gaan doen, SAVE AS, je gaat al je werk verliezen als je het als xlsx laat staan. Ja, 99,9% van de spreadsheets die u gebruikt, zijn xlsx, maar deze met een macro zal niet werken. ALT + F11. Oké, dus hier is de code.
We gaan drie caches voor slicers, één slicer-item en drie reeksen vinden. Voor elk van de slicercaches gaan we het instellen op de naam die wordt gebruikt in de formule die ik je zojuist heb laten zien in het dialoogvenster SLICER-INSTELLINGEN. Dus we hebben er drie. Ik wil ze allemaal wissen om er zeker van te zijn dat alles weer is geselecteerd. Deze teller wordt later in de bestandsnaam gebruikt.
Oké. Nu, dit volgende gedeelte hier, NAAR RECHTS, BOUW DRIE STATISCHE LIJSTEN VAN ALLE SLICER-ITEMS. Zie outtake # 2 om te zien waarom deze gekheid moest gebeuren. Dus ik ga uitzoeken waar de volgende beschikbare kolom is, ga ongeveer 2 van de laatste kolom over, onthoud dat zodat ik de dingen later kan verwijderen, en dan, voor elk SI, slicer-item, IN SC1.SLICERITEMS, we gaan dat bijschrift van de slicer naar de spreadsheet schrijven. Als we klaar zijn met al die slicer-items, zoek dan uit hoeveel rijen we vandaag hadden en noem dat bereik dan SLICERITEMS1. We gaan dat hele ding herhalen voor slicercache 2, waarbij we over 1 kolom gaan, SLICERITEMS2 en SLICERITEMS3.
Ik zal je laten zien hoe het er op dit punt uitziet. Dus ik zal hier een breekpunt plaatsen en we zullen deze code uitvoeren. Oké. Dat was snel. We gaan overschakelen naar VBA, en ver weg hier, aan de rechterkant, krijg ik 3 nieuwe lijsten. Deze lijsten zijn alles wat in de slicer zit, en je ziet dat het SLICERITEMS1, SLICERITEMS2 en SLICERITEMS3 heet, oké? Daar komen we aan het einde van af, maar dat geeft ons iets om doorheen te gaan. Terug naar VBA.
Oké. We gaan door alle items in SLICERITEMS1, wissen het filter voor slicercache 1, en dan gaan we één voor één door elk slicer-item om te zien of dit slicer-item = voor dit is CEL1.VALUE, en nogmaals, we doorlopen elk van de waarden. Dus de eerste keer wordt het ANDY en dan BETTY en, weet je, enzovoort.
Het is frustrerend. Ik kon geen manier vinden om alle snijmachines tegelijk uit te schakelen. Ik heb zelfs geprobeerd de code op te nemen en een slicer te kiezen, en de opgenomen code retourneerde 9 slicers uit en zette de ene slicer aan, oké? Zo frustrerend dat ik niets beters kon vinden dan dat, maar ik kon niets beters vinden dan dat.
Dus stellen we de eerste slicer = in op ANDY. Dan gaan we door, en voor de tweede slicer gaan we het = instellen op het eerste item. Stel voor de derde slicer = in op het eerste item.
Oké. Beslis dan hier beneden OF DIT EEN GELDIGE COMBINATIE IS. Ik moet je uitleggen waarom dat belangrijk is. Als wij, als mensen, dit doen, ANDY, zouden we A52 niet kiezen omdat het duidelijk grijs is, maar de macro zal te dom zijn en het zal A52 en dan 104 kiezen, en het zal dit lege draaitabel. Er zijn hier dus duizend mogelijke combinaties. Ik weet dat er maar 400 mogelijke rapporten zijn. Dat is wat de persoon me vertelde, en dus krijgen we 600 keer waar we een pdf van dit (lelijke - 04:45) rapport gaan maken.
Dus wat ik ga doen, is dat ik hier op het tabblad ANALYSE - het heette OPTIONS in 2010 - ga kijken wat de naam van deze draaitabel is, en ik wil zien hoeveel rijen we krijgen. Als ik in mijn geval twee rijen krijg, weet ik dat het een rapport is dat ik niet wil exporteren. Als ik meer dan 2 rijen krijg, 3, 4, 5, 6, dan weet ik dat het een rapport is dat ik wil exporteren. U zult in uw situatie moeten uitzoeken wat het is.
Oké. Daarom controleren we of de draaitabel 2 en, dat is de naam die daar achter in het lint stond, .TABLERANGE2.ROWS.COUNT is> 2. Als het niet> 2 is, willen we dat niet maak een pdf, oké? Dus deze IF-instructie tot aan deze END IF zegt dat we alleen de PDF's gaan maken voor de rapportcombinaties die waarden hebben. MYFILENAME, ik heb een map gemaakt met de naam C: REPORTS. Het is gewoon een lege map. C: RAPPORTEN. Je zorgt ervoor dat je een map hebt en gebruikt dezelfde mapnaam in de macro. C: REPORTS / en de naam van het bestand wordt REPORT001.PDF. Nu, teller die we terug hebben geïnitialiseerd, is er 1 met FORMAT, wat in Excel gelijk is aan het zeggen van de tekst van teller en 000. Op die manier krijg ik 001, dan 002, dan 003 en dan 004. Ze gaan correct sorteren.Als ik dit REPORT1 zojuist had gebeld, en later heb ik een REPORT10 en 11, en later REPORT100, die zullen allemaal samen worden gesorteerd als ze niet bij elkaar horen, oké? Dus als we de naam van het bestand maken voor het geval het bestand bestaat uit de laatste keer dat we dit hebben uitgevoerd, gaan we het doden. Met andere woorden, verwijder het. Als je een bestand probeert te doden dat er niet is, wordt er natuurlijk een fout gegenereerd. Dus als we een foutmelding krijgen in de volgende regel, is dat prima. Ga gewoon door, maar dan reset ik de foutcontrole ON ERROR GOTO 0.Als je een bestand probeert te doden dat er niet is, wordt er natuurlijk een fout gegenereerd. Dus als we een foutmelding krijgen in de volgende regel, is dat prima. Ga gewoon door, maar dan reset ik de foutcontrole ON ERROR GOTO 0.Als je een bestand probeert te doden dat er niet is, wordt er natuurlijk een fout gegenereerd. Dus als we een foutmelding krijgen in de volgende regel, is dat prima. Ga gewoon door, maar dan reset ik de foutcontrole ON ERROR GOTO 0.
Hier is het ACTIEVE BLAD, EXPORTEER ALS VAST FORMAAT, als pdf, daar is de bestandsnaam, al die keuzes, en dan verhoog ik de teller, zodat we de volgende keer dat we er een vinden met records, REPORT002.PDF aanmaken . Beëindig die drie lussen en WIS DE STATISCHE LIJSTEN UIT. Dus ik zal onthouden welke kolom we waren, het formaat van 1 rij, 3 kolommen, ENTIRECOLUMN.CLEAR wijzigen en dan een leuk klein berichtvenster om te laten zien dat er dingen zijn gemaakt. Oke. Laten we het doen.
Oké. Wat hier zou moeten gebeuren, is dat als we gaan kijken in Windows Verkenner, daar is het. Oke. Het is aan het creëren … zoals, elke seconde krijgen we er 2 of 3 of 4 of meer. Ik pauzeer dit en laat het lopen. Oké. Daar zijn we. Er zijn 326 rapporten gemaakt. Het doorliep alle 1000 mogelijkheden en bewaarde alleen degene met een daadwerkelijk resultaat. Oké, van 9:38 tot 9:42, 4 minuten om dat allemaal te doen, maar nog steeds sneller dan de 400, oké?
Oké. Dus dat is de macromanier om dit te doen. Het andere dat me hier opviel, is dat het wel of niet werkt. Het is echt moeilijk te zeggen. Laten we onze gegevens nemen en ik ga de gegevens naar een geheel nieuwe werkmap verplaatsen. VERPLAATS OF KOPIE, MAAK EEN KOPIE, naar een NIEUW BOEK, klik op OK en we gaan hier een truc gebruiken die ik voor het eerst leerde van Szilvia Juhasz - een geweldige Excel-adviseur in Zuid-Californië - en we gaan voeg hier een KEY-veld toe. Het veld SLEUTEL is = REVIEWER & ANTENNE & DISCIPLINE. We kopiëren dat naar beneden en we voegen een nieuwe draaitabel in. Klik op OK, en we nemen dat veld, het KEY-veld, en verplaatsen het naar de ouderwetse FILTERS, en laten we dan kijken. (Laten we hier een klein rapport verwijderen met - 08:30) REVIEWER, ANTENNE, DISCIPLINE en OMZET, zoals dat.
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.
En de derde outtake, oké? Dit is degene die gek is. Als ik een macro wil opnemen, als ik wil (een macro schrijven - 13:35) om slechts één item te kiezen, zoek dan uit hoe ik dat moet doen door ONTWIKKELAAR, RECORD MACRO, HOWTOCHOOSEONEITEMFROMSLICER te gebruiken, klik op OK, en we kiezen er gewoon een item. FLO. Klik op OPNEMEN STOPPEN, dan gaan we ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER, EDIT dat, en ja hoor, ze maken FLO WAAR en dan alle anderen FLASE. Het betekent dat als ik een slicer had met 100 items erin, ze 100 regels code erin zouden moeten plaatsen om de selectie ongedaan te maken. Lijkt ongelooflijk inefficiënt, maar daar ben je.
Download bestand
Download het voorbeeldbestand hier: Podcast2106.xlsx
