फॉर्मूला चुनौती - कई या मानदंड - पहेली

विषय - सूची

एक समस्या जो एक्सेल में बहुत ऊपर आती है वह कई या स्थितियों के आधार पर गिनती या योग है। उदाहरण के लिए, शायद आपको डेटा का विश्लेषण करने और सिएटल या डेनवर में ऑर्डर की गणना करने की आवश्यकता है, लाल, नीले या हरे रंग की वस्तुओं के लिए? यह आश्चर्यजनक रूप से मुश्किल हो सकता है, इसलिए स्वाभाविक रूप से यह एक अच्छी चुनौती है!

चुनौती

नीचे दिया गया डेटा आदेशों, प्रति पंक्ति एक आदेश का प्रतिनिधित्व करता है। तीन अलग-अलग चुनौतियां हैं।

F9, G9 और H9 में कौन-से सूत्र निम्न स्थितियों के साथ आदेशों की सही गणना करेंगे:

F9 - टीशर्ट या हूडी
जी 9 - (टीशर्ट या हुडी) और (लाल, नीला या हरा)
एच 9 - (टीशर्ट या हुडी) और (रेड, ब्लू या ग्रीन) और (डेनवर या सिएटल)

ग्रीन शेडिंग को सशर्त स्वरूपण के साथ लागू किया जाता है और प्रत्येक कॉलम में OR मानदंड के प्रत्येक सेट के लिए मिलान मूल्यों को इंगित करता है।

आपकी सुविधा के लिए, निम्नलिखित नामित श्रेणियां उपलब्ध हैं:

आइटम = बी 3: बी 16
रंग = सी 3: सी 16
शहर = डी 3: डी 16

वर्कशीट संलग्न है। अपने जवाब नीचे टिप्पणी के रूप में दें!

उत्तर (विस्तार के लिए क्लिक करें)

मेरा समाधान इस तरह ISNUMBER और MATCH के साथ SUMPRODUCT का उपयोग करता है:

=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )

जहां आदेशों की गिनती होगी …

आइटम है (टीशर्ट या हूडी) और
रंग है (लाल, नीला, या हरा) और
शहर है (डेनवर या सिएटल)

कई लोगों ने भी यही तरीका सुझाया। मुझे यह संरचना पसंद है क्योंकि यह अधिक मानदंडों को संभालने के लिए आसानी से तराजू करता है, और सेल संदर्भ (हार्ड-कोडित मूल्यों के बजाय) के साथ भी काम करता है। सेल संदर्भ के साथ, H9 में सूत्र है:

=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )

इस सूत्र की कुंजी ISNUMBER + MATCH निर्माण है। MATCH सेटअप "बैकवर्ड" है - लुकअप मान डेटा से आते हैं, और मापदंड सरणी के लिए उपयोग किए जाते हैं। हर बार MATCH का उपयोग करने पर परिणाम एक एकल स्तंभ सरणी है। इस सरणी में # N / A त्रुटियाँ (कोई मिलान नहीं) या संख्याएँ (मिलान) हैं, इसलिए ISNUMBER का उपयोग बूलियन मान TRUE और FALSE में परिवर्तित करने के लिए किया जाता है। एक साथ सरणियों को गुणा करने का संचालन TRUE FALSE मानों को 1s और 0s तक ले जाता है, और SUMPRODUCT के अंदर अंतिम सरणी में 1s होते हैं जहाँ पंक्तियाँ मानदंड पूरा करती हैं। SUMPRODUCT तब सरणी को सम्‍मिलित करता है और परिणाम देता है।