सामान्य सूत्र
=OFFSET(origin,0,0,COUNTA(range),COUNTA(range))
सारांश
सूत्र के साथ एक डायनामिक नाम की श्रेणी बनाने का एक तरीका यह है कि OFFSET फ़ंक्शन का उपयोग COUNTA फ़ंक्शन के साथ किया जाए। डायनामिक रेंज को विस्तार रेंज के रूप में भी जाना जाता है - वे नए या हटाए गए डेटा को समायोजित करने के लिए स्वचालित रूप से विस्तार और अनुबंध करते हैं।
नोट: OFFSET एक अस्थिर फ़ंक्शन है, जिसका अर्थ है कि यह एक वर्कशीट में हर परिवर्तन के साथ पुनर्गणना करता है। एक आधुनिक मशीन और छोटे डेटा सेट के साथ, यह एक समस्या का कारण नहीं होना चाहिए, लेकिन आप बड़े डेटा सेट पर धीमी कार्यक्षमता देख सकते हैं। उस स्थिति में, इसके बजाय INDEX फ़ंक्शन के साथ एक डायनामिक नामित श्रेणी बनाने पर विचार करें।
दिखाए गए उदाहरण में, गतिशील रेंज के लिए उपयोग किया जाने वाला सूत्र है:
=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))
स्पष्टीकरण
यह फ़ॉर्मूला एक ऐसी श्रेणी उत्पन्न करने के लिए OFFSET फ़ंक्शन का उपयोग करता है जो गैर-रिक्त कक्षों की संख्या के आधार पर ऊँचाई और चौड़ाई को समायोजित करके फैलता है।
OFFSET में पहला तर्क डेटा (मूल) में पहली सेल का प्रतिनिधित्व करता है, जो इस मामले में सेल B5 है। अगले दो तर्क पंक्तियों और स्तंभों के लिए ऑफसेट हैं, और शून्य के रूप में आपूर्ति की जाती है।
अंतिम दो तर्क ऊंचाई और चौड़ाई का प्रतिनिधित्व करते हैं। COUNTA का उपयोग करके मक्खी पर ऊँचाई और चौड़ाई उत्पन्न की जाती है, जिससे परिणामी संदर्भ गतिशील हो जाता है।
ऊंचाई के लिए, हम B5: B100 में गैर-रिक्त मानों की गणना करने के लिए COUNTA फ़ंक्शन का उपयोग करते हैं। यह डेटा में कोई रिक्त मान नहीं लेता है, और B100 से परे कोई मान नहीं है। COUNTA 6 लौटाता है।
चौड़ाई के लिए, हम B5: Z5 में गैर-रिक्त मानों की गणना करने के लिए COUNTA फ़ंक्शन का उपयोग करते हैं। यह कोई हेडर सेल नहीं मानता है, और Z5 से आगे कोई हेडर नहीं है। COUNTA 6 लौटाता है।
इस बिंदु पर, सूत्र इस तरह दिखता है:
=OFFSET(B5,0,0,6,6)
इस जानकारी के साथ, OFFSET B5: G10 का संदर्भ देता है, जो कि 6 स्तंभों की ऊंचाई 6 पंक्तियों की सीमा से मेल खाती है।
नोट: ऊंचाई और चौड़ाई के लिए उपयोग की जाने वाली श्रेणियों को वर्कशीट लेआउट से मेल खाने के लिए समायोजित किया जाना चाहिए।
पूर्ण स्तंभ / पंक्ति संदर्भों के साथ भिन्नता
आप ऊंचाई और चौड़ाई के लिए पूर्ण स्तंभ और पंक्ति संदर्भ का उपयोग भी कर सकते हैं:
=OFFSET($B$5,0,0,COUNTA($B:$B)-2,COUNTA($4:$4))
ध्यान दें कि कोशिकाओं बी 4 और बी 2 में हेडर और शीर्षक मूल्यों को ध्यान में रखने के लिए ऊंचाई को -2 के साथ समायोजित किया जा रहा है। इस दृष्टिकोण का लाभ COUNTA के अंदर की सीमाओं की सादगी है। नुकसान विशाल आकार के पूर्ण स्तंभों और पंक्तियों से आता है - सीमा के बाहर गलत मूल्यों को रोकने के लिए देखभाल की जानी चाहिए, क्योंकि वे आसानी से गिनती को फेंक सकते हैं।
अंतिम पंक्ति का निर्धारण
कार्यपत्रक में डेटा की संरचना और सामग्री के आधार पर, डेटा के एक सेट में अंतिम पंक्ति (अंतिम सापेक्ष स्थिति) निर्धारित करने के कई तरीके हैं:
- रिक्त डेटा के साथ मिश्रित डेटा में अंतिम पंक्ति
- मिश्रित डेटा में अंतिम पंक्ति बिना किसी रिक्त स्थान के
- पाठ डेटा में अंतिम पंक्ति
- संख्यात्मक डेटा में अंतिम पंक्ति
