Регулярні фільтри зведеної таблиці пропонують сторінки Показати всі фільтри звітів, але Зрізи не підтримують цю функцію. Сьогодні деякі VBA переглядають усі можливі комбінації нарізок.
Переглянути відео
Стенограма відео
Дізнайтеся Excel From, Підкаст Епізод 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, і, знову ж таки, ми проглядаємо кожне зі значень. Отже, перший раз це буде ЕНДІ, а потім БЕТТІ і, ви знаєте, тощо.
Це засмучує. Я не міг знайти жодного способу вимкнути всі нарізки відразу. Я навіть спробував записати код і вибрати один нарізач, а записаний код повертав 9 зрізів і вмикав один нарізач, добре? Настільки розчаровує, що я не міг знайти нічого кращого за це, але не міг знайти нічого кращого за це.
Отже, ми встановлюємо перший слайсер = 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. Це просто порожня папка. C: ЗВІТИ. Переконайтесь, що у вас є папка, і ви використовуєте те саме ім’я папки в макросі. C: ЗВІТИ / і ім'я файлу буде REPORT001.PDF. Тепер у лічильника, який ми ініціалізували, є 1 за допомогою FORMAT, що еквівалентно в Excel тексту тексту лічильника, і 000. Таким чином, я отримаю 001, потім 002, потім 003, а потім 004. Вони збираюся правильно сортувати.Якби я щойно назвав цей ЗВІТ1, а потім пізніше я маю ЗВІТ10 і 11, а пізніше ЗВІТ100, вони все будуть сортувати разом, коли вони не належать разом, добре? Отже, створюючи ім’я файлу на випадок, якщо файл існує з моменту останнього запуску, ми його вб’ємо. Іншими словами, видаліть його. Звичайно, якщо ви спробуєте вбити файл, якого немає, вони видадуть помилку. Отже, якщо ми отримаємо помилку в наступному рядку, це нормально. Просто продовжуйте, але тоді я скидаю перевірку помилок ON ERROR GOTO 0.Звичайно, якщо ви спробуєте вбити файл, якого немає, вони видадуть помилку. Отже, якщо ми отримаємо помилку в наступному рядку, це нормально. Просто продовжуйте, але тоді я скидаю перевірку помилок ON ERROR GOTO 0.Звичайно, якщо ви спробуєте вбити файл, якого немає, вони видадуть помилку. Отже, якщо ми отримаємо помилку в наступному рядку, це нормально. Просто продовжуйте, але тоді я скидаю перевірку помилок ON ERROR GOTO 0.
Ось АКТИВНИЙ ЛИСТ, ЕКСПОРТ ЯК ФІКСОВАНИЙ ФОРМАТ, як PDF, є ім’я файлу, усі ці варіанти, а потім я збільшую лічильник, таким чином, наступного разу, коли ми знайдемо такий, який має записи, ми створимо REPORT002.PDF . Закінчіть ці три петлі, а потім очистіть статистичні списки. Отже, я запам’ятаю, в якому стовпці ми були, змініть розмір 1 рядка, 3 стовпців, ENTIRECOLUMN.CLEAR, а потім гарненьке віконце повідомлення, щоб показати, що речі створені. Добре. Давайте запустимо.
Гаразд Тепер, що тут повинно відбуватися, це якщо ми зайдемо заглянути в Провідник Windows, ось воно. Добре. Це створює … як, щосекунди, ми отримуємо 2, 3 або 4 або більше. Я призупиню це і пущу. Гаразд Ось ми. Створено 326 звітів. Він прокрутив усі 1000 можливостей і зберіг лише ті, де був реальний результат. Добре, з 9:38 до 9:42, 4 хвилини, щоб зробити все це, але все-таки швидше, ніж зробити 400, добре?
Гаразд Отже, це макро спосіб зробити це. Інше, що мене тут вразило, це може працювати, а може і не працювати. Справді важко сказати. Давайте візьмемо наші дані, і я збираюся перенести дані до нової книги. ПЕРЕМІСТИТИ АБО КОПІЮВАННЯ, СТВОРИТИ КОПІЮ, до НОВОЇ КНИГИ натисніть ОК, і ми використаємо тут трюк, який я вперше дізнався від Сілвії Юхас - чудового консультанта 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, натисніть кнопку ОК, і ми просто вибираємо один пункт. ФЛО. Натисніть ЗУПИНИТЬ ЗАПИС, потім ми переходимо до ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER, РЕДАКТУЄМО, і, звичайно, вони роблять FLO TRUE, а потім всі інші FLASE. Це означає, що якби у мене був різак із 100 елементами, їм довелося б вставити туди 100 рядків коду, щоб скасувати вибір усього іншого. Здається, неймовірно неефективним, але ви там.
Завантажити файл
Завантажте зразок файлу тут: Podcast2106.xlsx
