VBA सभी स्लाइसर संयोजन - एक्सेल टिप्स

नियमित पिवट टेबल फ़िल्टर शो ऑल रिपोर्ट फ़िल्टर पेजों की पेशकश करते हैं, लेकिन स्लाइसर्स इस कार्यक्षमता का समर्थन नहीं करते हैं। आज, सभी संभव स्लाइसर संयोजनों के माध्यम से लूप में कुछ वीबीए।

वीडियो देखेंा

वीडियो ट्रांसक्रिप्ट

एक्सेल से जानें, पॉडकास्ट एपिसोड 2106: 3 स्लाइसरों के हर संयोजन का एक पीडीएफ बनाएं।

आज हमारे पास कितना बड़ा सवाल है। किसी ने लिखा था, जानना चाहता था कि क्या यह संभव है। अभी, उनके पास पिवट टेबल चलाने वाले 3 स्लाइसर्स हैं। मुझे नहीं पता कि धुरी तालिका कैसी दिखती है। यह गोपनीय है। मुझे इसे देखने की अनुमति नहीं है इसलिए मैं सिर्फ अनुमान लगा रहा हूं, है ना? इसलिए, वे क्या कर रहे हैं, वे प्रत्येक स्लाइसर से एक आइटम चुन रहे हैं और फिर एक पीडीएफ बना रहे हैं, और फिर जाकर अगले आइटम का चयन कर रहे हैं और एक पीडीएफ बना रहे हैं, और फिर अगले आइटम, और अगले आइटम, और आप कर सकते हैं कल्पना कीजिए, स्लाइसर्स के 400 संयोजनों के साथ, यह हमेशा के लिए ले सकता है, और उन्होंने कहा, क्या किसी कार्यक्रम के माध्यम से जाने और सभी विकल्पों के माध्यम से लूप करने का कोई तरीका है?

मैंने कहा, ठीक है, यहाँ कुछ योग्य प्रश्न हैं। नंबर एक, हम मैक पर नहीं हैं, है ना? एंड्रॉइड नहीं, iPhone के लिए एक्सेल नहीं। यह विंडोज के लिए एक्सेल है। हाँ, उन्होंने कहा। महान। मैंने कहा, दूसरा वास्तव में महत्वपूर्ण प्रश्न है, हम एक आइटम को एक स्लाइसर से चुनना चाहते हैं, और फिर अंत में स्लाइसर से दूसरे आइटम, और फिर अन्य आइटम को स्लाइसर से। हमें ANDY और फिर ANDY और BETTY, और फिर ANDY और CHARLIE जैसे संयोजनों की आवश्यकता नहीं है, है ना? वह बाहर है। मैं बस प्रत्येक स्लाइसर से एक आइटम करने जा रहा हूं। हाँ हाँ हाँ। इस तरह यह जाने वाला है। बिल्कुल सही, मैंने कहा। तो यहां, मुझे यह बताएं, प्रत्येक स्लाइसर चुनें, SLICER TOOLS, विकल्प पर जाएं, और SLICER SETTINGS पर जाएं। हमने अभी 2 एपिसोड पहले किया था। क्या यह पागल नहीं है? FORMULAS में उपयोग करने के लिए नाम और मुझे पता है कि यह SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE,ठीक है? तो, मुझे लगता है कि मुझे मिल गया है।

अब, हम यहां VBA पर स्विच करने जा रहे हैं, और, वैसे, सुनिश्चित करें कि आप xlsm के रूप में सहेजे गए हैं और सुनिश्चित करें कि मैक्रो की अनुमति देने के लिए आपकी मैक्रो सुरक्षा सेट है। यदि यह xlsx के रूप में सहेजा गया है, तो मुझ पर विश्वास करें, आपको एक फ़ाइल करना होगा, SAVE AS, यदि आप इसे xlsx के रूप में छोड़ते हैं, तो आप अपना सारा काम खो देंगे। हां, आपके द्वारा उपयोग किए जाने वाले 99.9% स्प्रेडशीट xlsx हैं, लेकिन मैक्रो वाला यह काम नहीं करेगा। ALT + F11। ठीक है, तो यहाँ कोड है।

हम तीन स्लाइसर कैश, एक स्लाइसर आइटम और 3 रेंज ढूंढने जा रहे हैं। प्रत्येक स्लाइसर कैश के लिए, हम इसे सूत्र में उपयोग किए गए नाम पर सेट करने जा रहे हैं, जो मैंने अभी आपको SLICER SETTINGS डायलॉग बॉक्स में दिखाया था। तो, हम उन तीनों है। मैं उन सभी को स्पष्ट करना चाहता हूं जो यह सुनिश्चित करते हैं कि हम सब कुछ चुने हुए हैं। यह काउंटर बाद में फ़ाइल नाम में उपयोग किया जा रहा है।

ठीक है। अब, यह अगला भाग, राइट टू द ऑफ, सभी तीन आईटीआईटीएस के तीन महत्वपूर्ण स्थान है। # 2 देखें कि यह पागलपन क्यों होना था। इसलिए मैं यह पता लगाने जा रहा हूं कि अगला उपलब्ध कॉलम कहां है, पिछले कॉलम से 2 से अधिक की तरह, याद रखें कि इसलिए मैं बाद में सामान हटा सकता हूं, और फिर, प्रत्येक SI, स्लाइसर आइटम, SC1.SLICERITEMS के लिए, हम उस स्लाइसर कैप्शन को स्प्रैडशीट पर लिखने जा रहे हैं। जब हम उन सभी स्लाइसर आइटमों के साथ काम करते हैं, तो यह पता करें कि आज हमारे पास कितनी पंक्तियाँ हैं, और फिर उस रेंज को SLICERITEME1 नाम दें। हम स्लाइसर कैश 2 के लिए उस पूरी चीज़ को दोहराने जा रहे हैं, 1 कॉलम, SLICERITEMS2 और SLICERITEMS3 पर।

आइए मैं आपको दिखाता हूं कि इस बिंदु पर क्या दिखता है। इसलिए, मैं यहीं एक ब्रेकपॉइंट लगाऊंगा और हम इस कोड को चलाएंगे। ठीक है। यह तेज़ था। हम VBA पर स्विच करने जा रहे हैं, और यहाँ से दायें हाथ की ओर, मैं 3 नई सूचियाँ प्राप्त करने जा रहा हूँ। ये सूची सब कुछ है जो स्लाइसर में है, और आप देखते हैं कि इसे SLICERITEMS1, SLICERITEMS2 और SLICERITEMS3 कहा जाता है, ठीक है? हम अंत में उस से छुटकारा पा लेंगे, लेकिन यह हमें लूप के माध्यम से कुछ देता है। VBA पर वापस जाएं।

ठीक है। हम SLICERITEMS1 में सभी आइटमों के माध्यम से लूप करने जा रहे हैं, स्लाइसर कैश 1 के लिए फ़िल्टर को साफ़ करें, और फिर हम प्रत्येक स्लाइसर आइटम के माध्यम से, एक बार में, एक बार जाने वाले हैं और देखें कि क्या यह स्लाइसर आइटम = के लिए है CELL1.VALUE, और, फिर से, हम प्रत्येक मान के माध्यम से लूपिंग कर रहे हैं। तो, पहली बार के माध्यम से, यह होने जा रहा है ANDY और फिर BETTY और, आप जानते हैं, और इसी तरह।

यह परेशान करने वाला है। मुझे एक बार में सभी स्लाइस को बंद करने का कोई तरीका नहीं मिला। मैंने कोड को रिकॉर्ड करने और एक स्लाइसर चुनने की भी कोशिश की, और रिकॉर्ड किया गया कोड 9 स्लाइसरों को वापस कर रहा था और एक स्लाइसर को चालू कर रहा था, ठीक है? इसलिए निराशा होती है कि मुझे इससे बेहतर कुछ नहीं मिला, लेकिन मुझे इससे बेहतर कुछ नहीं मिला।

तो, हमने पहला स्लाइसर = ANDY पर सेट किया। फिर हम गुजरते हैं, और दूसरे स्लाइसर के लिए, हम इसे = पहले आइटम पर सेट करने जा रहे हैं। तीसरे स्लाइसर के लिए, इसे = पहले आइटम पर सेट करें।

ठीक है। फिर, यहां नीचे, निर्णय लें कि क्या यह वैधता है। मैं आपको समझाता हूं कि यह महत्वपूर्ण क्यों है। यदि हम, मनुष्य के रूप में हम ऐसा कर रहे हैं, तो, हम A52 का चयन नहीं करेंगे क्योंकि स्पष्ट रूप से यह धूसर हो गया है, लेकिन मैक्रो बहुत बेवकूफ होने वाला है और यह A52 और फिर 104 चुनने जा रहा है, और यह इसे खाली बनाने जा रहा है पिवट तालिका। तो, यहाँ एक हजार संभावित संयोजन हैं। मुझे पता है कि केवल 400 संभावित रिपोर्टें हैं। उस व्यक्ति ने मुझे बताया, और इसलिए हम 600 बार प्राप्त करने जा रहे हैं जहां हम इस (बदसूरत - 04:45) रिपोर्ट की एक पीडीएफ बनाने जा रहे हैं।

इसलिए, मैं जो करने जा रहा हूं वह मैं यहां ANALYZE टैब पर देखने जा रहा हूं - इसे 2010 में विकल्प कहा गया था - और देखें कि इस धुरी तालिका का नाम क्या है, और मैं यह देखना चाहता हूं कि कितनी पंक्तियां हैं हमें मिला। मेरे मामले में, अगर मुझे 2 पंक्तियाँ मिलती हैं, तो मुझे पता है कि यह एक रिपोर्ट है जिसे मैं निर्यात नहीं करना चाहता। अगर मुझे 2 से अधिक पंक्तियाँ, 3, 4, 5, 6 मिलती हैं, तो मुझे पता है कि यह एक रिपोर्ट है जिसे मैं निर्यात करना चाहता हूं। आपको अपनी स्थिति के बारे में पता लगाना होगा कि यह क्या है।

ठीक है। तो, इसीलिए हम देख रहे हैं कि यदि पिवट टेबल 2 और, वह नाम जो वहाँ रिबन में वापस आ गया था, .TABLERANGE2.ROWS.COUNT है> 2. यदि यह नहीं है> 2, तो हम नहीं चाहते हैं पीडीएफ बनाएं, ठीक है? इसलिए, यह IF स्टेटमेंट इस END IF के लिए नीचे कह रहा है कि हम रिपोर्ट संयोजनों के लिए केवल PDF बनाने जा रहे हैं जिसमें मान हैं। MYFILENAME, मैंने C: REPORTS नामक एक फ़ोल्डर बनाया। यह सिर्फ एक खाली फ़ोल्डर है। सी: रिपोर्ट। आप सुनिश्चित करें कि आपके पास एक फ़ोल्डर है और मैक्रो में समान फ़ोल्डर नाम का उपयोग करें। C: REPORTS / और फ़ाइल का नाम REPORT001.PDF होने जा रहा है। अब, हमने काउंटर इनिशियलाइज़ किया, वहां 1 फॉरमैट का उपयोग किया गया है, जो काउंटर के पाठ को कहने के लिए एक्सेल के बराबर है, और 000। इस तरह, मैं 001, फिर 002, फिर 003 और फिर 004 प्राप्त करने जा रहा हूं। वे 'सही ढंग से सॉर्ट करने जा रहा है।अगर मैंने अभी इस REPORT1 को कॉल किया था, और फिर बाद में मेरे पास REPORT10 और 11 हैं, और बाद में REPORT100 पर, वे सभी एक साथ सॉर्ट करने जा रहे हैं जब वे एक साथ नहीं होते हैं, ठीक है? इसलिए, जब हमने इसे चलाया था, तब से फ़ाइल का नाम बनाते समय फ़ाइल मौजूद है, हम इसे मारने जा रहे हैं। दूसरे शब्दों में, इसे हटा दें। बेशक, यदि आप कोशिश करते हैं और उस फ़ाइल को मारते हैं जो वहां नहीं है, तो वे एक त्रुटि फेंक देंगे। इसलिए, अगर हमें अगली पंक्ति में कोई त्रुटि मिलती है, तो यह ठीक है। बस आगे बढ़ें, लेकिन फिर मैंने एरर गोटो 0 पर चेकिंग की त्रुटि को रीसेट कर दिया।बेशक, यदि आप कोशिश करते हैं और उस फ़ाइल को मारते हैं जो वहां नहीं है, तो वे एक त्रुटि फेंक देंगे। इसलिए, अगर हमें अगली पंक्ति में कोई त्रुटि मिलती है, तो यह ठीक है। बस आगे बढ़ें, लेकिन फिर मैंने एरर गोटो 0 पर चेकिंग की त्रुटि को रीसेट कर दिया।बेशक, यदि आप कोशिश करते हैं और उस फ़ाइल को मारते हैं जो वहां नहीं है, तो वे एक त्रुटि फेंक देंगे। इसलिए, अगर हमें अगली पंक्ति में कोई त्रुटि मिलती है, तो यह ठीक है। बस आगे बढ़ें, लेकिन फिर मैंने एरर गोटो 0 पर चेकिंग की त्रुटि को रीसेट कर दिया।

यहाँ सक्रिय शीट है, एक प्रारूप के रूप में निर्यात करें, एक पीडीएफ के रूप में, फ़ाइल नाम है, उन सभी विकल्पों को, और फिर मैं काउंटर को बढ़ाता हूं, इस तरह, अगली बार जब हम रिकॉर्ड रखते हैं, तो हम REPORT002.PDF बना रहे हैं । उन तीन छोरों को समाप्त करें और फिर खाली स्थान छोड़ दें। इसलिए, मुझे याद होगा कि हम कौन से कॉलम में थे, 1 पंक्ति, 3 कॉलम, ENTIRECOLUMN.CLEAR का आकार बदलें, और फिर एक अच्छा सा संदेश बॉक्स वहाँ दिखाने के लिए कि चीजें बनाई गई हैं। अच्छा जी। इसे चलाते हैं।

Alright. Now, what should be happening here is if we go and look in Windows Explorer, there it is. Okay. It is creating… like, every second, we're getting 2 or 3 or 4 or more. I'll pause this and let it run. Alright. There we are. 326 reports have been created. It looped through all 1000 possibilities and only kept the ones where there was an actual result. Alright, from 9:38 until 9:42, 4 minutes to do all that, but still faster than doing the 400, alright?

Alright. So, that's the macro way to do this. The other thing that struck me here that it may or may not work. It's really tough to say. Let's take our data and I'm going to move the data to a brand new workbook. MOVE OR COPY, CREATE A COPY, to a NEW BOOK, click OK, and we’re going to use a trick here that I first learned from Szilvia Juhasz - a great Excel consultant out in Southern California -- and we're going to add a KEY field here. The KEY field is = REVIEWER & ANTENNA & DISCIPLINE. We'll copy that down and we'll insert a new pivot table. Click OK, and we're going to take that field, the KEY field, and move it up to the old-fashioned FILTERS, and then let's see. (Let's dispel a little report here with - 08:30) REVIEWER, ANTENNA, DISCIPLINE, and REVENUE, like that.

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, HOWTOCHOOSEONEITEMFROITLICER का उपयोग करके ठीक है, और हम बस एक का चयन करते हैं। आइटम। एफएलओ। STOP RECORDING पर क्लिक करें, फिर हम ALT + F8, HOWTOCHOOSEONEITEMFROMSLICER, EDIT पर जाएँ, और, निश्चित रूप से, वे फ़्लू TRUE बनाते हैं और फिर बाकी सभी को फ़्लोज़ करते हैं। इसका मतलब है कि अगर मेरे पास इसमें 100 आइटम के साथ एक स्लाइसर था, तो उन्हें बाकी सब चीजों को अनदेखा करने के लिए कोड की 100 लाइनें डालनी होंगी। अविश्वसनीय रूप से अक्षम लगता है, लेकिन आप वहां हैं।

फ़ाइल डाउनलोड करें

यहाँ नमूना फ़ाइल डाउनलोड करें: Podcast2106.xlsx