Обычные фильтры сводной таблицы предлагают страницы фильтров Показать все отчеты, но срезы не поддерживают эту функцию. Сегодня некий VBA перебирает все возможные комбинации слайсеров.
Смотреть видео
Стенограмма видео
Учитесь Excel из эпизода подкаста 2106: создайте PDF-файл с каждой комбинацией из трех слайсеров.
Какой замечательный вопрос у нас сегодня. Кто-то написал, хотел узнать, возможно ли это. Прямо сейчас у них есть 3 слайсера, управляющих сводной таблицей. Я не знаю, как выглядит сводная таблица. Это конфиденциально. Мне не разрешено это видеть, поэтому я просто догадываюсь, верно? Итак, они выбирают по одному элементу из каждого слайсера и затем создают PDF-файл, а затем выбирают следующий элемент и создают PDF-файл, а затем следующий элемент и следующий элемент, и вы можете представьте, с 400 комбинациями слайсеров это может занять вечность, и они сказали, есть ли способ заставить программу пройти и перебрать все параметры?
Я сказал, хорошо, вот несколько уточняющих вопросов. Номер один, мы не на Mac, верно? Ни Android, ни Excel для iPhone. Это Excel для Windows. Да, сказали они. Отлично. Я сказал, второй действительно важный вопрос: мы хотим выбрать один элемент из слайсера, а затем, в конечном итоге, другой элемент из слайсера, а затем другой элемент из слайсера. Нам не нужны комбинации типа ЭНДИ, затем ЭНДИ и БЕТТИ, а затем ЭНДИ и ЧАРЛИ, верно? Это вышло. Я просто собираюсь сделать по одному элементу из каждого слайсера. Да, да, да. Так оно и будет. - Отлично, - сказал я. Так вот, скажите мне это, выберите каждый слайсер, перейдите в ИНСТРУМЕНТЫ, ОПЦИИ и перейдите в НАСТРОЙКИ SLICER. Мы только что сделали это 2 эпизода назад. Разве это не безумие? ИМЯ ДЛЯ ИСПОЛЬЗОВАНИЯ В ФОРМУЛАХ, и я знаю, что это SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,хорошо? Итак, я думаю, что понял.
Теперь мы собираемся переключиться на VBA и, кстати, убедитесь, что вы сохранены как xlsm, и убедитесь, что ваша безопасность макросов разрешена для макросов. Если он сохранен как xlsx, поверьте мне, вам нужно сделать ФАЙЛ, СОХРАНИТЬ КАК, вы потеряете всю свою работу, если оставите его как xlsx. Да, 99,9% таблиц, которые вы используете, имеют формат xlsx, но эта с макросом работать не будет. ALT + F11. Хорошо, вот код.
Мы собираемся найти три кэша среза, один элемент среза и 3 диапазона. Для каждого из кешей слайсера мы собираемся установить его имя, используемое в формуле, которую я только что показал вам в диалоговом окне НАСТРОЙКИ СЛАЙСЕРА. Итак, у нас их трое. Я хочу очистить все это, чтобы убедиться, что мы вернулись ко всему, что было выбрано. Этот счетчик будет использоваться в имени файла позже.
Хорошо. Теперь, в следующем разделе, НАПРАВО, СОЗДАЙТЕ ТРИ СТАТИЧЕСКИХ СПИСКА ВСЕХ ПРЕДМЕТОВ SLICER. Посмотрите отрывок №2, чтобы понять, почему произошло это безумие. Итак, я собираюсь выяснить, где находится следующий доступный столбец, как бы перейдя на 2 из последнего столбца, помните, что я могу удалить материал позже, а затем для каждого элемента SI, среза IN 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) отчета.
Итак, что я собираюсь сделать, так это взглянуть на вкладку АНАЛИЗ - в 2010 году она называлась ОПЦИИ - и посмотреть, как называется эта сводная таблица, и я хочу увидеть, сколько строк мы получаем. В моем случае, если я получаю 2 строки, я знаю, что это отчет, который я не хочу экспортировать. Если я получаю более 2 строк, 3, 4, 5, 6, то я знаю, что это отчет, который я действительно хочу экспортировать. В вашей ситуации вам придется выяснить, что это такое.
Хорошо. Итак, вот почему мы проверяем, является ли сводная таблица 2 и имя, которое было там на ленте, .TABLERANGE2.ROWS.COUNT,> 2. Если это не> 2, мы не хотим создать PDF, хорошо? Итак, этот оператор IF вплоть до этого END IF говорит, что мы собираемся создавать PDF-файлы только для тех комбинаций отчетов, которые имеют значения. MYFILENAME, я создал папку под названием C: REPORTS. Это просто пустая папка. C: ОТЧЕТЫ. Убедитесь, что у вас есть папка, и в макросе используется то же имя папки. 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, вот оно. Ладно. Это создает … как будто каждую секунду мы получаем 2, 3, 4 или больше. Я приостановлю это и позволю ему поработать. Хорошо. Вот и мы. Создано 326 отчетов. Он перебрал все 1000 возможностей и сохранил только те, где был реальный результат. Хорошо, с 9:38 до 9:42, 4 минуты на все это, но все равно быстрее, чем делать 400, хорошо?
Хорошо. Итак, это макро-способ сделать это. Еще меня поразило то, что это может работать, а может и не работать. Сложно сказать. Давайте возьмем наши данные, и я собираюсь переместить их в новую книгу. ПЕРЕМЕСТИТЕ ИЛИ КОПИРУЙТЕ, СОЗДАЙТЕ КОПИЮ в НОВУЮ КНИГУ, нажмите ОК, и мы собираемся использовать здесь трюк, который я впервые узнал от Сильвии Джухас, отличного консультанта по Excel из Южной Калифорнии, и мы собираемся добавьте сюда поле KEY. Поле KEY = РЕЦЕНЗЕНТ, АНТЕННА И ДИСЦИПЛИНА. Мы скопируем это и вставим новую сводную таблицу. Нажмите ОК, и мы возьмем это поле, поле 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, и мы просто выберем один предмет. FLO. Нажмите ОСТАНОВИТЬ ЗАПИСЬ, затем нажмите ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER, ИЗМЕНИТЕ это, и, конечно же, они сделают FLO TRUE, а затем все остальные FLASE. Это означает, что если бы у меня был слайсер со 100 элементами, им пришлось бы вставить туда 100 строк кода, чтобы отменить выбор всего остального. Кажется невероятно неэффективным, но вот вы где.
Скачать файл
Загрузите образец файла здесь: Podcast2106.xlsx
