एक्सेल सूत्र: या तर्क के साथ पंक्तियों की गणना -

सारांश

OR तर्क के साथ पंक्तियों को गिनने के लिए, आप SUMPRODUCT फ़ंक्शन के आधार पर एक सूत्र का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, G6 में सूत्र है:

=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))

जहाँ समूह (B5: B15), color1 (C5: C15), और color2 (D5: D15) को श्रेणी कहा जाता है।

स्पष्टीकरण

एक्सेल में मुश्किल समस्याओं में से एक "या तर्क" के साथ डेटा के एक सेट में पंक्तियों को गिनना है। दो बुनियादी परिदृश्य हैं: (1) आप उन पंक्तियों को गिनना चाहते हैं जहाँ एक कॉलम में मान "x" या "y" है (2) आप उन पंक्तियों को गिनना चाहते हैं जहाँ एक मान, "x", एक कॉलम में मौजूद है या किसी अन्य में है ।

इस उदाहरण में, लक्ष्य उन पंक्तियों को गिनना है जहाँ समूह = "a" और Color1 या Color2 "लाल" हैं। इसका मतलब है कि हम ऊपर के परिदृश्य 2 के साथ काम कर रहे हैं।

COUNTIFS के साथ

आप पहले COUNTIFS फ़ंक्शन के लिए पहुंच सकते हैं, जो कि कई मानदंडों को मूल रूप से संभालता है। हालाँकि, COUNTIFS फ़ंक्शन AND तर्क के साथ स्थितियों में शामिल होता है, इसलिए सभी मानदंडों को गिनती में शामिल करने के लिए TRUE होना चाहिए:

=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1

जब तक हम COUNTIFS के कई उदाहरणों का उपयोग नहीं करते हैं, तब तक यह COINTIFS को अयोग्य बनाता है:

=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")

अनुवाद: उन पंक्तियों को गिनें जहाँ समूह "a" है और color1 "लाल" + गिनती पंक्तियाँ हैं जहाँ समूह "a" है और color2 "लाल" है - पंक्तियों को गिनें जहाँ समूह "a" है और color1 "red" और color2 है " लाल ”(दोहरी गिनती से बचने के लिए)।

यह काम करता है, लेकिन आप देख सकते हैं कि यह कुछ जटिल और निरर्थक सूत्र है।

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

बूलियन तर्क का उपयोग करना और SUMPRODUCT फ़ंक्शन के साथ परिणाम को संसाधित करने के लिए एक बेहतर समाधान है। (यदि आपको बूलियन बीजगणित पर एक प्राइमर की आवश्यकता है, तो यह वीडियो एक परिचय प्रदान करता है।) दिखाए गए उदाहरण में, G6 में सूत्र है:

=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))

जहाँ समूह (B5: B15), color1 (C5: C15), और color2 (D5: D15) को श्रेणी कहा जाता है।

समस्या का पहला भाग समूह = "ए" के लिए परीक्षण करना है जो हम इस तरह करते हैं:

(group="a")

क्योंकि सीमा B5: B15 में 11 सेल हैं, यह अभिव्यक्ति 11 TRUE और FALSE मानों की एक सरणी देता है:

(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)

प्रत्येक TRUE एक पंक्ति का प्रतिनिधित्व करता है जहाँ समूह "A" है।

अगला, हमें कॉलम 1 या कॉलम 2 में "लाल" मान की जांच करने की आवश्यकता है। हम इसके अलावा दो अभिव्यक्तियों के साथ करते हैं (+), चूंकि बूलियन बीजगणित में OR तर्क के साथ मेल खाता है:

(color1="red")+(color2="red")

एक्सेल स्वचालित रूप से किसी भी गणित ऑपरेशन के दौरान 1s और 0s के रूप में TRUE और FALSE मूल्यों का मूल्यांकन करता है, इसलिए उपरोक्त अभिव्यक्ति से परिणाम इस तरह एक सरणी है:

(2;0;0;1;1;0;1;0;0;0;1)

सरणी में पहली संख्या 2 है, क्योंकि Color1 और Color2 दोनों पहली पंक्ति में "लाल" हैं। नीचे दिए गए कारणों के लिए, हमें शून्य से अधिक के मूल्यों की जाँच करके इस स्थिति से बचाव करना होगा:

((2;0;0;1;1;0;1;0;0;0;1))>0

अब हमारे पास फिर से TRUE और FALSE मूल्यों की एक सरणी है:

(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)

नीचे दी गई तालिका संक्षेप में बताती है कि कैसे एक्सेल ऊपर वर्णित रंग तर्क का मूल्यांकन करता है:

इस बिंदु पर, हमारे पास परीक्षण समूह = "ए" से एक सरणी में परिणाम हैं:

(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)

और Color1 या Color2 में "लाल" परीक्षण से परिणाम दूसरे सरणी में:

(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)

अगला कदम "एंड लॉजिक" के साथ इन दो सरणियों को एक साथ लाना है। ऐसा करने के लिए, हम गुणा (*) का उपयोग करते हैं, क्योंकि गुणन बूलियन बीजगणित में AND तर्क से मेल खाता है।

दो सरणियों को एक साथ गुणा करने के बाद, हमारे पास 1s और 0s का एक ही सरणी है, जिसे सीधे SUMPRODUCT फ़ंक्शन में वितरित किया जाता है:

=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))

SUMPRODUCT फ़ंक्शन अंतिम परिणाम के रूप में संख्या, 2 का योग लौटाता है। यह उन पंक्तियों की गिनती है जहाँ समूह = "a" और Color1 या Color2 "लाल" हैं।

दोहरी गिनती से बचने के लिए

हम उन पंक्तियों को गिनना नहीं चाहते हैं जहाँ Color1 और Color2 दोनों "लाल" हैं। यही कारण है कि हम नीचे दिए गए कोड में शून्य से अधिक मूल्यों के लिए (color1 = "red") + (color2 = "red") से परिणामों की जांच करते हैं:

((color1="red")+(color2="red"))>0

इस जाँच के बिना, डेटा में पहली पंक्ति से 2 अंतिम सरणी में दिखाई देगा, और अंतिम गणना के रूप में 3 को गलत तरीके से वापस करने के लिए सूत्र का कारण होगा।

फिल्टर का विकल्प

बूलियन तर्क के बारे में एक अच्छी बात यह है कि यह एक्सेल के नवीनतम कार्यों के साथ पूरी तरह से काम करता है, जैसे एक्सएलबुक और फिल्टर। उदाहरण के लिए, FILTER फ़ंक्शन मिलान पंक्तियों को निकालने के लिए ऊपर बताए गए एक ही तर्क का उपयोग कर सकता है:

=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))

फ़िल्टर से परिणाम दो पंक्तियाँ हैं जो मानदंडों को पूरा करती हैं, जैसा कि नीचे देखा गया है:

यदि आप इन नए कार्यों के बारे में अधिक जानना चाहते हैं, तो हमारे पास एक अवलोकन और वीडियो प्रशिक्षण है।