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

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

(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))

सारांश

मानदंड के साथ एक श्रेणी में अद्वितीय पाठ मानों की गणना करने के लिए, आप FREQUENCY और MATCH फ़ंक्शन के आधार पर एक सरणी सूत्र का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, G6 में सूत्र है:

(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))

तीन अलग-अलग लोगों ने प्रोजेक्ट ओमेगा पर काम किया है, जो 3 रिटर्न करता है।

नोट: यह एक सरणी सूत्र है और इसे नियंत्रण + शिफ्ट + दर्ज के साथ दर्ज किया जाना चाहिए।

स्पष्टीकरण

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

MATCH(B5:B11,B5:B11,0)

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

(1;1;3;1;1;6;7)

क्योंकि MATCH हमेशा पहले मैच की स्थिति को लौटाता है, जो मान डेटा में एक से अधिक बार दिखाई देते हैं वही स्थिति लौटाते हैं। उदाहरण के लिए, क्योंकि "जिम" सूची में 4 बार दिखाई देता है, वह इस सरणी में नंबर 1 के रूप में 4 बार दिखाता है।

MATCH फ़ंक्शन के बाहर, IF फ़ंक्शन का उपयोग मानदंड लागू करने के लिए किया जाता है, जिसमें इस मामले में परीक्षण शामिल है कि क्या परियोजना "ओमेगा" है (सेल G5 से):

IF(C5:C11=G5 // filter on "omega"

IF फ़ंक्शन फ़िल्टर की तरह कार्य करता है, केवल MATCH से मानों को गुजरने की अनुमति देता है यदि वे "ओमेगा" से जुड़े हैं। परिणाम इस तरह एक सरणी है:

(FALSE;FALSE;FALSE;1;1;6;7) // after filtering

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

ROW(B3:B12)-ROW(B3)+1

यह इस तरह एक सरणी बनाता है:

(1;2;3;4;5;6;7;8;9;10)

जो फिल्टर में bins_array तर्क बन जाता है । इस बिंदु पर, हमारे पास:

FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))

FREQUENCY उन संख्याओं की एक सरणी लौटाता है जो डेटा सरणी में प्रत्येक मान के लिए एक गिनती इंगित करते हैं, जो बिन द्वारा आयोजित किया जाता है। जब एक संख्या पहले से ही गिना गया है, तो FREQUENCY शून्य वापस आ जाएगा। FREQUENCY का परिणाम इस प्रकार है:

(2;0;0;0;0;1;1;0) // result from FREQUENCY

नोट: FREQUENCY हमेशा bins_array की तुलना में एक और आइटम के साथ एक सरणी देता है ।

इस बिंदु पर, हम इस तरह सूत्र को फिर से लिख सकते हैं:

=SUM(--((2;0;0;0;0;1;1;0)>0))

हम शून्य से अधिक के मूल्यों की जांच करते हैं, जो संख्याओं को TRUE या FALSE में परिवर्तित करता है:

=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))

तब हम 1s और 0s के लिए तार्किक मानों को एक-नकारात्मक करने के लिए एक दोहरे-नकारात्मक का उपयोग करते हैं:

=SUM((1;0;0;0;0;1;1;0))

अंत में, एसयूएम फ़ंक्शन 3 अंतिम परिणाम के रूप में देता है।

नोट: यह एक सरणी सूत्र है और इसे Control + Shift + Enter का उपयोग करके दर्ज किया जाना चाहिए।

रेंज में खाली कोशिकाओं को संभालना

यदि श्रेणी की कोई भी कोशिकाएँ रिक्त हैं, तो आपको MATCH फ़ंक्शन में जाने से रिक्त कक्षों को रोकने के लिए सूत्र को समायोजित करने की आवश्यकता होगी, जो एक त्रुटि फेंक देगा। आप रिक्त कोशिकाओं की जांच के लिए एक और नेस्टेड IF फ़ंक्शन जोड़कर ऐसा कर सकते हैं:

(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))

दो मानदंडों के साथ

यदि आपके पास दो मानदंड हैं, तो आप एक और नेस्टेड IF जोड़कर सूत्र के तर्क का विस्तार कर सकते हैं:

(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))

कहाँ c1 = criteria1, c2 = criteria2 और Vals = मूल्यों सीमा होती है।

बूलियन तर्क के साथ

बूलियन तर्क के साथ, आप नेस्टेड IF को कम कर सकते हैं:

(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))

इससे अतिरिक्त मानदंडों को जोड़ना और प्रबंधित करना आसान हो जाता है।

अच्छा लिंक

माइक गिरविन की पुस्तक कंट्रोल-शिफ्ट-एंटर