एक्सेल जब विशाल डेटासेट से निपटने की बात आती है तो यह सबसे उपयोगी उपकरण है। आम तौर पर हम ड्रॉप डाउन सूची . बनाने के आदी हो जाते हैं लेकिन हमें अक्सर एक गतिशील ड्रॉप डाउन सूची बनाने . की आवश्यकता होती है एक्सेल . में हमारे जीवन को आसान बनाने के लिए। हम ऑफसेट फ़ंक्शन applying लागू करके आसानी से ऐसा कर सकते हैं . इस लेख में, मैं आपको एक गतिशील ड्रॉप डाउन सूची . बनाने का तरीका दिखाऊंगा एक्सेल . में ऑफसेट . के साथ फ़ंक्शन ।
यह वह डेटासेट है जिसका उपयोग मैं एक डायनामिक ड्रॉप डाउन सूची . बनाने का तरीका बताने के लिए करने जा रहा हूं एक्सेल . में ऑफसेट फ़ंक्शन . के साथ . हमारे पास कुछ खेल हैं इवेंट (इवेंट) और विजेताओं की सूची . हम एक डायनामिक ड्रॉप डाउन सूची . बनाकर विजेताओं को संबंधित ईवेंट के अनुसार क्रमबद्ध करेंगे ।
एक्सेल ऑफ़सेट का उपयोग करके डायनामिक ड्रॉप डाउन सूची बनाने के 3 तरीके
<एच3>1. ऑफ़सेट और COUNTA फ़ंक्शंस के साथ एक्सेल में डायनामिक ड्रॉप डाउन लिस्ट बनाएंयहां, मैं समझाऊंगा कि कैसे एक डायनामिक ड्रॉप डाउन सूची बनाई जाए एक्सेल . में ऑफ़सेट . का उपयोग करके और COUNTA कार्य। मुझे एक गतिशील ड्रॉप डाउन सूची बनाने की आवश्यकता है C4:C11 . श्रेणी में . मैं विजेता का चयन करूंगा/करूंगी विजेताओं की सूची . से ।
कदम:
➤ श्रेणी चुनें C4:C11 . फिर डेटा . पर जाएं टैब>> डेटा टूल>> डेटा सत्यापन>> डेटा सत्यापन ।
➤ डेटा सत्यापन डायलॉग बॉक्स पॉप अप होगा। सूची Select चुनें ड्रॉप-डाउन . से उस संवाद बॉक्स . में ।
➤ स्रोत . में बॉक्स में, निम्न सूत्र लिखिए।
=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) ➜ किसी दिए गए संदर्भ की पंक्ति और स्तंभ के आधार पर एक श्रेणी लौटाता है।
➥ ऑफसेट($E$4,0,0,4,1)
आउटपुट ➜ {"एलेक्स";"मॉर्गन";"फॉल्कनर";"एलियट"}
स्पष्टीकरण: संदर्भ E4 . है . पंक्ति . के बाद से 0 . है और कॉलम 0 . है अंततः ऊंचाई . के साथ का 4 सेल, हमारे पास सेल से मान होंगे E4:E7 .
➤ ठीक . चुनें . 
एक्सेल एक ड्रॉप-डाउन बनाएगा बॉक्स प्रत्येक सेल . में श्रेणी . के C4:C11 ।
ध्यान दें कि विकल्प ड्रॉप-डाउन बॉक्स . में हैं बिल्कुल विजेताओं की सूची . की तरह हैं . अब, यह जांचने के लिए कि क्या यह एक डायनामिक ड्रॉप-डाउन बॉक्स . है या नहीं, मान लें कि विजेता इवेंट शूटिंग . के जेम्स . है . चूंकि जेम्स विजेताओं की सूची में नहीं है , चलिए उसका नाम जोड़ते हैं और देखते हैं कि क्या होता है।
जैसे ही हमने जेम्स . का नाम जोड़ा विजेताओं की सूची . में , एक्सेल ड्रॉप-डाउन विकल्पों में विकल्पों को स्वचालित रूप से अपडेट किया . तो ये ड्रॉप-डाउन सूचियां गतिशील हैं प्रकृति में .
➤ अब शेष विजेता . का चयन करें ।
नोट :याद रखें कि श्रेणी हमने COUNTA फ़ंक्शन . में चुना है E4:E100 . है . इसीलिए एक्सेल ड्रॉप-डाउन विकल्पों को अपडेट करेगा जब तक हम सेल . को जोड़ते या अपडेट करते हैं रेंज में E4:E100 ।
और पढ़ें: एक्सेल में वीबीए का उपयोग करके एक गतिशील डेटा सत्यापन सूची कैसे बनाएं
<एच3>2. OFFSET और COUNTIF फ़ंक्शंस के साथ एक्सेल में डायनेमिक ड्रॉप डाउन लिस्ट बनाने का तरीकाहम एक गतिशील ड्रॉप डाउन सूची भी बना सकते हैं एक्सेल . में ऑफ़सेट . का उपयोग करके और COUNTIF कार्य।
कदम:
➤ डेटा सत्यापन लाएं डायलॉग बॉक्स जैसे विधि-1 . स्रोत . में बॉक्स में निम्न सूत्र लिखें
=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,"<>")) ➜ किसी दिए गए संदर्भ की पंक्ति और स्तंभ के आधार पर एक श्रेणी लौटाता है।
➥ ऑफसेट($E$4,0,0,4,1)
आउटपुट ➜ {"एलेक्स";"मॉर्गन";"फॉल्कनर";"एलियट"}
स्पष्टीकरण: संदर्भ E4 . है . पंक्ति . के बाद से 0 . है और कॉलम 0 . है अंततः ऊंचाई . के साथ का 4 सेल, हमारे पास सेल से मान होंगे E4:E7
➤ ठीक . चुनें . 
➤ एक्सेल एक ड्रॉप-डाउन बनाएगा बॉक्स प्रत्येक सेल . में श्रेणी . के C4:C11 ।
यह जाँचने के लिए कि क्या यह एक डायनामिक ड्रॉप-डाउन बॉक्स . है या नहीं, मान लें कि विजेता इवेंट शूटिंग . के जेम्स . है . चूंकि जेम्स विजेताओं की सूची में नहीं है , चलिए उसका नाम जोड़ते हैं और देखते हैं कि क्या होता है।
जैसे ही हमने जेम्स . का नाम जोड़ा विजेताओं की सूची . में , एक्सेल ड्रॉप-डाउन विकल्पों में विकल्पों को स्वचालित रूप से अपडेट किया . तो ये ड्रॉप-डाउन सूचियां गतिशील हैं प्रकृति में .
➤ अब शेष विजेता . का चयन करें ।
नोट :याद रखें कि श्रेणी हमने COUNTIF फ़ंक्शन . में चुना है E4:E100 . है . इसीलिए एक्सेल ड्रॉप-डाउन विकल्पों को अपडेट करेगा जब तक हम सेल . को जोड़ते या अपडेट करते हैं रेंज में E4:E100 ।
<एच3>3. कार्यों के संयोजन का उपयोग करके नेस्टेड ड्रॉप-डाउन सूची कैसे बनाएं
इस अनुभाग में, हम एक स्मार्ट और अधिक उन्नत गतिशील ड्रॉप-डाउन सूची . बनाने पर विचार करेंगे , एक नेस्टेड एक। हम ऑफ़सेट . का उपयोग करेंगे , COUNTA , और MATCH एक साथ कार्य करता है। मुझे समझाएं कि हम क्या कर रहे हैं।
यह डेटासेट . है इस पद्धति के लिए जो विशेष उत्पादों की जानकारी का प्रतिनिधित्व करती है.. मूल रूप से, हम दो ड्रॉप-डाउन सूचियां बनाने जा रहे हैं कोशिकाओं F3 . में और F4 . F3 . में चयनित विकल्प के आधार पर , एक्सेल F4 . में विकल्पों को अपडेट करेगा . आइए इसे चरण दर चरण करते हैं।
STEP-1:F3 में ड्रॉप-डाउन सूची बनाना
➤ डेटा सत्यापन लाएं डायलॉग बॉक्स जैसे विधि-1 . स्रोत . में बॉक्स, सेल संदर्भ do करें , जो टेबल हेडर . हैं (सेल B3:D3 )।
एक्सेल एक ड्रॉप-डाउन सूची बनाएगा F3 . में ।
STEP-2:F4 में एक डायनामिक ड्रॉप-डाउन सूची बनाना
अब मैं एक और ड्रॉप-डाउन सूची बनाऊंगा F4 . में . ड्रॉप-डाउन सूची में विकल्प का F4 निर्भर करेगा कि हमने ड्रॉप-डाउन सूची में क्या चुना है का F3 . ऐसा करने के लिए,
➤ डेटा सत्यापन लाएं डायलॉग बॉक्स जैसे विधि-1 . स्रोत . में बॉक्स में निम्न सूत्र लिखें
=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) ➜ सेल मान F3 . की सापेक्ष स्थिति लौटाता है श्रेणी B3:D3 . से
आउटपुट:{1} ।
➥ OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1) ➜ एक श्रेणी . लौटाता है पंक्ति . पर आधारित और कॉलम दिए गए संदर्भ . का . ऊंचाई 10 . है . इसीलिए आउटपुट 10 . की एक सरणी होगी सेल मान संदर्भ से शुरू करते हुए ।
आउटपुट:{"सैम";"कुरन";"यान";"रोचेस्टर";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)
➥ ऑफसेट($B$3,1,0,4,1)
आउटपुट:{"सैम";"कुरन";"यैंक";"रोचेस्टर"}
स्पष्टीकरण: संदर्भ B3 . है . पंक्ति . के बाद से 1 . है और कॉलम 0 . है अंततः ऊंचाई . के साथ का 4 सेल, हमारे पास सेल से मान होंगे B4:B7 .
➤ ठीक . चुनें . 
एक्सेल एक गतिशील ड्रॉप-डाउन सूची बनाएगा F4 . में . विकल्प इस पर निर्भर करेगा कि आप F3 पर क्या चुनते हैं . उदाहरण के लिए, जब आप नाम . चुनते हैं F3 ड्रॉप-डाउन सूची . में , ड्रॉप-डाउन सूची F4 . में नाम कॉलम में उपलब्ध नाम दिखाएगा ।
इसी तरह, जब आप उत्पाद . चुनते हैं F3 ड्रॉप-डाउन सूची . में , ड्रॉप-डाउन सूची F4 . में उत्पाद कॉलम में उपलब्ध उत्पादों को दिखाएगा ।
अब अगर आप नाम . जोड़ते या अपडेट करते हैं , उत्पाद , या ब्रांड , एक्सेल ड्रॉप-डाउन सूची को अपडेट करेगा F4 . में . उदाहरण के लिए, मैंने एक नया नाम जोड़ा है रॉक नाम कॉलम . में और एक्सेल ड्रॉप-डाउन सूची . में नाम जोड़ा है . 
और पढ़ें: एक्सेल (8 तरीके) में डायनामिक टॉप 10 सूची कैसे बनाएं
कार्यपुस्तिका का अभ्यास करें
जैसा कि आप देख सकते हैं, एक गतिशील ड्रॉप डाउन सूची creating बनाना एक्सेल . में ऑफसेट फ़ंक्शन . के साथ वास्तव में पेचीदा है। इसलिए मेरा सुझाव है कि आप अधिक से अधिक अभ्यास करें। मैंने आपके लिए एक अभ्यास पत्रक संलग्न किया है।
निष्कर्ष
इस लेख में, मैंने 3 . का चित्रण किया है गतिशील ड्रॉप डाउन सूची create बनाने के तरीके एक्सेल . में ऑफसेट फ़ंक्शन . के साथ . मुझे उम्मीद है कि आपको यह लेख मददगार लगेगा। अंत में, यदि आपके पास कोई टिप्पणी है, तो कृपया उन्हें कमेंट बॉक्स में छोड़ दें।
संबंधित लेख
- Excel तालिका से गतिशील सूची बनाएं (3 आसान तरीके)
- मानदंड (एकल और एकाधिक मानदंड) के आधार पर एक्सेल में गतिशील सूची कैसे बनाएं