एक्सेल डायनामिक नेम्ड रेंज एक्सेल की उन विशेषताओं में से है जिसके बारे में ज्यादा एक्सेल उपयोगकर्ता नहीं जानते। यह लेख डायनामिक नेम्ड रेंज को आपके करीब लाएगा। आगे की पंक्तियों में, एक्सेल डायनामिक नेम्ड रेंज से संबंधित संकेत और ट्रिक्स दिखाए जाएंगे।
एक्सेल के OFFSET फ़ंक्शन के साथ डायनामिक नेम्ड रेंज
डायनामिक रेंज बनाने के लिए, OFFSET फ़ंक्शन का उपयोग किया जा सकता है।
एक्सेल का OFFSET फ़ंक्शन
यहां एक्सेल के OFFSET फ़ंक्शन का सिंटैक्स है:
OFFSET (संदर्भ, पंक्तियाँ, कॉलम, [ऊंचाई], [चौड़ाई])
फ़ंक्शन में आर्गुमेंट का अर्थ:
- संदर्भ – वह संदर्भ जिससे आप ऑफसेट को आधार बनाना चाहते हैं।
- पंक्तियाँ – संदर्भ सेल से ऊपर या नीचे पंक्तियों की संख्या।
- कॉलम – संदर्भ सेल से दाएं या बाएं कॉलम की संख्या।
- ऊंचाई, चौड़ाई – संदर्भित सेल से चयन की ऊंचाई और चौड़ाई को परिभाषित करें।
OFFSET फ़ंक्शन के साथ डायनामिक नेम्ड रेंज
नेम मैनेजर संवाद बॉक्स में, इसका उपयोग डायनामिक नेम्ड रेंज को परिभाषित करने के लिए किया जा सकता है।
यहां बताया गया है कि आप यह कैसे कर सकते हैं:
फ़ॉर्मूला टैब पर जाएं, परिभाषित नाम कमांड समूह में, नाम परिभाषित करें कमांड पर क्लिक करें, और आपको नया नाम संवाद बॉक्स मिलना चाहिए:
एक डायनामिक नेम्ड रेंज बनाई जाएगी जो A1 से एक सेल नीचे शुरू होने वाली सेल की रेंज का चयन करती है, उसी कॉलम में, और नीचे दी गई छवि के उदाहरण से K1 और K2 सेल में दी गई कॉलम और पंक्तियों की संख्या का चयन करती है:
"संदर्भित करता है" फ़ील्ड में फ़ॉर्मूला इस तरह दिखता है:
=OFFSET(Sheet1!$A$1;1;0;Sheet1!$K$1;Sheet1!$K$2)
OFFSET फ़ंक्शन में सभी तत्वों को डायनामिक बनाया जा सकता है। उदाहरण के लिए, मैं किसी विशिष्ट दिन के लिए कई तारीखों का चयन करना चाहूंगा।
यहां कुछ आउटपुट हैं:
=OFFSET (Sheet1!$A$1; 1; 0; 6; 2) = यह रेंज A2: B7 को संदर्भित करेगा
वह फ़ॉर्मूला जो K1, K2, K3, और K4 में प्रविष्टियों पर निर्भर करेगा, इस तरह दिखना चाहिए:
=OFFSET(Sheet1!$A$1;Sheet1!$K$3;Sheet1!$K$4;Sheet1!$K$1;Sheet1!$K$2)
एक्सेल में किसी अन्य फ़ंक्शन की तरह, OFFSET को अन्य फ़ंक्शन के साथ भी जोड़ा जा सकता है जो परिणाम के रूप में संख्या प्रदान करते हैं। मान लें कि मैं सोमवार को संदर्भ के रूप में लेकर, अंतिम सप्ताह को छोड़कर सभी मंगलवार की तारीखों का चयन करना चाहता हूं। उसी उदाहरण के आधार पर, फ़ॉर्मूला इस तरह दिखना चाहिए:
=OFFSET(Sheet1!$A$1;1;1;COUNT(Sheet1!$B:$B);1)
और पढ़ें: सेल मान के आधार पर एक्सेल डायनामिक नेम्ड रेंज (5 आसान तरीके)
समान पठन
- सेल मान के आधार पर एक्सेल डायनामिक रेंज
- एक्सेल VBA: सेल मान के आधार पर डायनामिक रेंज (3 विधियाँ)
- VBA के साथ एक्सेल में अंतिम पंक्ति के लिए डायनामिक रेंज का उपयोग कैसे करें (3 विधियाँ)
INDEX फ़ंक्शन के साथ डायनामिक नेम्ड रेंज
अन्य फ़ंक्शन का उपयोग भी डायनामिक नेम्ड रेंज के लिए किया जा सकता है, जैसे INDEX - उदाहरण के लिए, आप सभी "सोमवार" का चयन करना चाहेंगे, चाहे उनकी संख्या कितनी भी हो:
=Sheet1!$A$2:INDEX(Sheet1!$A:$A;COUNTA(Sheet1!$A:$A))
यदि आप संदर्भ को भी डायनामिक बनाना चाहते हैं, तो आप फ़ॉर्मूला के संयोजन का उपयोग कर सकते हैं। मान लें कि आप एक सेल में दिन का नाम दर्ज करना चाहते हैं, और फिर तालिका में उस दिन से संबंधित सभी तारीखों का चयन करें।
=OFFSET(INDIRECT(ADDRESS(2;MATCH(Sheet1!$K$6;Sheet1!$1:$1;0)));0;0;COUNT(Sheet1!$A:$A);1)
और पढ़ें: एक्सेल OFFSET डायनामिक रेंज मल्टीपल कॉलम प्रभावी तरीके से
VBA के साथ डायनामिक नेम्ड रेंज
अपने रोजमर्रा के काम में, पेशेवर उन स्थितियों का सामना करते हैं जहां उन्हें बार-बार एक ही ऑपरेशन करने की आवश्यकता होती है। डायनामिक नेम्ड रेंज बनाते समय भी यही बात हो सकती है। दोहराए जाने वाले कार्यों से बचने के लिए, VBA का उपयोग इसके लिए भी किया जा सकता है।
VBA फ़ॉर्मूला की तरह ही काम करता है, बस आपको नेम्ड रेंज जोड़ने के लिए विशेषता सिंटैक्स का पालन करना होगा।
सिंटैक्स इस तरह है:
ActiveWorkbook.names.Add Name:=”NAME”, RefersTo =”RANGE THAT YOU WANT TO SELECT”
उदाहरण के लिए, पिछले उदाहरणों में से एक को VBA में आसानी से लागू किया जा सकता है। एक पूर्ण मैक्रो इस तरह दिखेगा:
Sub naming()
ActiveWorkbook.names.Add Name:=”NAME9″, RefersTo:=”=OFFSET(Sheet1!$A$1,0,1,counta(A:A),2)”
End sub
और पढ़ें: VBA के साथ एक्सेल में डायनामिक नेम्ड रेंज बनाएं (चरण-दर-चरण दिशानिर्देश)
खाली सेल के साथ डायनामिक नेम्ड रेंज
आप अक्सर समस्याओं का सामना कर सकते हैं जब आपके पास सभी सेल भरे नहीं होते हैं, या दूसरे शब्दों में, कुछ सेल खाली होते हैं। उपयोग किए गए फ़ॉर्मूला की परिभाषाओं को जानना सबसे महत्वपूर्ण है जो संदर्भ ढूंढने के लिए उपयोग किया जाता है। उदाहरण के लिए, COUNTA एक फ़ंक्शन है जो एक रेंज में सभी गैर-खाली! सेल को गिनता है। यह इस बात पर निर्भर करता है कि आप खाली को शामिल करना चाहते हैं या नहीं।
नीचे दी गई छवि खाली सेल वाली स्थिति दिखाती है:
यदि आप सभी "सोमवार" का चयन करना चाहते हैं, तो पिछले उदाहरणों की तरह फ़ॉर्मूला का उपयोग करने से हर सोमवार का चयन होगा लेकिन अंतिम वाला नहीं, क्योंकि एक खाली सेल है - A5। इसका समाधान एक अलग फ़ॉर्मूला का उपयोग करना होगा - फ़ॉर्मूला का संयोजन:
=OFFSET(Sheet1!$A$1;1;0;SUMPRODUCT(MAX((Sheet1!$A:$A<>””)*ROW(Sheet1!$A:$A)))-1;1)
यह A2 से A7 तक के सभी सेल का चयन करेगा।
पिछली पंक्तियों और उदाहरणों से आपको अपना काम तेजी से और अधिक कुशलता से करने में मदद मिलनी चाहिए। डायनामिक नेम्ड रेंज खोजने के लिए कई विकल्पों के साथ एक विशाल क्षेत्र का प्रतिनिधित्व करते हैं और आपके कौशल और रचनात्मकता के आधार पर एक उपयोगी उपकरण हो सकते हैं। यह दिखाया गया है कि कई संयोजन हैं, और यह आप पर निर्भर है कि आप उनका आवेदन ढूंढें।
और पढ़ें: सेल मान के आधार पर एक्सेल में डायनामिक सम रेंज बनाएं (4 तरीके)
कार्य फ़ाइल डाउनलोड करें
नीचे दिए गए लिंक से कार्य फ़ाइल डाउनलोड करें:
संबंधित लेख
- VBA में एक्सेल में डायनामिक रेंज का उपयोग कैसे करें (11 तरीके)
- एक्सेल टेबल डायनामिक रेंज के साथ डेटा सत्यापन ड्रॉप डाउन सूची
- एक्सेल में डायनामिक चार्ट रेंज बनाएं (2 विधियाँ)
- एक्सेल में संख्याओं की रेंज कैसे बनाएं (3 आसान तरीके)
