MS एक्सेल में, कभी-कभी हमें एक सूची से मानदंड या शर्त के आधार पर मान निर्धारित करने या खोजने की आवश्यकता हो सकती है। मान लें हमारे पास एक कार्य योजना है जिसमें प्रत्येक कार्य की देखरेख करने वाले संबंधित व्यक्ति का नाम है। और अब हमें दिए गए व्यक्ति के आधार पर सभी कार्यों के नाम पंक्तियों में सूचीबद्ध करने की आवश्यकता है। इस तरह, एक्सेल सेल मान के आधार पर सूची को पॉप्युलेट करने के विभिन्न तरीके प्रदान करता है। इस लेख में, हम देखेंगे कि हम एक्सेल में सेल मान के आधार पर सूची कैसे पॉप्युलेट कर सकते हैं।
अभ्यास वर्कबुक डाउनलोड करें
एक्सेल में सेल मान के आधार पर सूची पॉप्युलेट करने के 6 तरीके
1. सेल मान के आधार पर सूची ऑटोफिल करें
आइए प्रोजेक्ट वर्कर्स के नाम के साथ सूचियों को प्रोजेक्ट करें। प्रत्येक प्रोजेक्ट में, वर्कर्स के नाम इस प्रारूप में असाइन किए गए हैं "प्रोजेक्ट_नंबर_नाम_सीरियल (Project_Number_Name_Serial)"। तो, हमारा कार्य प्रोजेक्ट का उपयोग करके सभी वर्कर्स के नाम ढूंढना है।
चरण 1: सेल D17 में निम्न सूत्र दर्ज करें और एंटर (Enter) दबाएं
=IFERROR(INDEX($B$3:$D$11,ROW(B2:D11),MATCH($C$16,$B$3:$D$3,0)),"")सूत्र व्याख्या
- यहां MATCH($C$16,$B$3:$D$3,0) यह भाग दर्ज किए गए प्रोजेक्ट नाम का डेटासेट के साथ मिलान कर रहा है और इसे केवल सटीक मिलान माना जाता है।
- ROW(B2:D11) डेटासेट की पंक्तियों की संख्या गिन रहा है।
- INDEX($B$3:$D$11, ROW(B2:D11), MATCH($C$16,$B$3:$D$3,0)) सूत्र का यह भाग मिलान किए गए प्रोजेक्ट वर्कर्स के नाम ढूंढ रहा है। यदि डेटा दिए गए डेटासेट में नहीं मिलता है तो यह #NA त्रुटि फेंकेगा।
- अंत में, IFERROR किसी भी प्रकार की त्रुटि को संभालने के लिए।
चरण 2: अब ड्रॉप-डाउन सूची से कोई भी प्रोजेक्ट नाम चुनें
चरण 3: सभी वर्कर्स के नाम दिखाए जाएंगे
2. विशिष्ट सेल मान के साथ फॉर्मूला का उपयोग करके पंक्तियां पॉप्युलेट करें
आइए देखें कि हम एक अलग दृष्टिकोण में वर्कर का नाम कैसे खोज सकते हैं। डेटासेट में, एक व्यक्ति को एकाधिक प्रोजेक्ट असाइन किए जा सकते हैं। अब हमारा कार्य वर्कर के नाम का उपयोग करके प्रोजेक्ट नाम ढूंढना है। यहां मुख्य डेटासेट इस तरह होगा:
चरण 1: सेल G6 में निम्न सूत्र दर्ज करें और ENTER कुंजी दबाएं
=FILTER(B4:B16, G5=C4:C16)
सूत्र व्याख्या
- FILTER फंक्शन में, B4:B16 यह वह रेंज है जहां से हम डेटा निकालेंगे।
- G5 इस सेल में इनपुट नाम देगा और नाम रेंज C4:C16 के साथ तुलना करेगा
- FILTER फंक्शन के बारे में अधिक जानने के लिए, आप इस लिंक की जांच कर सकते हैं
चरण 2: अब सेल G5 में कोई भी नाम टाइप करें और Enter दबाएं
3. पहले ड्रॉप डाउन में परिवर्तन ब्लॉक करें
मान लीजिए हमारे पास विभिन्न खाद्य पदार्थों की कई सूचियां हैं। प्रत्येक सूची दूसरों से अलग है और विशिष्ट खाद्य पदार्थ मान्य सूची पर होना चाहिए
दूसरी वर्कशीट में, हम खाद्य पदार्थों को उनके प्रकारों के अनुसार चुनेंगे।
हमारी चिंता यह है कि यदि हम कॉलम B में कोई खाद्य प्रकार चुनते हैं तो कॉलम C (आइटम) में केवल वही आइटम उपलब्ध होंगे जो उस खाद्य प्रकार या सूची के अंतर्गत हैं।
चरण 1: खाद्य आइटम सेल चुनें और डेटा वैलिडेशन (Data Validation) खोलें
चरण 2: फिर स्रोत में निम्न सूत्र लिखें
=IF(B4="",Foods, INDIRECT("FakeRange"))
चरण 3: एक चेतावनी पॉप अप होगी। हां (Yes) बटन पर क्लिक करें
चरण 4: अब खाद्य प्रकार चुनें और फिर आइटम चुनें
चरण 5: एक बार जब आप खाद्य प्रकार और आइटम दर्ज कर लेते हैं तो आप कोई भी खाद्य आइटम नहीं बदल सकते। इसलिए, गलत मिलान की कोई संभावना नहीं है
4. मानदंडों के आधार पर एक्सेल में अद्वितीय सूची बनाएं
अद्वितीय मान सूची खोजने के संदर्भ में, एक्सेल विभिन्न तरीके प्रदान करता है। आइए डुप्लिकेट मानों के साथ विधि 2 के समान डेटासेट पर विचार करें। अब हमारा लक्ष्य सूत्रों का उपयोग करके अद्वितीय सूची ढूंढना है।
चरण 1: सेल G6 में निम्न सूत्र दर्ज करें
=UNIQUE(FILTER(B4:B22,C4:C22=G5))
- FILTER(B4:B22, C4:C22=G5) यह फंक्शन विधि 2 के समान है। यह डेटासेट से सभी मिलान नाम निकालता है। यदि कोई डुप्लिकेट मिलान हैं, तो FILTER फंक्शन उन्हें भी गिनता है।
- FILTER फंक्शन द्वारा लौटाए गए डुप्लिकेट मानों को हटाने के लिए, हमने UNIQUE फंक्शन का उपयोग किया है। यह फंक्शन मिलान डेटा से सभी डुप्लिकेट मान हटा देगा। फंक्शन के बारे में अधिक जानने के लिए, आप इस लिंक पर जा सकते हैं
चरण 2: अब सेल G5 में कोई भी नाम दर्ज करें और Enter दबाएं
5. एक कॉलम में मानदंडों को पूरा करने वाली सूचियों से सभी पंक्तियां निकालें ऐरे फॉर्मूला का उपयोग करके
आइए उनके ID, ब्रांड (Brand), मॉडल (Model), और यूनिट प्राइस (Unit Price) के साथ एक उत्पाद डेटासेट लें। अब हमारा कार्य उन पंक्तियों को ढूंढना है जहां ब्रांड नाम हमारे दर्ज ब्रांड नामों (Brand Names) से मेल खाएगा जो सेल H5 और H7 में हैं।
चरण 1: सेल B19 में निम्न सूत्र दर्ज करें और CTRL + SHIFT + ENTER दबाएं और पूरे टेबल में सूत्र कॉपी करें।
=INDEX($B$4:$E$15, SMALL(IF(COUNTIF($H$5:$H$7,$C$4:$C$15), MATCH(ROW($B$4:$E$15), ROW($B$4:$E$15)), ""), ROWS(B19:$B$19)), COLUMNS($B$3:B3
चरण 2: अब सेल H5 और H7 में नाम दर्ज करें और Enter दबाएं
6. एक्सेल में डिपेंडेंट ड्रॉप-डाउन सूची बनाएं
MS एक्सेल में, ड्रॉप-डाउन सूची एक उपयोगी सुविधा है जब हम डेटा एंट्री फॉर्म या एक्सेल डैशबोर्ड बना रहे होते हैं।
यह एक सेल में ड्रॉप-डाउन के रूप में आइटम की सूची दिखाता है, और उपयोगकर्ता ड्रॉप-डाउन से चुन सकता है। यह तब उपयोगी हो सकता है जब आपके पास नामों, उत्पादों, या क्षेत्रों की सूची हो जिन्हें हम अक्सर सेल के एक सेट में दर्ज करने की आवश्यकता होती है।
मान लें हमारे पास तीन अलग-अलग खाद्य पदार्थ सूचियां हैं, अब हम उन सूचियों का उपयोग करके एक्सेल में डिपेंडेंट ड्रॉप-डाउन सूची बनाएंगे।
चरण 1: डेटा वैलिडेशन (Data Validation) विकल्प खोलें
चरण 2: डेटा वैलिडेशन (Data Validation) विंडो में, अनुमति दें (Allow) को सूची (List) के रूप में चुनें और स्रोत (Source) को नीचे के रूप में चुनें
चरण 3: आप खाद्य प्रकार कॉलम में एक ड्रॉप-डाउन (Drop-Down) सूची पाएंगे
चरण 4: अब सभी डेटासेट चुनें और फॉर्मूला (Formulas) टैब के तहत चयन से बनाएं (Create from Selection) विकल्प पर क्लिक करें
चरण 5: एक पॉप-अप दिखाई देगा। शीर्ष पंक्ति (Top row) पर क्लिक करें और फिर ओके (Ok) बटन दबाएं
चरण 6: अब सेल D14 पर जाएं और डेटा वैलिडेशन (Data Validation) खोलें। सुनिश्चित करें कि अनुमति दें (Allow) सूची (List) के रूप में सेट है और फिर स्रोत (Source) में निम्न सूत्र लिखें। अंत में, ओके (OK) बटन दबाएं
=INDIRECT(B14)
चरण 7: एक चेतावनी पॉप अप होगी। हां (Yes) बटन दबाएं
चरण 8: अब पहले ड्रॉप डाउन सूची से कोई भी खाद्य प्रकार चुनें और दूसरे ड्रॉप-डाउन सूची में संबंधित आइटम सूची ढूंढें
चरण 8: अंतिम आउटपुट इस तरह होगा:
याद रखने योग्य बातें
| सामान्य त्रुटियां | वे कब दिखाई देती हैं |
|---|---|
| FILTER में #VALUE! त्रुटि | इनक्लूड आर्गुमेंट में ऐरे आर्गुमेंट के साथ संगत आयाम होने चाहिए, अन्यथा FILTER #VALUE! लौटाएगा |
| #NA! त्रुटि | यदि सूत्र डेटासेट से कोई डेटा नहीं ढूंढता है, तो यह यह त्रुटि लौटाएगा। हमें इस त्रुटि को संभालने के लिए IFERROR फंक्शन की मदद लेनी होगी। |
| सूची में नामकरण | सूची का नाम देने के संदर्भ में, हम किसी भी स्पेस का उपयोग नहीं कर सकते। यदि नाम में स्पेस है, तो हम "_" का उपयोग कर सकते हैं। |
निष्कर्ष
ये एक्सेल में सेल मान के आधार पर सूची पॉप्युलेट करने के कुछ तरीके हैं। मैंने सभी विधियों को उनके संबंधित उदाहरणों के साथ दिखाया है लेकिन कई अन्य पुनरावृत्तियां हो सकती हैं। मैंने उपयोग किए गए फंक्शन के मूल सिद्धांतों पर भी चर्चा की है। यदि आपके पास इसे प्राप्त करने की कोई अन्य विधि है, तो कृपया बेझिझक इसे हमारे साथ साझा करें।
आगे पढ़ें
- एक एक्सेल वर्कशीट से दूसरी में डेटा स्वचालित रूप से स्थानांतरित करें
- एक्सेल में दूसरी वर्कशीट से ऑटो पॉप्युलेट कैसे करें
- एक्सेल में डेटा के साथ अंतिम पंक्ति तक कैसे भरें (3 त्वरित तरीके)
