Parastie rakurstabulas filtri piedāvā lapas Rādīt visus pārskatu filtrus, taču griezēji neatbalsta šo funkcionalitāti. Šodien daži VBA, lai iepazītos ar visām iespējamām šķēlēju kombinācijām.
Skatīties video
Video atšifrējums
Uzziniet programmu Excel, Podcast epizode 2106. Izveidojiet PDF failu no katras 3 sagriezēju kombinācijas.
Cik liels jautājums mums šodien ir. Kāds rakstīja, vēlējās uzzināt, vai tas ir iespējams. Pašlaik viņiem ir 3 griezēji, kas vada pagrieziena galdu. Es nezinu, kā izskatās pagrieziena galds. Tas ir konfidenciāli. Man nav atļauts to redzēt, tāpēc es tikai uzminu, vai ne? Tātad, viņi dara, ka viņi izvēlas vienu priekšmetu no katra griezēja un pēc tam izveido PDF failu, pēc tam izvēlas nākamo vienumu un izveido PDF failu, pēc tam nākamo vienumu un nākamo vienumu, un jūs varat iedomājieties, ka ar 400 sagriezēju kombinācijām tas varētu ilgt uz visiem laikiem, un viņi teica: vai ir kāds veids, kā programma iet cauri visām iespējām?
Es teicu, labi, šeit ir daži kvalifikācijas jautājumi. Pirmais, mēs neesam uz Mac, vai ne? Ne Android, ne Excel iPhone. Tas ir Excel for Windows. Jā, viņi teica. Lieliski. Es teicu, otrais patiešām svarīgais jautājums ir tāds, ka mēs vēlamies izvēlēties vienu priekšmetu no sagriezēja un pēc tam otru priekšmetu no sagriezēja un pēc tam otru priekšmetu no sagriezēja. Mums nav vajadzīgas tādas kombinācijas kā ANDY, un pēc tam ANDY un BETTY, un pēc tam ANDY un CHARLIE, vai ne? Tas ir ārā. Es tikai taisīšu vienu priekšmetu no katra sagriezēja. Jā, jā, jā. Tā tas notiks. Perfekti, es teicu. Tātad, pasakiet man to, izvēlieties katru sagriezēju, dodieties uz SLICER TOOLS, OPTIONS un dodieties uz SLICER SETTINGS. Mēs tikko to izdarījām pirms 2 epizodēm. Vai tas nav traki? NOSAUKUMS, KO LIETOT FORMULĀS, un es zinu, ka tas ir SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,labi? Tātad, es domāju, ka esmu to ieguvis.
Tagad mēs šeit pāriet uz VBA un, starp citu, pārliecinieties, vai esat saglabāts kā xlsm, un pārliecinieties, vai jūsu makro drošība ir iestatīta makro atļaušanai. Ja tas ir saglabāts kā xlsx, ticiet man, jums ir jādara FILE, SAVE AS, jūs zaudēsiet visu savu darbu, ja atstāsit to kā xlsx. Jā, 99,9% no jūsu izmantotajām izklājlapām ir xlsx, taču šī ar makro nedarbosies. ALT + F11. Labi, tāpēc šeit ir kods.
Mēs atradīsim trīs slicer kešatmiņas, vienu slicer priekšmetu un 3 diapazonus. Katrai no sadalītāja kešatmiņām mēs to iestatīsim uz nosaukumu, kas izmantots formulā, kuru es tikko parādīju dialoglodziņā SLICER SETTINGS. Tātad mums ir trīs no tiem. Es gribu notīrīt visus tos, lai pārliecinātos, ka esam atgriezušies pie visa atlasītā. Šis skaitītājs faila nosaukumā tiks izmantots vēlāk.
Labi. Tagad šī nākamā sadaļa, IZLABOJOTIES PAREIZI, VEICINA TRĪS VISU GABALĒTĀJU STATISKO SARAKSTU. Skatiet 2. izrādi, lai uzzinātu, kādēļ šai trakulībai bija jānotiek. Tāpēc es izdomāšu, kur atrodas nākamā pieejamā kolonna. Pārsniedziet 2 pēdējās no pēdējās kolonnas, atcerieties to, lai es vēlāk varētu izdzēst saturu un pēc tam katram SI slicer priekšmetam SC1. SLICERITEMS, mēs rakstīsim šo sadalītāja parakstu izklājlapā. Kad esam pabeiguši visus šos sagriezējus, noskaidrojiet, cik daudz rindu mums šodien bija, un pēc tam nosauciet šo diapazonu kā SLICERITEMS1. Mēs atkārtosim visu šo lietu sadalītāja kešatmiņā 2, pārejot pa vienu kolonnu, SLICERITEMS2 un SLICERITEMS3.
Ļaujiet man jums parādīt, kā tas izskatās šajā brīdī. Tātad, es šeit ievietošu pārtraukuma punktu, un mēs palaidīsim šo kodu. Labi. Tas bija ātri. Mēs pārejam uz VBA, un tālu šeit, labajā pusē, es saņemšu 3 jaunus sarakstus. Šie saraksti ir viss, kas atrodas sadalītājā, un redzat, ka labi tos sauc par SLICERITEMS1, SLICERITEMS2 un SLICERITEMS3? Mēs no tā atbrīvosimies beigās, bet tas mums dod kaut ko pa ceļam. Atpakaļ uz VBA.
Labi. Mēs pārskatīsim visus SLICERITEMS1 vienumus, notīriet slicer cache 1 filtru un pēc tam mēs veiksim, pa vienam, caur katru slicer elementu un redzēsim, vai šis slicer elements ir = šim CELL1.VALUE, un atkal mēs atrodamies katrā no šīm vērtībām. Tātad, pirmo reizi tas notiks ANDY un pēc tam BETTY un, jūs zināt, un tā tālāk.
Tas ir nomākta. Es nevarēju atrast veidu, kā visus izsmalcinātājus izslēgt uzreiz. Es pat mēģināju ierakstīt kodu un izvēlēties vienu sagriezēju, un ierakstītais kods atgrieza 9 sagriezējus un ieslēdza vienu sagriezēju, labi? Tik nomākta, ka es nevarēju atrast neko labāku par to, bet es nevarēju atrast neko labāku par to.
Tātad, mēs iestatījām pirmo griezēju = uz ANDY. Tad mēs ejam cauri, un otrajam griezējam mēs to iestatīsim = uz pirmo vienumu. Trešajam griezējam iestatiet to = uz pirmo vienumu.
Labi. Tad šeit lejā izlemiet, vai tas ir derīgs apvienojums. Man jums jāpaskaidro, kāpēc tas ir svarīgi. Ja mēs, kā cilvēki, to darām, ANDY, mēs neizvēlētos A52, jo tas acīmredzami ir pelēks, bet makro būs pārāk stulbs, un tas izvēlēsies A52 un tad 104, un tas radīs šo tukšo pagrieziena galds. Tātad, šeit ir tūkstoš iespējamo kombināciju. Es zinu, ka ir tikai 400 iespējamie ziņojumi. To man teica cilvēks, un tāpēc mēs 600 reizes saņemsim vietu, kur izveidosim šī (neglītā - 04:45) pārskata PDF failu.
Tātad, ko es darīšu, es šeit apskatīšu cilni ANALĪZE - 2010. gadā to sauca OPTIONS - un redzēšu, kā sauc šo rakurstabulu, un es vēlos redzēt, cik rindu mēs saņemam. Manā gadījumā, ja man ir 2 rindas, es zinu, ka tas ir pārskats, kuru nevēlos eksportēt. Ja es saņemu vairāk nekā 2 rindas, 3, 4, 5, 6, tad es zinu, ka tas ir pārskats, kuru es patiešām vēlos eksportēt. Jums savā situācijā būs jāizdomā, kāda tā ir.
Alright. So, that’s why we're checking to see if the pivot table 2 and, that's the name that was back there in the ribbon, .TABLERANGE2.ROWS.COUNT is> 2. If it's not> 2, we don't want to create a PDF, alright? So, this IF statement down to this END IF is saying we're only going to create the PDFs for the report combinations that have values. MYFILENAME, I created a folder called C:REPORTS. It's just an empty folder. C:REPORTS. You make sure that you have a folder and use the same folder name in the macro. C:REPORTS/ and the name of the file is going to be REPORT001.PDF. Now, counter we initialized back up there is 1 using FORMAT, which is equivalent in Excel to saying the text of counter, and 000. That way, I'm going to get 001, then 002, then 003, and then 004. They're going to sort correctly. If I had just called this REPORT1, and then later on I have a REPORT10 and 11, and later on REPORT100, those are all going to sort together when they don't belong together, alright? So, creating the name of the file in case the file exists from the last time we ran this, we're going to kill it. In other words, delete it. Of course, if you try and kill a file that's not there, they will throw an error. So, if we get an error in the next line, that's fine. Just go on, but then I reset the error checking ON ERROR GOTO 0.
Here’s the ACTIVE SHEET, EXPORT AS FIXED FORMAT, as a PDF, there's the file name, all those choices, and then I increment the counter, so that way, next time we find one that has records, we will be creating REPORT002.PDF. Finish those three loops and then CLEAR OUT THE STATIC LISTS. So, I'll remember which column we were, resize 1 row, 3 columns, ENTIRECOLUMN.CLEAR, and then a nice little message box there to show that things have been created. Okay. Let's run it.
Alright. Now, what should be happening here is if we go and look in Windows Explorer, there it is. Okay. It is creating… like, every second, we're getting 2 or 3 or 4 or more. I'll pause this and let it run. Alright. There we are. 326 reports have been created. It looped through all 1000 possibilities and only kept the ones where there was an actual result. Alright, from 9:38 until 9:42, 4 minutes to do all that, but still faster than doing the 400, alright?
Alright. So, that's the macro way to do this. The other thing that struck me here that it may or may not work. It's really tough to say. Let's take our data and I'm going to move the data to a brand new workbook. MOVE OR COPY, CREATE A COPY, to a NEW BOOK, click OK, and we’re going to use a trick here that I first learned from Szilvia Juhasz - a great Excel consultant out in Southern California -- and we're going to add a KEY field here. The KEY field is = REVIEWER & ANTENNA & DISCIPLINE. We'll copy that down and we'll insert a new pivot table. Click OK, and we're going to take that field, the KEY field, and move it up to the old-fashioned FILTERS, and then let's see. (Let's dispel a little report here with - 08:30) REVIEWER, ANTENNA, DISCIPLINE, and REVENUE, like that.
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.
Un trešais iznākums, labi? Tas ir tas, kas ir traks. Ja es vēlos ierakstīt makro, ja es vēlos (uzrakstīt makro - 13:35) izvēlēties tikai vienu vienumu, izdomājiet, kā to izdarīt, izmantojot DEVELOPER, RECORD MACRO, HOWTOCHOOSEONEITEMFROMSLICER, noklikšķiniet uz Labi, un mēs vienkārši izvēlamies vienu lieta. FLO. Noklikšķiniet uz Apturēt ierakstu ierakstīšanu, tad mēs ejam ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER, REDIĢĒJIET to un, protams, viņi FLO TRUE un visi citi FLASE. Tas nozīmē, ja man būtu griezējs ar 100 vienībām, viņiem būtu jāievieto 100 koda rindas, lai atceltu visu pārējo. Šķiet neticami neefektīva, bet jūs tur esat.
Lejupielādēt failu
Lejupielādējiet faila paraugu šeit: Podcast2106.xlsx
