एक्सेल सूत्र: मानदंड के साथ अद्वितीय मानों की गणना करें -

सामान्य सूत्र

=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))

सारांश

एक या अधिक शर्तों के साथ अद्वितीय मानों को गिनने के लिए, आप UNIQUE और FILTER के आधार पर एक सूत्र का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, H7 में सूत्र है:

=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))

जो 3 रिटर्न करता है, क्योंकि ओमेगा परियोजना से जुड़े बी 6: बी 15 में तीन अद्वितीय नाम हैं।

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

स्पष्टीकरण

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

अंदर से बाहर की ओर काम करते हुए, फिल्टर फ़ंक्शन का उपयोग मापदंड लागू करने और केवल "ओमेगा" परियोजना से जुड़े नामों को निकालने के लिए किया जाता है:

FILTER(B6:B15,C6:C15=H6) // Omega names only

FILTER का परिणाम इस प्रकार है:

("Jim";"Jim";"Carl";"Sue";"Carl")

अगला, डुप्लिकेट को निकालने के लिए UNIQUE फ़ंक्शन का उपयोग किया जाता है:

UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))

जो इस तरह एक नई सरणी में परिणाम:

("Jim";"Carl";"Sue") // after UNIQUE

इस बिंदु पर, हमारे पास ओमेगा से जुड़े नामों की एक अनूठी सूची है, और हमें उन्हें गिनने की जरूरत है। नीचे दिए गए कारणों के लिए, हम LEN फ़ंक्शन और SUM फ़ंक्शन के साथ ऐसा करते हैं। चीजों को स्पष्ट करने के लिए, हम पहले अद्वितीय सूची को शामिल करने के सूत्र को फिर से लिखेंगे:

=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))

LEN फ़ंक्शन को सूची में प्रत्येक आइटम की लंबाई मिलती है, और लंबाई की एक सरणी देता है:

LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)

अगला, हम जाँचते हैं कि लंबाई शून्य से अधिक है:

LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)

और 1s और 0s के लिए TRUE और FALSE मूल्यों के साथ तालमेल करने के लिए एक दोहरे नकारात्मक का उपयोग करें:

--((TRUE;TRUE;TRUE)) // returns (1;1;1)

अंत में, हम SUM फ़ंक्शन के साथ परिणाम जोड़ते हैं:

=SUM((1;1;1)) // returns 3

इस सरणी को सीधे COUNTA फ़ंक्शन पर वितरित किया जाता है, जो एक अंतिम गणना देता है:

=COUNTA(("Jim";"Carl";"Sue")) // returns 3

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

कई मानदंडों के साथ अद्वितीय गणना करें

कई मानदंडों के आधार पर अद्वितीय मानों को गिनने के लिए, FILTER के अंदर "शामिल करें" तर्क का विस्तार कर सकते हैं। उदाहरण के लिए, ओमेगा परियोजना के लिए केवल जून में अद्वितीय नाम गिनाने के लिए, उपयोग करें:

=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))

यह एक से अधिक स्थितियों को लागू करने के लिए बूलियन तर्क का उपयोग करने का एक उदाहरण है। दृष्टिकोण को यहां और अधिक विस्तार से समझाया गया है।

अधिक जानकारी के लिए, यह प्रशिक्षण वीडियो देखें: कई मानदंडों के साथ फ़िल्टर कैसे करें।

COUNTA

एक सरल सूत्र लिखना संभव है जो COUNTA फ़ंक्शन पर उत्तर देता है। हालाँकि एक महत्वपूर्ण चेतावनी यह है कि COUNTA 1 तब लौटेगा जब कोई मेल खाने वाले मान नहीं होंगे। इसका कारण यह है कि जब कोई डेटा मापदंड से मेल नहीं खाता है, तो FILTER फ़ंक्शन एक त्रुटि देता है, और यह त्रुटि COUNTA फ़ंक्शन द्वारा गिना जा रहा है। मूल COUNTA सूत्र इस तरह दिखता है:

=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))

फिर, यह फॉर्मूला 1 तब लौटेगा जब कोई मिलान डेटा नहीं होगा। इसमें रिक्त कोशिकाएं भी शामिल होंगी जो मानदंडों को पूरा करती हैं। LEN और SUM पर आधारित सूत्र एक बेहतर विकल्प है।

कोई गतिशील सरणियाँ नहीं

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