एक्सेल में ड्रॉपडाउन लिस्ट कैसे बनाएं

ड्रॉप-डाउन सूची एक अविश्वसनीय रूप से उपयोगी उपकरण है जो जानकारी के साथ काम करने को और अधिक आरामदायक बनाने में मदद कर सकता है। यह एक सेल में एक साथ कई मूल्यों को समाहित करना संभव बनाता है, जिसके साथ आप काम कर सकते हैं, जैसे कि किसी अन्य के साथ। आपको जिसकी आवश्यकता है उसे चुनने के लिए, बस तीर आइकन पर क्लिक करें, जिसके बाद uXNUMXbuXNUMXbis के मानों की एक सूची प्रदर्शित होती है। एक विशिष्ट का चयन करने के बाद, सेल स्वचालित रूप से इससे भर जाता है, और इसके आधार पर सूत्रों की पुनर्गणना की जाती है।

एक्सेल ड्रॉप-डाउन मेनू बनाने के लिए कई अलग-अलग तरीके प्रदान करता है, और इसके अलावा, यह आपको लचीले ढंग से उन्हें अनुकूलित करने की अनुमति देता है। आइए इन विधियों का अधिक विस्तार से विश्लेषण करें।

सूची निर्माण प्रक्रिया

एक पॉप-अप मेनू बनाने के लिए, "डेटा" - "डेटा सत्यापन" पथ के साथ मेनू आइटम पर क्लिक करें। एक डायलॉग बॉक्स खुलेगा जहां आपको "पैरामीटर" टैब खोजने की जरूरत है और अगर इसे पहले नहीं खोला गया है तो उस पर क्लिक करें। इसकी बहुत सारी सेटिंग्स हैं, लेकिन "डेटा प्रकार" आइटम हमारे लिए महत्वपूर्ण है। सभी अर्थों में से, "सूची" सही है।

पॉप-अप सूची में जानकारी दर्ज करने के तरीकों की संख्या काफी बड़ी है।

1. समान संवाद बॉक्स के एक ही टैब पर स्थित "स्रोत" फ़ील्ड में अर्धविराम द्वारा अलग किए गए सूची तत्वों का स्वतंत्र संकेत।
   

   

2. मूल्यों का प्रारंभिक संकेत। स्रोत फ़ील्ड में वह श्रेणी होती है जहाँ आवश्यक जानकारी उपलब्ध होती है।
   

   

3. एक नामित श्रेणी निर्दिष्ट करना। एक विधि जो पिछले एक को दोहराती है, लेकिन केवल प्रारंभिक रूप से श्रेणी का नाम देना आवश्यक है।
   

   

इनमें से कोई भी तरीका वांछित परिणाम देगा। आइए वास्तविक जीवन की स्थितियों में ड्रॉप-डाउन सूची बनाने के तरीकों को देखें।

सूची से डेटा के आधार पर

मान लें कि हमारे पास विभिन्न फलों के प्रकारों का वर्णन करने वाली एक तालिका है।

जानकारी के इस सेट के आधार पर ड्रॉप-डाउन मेनू में एक सूची बनाने के लिए, आपको निम्न कार्य करने होंगे:

1. भविष्य की सूची के लिए आरक्षित सेल का चयन करें।
2. रिबन पर डेटा टैब ढूंढें। वहां हम "डेटा सत्यापित करें" पर क्लिक करते हैं।
   

   

3. आइटम "डेटा प्रकार" ढूंढें और मान को "सूची" पर स्विच करें।
   

   

4. "स्रोत" विकल्प को दर्शाने वाले क्षेत्र में, वांछित श्रेणी दर्ज करें। कृपया ध्यान दें कि पूर्ण संदर्भों को निर्दिष्ट किया जाना चाहिए ताकि सूची की प्रतिलिपि बनाते समय, जानकारी स्थानांतरित न हो।
   

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

मैनुअल डेटा रिकॉर्डिंग के साथ

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

मान लीजिए कि हमें दो संभावित विकल्पों वाली सूची बनाने के कार्य का सामना करना पड़ रहा है: हाँ और नहीं। कार्य को पूरा करने के लिए, यह आवश्यक है:

1. सूची के लिए सेल पर क्लिक करें।
2. "डेटा" खोलें और हमारे लिए परिचित "डेटा चेक" अनुभाग ढूंढें।
   

   

3. फिर से, "सूची" प्रकार का चयन करें।
   

   

4. यहां आपको “हां; नहीं" स्रोत के रूप में। हम देखते हैं कि गणना के लिए अर्धविराम का उपयोग करके मैन्युअल रूप से जानकारी दर्ज की जाती है।

OK पर क्लिक करने के बाद, हमारे पास निम्न परिणाम है।

इसके बाद, प्रोग्राम स्वचालित रूप से उपयुक्त सेल में एक ड्रॉप-डाउन मेनू बनाएगा। सभी जानकारी जो उपयोगकर्ता ने पॉप-अप सूची में आइटम के रूप में निर्दिष्ट की है। कई कक्षों में सूची बनाने के नियम पिछले वाले के समान हैं, केवल अपवाद के साथ कि आपको अर्धविराम का उपयोग करके मैन्युअल रूप से जानकारी निर्दिष्ट करनी होगी।

OFFSET फ़ंक्शन का उपयोग करके ड्रॉप-डाउन सूची बनाना

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

चलो चादर खोलते हैं।

ड्रॉपडाउन सूची के लिए फ़ंक्शन का उपयोग करने के लिए, आपको निम्न कार्य करने होंगे:

1. रुचि के सेल का चयन करें जहाँ आप भविष्य की सूची रखना चाहते हैं।
2. क्रम में "डेटा" टैब और "डेटा सत्यापन" विंडो खोलें।
   

   

3. सूची सेट करें"। यह पिछले उदाहरणों की तरह ही किया जाता है। अंत में, निम्न सूत्र का उपयोग किया जाता है: =ऑफसेट(ए$2$;0;0;5)। हम इसे दर्ज करते हैं जहां एक तर्क के रूप में उपयोग की जाने वाली कोशिकाओं को निर्दिष्ट किया जाता है।

फिर प्रोग्राम फलों की सूची के साथ एक मेनू तैयार करेगा।

इसके लिए वाक्यविन्यास है:

= ऑफ़सेट (संदर्भ, लाइन_ऑफ़सेट, कॉलम_ऑफ़सेट, [ऊंचाई], [चौड़ाई])

हम देखते हैं कि इस फ़ंक्शन में 5 तर्क हैं। सबसे पहले, ऑफसेट होने वाला पहला सेल पता दिया जाता है। अगले दो तर्क निर्दिष्ट करते हैं कि कितनी पंक्तियों और स्तंभों को ऑफसेट करना है। हमारे बारे में बात करते हुए, ऊंचाई तर्क 5 है क्योंकि यह सूची की ऊंचाई का प्रतिनिधित्व करता है। 

डेटा प्रतिस्थापन के साथ एक्सेल में ड्रॉपडाउन सूची (+ OFFSET फ़ंक्शन का उपयोग करके)

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

नई जानकारी दर्ज करने के समर्थन के साथ एक गतिशील सूची बनाने के लिए, आपको यह करना होगा:

1. रुचि के सेल का चयन करें।
2. "डेटा" टैब का विस्तार करें और "डेटा सत्यापन" पर क्लिक करें।
3. खुलने वाली विंडो में, "सूची" आइटम को फिर से चुनें और डेटा स्रोत के रूप में निम्न सूत्र निर्दिष्ट करें: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. ठीक क्लिक करें.

इसमें एक फ़ंक्शन शामिल है COUNTIF, तुरंत यह निर्धारित करने के लिए कि कितने सेल भरे गए हैं (हालाँकि इसमें बहुत अधिक संख्या में उपयोग हैं, हम इसे यहाँ एक विशिष्ट उद्देश्य के लिए लिखते हैं)।

सूत्र के सामान्य रूप से कार्य करने के लिए, यह पता लगाना आवश्यक है कि क्या सूत्र के पथ पर रिक्त कक्ष हैं। उन्हें नहीं होना चाहिए।

किसी अन्य शीट या एक्सेल फ़ाइल के डेटा के साथ ड्रॉपडाउन सूची

यदि आपको किसी अन्य दस्तावेज़ या उसी फ़ाइल में निहित शीट से जानकारी प्राप्त करने की आवश्यकता है तो क्लासिक विधि काम नहीं करती है। इसके लिए, फ़ंक्शन का उपयोग किया जाता है अप्रत्यक्ष, जो आपको सही प्रारूप में किसी अन्य शीट या सामान्य रूप से एक फ़ाइल में स्थित सेल के लिए एक लिंक दर्ज करने की अनुमति देता है। आपको निम्न कार्य करने की आवश्यकता है:

1. उस सेल को सक्रिय करें जहां हम सूची रखते हैं।
2. हम वह विंडो खोलते हैं जिसे हम पहले से जानते हैं। उसी स्थान पर जहां हमने पहले अन्य श्रेणियों के लिए स्रोतों का संकेत दिया था, प्रारूप में एक सूत्र दर्शाया गया है =अप्रत्यक्ष ("[List1.xlsx]पत्रक1!$A$1:$A$9"). स्वाभाविक रूप से, List1 और Sheet1 के बजाय, आप क्रमशः अपनी पुस्तक और शीट के नाम सम्मिलित कर सकते हैं। 

ध्यान! फ़ाइल का नाम वर्गाकार कोष्ठकों में दिया गया है। इस स्थिति में, Excel उस फ़ाइल का उपयोग नहीं कर पाएगा जो वर्तमान में सूचना के स्रोत के रूप में बंद है।

यह भी ध्यान दिया जाना चाहिए कि फ़ाइल का नाम केवल तभी समझ में आता है जब आवश्यक दस्तावेज़ उसी फ़ोल्डर में स्थित हो जहां सूची डाली जाएगी। यदि नहीं, तो आपको इस दस्तावेज़ का पूरा पता बताना होगा।

आश्रित ड्रॉपडाउन बनाना

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

सूचियाँ बनाने के लिए आपको इन चरणों का पालन करना होगा, जिनका परिणाम किसी अन्य सूची में चयनित विकल्प से प्रभावित होता है।

1. श्रेणी नामों के साथ पहली सूची बनाएं।
   

   

2. स्रोत प्रवेश बिंदु पर, आवश्यक संकेतकों को एक-एक करके हाइलाइट किया जाता है।
   

   

3. व्यक्ति द्वारा चुने गए पौधे के प्रकार के आधार पर दूसरी सूची बनाएं। वैकल्पिक रूप से, यदि आप पहली सूची में पेड़ निर्दिष्ट करते हैं, तो दूसरी सूची में जानकारी "ओक, हॉर्नबीम, चेस्टनट" और उससे आगे होगी। डेटा स्रोत के इनपुट के स्थान पर सूत्र लिखना आवश्यक है = अप्रत्यक्ष (ई 3)। E3 - श्रेणी का नाम युक्त सेल 1.=अप्रत्यक्ष(E3)। E3 - सूची 1 के नाम वाला सेल।

अब सब कुछ तैयार है।

ड्रॉप डाउन सूची से एकाधिक मानों का चयन कैसे करें?

कभी-कभी केवल एक मान को वरीयता देना संभव नहीं होता है, इसलिए एक से अधिक का चयन किया जाना चाहिए। फिर आपको पेज कोड में एक मैक्रो जोड़ना होगा। कुंजी संयोजन Alt + F11 का उपयोग करके Visual Basic Editor खोलता है। और कोड वहां डाला गया है।

निजी उप वर्कशीट_चेंज (ByVal लक्ष्य सीमा के रूप में)

    त्रुटि पर अगला पुनरारंभ

    इफ नॉट इंटरसेक्ट (टारगेट, रेंज(«Е2:Е9»)) इज नथिंग एंड टारगेट। सेल। काउंट = 1 फिर

        आवेदन.सक्षम ईवेंट = गलत

        यदि लेन (लक्ष्य। ऑफसेट (0, 1)) = 0 तो

            लक्ष्य। ऑफसेट (0, 1) = लक्ष्य

        अन्य

            लक्ष्य.अंत (xlToRight) .Offset (0, 1) = लक्ष्य

        अगर अंत

        लक्ष्य। सामग्री साफ़ करें

        आवेदन.सक्षम ईवेंट = सत्य

    अगर अंत

अंत उप 

कोशिकाओं की सामग्री को नीचे दिखाए जाने के लिए, हम संपादक में निम्नलिखित कोड डालते हैं।

निजी उप वर्कशीट_चेंज (ByVal लक्ष्य सीमा के रूप में)

    त्रुटि पर अगला पुनरारंभ

    इफ नॉट इंटरसेक्ट(टारगेट, रेंज(«Н2:К2»)) इज नथिंग एंड टारगेट। सेल। काउंट = 1 फिर

        आवेदन.सक्षम ईवेंट = गलत

        यदि लेन (लक्ष्य। ऑफसेट (1, 0)) = 0 तो

            लक्ष्य। ऑफसेट (1, 0) = लक्ष्य

        अन्य

            लक्ष्य.अंत (xlDown) .Offset (1, 0) = लक्ष्य

        अगर अंत

        लक्ष्य। सामग्री साफ़ करें

        आवेदन.सक्षम ईवेंट = सत्य

    अगर अंत

अंत उप

और अंत में, इस कोड का उपयोग एक सेल में लिखने के लिए किया जाता है।

निजी उप वर्कशीट_चेंज (ByVal लक्ष्य सीमा के रूप में)

    त्रुटि पर अगला पुनरारंभ

    इफ नॉट इंटरसेक्ट (टारगेट, रेंज («C2:C5»)) इज नथिंग एंड टारगेट। सेल। काउंट = 1 फिर

        आवेदन.सक्षम ईवेंट = गलत

        न्यूवैल = लक्ष्य

        आवेदन। पूर्ववत करें

        ओल्डवल = लक्ष्य

        यदि लेन (ओल्डवैल) <> 0 और ओल्डवैल <> न्यूवैल तब

            लक्ष्य = लक्ष्य और «,» और newVal

        अन्य

            लक्ष्य = नयावैल

        अगर अंत

        यदि लेन (newVal) = 0 तो लक्ष्य। ClearContents

        आवेदन.सक्षम ईवेंट = सत्य

    अगर अंत

अंत उप

रेंज संपादन योग्य हैं।

खोज के साथ ड्रॉपडाउन सूची कैसे बनाएं?

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

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

स्वचालित डेटा प्रतिस्थापन के साथ ड्रॉपडाउन सूची

एक फ़ंक्शन यह भी है कि डेटा को श्रेणी में जोड़े जाने के बाद स्वचालित रूप से प्रतिस्थापित किया जाता है। ऐसा करना आसान है:

1. भविष्य की सूची के लिए कक्षों का एक सेट बनाएँ। हमारे मामले में, यह रंगों का एक सेट है। हम इसे चुनते हैं।
   

   

2. अगला, इसे एक तालिका के रूप में स्वरूपित करने की आवश्यकता है। आपको उसी नाम के बटन पर क्लिक करना होगा और तालिका शैली का चयन करना होगा।
   

   

   

इसके बाद, आपको "ओके" बटन दबाकर इस सीमा की पुष्टि करने की आवश्यकता है।

हम परिणामी तालिका का चयन करते हैं और इसे कॉलम ए के शीर्ष पर स्थित इनपुट फ़ील्ड के माध्यम से एक नाम देते हैं।

बस, एक तालिका है, और इसे ड्रॉप-डाउन सूची के आधार के रूप में उपयोग किया जा सकता है, जिसके लिए आपको आवश्यकता होगी:

1. उस सेल का चयन करें जहां सूची स्थित है।
2. डेटा सत्यापन संवाद खोलें।
   

   

3. हम डेटा प्रकार को "सूची" पर सेट करते हैं, और मानों के रूप में हम तालिका का नाम = चिह्न के माध्यम से देते हैं।
   

   

   

सब कुछ, सेल तैयार है, और इसमें रंगों के नाम दिखाए गए हैं, जैसा कि हमें मूल रूप से चाहिए था। अब आप नए पदों को केवल पिछले वाले के तुरंत बाद थोड़ी नीचे स्थित सेल में लिखकर जोड़ सकते हैं।

तालिका का यह लाभ है कि जब नया डेटा जोड़ा जाता है तो सीमा अपने आप बढ़ जाती है। तदनुसार, सूची जोड़ने का यह सबसे सुविधाजनक तरीका है।

ड्रॉप डाउन लिस्ट को कॉपी कैसे करें?

कॉपी करने के लिए, कुंजी संयोजन Ctrl + C और Ctrl + V का उपयोग करना पर्याप्त है। इसलिए प्रारूपण के साथ ड्रॉप-डाउन सूची की प्रतिलिपि बनाई जाएगी। स्वरूपण को हटाने के लिए, आपको एक विशेष पेस्ट का उपयोग करने की आवश्यकता है (संदर्भ मेनू में, यह विकल्प सूची की प्रतिलिपि बनाने के बाद दिखाई देता है), जहां "मानों पर शर्तें" विकल्प सेट है।

ड्रॉप डाउन सूची वाले सभी कक्षों का चयन करें

इस कार्य को पूरा करने के लिए, आपको "ढूंढें और चुनें" समूह में "कोशिकाओं के समूह का चयन करें" फ़ंक्शन का उपयोग करना होगा।

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