चतुर सरणी सूत्र: किसी पंक्ति के अंतिम गैर-रिक्त कक्ष में स्तंभ शीर्षक का संदर्भ लें

क्या आप किसी पंक्ति के अंतिम गैर-रिक्त सेल में कॉलम शीर्षक का संदर्भ बनाने की चतुर चाल जानते हैं? सबसे अच्छा: आपको किसी सहायक लाइन या कॉलम की आवश्यकता नहीं है। इट्स दैट ईजी:

चार कार्यों को मिलाएं IFERROR (), INDEX (), MAX () और IF ()

बिक्री में प्रबंध निदेशक आपको उन उत्पादों के लिए प्रति माह संपन्न अनुबंधों की एक सूची भेजेंगे जिन्हें चरणबद्ध तरीके से समाप्त किया जा रहा है। आपको प्रत्येक उत्पाद के लिए बिक्री के अंतिम महीने को निर्दिष्ट करने के लिए कॉलम N में एक सूत्र का उपयोग करना चाहिए - बिना किसी सहायक लाइन या कॉलम के। यदि कोई और अनुबंध समाप्त नहीं हुआ है, तो N कॉलम में एक खाली सेल दर्ज करें।

यह उदाहरण, पहली नज़र में केले, सहायक लाइनों या स्तंभों के उपयोग के बिना दरार करने के लिए एक कठिन अखरोट निकला। जैसा अभ्यास के लिए एक्सेल-पाठकों ने अखरोट तोड़ दिया! समस्या समाधान के लिए हमारे पास सेल में निम्नलिखित सरणी सूत्र है: एन 2 बनाया

{= IFERROR (इंडेक्स ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; COLUMN (B2: M2) -1; -1))); "")}

एक नज़र डालें कि यह चरण दर चरण कैसे काम करता है

सरणी सूत्र में निर्मित IF () स्थिति से प्रारंभ करें: {= इफरर (इंडेक्स ($ बी $ 1: $ एम $ 1; मैक्स (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}

IF () स्थिति सरणी सूत्र में एक काल्पनिक सहायक रेखा बनाती है और कक्षों के लिए जाँच करती है बी२ जब तक एम2ये खाली हैं या नहीं। यदि कोई सेल खाली है, तो मान -1 को अन्यथा COLUMN () फ़ंक्शन के माध्यम से वापस कर दिया जाता है, संबंधित कॉलम संख्या से मान 1 घटा दिया जाता है।
सूत्र में 1 का घटाव आवश्यक है क्योंकि तालिका के पहले कॉलम में महीने का नाम नहीं, बल्कि उत्पाद का नाम होता है। नीचे आप सीखेंगे कि संबंधित महीने के नाम को प्रदर्शित करने के लिए INDEX () फ़ंक्शन का उपयोग कैसे करें, जो - यदि आपने 1 घटाया नहीं है - उपयोग किए गए अतिरिक्त कॉलम के कारण ए। ठीक एक कॉलम से गलत होगा।

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

अगले चरण में आपने MAX () फ़ंक्शन के साथ सबसे बड़ा मान पढ़ा, जिसमें IF () स्थिति नेस्टेड है। यह पंक्ति ३ में १२ का मान है (कॉलम १३ माइनस १; चित्र में सहायक पंक्ति देखें):

{= इफरर (इंडेक्स ($ बी $ 1: $ एम $ 1;MAX (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}

आप इस MAX मान को INDEX () फ़ंक्शन में पास करते हैं। संबंधित कैलेंडर माह को तब पंक्ति 1 में पढ़ा जाता है। INDEX () फ़ंक्शन का डेटा क्षेत्र क्षेत्र है $ बी $ 1: $ एम $ 1. पारित MAX मान - उदाहरण 12 में - का अर्थ है कि सूची में बारहवां मान, अर्थात। दिसम्बर दिसंबर के महीने के लिए:

{= इफरर(इंडेक्स ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; COLUMN (B2: M2) -1; -1)));"")}

यदि तालिका की माह श्रेणी में सभी कक्ष खाली हैं, तो सबसे बड़ा मान -1 है (शुरुआत में IF () स्थिति देखें)। यदि मान -1 को INDEX () फ़ंक्शन में पास किया जाता है, तो यह अनिवार्य रूप से एक त्रुटि मान की ओर ले जाता है, क्योंकि INDEX () फ़ंक्शन के सूची क्षेत्र में केवल बारह प्रविष्टियाँ होती हैं और इसलिए प्रविष्टि -1 नहीं मिल सकती है। आप इस त्रुटि मान को IFERROR () फ़ंक्शन के साथ पकड़ते हैं और इसके बजाय एक खाली स्ट्रिंग लौटाते हैं। उदाहरण में, लाइन 4 में उत्पाद C के लिए यह मामला है, क्योंकि इस उत्पाद के लिए कोई और अनुबंध समाप्त नहीं किया जा सकता है:

{=इफ़रोर(इंडेक्स ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}

चूंकि यह एक सरणी सूत्र है, इसलिए कुंजी संयोजन Ctrl + Shift + Enter के साथ सूत्र की प्रविष्टि को पूरा करें।