Pravidelné filtry kontingenční tabulky nabízejí stránky Zobrazit vše Filtr sestav, ale průřezy tuto funkci nepodporují. Dnes některé VBA procházejí všemi možnými kombinacemi průřezů.
Sledovat video
Přepis videa
Naučte se Excel z podcastu, epizoda 2106: Vytvořte PDF každé kombinace 3 kráječů.
Jaká skvělá otázka dnes máme. Někdo napsal, chtěl vědět, jestli je to možné. Právě teď mají 3 kráječe spuštěné kontingenční tabulku. Nevím, jak vypadá kontingenční tabulka. Je to důvěrné. Nesmím to vidět, takže jen hádám, že? Takže to, co dělají, je, že si vyberou jednu položku z každého průřezu a poté vytvoří PDF, a poté zvolí další položku a vytvoří PDF, a pak další položku a další položku a můžete představte si, že se 400 kombinacemi kráječů by to mohlo trvat věčně, a oni řekli, existuje nějaký způsob, jak nechat program projít a procházet všemi možnostmi?
Řekl jsem, dobře, tady jsou některé kvalifikační otázky. Číslo jedna, nejsme na Macu, že? Ne Android, ne Excel pro iPhone. Toto je Excel pro Windows. Ano, řekli. Skvělý. Řekl jsem, že druhá opravdu důležitá otázka je, že chceme vybrat jednu položku z průřezu a nakonec druhou položku z průřezu a pak druhou položku z průřezu. Nepotřebujeme kombinace jako ANDY a potom ANDY a BETTY a pak ANDY a CHARLIE, že? To je venku. Prostě udělám jednu položku z každého kráječe. Ano ano ano. Tak to bude. Perfektní, řekl jsem. Tady, řekněte mi tohle, vyberte každý kráječ, přejděte na NÁSTROJE NASTAVENÍ, MOŽNOSTI a přejděte na NASTAVENÍ NÁSTROJE. Právě jsme to udělali před 2 epizodami. Není to šílené? JMÉNO K POUŽITÍ VE FORMULÁCH a vím, že je to SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,v pořádku? Takže si myslím, že to mám.
Nyní přejdeme na VBA a mimochodem se ujistěte, že jste uloženi jako xlsm a ujistěte se, že je vaše zabezpečení maker nastaveno tak, aby umožňovalo makra. Pokud je uložen jako xlsx, věřte mi, musíte jít udělat SOUBOR, ULOŽIT JA, ztratíte veškerou svou práci, pokud to necháte jako xlsx. Ano, 99,9% tabulek, které používáte, je xlsx, ale tato s makrem nebude fungovat. ALT + F11. Dobře, tak tady je kód.
Najdeme tři mezipaměti kráječe, jednu položku kráječe a 3 rozsahy. U každé mezipaměti průřezu ji nastavíme na název použitý ve vzorci, který jsem vám právě ukázal v dialogovém okně NASTAVENÍ NÁŘADÍ. Takže máme tři z nich. Chci všechny vymazat, abych se ujistil, že jsme zpět se vším, co je vybráno. Tento čítač bude později použit v názvu souboru.
V pořádku. Nyní, v této další části, VYPNĚTE SPRÁVNĚ, VYTVOŘTE TŘI STATICKÉ SEZNAMY VŠECH SLICEROVÝCH POLOŽEK. Podívejte se na outtake # 2, abyste zjistili, proč se toto šílenství muselo stát. Chystám se zjistit, kde je další dostupný sloupec, ale přejít na 2 z posledního sloupce, pamatujte, že to můžu později smazat, a pak pro každou SI položku slicer, IN SC1.SLICERITEMS, napíšeme tento popisek do tabulky. Až skončíme se všemi těmi průřezovými položkami, zjistíme, kolik řádků jsme dnes měli, a potom pojmenujeme tento rozsah jako SLICERITEMS1. Zopakujeme to celé pro mezipaměť slicer 2, přejdeme přes 1 sloupec, SLICERITEMS2 a SLICERITEMS3.
Ukážu vám, jak to v tuto chvíli vypadá. Takže sem vložím zarážku a spustíme tento kód. V pořádku. To bylo rychlé. Přepneme na VBA a daleko odtud na pravou stranu dostanu 3 nové seznamy. Tyto seznamy jsou vše, co je v průřezu, a uvidíte, že se jmenuje SLICERITEMS1, SLICERITEMS2 a SLICERITEMS3, dobře? Nakonec se toho zbavíme, ale to nám dá něco, čím bychom se mohli projít. Zpět na VBA.
V pořádku. Procházíme všechny položky ve SLICERITEMS1, vyčistíme filtr mezipaměti průřezu 1 a pak projdeme po jedné skrz každou položku průřezu a uvidíme, jestli je tato položka průřezu = k tomuto CELL1.VALUE a opět procházíme každou z hodnot. Takže poprvé to bude ANDY a pak BETTY a víte, atd.
Je to frustrující. Nenašel jsem žádný způsob, jak vypnout všechny kráječe najednou. Dokonce jsem se pokusil zaznamenat kód a vybrat jeden průřez a zaznamenaný kód vracel 9 průřezů a zapnul jeden průřez, dobře? Tak frustrující, že jsem nemohl najít nic lepšího než to, ale nemohl jsem najít nic lepšího než to.
Takže jsme nastavili první slicer = na ANDY. Pak projdeme a pro druhý průřez nastavíme = na první položku. U třetího průřezu jej nastavte = na první položku.
V pořádku. Pak, zde dole, ROZHODNĚTE SE, POKUD JE TO PLATNÁ KOMBINACE. Musím vám vysvětlit, proč je to důležité. Pokud to jako lidé děláme, ANDY, nevybrali bychom A52, protože jasně je zašedlé, ale makro bude příliš hloupé a zvolí A52 a pak 104 a vytvoří to prázdné kontingenční tabulka. Tady je tedy tisíc možných kombinací. Vím, že existuje jen 400 možných hlášení. To mi ten člověk řekl, a tak se dostaneme 600krát tam, kde vytvoříme PDF této (ošklivé - 04:45) zprávy.
Takže to, co se chystám udělat, je podívat se zde na kartu ANALYZE - v roce 2010 se tomu říkalo MOŽNOSTI - a zjistit, jaký je název této kontingenční tabulky, a chci vidět, kolik řádků dostaneme. V mém případě, pokud dostanu 2 řádky, vím, že je to zpráva, kterou nechci exportovat. Pokud dostanu více než 2 řádky, 3, 4, 5, 6, vím, že jde o zprávu, kterou chci exportovat. Budete muset přijít na to, v jaké situaci se nacházíte.
V pořádku. Proto kontrolujeme, zda je kontingenční tabulka 2 a to je název, který tam byl na pásu karet, .TABLERANGE2.ROWS.COUNT je> 2. Pokud není> 2, nechceme vytvořit PDF, dobře? Takže toto prohlášení IF až po tento END IF říká, že budeme vytvářet pouze soubory PDF pro kombinace sestav, které mají hodnoty. MYFILENAME, vytvořil jsem složku s názvem C: REPORTS. Je to jen prázdná složka. C: ZPRÁVY. Ujistěte se, že máte složku a v makru používáte stejný název složky. C: REPORTS / a název souboru bude REPORT001.PDF. Nyní, počitadlo, které jsme inicializovali zpět, je 1 pomocí FORMAT, což je ekvivalentní v aplikaci Excel k tomu, že říkáme text čítače, a 000. Tímto způsobem dostanu 001, pak 002, pak 003 a pak 004. Oni chystám se správně třídit.Pokud jsem právě volal tuto REPORT1, a pak později budu mít REPORT10 a 11 a později REPORT100, budou se všechny třídit společně, když k sobě nepatří, dobře? Takže vytvořením názvu souboru v případě, že soubor existuje od posledního spuštění, zabijeme ho. Jinými slovy to smažte. Samozřejmě, pokud se pokusíte zabít soubor, který tam není, způsobí chybu. Pokud se nám tedy v dalším řádku zobrazí chyba, je to v pořádku. Jen pokračujte, ale pak resetuji kontrolu chyb ON ERROR GOTO 0.Samozřejmě, pokud se pokusíte zabít soubor, který tam není, způsobí chybu. Pokud se nám tedy v dalším řádku zobrazí chyba, je to v pořádku. Jen pokračujte, ale pak resetuji kontrolu chyb ON ERROR GOTO 0.Samozřejmě, pokud se pokusíte zabít soubor, který tam není, způsobí chybu. Pokud se nám tedy v dalším řádku zobrazí chyba, je to v pořádku. Jen pokračujte, ale pak resetuji kontrolu chyb ON ERROR GOTO 0.
Tady je AKTIVNÍ LIST, EXPORTOVAT JAKO FIXOVANÝ FORMÁT, jako PDF, je zde název souboru, všechny tyto možnosti a pak zvýším počitadlo, takže tak příště najdeme ten, který má záznamy, budeme vytvářet REPORT002.PDF . Dokončete tyto tři smyčky a poté VYMAZEJTE STATICKÉ SEZNAMY. Vzpomenu si tedy, o který sloupec jsme šli, změnit velikost 1 řádku, 3 sloupce, ENTIRECOLUMN.CLEAR a potom pěkné malé okno se zprávou, které ukazuje, že věci byly vytvořeny. Dobře. Pojďme to spustit.
V pořádku. To, co by se tady mělo stát, je, když se podíváme do Průzkumníka Windows, tam to je. Dobře. Vytváří se … jako každou sekundu dostáváme 2 nebo 3 nebo 4 nebo více. Pozastavím to a nechám to běžet. V pořádku. Tady jsme. Bylo vytvořeno 326 zpráv. Prošlo všemi 1000 možnostmi a ponechalo pouze ty, kde byl skutečný výsledek. Dobře, od 9:38 do 9:42, 4 minuty na to všechno, ale stále rychlejší než dělat 400, dobře?
V pořádku. To je tedy makro způsob, jak toho dosáhnout. Další věc, která mě tady zasáhla, že to může nebo nemusí fungovat. Je opravdu těžké říci. Vezměme si svá data a já přesunu data do zcela nového sešitu. PŘESUNOUT NEBO KOPÍROVAT, VYTVOŘIT KOPÍROVAT, do NOVÉ KNIHY, kliknout na OK, a použijeme zde trik, který jsem se poprvé naučil od Szilvie Juhasz - skvělé Excel konzultantky v jižní Kalifornii - a budeme zde přidejte pole KLÍČ. Pole KLÍČ je = REVIEWER & ANTENNA & DISCIPLINE. Zkopírujeme to dolů a vložíme novou kontingenční tabulku. Klikněte na OK a vezmeme toto pole, pole KLÍČ, a přesuneme ho nahoru na staromódní FILTRY a pak se podívejme. (Rozpusťme zde malou zprávu s - 08:30) RECENZE, ANTÉNA, DISCIPLÍNA a PŘÍJMY, takhle.
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 třetí výstup, dobře? To je ten šílený. Pokud chci zaznamenat makro, pokud chci (napsat makro - 13:35) vybrat pouze jednu položku, přijít na to, jak to udělat pomocí DEVELOPER, RECORD MACRO, HOWTOCHOOSEONEITEMFROMSLICER, kliknout na OK a my jednoduše vybereme položka. FLO. Klikněte na ZASTAVIT ZÁZNAM, pak přejdeme na ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER, EDITUJEME to a určitě, udělají FLO TRUE a pak všichni ostatní FLASE. Znamená to, že kdybych měl průřez se 100 položkami, musel by tam dát 100 řádků kódu, aby zrušil výběr všeho jiného. Vypadá to neuvěřitelně neefektivně, ale tady jste.
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2106.xlsx
