Редовните филтри на обобщената таблица предлагат страниците Показване на всички филтри за отчети, но Slicers не поддържат тази функция. Днес някои VBA могат да променят всички възможни комбинации на слайсери.
Гледам видео
Видео транскрипция
Научете Excel от, Подкаст Епизод 2106: Създайте PDF от всяка комбинация от 3 слайсера.
Какъв страхотен въпрос имаме днес. Някой пише, иска да знае дали е възможно. В момента те имат 3 резеца, работещи с обобщена таблица. Не знам как изглежда обобщената таблица. Това е поверително. Нямам право да го виждам, така че просто предполагам, нали? И така, това, което правят е, че избират по един елемент от всяка машина за нарязване и след това създават PDF файл, след което отиват и избират следващия елемент и създават PDF файл, след това следващия елемент и следващия елемент и можете да представете си, с 400 комбинации на слайсери това може да отнеме вечно и те казаха, има ли някакъв начин програма да премине и да прегледа всички опции?
Казах, добре, ето няколко квалифициращи въпроса. Първо, ние не сме на Mac, нали? Не Android, не Excel за iPhone. Това е Excel за Windows. Да, казаха те. Страхотен. Казах, че вторият наистина важен въпрос е, че искаме да изберем единия елемент от нарязващото устройство и след това накрая другия елемент от нарязващото устройство и след това другия елемент от нарязващото устройство. Нямаме нужда от комбинации като ANDY, а след това ANDY и BETTY, а след това ANDY и CHARLIE, нали? Това е навън. Просто ще направя по един елемент от всеки резач. Да да да. Така ще тръгне. Перфектно, казах. Така че тук, кажете ми това, изберете всеки слайсър, отидете на ПОЛЪЗАТЕЛНИ ИНСТРУМЕНТИ, ОПЦИИ и отидете на НАСТРОЙКИ НА СЛИСЕР. Току-що направихме това преди 2 епизода. Това не е ли лудост? ИМЕ ЗА ИЗПОЛЗВАНЕ В ФОРМУЛИ и знам, че е SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,добре? Така че, мисля, че го имам.
Сега ще преминем към VBA тук и между другото се уверете, че сте запазени като xlsm и се уверете, че защитата на макросите ви е настроена да позволява макроси. Ако е запазено като xlsx, повярвайте ми, трябва да отидете да направите ФАЙЛ, СПАСЕТЕ КАТО, ще загубите цялата си работа, ако я оставите като xlsx. Да, 99,9% от електронните таблици, които използвате, са xlsx, но тази с макрос няма да работи. ALT + F11. Добре, ето кода.
Ще намерим три кеша за нарязване, един елемент за нарязване и 3 диапазона. За всеки от кешовете на среза ще го зададем на името, използвано във формулата, което току-що ви показах в диалоговия прозорец СЛИЦЕР НАСТРОЙКИ. И така, имаме трите от тях. Искам да изчистя всички, за да се уверя, че сме се върнали към всичко, което е избрано. Този брояч ще бъде използван в името на файла по-късно.
Добре. Сега, този следващ раздел тук, НАДЯСНО, СГРАДИ ТРИ СТАТИЧНИ СПИСЪКА НА ВСИЧКИ ПО-ПЛОСКИ ПРЕДМЕТИ. Вижте изход №2, за да разберете защо тази лудост трябваше да се случи. Така че ще разбера къде е следващата налична колона, нещо като преминете над 2 от последната колона, не забравяйте, че за да мога да изтрия нещата по-късно и след това, за всеки SI, елемент за нарязване, В SC1.SLICERITEMS, ще напишем този надпис на слайсър в електронната таблица. Когато приключим с всички тези елементи за нарязване, разберете колко реда сме имали днес и след това наречете този диапазон като SLICERITEMS1. Ще повторим всичко това за кеш 2 на слайсър, преминавайки през 1 колона, SLICERITEMS2 и SLICERITEMS3.
Позволете ми да ви покажа как изглежда в този момент. И така, ще сложа точка на прекъсване точно тук и ще пуснем този код. Добре. Това беше бързо. Ще преминем към VBA и далеч тук отдясно ще получа 3 нови списъка. Тези списъци са всичко, което е в среза и виждате, че се нарича SLICERITEMS1, SLICERITEMS2 и SLICERITEMS3, нали? Ще се отървем от това накрая, но това ни дава нещо, през което да преминем. Обратно към VBA.
Добре. Ще преминем през всички елементи в SLICERITEMS1, ще изчистим филтъра за кеша на слайсери 1 и след това ще преминем един по един през всеки елемент на слайсър и ще видим дали този елемент на слайсър е = към това CELL1.VALUE и отново повтаряме всяка от стойностите. Така че, за първи път, това ще бъде ANDY и след това BETTY и, знаете ли, и така нататък.
Това е разочароващо. Не можах да намеря начин да изключа наведнъж всички ножове. Дори се опитах да запиша кода и да избера един слайсър, а записаният код връщаше 9 слайсера и включваше един слайсър, нали? Толкова разочароващо, че не можах да намеря нищо по-добро от това, но не можах да намеря нищо по-добро от това.
И така, поставяме първия слайсър = на ANDY. След това преминаваме и за втория слайсър ще го зададем = на първия елемент. За третия резач задайте = на първия елемент.
Добре. След това, тук долу, РЕШЕТЕ АКО ТОВА Е ВАЛИДНА КОМБИНАЦИЯ. Трябва да ви обясня защо това е важно. Ако ние, като хора, правим това, ANDY, не бихме избрали A52, защото очевидно е сиво, но макросът ще бъде твърде глупав и ще избере A52 и след това 104 и ще създаде това празно обобщена таблица. И така, тук има хиляда възможни комбинации. Знам, че има само 400 възможни доклада. Това ми каза човекът и така ще стигнем 600 пъти, където ще създадем PDF на този (грозен - 04:45) доклад.
И така, това, което ще направя, е да погледна тук в раздела ANALYZE - той се нарича OPTIONS през 2010 г. - и да видя как е името на тази обобщена таблица и искам да видя колко реда получаваме. В моя случай, ако получа 2 реда, знам, че това е отчет, който не искам да експортирам. Ако получа повече от 2 реда, 3, 4, 5, 6, тогава знам, че това е отчет, който искам да експортирам. Ще трябва да разберете във вашата ситуация коя е тя.
Добре. И така, затова проверяваме дали обобщената таблица 2 и това е името, което беше там в лентата, .TABLERANGE2.ROWS.COUNT е> 2. Ако не е> 2, не искаме да да създадете PDF, нали? И така, това изявление IF до този END IF казва, че ще създадем само PDF файловете за комбинациите от отчети, които имат стойности. MYFILENAME, създадох папка, наречена C: REPORTS. Това е просто празна папка. В: ОТЧЕТИ. Уверете се, че имате папка и използвате същото име на папка в макроса. В: ОТЧЕТИ / и името на файла ще бъде REPORT001.PDF. Сега, броячът, който инициализирахме, има 1, използвайки FORMAT, което е еквивалентно в Excel на изричането на текста на брояча и 000. По този начин ще получа 001, след това 002, след това 003 и след това 004. Те ще сортирам правилно.Ако току-що бях нарекъл този REPORT1, а след това по-късно имам REPORT10 и 11 и по-късно REPORT100, всички те ще се сортират заедно, когато не са заедно, нали? Така че, създавайки името на файла, в случай че файлът съществува от последния път, когато го стартирахме, ще го убием. С други думи, изтрийте го. Разбира се, ако се опитате да убиете файл, който го няма, те ще изхвърлят грешка. Така че, ако получим грешка в следващия ред, това е добре. Просто продължете, но след това рестартирам проверката за грешка ON ERROR GOTO 0.Разбира се, ако се опитате да убиете файл, който го няма, те ще изхвърлят грешка. Така че, ако получим грешка в следващия ред, това е добре. Просто продължете, но след това рестартирам проверката за грешка ON ERROR GOTO 0.Разбира се, ако се опитате да убиете файл, който го няма, те ще изхвърлят грешка. Така че, ако получим грешка в следващия ред, това е добре. Просто продължете, но след това рестартирам проверката за грешка ON ERROR GOTO 0.
Ето АКТИВНИЯ ЛИСТ, ИЗНОСЕТЕ КАТО ФИКСИРАН ФОРМАТ, като PDF, има името на файла, всички тези избори и след това увеличавам брояча, така че следващия път, когато намерим такъв, който има записи, ще създадем REPORT002.PDF . Завършете тези три цикъла и след това ИЗЧИСТЕТЕ СТАТИЧНИТЕ СПИСЪЦИ. И така, ще си спомня коя колона бяхме, преоразмерете 1 ред, 3 колони, ENTIRECOLUMN.CLEAR и след това хубаво малко поле за съобщение там, за да покажете, че нещата са създадени. Добре. Да го пуснем.
Добре. Сега това, което трябва да се случи тук, е ако отидем и погледнем в Windows Explorer, ето го. Добре. Създава се … като всяка секунда получаваме 2 или 3 или 4 или повече. Ще поставя на пауза това и ще го пусна. Добре. Ето ни. Създадени са 326 отчета. Той прегледа всички 1000 възможности и запази само тези, при които имаше действителен резултат. Добре, от 9:38 до 9:42, 4 минути, за да направите всичко това, но все пак по-бързо от това на 400, нали?
Добре. Това е макро начинът да направите това. Другото нещо, което ме порази тук, че може и да не работи. Наистина е трудно да се каже. Нека вземем данните си и ще преместя данните в чисто нова работна книга. ПРЕМЕСТЕТЕ ИЛИ КОПИРЕТЕ, СЪЗДАЙТЕ КОПИЯ, до НОВА КНИГА, щракнете върху OK и ще използваме трик тук, който за първи път научих от Силвия Юхас - страхотен консултант на Excel в Южна Калифорния - и ще добавете КЛЮЧОВО поле тук. Полето КЛЮЧ е = ПРЕГЛЕДЪТ & АНТЕНА & ДИСЦИПЛИНА. Ще копираме това и ще вмъкнем нова обобщена таблица. Щракнете върху OK и ще вземем това поле, полето KEY, и ще го преместим до старомодните ФИЛТРИ и тогава нека видим. (Нека разсеем малък доклад тук с - 08:30) ПРЕГЛЕД, АНТЕНА, ДИСЦИПЛИНА и ПРИХОДИ, така.
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.
И третият изход, нали? Това е този, който е луд. Ако искам да запиша макрос, ако искам (да напиша макрос - 13:35) да избера само един елемент, измислете как да го направите, като използвате DEVELOPER, RECORD MACRO, HOWTOCHOOSEONEITEMFROMSLICER, щракнете върху OK и ние просто избираме един вещ. ФЛО. Щракнете върху СТОП НА ЗАПИСВАНЕТО, след което отиваме ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER, РЕДАКТИРАНЕ и, разбира се, те правят FLO TRUE и след това всички останали FLASE. Това означава, че ако имах слайсър със 100 елемента в него, те ще трябва да поставят 100 реда код там, за да премахнат всичко останало. Изглежда невероятно неефективно, но там сте.
Свали файл
Изтеглете примерния файл тук: Podcast2106.xlsx
