लिंक की गई ड्रॉप-डाउन सूचियाँ

विवरण

Excel में आप जल्दी और आसानी से कर सकते हैं एक ड्रॉपडाउन सूची बनाएं , लेकिन क्या आपने कभी आश्रित ड्रॉपडाउन सूची बनाने का प्रयास किया है? ऐसी सूची बनाने के कई तरीके हैं, तो आइए उन पर एक नज़र डालें।

विधि 1. INDIRECT फ़ंक्शन

यह तकनीक INDIRECT फ़ंक्शन के अनुप्रयोग पर आधारित है, जो एक साधारण काम कर सकती है - किसी भी निर्दिष्ट सेल की सामग्री को एक श्रेणी पते में परिवर्तित करें जिसे Excel समझता है। अर्थात्, यदि सेल में टेक्स्ट है " А1 ", तो परिणामस्वरूप फ़ंक्शन सेल को एक संदर्भ देगा А1 . यदि सेल में "शब्द है Auto ", तो फ़ंक्शन नाम के साथ नामित श्रेणी के संदर्भ को आउटपुट करेगा Auto वगैरह।

उदाहरण के लिए, कार मॉडलों की इस सूची को लें Toyota, Ford और Nissan :

टोयोटा मॉडल की पूरी सूची का चयन करें (सेल से)। А2 और सूची के अंत तक) और इस श्रेणी को एक नाम दें Toyota मेनू में सम्मिलित करें - नाम - निर्दिष्ट करें (Insert - Name - Define) . फिर हम सूचियों के साथ भी यही दोहराएंगे Ford और Nissan , तदनुसार श्रेणियों के नाम निर्दिष्ट करना Ford और Nissan .

नाम निर्दिष्ट करते समय, याद रखें कि Excel में श्रेणियों के नामों में रिक्त स्थान, विराम चिह्न नहीं होने चाहिए और वे एक अक्षर से शुरू होने चाहिए। इसलिए, यदि किसी कार ब्रांड में कोई अंतर था (उदाहरण के लिए)। Ssang Yong), तो इसे सेल में और रेंज के नाम में अंडरस्कोर (यानी) से बदलना होगा Ssang_Yong).

आइए अब कार ब्रांड चुनने के लिए पहली ड्रॉप-डाउन सूची बनाएं। एक खाली सेल चुनें और मेनू खोलें डेटा - जांचें (Data - Validation) , फिर ड्रॉप-डाउन सूची से डेटा प्रकार कोई विकल्प चुनें सूची और मैदान में स्रोत - ब्रांड नामों के साथ कोशिकाओं को हाइलाइट करें (हमारे उदाहरण में पीली कोशिकाएं)। पर क्लिक करने के बाद ОК पहली ड्रॉप-डाउन सूची तैयार है:

अब दूसरी ड्रॉप-डाउन सूची बनाएं, जो पहली सूची में चयनित ब्रांड के मॉडल प्रदर्शित करेगी। पिछले मामले की तरह, एक खाली सेल का चयन करें और मेनू खोलें डेटा - जांचें - आगे सूची . क्षेत्र में स्रोत आपको निम्नलिखित सूत्र दर्ज करना होगा:

=INDIRECT(F3)

कहाँ:

F3 - पहली ड्रॉप-डाउन सूची वाले सेल का पता - इसे अपने से बदलें।

सभी। पर क्लिक करने के बाद ठीक है दूसरी सूची की सामग्री का चयन पहली सूची में चयनित श्रेणी के नाम से किया जाएगा।

इस विधि के विपक्ष:

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

विधि 2. मिलान सूची (OFFSET और MATCH)

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

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

श्रेणी D1:D3 का नाम दें (उदा ब्रांड्स )
टैब पर चयन करें डेटा (Data) टीम डेटा सत्यापन (Data validation)
ड्रॉप-डाउन सूची से एक चेक विकल्प चुनें सूची (List) और गुणवत्ता के रूप में निर्दिष्ट करें सूत्रों का कहना है (Source) = ब्रांड्स या बस सेल D1:D3 का चयन करें (यदि वे सूची के समान शीट पर हैं)।

लेकिन मॉडलों की एक आश्रित सूची के लिए, आपको फ़ंक्शन OFFSET के साथ एक नामित श्रेणी बनानी होगी, जो गतिशील रूप से केवल एक निश्चित ब्रांड के मॉडल की कोशिकाओं को संदर्भित करेगी। इसके लिए:

Ctrl + F3 दबाएँ या बटन का उपयोग करें नाम प्रबंधक (Name manager) टैब पर सूत्रों (Formulas) . 2003 से पहले के संस्करणों में, यह एक मेनू कमांड था सम्मिलित करें - नाम - निर्दिष्ट करें (Insert - Name - Define)
किसी भी नाम से एक नई नामित श्रेणी बनाएं (उदा मॉडल ) और क्षेत्र में जोड़ना (Reference) विंडो के निचले भाग में, मैन्युअल रूप से निम्न सूत्र दर्ज करें:

=OFFSET( $ए$1 ; MATCH($G$7;$A:$A;0)-1 ; 1 ; COUNTIF($A:$A;$G$7) ; 1 )

सन्दर्भ निरपेक्ष (संकेतों सहित) होने चाहिए $ ). _TblEnter दबाने के बाद, शीट नाम स्वचालित रूप से सूत्र में जुड़ जाएंगे।

फ़ंक्शन OFFSET पंक्तियों और स्तंभों की दी गई संख्या द्वारा प्रारंभिक सेल के सापेक्ष स्थानांतरित वांछित आकार की एक सीमा का संदर्भ जारी करने में सक्षम है। अधिक समझने योग्य संस्करण में, इस फ़ंक्शन का सिंटैक्स इस प्रकार है:

=OFFSET( प्रारंभ_सेल ; नीचे खिसकना ; शिफ्ट_राइट ; रेजेज_अक्ष_में_पंक्तियां ; रेंज_साइज_इन_कॉलम )

इसलिए:

प्रारंभ_सेल - हम अपनी सूची का पहला सेल लेते हैं, यानी ए 1 ;
नीचे खिसकना - हम MATCH फ़ंक्शन को गिनते हैं, जो सीधे शब्दों में कहें तो, दी गई रेंज (कॉलम) में चयनित ब्रांड (G7) के साथ सेल का सीरियल नंबर आउटपुट करता है और );
शिफ्ट_राइट =1 , इसलिए हम आसन्न कॉलम में मॉडलों का उल्लेख करना चाहते हैं ( में );
रेजेज_अक्ष_में_पंक्तियां - हम COUNTIF फ़ंक्शन का उपयोग करके गणना करते हैं, जो हमें आवश्यक मानों की सूची (कॉलम ए) में घटनाओं की संख्या की गणना करने में सक्षम है - कार ब्रांड ( जी7 );
रेंज_साइज_इन_कॉलम =1 , इसलिए हमें मॉडलों के साथ एक कॉलम की आवश्यकता है।

परिणाम कुछ इस प्रकार होना चाहिए:

सेल में बनाए गए फॉर्मूले के आधार पर एक ड्रॉप-डाउन सूची जोड़ना बाकी है जी -8 . इसके लिए:

एक सेल चुनें जी -8
टैब पर चयन करें डेटा (Data) टीम डेटा सत्यापन (Data validation) या मेनू में डेटा - जांचें (Data - Validation)
ड्रॉप-डाउन सूची से चेक विकल्प चुनें सूची (List) और के रूप में दर्ज करें सूत्रों का कहना है (Source) चिन्ह हमारी सीमा के नाम के बराबर है, अर्थात = मॉडल .

विषय पर लेख: