एक्सेल सूत्र: सेल कई चीजों में से एक के बराबर है -

विषय - सूची

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

=SUMPRODUCT(--(A1=things))>0

सारांश

यदि आप यह देखने के लिए सेल का परीक्षण करना चाहते हैं कि क्या यह कई चीजों में से एक के बराबर है, तो आप SUMPRODUCT फ़ंक्शन का उपयोग करने वाले सूत्र के साथ ऐसा कर सकते हैं।

प्रसंग

मान लीजिए कि आपके पास B5: B11 रेंज में टेक्स्ट स्ट्रिंग्स की एक सूची है, और आप प्रत्येक सेल को E5: E9 श्रेणी की चीजों की एक और सूची के विरुद्ध परीक्षण करना चाहते हैं। दूसरे शब्दों में, बी 5: बी 11 में प्रत्येक सेल के लिए, आप जानना चाहते हैं: क्या यह सेल ई 5: 9 में किसी भी चीज के बराबर है?

आप नेस्टेड IF स्टेटमेंट्स के आधार पर एक बड़ा फॉर्मूला बनाना शुरू कर सकते हैं, लेकिन SUMPRODUCT पर आधारित एक अरै फॉर्मूला एक सरल, क्लीनर दृष्टिकोण है।

उपाय

इसका समाधान एक सूत्र बनाना है जो कई मूल्यों के लिए परीक्षण करेगा और TRUE / FALSE मूल्यों की सूची लौटाएगा। एक बार हमारे पास, हम उस सूची (एक सरणी, वास्तव में) को SUMPRODUCT के साथ संसाधित कर सकते हैं।

सूत्र हम उपयोग कर रहे हैं इस तरह दिखता है:

=SUMPRODUCT(--(B5=things))>0

स्पष्टीकरण

कुंजी यह स्निपेट है:

--(B5=things)

जो कि नामांकित श्रेणी "चीजों" में प्रत्येक मूल्य के लिए बी 5 में मूल्य की तुलना करता है। क्योंकि हम B5 की एक सरणी (अर्थात नामांकित श्रेणी "चीजों", E5: E11) से तुलना कर रहे हैं, परिणाम इस प्रकार TRUE / FALSE मानों का एक सरणी होगा:

(सही; FALSE; FALSE; FALSE; FALSE)

यदि हमारे पास सरणी में एक भी TRUE है, तो हम जानते हैं कि B5 सूची में कम से कम एक चीज़ के बराबर है, इसलिए, TRUE / FALSE मूल्यों को 1s और 0s के लिए बाध्य करने के लिए, हम एक दोहरे नकारात्मक का उपयोग करते हैं (-, जिसे दोहरी भी कहा जाता है एकतरफा)। इस जबरदस्ती के बाद, हमारे पास यह है:

(1; 0; 0; 0; 0)

अब हम SUMPRODUCT के साथ परिणाम की प्रक्रिया करते हैं, जो सरणी में तत्वों को जोड़ देगा। यदि हमें कोई गैर-शून्य परिणाम मिलता है, तो हमारे पास कम से कम एक मैच होता है, इसलिए हम TR1 या FALSE के अंतिम परिणाम को बाध्य करने के लिए> 1 का उपयोग करते हैं।

एक हार्ड-कोडित सूची के साथ

कोई आवश्यकता नहीं है कि आप अपनी चीजों की सूची के लिए एक सीमा का उपयोग करें। यदि आप केवल बहुत सी चीजों की तलाश कर रहे हैं, तो आप सरणी प्रारूप में एक सूची का उपयोग कर सकते हैं, जिसे एक सरणी स्थिरांक कहा जाता है। उदाहरण के लिए, यदि आप रंगों को लाल, नीला और हरा देख रहे हैं, तो आप इस तरह से ("लाल", "नीला", "हरा") का उपयोग कर सकते हैं:

--(B5=("red","blue","green"))

अतिरिक्त स्थानों से निपटना

यदि आपके द्वारा परीक्षण किए जा रहे सेल में कोई अतिरिक्त स्थान है, तो वे ठीक से मेल नहीं खाएंगे। सभी अतिरिक्त स्थान को हटाने के लिए, आप TRIM फ़ंक्शन का उपयोग करने के लिए सूत्र को संशोधित कर सकते हैं जैसे: