Các bộ lọc bảng tổng hợp thông thường cung cấp các trang Hiển thị Tất cả các Bộ lọc Báo cáo, nhưng các trang Slippers không hỗ trợ chức năng này. Ngày nay, một số VBA để lặp lại tất cả các kết hợp bộ cắt có thể có.
Xem video
Bản ghi video
Học Excel Từ, Podcast Tập 2106: Tạo tệp PDF của mọi sự kết hợp của 3 lát cắt.
Thật là một câu hỏi tuyệt vời mà chúng ta có ngày hôm nay. Có người viết vào, muốn biết có được không. Hiện tại, họ có 3 máy thái đang chạy một bảng xoay. Tôi không biết bảng tổng hợp trông như thế nào. Bí mật của nó. Tôi không được phép xem nó nên tôi chỉ đoán, phải không? Vì vậy, những gì họ đang làm là họ chọn một mục từ mỗi máy cắt và sau đó tạo một tệp PDF, sau đó chọn mục tiếp theo và tạo PDF, rồi đến mục tiếp theo và mục tiếp theo, và bạn có thể hãy tưởng tượng, với 400 tổ hợp máy thái, điều này có thể mất vĩnh viễn, và họ nói, có cách nào đó để một chương trình đi qua và lặp lại tất cả các tùy chọn?
Tôi đã nói, được rồi, đây là một số câu hỏi đủ điều kiện. Thứ nhất, chúng tôi không sử dụng máy Mac, phải không? Không phải Android, không phải Excel cho iPhone. Đây là Excel dành cho Windows. Có, họ nói. Tuyệt quá. Tôi đã nói, câu hỏi thực sự quan trọng thứ hai là, chúng tôi muốn chọn một mục từ máy thái, và cuối cùng là mục khác từ máy thái, và sau đó là mục khác từ máy thái. Chúng ta không cần những kết hợp như ANDY, rồi ANDY và BETTY, rồi ANDY và CHARLIE, phải không? Đó là ra. Tôi chỉ làm một mục từ mỗi máy thái. Có có có. Đó là cách nó sẽ đi. Hoàn hảo, tôi nói. Vì vậy, ở đây, hãy cho tôi biết điều này, chọn từng máy cắt, đi tới CÔNG CỤ TRƯỢT, TÙY CHỌN và đi tới CÀI ĐẶT SLICER. Chúng tôi vừa mới làm điều này 2 tập trước. Điều này không phải là điên rồ? TÊN SỬ DỤNG TRONG CÔNG THỨC và tôi biết rằng đó là SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,ổn thỏa? Vì vậy, tôi nghĩ rằng tôi đã hiểu nó.
Bây giờ, chúng ta sẽ chuyển sang VBA ở đây và nhân tiện, hãy đảm bảo rằng bạn được lưu dưới dạng xlsm và đảm bảo bảo mật macro của bạn được đặt để cho phép macro. Nếu nó được lưu dưới dạng xlsx, hãy tin tôi, bạn phải thực hiện FILE, LƯU AS, bạn sẽ mất tất cả công việc của mình nếu bạn để nó dưới dạng xlsx. Có, 99,9% bảng tính mà bạn sử dụng là xlsx nhưng bảng tính này có macro sẽ không hoạt động. ALT + F11. Được rồi, đây là mã.
Chúng ta sẽ tìm thấy ba bộ nhớ đệm của máy cắt, một mục của máy cắt và 3 phạm vi. Đối với mỗi bộ đệm của máy cắt, chúng tôi sẽ đặt nó thành tên được sử dụng trong công thức mà tôi vừa chỉ cho bạn trong hộp thoại CÀI ĐẶT SLICER. Vì vậy, chúng tôi có ba trong số đó. Tôi muốn xóa tất cả những thứ đó để đảm bảo rằng chúng tôi quay lại mọi thứ đang được chọn. Bộ đếm này sẽ được sử dụng trong tên tệp sau này.
Ổn thỏa. Bây giờ, phần tiếp theo này ở đây, TẮT BÊN PHẢI, XÂY DỰNG BA DANH SÁCH THỐNG KÊ CỦA TẤT CẢ CÁC MỤC SLICER. Xem outtake # 2 để biết tại sao sự điên rồ này phải xảy ra. Vì vậy, tôi sẽ tìm ra vị trí của cột có sẵn tiếp theo, loại bỏ qua 2 từ cột cuối cùng, hãy nhớ điều đó để tôi có thể xóa nội dung sau và sau đó, đối với mỗi mục SI, bộ cắt, TRONG SC1.SLICERITEMS, chúng ta sẽ viết chú thích của bộ cắt đó vào bảng tính. Khi chúng ta hoàn thành tất cả các mục của máy cắt đó, hãy tìm xem chúng ta có bao nhiêu hàng ngày hôm nay và sau đó đặt tên cho dải ô đó là SLICERITEMS1. Chúng ta sẽ lặp lại toàn bộ điều đó cho bộ đệm ẩn 2, đi qua 1 cột, SLICERITEMS2 và SLICERITEMS3.
Hãy để tôi cho bạn thấy nó trông như thế nào vào thời điểm này. Vì vậy, tôi sẽ đặt một điểm ngắt ngay tại đây và chúng tôi sẽ chạy đoạn mã này. Ổn thỏa. Đó là nhanh chóng. Chúng ta sẽ chuyển sang VBA, và ở phía bên tay phải, tôi sẽ nhận được 3 danh sách mới. Các danh sách này là tất cả mọi thứ có trong trình cắt và bạn thấy nó được gọi là SLICERITEMS1, SLICERITEMS2 và SLICERITEMS3, được chứ? Chúng tôi sẽ loại bỏ điều đó vào cuối, nhưng điều đó cho chúng tôi một cái gì đó để lặp lại. Quay lại VBA.
Ổn thỏa. Chúng ta sẽ lặp lại tất cả các mục trong SLICERITEMS1, xóa bộ lọc cho bộ đệm của máy cắt 1, và sau đó chúng ta sẽ đi qua từng mục một, qua từng mục của máy cắt và xem liệu mục này có = với cái này CELL1.VALUE, và một lần nữa, chúng tôi đang lặp lại từng giá trị. Vì vậy, lần đầu tiên thông qua, nó sẽ là ANDY và sau đó là BETTY và, bạn biết đấy, v.v.
Thật là bực bội. Tôi không thể tìm thấy bất kỳ cách nào để tắt tất cả các máy cắt cùng một lúc. Tôi thậm chí đã thử ghi lại mã và chọn một máy thái, và mã được ghi lại đang trả về 9 máy thái và bật một máy thái, được chứ? Bực bội đến mức không tìm được gì tốt hơn thế nhưng cũng không tìm được gì tốt hơn thế.
Vì vậy, chúng tôi đặt máy cắt đầu tiên = thành ANDY. Sau đó, chúng ta đi qua, và đối với máy cắt thứ hai, chúng ta sẽ đặt nó = thành mục đầu tiên. Đối với bộ cắt thứ ba, hãy đặt nó = thành mục đầu tiên.
Ổn thỏa. Sau đó, tại đây, QUYẾT ĐỊNH ĐÂY LÀ SỰ KẾT HỢP HỢP LỆ. Tôi phải giải thích cho bạn tại sao điều đó lại quan trọng. Nếu chúng ta, với tư cách là con người đang làm điều này, ANDY, chúng ta sẽ không chọn A52 vì rõ ràng nó bị xám, nhưng macro sẽ quá ngu ngốc và nó sẽ chọn A52 rồi đến 104, và nó sẽ tạo ra cái trống bảng tổng hợp. Vì vậy, có hàng nghìn sự kết hợp khả dĩ ở đây. Tôi biết rằng chỉ có 400 báo cáo khả thi. Đó là những gì người đó nói với tôi, và vì vậy chúng tôi sẽ gấp 600 lần nơi chúng tôi sẽ tạo một bản PDF của báo cáo (xấu xí - 04:45) này.
Vì vậy, những gì tôi sẽ làm là tôi sẽ xem ở đây trên tab ANALYZE - nó được gọi là OPTIONS vào năm 2010 - và xem tên của bảng tổng hợp này là gì và tôi muốn xem có bao nhiêu hàng chúng tôi nhận được. Trong trường hợp của tôi, nếu tôi nhận được 2 hàng, tôi biết đó là báo cáo mà tôi không muốn xuất. Nếu tôi nhận được nhiều hơn 2 hàng, 3, 4, 5, 6, thì tôi biết đó là báo cáo mà tôi muốn xuất. Bạn sẽ phải tìm ra tình huống của mình.
Ổn thỏa. Vì vậy, đó là lý do tại sao chúng tôi đang kiểm tra xem bảng tổng hợp 2 có phải là tên đã trở lại đó trong dải băng hay không, .TABLERANGE2.ROWS.COUNT là> 2. Nếu không phải> 2, chúng tôi không muốn tạo một tệp PDF được không? Vì vậy, câu lệnh IF cho đến END IF này nói rằng chúng tôi sẽ chỉ tạo các tệp PDF cho các kết hợp báo cáo có giá trị. MYFILENAME, tôi đã tạo một thư mục có tên C: REPORTS. Nó chỉ là một thư mục trống rỗng. C: BÁO CÁO. Bạn đảm bảo rằng bạn có một thư mục và sử dụng cùng một tên thư mục trong macro. C: REPORTS / và tên của tệp sẽ là REPORT001.PDF. Bây giờ, bộ đếm mà chúng tôi đã khởi tạo sao lưu có 1 bằng cách sử dụng FORMAT, tương đương trong Excel để nói văn bản của bộ đếm, và 000. Bằng cách đó, tôi sẽ nhận được 001, sau đó 002, sau đó 003 và sau đó là 004. Họ sẽ sắp xếp chính xác.Nếu tôi vừa gọi đây là BÁO CÁO1, sau đó tôi có BÁO CÁO 10 và 11, và sau đó là BÁO CÁO100, tất cả chúng sẽ được sắp xếp cùng nhau khi chúng không thuộc về nhau, được chứ? Vì vậy, tạo tên của tệp trong trường hợp tệp tồn tại từ lần cuối cùng chúng tôi chạy điều này, chúng tôi sẽ loại bỏ nó. Nói cách khác, xóa nó. Tất nhiên, nếu bạn cố gắng giết một tệp không có ở đó, chúng sẽ báo lỗi. Vì vậy, nếu chúng ta gặp lỗi ở dòng tiếp theo, điều đó không sao cả. Chỉ cần tiếp tục, nhưng sau đó tôi đặt lại kiểm tra lỗi ON ERROR GOTO 0.Tất nhiên, nếu bạn cố gắng giết một tệp không có ở đó, chúng sẽ báo lỗi. Vì vậy, nếu chúng ta gặp lỗi ở dòng tiếp theo, điều đó không sao cả. Chỉ cần tiếp tục, nhưng sau đó tôi đặt lại kiểm tra lỗi ON ERROR GOTO 0.Tất nhiên, nếu bạn cố gắng giết một tệp không có ở đó, chúng sẽ báo lỗi. Vì vậy, nếu chúng ta gặp lỗi ở dòng tiếp theo, điều đó không sao cả. Chỉ cần tiếp tục, nhưng sau đó tôi đặt lại kiểm tra lỗi ON ERROR GOTO 0.
Đây là ACTIVE SHEET, XUẤT NHƯ ĐỊNH DẠNG CỐ ĐỊNH, dưới dạng PDF, có tên tệp, tất cả các lựa chọn đó và sau đó tôi tăng bộ đếm, theo cách đó, lần tới khi chúng tôi tìm thấy một tệp có bản ghi, chúng tôi sẽ tạo REPORT002.PDF . Kết thúc ba vòng đó và sau đó XÓA DANH SÁCH THỐNG KÊ. Vì vậy, tôi sẽ nhớ chúng ta đang ở cột nào, thay đổi kích thước 1 hàng, 3 cột, ENTIRECOLUMN.CLEAR, và sau đó một hộp thông báo nhỏ xinh xắn ở đó để cho biết rằng mọi thứ đã được tạo. Được chứ. Hãy chạy nó.
Ổn thỏa. Bây giờ, những gì sẽ xảy ra ở đây là nếu chúng ta đi và xem xét trong Windows Explorer, nó đang ở đó. Được chứ. Nó đang tạo ra… giống như mỗi giây, chúng tôi nhận được 2 hoặc 3 hoặc 4 hoặc nhiều hơn. Tôi sẽ tạm dừng việc này và để nó chạy. Ổn thỏa. Đây rồi. 326 báo cáo đã được tạo. Nó lặp lại tất cả 1000 khả năng và chỉ giữ lại những khả năng có kết quả thực tế. Được rồi, từ 9:38 đến 9:42, 4 phút để làm tất cả điều đó, nhưng vẫn nhanh hơn làm 400, được chứ?
Ổn thỏa. Vì vậy, đó là cách vĩ mô để làm điều này. Điều khác khiến tôi ấn tượng ở đây là nó có thể hoạt động hoặc có thể không hoạt động. Nó thực sự khó để nói. Hãy lấy dữ liệu của chúng tôi và tôi sẽ chuyển dữ liệu sang một sổ làm việc hoàn toàn mới. CHUYỂN HOẶC SAO CHÉP, TẠO BẢN SAO, sang SÁCH MỚI, nhấp vào OK, và chúng tôi sẽ sử dụng một mẹo ở đây mà tôi đã học được lần đầu tiên từ Szilvia Juhasz - một nhà tư vấn Excel giỏi ở Nam California - và chúng tôi sẽ thêm trường KEY ở đây. Trường KEY là = REVIEWER & ANTENNA & DISCIPLINE. Chúng tôi sẽ sao chép nó xuống và chúng tôi sẽ chèn một bảng tổng hợp mới. Nhấp vào OK, và chúng ta sẽ lấy trường đó, trường KEY và di chuyển nó lên các BỘ LỌC kiểu cũ, và sau đó chúng ta hãy xem. (Hãy xua tan một báo cáo nhỏ ở đây với - 08:30) NGƯỜI ĐÁNH GIÁ, ANTENNA, KỶ LUẬT và DOANH THU, như vậy.
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.
Và lần thứ ba, được chứ? Đây là một trong những điều đó là điên rồ. Nếu tôi muốn ghi macro, nếu tôi muốn (viết macro - 13:35) chỉ chọn một mục, hãy tìm cách thực hiện điều đó bằng cách sử dụng DEVELOPER, RECORD MACRO, HOWTOCHOOSEONEITEMFROMSLICER, nhấp vào OK và chúng tôi chỉ cần chọn một mục mục. BAY. Nhấp vào DỪNG GHI, sau đó chúng tôi đi ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER, CHỈNH SỬA điều đó, và chắc chắn, họ làm cho FLO TRUE và sau đó mọi người khác FLASE. Có nghĩa là nếu tôi có một máy cắt lát với 100 mục trong đó, họ sẽ phải đặt 100 dòng mã vào đó để bỏ chọn mọi thứ khác. Có vẻ vô cùng kém hiệu quả nhưng bạn đã có.
Tải tập tin
Tải file mẫu tại đây: Podcast2106.xlsx
