VBA All Slicer Combinations - Excel Tips

I normali filtri delle tabelle pivot offrono le pagine Mostra tutti i filtri dei rapporti, ma i filtri dei dati non supportano questa funzionalità. Oggi, alcuni VBA consentono di scorrere tutte le possibili combinazioni di filtri dei dati.

Guarda un video

Trascrizione del video

Impara Excel da, Podcast Episodio 2106: Crea un PDF di ogni combinazione di 3 affettatrici.

Che bella domanda abbiamo oggi. Qualcuno ci ha scritto, voleva sapere se era possibile. In questo momento, hanno 3 filtri dei dati che eseguono una tabella pivot. Non so come sia la tabella pivot. È confidenziale. Non mi è permesso vederlo quindi sto solo indovinando, giusto? Quindi, quello che stanno facendo è scegliere un elemento da ogni filtro dei dati e quindi creare un PDF, quindi andare e scegliere l'elemento successivo e creare un PDF, quindi l'elemento successivo e l'elemento successivo, e puoi immagina, con 400 combinazioni di affettatrici, questo potrebbe richiedere un'eternità, e hanno detto, c'è un modo per far passare un programma e scorrere tutte le opzioni?

Ho detto, va bene, ecco alcune domande qualificanti. Numero uno, non siamo su un Mac, giusto? Non Android, non Excel per iPhone. Questo è Excel per Windows. Sì, hanno detto. Grande. Ho detto, la seconda domanda davvero importante è, vogliamo scegliere un elemento da un'affettatrice, e poi eventualmente l'altro elemento dall'affettatrice, e poi l'altro elemento dall'affettatrice. Non abbiamo bisogno di combinazioni come ANDY, e poi ANDY e BETTY, e poi ANDY e CHARLIE, giusto? È fuori. Farò solo un elemento per ogni affettatrice. Sì sì sì. È così che andrà. Perfetto, ho detto. Quindi qui, dimmi questo, scegli ogni affettatrice, vai su STRUMENTI AFFETTATRICE, OPZIONI e vai su IMPOSTAZIONI AFFETTATRICE. Abbiamo appena fatto questo 2 episodi fa. Non è da pazzi? NAME TO USE IN FORMULAS e so che è SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,Tutto a posto? Quindi, penso di averlo capito.

Ora passeremo a VBA qui e, a proposito, assicurati di essere salvato come xlsm e assicurati che la sicurezza delle macro sia impostata per consentire le macro. Se è salvato come xlsx, credimi, devi andare a fare un FILE, SALVA CON NOME, perderai tutto il tuo lavoro se lo lasci come xlsx. Sì, il 99,9% dei fogli di calcolo che utilizzi sono xlsx, ma questo con una macro non funzionerà. ALT + F11. Va bene, quindi ecco il codice.

Troveremo tre cache slicer, un elemento slicer e 3 intervalli. Per ciascuna delle cache dello slicer, lo imposteremo sul nome utilizzato nella formula che ti ho appena mostrato nella finestra di dialogo SLICER SETTINGS. Quindi, abbiamo i tre di quelli. Voglio cancellare tutti quelli per assicurarmi di tornare a tutto ciò che è stato selezionato. Questo contatore verrà utilizzato successivamente nel nome del file.

Tutto a posto. Ora, questa prossima sezione qui, A DESTRA, COSTRUISCI TRE ELENCHI STATICI DI TUTTI GLI ARTICOLI AFFETTATRICE. Vedi outtake n. 2 per capire perché questa follia è dovuta accadere. Quindi ho intenzione di capire dove si trova la prossima colonna disponibile, più o meno 2 dall'ultima colonna, ricordati che così posso eliminare le cose in seguito, e poi, per ogni SI, elemento slicer, IN SC1.SLICERITEMS, scriveremo la didascalia del filtro dei dati sul foglio di calcolo. Quando abbiamo finito con tutti quegli elementi del filtro dei dati, calcola quante righe abbiamo oggi, quindi denomina l'intervallo come SLICERITEMS1. Ripeteremo l'intera operazione per slicer cache 2, andando su 1 colonna, SLICERITEMS2 e SLICERITEMS3.

Lascia che ti mostri come appare a questo punto. Quindi, inserirò un punto di interruzione proprio qui e eseguiremo questo codice. Tutto a posto. È stato veloce. Passeremo a VBA e, molto lontano qui sul lato destro, otterrò 3 nuovi elenchi. Questi elenchi sono tutto ciò che è nel filtro dei dati e vedi che si chiama SLICERITEMS1, SLICERITEMS2 e SLICERITEMS3, va bene? Alla fine ce ne sbarazzeremo, ma questo ci dà qualcosa da fare. Torna a VBA.

Tutto a posto. Passeremo in rassegna tutti gli elementi in SLICERITEMS1, cancelleremo il filtro per la cache dei filtri dei dati 1, quindi esamineremo, uno alla volta, ciascun elemento dei filtri dei dati e vedremo se questo elemento dei filtri dei dati è = a questo CELL1.VALUE e, ancora una volta, stiamo eseguendo un ciclo attraverso ciascuno dei valori. Quindi, la prima volta sarà ANDY e poi BETTY e, sai, e così via.

È frustrante. Non sono riuscito a trovare alcun modo per spegnere tutte le affettatrici contemporaneamente. Ho anche provato a registrare il codice e scegliere un'affettatrice, e il codice registrato stava restituendo 9 affettatrici e accendendo quella, va bene? Così frustrante che non sono riuscito a trovare niente di meglio di quello, ma non sono riuscito a trovare niente di meglio di quello.

Quindi, impostiamo il primo slicer = su ANDY. Quindi procediamo e, per il secondo filtro dei dati, lo imposteremo = al primo elemento. Per il terzo filtro dei dati, impostalo = sul primo elemento.

Tutto a posto. Quindi, quaggiù, DECIDI SE QUESTA È UNA COMBINAZIONE VALIDA. Devo spiegarti perché è importante. Se noi, come esseri umani, stiamo facendo questo, ANDY, non sceglieremmo A52 perché chiaramente è disattivato, ma la macro sarà troppo stupida e sceglierà A52 e poi 104, e creerà questo vuoto tabella pivot. Quindi, ci sono mille combinazioni possibili qui. So che ci sono solo 400 segnalazioni possibili. Questo è ciò che la persona mi ha detto, quindi otterremo 600 volte dove creeremo un PDF di questo (brutto - 04:45) rapporto.

Quindi, quello che ho intenzione di fare è guardare qui nella scheda ANALISI - si chiamava OPZIONI nel 2010 - e vedere qual è il nome di questa tabella pivot e voglio vedere quante righe noi abbiamo. Nel mio caso, se ottengo 2 righe, so che è un rapporto che non voglio esportare. Se ottengo più di 2 righe, 3, 4, 5, 6, allora so che è un rapporto che voglio esportare. Dovrai capire nella tua situazione qual è.

Tutto a posto. Quindi, ecco perché stiamo controllando se la tabella pivot 2 e, questo è il nome che era lì dietro nella barra multifunzione, .TABLERANGE2.ROWS.COUNT è> 2. Se non è> 2, non vogliamo creare un PDF, va bene? Quindi, questa istruzione IF fino a questa END IF sta dicendo che creeremo solo i PDF per le combinazioni di report che hanno valori. MYFILENAME, ho creato una cartella chiamata C: REPORTS. È solo una cartella vuota. C: RAPPORTI. Assicurati di avere una cartella e di utilizzare lo stesso nome di cartella nella macro. C: REPORTS / e il nome del file sarà REPORT001.PDF. Ora, il contatore che abbiamo inizializzato di nuovo è 1 usando FORMAT, che in Excel equivale a dire il testo di counter e 000. In questo modo, otterrò 001, quindi 002, quindi 003 e quindi 004. stai per ordinare correttamente.Se avessi appena chiamato questo REPORT1, e poi avessi un REPORT10 e 11, e successivamente REPORT100, questi verranno tutti ordinati insieme quando non appartengono insieme, va bene? Quindi, creando il nome del file nel caso in cui il file esista dall'ultima volta che lo abbiamo eseguito, lo uccideremo. In altre parole, cancellalo. Ovviamente, se provi ad eliminare un file che non è presente, verrà generato un errore. Quindi, se otteniamo un errore nella riga successiva, va bene. Basta andare avanti, ma poi resetto il controllo degli errori SU ERRORE GOTO 0.Ovviamente, se provi ad eliminare un file che non è presente, verrà generato un errore. Quindi, se otteniamo un errore nella riga successiva, va bene. Basta andare avanti, ma poi resetto il controllo degli errori SU ERRORE GOTO 0.Ovviamente, se provi a terminare un file che non è presente, verrà generato un errore. Quindi, se otteniamo un errore nella riga successiva, va bene. Basta andare avanti, ma poi resetto il controllo degli errori SU ERRORE GOTO 0.

Ecco il FOGLIO ATTIVO, ESPORTA COME FORMATO FISSO, come PDF, c'è il nome del file, tutte quelle scelte, e poi incremento il contatore, così in questo modo, la prossima volta che ne troviamo uno che ha dei record, creeremo REPORT002.PDF . Termina quei tre cicli e poi ELIMINA GLI ELENCHI STATICI. Quindi, ricorderò quale colonna eravamo, ridimensionerò 1 riga, 3 colonne, ENTIRECOLUMN.CLEAR, e poi una bella finestra di messaggio lì per mostrare che le cose sono state create. Va bene. Facciamolo.

Tutto a posto. Ora, quello che dovrebbe succedere qui è che se andiamo a cercare in Windows Explorer, eccolo. Va bene. Sta creando … come, ogni secondo, ne otteniamo 2 o 3 o 4 o più. Metterò in pausa questo e lo lascerò funzionare. Tutto a posto. Ci siamo. Sono stati creati 326 rapporti. Ha esplorato tutte le 1000 possibilità e ha mantenuto solo quelle in cui c'era un risultato effettivo. Va bene, dalle 9:38 alle 9:42, 4 minuti per fare tutto questo, ma comunque più velocemente di 400, va bene?

Tutto a posto. Quindi, questo è il modo macro per farlo. L'altra cosa che mi ha colpito qui è che potrebbe o non potrebbe funzionare. È davvero difficile da dire. Prendiamo i nostri dati e sposterò i dati in una nuova cartella di lavoro. SPOSTA O COPIA, CREA UNA COPIA, su un NUOVO LIBRO, fai clic su OK, e qui utilizzeremo un trucco che ho imparato per la prima volta da Szilvia Juhasz, una grande consulente di Excel nel sud della California, e andremo a aggiungi un campo CHIAVE qui. Il campo CHIAVE è = REVISORE & ANTENNA & DISCIPLINA. Lo copieremo e inseriremo una nuova tabella pivot. Fare clic su OK, e prenderemo quel campo, il campo CHIAVE, e lo sposteremo sui FILTRI vecchio stile, e poi vediamo. (Dissolviamo qui un piccolo rapporto con - 08:30) REVISORE, ANTENNA, DISCIPLINA e RICAVI, così.

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.

E il terzo outtake, va bene? Questo è quello che è pazzo. Se voglio registrare una macro, se voglio (scrivere una macro - 13:35) per scegliere un solo elemento, scopri come farlo usando DEVELOPER, RECORD MACRO, HOWTOCHOOSEONEITEMFROMSLICER, fai clic su OK e ne scegliamo semplicemente uno articolo. FLO. Fare clic su STOP RECORDING, poi andiamo ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER, EDIT quello e, abbastanza sicuro, rendono FLO TRUE e poi tutti gli altri FLASE. Significa che se avessi un'affettatrice con 100 elementi, dovrebbero inserire 100 righe di codice per deselezionare tutto il resto. Sembra incredibilmente inefficiente ma ci sei.

Download file

Scarica il file di esempio qui: Podcast2106.xlsx

Articoli interessanti...