Excel विशाल डेटासेट से निपटने के लिए सबसे उपयोगी टूल है। आमतौर पर हम ड्रॉप-डाउन सूची बनाना जानते हैं, लेकिन अक्सर हमें डायनामिक ड्रॉप-डाउन सूची बनाने की आवश्यकता होती है ताकि काम आसान हो। हम OFFSET फ़ंक्शन लागू करके इसे आसानी से कर सकते हैं। इस लेख में, मैं आपको दिखाऊंगा कि OFFSET फ़ंक्शन के साथ Excel में डायनामिक ड्रॉप-डाउन सूची कैसे बनाएं।
यह वह डेटासेट है जिसका उपयोग मैं OFFSET फ़ंक्शन के साथ Excel में डायनामिक ड्रॉप-डाउन सूची बनाने के लिए करूंगा। हमारे पास कुछ खेल इवेंट और विजेताओं की सूची है। हम डायनामिक ड्रॉप-डाउन सूची बनाकर विजेताओं को संबंधित इवेंट के अनुसार क्रमबद्ध करेंगे।
Excel OFFSET का उपयोग करके डायनामिक ड्रॉप-डाउन सूची बनाने के 3 तरीके
1. Excel में OFFSET और COUNTA फ़ंक्शन के साथ डायनामिक ड्रॉप-डाउन सूची बनाएं
यहां, मैं OFFSET और COUNTA फ़ंक्शन का उपयोग करके Excel में डायनामिक ड्रॉप-डाउन सूची बनाने का तरीका बताऊंगा। मुझे रेंज C4:C11 में एक डायनामिक ड्रॉप-डाउन सूची बनानी है। मैं विजेताओं की सूची से विजेता का चयन करूंगा।
चरण:
➤ रेंज C4:C11 चुनें। फिर Data टैब >> Data Tools >> Data Validation >> Data Validation पर जाएं।
➤ Data Validation डायलॉग बॉक्स खुलेगा। उस डायलॉग बॉक्स में ड्रॉप-डाउन से List चुनें।
➤ Source बॉक्स में निम्न सूत्र लिखें।
=OFFSET($E$4,0,0,COUNTA($E$4:$E$100),1)
सूत्र विश्लेषण
➥ COUNTA($E$4:$E$100) ➜ रेंज E4:E100 में खाली न होने वाली सेल्स की संख्या लौटाता है
आउटपुट ➜ {4}
➥ OFFSET($E$4,0,0,COUNTA($E$4:$E$100),1) ➜ दिए गए रेफरेंस की पंक्ति और कॉलम के आधार पर एक रेंज लौटाता है।
➥ OFFSET($E$4,0,0,4,1)
आउटपुट ➜ {"Alex";"Morgan";"Faulkner";"Eliot"}
व्याख्या: रेफरेंस है E4। चूंकि पंक्ति 0 है और कॉलम 0 है, अंततः 4 सेल्स की ऊंचाई के साथ, हमें सेल E4:E7 से मान मिलेंगे।
➤ OK चुनें।
Excel रेंज C4:C11 की प्रत्येक सेल में एक ड्रॉप-डाउन बॉक्स बनाएगा।
ध्यान दें कि ड्रॉप-डाउन बॉक्स में विकल्प बिल्कुल विजेताओं की सूची की तरह हैं। अब, यह जांचने के लिए कि यह डायनामिक ड्रॉप-डाउन बॉक्स है या नहीं, मान लें कि शूटिंग इवेंट का विजेता James है। चूंकि James विजेताओं की सूची में नहीं है, आइए उसका नाम जोड़ें और देखें क्या होता है।
जैसे ही हमने James का नाम विजेताओं की सूची में जोड़ा, Excel ने स्वचालित रूप से ड्रॉप-डाउन विकल्पों को अपडेट कर दिया। इसलिए ये ड्रॉप-डाउन सूचियाँ प्रकृति में डायनामिक हैं।
➤ अब शेष विजेता चुनें।
नोट: याद रखें कि COUNTA फ़ंक्शन में हमने जो रेंज चुनी है वह E4:E100 है। इसलिए Excel तब तक ड्रॉप-डाउन विकल्पों को अपडेट करेगा जब तक हम रेंज E4:E100 में सेल्स जोड़ते या अपडेट करते हैं।
और पढ़ें: Excel में VBA का उपयोग करके डायनामिक डेटा वैलिडेशन सूची कैसे बनाएं
2. Excel में OFFSET और COUNTIF फ़ंक्शन के साथ डायनामिक ड्रॉप-डाउन सूची बनाने का तरीका
हम OFFSET और COUNTIF फ़ंक्शन का उपयोग करके Excel में डायनामिक ड्रॉप-डाउन सूची भी बना सकते हैं।
चरण:
➤ Data Validation डायलॉग बॉक्स लाएं जैसे विधि-1। Source बॉक्स में निम्न सूत्र लिखें
=OFFSET($E$4,0,0,COUNTIF($E$4:$E$100,"<>"))
सूत्र विश्लेषण
➥ COUNTIF($E$4:$E$100,"<>")➜ रेंज E4:E100 में खाली न होने वाली सेल्स की संख्या लौटाता है
आउटपुट ➜ {4}
➥ OFFSET($E$4,0,0,COUNTIF($E$4:$E$100,"<>")) ➜ दिए गए रेफरेंस की पंक्ति और कॉलम के आधार पर एक रेंज लौटाता है।
➥ OFFSET($E$4,0,0,4,1)
आउटपुट ➜ {"Alex";"Morgan";"Faulkner";"Eliot"}
व्याख्या: रेफरेंस है E4। चूंकि पंक्ति 0 है और कॉलम 0 है, अंततः 4 सेल्स की ऊंचाई के साथ, हमें सेल E4:E7 से मान मिलेंगे
➤ OK चुनें।
➤ Excel रेंज C4:C11 की प्रत्येक सेल में एक ड्रॉप-डाउन बॉक्स बनाएगा।
यह जांचने के लिए कि यह डायनामिक ड्रॉप-डाउन बॉक्स है या नहीं, मान लें कि शूटिंग इवेंट का विजेता James है। चूंकि James विजेताओं की सूची में नहीं है, आइए उसका नाम जोड़ें और देखें क्या होता है।
जैसे ही हमने James का नाम विजेताओं की सूची में जोड़ा, Excel ने स्वचालित रूप से ड्रॉप-डाउन विकल्पों को अपडेट कर दिया। इसलिए ये ड्रॉप-डाउन सूचियाँ प्रकृति में डायनामिक हैं।
➤ अब शेष विजेता चुनें।
नोट: याद रखें कि COUNTIF फ़ंक्शन में हमने जो रेंज चुनी है वह E4:E100 है। इसलिए Excel तब तक ड्रॉप-डाउन विकल्पों को अपडेट करेगा जब तक हम रेंज E4:E100 में सेल्स जोड़ते या अपडेट करते हैं।
3. फ़ंक्शन के संयोजन का उपयोग करके नेस्टेड ड्रॉप-डाउन सूची कैसे बनाएं
इस खंड में, हम एक स्मार्ट और अधिक उन्नत डायनामिक ड्रॉप-डाउन सूची बनाने में गोता लगाएंगे, एक नेस्टेड सूची। हम OFFSET, COUNTA, और MATCH फ़ंक्शन का एक साथ उपयोग करेंगे। मैं समझाता हूं कि हम क्या करने जा रहे हैं।
यह इस विधि के लिए डेटासेट है जो विशेष उत्पादों की जानकारी दर्शाता है। मूल रूप से, हम सेल F3 और F4 में दो ड्रॉप-डाउन सूचियाँ बनाएंगे। F3 में चुने गए विकल्प के आधार पर, Excel F4 में विकल्पों को अपडेट करेगा। आइए इसे चरण दर चरण करें।
चरण-1: F3 में ड्रॉप-डाउन सूची बनाना
➤ Data Validation डायलॉग बॉक्स लाएं जैसे विधि-1। Source बॉक्स में, एक सेल रेफरेंस दें, जो टेबल हेडर हैं (सेल B3:D3)।
Excel F3 में एक ड्रॉप-डाउन सूची बनाएगा।
चरण-2: F4 में डायनामिक ड्रॉप-डाउन सूची बनाना
अब मैं F4 में एक और ड्रॉप-डाउन सूची बनाऊंगा। F4 की ड्रॉप-डाउन सूची के विकल्प इस बात पर निर्भर करेंगे कि हमने F3 की ड्रॉप-डाउन सूची में क्या चुना है। ऐसा करने के लिए,
➤ Data Validation डायलॉग बॉक्स लाएं जैसे विधि-1। Source बॉक्स में निम्न सूत्र लिखें
=OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,COUNTA(OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1)),1)
सूत्र विश्लेषण
➥ MATCH($F$3,$B$3:$D$3,0) ➜ रेंज B3:D3 से सेल मान F3 की सापेक्ष स्थिति लौटाता है
आउटपुट: {1}।
➥ OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1) ➜ दिए गए रेफरेंस की पंक्ति और कॉलम के आधार पर एक रेंज लौटाता है। ऊंचाई 10 है। इसलिए आउटपुट रेफरेंस से शुरू होने वाले 10 सेल मानों की एक सरणी होगी।
आउटपुट: {"Sam";"Curran";"Yank";"Rochester";0;0;0;0;0;0}
➥ COUNTA(OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1)) ➜ चयनित रेंज में खाली न होने वाली सेल्स की संख्या लौटाता है।
➥ COUNTA{"Sam";"Curran";"Yank";"Rochester";0;0;0;0;0;0}
आउटपुट: {4}
➥ OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,COUNTA(OFFSET($B$3,1,MATCH ($F$3,$B $3:$D$3,0)-1,10,1)),1) ➔ दिए गए रेफरेंस की पंक्ति और कॉलम के आधार पर एक रेंज लौटाता है
➥ OFFSET($B$3,1,1-1,COUNTA{"Sam";"Curran";"Yank";"Rochester";0;0;0;0;0;0}),1)
➥ OFFSET($B$3,1,0,4,1)
आउटपुट: {"Sam";"Curran";"Yank";"Rochester"}
व्याख्या: रेफरेंस है B3। चूंकि पंक्ति 1 है और कॉलम 0 है, अंततः 4 सेल्स की ऊंचाई के साथ, हमें सेल B4:B7 से मान मिलेंगे।
➤ OK चुनें।
Excel F4 में एक डायनामिक ड्रॉप-डाउन सूची बनाएगा। विकल्प इस बात पर बदलेंगे कि आप F3 पर क्या चुनते हैं। उदाहरण के लिए, जब आप F3 ड्रॉप-डाउन सूची में Name चुनते हैं, तो F4 में ड्रॉप-डाउन सूची Name कॉलम में उपलब्ध नाम दिखाएगी।
इसी तरह, जब आप F3 ड्रॉप-डाउन सूची में Product चुनते हैं, तो F4 में ड्रॉप-डाउन सूची Product कॉलम में उपलब्ध उत्पाद दिखाएगी।
अब यदि आप Name, Product, या Brand जोड़ते या अपडेट करते हैं, तो Excel F4 में ड्रॉप-डाउन सूची को अपडेट करेगा। उदाहरण के लिए, मैंने Name कॉलम में एक नया नाम Rock जोड़ा है और Excel ने उस नाम को ड्रॉप-डाउन सूची में जोड़ दिया है।
और पढ़ें: Excel में डायनामिक टॉप 10 सूची कैसे बनाएं (8 तरीके)
अभ्यास कार्यपुस्तिका
जैसा कि आप देख सकते हैं, OFFSET फ़ंक्शन के साथ Excel में डायनामिक ड्रॉप-डाउन सूची बनाना वास्तव में मुश्किल है। इसलिए मैं सुझाव देता हूं कि आप अधिक से अधिक अभ्यास करें। मैंने आपके लिए एक अभ्यास शीट संलग्न की है।
निष्कर्ष
इस लेख में, मैंने OFFSET फ़ंक्शन के साथ Excel में डायनामिक ड्रॉप-डाउन सूची बनाने के 3 तरीके बताए हैं। मुझे आशा है कि आपको यह लेख उपयोगी लगेगा। अंत में, यदि आपके कोई टिप्पणी हैं, तो कृपया उन्हें टिप्पणी बॉक्स में छोड़ दें।
संबंधित लेख
- Excel डायनामिक सूची टेबल से बनाएं (3 आसान तरीके)
- Excel में मानदंड के आधार पर डायनामिक सूची कैसे बनाएं (एकाधिक और एकल मानदंड)
