Zwykłe filtry tabel przestawnych oferują strony Pokaż wszystkie filtry raportów, ale fragmentatory nie obsługują tej funkcji. Obecnie niektóre VBA przechodzą przez wszystkie możliwe kombinacje fragmentatorów.
Obejrzyj wideo
Transkrypcja wideo
Learn Excel From, Podcast, odcinek 2106: Utwórz plik PDF każdej kombinacji 3 fragmentatorów.
Co za wspaniałe pytanie dzisiaj. Ktoś napisał, chciał wiedzieć, czy to możliwe. W tej chwili mają 3 fragmentatory obsługujące tabelę przestawną. Nie wiem, jak wygląda tabela przestawna. To poufne. Nie mogę tego zobaczyć, więc tylko zgaduję, prawda? Tak więc wybierają jeden element z każdego fragmentatora, a następnie tworzą plik PDF, a następnie wybierają następny element i tworzą plik PDF, a następnie następny element i następny element, i możesz wyobraź sobie, że przy 400 kombinacjach fragmentatorów może to zająć wieczność, a oni powiedzieli, czy jest jakiś sposób, aby program przeszedł przez wszystkie opcje i przeszedł przez pętlę?
Odpowiedziałem, w porządku, oto kilka pytań kwalifikujących. Po pierwsze, nie korzystamy z komputera Mac, prawda? Nie Android, nie Excel dla iPhone'a. To jest Excel dla Windows. Tak, powiedzieli. Świetny. Powiedziałem, drugie naprawdę ważne pytanie polega na tym, że chcemy wybrać jeden przedmiot z krajalnicy, a następnie ostatecznie drugi element z krajalnicy, a następnie drugi element z krajalnicy. Nie potrzebujemy kombinacji takich jak ANDY, potem ANDY i BETTY, a potem ANDY i CHARLIE, prawda? To się skończyło. Zamierzam zrobić po jednym elemencie z każdego fragmentatora. Tak tak tak. Tak to będzie wyglądać. Doskonale, powiedziałem. Więc powiedz mi to, wybierz każdą krajalnicę, przejdź do KRAJARKI, OPCJE i przejdź do USTAWIEŃ KRAJARKI. Właśnie zrobiliśmy to 2 odcinki temu. Czy to nie jest szalone? NAZWA DO UŻYCIA W FORMUŁACH i wiem, że to SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,w porządku? Więc myślę, że mam to.
Teraz przejdziemy tutaj na VBA, a przy okazji upewnij się, że jesteś zapisany jako xlsm i upewnij się, że zabezpieczenia makr są ustawione tak, aby zezwalały na makra. Jeśli jest zapisany jako xlsx, zaufaj mi, musisz zrobić PLIK, ZAPISZ JAKO, stracisz całą swoją pracę, jeśli zostawisz ją jako xlsx. Tak, 99,9% arkuszy kalkulacyjnych, których używasz, to xlsx, ale ten z makrem nie będzie działać. ALT + F11. W porządku, więc oto kod.
Znajdziemy trzy zasobniki fragmentatorów, jeden przedmiot fragmentatora i 3 zakresy. Dla każdej pamięci podręcznej fragmentatora nadamy jej nazwę użytą w formule, którą właśnie pokazałem w oknie dialogowym USTAWIENIA KRAJARKI. Mamy więc trzy z nich. Chcę wyczyścić je wszystkie, aby mieć pewność, że wrócimy do wszystkiego, co zostało wybrane. Ten licznik będzie później używany w nazwie pliku.
W porządku. Teraz, w tej następnej sekcji, OD PRAWO, STWÓRZ TRZY STATYCZNE LISTY WSZYSTKICH ELEMENTÓW KROJARKI. Zobacz wyjście nr 2, aby zobaczyć, dlaczego to szaleństwo musiało się wydarzyć. Więc mam zamiar dowiedzieć się, gdzie jest następna dostępna kolumna, w pewnym sensie przejdź przez 2 z ostatniej kolumny, pamiętaj, żebym mógł później usunąć rzeczy, a następnie, dla każdego elementu fragmentatora SI, w SC1.SLICERITEMS, mamy zamiar napisać ten podpis fragmentatora do arkusza kalkulacyjnego. Kiedy skończymy z tymi wszystkimi elementami fragmentatora, dowiedz się, ile wierszy mieliśmy dzisiaj, a następnie nazwij ten zakres jako SLICERITEMS1. Powtórzymy to wszystko dla pamięci podręcznej fragmentatora 2, przechodząc przez 1 kolumnę, SLICERITEMS2 i SLICERITEMS3.
Pokażę ci, jak to wygląda w tym momencie. Więc wstawię tutaj punkt przerwania i uruchomimy ten kod. W porządku. To było szybkie. Zamierzamy przełączyć się na VBA i daleko tutaj, po prawej stronie, otrzymam 3 nowe listy. Te listy to wszystko, co znajduje się we fragmentatorze, i widzisz, że nazywa się SLICERITEMS1, SLICERITEMS2 i SLICERITEMS3, dobrze? Pozbędziemy się tego na końcu, ale to daje nam coś do zapętlenia. Powrót do VBA.
W porządku. Zamierzamy przejrzeć wszystkie elementy w SLICERITEMS1, wyczyścić filtr pamięci podręcznej fragmentatora 1, a następnie przejdziemy pojedynczo przez każdy element fragmentatora i sprawdzimy, czy ten element fragmentatora jest równy temu CELL1.VALUE i ponownie przeglądamy każdą z wartości. A więc za pierwszym razem będzie to ANDY, potem BETTY i, no wiesz, i tak dalej.
To frustrujące. Nie mogłem znaleźć sposobu, aby wyłączyć wszystkie krajalnice na raz. Próbowałem nawet nagrać kod i wybrać jeden fragmentator, a zarejestrowany kod wyłączał 9 fragmentatorów i włączał jeden fragmentator, dobrze? Tak frustrujące, że nie mogłem znaleźć nic lepszego, ale nie mogłem znaleźć nic lepszego.
Więc ustawiamy pierwszy fragmentator = na ANDY. Następnie przechodzimy przez i dla drugiego fragmentatora ustawimy go = na pierwszy element. W przypadku trzeciego fragmentatora ustaw go = na pierwszy element.
W porządku. Następnie, na dole, zdecyduj, czy to jest poprawna kombinacja. Muszę ci wyjaśnić, dlaczego to ważne. Jeśli my, jako ludzie, robimy to, ANDY, nie wybralibyśmy A52, ponieważ jest wyraźnie wyszarzone, ale makro będzie zbyt głupie i wybierze A52, a następnie 104, i stworzy to puste Stół obrotowy. Tak więc jest tu tysiąc możliwych kombinacji. Wiem, że jest tylko 400 możliwych zgłoszeń. Tak powiedziała mi osoba, a więc 600 razy będziemy tworzyć plik PDF tego (brzydkiego - 04:45) raportu.
A więc zajrzę tutaj do zakładki ANALIZA - nazywała się OPCJE w 2010 roku - i zobaczę, jak nazywa się ta tabela przestawna, i chcę zobaczyć, ile wierszy dostajemy. W moim przypadku, jeśli otrzymam 2 wiersze, wiem, że jest to raport, którego nie chcę eksportować. Jeśli otrzymam więcej niż 2 wiersze, 3, 4, 5, 6, to wiem, że jest to raport, który chcę wyeksportować. Będziesz musiał dowiedzieć się w swojej sytuacji, która to jest.
W porządku. Dlatego właśnie sprawdzamy, czy tabela przestawna 2 i to jest nazwa, która była tam na wstążce, .TABLERANGE2.ROWS.COUNT jest> 2. Jeśli nie jest> 2, nie chcemy utwórz plik PDF, dobrze? Tak więc to stwierdzenie JEŻELI aż do tego KONIEC JEŻELI mówi, że utworzymy pliki PDF tylko dla kombinacji raportów, które mają wartości. MYFILENAME, utworzyłem folder o nazwie C: REPORTS. To tylko pusty folder. C: RAPORTY. Upewnij się, że masz folder i używasz tej samej nazwy folderu w makrze. C: REPORTS /, a nazwa pliku będzie miała postać REPORT001.PDF. Teraz licznik, który zainicjowaliśmy z powrotem, to 1 używając FORMATU, co w Excelu jest odpowiednikiem wypowiedzenia tekstu licznika i 000. W ten sposób otrzymam 001, potem 002, potem 003 i 004. Oni zostaną poprawnie posortowane.Gdybym właśnie zadzwonił do tego RAPORTU1, a później miałbym RAPORT 10 i 11, a później RAPORT 100, to wszystko to posortuje się razem, gdy nie pasują do siebie, dobrze? Tak więc, tworząc nazwę pliku na wypadek, gdyby plik istniał od ostatniego uruchomienia, zamierzamy go zabić. Innymi słowy, usuń go. Oczywiście, jeśli spróbujesz zabić plik, którego nie ma, zgłosi błąd. Tak więc, jeśli w następnym wierszu pojawi się błąd, nie ma sprawy. Po prostu idź dalej, ale potem zresetowałem sprawdzanie błędów ON ERROR GOTO 0.Oczywiście, jeśli spróbujesz zabić plik, którego nie ma, zgłosi błąd. Tak więc, jeśli w następnym wierszu pojawi się błąd, nie ma sprawy. Po prostu idź dalej, ale potem zresetowałem sprawdzanie błędów ON ERROR GOTO 0.Oczywiście, jeśli spróbujesz zabić plik, którego nie ma, zgłosi błąd. Tak więc, jeśli w następnym wierszu pojawi się błąd, nie ma sprawy. Po prostu idź dalej, ale potem zresetowałem sprawdzanie błędów ON ERROR GOTO 0.
Oto AKTYWNY ARKUSZ, EKSPORT JAKO STAŁY FORMAT, jako PDF, jest tam nazwa pliku, wszystkie te opcje, a następnie zwiększam licznik, aby następnym razem, gdy znajdziemy taki, który ma rekordy, utworzymy REPORT002.PDF . Zakończ te trzy pętle, a następnie WYCZYŚĆ LISTY STATYCZNE. Tak więc, zapamiętam, którą kolumnę byliśmy, zmienię rozmiar 1 wiersza, 3 kolumny, ENTIRECOLUMN.CLEAR, a następnie ładne małe okno komunikatu, aby pokazać, że rzeczy zostały utworzone. W porządku. Uruchommy to.
W porządku. To, co powinno się tutaj dziać, to jeśli spojrzymy w Eksploratorze Windows, oto jest. W porządku. Tworzy… jakby w każdej sekundzie otrzymujemy 2, 3, 4 lub więcej. Zatrzymam to i pozwolę mu działać. W porządku. Tam jesteśmy. Utworzono 326 raportów. Przejrzał wszystkie 1000 możliwości i zachował tylko te, w których był rzeczywisty wynik. Dobrze, od 9:38 do 9:42, 4 minuty na to wszystko, ale nadal szybciej niż 400, dobrze?
W porządku. Więc to jest sposób na makro. Inna rzecz, która mnie tu uderzyła, to może, ale nie musi, działać. Naprawdę trudno powiedzieć. Weźmy nasze dane, a zamierzam przenieść je do zupełnie nowego skoroszytu. PRZENIEŚ LUB KOPIUJ, UTWÓRZ KOPIĘ, DO NOWEJ KSIĄŻKI, kliknij OK, a my użyjemy tutaj sztuczki, której nauczyłem się od Szilvii Juhasz - świetnego konsultanta Excela z Południowej Kalifornii - i zamierzamy dodaj tutaj pole KEY. Pole KLUCZ to = RECENZENT I ANTENA I DYSCYPLINA. Skopiujemy to i wstawimy nową tabelę przestawną. Kliknij OK, a my weźmiemy to pole, pole KEY, i przeniesiemy je do staromodnych FILTRÓW, a potem zobaczmy. (Rozwiążmy tutaj mały raport z - 08:30) RECENZENT, ANTENA, DYSCYPLINA i PRZYCHODY, w ten sposób.
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 trzeci wypad, dobrze? To jest ten, który jest szalony. Jeśli chcę nagrać makro, jeśli chcę (napisać makro - 13:35) wybrać tylko jedną pozycję, wymyśl, jak to zrobić za pomocą DEVELOPER, RECORD MACRO, HOWTOCHOOSEONEITEMFROMSLICER, kliknij OK i po prostu wybierz jedną pozycja. FLO. Kliknij ZATRZYMAJ NAGRYWANIE, a następnie przechodzimy ALT + F8, JAK WYBRAĆ JEDEN ELEMENTFROMSLICER, EDYTUJ to i, oczywiście, robią FLO TRUE, a następnie wszyscy inni FLASE. Oznacza to, że gdybym miał fragmentator zawierający 100 elementów, musieliby umieścić tam 100 wierszy kodu, aby odznaczyć wszystko inne. Wydaje się niewiarygodnie nieefektywne, ale tak jest.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2106.xlsx
