एमएस एक्सेल में, कभी-कभी हमें किसी सूची से कुछ मानदंडों या शर्तों के आधार पर मूल्यों को निर्धारित करने या खोजने की आवश्यकता हो सकती है। मान लें कि हमारे पास संबंधित व्यक्ति के नाम के साथ एक कार्य योजना है जो प्रत्येक कार्य की देखरेख करता है। और अब हमें दिए गए व्यक्ति के आधार पर सभी कार्यों के नामों को पंक्तियों में सूचीबद्ध करने की आवश्यकता है। इस तरह, एक्सेल सेल वैल्यू के आधार पर सूची को पॉप्युलेट करने के विभिन्न तरीके प्रदान करता है। इस लेख में, हम देखेंगे कि हम एक्सेल में सेल वैल्यू के आधार पर एक सूची कैसे बना सकते हैं।
प्रैक्टिस वर्कबुक डाउनलोड करें
एक्सेल में सेल वैल्यू के आधार पर सूची तैयार करने के 6 तरीके
1. सेल मान के आधार पर किसी सूची को स्वतः भरें
आइए प्रोजेक्ट वर्कर्स के नामों के साथ प्रोजेक्ट लिस्ट करें। प्रत्येक प्रोजेक्ट में, कार्यकर्ता के नाम इस प्रारूप में निर्दिष्ट किए जाते हैं “Project_Number_Name_Serial " इसलिए, हमारा काम परियोजना का उपयोग करके सभी कार्यकर्ताओं के नाम ढूंढना है।
चरण 1: सेल D17 . में निम्न सूत्र दर्ज करें और Enter press दबाएं
=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) फॉर्मूला स्पष्टीकरण
- फ़िल्टर . में फ़ंक्शन, B4:B16 यह वह सीमा है जहां से हम डेटा निकालेंगे।
- G5 इस सेल में इनपुट नाम देगा और नाम श्रेणी के साथ तुलना करेगा C4:C16
- फ़िल्टर फ़ंक्शन के बारे में अधिक जानने के लिए, आप इस लिंक को देख सकते हैं
चरण 2: अब सेल में कोई भी नाम टाइप करें G5 और Enter press दबाएं
3. पहले ड्रॉप डाउन में परिवर्तन ब्लॉक करें
मान लें कि हमारे पास विभिन्न खाद्य पदार्थों की कई सूचियां हैं। प्रत्येक सूची दूसरों से अलग होती है और विशिष्ट खाद्य पदार्थ मान्य सूची में होना चाहिए
एक अन्य वर्कशीट में, हम खाद्य पदार्थों को उनके प्रकार के अनुसार चुनेंगे।
हमारी चिंता यह है कि यदि हम कॉलम बी में किसी भी प्रकार के भोजन का चयन करते हैं तो कॉलम सी (आइटम) में केवल वही आइटम उपलब्ध होंगे जो उस खाद्य प्रकार या सूची के अंतर्गत हैं।
चरण 1: खाद्य पदार्थ सेल चुनें और डेटा सत्यापन खोलें
चरण 2: फिर स्रोत में निम्न सूत्र लिखें
=IF(B4="",Foods, INDIRECT("FakeRange"))
चरण 3: एक चेतावनी पॉप अप होगी। हां . पर क्लिक करें बटन
चरण 4: अब खाद्य प्रकार चुनें और फिर आइटम चुनें
चरण 5: एक बार जब आप खाद्य प्रकार और वस्तुओं में प्रवेश कर लेते हैं तो आप किसी भी खाद्य पदार्थ को नहीं बदल सकते हैं। इसलिए, मैचमेकिंग में त्रुटि की कोई संभावना नहीं है
4. मानदंड के आधार पर एक्सेल में एक अनूठी सूची बनाएं
अद्वितीय मान सूची खोजने के संदर्भ में, एक्सेल विभिन्न तरीके प्रदान करता है। आइए समान डेटासेट को डुप्लिकेट मानों के साथ विधि 2 के रूप में मानें। अब हमारा लक्ष्य फ़ार्मुलों का उपयोग करके अद्वितीय सूची का पता लगाना है।
चरण 1: सेल G6 . में निम्न सूत्र दर्ज करें
=UNIQUE(FILTER(B4:B22,C4:C22=G5))
- फ़िल्टर(B4:B22, C4:C22=G5) यह फ़ंक्शन विधि 2 के समान है। यह डेटासेट से सभी मिलान किए गए नामों को निकालता है। यदि कोई डुप्लीकेट मिलान हैं, तो फ़िल्टर फ़ंक्शन भी उन्हें गिनता है।
- फ़िल्टर . द्वारा लौटाए गए डुप्लिकेट मानों को निकालने के लिए फ़ंक्शन, हमने अद्वितीय . का उपयोग किया है समारोह। यह फ़ंक्शन मिलान किए गए डेटा से सभी डुप्लिकेट मानों को हटा देगा। समारोह के बारे में अधिक जानने के लिए, आप इस लिंक पर जा सकते हैं
चरण 2: अब कोई भी नाम enter दर्ज करें सेल में G5 और Enter press दबाएं
5. ऐरे फॉर्मूला का उपयोग करके एक कॉलम में मानदंडों को पूरा करने वाली सूचियों से सभी पंक्तियां निकालें
आइए उनके ID . के साथ एक उत्पाद डेटासेट बनाएं , ब्रांड , मॉडल , और इकाई मूल्य . अब हमारा काम उन पंक्तियों का पता लगाना है जहां हमारे दर्ज किए गए ब्रांड नाम के साथ ब्रांड नाम का मिलान किया जाएगा। कोशिकाओं में H5 और H7 ।
चरण 1 :सेल B19 . में निम्न सूत्र दर्ज करें और CTRL . दबाएं + शिफ्ट + दर्ज करें और सूत्र को पूरी तालिका में कॉपी करें।
=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 press दबाएं
6. एक्सेल में एक आश्रित ड्रॉप-डाउन सूची बनाएं
एमएस एक्सेल में, ड्रॉप-डाउन सूची एक उपयोगी विशेषता है जब हम डेटा एंट्री फॉर्म या एक्सेल डैशबोर्ड का प्रदर्शन कर रहे होते हैं।
यह एक सेल में ड्रॉप-डाउन के रूप में आइटम्स की एक सूची दिखाता है, और उपयोगकर्ता ड्रॉप-डाउन से चयन कर सकता है। यह तब उपयोगी हो सकता है जब आपके पास उन नामों, उत्पादों या क्षेत्रों की सूची हो, जिन्हें हमें अक्सर कक्षों के समूह में दर्ज करने की आवश्यकता होती है।
आइए मान लें कि हमारे पास तीन अलग-अलग खाद्य पदार्थ सूचियां हैं, अब हम उन सूचियों का उपयोग करके एक्सेल में एक निर्भर ड्रॉप-डाउन सूची तैयार करेंगे।
चरण 1: डेटा सत्यापन खोलें विकल्प
चरण 2: डेटा सत्यापन . में विंडो में, अनुमति दें . चुनें सूची . के रूप में और स्रोत . चुनें नीचे के रूप में
चरण 3: आपको एक ड्रॉप-डाउन . मिलेगा खाद्य प्रकार कॉलम में सूची बनाएं
चरण 4: अब सभी डेटासेट चुनें और चयन से बनाएं . पर क्लिक करें सूत्रों . के अंतर्गत विकल्प टैब
चरण 5: एक पॉप-अप दिखाई देगा। शीर्ष पंक्ति . पर क्लिक करें और फिर ठीक . दबाएं बटन
चरण 6: अब सेल पर जाएँ D14 और डेटा सत्यापन खोलें . सुनिश्चित करें कि अनुमति दें सूची . के रूप में सेट है और फिर निम्न सूत्र को स्रोत . में लिखें . अंत में, ठीक . दबाएं बटन
=INDIRECT(B14)
चरण 7: एक चेतावनी पॉप अप होगी। हां दबाएं बटन
चरण 8: अब पहली ड्रॉप डाउन सूची से किसी भी खाद्य प्रकार का चयन करें और अन्य ड्रॉप-डाउन सूची में सहयोगी आइटम सूची खोजें
चरण 8: अंतिम आउटपुट इस तरह होगा:
याद रखने योग्य बातें
| सामान्य त्रुटियाँ | जब वे दिखाई देते हैं |
|---|---|
| #VALUE! फ़िल्टर में त्रुटि | शामिल तर्क में सरणी तर्क के साथ संगत आयाम होना चाहिए, अन्यथा, FILTER #VALUE लौटाएगा! |
| #NA! त्रुटि | यदि सूत्र को डेटासेट से कोई डेटा नहीं मिलता है, तो यह इस त्रुटि को वापस कर देगा। हमें IFERROR . की मदद लेनी होगी इस त्रुटि को संभालने के लिए कार्य करें। |
| नामकरण सूची में है | सूची का नाम देने के संदर्भ में, हम किसी भी स्थान का उपयोग नहीं कर सकते हैं। यदि किसी नाम में जगह है, तो हम “_ . का उपयोग कर सकते हैं "। |
निष्कर्ष
एक्सेल में सेल वैल्यू के आधार पर लिस्ट को पॉप्युलेट करने के ये कुछ तरीके हैं। मैंने सभी विधियों को उनके संबंधित उदाहरणों के साथ दिखाया है लेकिन कई अन्य पुनरावृत्तियां भी हो सकती हैं। मैंने प्रयुक्त कार्यों के मूल सिद्धांतों पर भी चर्चा की है। यदि आपके पास इसे प्राप्त करने का कोई अन्य तरीका है, तो कृपया इसे बेझिझक हमारे साथ साझा करें।
आगे की रीडिंग
- डेटा को एक एक्सेल वर्कशीट से दूसरे में स्वचालित रूप से स्थानांतरित करें
- एक्सेल में किसी अन्य वर्कशीट से ऑटो कैसे पॉप्युलेट करें
- एक्सेल में डेटा के साथ अंतिम पंक्ति तक कैसे भरें (3 त्वरित तरीके)