एक्सेल सूत्र: दो-तरफ़ा अनुमानित मैच कई मापदंड -

सारांश

कई मानदंडों के साथ दो-तरफ़ा अनुमानित मैच लुकअप करने के लिए, आप मानदंड लागू करने के लिए IF फ़ंक्शन की मदद से INDEX और MATCH पर आधारित एक सरणी सूत्र का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, K8 में सूत्र है:

=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))

जहां डेटा (D6: H16), व्यास (D5: H5), सामग्री (B6: B16), और कठोरता (C6: C16) को केवल सुविधा के लिए उपयोग की जाने वाली श्रेणियों का नाम दिया गया है।

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

स्पष्टीकरण

लक्ष्य सामग्री, कठोरता और ड्रिल बिट व्यास के आधार पर फ़ीड दर की खोज करना है। फ़ीड दर मान नामित श्रेणी डेटा (D6: H16) में हैं।

यह दो तरह से INDEX और MATCH फॉर्मूला के साथ किया जा सकता है। एक MATCH फ़ंक्शन पंक्ति संख्या (सामग्री और कठोरता) को पूरा करता है, और दूसरा MATCH फ़ंक्शन कॉलम नंबर (व्यास) को खोजता है। INDEX फ़ंक्शन अंतिम परिणाम देता है।

दिखाए गए उदाहरण में, K8 में सूत्र है:

=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column

(केवल पठनीयता के लिए लाइन ब्रेक को जोड़ा गया)।

मुश्किल बिट यह है कि सामग्री और कठोरता को एक साथ संभालने की आवश्यकता है। हमें MATCH को दिए गए मटीरियल के लिए कठोरता मान (दिखाए गए उदाहरण में कम कार्बन स्टील) तक सीमित रखने की आवश्यकता है।

हम IF फ़ंक्शन के साथ ऐसा कर सकते हैं। अनिवार्य रूप से, हम मैच देखने से पहले अप्रासंगिक मूल्यों को "दूर फेंक" करने के लिए IF का उपयोग करते हैं।

विवरण

INDEX फ़ंक्शन को सरणी के लिए नामित रेंज डेटा (D6: H16) दिया गया है। पहला MATCH फ़ंक्शन पंक्ति संख्या को पूरा करता है:

MATCH(K6,IF(material=K5,hardness),1) // get row num

सही पंक्ति का पता लगाने के लिए, हमें सामग्री पर एक सटीक मिलान और कठोरता पर एक अनुमानित मिलान करने की आवश्यकता है। हम पहले अप्रासंगिक कठोरता को फ़िल्टर करने के लिए IF फ़ंक्शन का उपयोग करके ऐसा करते हैं:

IF(material=K5,hardness) // filter

हम सामग्री के सभी मूल्यों (बी 6: बी 16) का परीक्षण करते हैं, यह देखने के लिए कि क्या वे के 5 ("लो कार्बन स्टील") में मूल्य से मेल खाते हैं। यदि हां, तो कठोरता मूल्य को पारित किया जाता है। यदि नहीं, तो IF FALSE देता है। परिणाम इस तरह एक सरणी है:

(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)

ध्यान दें कि एकमात्र जीवित मूल्य निम्न कार्बन स्टील से जुड़े हैं। अन्य मूल्य अब FALSE हैं। यह सरणी लुकअप_अरे के रूप में सीधे MATCH फ़ंक्शन पर वापस आ जाती है।

मैच के लिए लुकअप वैल्यू K6 से आता है, जिसमें दी गई कठोरता है, 176. MATCH को मैच के लिए सेट करके अनुमानित मैच के लिए कॉन्फ़िगर किया गया है। टाइप 1 से इन सेटिंग्स के साथ, MATCH FALSE मूल्यों की अनदेखी करता है और एक सटीक मिलान या अगले सबसे छोटे मान की स्थिति को लौटाता है। ।

नोट: कठोरता मूल्यों को प्रत्येक सामग्री के लिए आरोही क्रम में क्रमबद्ध किया जाना चाहिए।

176 के रूप में दिए गए कठोरता के साथ, MATCH 6, प्रति पंक्ति नंबर के रूप में सीधे INDEX को दिया गया। अब हम इस तरह से मूल सूत्र को फिर से लिख सकते हैं:

=INDEX(data,6,MATCH(K7,diameter,1))

दूसरा MATCH फॉर्मूला व्यास पर एक अनुमानित मिलान करके सही कॉलम संख्या का पता लगाता है:

MATCH(K7,diameter,1) // get column num

नोट: व्यास D5 में मान : H5 को आरोही क्रम में क्रमबद्ध किया जाना चाहिए।

लुकअप मान K7 (0.75) से आता है, और लुकअप_अरे नाम सीमा व्यास (D5: H5) है।

पहले की तरह, MATCH को match_type से 1 पर सेट करके अनुमानित मैच के लिए सेट किया गया है।

0.75 के रूप में दिए गए व्यास के साथ, MATCH 3 रिटर्न, कॉलम नंबर के रूप में सीधे INDEX फ़ंक्शन को दिया गया। मूल सूत्र अब हल होता है:

=INDEX(data,6,3) // returns 0.015

INDEX 0.015 का अंतिम परिणाम देता है, F11 से मान।