माइक्रोसॉफ्ट एक्सेल में एडवांस्ड फिल्टर विकल्प तब उपयोगी होता है जब आपको दो या अधिक मानदंडों को पूरा करने वाले डेटा की खोज करनी हो। इस लेख में हम एक्सेल में एडवांस्ड फिल्टर क्राइटेरिया रेंज के अनुप्रयोगों पर चर्चा करेंगे।
यहां से प्रैक्टिस वर्कबुक डाउनलोड करें।
एक्सेल में एडवांस्ड फिल्टर क्राइटेरिया रेंज के 18 अनुप्रयोग
1. संख्याओं और तिथियों के लिए एडवांस्ड फिल्टर क्राइटेरिया रेंज का उपयोग
सबसे पहले, हम अपने डेटासेट से परिचित होते हैं। कॉलम B से कॉलम E बिक्री से जुड़े विभिन्न डेटा को दर्शाते हैं। अब हम यहां एडवांस्ड फिल्टर क्राइटेरिया रेंज लागू कर सकते हैं। इस उदाहरण में, हम संख्याओं और तिथियों को फ़िल्टर करने के लिए एडवांस्ड फिल्टर क्राइटेरिया रेंज का उपयोग करेंगे। हम वह सभी डेटा निकालेंगे जहां बिक्री की मात्रा 10 से अधिक है। आइए प्रक्रिया देखें।
- सबसे पहले, डेटा टैब में, सॉर्ट और फ़िल्टर विकल्प से एडवांस्ड कमांड चुनें। एडवांस्ड फिल्टर नामक एक डायलॉग बॉक्स दिखाई देगा।
- अगला, संपूर्ण तालिका (B4:E14) को लिस्ट रेंज के लिए चुनें।
- सेल (C17:C18) को क्राइटेरिया रेंज के रूप में चुनें।
- OK दबाएं।
- अंत में, हम केवल वही डेटा देख सकते हैं जिसमें मात्रा 10 से अधिक है।
नोट:
1. कम से कम दो पंक्तियों वाले क्राइटेरिया चुनें।
2. हम उन संबंधित कॉलम के लिए हेडर का उपयोग करेंगे जहां फ़िल्टरिंग क्राइटेरिया लागू होंगे।
2. एडवांस्ड फिल्टर क्राइटेरिया के साथ टेक्स्ट मान फ़िल्टर करें
हम संख्याओं और तिथियों के अलावा तार्किक ऑपरेटरों का उपयोग करके टेक्स्ट मानों की तुलना भी कर सकते हैं। इस खंड में, हम चर्चा करेंगे कि टेक्स्ट के सटीक मिलान के साथ-साथ शुरुआत में विशिष्ट वर्ण होने पर एडवांस्ड फिल्टर क्राइटेरिया के साथ टेक्स्ट मान कैसे फ़िल्टर करें।
2.1 टेक्स्ट के सटीक मिलान के लिए
इस विधि में, फ़िल्टरिंग हमें इनपुट टेक्स्ट का सटीक मान लौटाएगा। मान लें हमारे पास बिक्री का निम्नलिखित डेटासेट है जिसमें एक नया कॉलम शहर है। हम केवल शहर 'NEW YORK' के लिए डेटा निकालेंगे। इस क्रिया को करने के लिए बस निम्नलिखित चरणों का पालन करें:
- शुरुआत में, सेल C18 चुनें। निम्न सूत्र डालें:
=EXACT(D5," NEW YORK")- Enter दबाएं।
- अगला, निम्न फ़िल्टर क्राइटेरिया रेंज चुनें:
लिस्ट रेंज: B4:F14
क्राइटेरिया रेंज: C17:C18
- OK दबाएं।
- अंत में, हमें केवल शहर 'NEW YORK' के लिए डेटा मिलेगा।
2.1 शुरुआत में विशिष्ट वर्ण होने पर
अब हम सटीक मिलान के बजाय विशिष्ट वर्ण से शुरू होने वाले टेक्स्ट मानों को फ़िल्टर करेंगे। यहां, हम केवल उन शहरों के मान निकालेंगे जो शब्द 'New' से शुरू होते हैं। आइए देखें कि यह कैसे करना है।
- सबसे पहले, एडवांस्ड फिल्टर बॉक्स में क्राइटेरिया रेंज चुनें:
लिस्ट रेंज: B4:F14
क्राइटेरिया रेंज: C18:C19
- OK दबाएं।
- अंत में, हमें शब्द 'New' से शुरू होने वाले सभी शहरों का डेटा मिलेगा।
3. एडवांस्ड फिल्टर विकल्प के साथ वाइल्डकार्ड का उपयोग
वाइल्डकार्ड वर्णों का उपयोग एडवांस्ड फिल्टर क्राइटेरिया रेंज लागू करने का एक और तरीका है। आमतौर पर एक्सेल में तीन प्रकार के वाइल्डकार्ड वर्ण होते हैं:
? (प्रश्न चिह्न) - टेक्स्ट में किसी एक वर्ण को दर्शाता है।
* (एस्टेरिस्क) - किसी भी संख्या में वर्णों को दर्शाता है।
~ (टिल्ड) - टेक्स्ट में वाइल्डकार्ड वर्ण की उपस्थिति को दर्शाता है।
हम एस्टेरिस्क (*) का उपयोग करके अपने डेटासेट में एक विशिष्ट टेक्स्ट स्ट्रिंग खोज सकते हैं। इस उदाहरण में, हम उन सेल्सपर्सन के नाम ढूंढते हैं जो टेक्स्ट 'J' से शुरू होते हैं। ऐसा करने के लिए, हमें इन चरणों का पालन करना होगा।
- सबसे पहले, एडवांस्ड फिल्टर विंडो खोलें। निम्न क्राइटेरिया रेंज चुनें:
लिस्ट रेंज: B4:F14
क्राइटेरिया रेंज: C17:C18
- OK दबाएं।
- अंत में, हमें केवल उन सेल्सपर्सन के नाम मिलेंगे जो टेक्स्ट 'J' से शुरू होते हैं।
संबंधित सामग्री: एक्सेल एडवांस्ड फिल्टर [एकाधिक कॉलम और क्राइटेरिया, सूत्र और वाइल्डकार्ड के साथ उपयोग]
4. एडवांस्ड फिल्टर क्राइटेरिया रेंज के साथ सूत्र लागू करें
एडवांस्ड फिल्टर क्राइटेरिया रेंज का उपयोग करने का एक और तरीका सूत्र लागू करना है। इस उदाहरण में, हम $350 से अधिक बिक्री राशि निकालेंगे। ऐसा करने के लिए बस नीचे दिए गए चरणों का पालन करें:
- शुरुआत में, सेल C19 चुनें। निम्न सूत्र डालें:
=F5>350- OK दबाएं।
सूत्र बिक्री राशि के मान की जांच करता है कि यह $350 से अधिक है या नहीं।
- अगला, एडवांस्ड फिल्टर डायलॉग बॉक्स में निम्न क्राइटेरिया रेंज चुनें:
लिस्ट रेंज: B4:F14
क्राइटेरिया रेंज: C17:C19
- OK दबाएं।
- तो, हम केवल $350 से अधिक बिक्री मान वाले डेटा देख सकते हैं।
5. AND लॉजिक क्राइटेरिया के साथ एडवांस्ड फिल्टर
अब हम एडवांस्ड फिल्टर क्राइटेरिया रेंज में AND लॉजिक पेश करेंगे। यह लॉजिक दो क्राइटेरिया का उपयोग करता है। यह आउटपुट मान लौटाता है जब डेटा दोनों क्राइटेरिया को पूरा करता है। यहां हमारे पास निम्नलिखित डेटासेट है। इस डेटासेट में, हम न्यूयॉर्क शहर के साथ-साथ >= 200 बिक्री मूल्य वाले डेटा को फ़िल्टर करेंगे। आइए देखें कि यह कैसे करना है।
- सबसे पहले, एडवांस्ड फिल्टर डायलॉग बॉक्स पर जाएं और निम्न क्राइटेरिया रेंज चुनें:
लिस्ट रेंज: B4:F14
क्राइटेरिया रेंज: C18:C19
- OK दबाएं।
- अंत में, हमें केवल न्यूयॉर्क शहर का डेटासेट मिलेगा जिसका बिक्री मूल्य $250 से अधिक है।
6. एडवांस्ड फिल्टर क्राइटेरिया रेंज के साथ OR लॉजिक का उपयोग
AND लॉजिक की तरह, OR लॉजिक भी दो क्राइटेरिया का उपयोग करता है। AND लॉजिक आउटपुट लौटाता है यदि दोनों क्राइटेरिया पूरे होते हैं जबकि OR लॉजिक लौटाता है यदि केवल एक क्राइटेरिया पूरा होता है। यहां हम केवल शहरों न्यूयॉर्क और टेक्सास के लिए डेटा लेंगे। इस क्रिया को करने के लिए बस नीचे दिए गए चरणों का पालन करें:
- शुरुआत में, एडवांस्ड फिल्टर डायलॉग बॉक्स खोलें। निम्न क्राइटेरिया रेंज डालें:
लिस्ट रेंज: B4:F14
क्राइटेरिया रेंज: C18:C20
- OK दबाएं।
- अंत में, हमें केवल शहरों न्यूयॉर्क और टेक्सास के लिए डेटासेट मिलता है।
7. क्राइटेरिया रेंज के रूप में AND & OR लॉजिक का संयोजन
कभी-कभी हमें एकाधिक क्राइटेरिया के लिए डेटा फ़िल्टर करने की आवश्यकता होती है। उस स्थिति में, हम AND और OR लॉजिक के संयोजन का उपयोग कर सकते हैं। हम दिए गए क्राइटेरिया के आधार पर निम्नलिखित डेटासेट से डेटा निकालेंगे। इस क्रिया को करने के लिए बस निम्नलिखित चरणों का पालन करें:
- सबसे पहले, एडवांस्ड फिल्टर डायलॉग बॉक्स खोलें। निम्न क्राइटेरिया चुनें:
लिस्ट रेंज: B4:F14
क्राइटेरिया रेंज: C18:C20
- फिर OK दबाएं।
- तो, हम केवल वही डेटासेट देख सकते हैं जो हमारे क्राइटेरिया से मेल खाता है।
8. विशिष्ट कॉलम निकालने के लिए एडवांस्ड फिल्टर क्राइटेरिया रेंज का उपयोग
इस उदाहरण में, हम डेटासेट के विशिष्ट भागों को फ़िल्टर करेंगे। फ़िल्टर करने के बाद हम फ़िल्टर किए गए भाग को दूसरे कॉलम में ले जाएंगे। हम इस क्रिया को नीचे दी गई प्रक्रिया के माध्यम से करने के लिए निम्नलिखित डेटासेट का उपयोग करेंगे।
- सबसे पहले, एडवांस्ड फिल्टर डायलॉग बॉक्स से निम्न क्राइटेरिया चुनें:
लिस्ट रेंज: B4:F14
क्राइटेरिया रेंज: C18:C20
- कॉपी टू अनदर लोकेशन विकल्प चुनें।
- कॉपी टू रेंज H8:I10 डालें।
- OK दबाएं।
- तो, हमें हमारे क्राइटेरिया के अनुसार H8:I10 में फ़िल्टर किया गया डेटा मिलता है।
9. फ़िल्टरिंग के बाद डेटा को दूसरी वर्कशीट में कॉपी करें
इस उदाहरण में, हम पिछले उदाहरण की तरह उसी वर्कशीट में नहीं बल्कि दूसरी वर्कशीट में डेटा कॉपी करेंगे। इसे निष्पादित करने के लिए निम्नलिखित चरणों का पालन करें:
- सबसे पहले, 'अनदर वर्कशीट-2' पर जाएं जहां हम फ़िल्टरिंग के बाद डेटा कॉपी करेंगे।
हम 'अनदर वर्कशीट-2' में दो कॉलम 'शहर' और 'बिक्री' देख सकते हैं।
- अगला, 'एडवांस्ड फिल्टर' डायलॉग बॉक्स खोलें।
- फिर 'अनदर वर्कशीट-1' पर जाएं। निम्न क्राइटेरिया चुनें:
लिस्ट रेंज: B4:F14
क्राइटेरिया रेंज: C18:C19
- अब, कॉपी टू अनदर लोकेशन विकल्प चुनें।
- उसके बाद, 'अनदर वर्कशीट-2' पर जाएं। कॉपी टू रेंज B2:C4 चुनें।
- OK दबाएं।
- अंत में, हम 'अनदर वर्कशीट-2' में फ़िल्टर किया गया डेटा देख सकते हैं।
10. एडवांस्ड फिल्टर क्राइटेरिया के साथ अद्वितीय रिकॉर्ड निकालें
इस मामले में, हम किसी विशिष्ट कॉलम से केवल अद्वितीय मान निकालेंगे। निम्नलिखित डेटासेट से, हम शहरों के अद्वितीय मानों को दूसरे कॉलम में निकालेंगे। बस चरणों का पालन करें:
- शुरुआत में, एडवांस्ड फिल्टर विंडो खोलें। क्राइटेरिया चुनें
लिस्ट रेंज: D4:D14
- अगला, कॉपी टू अनदर लोकेशन विकल्प चुनें।
- फिर, कॉपी टू रेंज को H4:H8 के रूप में डालें।
- यूनिक रिकॉर्ड्स ओनली बॉक्स चेक करें।
- OK दबाएं।
- अंत में, हम कॉलम H में केवल अद्वितीय रिकॉर्ड वाले शहरों के नाम देख सकते हैं।
11. एडवांस्ड फिल्टर क्राइटेरिया रेंज के साथ सप्ताह के दिन ढूंढें
हम एडवांस्ड फिल्टर क्राइटेरिया रेंज के साथ सप्ताह के दिन ढूंढ सकते हैं। यहां हम इस प्रक्रिया को समझाने के लिए निम्नलिखित डेटासेट का उपयोग करेंगे:
- सबसे पहले, सेल C19 चुनें। निम्न सूत्र डालें:
=AND(WEEKDAY(B5)<>1,WEEKDAY(B5)<>7)
- अगला, एडवांस्ड फिल्टर डायलॉग बॉक्स में निम्न क्राइटेरिया रेंज सेट करें:
लिस्ट रेंज: B4:F14
क्राइटेरिया रेंज: C18:C19
- OK दबाएं।
- अंत में, हमें केवल सप्ताह के दिनों के लिए तिथि मान मिलेंगे।
🔎 सूत्र कैसे काम करता है?
- WEEKDAY(B5)<>1: 1 रविवार को दर्शाता है। यह भाग क्राइटेरिया सेट करता है कि तिथि रविवार नहीं है।
- WEEKDAY(B5)<>7: 7 शनिवार को दर्शाता है। यह भाग क्राइटेरिया सेट करता है कि तिथि शनिवार नहीं है।
- AND(WEEKDAY(B5)<>1,WEEKDAY(B5)<>7): क्राइटेरिया सेट करता है कि दिन न तो शनिवार है और न ही रविवार।
12. वीकेंड ढूंढने के लिए एडवांस्ड फिल्टर लागू करें
हम वीकेंड को तिथि कॉलम से ढूंढने के लिए एडवांस्ड फिल्टर क्राइटेरिया रेंज का उपयोग भी कर सकते हैं। आइए देखें कि निम्नलिखित डेटासेट का उपयोग करके यह कैसे करना है:
- शुरुआत में सेल C19 चुनें। निम्न सूत्र डालें:
=OR(WEEKDAY(B5)=1,WEEKDAY(B5)=7)- Enter दबाएं।
- अगला, एडवांस्ड फिल्टर डायलॉग बॉक्स से निम्न क्राइटेरिया रेंज चुनें:
लिस्ट रेंज: B4:F14
क्राइटेरिया रेंज: C18:C19
- OK दबाएं।
- तो, हम तिथि कॉलम में केवल वीकेंड के मान देख सकते हैं।
13. औसत से नीचे या ऊपर के मानों की गणना के लिए एडवांस्ड फिल्टर का उपयोग
इस खंड में, हम एडवांस्ड फिल्टर क्राइटेरिया रेंज का उपयोग करके औसत से नीचे या ऊपर के मान की गणना करेंगे। यहां हम केवल उस बिक्री मूल्य को फ़िल्टर करेंगे जो औसत बिक्री मूल्य से अधिक है।
- सबसे पहले, सेल C19 चुनें। निम्न सूत्र डालें:
=E5>AVERAGE(E5:E14)
- अगला, एडवांस्ड फिल्टर डायलॉग बॉक्स खोलें। निम्न क्राइटेरिया रेंज डालें:
लिस्ट रेंज: B4:F14
क्राइटेरिया रेंज: C18:C19
- OK दबाएं।
- तो, हमें केवल औसत मूल्य से अधिक बिक्री मूल्य वाले डेटासेट मिलते हैं।
14. OR लॉजिक के साथ रिक्त सेल फ़िल्टर करना
यदि हमारे डेटासेट में रिक्त सेल हैं, तो हम एडवांस्ड फिल्टर का उपयोग करके रिक्त सेल निकाल सकते हैं।
हमारे पास निम्नलिखित डेटासेट है। डेटासेट में रिक्त सेल हैं। हमने निम्न सूत्र का उपयोग करके क्राइटेरिया सेट किया है:
=B5=""
- सबसे पहले, एडवांस्ड फिल्टर डायलॉग बॉक्स पर जाएं। निम्न क्राइटेरिया डालें:
लिस्ट रेंज: B4:F14
क्राइटेरिया रेंज: C17:C22
- OK दबाएं।
- अंत में, हमें वह डेटासेट मिलता है जिसमें केवल रिक्त सेल होते हैं।
15. OR और AND लॉजिक का उपयोग करके नॉन-ब्लैंक सेल फ़िल्टर करने के लिए एडवांस्ड फिल्टर लागू करें
इस उदाहरण में, हम रिक्त सेल को हटाएंगे जबकि पिछले उदाहरण में हमने नॉन-ब्लैंक सेल हटाए थे। हमने सूत्र का उपयोग करके निम्न क्राइटेरिया सेट किया है:
=B5<>""
- सबसे पहले, एडवांस्ड फिल्टर डायलॉग बॉक्स पर जाएं। निम्न क्राइटेरिया रेंज डालें:
लिस्ट रेंज: B4:F14
क्राइटेरिया रेंज: C17:G18
- अब OK दबाएं।
- तो, हमें रिक्त सेल से मुक्त डेटासेट मिलता है।
16. एडवांस्ड फिल्टर क्राइटेरिया रेंज का उपयोग करके पहले 5 रिकॉर्ड ढूंढें
अब हम किसी भी प्रकार के डेटासेट से पहले 5 रिकॉर्ड निकालने के लिए एडवांस्ड फिल्टर विकल्प लागू करेंगे। इस उदाहरण में, हम बिक्री कॉलम के पहले पांच मान लेंगे। ऐसा करने के लिए हम पहले निम्न सूत्र के आधार पर क्राइटेरिया सेट करेंगे:
=F5>=LARGE($F$5:$F$14,5)
उसके बाद, बस निम्नलिखित चरणों का पालन करें:
- शुरुआत में, एडवांस्ड फिल्टर डायलॉग बॉक्स पर जाएं। निम्न क्राइटेरिया रेंज डालें:
लिस्ट रेंज: B4:F14
क्राइटेरिया रेंज: C17:C18
- OK दबाएं।
- अंत में, हमें बिक्री कॉलम के शीर्ष पांच रिकॉर्ड मिलते हैं।
17. एडवांस्ड फिल्टर क्राइटेरिया रेंज का उपयोग करके निचले पांच रिकॉर्ड ढूंढें
हम एडवांस्ड फिल्टर विकल्प का उपयोग करके निचले पांच रिकॉर्ड भी ढूंढ सकते हैं। बिक्री कॉलम के लिए निचले पांच रिकॉर्ड ढूंढने के लिए, हम नीचे दिए गए सूत्र का उपयोग करके निम्न क्राइटेरिया बनाएंगे:
=F5<=SMALL($F$5:$F$14,5)
फिर इस क्रिया को करने के लिए नीचे दिए गए चरणों का पालन करें:
- सबसे पहले, एडवांस्ड फिल्टर डायलॉग बॉक्स में निम्न क्राइटेरिया रेंज डालें:
लिस्ट रेंज: B4:F14
क्राइटेरिया रेंज: C17:C18
- उसके बाद, OK दबाएं।
- अंत में, हम बिक्री कॉलम के निचले पांच मान देख सकते हैं।
18. एडवांस्ड फिल्टर क्राइटेरिया रेंज का उपयोग करके सूची की मिलान प्रविष्टियों के अनुसार पंक्तियाँ फ़िल्टर करें
कभी-कभी हमें किसी डेटासेट के दो कॉलम या पंक्तियों की तुलना करके विशेष मानों को हटाने या रखने की आवश्यकता होती है। हम इस प्रकार की क्रिया करने के लिए मिलान प्रविष्टि विकल्प का उपयोग कर सकते हैं।
18.1 सूची में आइटम से मिलान
मान लें हमारे पास शहरों के दो कॉलम वाला निम्नलिखित डेटासेट है। हम इन दो कॉलम के बीच केवल मिलान वाली प्रविष्टियाँ लेंगे। ऐसा करने के लिए हम नीचे दिए गए सूत्र का उपयोग करके निम्न क्राइटेरिया सेट करेंगे:
=C5=E5
इस क्रिया को करने के लिए बस निम्नलिखित चरणों का पालन करें:
- शुरुआत में, एडवांस्ड फिल्टर विकल्प खोलें। निम्न क्राइटेरिया रेंज डालें:
लिस्ट रेंज: B4:F14
क्राइटेरिया रेंज: C17:C18
- OK दबाएं।
- अंत में, हम शहरों के दो कॉलम में समान मान देख सकते हैं।
18.2 सूची में आइटम से मेल न खाना
पिछला उदाहरण मिलान वाली प्रविष्टियों के लिए था जबकि यह उदाहरण गैर-मिलान वाली प्रविष्टियों को फ़िल्टर करेगा। हम निम्न सूत्र का उपयोग करके क्राइटेरिया सेट करेंगे:
=C5<>E5
आइए देखें कि यह कैसे करना है:
- सबसे पहले, एडवांस्ड फिल्टर से निम्न क्राइटेरिया रेंज डालें:
लिस्ट रेंज: B4:F14
क्राइटेरिया रेंज: C17:C18
- फिर, OK दबाएं।
- अंत में, हमें कॉलम C और कॉलम E में शहरों के मान मिलेंगे जो एक दूसरे से मेल नहीं खाते।
निष्कर्ष
इस लेख में, हमने एडवांस्ड फिल्टर क्राइटेरिया रेंज विकल्प के सभी तरीकों को कवर करने का प्रयास किया है। इस लेख में जोड़ा गया हमारा प्रैक्टिस वर्कबुक डाउनलोड करें और स्वयं अभ्यास करें। यदि आपको कोई भ्रम है या कोई सुझाव है तो नीचे टिप्पणी छोड़ दें, हम जल्द से जल्द आपको जवाब देने का प्रयास करेंगे।
संबंधित लेख
- एक्सेल एडवांस्ड फिल्टर काम नहीं कर रहा (2 कारण और समाधान)
- डायनामिक एडवांस्ड फिल्टर एक्सेल (VBA और मैक्रो)
- VBA में एडवांस्ड फिल्टर का उपयोग कैसे करें (चरण-दर-चरण दिशानिर्देश)
