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

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

=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)

सारांश

यदि आप यह देखने के लिए सेल का परीक्षण करना चाहते हैं कि क्या इसमें किसी सूची में सभी आइटम हैं, तो आप ISNUMBER, SUMPRODUCT और COUNTA फ़ंक्शन की सहायता से SEARCH फ़ंक्शन का उपयोग करने वाले सूत्र के साथ ऐसा कर सकते हैं।

प्रसंग

मान लें कि आपके पास B5: B8 श्रेणी के टेक्स्ट स्ट्रिंग्स की एक सूची है, और आप यह पता लगाना चाहते हैं कि क्या इन कक्षों में अन्य श्रेणी के सभी शब्द E5: E7 हैं।

यदि आप प्रत्येक आइटम की जाँच करने के लिए नेस्टेड IF कथन का उपयोग करता है, तो आप एक सूत्र का निर्माण कर सकते हैं, लेकिन अगर आपके पास देखने के लिए बहुत सी चीजें हैं, तो यह बहुत अच्छा नहीं होगा। हर बार जब आप एक शब्द जोड़ते हैं तो आपको एक और नेस्टेड आईएफ जोड़ना होगा और कोष्ठक को समायोजित करना होगा।

उपाय

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

दिखाए गए उदाहरण में, हम जिस सूत्र का उपयोग कर रहे हैं वह है:

=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)

स्पष्टीकरण

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

ISNUMBER(SEARCH(things,B5)

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

हालाँकि, अगर हम उसी सूत्र को चीजों की एक सूची देते हैं (इस मामले में, हम एक नामित श्रेणी का उपयोग कर रहे हैं जिसे "चीजें", E5: E7) कहा जाता है, तो यह हमें TRUE / FALSE मूल्यों की एक सूची वापस देगा, जिसमें प्रत्येक आइटम के लिए एक । परिणाम एक सरणी है जो इस तरह दिखता है:

(सही; TRUE; TRUE)

जहां प्रत्येक TRUE एक पाया आइटम का प्रतिनिधित्व करता है, और प्रत्येक FALSE एक आइटम का प्रतिनिधित्व करता है जो नहीं मिला।

हम TRUE / FALSE मूल्यों को 1s और 0s को एक दोहरे नकारात्मक के साथ बाध्य कर सकते हैं (-, जिसे दोहरी अनुपयोगी भी कहा जाता है):

--ISNUMBER(SEARCH(things,B5))

जो इस तरह एक सरणी देता है:

(1; 1; 1)

अगला, हम इस सरणी को SUMPRODUCT के साथ संसाधित करते हैं, जो हमें कुल योग देगा। यदि यह राशि नामित श्रेणी "चीजों" में मदों की संख्या के बराबर है, तो हम जानते हैं कि हमने सभी चीजें ढूंढ ली हैं और TRUE वापस कर सकते हैं। जिस तरह से हम करते हैं वह दो संख्याओं की सीधे तुलना करना है। हमें COUNTA का उपयोग करते हुए "चीजों" में गैर-रिक्त कोशिकाओं की गिनती मिलती है:

=COUNTA(things)

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

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

=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)