एडवांस्ड फिल्टर - एक्सेल टिप्स

मौत की समस्या को हल करने के लिए एक्सेल में उन्नत फ़िल्टर का उपयोग करना। हालाँकि, नियमित फ़िल्टर अधिक शक्तिशाली हो गए हैं, फिर भी कई बार ऐसा होता है कि उन्नत फ़िल्टर कुछ ऐसी तरकीबें कर सकता है जो अन्य नहीं कर सकते।

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

• उन्नत फ़िल्टर नियमित फ़िल्टर की तुलना में अधिक "उन्नत" है क्योंकि:
• 1) यह एक नई रेंज में कॉपी कर सकता है
• 2) आप अधिक जटिल मानदंड बना सकते हैं जैसे कि फ़ील्ड 1 = ए या फ़ील्ड 2 = ए
• 3) यह तेज है
• मोर्टा वीबीए में रिकॉर्ड के माध्यम से लूप या एक सरणी का उपयोग करके 100K पंक्तियों को संसाधित करने की कोशिश कर रहा है
• यह हमेशा अपने स्वयं के कोड लिखने की तुलना में अंतर्निहित एक्सेल सुविधाओं का उपयोग करने के लिए तेज़ होगा।
• आपको एक इनपुट रेंज की आवश्यकता है, और फिर एक क्राइटेरिया रेंज और / या एक आउटपुट रेंज
• इनपुट रेंज के लिए: डेटा के ऊपर हेडिंग की एकल पंक्ति
• शीर्षकों के लिए एक अस्थायी पंक्ति जोड़ें
• आउटपुट रेंज के लिए: उन कॉलम के लिए शीर्षकों की एक पंक्ति जिन्हें आप निकालना चाहते हैं
• मानदंड सीमा के लिए: पंक्ति 1 में शीर्षक, पंक्ति 2 में शुरू होने वाले मान
• जटिलता: एक्सेल के पुराने संस्करण आउटपुट रेंज को दूसरी शीट पर नहीं होने देंगे
• यदि आप एक मैक्रो लिख रहे हैं जो 2003 में चलाया जा सकता है, तो परिधि के लिए इनपुट रेंज के लिए नामित सीमा का उपयोग करें

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

पॉडकास्ट से एक्सेल सीखें, एपिसोड 2060: एक्सेल एडवांस्ड फ़िल्टर

अरे, नेटकास्ट में आपका स्वागत है, मैं बिल जेलन हूं। मॉर्ट द्वारा भेजा गया आज का प्रश्न। मोर्ट, उसके पास डेटा की 100,000 पंक्तियाँ हैं और वह कॉलम A, B और D में रुचि रखता है जहाँ Column C कुछ विशेष वर्ष से मेल खाता है। इसलिए वह चाहता है कि एक व्यक्ति एक वर्ष में प्रवेश करे और फिर कॉलम A, B और D. प्राप्त करे और मोर्टार के पास कुछ VBA हो जहाँ वह ऐसा करने के लिए सरणियों का उपयोग कर रहा हो और मैंने कहा, "एक सेकंड रुको, तुम्हें पता है, उन्नत फ़िल्टर यह करेगा बहुत अच्छा। ” ठीक है, और अब बस समीक्षा करने के लिए, मैं वापस चला गया, मैंने अपने वीडियो के माध्यम से वापस देखा। मैंने लंबे समय में उन्नत फ़िल्टर को कवर नहीं किया है इसलिए हमें इस बारे में बात करनी चाहिए।

उन्नत फ़िल्टर के लिए एक इनपुट रेंज की आवश्यकता होती है और फिर इनमें से कम से कम एक: एक मानदंड श्रेणी या एक आउटपुट रेंज। हालाँकि आज हम उन दोनों का उपयोग करने जा रहे हैं। ठीक है, इसलिए इनपुट रेंज आपका डेटा है और आपको डेटा के ऊपर शीर्षक रखना होगा। इसलिए, मोर्ट के पास शीर्ष लेख नहीं हैं और इसलिए मैं अस्थायी रूप से यहां एक पंक्ति डालने जा रहा हूं और फील्ड 1 को पसंद करता हूं। मोर्ट को पता है कि उसका डेटा क्या है और इसलिए वह वहां वास्तविक शीर्षक डाल सकता है। और हम O के माध्यम से Columns E में कुछ भी इन तथाकथित डेटा का उपयोग नहीं कर रहे हैं, इसलिए मुझे वहां शीर्षक जोड़ने की आवश्यकता नहीं है, ठीक है? तो अब, D के माध्यम से A1, 100000 मेरी इनपुट रेंज बन जाता है। और फिर आउटपुट रेंज और मानदंड रेंज - ठीक है, आउटपुट रेंज केवल उन शीर्षकों की एक सूची है जो आप चाहते हैं। इसलिए मैं यहां आउटपुट रेंज डालने जा रहा हूं और हमें फील्ड 3 की जरूरत नहीं है इसलिए मैं 'मैं बस उस तरफ ले जाऊंगा। तो अब, यह सीमा यहीं है, C1 के माध्यम से A1 मेरी आउटपुट रेंज बन जाती है जो एक्सेल को बताती है कि मुझे इनपुट रेंज से कौन से क्षेत्र चाहिए। और, वे एक अलग क्रम में हो सकते हैं यदि आप चीजों को फिर से व्यवस्थित करना चाहते हैं, जैसे कि यदि मैं पहले फ़ील्ड 4 चाहता हूं, और फिर फ़ील्ड 1 तो फ़ील्ड 2। और फिर, ये इनवॉइस नंबर की तरह वास्तविक शीर्षक होंगे। मुझे अभी पता नहीं है कि मोर्ट का डेटा कैसा दिखता है।

और फिर, मानदंड श्रेणी एक शीर्षक है और आप क्या मूल्य चाहते हैं। तो मान लीजिए कि मैं वर्ष 2014 में कुछ भी पाने की कोशिश कर रहा था। यह इस तरह की मानदंड सीमा बन जाती है। ठीक है, यहाँ सावधानी का एक शब्द है। मैं एक्सेल २०१६ में हूँ और एक्सेल २०१६ में दो शीट्स के बीच एक उन्नत फ़िल्टर करना संभव है, लेकिन अगर आप वापस जाते हैं, और मुझे याद नहीं है कि क्या तरीका है, शायद २००३, मुझे यकीन नहीं है। किसी समय में, यह हुआ करता था कि आप एक शीट से दूसरी शीट पर एक उन्नत फ़िल्टर नहीं कर सकते थे, इसलिए आपको यहाँ आना होगा और अपनी इनपुट रेंज का नाम देना होगा। आपको यहां एक नाम बनाना होगा। MyName या ऐसा कुछ, ठीक है? और यही वह तरीका होगा जिससे आप इसे ठीक से खींच पाएंगे। जरूरी नहीं कि एक्सेल 2016 में फिर से, मैं 'यकीन नहीं है कि अगर मोर्ट डेटा के पुराने संस्करणों में यह चल रहा है।

ठीक है, इसलिए यहां डेटा पर वापस, हम उन्नत फ़िल्टर पर जाते हैं, ठीक है। और हम एक अन्य स्थान पर कॉपी करने जा रहे हैं जो हमारी आउटपुट रेंज को सक्षम करता है। ठीक है, तो सूची सीमा, डेटा कहां है? क्योंकि मैं एक्सेल २०१६ में हूं, मैं नाम रेंज का उपयोग करने के बजाय डेटा पर जाने वाला हूं - इसलिए यह मेरी इनपुट रेंज है। मानदंड श्रेणी वे कोशिकाएं हैं और फिर, जहां हम करने जा रहे हैं - आउटपुट, यह सिर्फ ये तीन कोशिकाएं हैं। और फिर हम ठीक पर क्लिक करते हैं। ठीक है, और बम! वह कितना तेज, कितना तेज है। और क्या होगा अगर हम एक अलग साल चाहते थे? यदि हम एक अलग वर्ष चाहते थे तो हम परिणाम को हटा देंगे, 2015 में डाल देंगे, और फिर एक उन्नत फ़िल्टर फिर से करेंगे, किसी अन्य स्थान पर कॉपी करेंगे, ठीक पर क्लिक करेंगे और 2015 के सभी रिकॉर्ड होंगे। बिजली की तेजी से।

ठीक है अब, जबकि मैं नियमित एक्सेल में उन्नत फ़िल्टर का प्रशंसक हूं, मैं VBA में उन्नत फ़िल्टर का बहुत बड़ा प्रशंसक था, ठीक है, क्योंकि VBA अग्रिम फ़िल्टर वास्तव में, वास्तव में, वास्तव में सरल बनाता है। ठीक है, इसलिए हम मोर्ट के लिए यहां कुछ कोड लिखने जा रहे हैं, यह मानते हुए कि मोर्ट के डेटा में कोई हेडिंग नहीं है और हम अस्थायी रूप से हेडिंग को जोड़ने जा रहे हैं, ठीक है? इसलिए, मैं VBA, Alt + F11 पर स्विच करूंगा और हम इसे उस वर्कशीट से चलाने जा रहे हैं जिसमें डेटा है। तो: डिम WS WS वर्कशीट के रूप में, WS = ActiveSheet सेट करें। और फिर, रो 1 डालें और बस कुछ हेडिंग जोड़ें: ए, बी, ईयर, और डी। यह पता लगाएं कि आज हमारे पास कितने डेटा हैं और फिर सेल ए 1 से शुरू होकर 4 कॉलम अंतिम पंक्ति तक नीचे जा रहे हैं, नाम है कि इनपुट रेंज हो। ठीक है, और फिर यह वास्तव में मॉर्ट कोड है, जहां उन्होंने इनपुटबॉक्स के लिए कहा था,उस वर्ष को वे चाहते हैं और फिर वह पूछते हैं कि वह किस वर्ष या नई चादर का नाम रखना चाहते हैं, ठीक है। तो यह वास्तव में फ्लाई पर एक शीट डालने जा रहा है और फिर मैं- ActiveSheet के रूप में एक नई शीट, डब्ल्यूएसएन, आयाम। तो मुझे पता है कि डब्ल्यूएसओ मूल पत्रक है, डब्ल्यूएसएन वह नई शीट है जिसे अभी जोड़ा गया था। नई शीट पर, कॉलम E के तहत मानदंड सीमा डालें, इस शीर्षक का मिलान यहां किया जा रहा है, और फिर, जो भी उत्तर उन्होंने हमें दिया है वह E2 में जाता है। आउटपुट रेंज मेरे अन्य तीन शीर्षक होने जा रहे हैं: ए, बी और डी। और फिर, यदि आप या मृत्यु इन्हें वास्तविक शीर्षकों में बदलते हैं जो ए, बी, डी, और आप की तुलना में करना बेहतर है। इन वास्तविक शीर्षकों में परिवर्तित करें, ठीक है? तो यह सब यहाँ थोड़ा पूर्व-कार्य है। कोड की यह एक भयानक रेखा संपूर्ण उन्नत फ़िल्टर करेगी। इसलिए,InputRange से हम एक AdvancedFilter करते हैं, हम कॉपी करने जा रहे हैं। कि जगह या कॉपी में हमारी पसंद फिल्टर है। CriteriaRange E1 से E2 है, CopyToRange A से C. अद्वितीय मान है -नहीं, हम सभी मान चाहते हैं। ठीक है, कि कोड की एक पंक्ति सभी रिकॉर्ड के माध्यम से लूपिंग का जादू करती है या सभी रिकॉर्ड के माध्यम से लूप बदलने या सरणियों को करने का कार्य करती है। और फिर हम कर रहे हैं, हम मापदंड सीमा को साफ़ कर देंगे और फिर मूल कार्यपत्रक पर पंक्ति 1 को हटा दें।और फिर हम कर रहे हैं, हम मापदंड सीमा को साफ़ कर देंगे और फिर मूल कार्यपत्रक पर पंक्ति 1 को हटा दें।और फिर हम कर रहे हैं, हम मापदंड सीमा को साफ़ कर देंगे और फिर मूल कार्यपत्रक पर पंक्ति 1 को हटा दें।

ठीक है, तो चलो हमारे डेटा पर वापस यहाँ जाएँ। हम इसे चलाना आसान बना देंगे, इसलिए: एक सम्मिलित करें, एक आकृति डालें और इस फ़िल्टर को कॉल करें, घर, केंद्र, केंद्र, बड़ा, बड़ा, बड़ा, राइट-क्लिक करें, मैक्रो को असाइन करें, और इसे MacroForMort को असाइन करें। ठीक है, इसलिए यहां हम जाते हैं। हम एक परीक्षण करने जा रहे हैं। देखें कि हम डेटा शीट पर हैं, फ़िल्टर पर क्लिक करें, हम किस वर्ष चाहते हैं? हम 2015 चाहते हैं। मैं इसे क्या कहना चाहता हूं? मैं इसे 2015 को कॉल करना चाहता हूं, ठीक है। और बम! वहाँ यह किया है। यह कितना तेज है, यह कितना तेज है।

अब, क्योंकि मोर्ट के मूल डेटा में हेडिंग नहीं थी, हो सकता है कि इस डेटा में हेडिंग न हों। तो चलिए Alt + F11 पर चलते हैं, यहाँ हम मापदंड श्रेणी को साफ़ करना चाहते हैं। हम भी पंक्तियों (1)। ठीक है, इसलिए अब अगली बार जब हम इस पर थे, तो इससे उन शीर्षकों से छुटकारा मिल जाएगा। और चलो बस - जल्दी से पूरी बात चलाने के बजाय, चलो 2014 के साथ यहां एक नज़र डालते हैं। इसलिए मैं डेटा, Alt + F11 पर एक सेल का चयन करूंगा, और मैं उस बिंदु पर नीचे चलना चाहता हूं जहां हम करते हैं। आधुनिक फ़िल्टर। इसलिए हम देख सकते हैं और देख सकते हैं कि पूरा मैक्रो यहां क्या कर रहा है। तो हम Run पर क्लिक करेंगे, और मैं 2014 प्राप्त करना चाहता हूं। 2014, ठीक है। और इसलिए, F8 दबाएं, हम उन्नत फ़िल्टर करने वाले हैं। हम यहां Excel में वापस जा सकते हैं और देख सकते हैं कि क्या हुआ है।

First thing that's happened- Now, first thing that’s happened is we've added a new temporary row with the headings. Inserted this worksheet, built a criteria range with a heading and what year they input, chose the fields that we want to do and then back in VBA, I'll run the next line of codes, that's F8 that does the advanced filter right there. It's incredibly fast and you'll see that that has actually now brought us all the records. From there, it's just a bit of cleanup, delete this, delete this. I'll go back to the data and delete Row 1 and we will be good to go. So I'll just let the rest of that run, remove that breakpoint, alright? So there's the VBA. For me, this is I think the fastest way, fastest way to go.

Alright, episode recap: The advanced filter is more advanced than the regular filter because it can copy to a new range. And now, I didn't show it in this video but you can build complex criteria where Field 1 = A or Field 2 = A. The regular auto filter can't do that and it's fast. Mort is trying to process 100,000 rows in VBA by using an array or by looping, but it'll always be faster to use Excel building features than writing your own code. You need to define an input range, criteria range, output range. You always need an input range in at least one of these, although today I'm using both. For the input range, single row of headings above the data. So we're going to add a temporary row of headings. For the output range, the same headings that you want to extract, alright. So, you know, if it was A, B, Year and D, we’ll just put A, B and D as the output range. For the criteria range, headings in Row 1. So this is the field I want to build a criteria on and this is the value I'm looking for. Complications: Older versions of Excel will not allow the output range to be on another sheet, so, potentially your code will run back then. You want to use a named range for the input range because from this sheet, you know, the named range, even though it's on another sheet, the sheet believes the name branches on the current sheet. So that would allow the advanced filter to work.

ठीक है, ठीक है, वहाँ तुम्हारे पास है। मैं उस प्रश्न को भेजने के लिए मोर्ट को धन्यवाद देना चाहता हूं। मैं आपके द्वारा रोकने के लिए धन्यवाद देना चाहता हूं। हम आपको अगली बार एक और नेटकास्ट से देखेंगे।

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

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