एक्सेल स्प्रेडशीट में डेटा प्रविष्टि को सरल और / या मानकीकृत करने के लिए अक्सर सेल ड्रॉपडाउन शामिल होते हैं। ये ड्रॉपडाउन स्वीकार्य प्रविष्टियों की सूची निर्दिष्ट करने के लिए डेटा सत्यापन सुविधा का उपयोग करके बनाए गए हैं।
एक सरल ड्रॉपडाउन सूची सेट करने के लिए, उस सेल का चयन करें जहां डेटा दर्ज किया जाएगा, फिर डेटा सत्यापन(डेटाटैब पर), डेटा सत्यापन चुनें, सूचीचुनें (अनुमति दें :) के तहत, और फिर सूची आइटम (अल्पविराम द्वारा अलग) दर्ज करें। >स्रोत: फ़ील्ड (चित्र 1 देखें)।
इस प्रकार के बुनियादी ड्रॉपडाउन में, स्वीकार्य प्रविष्टियों की सूची निर्दिष्ट है डेटा सत्यापन के भीतर ही; इसलिए, सूची में परिवर्तन करने के लिए, उपयोगकर्ता को डेटा सत्यापन खोलना और संपादित करना होगा। हालांकि, अनुभवहीन उपयोगकर्ताओं के लिए, या ऐसे मामलों में, जहां विकल्पों की सूची लंबी है, मुश्किल हो सकती है।
सूची को एक और विकल्प में डालना है, और फिर निर्दिष्ट करें वह श्रेणी नाम (<बराबर>के साथ पूर्व संकेत के साथ) स्रोत: डेटा सत्यापन का क्षेत्र (जैसा कि चित्र 2 में दिखाया गया है)।
<। / p>In_content_1 सभी: [300x250] / dfp: [640x360]->
यह दूसरी विधि सूची में विकल्पों को संपादित करना आसान बनाती है, लेकिन आइटम जोड़ना या निकालना समस्याग्रस्त हो सकता है। चूंकि नामित सीमा (फ्रूटचॉइज, हमारे उदाहरण में) कोशिकाओं की एक निश्चित सीमा को संदर्भित करता है ($ H $ 3: $ H $ 10 जैसा कि दिखाया गया है), यदि अधिक विकल्प H11 या उससे नीचे की कोशिकाओं में जोड़े जाते हैं, तो वे ड्रॉपडाउन में दिखाई नहीं देंगे। (चूँकि वे कोशिकाएँ फ्रूटचाइज़ रेंज का हिस्सा नहीं हैं)।
इसी तरह अगर, उदाहरण के लिए, पियर्स और स्ट्रॉबेरी प्रविष्टियाँ मिट जाती हैं, तो वे अब ड्रॉपडाउन में दिखाई नहीं देंगे, लेकिन इसके बजाय ड्रॉपडाउन में दो शामिल होंगे ड्रॉपडाउन के बाद से "खाली" विकल्प अभी भी पूरे FruitChoices रेंज को संदर्भित करता है, जिसमें खाली सेल H9 और H8 शामिल हैं।
इन कारणों से, जब ड्रॉपडाउन के लिए सूची स्रोत के रूप में एक सामान्य नामित सीमा का उपयोग किया जाता है, तो नामांकित सीमा। यदि प्रविष्टियों को सूची से जोड़ा जाता है या हटा दिया जाता है, तो अधिक या कम कक्षों को शामिल करने के लिए स्वयं को संपादित किया जाना चाहिए।
इस समस्या का समाधान गतिशीलड्रॉपडाउन विकल्पों के लिए स्रोत के रूप में रेंज नाम। डायनामिक रेंज नाम वह है जो स्वचालित रूप से डेटा के ब्लॉक के आकार से मेल खाने या हटाने के लिए फैलता है (या अनुबंध)। ऐसा करने के लिए, आप नामित सीमाओं को परिभाषित करने के लिए, सेल पतों की एक निश्चित सीमा के बजाय सूत्रका उपयोग करते हैं।
एक डायनामिक सेटअप कैसे करें। एक्सेल में रेंज
एक सामान्य (स्थिर) श्रेणी नाम कोशिकाओं की एक निर्दिष्ट श्रेणी को संदर्भित करता है ($ H $ 3: $ H $ 10 हमारे उदाहरण में, नीचे देखें):
लेकिन एक डायनामिक रेंज को एक सूत्र का उपयोग करके परिभाषित किया गया है (नीचे देखें, एक अलग स्प्रेडशीट से लिया गया है जो डायनामिक रेंज नामों का उपयोग करता है):
इससे पहले कि हम आरंभ करें, सुनिश्चित करें कि आप हमारे एक्सेल उदाहरण फ़ाइल डाउनलोड करें (जैसे मैक्रोज़ अक्षम कर दिए गए हैं)।
आइए इस सूत्र की विस्तार से जाँच करें। फलों के लिए विकल्प सीधे एक हेडिंग के नीचे कोशिकाओं के एक ब्लॉक में होते हैं (FRUITS)। उस शीर्षक को एक नाम भी दिया गया है: फल-भरण:
के लिए गतिशील रेंज को परिभाषित करने के लिए उपयोग किया जाने वाला संपूर्ण सूत्र फलों के विकल्प हैं:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
FruitHeadingउस शीर्षक को संदर्भित करता है जो सूची में पहली प्रविष्टि के ऊपर एक पंक्ति है। संख्या 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 (फ्रूट हियरिंग, 1,0,20,1)। यह 20 कोशिकाओं के ब्लॉक (फ्रूटहेडिंग सेल के नीचे) को संदर्भित करता है, जहां विकल्प दर्ज किए जा सकते हैं। यह OFFSET फ़ंक्शन मूल रूप से कहता है: FruitHeadingसेल पर शुरू करें, 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 मूल्यों का एक सेट बनाता है, जो यह दर्शाता है कि प्रत्येक में 20- अलग-अलग सेल हैं। OFFSET फ़ंक्शन द्वारा संदर्भित पंक्ति श्रेणी रिक्त (रिक्त) है या नहीं। इस उदाहरण में, सेट में पहले 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का उपयोग आम तौर पर फ़ंक्शन से बाहर एक निश्चित मान (या मानों की श्रेणी) लेने के लिए किया जाता है। एक निश्चित पंक्ति और कॉलम (उस ब्लॉक के भीतर) को निर्दिष्ट करके डेटा का एक ब्लॉक। लेकिन पंक्ति और स्तंभ इनपुट को शून्य पर सेट करना (जैसा कि यहां किया गया है) INDEX का कारण बनता है ताकि डेटा के पूरे ब्लॉक से युक्त एक सरणी वापस आ सके।
सूत्र का अगला टुकड़ा MATCH फ़ंक्शन है:
INDEX फ़ंक्शन द्वारा लौटाए गए सरणी के भीतर=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)
MATCHफ़ंक्शन पहले 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 (फ्रूटहेडिंग, 1,0, उपरोक्त, 1)रिटर्न रेंज जिसे हम वास्तव में ढूंढ रहे हैं: FruitHeading सेल पर शुरू करें, 1 पंक्ति से नीचे जाएं और 0 कॉलम पर जाएं, फिर एक ऐसे क्षेत्र का चयन करें जो सूची में प्रविष्टियां (और 1 कॉलम चौड़ा) होने पर भी कई पंक्तियां हैं। तो पूरा सूत्र एक साथ उस सीमा को लौटा देगा जिसमें केवल वास्तविक प्रविष्टियाँ हैं (पहली खाली सेल के नीचे)।
ड्रॉपडाउन के लिए स्रोत की सीमा को परिभाषित करने के लिए इस सूत्र का उपयोग करने का मतलब है कि आप स्वतंत्र रूप से संपादित कर सकते हैं सूची (प्रविष्टियों को जोड़ने या हटाने, जब तक कि शेष प्रविष्टियाँ शीर्ष सेल पर शुरू होती हैं और सन्निहित हैं) और ड्रॉपडाउन हमेशा वर्तमान सूची (चित्र 6 देखें) को प्रतिबिंबित करेगा।
7 <। / s>
उदाहरण फ़ाइल (डायनामिक सूची) जो यहां उपयोग किया गया है वह शामिल है और इस वेबसाइट से डाउनलोड करने योग्य है। हालाँकि, मैक्रोज़ काम नहीं करते हैं, क्योंकि वर्डप्रेस को मैक्रोज़ के साथ एक्सेल किताबें पसंद नहीं हैं।
सूची ब्लॉक में पंक्तियों की संख्या निर्दिष्ट करने के लिए एक विकल्प के रूप में, सूची ब्लॉक को सौंपा जा सकता है। स्वयं की श्रेणी का नाम, जिसे तब संशोधित सूत्र में उपयोग किया जा सकता है। उदाहरण फ़ाइल में, एक दूसरी सूची (नाम) इस पद्धति का उपयोग करती है। यहां, संपूर्ण सूची ब्लॉक ("NAMES" शीर्षक के नीचे, उदाहरण फ़ाइल में 40 पंक्तियाँ) को NameBlockकी श्रेणी नाम दिया गया है। NamesList को परिभाषित करने का वैकल्पिक सूत्र तब है:
=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)
जहां NamesBlockOFFSET की जगह (फ्रूट हियरिंग, 1,0,20,1) और ROWS (NamesBlock)पहले वाले फॉर्मूले में 20 (पंक्तियों की संख्या) को बदलता है।
इसलिए, ड्रॉपडाउन सूचियों के लिए जिसे आसानी से संपादित किया जा सकता है (अन्य उपयोगकर्ताओं द्वारा, जो अनुभवहीन हो सकते हैं, सहित) डायनामिक रेंज नामों का उपयोग करके देखें! और ध्यान दें कि, हालांकि इस लेख को ड्रॉपडाउन सूचियों पर केंद्रित किया गया है, लेकिन डायनामिक रेंज नामों का उपयोग कहीं भी किया जा सकता है, आपको एक सीमा या सूची का संदर्भ देने की आवश्यकता होती है जो आकार में भिन्न हो सकती है। का आनंद लें!