एक्सेल फार्मूला: हेल्पर कॉलम के साथ सभी मैचों को निकालें -

विषय - सूची

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

=IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),"")

सारांश

एक्सेल में कई मैचों को निकालने का एक तरीका है INDEX और MATCH का उपयोग एक सहायक कॉलम के साथ किया जाता है जो डेटा से मेल खाता है। यह अधिक उन्नत सरणी सूत्र की जटिलता से बचा जाता है। दिखाए गए उदाहरण में, H6 में सूत्र है:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

जहाँ ct (G3), डेटा (B3: E52), और हेल्पर (E3: E52) को श्रेणी कहा जाता है।

स्पष्टीकरण

लुकअप फ़ार्मुलों के साथ चुनौती जो एक से अधिक मैचों को पुनः प्राप्त करती है, डुप्लिकेट (यानी कई मैच) का प्रबंधन कर रही है। VLOOKUP और INDEX + MATCH जैसे लुकअप फॉर्मूले आसानी से पहला मैच ढूंढ सकते हैं, लेकिन जब सभी मैच एक से अधिक मिलते हैं तो "सभी मैचों" को देखना बहुत मुश्किल होता है।

यह सूत्र इस चुनौती से निपटने के लिए एक सहायक स्तंभ का उपयोग करता है जो एक संख्यात्मक मान लौटाता है जिसका उपयोग कई मैचों को आसानी से निकालने के लिए किया जा सकता है। सहायक स्तंभ का सूत्र इस तरह दिखता है:

=SUM(E2,AND(C3=$I$3,D3=$J$3))

सहायक कॉलम डेटा में प्रत्येक पंक्ति को यह देखने के लिए परीक्षण करता है कि क्या स्तंभ C में विभाग I3 में मान से मेल खाता है और स्तंभ D में भवन J3 में मान से मेल खाता है। दोनों तार्किक परीक्षणों के लिए और TRUE को वापस करने के लिए TRUE वापस करना होगा।

प्रत्येक पंक्ति के लिए, AND और फ़ंक्शन से परिणाम गणना उत्पन्न करने के लिए सहायक कॉलम में "ऊपर मूल्य" में जोड़ा जाता है। इस सूत्र का व्यावहारिक प्रभाव एक वृद्धिशील काउंटर है जो केवल तभी बदलता है जब (नया) मैच मिलता है। तब तक मान वैसा ही रहता है जब तक कि अगला मैच नहीं मिलता। यह काम करता है क्योंकि TRUE / FALSE परिणाम द्वारा वापस आते हैं और योग ऑपरेशन के हिस्से के रूप में 1/0 मूल्यों के लिए बाध्य होते हैं। FALSE परिणाम कुछ नहीं जोड़ते हैं, और TRUE परिणाम 1 जोड़ते हैं।

निष्कर्षण क्षेत्र में वापस, स्तंभ H में नाम के लिए लुकअप सूत्र इस तरह दिखता है:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

अंदर से बाहर की ओर काम करते हुए, सूत्र के INDEX + MATCH भाग को पहले मैच के लिए मिला हुआ नाम दिखता है, कॉलम G में पंक्ति संख्या का उपयोग मैच वैल्यू के रूप में किया जाता है:

INDEX(data,MATCH($G6,helper,0),1)

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

यह वह जगह है जहां सूत्र की चतुराई स्पष्ट हो जाती है। सहायक कॉलम में स्पष्ट रूप से डुप्लिकेट होते हैं, लेकिन यह कोई फर्क नहीं पड़ता, क्योंकि MATCH केवल पहले मूल्य से मेल खाएगा। डिज़ाइन के अनुसार, प्रत्येक "पहला मान" डेटा तालिका में सही पंक्ति से मेल खाता है।

कॉलम I और J के सूत्र स्तंभ संख्या को छोड़कर H के समान हैं, जो प्रत्येक मामले में एक-एक करके बढ़ाया जाता है।

IFEX / MATCH सूत्र को लपेटने वाला IF कथन एक साधारण कार्य करता है - यह निष्कर्षण क्षेत्र में प्रत्येक पंक्ति संख्या को यह देखने के लिए जाँचता है कि क्या पंक्ति संख्या G3 में मान के बराबर या उससे कम है (जिसका नाम रेंज "ct") है, जो सभी मिलान रिकॉर्ड की कुल संख्या। यदि ऐसा है, तो INDEX / MATCH तर्क चलाया जाता है। यदि नहीं, तो IF खाली स्ट्रिंग ("") को आउटपुट करता है।

G3 में सूत्र (नामित श्रेणी "ct") सरल है:

=MAX(helper)

चूंकि हेल्पर कॉलम में अधिकतम मूल्य कुल मिलान संख्या के समान है, मैक्स फ़ंक्शन को हम सभी की आवश्यकता है।

ध्यान दें: निष्कर्षण क्षेत्र को मैन्युअल रूप से आवश्यक डेटा (यानी 5 पंक्तियों, 10 पंक्तियों, 20 पंक्तियों, आदि) को संभालने के लिए कॉन्फ़िगर करना होगा। इस उदाहरण में, यह केवल वर्कशीट को कॉम्पैक्ट रखने के लिए 5 पंक्तियों तक सीमित है।

मैंने इस तकनीक को माइक गिरविन की पुस्तक कंट्रोल + शिफ्ट + एंटर में सीखा।