Bežné filtre kontingenčných tabuliek ponúkajú stránky Zobraziť všetky filtre prehľadov, ale Krájače túto funkciu nepodporujú. Dnes niektoré VBA prechádzajú všetkými možnými kombináciami výrezov.
Pozeraj video
Prepis videa
Dozviete sa z Excelu, Podcast Episode 2106: Vytvorte PDF z každej kombinácie 3 krájačov.
Akú skvelú otázku dnes máme. Niekto napísal, chcel vedieť, či je to možné. Práve teraz majú 3 krájače spustené kontingenčnú tabuľku. Neviem, ako vyzerá kontingenčná tabuľka. Je to dôverné. Ja to nesmiem vidieť, takže len hádam, že? To, čo robia, je, že si vyberú jednu položku z každého výrezu a potom vytvoria PDF, a potom vyberú ďalšiu položku a vytvoria PDF, a potom ďalšiu položku a ďalšiu položku a vy môžete predstavte si, že pri 400 kombináciách krájačov by to mohlo trvať večne a povedali si, existuje nejaký spôsob, ako program prejsť a prepracovať všetky možnosti?
Dobre som povedal, tu je niekoľko kvalifikačných otázok. Číslo jedna, nie sme na Macu, že? Nie Android, nie Excel pre iPhone. Toto je Excel pre Windows. Áno, povedali. Skvelé. Povedal som, druhou skutočne dôležitou otázkou je, že chceme vybrať jednu položku z krájača a potom nakoniec druhú položku z krájača a potom druhú položku z krájača. Nepotrebujeme kombinácie ako ANDY a potom ANDY a BETTY a potom ANDY a CHARLIE, však? To je vonku. Idem urobiť jednu položku z každého krájača. Áno áno áno. Takto to bude prebiehať. Perfektné, povedal som. Takže tu, povedzte mi to, vyberte každý krájač, choďte na NÁSTROJE, MOŽNOSTI MOŽNOSTI PRECHODU a choďte na NASTAVENIA NÁDOBKY. Práve sme to urobili pred 2 epizódami. Nie je to šialené? NÁZOV NA POUŽITIE VO FORMULÁCH a viem, že je to SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,dobre? Takže si myslím, že to mám.
Teraz tu prejdeme na VBA a mimochodom sa uistite, že ste uložený ako xlsm a či je vaše zabezpečenie makier nastavené tak, aby umožňovalo makrá. Ak je uložený ako xlsx, verte mi, musíte ísť urobiť SÚBOR, ULOŽIŤ AS, stratíte všetku svoju prácu, ak to necháte ako xlsx. Áno, 99,9% tabuliek, ktoré používate, je xlsx, ale táto s makrom nebude fungovať. ALT + F11. Dobre, takže tu je kód.
Nájdeme tri kešky na krájanie, jednu položku na krájanie a 3 rozsahy. Pre každú z kešiek krájača ju nastavíme na názov použitý vo vzorci, ktorý som vám práve ukázal v dialógovom okne NASTAVENIA KROUŽKU. Takže máme tri z nich. Chcem všetkých vyčistiť, aby sa ubezpečil, že sme späť so všetkým, čo je vybrané. Toto počítadlo bude neskôr použité v názve súboru.
Dobre. Teraz v tejto nasledujúcej sekcii, ZPRÁVA, VYTVORTE TROCH STATICKÝCH ZOZNAMOV VŠETKÝCH POLOŽIEK NA PRECHOD. Pozrite si výstup č. 2, aby ste zistili, prečo sa toto šialenstvo muselo stať. Takže prídem na to, kde je ďalší dostupný stĺpec, prejdem cez 2 z posledného stĺpca, nezabudnite, že to môžem neskôr odstrániť, a potom pre každú SI položku slicer v SC1.SLICERITEMS, tento popisovač napíšeme do tabuľky. Keď skončíme so všetkými týmito položkami výrezu, zistíme, koľko riadkov sme dnes mali, a potom tento rozsah pomenujeme ako SLICERITEMS1. Zopakujeme to celé pre medzipamäť slicer 2, prejdeme cez 1 stĺpec, SLICERITEMS2 a SLICERITEMS3.
Ukážem vám, ako to v tejto chvíli vyzerá. Takže tu uvediem bod prerušenia a spustíme tento kód. Dobre. To bolo rýchle. Prejdeme na VBA a zďaleka tu na pravej strane dostanem 3 nové zoznamy. Tieto zoznamy obsahujú všetko, čo sa nachádza v zozname, a vidíte, že sa volá SLICERITEMS1, SLICERITEMS2 a SLICERITEMS3, dobre? Toho sa na konci zbavíme, ale to nám dá niečo, cez čo sa prepracovať. Späť na VBA.
Dobre. Prejdeme všetky položky v SLICERITEMS1, vyčistíme filter pre medzipamäť slicer 1 a potom prejdeme po jednom cez každú položku slicer a uvidíme, či je táto položka slicer = to CELL1.VALUE a opäť prechádzame každou z hodnôt. Takže prvýkrát to bude ANDY a potom BETTY a, viete, atď.
Je to frustrujúce. Nenašiel som žiadny spôsob, ako vypnúť všetky krájače naraz. Skúšal som dokonca zaznamenať kód a vybrať jeden krájač. Zaznamenaný kód vracal 9 krájačov a jeden krájač zapol, dobre? Tak frustrujúce, že som nemohol nájsť nič lepšie ako toto, ale nenašiel som nič lepšie ako to.
Prvý krájač = sme teda nastavili na ANDY. Potom prejdeme a pre druhý krájač ho nastavíme = na prvú položku. Pre tretí krájač nastavte = na prvú položku.
Dobre. Potom sa tu dole ROZHODNITE, AK TO JE PLATNÁ KOMBINÁCIA. Musím vám vysvetliť, prečo je to dôležité. Keby sme to ako ľudia robili, ANDY, nevybrali by sme si A52, pretože je jasne zašednuté, ale makro bude príliš hlúpe a zvolí A52 a potom 104 a vytvorí to prázdne. kontingenčná tabuľka. Existuje tu teda tisíc možných kombinácií. Viem, že existuje iba 400 možných správ. To mi ten človek povedal, a tak sa dostaneme 600-krát tam, kde vytvoríme PDF z tejto (škaredej - 04:45) správy.
Čo teda urobím, je to, že sa pozriem sem na kartu ANALÝZA - v roku 2010 sa volala MOŽNOSTI - a uvidím, aký je názov tejto kontingenčnej tabuľky, a chcem zistiť, koľko riadkov dostaneme. V mojom prípade, ak dostanem 2 riadky, viem, že ide o prehľad, ktorý nechcem exportovať. Ak dostanem viac ako 2 riadky, 3, 4, 5, 6, potom viem, že to je prehľad, ktorý chcem exportovať. Budete musieť prísť na to, v akej situácii sa nachádzate.
Dobre. Preto preto kontrolujeme, či je kontingenčná tabuľka 2 a, to je meno, ktoré sa tam na páse s nástrojmi nachádzalo, .TABLERANGE2.ROWS.COUNT je> 2. Ak nie je> 2, nechceme vytvoriť PDF, dobre? Takže toto vyhlásenie IF až po tento END IF hovorí, že vytvoríme iba súbory PDF pre kombinácie prehľadov, ktoré majú hodnoty. MYFILENAME, vytvoril som priečinok s názvom C: REPORTS. Je to iba prázdny priečinok. C: SPRÁVY. Uistite sa, že máte priečinok a v makre používate rovnaký názov priečinka. C: REPORTS / a názov súboru bude REPORT001.PDF. Teraz, počítadlo, ktoré sme inicializovali späť, je 1 pomocou FORMATU, čo je ekvivalent v Exceli s vyslovením textu počítadla, a 000. Takto dostanem 001, potom 002, potom 003 a potom 004. Oni idem triediť správne.Keby som práve volal túto SPRÁVU1 a potom neskôr budem mať SPRÁVU 10 a 11 a neskôr SPRÁVU 100, budú sa všetky triediť dokopy, keď k sebe nepatria, dobre? Takže vytvorením názvu súboru pre prípad, že by súbor existoval od posledného spustenia, ho zabijeme. Inými slovami, odstráňte ho. Samozrejme, ak sa pokúsite zabiť súbor, ktorý tam nie je, spôsobí chybu. Ak sa nám teda v ďalšom riadku zobrazí chyba, je to v poriadku. Len pokračujte, ale potom som resetoval kontrolu chýb ON ERROR GOTO 0.Samozrejme, ak sa pokúsite zabiť súbor, ktorý tam nie je, spôsobí chybu. Ak sa nám teda v ďalšom riadku zobrazí chyba, je to v poriadku. Len pokračujte, ale potom som resetoval kontrolu chýb ON ERROR GOTO 0.Samozrejme, ak sa pokúsite zabiť súbor, ktorý tam nie je, spôsobí chybu. Ak sa nám teda v ďalšom riadku zobrazí chyba, je to v poriadku. Len pokračujte, ale potom som resetoval kontrolu chýb ON ERROR GOTO 0.
Tu je AKTÍVNY LIST, EXPORTOVANÝ AKO FIXOVANÝ FORMÁT, ako PDF, je tu názov súboru, všetky tieto možnosti a potom zvýšim počítadlo, takže takým spôsobom, keď nabudúce nájdeme taký, ktorý má záznamy, vytvoríme REPORT002.PDF . Dokončite tieto tri slučky a potom VYMAZAJTE STATICKÉ ZOZNAMY. Pamätám si teda, o ktorý stĺpec sme sa jednalo, zmeniť veľkosť 1 riadku, 3 stĺpce, ENTIRECOLUMN.CLEAR a potom pekné malé okno so správou, ktoré ukazuje, že veci boli vytvorené. Dobre. Poďme to spustiť.
Dobre. Čo by sa tu malo stať, je to, že keď sa pôjdeme pozrieť do programu Windows Explorer, tam to je. Dobre. Vytvára sa to … ako každú sekundu dostávame 2 alebo 3 alebo 4 alebo viac. Toto pozastavím a nechám bežať. Dobre. Sme tu. Bolo vytvorených 326 správ. Prelistovalo všetkých 1000 možností a ponechalo iba tie, kde bol skutočný výsledok. V poriadku, od 9:38 do 9:42, 4 minúty na to všetko, ale stále rýchlejšie ako pri 400, v poriadku?
Dobre. Toto je teda makro spôsob, ako to dosiahnuť. Ďalšia vec, ktorá ma tu zarazila, že to môže, ale nemusí fungovať. Je to naozaj ťažké povedať. Zoberme si naše údaje a údaje presuniem do úplne nového zošita. PRESUNUJTE ALEBO KOPÍRUJTE, VYTVORTE KOPÍROVANIE, do NOVEJ KNIHY, kliknite na tlačidlo OK, a použijeme tu trik, ktorý som sa prvýkrát naučil od Szilvie Juhaszovej - skvelej konzultantky pre Excel v južnej Kalifornii - a chystáme sa tu pridajte KĽÚČOVÉ pole. Pole KĽÚČ je = REVIEWER & ANTENNA & DISCIPLINE. Skopírujeme to dolu a vložíme novú kontingenčnú tabuľku. Kliknite na OK, a my vezmeme toto pole, pole KĽÚČ, a presunieme ho hore k staromódnym FILTROM a potom sa pozrime. (Rozpusťme tu malú správu s - 08:30) RECENZOR, ANTÉNA, DISCIPLÍNA a PRÍJMY, podobne.
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.
A tretí výstup, dobre? To je ten blázon. Ak chcem zaznamenať makro, ak chcem (napísať makro - 13:35) zvoliť iba jednu položku, pomocou príkazov DEVELOPER, RECORD MACRO, HOWTOCHOOSEONEITEMFROMSLICER kliknúť na tlačidlo OK a jednoducho vyberieme jednu položku položka. FLO. Kliknite na ZASTAVIŤ ZÁZNAM, potom prejdeme na ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER, EDITUJEME to a určite urobia FLO TRUE a potom všetci ostatní FLASE. Znamená to, že keby som mal krájač so 100 položkami, museli by tam vložiť 100 riadkov kódu, aby zrušili výber všetkého ostatného. Zdá sa to neuveriteľne neefektívne, ale tu ste.
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2106.xlsx
