एक्सेल सूत्र: उन कोशिकाओं की गणना करें जिनमें बहुत सारे तार नहीं होते हैं -

विषय - सूची

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

(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))

सारांश

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

(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))

जहाँ "डेटा" नाम की सीमा B5: B14 है, और "बहिष्कृत" नाम की श्रेणी D5: D7 है।

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

प्रस्तावना

यह सूत्र "समाहित" आवश्यकता से जटिल है। यदि आपको केवल उन कोशिकाओं को गिनने के लिए एक सूत्र की आवश्यकता है जो कई चीजों को * बराबर * नहीं करते हैं, तो आप MATR फ़ंक्शन के आधार पर अधिक सरल फार्मूला का उपयोग कर सकते हैं। इसके अलावा, यदि आपके पास सीमित संख्या में तार हैं, तो आप इस तरह COUNTIFS फ़ंक्शन का उपयोग कर सकते हैं:

=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")

हालाँकि, इस दृष्टिकोण के साथ, आपको प्रत्येक स्ट्रिंग को बाहर करने के लिए श्रेणी / मापदंड तर्क की एक नई जोड़ी दर्ज करनी होगी। इसके विपरीत, नीचे दिया गया सूत्र कार्यपत्रक पर सीधे दर्ज किए गए को बाहर करने के लिए बड़ी संख्या में तारों को संभाल सकता है।

अंत में, यह सूत्र जटिल है। मुझे बताएं कि क्या आपके पास प्रपोज़ करने का कोई सरल फॉर्मूला है :)

स्पष्टीकरण

इस सूत्र का मूल ISNUMBER और SEARCH है:

ISNUMBER(SEARCH(TRANSPOSE(exclude),data))

यहां, हम आइटम को नामित श्रेणी "बहिष्कृत" में स्थानांतरित करते हैं, फिर "खोज" के रूप में "पाठ" के रूप में "डेटा" के रूप में खोज को परिणाम फ़ीड करें। खोज समारोह TRUE और FALSE मानों की 2d सरणी, 3 पंक्तियों की 10 पंक्तियों को इस तरह लौटाता है:

(3,#VALUE!,12;#VALUE!,4,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,3;14,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;3,#VALUE!,12)

"डेटा" में प्रत्येक मान के लिए, हमारे पास 3 परिणाम (एक प्रति खोज स्ट्रिंग) हैं जो या तो # त्रुटियों या संख्याओं के हैं। नंबर एक पाया टेक्स्ट स्ट्रिंग की स्थिति का प्रतिनिधित्व करते हैं, और त्रुटि टेक्स्ट स्ट्रिंग्स का प्रतिनिधित्व नहीं करते हैं। वैसे, पूर्ण परिणामों के 10 x 3 सरणी उत्पन्न करने के लिए TRANSPOSE फ़ंक्शन की आवश्यकता होती है।

TRUE FALSE मान प्राप्त करने के लिए इस सरणी को ISNUMBER में खिलाया गया है, जिसे हम 1s और 0s में एक दोहरे नकारात्मक (-) ऑपरेटर के साथ परिवर्तित करते हैं। परिणाम इस तरह एक सरणी है:

(1,0,1;0,1,0;0,0,0;0,0,0;0,0,1;1,0,0;0,0,0;0,0,0;0,0,0;1,0,1)

जो array1 के रूप में MMULT फ़ंक्शन में जाता है। मैट्रिक्स गुणन के नियमों के बाद, array1 में स्तंभों की संख्या array2 में पंक्तियों की संख्या के बराबर होनी चाहिए। Array2 उत्पन्न करने के लिए , हम इस तरह ROW फ़ंक्शन का उपयोग करते हैं:

ROW(exclude)^0

यह 1 कॉलम द्वारा 1s, 3 पंक्तियों की एक सरणी देता है:

(1;1;1)

जो के रूप में MMULT में चला जाता है array2 । सरणी गुणा के बाद, हमारे पास मूल डेटा से मिलान करने के लिए एक सरणी है:

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

इस सरणी में, कोई भी गैर-शून्य संख्या एक मान का प्रतिनिधित्व करती है जहां कम से कम एक तार को मिला है। शून्य इंगित करते हैं कि कोई बहिष्कृत तार नहीं मिला। सभी गैर-शून्य मानों को 1 के लिए बाध्य करने के लिए, हम शून्य से अधिक का उपयोग करते हैं:

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

जो अभी तक एक और सरणी या TRUE और FALSE मान बनाता है:

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

हमारा अंतिम लक्ष्य केवल पाठ मूल्यों की गणना करना है जहां कोई बहिष्कृत तार नहीं पाए गए थे, इसलिए हमें इन मूल्यों को उलटने की आवश्यकता है। हम इसे 1 से सरणी घटाकर करते हैं। यह बूलियन तर्क का एक उदाहरण है। गणित का संचालन स्वचालित रूप से 1s और 0s के लिए TRUE और FALSE मूल्यों को जोड़ता है, और अंत में हमारे पास SUM फ़ंक्शन पर लौटने के लिए एक सरणी है: