एक्सेल में मास्टरिंग: यूनिक वैल्यू फिल्टर करने के 8 सरल तरीके

फिल्टर यूनिक डेटासेट में कई प्रविष्टियों से निपटने का एक प्रभावी तरीका है। एक्सेल यूनिक डेटा फिल्टर करने या डुप्लिकेट हटाने के लिए कई सुविधाएँ प्रदान करता है, चाहे हम इसे कुछ भी कहें। इस लेख में, हम एक सैंपल डेटासेट से यूनिक डेटा फिल्टर करने के तरीकों को प्रदर्शित करेंगे।

मान लीजिए हमारे पास एक एक्सेल डेटासेट में तीन सरल कॉलम हैं जिनमें ऑर्डर डेट, कैटेगरी, और उत्पाद हैं। हम पूरे डेटासेट के भीतर यूनिक ऑर्डर किए गए उत्पाद चाहते हैं।

एक्सेल वर्कबुक डाउनलोड करें

एक्सेल में यूनिक वैल्यू फिल्टर करने के 8 आसान तरीके

विधि 1: यूनिक वैल्यू फिल्टर करने के लिए एक्सेल रिमूव डुप्लीकेट फीचर का उपयोग

एक बड़े डेटासेट में प्रविष्टियों को समझने के लिए, हमें कभी-कभी डुप्लिकेट हटाने की आवश्यकता होती है। एक्सेल डेटासेट से डुप्लिकेट प्रविष्टियों को छोड़ने के लिए डेटा टैब में डुप्लीकेट हटाएं सुविधा प्रदान करता है। इस मामले में, हम कैटेगरी और उत्पाद कॉलम से डुप्लिकेट हटाना चाहते हैं। परिणामस्वरूप, हम ऐसा करने के लिए डुप्लीकेट हटाएं सुविधा का उपयोग कर सकते हैं।

चरण 1: रेंज चुनें (यानी, कैटेगरी और उत्पाद) फिर डेटा टैब पर जाएं > डुप्लीकेट हटाएं चुनें (डेटा टूल्स सेक्शन से)।

चरण 2: डुप्लीकेट हटाएं विंडो दिखाई देती है। डुप्लीकेट हटाएं विंडो में,

सभी कॉलम चेक करें।

मेरे डेटा में हेडर हैं विकल्प पर टिक करें।

ओके पर क्लिक करें।

चरण 3: एक पुष्टिकरण डायलॉग बॉक्स दिखाई देता है जिसमें कहा जाता है 8 डुप्लीकेट मान मिले और हटाए गए; 7 यूनिक मान शेष।

ओके पर क्लिक करें।

सभी चरण नीचे दी गई इमेज में दिखाए गए परिणाम की ओर ले जाते हैं।

विधि 2: यूनिक वैल्यू फिल्टर करने के लिए कंडीशनल फॉर्मेटिंग का उपयोग

यूनिक को फ़िल्टर करने का दूसरा तरीका है कंडीशनल फॉर्मेटिंग। एक्सेल कंडीशनल फॉर्मेटिंग कई मानदंडों के साथ सेल्स को फॉर्मेट कर सकता है। हालांकि, इस मामले में, हम एक रेंज (यानी, उत्पाद कॉलम) में सेल्स को कंडीशनली फॉर्मेट करने के लिए एक फॉर्मूला का उपयोग करते हैं। कंडीशनल फॉर्मेटिंग लागू करने के दो विकल्प हैं; एक है यूनिक वैल्यू फिल्टर करने के लिए कंडीशनल फॉर्मेटिंग और दूसरा है रेंज से डुप्लीकेट मान छिपाना।

2.1. यूनिक वैल्यू फिल्टर करने के लिए कंडीशनल फॉर्मेटिंग

इस मामले में, हम एक्सेल में यूनिक प्रविष्टियों को फ़िल्टर करने के लिए कंडीशनल फॉर्मेटिंग विकल्पों में एक फॉर्मूला का उपयोग करते हैं।

चरण 1: रेंज चुनें (यानी, उत्पाद 1) फिर होम टैब पर जाएं > कंडीशनल फॉर्मेटिंग चुनें (स्टाइल्स सेक्शन से) > नया नियम चुनें।

चरण 2: नया फॉर्मेटिंग नियम विंडो पॉप अप होती है। नया फॉर्मेटिंग नियम विंडो में,

नियम प्रकार चुनें विकल्प के तहत कौन से सेल फॉर्मेट करने हैं यह निर्धारित करने के लिए फॉर्मूला का उपयोग करें चुनें।

नियम विवरण संपादित करें विकल्प के तहत निम्नलिखित फॉर्मूला टाइप करें।

फॉर्मूला में, हमने एक्सेल को D कॉलम में प्रत्येक सेल को यूनिक (यानी, 1 के बराबर) के रूप में गिनने का निर्देश दिया। यदि प्रविष्टियाँ लगाई गई शर्त से मेल खाती हैं तो यह TRUE लौटाता है और सेल्स को कलर फॉर्मेट करता है।

फॉर्मेट पर क्लिक करें।

चरण 3: एक पल में, सेल फॉर्मेट करें विंडो दिखाई देती है। सेल फॉर्मेट करें विंडो में,

फ़ॉन्ट सेक्शन में- नीचे दी गई इमेज में दर्शाए अनुसार कोई भी फॉर्मेटिंग रंग चुनें।

फिर ओके पर क्लिक करें।

चरण 4: पिछले चरण में ओके पर क्लिक करने से आप फिर से नया फॉर्मेटिंग नियम विंडो पर पहुंच जाते हैं। नया फॉर्मेटिंग नियम विंडो में, आप यूनिक प्रविष्टियों का पूर्वावलोकन देख सकते हैं।

ओके पर क्लिक करें।

अंत में, आप यूनिक प्रविष्टियों को कलर फॉर्मेटेड प्राप्त करते हैं जैसा कि आप उन्हें नीचे दी गई तस्वीर के समान चाहते हैं।

2.2. डुप्लीकेट छिपाने के लिए कंडीशनल फॉर्मेटिंग

यूनिक मानों से छेड़छाड़ किए बिना, हम कंडीशनल फॉर्मेटिंग का उपयोग करके केवल डुप्लीकेट मान छिपा सकते हैं। डुप्लीकेट छिपाने के लिए, हमें वही फॉर्मूला लागू करना होगा जो हमने यूनिक को फ़िल्टर करने के लिए किया था, सिवाय इसके कि उन्हें 1 से अधिक मान असाइन करना होगा। व्हाइट फ़ॉन्ट रंग असाइन करने के बाद, हम उन्हें बाकी प्रविष्टियों से छिपा सकते हैं।

चरण 1: विधि 2.1 के चरण 1 से 2 दोहराएं लेकिन डाले गए फॉर्मूला को नीचे वाले से बदलें।

फॉर्मूला एक्सेल को D कॉलम में प्रत्येक सेल को डुप्लीकेट (यानी, 1 से अधिक) के रूप में गिनने का निर्देश देता है। यदि प्रविष्टियाँ लगाई गई शर्त से मेल खाती हैं तो यह TRUE लौटाता है और कलर फॉर्मेट (यानी, छिपाना) करता है।

फॉर्मेट पर क्लिक करें।

चरण 2: फॉर्मेट पर क्लिक करने से आप सेल फॉर्मेट करें विंडो पर पहुंच जाते हैं। सेल फॉर्मेट करें विंडो में,

फ़ॉन्ट रंग सफेद चुनें।

फिर ओके पर क्लिक करें।

चरण 3: फ़ॉन्ट रंग चुनने के बाद, ओके पर क्लिक करने से आप फिर से नया फॉर्मेटिंग नियम विंडो पर पहुंच जाते हैं। आप पूर्वावलोकन को धुंधला देख सकते हैं क्योंकि हम सफेद को फ़ॉन्ट रंग के रूप में चुनते हैं।

ओके पर क्लिक करें।

सभी चरणों का पालन करने से आप डुप्लीकेट मानों के लिए नीचे दी गई इमेज के समान चित्रण पर पहुंचते हैं।

आपको सफेद को फ़ॉन्ट रंग के रूप में चुनना होगा अन्यथा डुप्लीकेट प्रविष्टियाँ नहीं छिपेंगी।

और पढ़ें: फॉर्मूला का उपयोग करके एक्सेल में डेटा कैसे फिल्टर करें

विधि 3: यूनिक वैल्यू फिल्टर करने के लिए डेटा टैब एडवांस्ड फिल्टर फीचर का उपयोग

पहले की विधियाँ यूनिक फिल्टर करने के लिए डेटासेट से प्रविष्टियों को डिलीट या हटा देती हैं। कुछ डेटासेट पर काम करते समय यह काफी खतरनाक है। ऐसी स्थितियाँ हो सकती हैं जहाँ हम रॉ डेटासेट को बदल नहीं सकते, उन मामलों में हम वांछित स्थान पर यूनिक फ़िल्टर करने के लिए एडवांस्ड फिल्टर विकल्प का उपयोग कर सकते हैं।

चरण 1: रेंज चुनें (यानी, उत्पाद कॉलम)। फिर डेटा टैब पर जाएं > एडवांस्ड चुनें (सॉर्ट एंड फिल्टर सेक्शन से)।

चरण 2: एडवांस्ड फिल्टर विंडो दिखाई देती है। एडवांस्ड फिल्टर विंडो में,

एक्शन विकल्प के तहत दूसरे स्थान पर कॉपी करें एक्शन चुनें। आप या तो लिस्ट को स्थान पर फ़िल्टर करें, या दूसरे स्थान पर कॉपी करें चुन सकते हैं, हालांकि हम रॉ डेटा को बदले बिना बाद वाला चुन रहे हैं।

कॉपी टू विकल्प में एक स्थान असाइन करें (यानी, F4)।

केवल यूनिक रिकॉर्ड विकल्प चेक करें।

ओके पर क्लिक करें।

ओके पर क्लिक करने से आपको चरणों में निर्देशित अनुसार वांछित स्थान पर यूनिक वैल्यू मिलती हैं।

विधि 4: एक्सेल UNIQUE फंक्शन का उपयोग करके यूनिक वैल्यू फिल्टर करें

दूसरे कॉलम में यूनिक वैल्यू प्रदर्शित करना UNIQUE फंक्शन द्वारा भी प्राप्त किया जा सकता है। UNIQUE फंक्शन एक रेंज या ऐरे से यूनिक प्रविष्टियों की सूची लाता है। UNIQUE फंक्शन का सिंटैक्स है

UNIQUE (array, [by_col], [exactly_once])

आर्गुमेंट्स,

array; रेंज, या ऐरे जहां से यूनिक वैल्यू निकाले जाते हैं।

[by_col]; मानों की तुलना और निकालने के तरीके, पंक्ति द्वारा = FALSE (डिफ़ॉल्ट) और कॉलम द्वारा = TRUE. [वैकल्पिक]

[exactly_once]; एक बार आने वाले मान = TRUE और मौजूदा यूनिक मान = FALSE (डिफ़ॉल्ट रूप से)। [वैकल्पिक]

चरण 1: किसी भी खाली सेल (यानी, E5) में निम्नलिखित फॉर्मूला टाइप करें।

चरण 2: एंटर दबाएं फिर एक सेकंड में सभी यूनिक प्रविष्टियाँ कॉलम में पॉप अप होती हैं जैसा कि नीचे दी गई तस्वीर में दिखाया गया है।

UNIQUE फंक्शन एक बार में सभी यूनिक प्रविष्टियों को फैला देता है। हालांकि, आप UNIQUE फंक्शन का उपयोग एक्सेल 365 संस्करण के अलावा नहीं कर सकते।

समान पठन

• सेल वैल्यू के आधार पर एक्सेल फिल्टर डेटा (6 कुशल तरीके)
• एक्सेल में फिल्टर कैसे जोड़ें (4 तरीके)
• एक्सेल फिल्टर के लिए शॉर्टकट (उदाहरण के साथ 3 त्वरित उपयोग)
• एक्सेल में टेक्स्ट फिल्टर का उपयोग कैसे करें (5 उदाहरण)

विधि 5: UNIQUE और FILTER फंक्शन का उपयोग (मानदंड के साथ)

विधि 4 में, हम यूनिक वैल्यू को फैलाने के लिए UNIQUE फंक्शन का उपयोग करते हैं। क्या होगा यदि हम किसी शर्त के आधार पर यूनिक प्रविष्टियाँ चाहते हैं? मान लीजिए हम अपने डेटासेट से किसी विशेष कैटेगरी के यूनिक उत्पाद नाम चाहते हैं।

इस मामले में, हम अपने डेटासेट से बार्स (यानी, E4) श्रेणी के यूनिक उत्पाद नाम चाहते हैं।

चरण 1: किसी भी सेल (यानी, E5) में नीचे दिया गया फॉर्मूला लिखें।

फॉर्मूला D5:D19 रेंज को फ़िल्टर करने का निर्देश देता है, रेंज C5:C19 पर शर्त लगाकर सेल E4 के बराबर होने की।

चरण 2: एंटर दबाएं। उसके बाद बार्स श्रेणी के अंतर्गत उत्पाद, बार्स कॉलम के सेल्स में दिखाई देते हैं जैसा कि निम्नलिखित स्क्रीनशॉट में दिखाया गया है।

आप यूनिक उत्पादों को फ़िल्टर करने के लिए किसी भी कैटेगरी को चुन सकते हैं। यह विशाल बिक्री डेटासेट को संभालने का काफी प्रभावी तरीका है। FILTER फंक्शन केवल एक्सेल 365 में उपलब्ध है।

और पढ़ें: एक्सेल में मल्टीपल क्राइटेरिया फिल्टर करें

विधि 6: MATCH और INDEX फंक्शन का उपयोग (ऐरे फॉर्मूला)

सरल प्रदर्शन के लिए, हम बिना ब्लैंक या केस-सेंसिटिव प्रविष्टियों वाले डेटासेट का उपयोग करते हैं। तो, हम ऐसे डेटासेट को कैसे संभाल सकते हैं जिसमें ब्लैंक और केस-सेंसिटिव प्रविष्टियाँ हैं? एक रास्ता दिखाने से पहले, आइए एक संयुक्त फॉर्मूला का उपयोग करके नॉन-ब्लैंक रेंज (यानी, उत्पाद 1) को फ़िल्टर करें। इस मामले में, हम यूनिक फ़िल्टर करने के लिए MATCH और INDEX फंक्शन का उपयोग करते हैं।

6.1. MATCH और INDEX फंक्शन नॉन-ब्लैंक रेंज से यूनिक वैल्यू फिल्टर करें

हम देख सकते हैं कि उत्पाद 1 रेंज में कोई मौजूदा ब्लैंक सेल नहीं हैं।

चरण 1: सेल G5 में यूनिक को फ़िल्टर करने के लिए निम्नलिखित फॉर्मूला टाइप करें।

फॉर्मूला द्वारा,

पहला, COUNTIF($G$4:G4, $D$5:$D$19); रेंज (यानी, $G$4:G4) में शर्त (यानी, $D$5:$D$19) का पालन करने वाले सेल्स की संख्या गिनता है। COUNTIF 1 लौटाता है यदि यह रेंज में $G$4:G4 पाता है अन्यथा 0।

दूसरा, MATCH(0, COUNTIF($G$4:G4, $D$5:$D$19), 0)); रेंज में एक उत्पाद की सापेक्ष स्थिति लौटाता है।

अंत में, INDEX($D$5:$D$19, MATCH(0, COUNTIF($G$4:G4, $D$5:$D$19), 0)); शर्त को पूरा करने वाली सेल प्रविष्टियाँ लौटाता है।

IFERROR फंक्शन फॉर्मूला को परिणामों में कोई त्रुटि प्रदर्शित करने से रोकता है।

चरण 2: चूंकि फॉर्मूला एक ऐरे फॉर्मूला है, CTRL+SHIFT+ENTER एक साथ दबाएं। उत्पाद 1 रेंज से सभी यूनिक प्रविष्टियाँ दिखाई देती हैं।

6.2. MATCH और INDEX फंक्शन रेंज में मौजूदा ब्लैंक सेल से यूनिक वैल्यू फिल्टर करें

अब, उत्पाद 2 रेंज में, हम कई ब्लैंक सेल देख सकते हैं। ब्लैंक सेल के बीच यूनिक को फ़िल्टर करने के लिए, हमें ISBLANK फंक्शन डालना होगा।

चरण 1: सेल H5 में नीचे दिया गया फॉर्मूला पेस्ट करें।

यह फॉर्मूला उसी तरह काम करता है जैसा हमने इसे 6.1. सेक्शन में वर्णित किया था। हालांकि, IF फंक्शन के साथ ISBLANK फंक्शन के लॉजिकल टेस्ट के कारण फॉर्मूला रेंज में किसी भी ब्लैंक सेल को अनदेखा करने में सक्षम होता है।

चरण 2: CTRL+SHIFT+ENTER दबाएं और फॉर्मूला ब्लैंक सेल को अनदेखा करता है और निम्नलिखित तस्वीर में दर्शाए अनुसार सभी यूनिक प्रविष्टियाँ लाता है।

6.3. MATCH और INDEX फंक्शन केस-सेंसिटिव रेंज से यूनिक वैल्यू फिल्टर करें

यदि हमारे डेटासेट में केस-सेंसिटिव प्रविष्टियाँ हैं, तो हमें यूनिक को फ़िल्टर करने के लिए FREQUENCY फंक्शन के साथ TRANSPOSE और ROW फंक्शन का उपयोग करना होगा।

चरण 1: सेल I5 में नीचे दिया गया फॉर्मूला लागू करें।

फॉर्मूला के खंड,

• TRANSPOSE($I$4:I4); पिछले मानों को ट्रांसपोज़ करके सेमीकोलन को कॉमा में बदलता है। (यानी, TRANSPOSE({“यूनिक वैल्यू (केस सेंसिटिव)”;होल व्हीट”}) बन जाता है {“यूनिक वैल्यू (केस सेंसिटिव)”, “होल व्हीट”}
• EXACT($F$5:$F$19, TRANSPOSE($I$4:I4); जांचता है कि स्ट्रिंग्स समान और केस-सेंसिटिव हैं या नहीं।
• IF(EXACT($F$5:$F$19, TRANSPOSE($I$4:I4)), MATCH(ROW($F$5:$F$19), ROW($F$5:$F$19)); TRUE होने पर ऐरे में स्ट्रिंग की सापेक्ष स्थिति लौटाता है।
• FREQUENCY(IF(EXACT($F$5:$F$19, TRANSPOSE($I$4:I4)), MATCH(ROW($F$5:$F$19), ROW($F$5:$F$19)), “”); गणना करता है कि ऐरे में स्ट्रिंग कितनी बार मौजूद है।
• MATCH(0, FREQUENCY(IF(EXACT($F$5:$F$19, TRANSPOSE($I$4:I4)), MATCH(ROW($F$5:$F$19), ROW($F$5:$F$19)), “”), MATCH(ROW($F$5:$F$19), ROW($F$5:$F$19))), 0)); ऐरे में पहला फाल्स (यानी, खाली) मान ढूंढता है।
• INDEX($F$5:$F$19, MATCH(0, FREQUENCY(IF(EXACT($F$5:$F$19, TRANSPOSE($I$4:I4)), MATCH(ROW($F$5:$F$19), ROW($F$5:$F$19)), “”), MATCH(ROW($F$5:$F$19), ROW($F$5:$F$19))), 0)); ऐरे से यूनिक वैल्यू लौटाता है।

चरण 2: आपको CTRL+SHIFT+ENTER एक साथ दबाने की आवश्यकता है और केस-सेंसिटिव यूनिक वैल्यू सेल्स में दिखाई देती हैं।

तो, सभी प्रकार की प्रविष्टियों को उनके संबंधित कॉलम में सॉर्ट करने के बाद पूरा डेटासेट नीचे दी गई इमेज जैसा दिखता है।

आप अपनी मांग पूरी करने के लिए किसी भी उत्पाद डेटा प्रकार को बदल सकते हैं और उसके अनुसार फॉर्मूला लागू कर सकते हैं।

विधि 7: VBA मैक्रो कोड का उपयोग करके एक्सेल फिल्टर यूनिक वैल्यू

डेटासेट से, हम जानते हैं कि हमारे पास एक उत्पाद कॉलम है, और हम कॉलम से यूनिक वैल्यू चाहते हैं। काम पूरा करने के लिए, हम VBA मैक्रो कोड का उपयोग कर सकते हैं। हम एक कोड लिख सकते हैं जो चयन से मान असाइन करता है फिर इसे लूप के माध्यम से भेजता है जब तक कि यह सभी डुप्लीकेट से छुटकारा नहीं पा लेता।

VBA मैक्रो कोड लागू करने से पहले, आइए सुनिश्चित करें कि हमारे पास निम्नलिखित प्रकार का डेटासेट है और हम उस रेंज को चुनते हैं जहां से हम यूनिक फ़िल्टर करना चाहते हैं।

चरण 1: मैक्रो कोड लिखने के लिए, ALT+F11 दबाकर माइक्रोसॉफ्ट विजुअल बेसिक विंडो खोलें। विंडो में, इन्सर्ट टैब ( टूलबार में) पर जाएं > मॉड्यूल चुनें।

चरण 2: मॉड्यूल विंडो दिखाई देती है। मॉड्यूल में, निम्नलिखित कोड पेस्ट करें।

Sub Unique_Values() Dim Range As Variant, prdct As Variant Dim mrf As Object Dim i As Long Set mrf = CreateObject("scripting.dictionary") Range = Selection For i = 1 To UBound(Range) mrf(Range(i, 1) & "") = "" Next prdct = mrf.keys Selection.ClearContents Selection(1, 1).Resize(mrf.Count, 1) = Application.Transpose(prdct) End Sub

मैक्रो कोड में,

वेरिएबल घोषित करने के बाद, mrf = CreateObject(“scripting.dictionary”) एक ऑब्जेक्ट बनाता है जिसे mrf को असाइन किया जाता है।

चयन को रेंज को असाइन किया जाता है। For लूप प्रत्येक सेल लेता है फिर डुप्लीकेट के लिए रेंज के साथ मिलान करता है। उसके बाद, कोड चयन को क्लियर करता है और यूनिक के साथ प्रकट होता है।

चरण 3: मैक्रो चलाने के लिए F5 दबाएं फिर वर्कशीट पर लौटकर, आप चयन से सभी यूनिक वैल्यू देखते हैं।

विधि 8: यूनिक वैल्यू फिल्टर करने के लिए पिवट टेबल का उपयोग

पिवट टेबल चयनित सेल्स से यूनिक आइटम सूची निर्यात करने के लिए एक मजबूत टूल है। एक्सेल में, हम आसानी से एक पिवट टेबल डाल सकते हैं और जो हम यहां चाहते हैं उसे प्राप्त कर सकते हैं।

चरण 1: एक निश्चित रेंज चुनें (यानी, उत्पाद)। बाद में, इन्सर्ट टैब पर जाएं > पिवट टेबल चुनें (टेबल्स सेक्शन से)।

चरण 2: टेबल या रेंज से पिवटटेबल विंडो दिखाई देती है। विंडो में,

रेंज (यानी, D4:D19) ऑटोमैटिकली चुनी जाएगी।