Excel स्प्रेडशीट में अक्सर डेटा एंट्री को सरल और/या मानकीकृत करने के लिए सेल ड्रॉपडाउन शामिल होते हैं। ये ड्रॉपडाउन डेटा वैलिडेशन सुविधा का उपयोग करके बनाए जाते हैं ताकि स्वीकार्य प्रविष्टियों की सूची निर्दिष्ट की जा सके।
एक साधारण ड्रॉपडाउन सूची सेट अप करने के लिए, उस सेल को चुनें जहां डेटा दर्ज किया जाएगा, फिर Data Validation ( Data टैब पर) पर क्लिक करें, Data Validation चुनें, List चुनें (Allow: के तहत), और फिर Source: फ़ील्ड में सूची आइटम दर्ज करें (कॉमा से अलग) (चित्र 1 देखें)।
इस प्रकार के बेसिक ड्रॉपडाउन में, स्वीकार्य प्रविष्टियों की सूची डेटा वैलिडेशन के भीतर ही निर्दिष्ट होती है; इसलिए, सूची में परिवर्तन करने के लिए, उपयोगकर्ता को डेटा वैलिडेशन खोलना और संपादित करना होगा। यह अनुभवहीन उपयोगकर्ताओं के लिए मुश्किल हो सकता है, या उन मामलों में जहां विकल्पों की सूची लंबी है।
एक अन्य विकल्प सूची को स्प्रेडशीट के भीतर एक नामित रेंज में रखना है, और फिर उस रेंज नाम को (बराबर चिह्न से पहले) डेटा वैलिडेशन के Source: फ़ील्ड में निर्दिष्ट करना है (चित्र 2 में दिखाया गया है)।
यह दूसरी विधि सूची में विकल्पों को संपादित करना आसान बनाती है, लेकिन आइटम जोड़ना या हटाना समस्याग्रस्त हो सकता है। चूंकि नामित रेंज (हमारे उदाहरण में FruitChoices) सेल की एक निश्चित रेंज को संदर्भित करता है ($H$3:$H$10 जैसा कि दिखाया गया है), यदि अधिक विकल्प H11 या नीचे के सेल में जोड़े जाते हैं, तो वे ड्रॉपडाउन में दिखाई नहीं देंगे (चूंकि वे सेल FruitChoices रेंज का हिस्सा नहीं हैं)।
इसी तरह यदि, उदाहरण के लिए, Pears और Strawberries एंट्री मिटा दी जाती हैं, तो वे अब ड्रॉपडाउन में दिखाई नहीं देंगी, लेकिन इसके बजाय ड्रॉपडाउन में दो "खाली" विकल्प शामिल होंगे क्योंकि ड्रॉपडाउन अभी भी पूरे FruitChoices रेंज को संदर्भित करता है, जिसमें खाली सेल H9 और H10 शामिल हैं।
इन कारणों से, जब ड्रॉपडाउन के लिए सूची स्रोत के रूप में सामान्य नामित रेंज का उपयोग किया जाता है, तो नामित रेंज को स्वयं अधिक या कम सेल शामिल करने के लिए संपादित किया जाना चाहिए यदि सूची से प्रविष्टियां जोड़ी या हटाई जाती हैं।
इस समस्या का समाधान ड्रॉपडाउन विकल्पों के स्रोत के रूप में एक डायनामिक रेंज नाम का उपयोग करना है। एक डायनामिक रेंज नाम वह है जो स्वचालित रूप से विस्तार (या संकुचित) होता है ताकि डेटा के एक ब्लॉक के आकार से बिल्कुल मेल खा सके जैसे-जैसे प्रविष्टियां जोड़ी या हटाई जाती हैं। ऐसा करने के लिए, आप एक फॉर्मूला का उपयोग करते हैं, बजाय सेल पते की एक निश्चित रेंज के, नामित रेंज को परिभाषित करने के लिए।
Excel में डायनामिक रेंज कैसे सेट अप करें
एक सामान्य (स्थिर) रेंज नाम सेल की एक निर्दिष्ट रेंज को संदर्भित करता है (हमारे उदाहरण में $H$3:$H$10, नीचे देखें):
लेकिन एक डायनामिक रेंज को एक फॉर्मूला का उपयोग करके परिभाषित किया जाता है (नीचे देखें, एक अलग स्प्रेडशीट से लिया गया जो डायनामिक रेंज नाम का उपयोग करता है):
शुरू करने से पहले, सुनिश्चित करें कि आप हमारी Excel उदाहरण फ़ाइल डाउनलोड करते हैं (सॉर्ट मैक्रो अक्षम कर दिए गए हैं)।
आइए इस फॉर्मूला की विस्तार से जांच करें। Fruits के विकल्प एक हेडिंग (FRUITS) के ठीक नीचे सेल के ब्लॉक में हैं। उस हेडिंग को एक नाम भी सौंपा गया है: FruitsHeading:
Fruits विकल्पों के लिए डायनामिक रेंज को परिभाषित करने के लिए उपयोग किया गया पूरा फॉर्मूला है:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
FruitsHeading उस हेडिंग को संदर्भित करता है जो सूची में पहली प्रविष्टि से एक पंक्ति ऊपर है। संख्या 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 सेल के नीचे) जहां विकल्प दर्ज किए जा सकते हैं। यह OFFSET फंक्शन मूल रूप से कहता है: FruitsHeading सेल से शुरू करें, 1 पंक्ति नीचे जाएं और 0 कॉलम पार करें, फिर एक ऐसे क्षेत्र का चयन करें जो 20 पंक्तियों लंबा और 1 कॉलम चौड़ा हो। तो यह हमें 20-पंक्ति ब्लॉक देता है जहां Fruits विकल्प दर्ज किए जाते हैं।
फॉर्मूला का अगला टुकड़ा ISBLANK फंक्शन है:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)
यहां, ऊपर समझाए गए OFFSET फंक्शन को "the above" से बदल दिया गया है (चीजों को पढ़ने में आसान बनाने के लिए)। लेकिन ISBLANK फंक्शन OFFSET फंक्शन द्वारा परिभाषित 20-पंक्ति रेंज के सेल पर काम कर रहा है।
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)
फिर से, "the above" ऊपर वर्णित ISBLANK और OFFSET फंक्शन को संदर्भित करता है। INDEX फंक्शन ISBLANK फंक्शन द्वारा बनाए गए 20 TRUE/FALSE मानों वाला एक ऐरे लौटाता है।
INDEX का उपयोग सामान्य रूप से एक ब्लॉक के डेटा से एक निश्चित मान (या मानों की रेंज) चुनने के लिए किया जाता है, उस ब्लॉक के भीतर एक निश्चित पंक्ति और कॉलम निर्दिष्ट करके। लेकिन पंक्ति और कॉलम इनपुट को शून्य पर सेट करना (जैसा कि यहां किया गया है) INDEX को डेटा के पूरे ब्लॉक को समाहित करने वाला एक ऐरे लौटाने का कारण बनता है।
फॉर्मूला का अगला टुकड़ा MATCH फंक्शन है:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)
MATCH फंक्शन INDEX फंक्शन द्वारा लौटाए गए ऐरे के भीतर पहले TRUE मान की स्थिति लौटाता है। चूंकि सूची में पहले 8 प्रविष्टियां खाली नहीं हैं, ऐरे में पहले 8 मान FALSE होंगे, और नौवां मान TRUE होगा (चूंकि रेंज में 9वीं पंक्ति खाली है)।
इसलिए MATCH फंक्शन 9 का मान लौटाएगा। इस मामले में, हालांकि, हम वास्तव में जानना चाहते हैं कि सूची में कितनी प्रविष्टियां हैं, इसलिए फॉर्मूला MATCH मान से 1 घटाता है (जो अंतिम प्रविष्टि की स्थिति देता है)। तो अंततः, MATCH(TRUE,the above,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,the above,1) वह रेंज लौटाता है जिसे हम वास्तव में ढूंढ रहे हैं: FruitsHeading सेल से शुरू करें, 1 पंक्ति नीचे जाएं और 0 कॉलम पार करें, फिर एक ऐसे क्षेत्र का चयन करें जो सूची में प्रविष्टियों की संख्या के बराबर पंक्तियों लंबा हो (और 1 कॉलम चौड़ा)। तो पूरा फॉर्मूला एक साथ केवल वास्तविक प्रविष्टियों (पहले खाली सेल तक) वाली रेंज को लौटाएगा।
ड्रॉपडाउन के स्रोत के लिए रेंज को परिभाषित करने के लिए इस फॉर्मूला का उपयोग करने का मतलब है कि आप सूची को स्वतंत्र रूप से संपादित कर सकते हैं (प्रविष्टियों को जोड़ या हटा सकते हैं, जब तक शेष प्रविष्टियां शीर्ष सेल से शुरू होती हैं और सन्निकट हैं) और ड्रॉपडाउन हमेशा वर्तमान सूची को प्रतिबिंबित करेगा (चित्र 6 देखें)।
यहां उपयोग की गई उदाहरण फ़ाइल (Dynamic Lists) शामिल है और इस वेबसाइट से डाउनलोड करने योग्य है। हालांकि मैक्रोज़ काम नहीं करते, क्योंकि WordPress को मैक्रो वाली Excel बुक पसंद नहीं है।
सूची ब्लॉक में पंक्तियों की संख्या निर्दिष्ट करने के विकल्प के रूप में, सूची ब्लॉक को अपना स्वयं का रेंज नाम दिया जा सकता है, जिसे तब एक संशोधित फॉर्मूला में उपयोग किया जा सकता है। उदाहरण फ़ाइल में, दूसरी सूची (Names) इस विधि का उपयोग करती है। यहां, "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 (पंक्तियों की संख्या) को प्रतिस्थापित करता है।
तो, ड्रॉपडाउन सूचियों के लिए जिन्हें आसानी से संपादित किया जा सकता है (अन्य उपयोगकर्ताओं द्वारा भी जो अनुभवहीन हो सकते हैं), डायनामिक रेंज नाम आज़माएं! और ध्यान दें, हालांकि यह लेख ड्रॉपडाउन सूचियों पर केंद्रित रहा है, डायनामिक रेंज नामों का उपयोग कहीं भी किया जा सकता है जहां आपको किसी रेंज या सूची को संदर्भित करने की आवश्यकता होती है जो आकार में भिन्न हो सकती है। आनंद लें!
