Excel में यूनिक वैल्यू कैसे गिनें: जो आपको जानना चाहिए

इस गाइड में, मैं आपको Excel में यूनिक वैल्यू गिनना दिखाऊंगा। मैं कुछ खास स्थितियाँ भी दिखाऊंगा, जैसे केस-सेंसिटिविटी को कैसे हैंडल करें, या एक या अधिक शर्तों के आधार पर यूनिक वैल्यू कैसे गिनें।

अपनी Excel स्किल्स को आगे बढ़ाने के लिए, हमारे Excel Fundamentals स्किल ट्रैक में नामांकन करें और विशेषज्ञ बनें।

Excel में Unique Values बनाम Distinct Values

ये अवधारणाएँ कभी-कभी गड़बड़ा जाती हैं, लेकिन यूनिक वैल्यू और डिस्टिंक्ट वैल्यू एक जैसी नहीं होतीं।

• Distinct values वे अलग-अलग मान होते हैं जिनमें डुप्लिकेट निकाल दिए जाते हैं। उदाहरण के लिए, सूची A, A, B, C, C, D में डिस्टिंक्ट वैल्यू हैं A, B, C, D. 
• Unique Values, दूसरी ओर, वे मान होते हैं जो डेटासेट में केवल एक बार आते हैं। इसी उदाहरण का उपयोग करते हुए, यूनिक वैल्यू हैं B और D (क्योंकि A और C एक से अधिक बार आते हैं)।

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

Excel में यूनिक वैल्यू गिनने के दो तरीके

Excel में यूनिक वैल्यू गिनने के दो आम तरीके हैं।

UNIQUE() फ़ंक्शन का उपयोग करके Excel में यूनिक वैल्यू कैसे गिनें

यूनिक वैल्यू गिनने का सबसे आसान तरीका है UNIQUE() फ़ंक्शन और COUNTA() फ़ंक्शन का उपयोग करना। UNIQUE() फ़ंक्शन किसी रेंज से सभी यूनिक वैल्यू निकालता है, और COUNTA() यह गिनता है कि कितनी यूनिक वैल्यू मौजूद हैं। 

COUNTA(UNIQUE(range, false, true))

उदाहरण के लिए, मैंने एक छोटे डेटासेट पर निम्न फ़ॉर्मूला लागू किया, और इसने मुझे यूनिक वैल्यू दीं।

=COUNTA(UNIQUE(A2:A8, false, true))

यदि आप इसकी जगह यह सिंटैक्स उपयोग करें, =COUNTA(UNIQUE(A2:A8)), जिसमें वह तीसरा पैरामीटर नहीं है जिसे हमने TRUE सेट किया था, तो परिणाम डिस्टिंक्ट काउंट होगा, यूनिक नहीं। तीसरे आर्ग्युमेंट में TRUE सेट करने से फ़ंक्शन को यूनिक काउंट लौटाने के लिए कहा जाता है। दोनों चीज़ें UNIQUE() फ़ंक्शन के भीतर हैं, इसलिए शुरुआत में यह थोड़ा भ्रमित कर सकता है।

SUM() और COUNTIF() के साथ Excel में यूनिक वैल्यू कैसे गिनें

यदि आप UNIQUE() फ़ंक्शन की बारीकियों को लेकर थोड़े अनिश्चित हैं, या आपके पास Excel 365 नहीं है, तो जान लें कि हम SUM() को IF() और COUNTIF() के साथ मिलाकर भी यूनिक वैल्यू गिन सकते हैं।

=SUM(IF(COUNTIF(range, range)=1,1,0))

उदाहरण के लिए, मेरे पास A2:A8 रेंज में डेटा है और मुझे यूनिक वैल्यू गिननी हैं, तो मैं निम्न फ़ॉर्मूला लिखता हूँ:

SUM(IF(COUNTIF(A2:A8, A2:A8)=1,1,0))

यहाँ COUNTIF() सूची में जाता है और देखता है कि प्रत्येक नाम कितनी बार आता है। यदि कोई नाम केवल एक बार आता है, तो इसे 1 गिना जाता है। यदि यह एक से अधिक बार आता है, तो एक बड़ा नंबर मिलता है। फिर, IF() उन परिणामों को फ़िल्टर करता है—1 को वैसा ही रखता है, जबकि बाकी सबको 0 में बदल देता है। अंत में, SUM() सभी 1 का योग करता है और हमें यूनिक वैल्यू की कुल संख्या देता है।

Excel में यूनिक डेटा टाइप कैसे गिनें

कभी-कभी हमारे डेटासेट में मिले-जुले डेटा टाइप होते हैं और हमें उन्हें अलग-अलग विश्लेषित करना पड़ता है। शुरुआत में यह थोड़ा कठिन लग सकता है, लेकिन Excel में यह संभव है। आइए दो तरीकों को देखें—एक यूनिक टेक्स्ट वैल्यू गिनने के लिए और दूसरा यूनिक नंबरों के लिए।

Excel में यूनिक टेक्स्ट वैल्यू कैसे गिनें

यदि आप यूनिक टेक्स्ट वैल्यू गिनना चाहते हैं, तो ISTEXT(), COUNTIF(), और SUM() फ़ंक्शंस को मिलाएँ। ISTEXT() जाँचता है कि कोई वैल्यू टेक्स्ट है या नहीं, जबकि COUNTIF() गिनता है कि प्रत्येक वैल्यू कितनी बार आती है, और SUM() डिस्टिंक्ट टेक्स्ट एंट्री का योग करता है। 

मैंने सैंपल डेटा पर निम्न फ़ॉर्मूला लागू किया, और इसने तुरंत दिखा दिया कि कितनी यूनिक टेक्स्ट वैल्यू मौजूद हैं।

=SUM(IF(ISTEXT(A2:A9)*COUNTIF(A2:A9,A2:A9)=1,1,0))

Excel में यूनिक संख्यात्मक वैल्यू कैसे गिनें

यदि आप टेक्स्ट के बजाय यूनिक नंबर गिनना चाहते हैं, तो वही फ़ॉर्मूला उपयोग करें लेकिन ISTEXT() को ISNUMBER() से बदल दें।

ISNUMBER() केवल संख्यात्मक मानों पर विचार करेगा, जबकि COUNTIF() और SUM() यूनिकनेस संभालेंगे। मैंने ऊपर वाले फ़ॉर्मूले को उसी डेटा पर बदला, और अब यह केवल यूनिक संख्यात्मक वैल्यू की संख्या दिखाता है:

=SUM(IF(ISNUMBER(A2:A9)*COUNTIF(A2:A9,A2:A9)=1,1,0))

Excel में शर्तों के साथ यूनिक वैल्यू कैसे गिनें

अब जब हमने यूनिक वैल्यू गिनने के बुनियादी तरीके कवर कर लिए हैं, तो कुछ उन्नत तकनीकों का पता लगाएँ।

शर्तों के आधार पर यूनिक वैल्यू कैसे गिनें

किसी विशेष शर्त के आधार पर यूनिक वैल्यू गिनने के लिए, मैं यह सिंटैक्स उपयोग करता हूँ:

=IFERROR(ROWS(UNIQUE(FILTER(range, criteria_range=criteria))), 0)

इस फ़ॉर्मूले में, FILTER() विशेष मानदंड के आधार पर रेंज को फ़िल्टर करता है, UNIQUE() डुप्लिकेट हटाता है, ROWS() परिणामों को गिनता है, और IFERROR() कोई मैच न मिलने पर Not Found लौटाकर त्रुटियों से बचाता है।

मान लीजिए मेरे पास Employees और उनके Departments की सूची है, और मुझे किसी विशेष विभाग में काम करने वाले यूनिक कर्मचारियों की संख्या गिननी है। मैं निम्न फ़ॉर्मूला उपयोग करूंगा:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A20, B2:B20=F1))), "Not Found")

यहाँ, A2:A10 उस कर्मचारी नामों की रेंज है जिसे मैं फ़िल्टर करना चाहता हूँ और B2:B10=F1 जाँचता है कि कौन-से कर्मचारी उस विभाग से संबंधित हैं जो F1 में लिखा है।

एकाधिक कॉलम के आधार पर यूनिक रो कैसे गिनें

यदि मैं किसी विशेष विभाग में उन यूनिक कर्मचारियों को गिनना चाहता हूँ जिनकी सैलरी $50,000 से कम है, तो मैं यह फ़ॉर्मूला उपयोग करता हूँ:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A10, (B2:B10=F1) * (C2:C10>F2)))), "Not Found")

यहाँ, A2:A10 कर्मचारी नामों की रेंज है, B2:B10=F1 यह जाँचता है कि कौन-से कर्मचारी उस विभाग से हैं, जो F1 में लिखा है, और C2:C10>F2 यह जाँचता है कि सैलरी F2 में दिए गए अमाउंट से कम है या नहीं।

केस-सेंसिटिव यूनिक काउंट कैसे संभालें

डिफ़ॉल्ट रूप से, Excel केस-सेंसिटिव होता है। उदाहरण के लिए, Apple और APPLE अलग माने जाते हैं। ऐसे मामलों को पकड़ने के लिए, मैं एक हेल्पर कॉलम बनाता हूँ और यह फ़ॉर्मूला लगाता हूँ:

=IF(SUM((--EXACT($A$2:$A$11,A2)))=1,"Unique","Duplicate")

और अब, यूनिक वैल्यू की गिनती पाने के लिए, मैं COUNTIF() फ़ंक्शन इस तरह उपयोग करता हूँ:

=COUNTIF(B2:B11,"Unique")

इसके बजाय Distinct Values कैसे गिनें

अब, एक अंतिम सेक्शन के रूप में, यदि आप वास्तव में distinct वैल्यू गिनना चाहते थे, तो मैं डिस्टिंक्ट वैल्यू गिनने के कई तरीके दिखाऊंगा। लेकिन पहले, यह तालिका देखें ताकि आप अंतर समझ सकें:

COUNTIF() और SUM() का उपयोग करके Excel में डिस्टिंक्ट वैल्यू कैसे गिनें

Excel में डिस्टिंक्ट वैल्यू गिनने के लिए, आप COUNTIF() और SUM() फ़ंक्शंस को मिला सकते हैं। COUNTIF() देखता है कि किसी दी गई रेंज में प्रत्येक वैल्यू कितनी बार आती है। फिर, SUM() COUNTIF() से लौटे मानों का योग करता है और डिस्टिंक्ट एंट्री की कुल संख्या देता है। 

=SUM(1/COUNTIF(range, range))

मान लीजिए मैं A2:A8 रेंज में डिस्टिंक्ट वैल्यू ढूँढना चाहता हूँ। मैं यह फ़ॉर्मूला दर्ज करता हूँ:

=SUM(1/COUNTIF(A2:A8, A2:A8))

यहाँ, COUNTIF() फ़ंक्शन यह जाँचता है कि सूची में प्रत्येक वैल्यू कितनी बार आती है।

1/COUNTIF(A2:A8, A2:A8) 1 को उस संख्या से भाग देता है जितनी बार प्रत्येक वैल्यू आती है। उदाहरण के लिए, यदि कोई संख्या दो बार आती है, तो प्रत्येक उदाहरण 0.5 (1/2) बन जाता है। यदि यह तीन बार आती है, तो 0.33 (1/3) होगा। 

क्योंकि हम इन भिन्नों को नहीं रखना चाहते, हम SUM() या SUMPRODUCT() का उपयोग करके सभी मानों को जोड़ सकते हैं। हर डुप्लिकेट के भिन्न मिलकर 1 बनते हैं, और डिस्टिंक्ट वैल्यू 1 ही रहती हैं क्योंकि 1/1 = 1:

=SUMPRODUCT(1/COUNTIF(A2:A8, A2:A8))

PivotTables का उपयोग करके Excel में डिस्टिंक्ट वैल्यू कैसे गिनें

Excel 2013 और Excel 2016 में PivotTable में डिस्टिंक्ट वैल्यू गिनने की बिल्ट-इन सुविधा है। इसका उपयोग करने के लिए, पहले डेटा चुनकर PivotTable बनाएँ: Insert पर जाएँ और PivotTable चुनें। एक डायलॉग बॉक्स दिखाई देगा—इस डायलॉग बॉक्स से Existing Worksheet चुनें और Add this data to the Data Model बॉक्स पर चेकमार्क करें।

अगला, अपनी इच्छित कॉलम को Values फ़ील्ड में ड्रैग करें। मेरे मामले में, मैं Fruits कॉलम को ड्रैग करता हूँ क्योंकि मुझे इसकी डिस्टिंक्ट वैल्यू गिननी हैं। फिर, ड्रॉप-डाउन मेनू से Value Field Settings पर क्लिक करें। एक पॉप-अप दिखाई देगा—वहाँ से Distinct Count चुनें। आप चाहें तो इस कॉलम को कस्टम नाम भी दे सकते हैं, लेकिन यह वैकल्पिक है।

फ़िल्टर का उपयोग करके Excel में डिस्टिंक्ट वैल्यू कैसे गिनें

यदि आपको फ़ॉर्मूला के साथ काम करना पसंद नहीं है, तो उन्नत फ़िल्टर का उपयोग करें। 

इस उदाहरण में, मेरे पास एक डेटासेट है और मैं एडवांस्ड फ़िल्टर का उपयोग करके डिस्टिंक्ट वैल्यू ढूँढना चाहता हूँ। इसके लिए, मैं उस रेंज का चयन करता हूँ जहाँ से मुझे वह वैल्यू ढूँढनी है। फिर, मैं Data टैब में जाकर Advanced पर क्लिक करता हूँ। 

अब, Advanced Filter डायलॉग बॉक्स में, मैं Copy to another location चुनता हूँ, और Copy to फ़ील्ड में वह डेस्टिनेशन सेल दर्ज करता हूँ जहाँ मुझे अपनी सूची दिखनी है। फिर Unique Records Only बॉक्स को चेक करें और OK पर क्लिक करें।

जब मेरे पास यूनिक वैल्यू की सूची आ जाती है, तो मैं ROWS() फ़ंक्शन का उपयोग करके इन यूनिक वैल्यू की गिनती निकालता हूँ:

=ROWS(D2:D5)

हालाँकि नीचे की छवि में विकल्प Unique records only लिखा है, यह वास्तव में डिस्टिंक्ट वैल्यू निकालता है। 

VBA मैक्रोज़ का उपयोग करके Excel में डिस्टिंक्ट वैल्यू कैसे गिनें

यदि आप बार-बार डिस्टिंक्ट वैल्यू गिनना चाहते हैं, तो VBA (Visual Basic for Applications) का उपयोग करें, जहाँ हम एक बार कोड लिखते हैं, और हर बार फ़ंक्शन को उसके नाम से कॉल करने पर यह पूरा प्रोसेस ऑटोमेट कर देता है।

इसे इस तरह उपयोग कर सकते हैं: Alt + F11 दबाएँ और Insert टैब के अंतर्गत Module विकल्प पर जाएँ। एक नया मॉड्यूल दिखाई देगा, और आप यहाँ अपना कोड पेस्ट कर सकते हैं। 

यह वह कोड है जो मैंने डिस्टिंक्ट वैल्यू गिनने के लिए बनाया:

Function CountUnique(rng As Range) As Integer
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    Dim cell As Range
    For Each cell In rng
        If Not dict.exists(cell.Value) And cell.Value <> "" 

Then,
            dict.Add cell.Value, Nothing
        End If
    Next cell
    
    CountUnique = dict.Count
End Function

इसके बाद, सेव करने के लिए Ctrl + S दबाएँ और VBA एडिटर बंद करने के लिए ALt + Q दबाएँ। अब, आप इस कस्टम फ़ंक्शन को शीट में कहीं भी कॉल कर सकते हैं और वह रेंज बताएँ जहाँ से डिस्टिंक्ट वैल्यू ढूँढनी हैं। मेरे मामले में, इसका नाम CountUnique() है, तो जब भी मैं इस फ़ंक्शन को किसी निर्दिष्ट रेंज के साथ कॉल करता हूँ, यह मुझे काउंट वैल्यू देता है:

=CountUnique(A2:A8)

अंतिम विचार

मैंने यूनिक और डिस्टिंक्ट वैल्यू गिनने के लिए प्रमुख फ़ॉर्मूले, PivotTables, और VBA मैक्रोज़ कवर किए हैं। हर तरीके के अपने फायदे हैं, लेकिन आपको वही चुनना चाहिए जो आपकी ज़रूरतों और आपके Excel संस्करण के अनुकूल हो।

यदि आप अपनी Excel स्किल्स निखारना चाहते हैं, तो हमारा Excel Fundamentals स्किल ट्रैक और Data Preparation in Excel कोर्स देखें।