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

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

=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0

सारांश

किसी सेल को यह देखने के लिए परीक्षण करने के लिए कि उसमें कई तार हैं, आप SEARCH, ISNUMBER और SUMPRODUCT फ़ंक्शंस के आधार पर एक सूत्र का उपयोग कर सकते हैं। C5 में सूत्र, नीचे कॉपी किया गया है:

=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0

जहाँ चीजें नामित श्रेणी E5: E9 हैं।

स्पष्टीकरण

हम बी 5: बी 11 में प्रत्येक सेल का परीक्षण करना चाहते हैं, यह देखने के लिए कि इसमें नामित रेंज चीजों (ई 5: ई 9) में कोई तार शामिल है या नहीं । C5 में जिस सूत्र का हम उपयोग कर रहे हैं, वह नीचे है:

=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0

यह सूत्र एक सूत्र पर आधारित है (यहां बताया गया है) जो किसी एकल सबस्ट्रिंग के लिए एक सेल की जांच करता है। यदि कक्ष में सबस्ट्रिंग है, तो सूत्र TRUE लौटाता है। यदि नहीं, तो सूत्र FALSE लौटाता है:

ISNUMBER(SEARCH(things,B5))

हालाँकि, इस मामले में, हम खोज को तार की एक सूची देते हैं। चूंकि चीजों में 5 तार होते हैं , SEARCH इस तरह से एक सरणी में 5 परिणाम देता है:

(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)

जब खोज एक स्ट्रिंग पाता है, यह उस स्ट्रिंग की स्थिति लौटाता है। यदि खोज में स्ट्रिंग नहीं मिलती है, तो यह एक # रिटर्न देता है! त्रुटि। क्योंकि "पीला" बी 5 में पहले शब्द के रूप में दिखाई देता है, हम एक 1. देखते हैं क्योंकि अन्य तार नहीं मिले हैं, अन्य 4 आइटम त्रुटियां हैं।

यह सरणी सीधे ISNUMBER फ़ंक्शन पर लौटा दी गई है। ISNUMBER फिर TRUE / FALSE मानों की एक सरणी लौटाता है:

(TRUE;FALSE;FALSE;FALSE;FALSE)

यदि हमारे पास सरणी में एक भी TRUE है, तो हम जानते हैं कि एक सेल में कम से कम एक तार होता है जिसे हम खोज रहे हैं। TRUE की जांच करने का सबसे आसान तरीका सभी मूल्यों को एक साथ जोड़ना है। हम SUMPRODUCT के साथ ऐसा कर सकते हैं, लेकिन पहले हमें TRUE / FALSE मानों को 1s और 0s को एक दोहरे नकारात्मक (-) के साथ इस तरह लागू करने की आवश्यकता है:

--ISNUMBER(SEARCH(things,B5))

यह केवल 1s और 0s युक्त एक नई सरणी देता है:

(1;0;0;0;0)

सीधे SUMPRODUCT को दिया:

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

प्रक्रिया के लिए सिर्फ एक सरणी के साथ, SUMPRODUCT सरणी में आइटम जोड़ता है और एक परिणाम देता है। किसी भी गैर-शून्य परिणाम का मतलब है कि हमारे पास "हिट" है, इसलिए हम TRUE या FALSE के अंतिम परिणाम को लागू करने के लिए> 0 जोड़ते हैं:

=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE

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

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

=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0

झूठे मेल को रोकना

इस दृष्टिकोण के साथ एक समस्या यह है कि आप लंबे समय तक शब्दों के अंदर दिखाई देने वाले सबस्ट्रिंग से झूठे मेल प्राप्त कर सकते हैं। उदाहरण के लिए, यदि आप "ड्र" से मेल खाने की कोशिश करते हैं, तो आप "एंड्रिया", "ड्रिंक", "ड्राई" आदि को भी ढूंढ सकते हैं, क्योंकि "ड्र" इन शब्दों के अंदर दिखाई देता है। ऐसा इसलिए होता है क्योंकि SEARCH स्वचालित रूप से "समाहित" मैच करता है।

एक त्वरित हैक के लिए, आप दूसरे शब्दों में "ड्र" को पकड़ने से बचने के लिए खोज शब्द (यानी "डॉ", या "डॉ") के आसपास स्थान जोड़ सकते हैं। लेकिन यह विफल हो जाएगा यदि "ड्र" सेल में पहले या अंतिम रूप से प्रकट होता है, या विराम चिह्न के साथ दिखाई देता है।

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