Regelmäßige Pivot-Tabellenfilter bieten die Seiten Alle Berichtsfilter anzeigen, Slicer unterstützen diese Funktionalität jedoch nicht. Heute durchlaufen einige VBA alle möglichen Slicer-Kombinationen.
Schau Video
Video-Transkript
Lernen Sie Excel aus, Podcast Episode 2106: Erstellen Sie ein PDF von jeder Kombination von 3 Slicern.
Was für eine großartige Frage wir heute haben. Jemand schrieb, wollte wissen, ob es möglich war. Im Moment haben sie 3 Slicer, die einen Pivot-Tisch laufen lassen. Ich weiß nicht, wie der Pivot-Tisch aussieht. Es ist vertraulich. Ich darf es nicht sehen, also rate ich nur, oder? Sie wählen also aus jedem Slicer ein Element aus und erstellen dann eine PDF-Datei. Anschließend wählen sie das nächste Element aus und erstellen eine PDF-Datei. Anschließend können Sie das nächste Element und das nächste Element erstellen Stellen Sie sich vor, bei 400 Slicerkombinationen könnte dies ewig dauern, und sie sagten, gibt es eine Möglichkeit, ein Programm alle Optionen durchlaufen zu lassen?
Ich sagte, in Ordnung, hier sind einige qualifizierende Fragen. Nummer eins, wir sind nicht auf einem Mac, oder? Nicht Android, nicht Excel für das iPhone. Dies ist Excel für Windows. Ja, sagten sie. Groß. Ich sagte, die zweite wirklich wichtige Frage ist, dass wir einen Artikel aus einem Slicer auswählen möchten und schließlich den anderen Artikel aus dem Slicer und dann den anderen Artikel aus dem Slicer. Wir brauchen keine Kombinationen wie ANDY und dann ANDY und BETTY und dann ANDY und CHARLIE, oder? Das ist raus. Ich mache nur einen Gegenstand von jedem Slicer. Ja Ja Ja. So wird es gehen. Perfekt, sagte ich. Sagen Sie mir dies hier, wählen Sie jeden Slicer aus, gehen Sie zu den SLICER TOOLS, OPTIONS und gehen Sie zu SLICER SETTINGS. Wir haben das gerade vor 2 Folgen gemacht. Ist das nicht verrückt? NAME FÜR FORMELN und ich weiß, dass es SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,in Ordung? Also, ich denke ich habe es.
Jetzt wechseln wir hier zu VBA und stellen übrigens sicher, dass Sie als xlsm gespeichert sind und dass Ihre Makrosicherheit so eingestellt ist, dass Makros zulässig sind. Wenn es als xlsx gespeichert ist, vertrau mir, du musst eine DATEI erstellen, SPEICHERN ALS, du wirst all deine Arbeit verlieren, wenn du es als xlsx belässt. Ja, 99,9% der von Ihnen verwendeten Tabellen sind xlsx, aber diese mit einem Makro funktioniert nicht. ALT + F11. Okay, hier ist der Code.
Wir werden drei Slicer-Caches, einen Slicer-Gegenstand und 3 Bereiche finden. Für jeden der Slicer-Caches setzen wir ihn auf den Namen, der in der Formel verwendet wird, die ich Ihnen gerade im Dialogfeld SLICER-EINSTELLUNGEN gezeigt habe. Wir haben also die drei. Ich möchte alle löschen, um sicherzustellen, dass wir wieder zu allem zurückkehren, was ausgewählt wird. Dieser Zähler wird später im Dateinamen verwendet.
In Ordung. Nun, dieser nächste Abschnitt hier, auf der rechten Seite, erstellt drei statistische Listen aller Slicer-Artikel. Sehen Sie sich Outtake Nr. 2 an, um zu sehen, warum diese Verrücktheit passieren musste. Also werde ich herausfinden, wo sich die nächste verfügbare Spalte befindet, über 2 aus der letzten Spalte gehen, daran denken, damit ich das Zeug später löschen kann, und dann für jeden SI das Slicer-Element IN SC1.SLICERITEMS, Wir werden diese Slicer-Beschriftung in die Tabelle schreiben. Wenn wir mit all diesen Slicer-Elementen fertig sind, stellen Sie fest, wie viele Zeilen wir heute hatten, und nennen Sie diesen Bereich dann SLICERITEMS1. Wir werden das Ganze für Slicer Cache 2 wiederholen und dabei 1 Spalte, SLICERITEMS2 und SLICERITEMS3, durchgehen.
Lassen Sie mich Ihnen zeigen, wie es an dieser Stelle aussieht. Also werde ich hier einen Haltepunkt setzen und diesen Code ausführen. In Ordung. Das war schnell. Wir werden zu VBA wechseln und weit weg hier auf der rechten Seite werde ich 3 neue Listen bekommen. Diese Listen sind alles, was im Slicer enthalten ist, und Sie sehen, es heißt SLICERITEMS1, SLICERITEMS2 und SLICERITEMS3, okay? Wir werden das am Ende loswerden, aber das gibt uns etwas zum Durchlaufen. Zurück zu VBA.
In Ordung. Wir werden alle Elemente in SLICERITEMS1 durchlaufen, den Filter für Slicer-Cache 1 löschen und dann jedes Slicer-Element einzeln durchgehen und prüfen, ob dieses Slicer-Element = dazu gehört CELL1.VALUE, und wir durchlaufen erneut jeden der Werte. Also, beim ersten Mal wird es ANDY und dann BETTY sein und, wissen Sie, und so weiter.
Es ist frustrierend. Ich konnte keine Möglichkeit finden, alle Schneidemaschinen gleichzeitig auszuschalten. Ich habe sogar versucht, den Code aufzuzeichnen und einen Slicer auszuwählen, und der aufgezeichnete Code brachte 9 Slicer zurück und schaltete den einen Slicer ein, okay? So frustrierend, dass ich nichts Besseres finden konnte, aber nichts Besseres.
Also setzen wir den ersten Slicer = auf ANDY. Dann gehen wir durch und für den zweiten Slicer setzen wir ihn = auf den ersten Punkt. Setzen Sie für den dritten Slicer = auf das erste Element.
In Ordung. Dann entscheiden Sie hier unten, ob dies eine gültige Kombination ist. Ich muss dir erklären, warum das wichtig ist. Wenn wir als Menschen dies tun, ANDY, würden wir A52 nicht wählen, weil es eindeutig ausgegraut ist, aber das Makro wird zu dumm sein und A52 und dann 104 wählen, und es wird dies leer erzeugen Schwenktisch. Hier gibt es also tausend mögliche Kombinationen. Ich weiß, dass es nur 400 mögliche Berichte gibt. Das hat mir die Person gesagt, und so werden wir 600 Mal dort sein, wo wir ein PDF dieses (hässlichen - 04:45) Berichts erstellen werden.
Also werde ich hier auf der Registerkarte ANALYSE nachsehen - sie hieß 2010 OPTIONEN - und sehen, wie diese Pivot-Tabelle heißt, und ich möchte sehen, wie viele Zeilen wir bekommen. Wenn ich in meinem Fall zwei Zeilen erhalte, weiß ich, dass es sich um einen Bericht handelt, den ich nicht exportieren möchte. Wenn ich mehr als 2 Zeilen, 3, 4, 5, 6 erhalte, weiß ich, dass es sich um einen Bericht handelt, den ich exportieren möchte. Sie müssen in Ihrer Situation herausfinden, um welche es sich handelt.
In Ordung. Deshalb prüfen wir, ob die Pivot-Tabelle 2 und der Name, der sich dort im Menüband befand, .TABLERANGE2.ROWS.COUNT> 2 ist. Wenn es nicht> 2 ist, möchten wir nicht PDF erstellen, okay? Diese IF-Anweisung bis hin zu END IF besagt also, dass wir nur die PDFs für die Berichtskombinationen mit Werten erstellen werden. MYFILENAME, ich habe einen Ordner namens C: REPORTS erstellt. Es ist nur ein leerer Ordner. C: BERICHTE. Sie stellen sicher, dass Sie einen Ordner haben und verwenden den gleichen Ordnernamen im Makro. C: REPORTS / und der Name der Datei lautet REPORT001.PDF. Nun, der Zähler, den wir wieder initialisiert haben, ist 1 mit FORMAT, was in Excel dem Text des Zählers entspricht, und 000. Auf diese Weise erhalte ich 001, dann 002, dann 003 und dann 004. Sie werde richtig sortieren.Wenn ich diesen REPORT1 gerade angerufen hätte und später einen REPORT10 und 11 und später einen REPORT100 habe, werden diese alle zusammen sortiert, wenn sie nicht zusammen gehören, okay? Wenn Sie also den Namen der Datei erstellen, falls die Datei seit dem letzten Ausführen vorhanden ist, werden wir sie beenden. Mit anderen Worten, löschen Sie es. Wenn Sie versuchen, eine Datei zu töten, die nicht vorhanden ist, wird natürlich ein Fehler ausgegeben. Wenn wir also in der nächsten Zeile einen Fehler erhalten, ist das in Ordnung. Fahren Sie einfach fort, aber dann habe ich die Fehlerprüfung auf FEHLER GOTO 0 zurückgesetzt.Wenn Sie versuchen, eine Datei zu töten, die nicht vorhanden ist, wird natürlich ein Fehler ausgegeben. Wenn wir also in der nächsten Zeile einen Fehler erhalten, ist das in Ordnung. Fahren Sie einfach fort, aber dann setze ich die Fehlerprüfung auf ON ERROR GOTO 0 zurück.Wenn Sie versuchen, eine Datei zu töten, die nicht vorhanden ist, wird natürlich ein Fehler ausgegeben. Wenn wir also in der nächsten Zeile einen Fehler erhalten, ist das in Ordnung. Fahren Sie einfach fort, aber dann setze ich die Fehlerprüfung auf ON ERROR GOTO 0 zurück.
Hier ist das AKTIVE BLATT, EXPORTIEREN ALS FESTFORMAT, als PDF, es gibt den Dateinamen, all diese Auswahlmöglichkeiten, und dann erhöhe ich den Zähler. Auf diese Weise erstellen wir beim nächsten Mal, wenn wir einen Datensatz finden, REPORT002.PDF . Beenden Sie diese drei Schleifen und löschen Sie dann die statischen Listen. Also werde ich mich daran erinnern, welche Spalte wir waren, die Größe von 1 Zeile, 3 Spalten, ENTIRECOLUMN.CLEAR und dann ein nettes kleines Meldungsfeld ändern, um zu zeigen, dass Dinge erstellt wurden. Okay. Lass es uns laufen.
In Ordung. Was hier nun passieren sollte, ist, wenn wir uns den Windows Explorer ansehen, da ist es. Okay. Es schafft … wie, jede Sekunde bekommen wir 2 oder 3 oder 4 oder mehr. Ich werde das pausieren und es laufen lassen. In Ordung. Da sind wir. Es wurden 326 Berichte erstellt. Es durchlief alle 1000 Möglichkeiten und behielt nur diejenigen bei, bei denen es ein tatsächliches Ergebnis gab. Okay, von 9:38 bis 9:42, 4 Minuten, um all das zu tun, aber immer noch schneller als die 400, okay?
In Ordung. Das ist also der Makro-Weg, um dies zu tun. Das andere, was mir hier aufgefallen ist, dass es funktionieren kann oder nicht. Es ist wirklich schwer zu sagen. Nehmen wir unsere Daten und ich werde die Daten in eine brandneue Arbeitsmappe verschieben. Verschieben oder kopieren, eine Kopie erstellen, in ein neues Buch klicken, auf OK klicken, und wir werden hier einen Trick anwenden, den ich zuerst von Szilvia Juhasz - einer großartigen Excel-Beraterin in Südkalifornien - gelernt habe Fügen Sie hier ein KEY-Feld hinzu. Das KEY-Feld lautet = REVIEWER & ANTENNA & DISCIPLINE. Wir werden das nach unten kopieren und eine neue Pivot-Tabelle einfügen. Klicken Sie auf OK, und wir nehmen dieses Feld, das KEY-Feld, und verschieben es in die altmodischen FILTER. Dann wollen wir sehen. (Lassen Sie uns hier einen kleinen Bericht mit - 08:30 zerstreuen.) REVIEWER, ANTENNA, DISCIPLINE und REVENUE, so.
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.
Und das dritte Outtake, okay? Dies ist derjenige, der verrückt ist. Wenn ich ein Makro aufzeichnen möchte, wenn ich (ein Makro schreiben - 13:35) nur ein Element auswählen möchte, finden Sie heraus, wie das geht, indem Sie DEVELOPER, RECORD MACRO, HOWTOCHOOSEONEITEMFROMSLICER verwenden, klicken Sie auf OK, und wir wählen einfach eines aus Artikel. FLO. Klicken Sie auf STOP RECORDING, dann gehen wir zu ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER, BEARBEITEN Sie das und machen Sie FLO TRUE und dann alle anderen FLASE. Wenn ich einen Slicer mit 100 Elementen hätte, müssten 100 Codezeilen eingefügt werden, um die Auswahl aller anderen Elemente aufzuheben. Scheint unglaublich ineffizient, aber da bist du ja.
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2106.xlsx
