ब्लॉग

Excel में डायनैमिक रेंज: OFFSET और टेबल कब इस्तेमाल करें

नई पंक्तियों के साथ कुल और ड्रॉपडाउन विकल्प अपडेट रखें। OFFSET और COUNTA का इस्तेमाल, खाली ID का असर और Excel टेबल बनाने का तरीका समझें।

पंक्ति जोड़ने पर हाइलाइट की हुई रेंज नीचे की ओर बढ़ती है।

Excel की उत्पाद सूची में एक आइटम जोड़ने के बाद भी कुल और ड्रॉपडाउन जस के तस रह सकते हैं। रेफ़रेंस अभी भी A2:A10 है तो 11वीं पंक्ति बाहर है। डायनैमिक रेंज से Excel सूची की सीमा तय करता है और हर बार फ़ॉर्मूला बदलना नहीं पड़ता।

OFFSET से ऊँचाई और चौड़ाई गिनें

लगातार डेटा वाली सूची के हेडर और उत्पाद ID गिनकर OFFSET को ऊँचाई और चौड़ाई दें। नमूना Sheet1 पर बनाएँ और कुल का फ़ॉर्मूला दूसरी शीट में डालें। बाद के स्ट्रक्चर्ड रेफ़रेंस के लिए नीचे दिए जापानी हेडर ज्यों के त्यों रखें।

सेल कॉलम A कॉलम B कॉलम C
पंक्ति 1 商品ID (उत्पाद ID) 商品名 (उत्पाद का नाम) 数量 (मात्रा)
पंक्ति 2 A01 पेन 4
पंक्ति 3 A02 नोटबुक 6
=SUM(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$1000),COUNTA(Sheet1!$A$1:$Z$1)))

A1 से बिना पंक्ति या कॉलम खिसकाए शुरू करें। A में डेटा की गिनती ऊँचाई और पंक्ति 1 में हेडर की गिनती चौड़ाई तय करती है। नतीजे में तीन पंक्तियों और तीन कॉलम की A1:C3 रेंज और कुल 10 मिलता है। पंक्ति 4 पर A03, स्केल और 2 जोड़ने पर A1:C4 और कुल 12 मिलता है। उत्पाद ID टेक्स्ट होने चाहिए और गिनी जाने वाली रेंज में दूसरी जानकारी नहीं होनी चाहिए। Microsoft का OFFSET संदर्भ

अब सिर्फ़ A3 की ID मिटाएँ। A में तीन सेल गिने जाएँगे: हेडर A1 और ID वाले A2 और A4। इसलिए OFFSET ऊपर से पहली तीन पंक्तियाँ A1:C3 लौटाता है। पंक्ति 3 की मात्रा 6 शामिल है, लेकिन पंक्ति 4 की मात्रा 2 नहीं, इसलिए कुल वापस 10 है।

A3 खाली है। OFFSET की A1:C3 रेंज में पंक्ति 3 की 6 शामिल है और पंक्ति 4 की 2 बाहर है, इसलिए कुल 10 है।
हरा बॉर्डर Windows के Excel में OFFSET से मिली A1:C3 रेंज दिखाता है।

बीच का हेडर खाली हो तो आखिर के कॉलम भी रेंज से बाहर हो सकते हैं। COUNTA उन फ़ॉर्मूला सेल को भी गिनता है जो खाली स्ट्रिंग ("") लौटाते हैं। मौजूदा वर्कबुक में हेडर और ID लगातार रखें और गिनती की सीमा व खाली सेल जाँचें। ID मौजूद हो तो उसकी मात्रा खाली रहने से ऊँचाई नहीं बदलती। Microsoft का COUNTA संदर्भ

हेडर दूसरी पंक्ति में हो तो

पंक्ति 1 में रिपोर्ट का शीर्षक रखने के लिए सूची A2 से शुरू करें। ऊँचाई-चौड़ाई भी पंक्ति 2 से गिनें। उदाहरण एक पंक्ति नीचे जाने पर हेडर और दो उत्पादों की रेंज A2:C4 रहेगी और कुल 10 ही होगा।

=SUM(OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1000),COUNTA(Sheet1!$A$2:$Z$2)))

नई पंक्तियों के साथ बढ़ने वाली टेबल बनाएँ

नई सूची में हेडर समेत रेंज चुनें और Insert → Table पर जाएँ। टेबल का नाम Items रखें। नीचे का फ़ॉर्मूला टेबल के बाहर से मात्रा कॉलम का कुल देता है। नई पंक्तियाँ जोड़ने से रेंज भी बढ़ेगी। Microsoft की स्ट्रक्चर्ड रेफ़रेंस गाइड

=SUM(Items[数量])

सिर्फ़ उत्पाद ID वाली पंक्तियों का कुल लें

A1:C4 को Items टेबल बनाने पर A3 खाली होने के बावजूद ऊपर का फ़ॉर्मूला 4 + 6 + 2 जोड़कर 12 देता है। सिर्फ़ ID वाली पंक्तियों के लिए SUMIF में ID कॉलम खाली न होने की शर्त रखें। यहाँ पंक्ति 2 और 4 का 4 + 2 जोड़कर 6 मिलता है। Microsoft का SUMIF संदर्भ

=SUMIF(Items[商品ID],"<>",Items[数量])

ड्रॉपडाउन विकल्प टेबल से जोड़ें

दूसरी शीट में उत्पाद के नाम दिखाने के लिए डेस्कटॉप Excel में Formulas → Name Manager खोलें। वर्कबुक के दायरे में ItemChoices नाम बनाएँ और Refers To में =Items[商品名] डालें। इनपुट सेल के लिए Data Validation में List चुनें और Source में =ItemChoices डालें। हेडर विकल्प नहीं बनता और टेबल के अंत में नई पंक्ति जोड़ने पर नया नाम जुड़ता है। खाली विकल्पों से बचने के लिए हर नई पंक्ति में नाम भरें। ड्रॉपडाउन सूची बनाने की Microsoft की गाइड