Filtrele obișnuite din tabelul pivot oferă paginile Afișează toate filtrele de rapoarte, dar secționatoarele nu acceptă această funcționalitate. Astăzi, unele VBA pentru a parcurge toate combinațiile posibile de feliere.
Urmăriți videoclipul
Transcriere video
Aflați Excel din, Podcast Episodul 2106: Creați un PDF cu fiecare combinație de 3 feliere.
Ce mare întrebare avem astăzi. Cineva a scris, a vrut să știe dacă este posibil. În momentul de față, au 3 secționatoare care rulează un tabel pivot. Nu știu cum arată tabelul pivot. Este confidențial. Nu am voie să-l văd, așa că doar ghicesc, nu? Deci, ceea ce fac este să aleagă un articol din fiecare feliator și apoi să creeze un PDF, apoi să aleagă următorul articol și să creeze un PDF, apoi următorul articol și următorul element, și puteți imaginați-vă, cu 400 de combinații de feliere, acest lucru ar putea dura pentru totdeauna și au spus, există vreo modalitate de a face un program să treacă și să parcurgă toate opțiunile?
Am spus, bine, iată câteva întrebări de calificare. Numărul unu, nu suntem pe un Mac, nu? Nu Android, nu Excel pentru iPhone. Acesta este Excel pentru Windows. Da, au spus ei. Grozav. Am spus, a doua întrebare cu adevărat importantă este, vrem să alegem un articol dintr-un feliator și apoi în cele din urmă celălalt element din feliator, apoi celălalt element din feliator. Nu avem nevoie de combinații precum ANDY, apoi ANDY și BETTY, apoi ANDY și CHARLIE, nu? Asta e afară. Voi face doar un articol din fiecare feliator. Da da da. Așa va merge. Perfect, am spus. Deci, iată-mi, spune-mi asta, alege fiecare feliată, mergi la Instrumentele pentru SLICER, OPȚIUNI și accesează SETĂRI SLICER Tocmai am făcut asta acum 2 episoade. Nu este o nebunie? NUME DE UTILIZAT ÎN FORMULĂ și știu că este SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,bine? Deci, cred că am înțeles.
Acum, vom trece la VBA aici și, apropo, ne asigurăm că sunteți salvat ca xlsm și asigurați-vă că securitatea macro este setată pentru a permite macro-urile. Dacă este salvat ca xlsx, credeți-mă, trebuie să mergeți să faceți un FIȘIER, SAVE AS, veți pierde toată munca dacă o lăsați ca xlsx. Da, 99,9% din foile de calcul pe care le utilizați sunt xlsx, dar acesta cu o macro nu va funcționa. ALT + F11. Bine, deci iată codul.
Vom găsi trei cache-uri pentru slicer, un articol pentru slicer și 3 game. Pentru fiecare dintre cache-urile pentru slicer, îl vom seta la numele folosit în formula pe care tocmai v-am arătat-o în caseta de dialog SETĂRI SLICER. Deci, le avem pe cele trei. Vreau să le șterg pe toți pentru a mă asigura că ne întoarcem la tot ce este selectat. Acest contor va fi folosit mai târziu în numele fișierului.
Bine. Acum, următoarea secțiune aici, ÎN CUMPĂRAT, CONSTRUIȚI TREI LISTE STATICE ALE TOATE ARTICOLELE DE SLICER. Vedeți outtake # 2 pentru a vedea de ce a trebuit să se întâmple această nebunie. Așa că am să-mi dau seama unde este următoarea coloană disponibilă, cam trec peste 2 din ultima coloană, amintiți-vă că aș putea șterge lucrurile mai târziu și apoi, pentru fiecare articol de tip slicer SI, ÎN SC1.SLICERITEMS, vom scrie acea subtitrare în foaia de calcul. Când am terminat cu toate acele articole de feliat, aflați câte rânduri aveam astăzi și apoi denumiți acea zonă ca SLICERITEMS1. Vom repeta totul pentru cache-ul de slicer 2, trecând peste 1 coloană, SLICERITEMS2 și SLICERITEMS3.
Permiteți-mi să vă arăt cum arată în acest moment. Deci, voi pune un punct de întrerupere chiar aici și vom rula acest cod. Bine. A fost rapid. Vom trece la VBA și, departe, aici, în partea dreaptă, voi primi 3 liste noi. Aceste liste sunt tot ceea ce este în feliat și vedeți că se numește SLICERITEMS1, SLICERITEMS2 și SLICERITEMS3, bine? Vom scăpa de asta la sfârșit, dar asta ne oferă ceva de parcurs. Înapoi la VBA.
Bine. Vom parcurge toate elementele din SLICERITEMS1, ștergem filtrul pentru cache-ul 1 pentru slicer și apoi vom parcurge, câte unul, fiecare element de slicer și vom vedea dacă acest element de slicer este = to this CELL1.VALUE și, din nou, parcurgem fiecare dintre valori. Așadar, prima dată va fi ANDY și apoi BETTY și, știi, și așa mai departe.
Este frustrant. Nu am găsit nicio modalitate de a opri toate feliile deodată. Am încercat chiar să înregistrez codul și să aleg un felietor, iar codul înregistrat returnează 9 feliere și îl pornește pe unul, bine? Atât de frustrant încât nu am putut găsi nimic mai bun decât asta, dar nu am putut găsi nimic mai bun decât atât.
Deci, setăm primul feliat = la ANDY. Apoi trecem și, pentru al doilea feliator, îl vom seta = la primul element. Pentru al treilea feliator, setați-l = la primul element.
Bine. Apoi, aici jos, DECIDEȚI DACĂ ESTE O COMBINAȚIE VALABILĂ Trebuie să vă explic de ce este important. Dacă noi, ca oameni, facem acest lucru, ANDY, nu am alege A52, deoarece este clar că este gri, dar macro-ul va fi prea prost și va alege A52 și apoi 104 și va crea acest gol masă rotativă. Deci, există o mie de combinații posibile aici. Știu că există doar 400 de rapoarte posibile. Așa mi-a spus persoana respectivă, așa că vom ajunge de 600 de ori unde vom crea un PDF al acestui raport (urât - 04:45).
Deci, ceea ce voi face este să mă uit aici în fila ANALIZĂ - s-a numit OPȚIUNI în 2010 - și să văd care este numele acestui tabel pivot și vreau să văd câte rânduri primim. În cazul meu, dacă primesc 2 rânduri, știu că este un raport pe care nu vreau să îl export. Dacă primesc mai mult de 2 rânduri, 3, 4, 5, 6, atunci știu că este un raport pe care vreau să îl export. Va trebui să vă dați seama în situația dvs. care este.
Bine. Deci, de aceea verificăm dacă tabelul pivot 2 și, acesta este numele care a fost înapoi acolo în panglică, .TABLERANGE2.ROWS.COUNT este> 2. Dacă nu este> 2, nu vrem să creați un PDF, bine? Deci, această afirmație IF până la acest END IF spune că vom crea doar PDF-urile pentru combinațiile de rapoarte care au valori. MYFILENAME, am creat un folder numit C: RAPOARTE. Este doar un folder gol. C: RAPOARTE. Vă asigurați că aveți un folder și că utilizați același nume de folder în macro. C: RAPOARTE / și numele fișierului va fi REPORT001.PDF. Acum, contorul pe care l-am inițializat este 1 folosind FORMAT, care este echivalent în Excel cu a spune textul contorului și 000. În acest fel, voi obține 001, apoi 002, apoi 003 și apoi 004. Ei o să sortez corect.Dacă tocmai aș fi sunat la acest RAPORT1 și apoi mai târziu am un RAPORT10 și 11, iar mai târziu la RAPORT100, aceștia vor sorta toți împreună când nu aparțin, bine? Deci, creând numele fișierului în cazul în care fișierul există de la ultima dată când am rulat acest lucru, îl vom ucide. Cu alte cuvinte, ștergeți-l. Bineînțeles, dacă încercați să distrugeți un fișier care nu este acolo, ei vor arunca o eroare. Deci, dacă primim o eroare în următoarea linie, este în regulă. Continuă, dar apoi resetez eroarea verificând ON EROARE GOTO 0.Bineînțeles, dacă încercați să distrugeți un fișier care nu există, acestea vor arunca o eroare. Deci, dacă primim o eroare în următoarea linie, este în regulă. Continuă, dar apoi resetez eroarea verificând ON EROARE GOTO 0.Bineînțeles, dacă încercați să distrugeți un fișier care nu există, acestea vor arunca o eroare. Deci, dacă primim o eroare în următoarea linie, este în regulă. Continuă, dar apoi resetez eroarea verificând ON EROARE GOTO 0.
Iată FIȘA ACTIVĂ, EXPORTĂȚI CA FORMAT FIX, ca fișier PDF, există numele fișierului, toate acele alegeri, iar apoi incrementez contorul, astfel încât, data viitoare când vom găsi unul care are înregistrări, vom crea REPORT002.PDF . Finalizați aceste trei bucle și apoi ȘTERGEȚI LISTELE STATICE. Deci, îmi voi aminti ce coloană am fost, redimensionează 1 rând, 3 coloane, ENTIRECOLUMN.CLEAR și apoi o căsuță de mesaje frumoasă acolo pentru a arăta că lucrurile au fost create. Bine. Să-l rulăm.
Bine. Acum, ceea ce ar trebui să se întâmple aici este dacă mergem și căutăm în Windows Explorer, acolo este. Bine. Se creează … ca, în fiecare secundă, primim 2 sau 3 sau 4 sau mai multe. Voi face o pauză și o voi lăsa să ruleze. Bine. Iată-ne. Au fost create 326 de rapoarte. A parcurs toate cele 1000 de posibilități și le-a păstrat doar pe cele în care a existat un rezultat real. Bine, de la 9:38 până la 9:42, 4 minute pentru a face toate acestea, dar totuși mai repede decât să faci 400, bine?
Bine. Deci, acesta este modul macro de a face acest lucru. Celălalt lucru care m-a izbit aici este că poate funcționa sau nu. Este foarte greu de spus. Să luăm datele noastre și voi muta datele într-un registru de lucru nou. Mutați-vă sau copiați, creați o copie, într-o carte nouă, faceți clic pe OK și vom folosi aici un truc pe care l-am învățat mai întâi de la Szilvia Juhasz - un consultant excelent Excel din California de Sud - și vom adăugați aici un câmp CHEIE. Câmpul CHEIE este = REVIEWER & ANTENNA & DISCIPLINE. O vom copia și vom insera un nou tabel pivot. Faceți clic pe OK și vom lua acel câmp, câmpul CHEIE, și îl vom muta la FILTRURILE de modă veche, apoi să vedem. (Să disipăm un mic raport aici cu - 08:30) REVIEWER, ANTENNA, DISCIPLINA și REVENUE, așa.
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.
Și a treia ieșire, bine? Acesta este cel care este nebun. Dacă vreau să înregistrez o macrocomandă, dacă vreau să (scriu o macrocomandă - 13:35) să aleg doar un singur articol, află cum să faci asta folosind DEVELOPER, RECORD MACRO, HOWTOCHOOSEONEITEMFROMSLICER, dă clic pe OK și pur și simplu alegem unul articol. FLO. Faceți clic pe STOP RECORDING, apoi mergem ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER, EDIT, și, destul de sigur, fac FLO TRUE și apoi toți ceilalți FLASE. Înseamnă că dacă aș avea un feliator cu 100 de articole, ar trebui să pună 100 de linii de cod acolo pentru a deselecta orice altceva. Pare incredibil de ineficient, dar iată-te.
Descărcare fișier
Descărcați exemplul de fișier aici: Podcast2106.xlsx
