फ्लेक्सिबल ड्रॉपडाउन के लिए एक्सेल में डायनामिक रेंज नेम्स का उपयोग करें

एक्सेल स्प्रेडशीट में अक्सर डेटा प्रविष्टि को सरल और/या मानकीकृत करने के लिए सेल ड्रॉपडाउन शामिल होते हैं। ये ड्रॉपडाउन स्वीकार्य प्रविष्टियों की सूची निर्दिष्ट करने के लिए डेटा सत्यापन सुविधा का उपयोग करके बनाए गए हैं।

एक साधारण ड्रॉपडाउन सूची सेट करने के लिए, उस सेल का चयन करें जहां डेटा दर्ज किया जाएगा, फिर डेटा सत्यापन पर क्लिक करें। (डेटा . पर) टैब), डेटा सत्यापन चुनें, सूची चुनें (अनुमति के तहत :), और फिर स्रोत में सूची आइटम (अल्पविराम से अलग) दर्ज करें :फ़ील्ड (चित्र 1 देखें)।

इस प्रकार के मूल ड्रॉपडाउन में, स्वीकार्य प्रविष्टियों की सूची डेटा सत्यापन के भीतर ही निर्दिष्ट की जाती है; इसलिए, सूची में परिवर्तन करने के लिए, उपयोगकर्ता को डेटा सत्यापन को खोलना और संपादित करना होगा। हालांकि, अनुभवहीन उपयोगकर्ताओं के लिए या ऐसे मामलों में जहां विकल्पों की सूची लंबी है, यह मुश्किल हो सकता है।

एक अन्य विकल्प यह है कि सूची को स्प्रैडशीट के भीतर एक नामित श्रेणी में रखा जाए, और फिर स्रोत में उस श्रेणी का नाम (एक समान चिह्न के साथ पहले) निर्दिष्ट किया जाए :डेटा सत्यापन का क्षेत्र (जैसा चित्र 2 में दिखाया गया है)।

यह दूसरी विधि सूची में विकल्पों को संपादित करना आसान बनाती है, लेकिन आइटम जोड़ना या हटाना समस्याग्रस्त हो सकता है। चूंकि नामित श्रेणी (FruitChoices, हमारे उदाहरण में) कोशिकाओं की एक निश्चित श्रेणी ($H$3:$H$10 जैसा कि दिखाया गया है) को संदर्भित करता है, यदि H11 या उससे नीचे के कक्षों में अधिक विकल्प जोड़े जाते हैं, तो वे ड्रॉपडाउन में दिखाई नहीं देंगे (चूंकि वे सेल FruitChoices श्रेणी का हिस्सा नहीं हैं)।

इसी तरह, उदाहरण के लिए, यदि नाशपाती और स्ट्रॉबेरी प्रविष्टियां मिटा दी जाती हैं, तो वे अब ड्रॉपडाउन में दिखाई नहीं देंगी, बल्कि ड्रॉपडाउन में दो "खाली" विकल्प शामिल होंगे क्योंकि ड्रॉपडाउन अभी भी संपूर्ण FruitChoices श्रेणी का संदर्भ देता है, जिसमें खाली कक्ष H9 और एच10.

इन कारणों से, ड्रॉपडाउन के लिए सूची स्रोत के रूप में एक सामान्य नामित श्रेणी का उपयोग करते समय, यदि प्रविष्टियों को सूची से जोड़ा या हटाया जाता है, तो अधिक या कम कक्षों को शामिल करने के लिए नामित श्रेणी को ही संपादित किया जाना चाहिए।

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

Excel में डायनामिक रेंज कैसे सेटअप करें

एक सामान्य (स्थिर) श्रेणी का नाम कोशिकाओं की एक निर्दिष्ट श्रेणी को संदर्भित करता है ($H$3:$H$10 हमारे उदाहरण में, नीचे देखें):

लेकिन एक गतिशील श्रेणी को एक सूत्र का उपयोग करके परिभाषित किया जाता है (नीचे देखें, एक अलग स्प्रैडशीट से लिया गया है जो गतिशील श्रेणी नामों का उपयोग करता है):

शुरू करने से पहले, सुनिश्चित करें कि आपने हमारी एक्सेल उदाहरण फ़ाइल डाउनलोड कर ली है (सॉर्ट मैक्रोज़ अक्षम कर दिए गए हैं)।

आइए इस सूत्र की विस्तार से जाँच करें। फलों के विकल्प सीधे शीर्षक के नीचे कोशिकाओं के एक ब्लॉक में होते हैं (फल ) उस शीर्षक को एक नाम भी दिया गया है:FruitsHeading :

फलों की पसंद के लिए गतिशील रेंज को परिभाषित करने के लिए उपयोग किया जाने वाला संपूर्ण सूत्र है:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)

फलों का शीर्षक उस शीर्षक को संदर्भित करता है जो सूची में पहली प्रविष्टि के ऊपर एक पंक्ति है। संख्या 20 (सूत्र में दो बार प्रयुक्त) सूची के लिए अधिकतम आकार (पंक्तियों की संख्या) है (इसे इच्छानुसार समायोजित किया जा सकता है)।

ध्यान दें कि इस उदाहरण में, सूची में केवल 8 प्रविष्टियाँ हैं, लेकिन इनके नीचे रिक्त कक्ष भी हैं जहाँ अतिरिक्त प्रविष्टियाँ जोड़ी जा सकती हैं। संख्या 20 पूरे ब्लॉक को संदर्भित करता है जहां प्रविष्टियां की जा सकती हैं, न कि प्रविष्टियों की वास्तविक संख्या के लिए।

आइए अब सूत्र को टुकड़ों में तोड़ दें (प्रत्येक टुकड़े को रंग-कोडिंग), यह समझने के लिए कि यह कैसे काम करता है:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)

"अंतरतम" टुकड़ा है OFFSET(FruitsHeading,1,0,20,1) . यह 20 कोशिकाओं (FruitsHeading सेल के नीचे) के ब्लॉक को संदर्भित करता है जहां विकल्प दर्ज किए जा सकते हैं। यह ऑफ़सेट फ़ंक्शन मूल रूप से कहता है:FruitsHeading . से प्रारंभ करें सेल में, 1 पंक्ति और 0 से अधिक कॉलम नीचे जाएं, फिर एक ऐसा क्षेत्र चुनें जो 20 पंक्तियाँ लंबा और 1 कॉलम चौड़ा हो। तो यह हमें 20-पंक्ति ब्लॉक देता है जहां फल विकल्प दर्ज किए जाते हैं।

सूत्र का अगला भाग ISBLANK . है समारोह:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)

यहां, OFFSET फ़ंक्शन (ऊपर बताया गया है) को "उपरोक्त" (चीजों को पढ़ने में आसान बनाने के लिए) से बदल दिया गया है। लेकिन ISBLANK फ़ंक्शन सेल की 20-पंक्ति श्रेणी पर काम कर रहा है जिसे OFFSET फ़ंक्शन परिभाषित करता है।

ISBLANK तब 20 TRUE और FALSE मानों का एक सेट बनाता है, जो दर्शाता है कि OFFSET फ़ंक्शन द्वारा संदर्भित 20-पंक्ति श्रेणी में प्रत्येक व्यक्तिगत सेल खाली (खाली) है या नहीं। इस उदाहरण में, सेट में पहले 8 मान FALSE होंगे क्योंकि पहले 8 सेल खाली नहीं हैं और अंतिम 12 मान TRUE होंगे।

सूत्र का अगला भाग INDEX फ़ंक्शन है:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)

फिर से, "उपरोक्त" ऊपर वर्णित ISBLANK और OFFSET कार्यों को संदर्भित करता है। INDEX फ़ंक्शन ISBLANK फ़ंक्शन द्वारा बनाए गए 20 TRUE / FALSE मानों वाली एक सरणी देता है।

इंडेक्स आमतौर पर एक निश्चित पंक्ति और कॉलम (उस ब्लॉक के भीतर) को निर्दिष्ट करके डेटा के एक ब्लॉक से एक निश्चित मूल्य (या मूल्यों की श्रेणी) को चुनने के लिए उपयोग किया जाता है। लेकिन पंक्ति और स्तंभ इनपुट को शून्य पर सेट करना (जैसा कि यहां किया गया है) INDEX को डेटा के पूरे ब्लॉक वाले सरणी को वापस करने का कारण बनता है।

सूत्र का अगला भाग MATCH फ़ंक्शन है:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)

मैच फ़ंक्शन INDEX फ़ंक्शन द्वारा लौटाए गए सरणी के भीतर, पहले TRUE मान की स्थिति देता है। चूंकि सूची में पहली 8 प्रविष्टियां खाली नहीं हैं, इसलिए सरणी में पहले 8 मान FALSE होंगे, और नौवां मान TRUE होगा (9 ^th के बाद से) श्रेणी में पंक्ति खाली है)।

तो MATCH फ़ंक्शन 9 . का मान लौटाएगा . इस मामले में, हालांकि, हम वास्तव में जानना चाहते हैं कि सूची में कितनी प्रविष्टियां हैं, इसलिए सूत्र MATCH मान से 1 घटाता है (जो अंतिम प्रविष्टि की स्थिति देता है)। तो अंततः, MATCH(TRUE,उपरोक्त,0)-1 8 . का मान लौटाता है ।

सूत्र का अगला भाग IFERROR फ़ंक्शन है:

=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)

IFERROR फ़ंक्शन एक वैकल्पिक मान लौटाता है, यदि निर्दिष्ट पहला मान त्रुटि में परिणाम देता है। इस फ़ंक्शन को शामिल किया गया है, यदि कोशिकाओं का पूरा ब्लॉक (सभी 20 पंक्तियाँ) प्रविष्टियों से भरा है, तो MATCH फ़ंक्शन एक त्रुटि लौटाएगा।

ऐसा इसलिए है क्योंकि हम MATCH फ़ंक्शन को पहले TRUE मान (ISBLANK फ़ंक्शन से मानों की सरणी में) देखने के लिए कह रहे हैं, लेकिन यदि कोई भी सेल खाली नहीं है, तो संपूर्ण सरणी FALSE मानों से भर जाएगी। यदि MATCH को खोजी जा रही सरणी में लक्ष्य मान (TRUE) नहीं मिल रहा है, तो यह एक त्रुटि देता है।

इसलिए, यदि पूरी सूची भरी हुई है (और इसलिए, MATCH एक त्रुटि देता है), IFERROR फ़ंक्शन इसके बजाय 20 का मान लौटाएगा (यह जानते हुए कि सूची में 20 प्रविष्टियां होनी चाहिए)।

अंत में, OFFSET(FruitsHeading,1,0,उपरोक्त,1) वह श्रेणी लौटाता है जिसकी हम वास्तव में तलाश कर रहे हैं:फ्रूटहेडिंग सेल से शुरू करें, 1 पंक्ति और 0 से अधिक कॉलम नीचे जाएं, फिर उस क्षेत्र का चयन करें जो सूची में प्रविष्टियां हैं (और 1 कॉलम चौड़ा)। तो पूरा सूत्र एक साथ उस श्रेणी को वापस कर देगा जिसमें केवल वास्तविक प्रविष्टियां हैं (पहले खाली सेल के नीचे)।

ड्रॉपडाउन के लिए स्रोत की सीमा को परिभाषित करने के लिए इस सूत्र का उपयोग करने का मतलब है कि आप सूची को स्वतंत्र रूप से संपादित कर सकते हैं (प्रविष्टियों को जोड़ना या हटाना, जब तक कि शेष प्रविष्टियां शीर्ष सेल पर शुरू होती हैं और सन्निहित होती हैं) और ड्रॉपडाउन हमेशा वर्तमान को प्रतिबिंबित करेगा सूची (चित्र 6 देखें)।

यहां उपयोग की गई उदाहरण फ़ाइल (डायनामिक सूचियां) शामिल है और इस वेबसाइट से डाउनलोड करने योग्य है। हालाँकि, मैक्रोज़ काम नहीं करते हैं, क्योंकि वर्डप्रेस को मैक्रोज़ वाली एक्सेल किताबें पसंद नहीं हैं।

सूची ब्लॉक में पंक्तियों की संख्या निर्दिष्ट करने के विकल्प के रूप में, सूची ब्लॉक को अपना स्वयं का श्रेणी नाम सौंपा जा सकता है, जिसे बाद में संशोधित सूत्र में उपयोग किया जा सकता है। उदाहरण फ़ाइल में, दूसरी सूची (नाम) इस पद्धति का उपयोग करती है। यहां, संपूर्ण सूची ब्लॉक ("NAMES" शीर्षक के नीचे, उदाहरण फ़ाइल में 40 पंक्तियाँ) को NameBlock का श्रेणी नाम दिया गया है . तब NamesList को परिभाषित करने का वैकल्पिक सूत्र है:

=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)

जहां NamesBlock OFFSET(FruitsHeading,1,0,20,1) और ROWS(NamesBlock) की जगह लेता है पहले के फॉर्मूले में 20 (पंक्तियों की संख्या) को बदल देता है।

तो, ड्रॉपडाउन सूचियों के लिए जिन्हें आसानी से संपादित किया जा सकता है (अन्य उपयोगकर्ताओं द्वारा जो अनुभवहीन हो सकते हैं), गतिशील श्रेणी नामों का उपयोग करने का प्रयास करें! और ध्यान दें कि, हालांकि यह आलेख ड्रॉपडाउन सूचियों पर केंद्रित है, गतिशील श्रेणी नामों का उपयोग कहीं भी किया जा सकता है जहां आपको किसी श्रेणी या सूची को संदर्भित करने की आवश्यकता होती है जो आकार में भिन्न हो सकती है। आनंद लें!