Tavalised pöördtabeli filtrid pakuvad lehte Kuva kõik aruandefilter, kuid viilutajad seda funktsiooni ei toeta. Täna on mõned VBA-d, et tutvuda kõigi võimalike viilutajate kombinatsioonidega.
Vaata videot
Video ärakiri
Õppige Exceli, Podcast Episode 2106: looge PDF iga kolme viilutaja kombinatsioonist.
Mis suurepärane küsimus meil täna on. Keegi kirjutas sisse, tahtis teada, kas see on võimalik. Praegu on neil 3 viilutajat, kes juhivad pöördlauda. Ma ei tea, kuidas pöördtabel välja näeb. See on konfidentsiaalne. Ma ei tohi seda näha, nii et ma lihtsalt aiman, eks? Mida nad teevad, on see, et nad valivad igast viilutajast ühe elemendi ja loovad seejärel PDF-faili, seejärel valivad järgmise üksuse ja loovad PDF-faili ning seejärel järgmise üksuse ja järgmise üksuse ning saate kujutage ette, et 400 viilutajakombinatsiooniga võib see kesta igavesti ja nad ütlesid, kas on mingil viisil võimalik, et programm läbiks kõik võimalused?
Ma ütlesin, et korras, siin on mõned kvalifitseeruvad küsimused. Number üks, me ei ole Macis, eks? Mitte Android, mitte iPhone'i jaoks mõeldud Excel. See on Exceli Windows. Jah, nad ütlesid. Suurepärane. Ma ütlesin, et teine tõeliselt oluline küsimus on see, et me tahame valida ühe eseme viilutajast ja seejärel teise eseme viilutajast ja siis teise eseme viilutajast. Me ei vaja selliseid kombinatsioone nagu ANDY ja siis ANDY ja BETTY ning siis ANDY ja CHARLIE, eks? See on väljas. Ma lihtsalt teen igast viilutajast ühe eseme. Jah, jah, jah. Nii see läheb. Täiuslik, ütlesin. Nii et siin, öelge mulle see, valige iga viilutaja, minge viilutaja tööriistadesse, valikutesse ja minge viilutaja seadistustesse. Tegime seda just 2 osa tagasi. Kas pole hullu? NIMI, MIDA FORMULIDES KASUTADA, ja ma tean, et see on SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,olgu? Nii et ma arvan, et mul on see olemas.
Nüüd läheme siin üle VBA-le ja muide, veenduge, et olete salvestatud xlsm-na, ja veenduge, et teie makroturvalisus oleks makrode lubamiseks. Kui see on salvestatud kui xlsx, siis usaldage mind, peate minema tegema FILE, SAVE AS, siis kaotate kogu oma töö, kui jätate selle xlsx-iks. Jah, 99,9% teie kasutatavatest arvutustabelitest on xlsx, kuid see makroga ei tööta. ALT + F11. Hästi, nii et siin on kood.
Leiame kolm viilutaja vahemälu, ühe viilutuselemendi ja 3 vahemikku. Iga viilutaja vahemälu jaoks määrame selle valemis kasutatud nimele, mida ma teile just dialoogiboksis SLICER SETTINGS näitasin. Nii et meil on neid kolm. Ma tahan kõik need selgeks teha, et veenduda, et oleme valimise juures tagasi. Seda loendurit hakatakse hiljem failinimes kasutama.
Hästi. Nüüd, see järgmine jaotis, mis asub paremal pool, ehitage KÕIK STAATILISED NIMEKIRJAD KÕIKidest viilutajatest. Vaadake väljavõtet nr 2, et näha, miks see hullus pidi juhtuma. Nii et ma mõtlen välja, kus asub järgmine saadaval olev veerg, minge viimasest veerust üle 2, pidage meeles, et ma saaksin kraami hiljem kustutada ja seejärel iga SI, viilutaja üksuse jaoks SC SC-s. SLICERITEMS, kirjutame selle tükeldaja pealdise arvutustabelisse. Kui oleme kõigi nende viilutajate üksustega valmis saanud, mõelge välja, mitu rida meil täna oli, ja nimetage siis vahemik SLICERITEMS1. Kordame kogu asja viilutaja vahemälu 2 puhul, liikudes üle ühe veeru, SLICERITEMS2 ja SLICERITEMS3.
Las ma näitan teile, kuidas see praegu välja näeb. Nii et panen siin katkestuspunkti ja käivitame selle koodi. Hästi. See oli kiire. Läheme üle VBA-le ja kaugel siin paremal pool saan 3 uut nimekirja. Need loendid on kõik, mis viilutamismasinas on, ja näete, et need on SLICERITEMS1, SLICERITEMS2 ja SLICERITEMS3, eks? Me vabaneme sellest lõpuks, kuid see annab meile midagi läbi vaadata. Tagasi VBA juurde.
Hästi. Tutvume kõigi üksuste SLICERITEMS1 üksustega, tühjendame viilutaja vahemälu 1 filtri ja siis käime ükshaaval läbi iga viilutaja üksuse ja vaatame, kas see viilutaja üksus on = sellele CELL1.VALUE ja jällegi läheme läbi iga väärtuse. Nii et esimest korda saab see olema ANDY ja siis BETTY ja teate jne.
See on masendav. Ma ei leidnud viisi, kuidas kõiki viilutajaid korraga välja lülitada. Proovisin isegi koodi salvestada ja ühe viilutaja valida ning salvestatud kood tagastas 9 viilutaja välja ja lülitas ühe viilutaja sisse, olgu? Nii masendav, et ma ei leidnud midagi paremat kui see, kuid ma ei suutnud midagi paremat leida.
Seega seadsime esimese viilutaja = väärtusele ANDY. Siis läheme läbi ja teise viilutaja jaoks seame selle = esimesele elemendile. Kolmanda viilutaja jaoks määrake see = esimesele üksusele.
Hästi. Siis, siin all, OTSUSTAGE, KAS SEE ON KEHTIV KOMBINATSIOON. Pean teile selgitama, miks see oluline on. Kui me inimestena seda teeme, siis ANDY, siis me ei valiks A52, sest see on selgelt hall, kuid makro on liiga rumal ja ta valib A52 ja siis 104 ning see loob selle tühja pöördlaud. Nii et siin on tuhat võimalikku kombinatsiooni. Ma tean, et on ainult 400 võimalikku aruannet. Seda ütles inimene mulle ja nii jõuame 600 korda, kus loome selle (kole - 04:45) aruande PDF-i.
Mida ma teen, on see, et ma vaatan siin vahekaardil ANALÜÜS - see sai 2010. aastal nimeks OPTIONS - ja näen, mis selle pöördtabeli nimi on, ja ma tahan näha, mitu rida saame. Minu puhul, kui saan 2 rida, tean, et see on aruanne, mida ma ei soovi eksportida. Kui ma saan rohkem kui 2 rida, 3, 4, 5, 6, siis tean, et see on aruanne, mida tahan eksportida. Peate oma olukorras välja mõtlema, mis see on.
Hästi. Niisiis, sellepärast kontrollime, kas pöördtabel 2 ja see nimi oli lindil tagasi .TABLERANGE2.ROWS.COUNT on> 2. Kui see pole> 2, ei taha me PDF-i luua, eks? Niisiis, see IF-lause kuni selle END IF-ni ütleb, et loome ainult PDF-id aruandekombinatsioonide jaoks, millel on väärtused. MYFILENAME, lõin kausta nimega C: REPORTS. See on lihtsalt tühi kaust. C: ARUANDED. Veenduge, et teil oleks kaust, ja kasutage makros sama kausta nime. C: REPORTS / ja faili nimeks saab REPORT001.PDF. Nüüd initsialiseerisime loenduri, kus on üks vormingut FORMAT kasutades, mis on Excelis samaväärne loenduri teksti ütlemisega, ja 000. Nii saan 001, siis 002, siis 003 ja siis 004. Nad hakkan õigesti sortima.Kui ma oleksin just sellele aruandele helistanud1 ja hiljem on mul REPORT10 ja 11 ning hiljem REPORT100, siis hakkavad need kõik koos sorteerima, kui nad kokku ei kuulu, eks? Niisiis, kui loome faili nime juhul, kui fail on olemas sellest, kui seda viimati käitasime, siis tapame selle. Teisisõnu kustutage see. Muidugi, kui proovite tappa faili, mida seal pole, viskavad nad vea. Seega, kui järgmises reas kuvatakse viga, on see kõik korras. Lihtsalt jätkake, kuid siis lähtestasin vea kontrollimisel rakenduse ON ERROR GOTO 0.Muidugi, kui proovite tappa faili, mida seal pole, viskavad nad vea. Seega, kui järgmises reas kuvatakse viga, on see kõik korras. Lihtsalt jätkake, kuid siis lähtestasin vea kontrollimisel rakenduse ON ERROR GOTO 0.Muidugi, kui proovite tappa faili, mida seal pole, viskavad nad vea. Seega, kui järgmises reas ilmneb viga, on see kõik korras. Lihtsalt jätkake, kuid siis lähtestasin vea kontrollimisel rakenduse ON ERROR GOTO 0.
Siin on AKTIIVNE Leht, EKSPORT FIKSEERITUD VORMIS, PDF-na, seal on failinimi, kõik need valikud ja siis suurendan loendurit, nii et järgmisel korral, kui leiame kirjeid, loome REPORT002.PDF . Lõpeta need kolm silmust ja SELLE STAATILISED NIMEKIRJAD. Niisiis, ma mäletan, milline veerg me olime, muutke suuruse 1 rida, 3 veergu, ENTIRECOLUMN.CLEAR ja seejärel seal väike kena sõnumikast, mis näitab, et asjad on loodud. Okei. Käivitame selle.
Hästi. Mis peaks siin juhtuma, on see, kui läheme ja uurime Windows Exploreris, seal see on. Okei. See loob … nagu iga sekund, saame 2, 3 või 4 või rohkem. Ma peatan selle ja lasen sellel joosta. Hästi. Seal me oleme. Loodud on 326 aruannet. See ajas läbi kõik 1000 võimalust ja hoidis alles neid, kus oli tegelik tulemus. Hästi, kell 9:38 kuni 9:42, 4 minutit, et seda kõike teha, kuid siiski kiiremini kui 400-ga, olgu?
Hästi. Nii, see on makro viis seda teha. Teine asi, mis mind siin rabas, et see võib töötada või mitte. See on tõesti raske öelda. Võtame oma andmed ja ma liigutan andmed uhiuude töövihikusse. KOLIGE VÕI KOOPI, LOE KOOPIA, UUE RAAMATU juurde, klõpsake nuppu OK ja me kasutame siin nippi, mille õppisin kõigepealt Szilvia Juhaszilt - suurepäraselt Exceli konsultandilt Lõuna-Californias - ja läheme lisage siia KEY väli. VÕTI väli on = REVIEWER & ANTENNA & DISCIPLINE. Kopeerime selle alla ja sisestame uue pöördtabeli. Klõpsake nuppu OK ja me võtame selle välja, võtme välja ja liigutame selle vanamoodsate FILTRITE juurde ja siis vaatame. (Hajutame siin väikese aruande - 08:30.) KASUTAJAD, ANTEN, DISCIPLINE ja TULUD.
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.
Ja kolmas väljavõtmine, olgu? See on hull. Kui ma tahan makrot salvestada, siis kui tahan (kirjutada makro - 13:35) valida ainult üks üksus, siis mõelge välja, kuidas seda teha, kasutades DEVELOPER, RECORD MACRO, HOWTOCHOOSEONEITEMFROMSLICER, klõpsake nuppu OK ja valime lihtsalt ühe üksus. FLO. Klõpsake nuppu STOP RECORDING, siis läheme ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER, MUUDA seda ja tõepoolest, nad muudavad FLO TRUE ja siis kõik teised FLASE. See tähendab, et kui mul oleks 100 tükiga viilutaja, peaksid nad kogu muu valiku tühistamiseks panema sinna 100 rida koodi. Tundub uskumatult ebaefektiivne, kuid seal sa oled.
Laadige fail alla
Laadige näidisfail alla siit: Podcast2106.xlsx
