Įprastiniai suvestinių lentelių filtrai siūlo puslapius Rodyti visus ataskaitų filtrus, tačiau pjaustyklės nepalaiko šios funkcijos. Šiandien kai kurie VBA peržiūri visus įmanomus pjaustyklių derinius.
Žiūrėti video
Vaizdo įrašo nuorašas
Išmokite „Excel“ iš „Podcast“ serijos 2106: sukurkite kiekvieno 3 pjaustyklių derinio PDF.
Koks puikus klausimas šiandien mums kyla. Kažkas parašė, norėjo sužinoti, ar tai įmanoma. Šiuo metu jie turi 3 pjaustytuvus, valdančius sukamą stalą. Nežinau, kaip atrodo sukama lentelė. Tai konfidencialu. Man neleidžiama to matyti, todėl tik spėju, tiesa? Taigi, jie daro tai, kad jie pasirenka po vieną elementą iš kiekvieno pjaustyklės, tada sukuria PDF, tada eina ir pasirenka kitą elementą ir sukuria PDF, tada kitą elementą ir kitą elementą, ir jūs galite Įsivaizduokite, kad naudojant 400 pjaustyklių derinių tai gali užtrukti amžinai, ir jie sakė: ar yra koks nors būdas, kad programa pereitų per visas galimybes?
Aš pasakiau, gerai, štai keli kvalifikaciniai klausimai. Pirmas numeris, mes ne „Mac“, tiesa? Ne „Android“, ne „iPhone“ skirta „Excel“. Tai „Windows“ skirta „Excel“. Taip, jie pasakė. Puiku. Aš sakiau, kad antras tikrai svarbus klausimas yra tas, kad mes norime pasirinkti vieną elementą iš pjaustyklės, o paskui kitą daiktą iš pjaustyklės, o paskui kitą iš pjaustyklės. Mums nereikia tokių derinių kaip ANDY, tada ANDY ir BETTY, o paskui ANDY ir CHARLIE, tiesa? Tai išėjo. Aš tiesiog padarysiu po vieną daiktą iš kiekvieno pjaustyklės. Taip taip taip. Taip ir eis. Puikiai, sakiau aš. Taigi, pasakykite man, išsirinkite kiekvieną pjaustyklę, eikite į pjaustyklių įrankius, parinktis ir eikite į pjaustytuvų nustatymus. Ką tik tai padarėme prieš 2 epizodus. Argi tai nėra beprotiška? PAVADINIMAS, KURĮ VARTOTI FORMULĖSE, ir aš žinau, kad tai SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,gerai? Taigi, manau, aš tai supratau.
Dabar čia pereisime prie VBA ir, beje, įsitikinkite, kad esate išsaugotas kaip xlsm, ir įsitikinkite, kad jūsų makrokomandos saugumas nustatytas leisti makrokomandas. Jei jis išsaugotas kaip „xlsx“, patikėkite manimi, jūs turite atlikti FILE, SAVE AS, jūs prarasite visą savo darbą, jei paliksite jį kaip „xlsx“. Taip, 99,9% naudojamų skaičiuoklių yra xlsx, bet ši su makrokomanda neveiks. ALT + F11. Gerai, todėl štai kodas.
Rasime tris pjaustyklių talpyklas, vieną pjaustyklės elementą ir 3 diapazonus. Kiekvienai iš pjaustyklių talpyklų nustatysime pavadinimą, naudojamą formulėje, kurią ką tik parodžiau jums dialogo lange SLICER SETTINGS. Taigi, mes turime tris iš jų. Noriu išvalyti visus, kad įsitikintume, jog grįžome prie visko, kas buvo pasirinkta. Šis skaitiklis vėliau bus naudojamas failo pavadinime.
Gerai. Dabar šis kitas skyrius, NETINKANT TEISĖS, PASTATYTI TRIS STATINIUS VISŲ Pjaustyklių elementų sąrašus. Pažiūrėkite į 2-ąjį patyrimą, kad sužinotumėte, kodėl šis beprotybė turėjo įvykti. Taigi aš išsiaiškinsiu, kur yra kitas galimas stulpelis, tarsi pereikite per 2 iš paskutinio stulpelio, nepamirškite, kad galėčiau vėliau ištrinti medžiagą ir tada kiekvienam SI, pjaustymo elementui, SC1. SLICERITEMS parašysime tą pjaustyklės antraštę į skaičiuoklę. Kai baigsime visus tuos pjaustymo elementus, išsiaiškinkite, kiek eilučių šiandien turėjome, tada pavadinkite tą diapazoną kaip SLICERITEMS1. Mes pakartosime visą tą dalyką 2 pjaustyklės talpyklai, einančiai per 1 stulpelį, SLICERITEMS2 ir SLICERITEMS3.
Leiskite man parodyti, kaip tai atrodo šiuo metu. Taigi, aš įdėsiu lūžio tašką čia ir mes paleisime šį kodą. Gerai. Tai buvo greita. Mes pereisime prie VBA ir toli čia, dešinėje pusėje, aš gausiu 3 naujus sąrašus. Šie sąrašai yra viskas, kas yra pjaustyklėje, ir matote, kad jie vadinami SLICERITEMS1, SLICERITEMS2 ir SLICERITEMS3, gerai? Pabaigoje to atsikratysime, bet tai suteikia mums ką nors persikelti. Grįžti į VBA.
Gerai. Peržiūrėsime visus SLICERITEMS1 elementus, išvalysime pjaustyklės 1 talpyklos filtrą ir tada eisime po vieną po kiekvieną pjaustyklės elementą ir pamatysime, ar šis pjaustyklės elementas yra = šiam CELL1.VALUE, ir vėlgi, mes pereiname kiekvieną vertę. Taigi, pirmą kartą tai bus ANDY, tada BETTY ir, žinote, ir pan.
Tai apmaudu. Negalėjau rasti būdų, kaip išjungti visas pjaustykles vienu metu. Aš net bandžiau įrašyti kodą ir pasirinkti vieną pjaustyklę, o įrašytas kodas grąžino 9 pjaustykles ir įjungė vieną pjaustyklę, gerai? Taip apmaudu, kad negalėjau rasti nieko geresnio už tai, bet negalėjau rasti nieko geresnio už tai.
Taigi, mes nustatėme pirmąją pjaustyklę = į ANDY. Tada mes einame per antrą pjaustyklę, mes nustatysime tai = pirmajam elementui. Trečiajam pjaustytuvui nustatykite = pirmąjį elementą.
Gerai. Tada čia nuspręskite, ar tai yra galiojantis derinys. Turiu jums paaiškinti, kodėl tai svarbu. Jei mes, kaip žmonės, darome tai, ANDY, nesirinktume A52, nes akivaizdu, kad jis yra pilkas, tačiau makrokomanda bus per kvaila ir pasirinks A52, tada 104, ir tai sukurs tuščią suvestinės lentelės. Taigi, čia yra tūkstantis galimų derinių. Žinau, kad galimi tik 400 pranešimų. Tai žmogus man pasakė, todėl gausime 600 kartų, kur sukursime šios (negražios - 04:45) ataskaitos PDF.
Taigi, ką aš darysiu, aš ieškosiu čia skirtuko ANALIZĖ - 2010 m. Jis buvo vadinamas OPTIONS - ir pamatysiu, koks šios sukamosios lentelės pavadinimas, ir aš noriu pamatyti, kiek eilučių mes gauname. Mano atveju, jei gaunu 2 eilutes, žinau, kad tai ataskaita, kurios nenoriu eksportuoti. Jei gaunu daugiau nei 2 eiles, 3, 4, 5, 6, tada žinau, kad tai ataskaita, kurią noriu eksportuoti. Savo situacijoje turėsite išsiaiškinti, kokia ji yra.
Gerai. Taigi, todėl mes tikriname, ar 2 suvestinė lentelė, ir tai yra vardas, kuris buvo atgal juostoje, .TABLERANGE2.ROWS.COUNT yra> 2. Jei tai nėra> 2, mes nenorime susikurti PDF, gerai? Taigi, šis IF teiginys iki šio END IF sako, kad kursime tik ataskaitų derinių, turinčių reikšmes, PDF. „MYFILENAME“, sukūriau aplanką „C: REPORTS“. Tai tik tuščias aplankas. C: ATASKAITOS. Įsitikinkite, kad turite aplanką ir makrokomandoje naudojate tą patį aplanko pavadinimą. C: REPORTS / ir failo pavadinimas bus REPORT001.PDF. Dabar skaitiklis, kurį inicijavome atgal, yra 1, naudojant FORMAT, kuris „Excel“ prilygsta skaitiklio teksto sakymui, ir 000. Tokiu būdu gausiu 001, tada 002, tada 003 ir 004. Jie rūšiuos teisingai.Jei ką tik buvau paskambinęs į šį PRANEŠIMĄ1, o vėliau turiu ATASKAITĄ10 ir 11, o vėliau - „REPORT100“, visi jie susitvarkys kartu, kai nepriklauso, gerai? Taigi, sukurdami failo pavadinimą, jei failas egzistuoja nuo paskutinio karto, kai jį vykdėme, mes jį nužudysime. Kitaip tariant, ištrinkite. Žinoma, jei bandysite užmušti failą, kurio nėra, jie sukels klaidą. Taigi, jei kitoje eilutėje gausime klaidą, viskas gerai. Tiesiog tęskite, bet tada iš naujo nustatau klaidą tikrinant ON ERROR GOTO 0.Žinoma, jei bandysite užmušti failą, kurio nėra, jie sukels klaidą. Taigi, jei kitoje eilutėje gausime klaidą, viskas gerai. Tiesiog tęskite, bet tada iš naujo nustatau klaidą tikrinant ON ERROR GOTO 0.Žinoma, jei bandysite užmušti failą, kurio nėra, jie sukels klaidą. Taigi, jei kitoje eilutėje gausime klaidą, viskas gerai. Tiesiog tęskite, bet tada iš naujo nustatau klaidą tikrinant ON ERROR GOTO 0.
Štai AKTYVUS LAPAS, EKSPORTUOTI NUO FIKSUOTO FORMATO, kaip PDF, yra failo pavadinimas, visi tie pasirinkimai, tada aš padidinu skaitiklį, taigi, kai kitą kartą rasime įrašą, sukursime REPORT002.PDF . Užbaikite tas tris kilpas ir tada PAŠALINKITE STATINIUS SĄRAŠUS. Taigi, aš prisiminsiu, koks buvome stulpelis, pakeiskite 1 eilutės, 3 stulpelių dydį, ENTIRECOLUMN.CLEAR, tada ten - gražus pranešimas, rodantis, kad viskas sukurta. Gerai. Paleiskime.
Gerai. Tai, kas čia turėtų atsitikti, yra tai, kad einame ir ieškome „Windows Explorer“. Gerai. Tai kuria … pavyzdžiui, kiekvieną sekundę gauname 2, 3, 4 ar daugiau. Aš tai pristabdysiu ir paleisiu. Gerai. Čia mes esame. Sukurtos 326 ataskaitos. Jis pasinaudojo visomis 1000 galimybių ir išlaikė tik tas, kur buvo tikras rezultatas. Gerai, nuo 9:38 iki 9:42, 4 minutės visa tai padaryti, bet vis tiek greičiau nei atliekant 400, gerai?
Gerai. Taigi, tai yra makrokomandas. Kitas dalykas, kuris čia mane pribloškė, kad jis gali ir neveikti. Tikrai sunku pasakyti. Paimkime savo duomenis ir aš ketinu juos perkelti į visiškai naują darbaknygę. Perkelkite arba nukopijuokite, sukurkite kopiją į NAUJĄ KNYGĄ, spustelėkite Gerai, ir mes panaudosime čia triuką, kurį pirmą kartą sužinojau iš Szilvijos Juhasz - puikios „Excel“ konsultantės Pietų Kalifornijoje - ir čia pridėkite lauką RAKTAS. PAGRINDINIS laukas yra = PERŽIŪRĖTOJAS & ANTENA & DISCIPLINĖ. Nukopijuosime tai žemyn ir įterpsime naują sukamą lentelę. Spustelėkite Gerai, ir mes paimsime tą lauką, RAKTŲ lauką, ir perkelsime jį į senamadiškus FILTRUS, tada pažiūrėkime. (Išsklaidykime nedidelę ataskaitą čia - 08:30). PATIKRINTOJAS, ANTNA, Drausmė ir Pajamos, panašiai.
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.
O trečiasis išėjimas, gerai? Tai beprotiška. Jei noriu įrašyti makrokomandą, jei noriu (parašyti makrokomandą - 13:35) pasirinkti tik vieną elementą, išsiaiškinkite, kaip tai padaryti naudojant „DEVELOPER“, „RECORD MACRO“, „HOWTOCHOOSEONEITEMFROMSLICER“, spustelėkite Gerai ir mes paprasčiausiai pasirenkame vieną elementą. FLO. Spustelėkite NUSTATYTI ĮRAŠYMĄ, tada einame ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER, REDAGUOKITE tai ir, tikrai, jie padaro FLO TIKRĄ, o tada visi kiti FLASE. Tai reiškia, kad jei aš turėčiau pjaustyklę su 100 elementų, jie turėtų ten įdėti 100 eilučių kodo, kad panaikintų visa kita. Atrodo nepaprastai neefektyvu, bet ten tu esi.
Atsisiųsti failą
Atsisiųskite failo pavyzdį čia: Podcast2106.xlsx
