एकाधिक मानदंडों के साथ एक एक्सेल लुकअप फॉर्मूला कैसे बनाएं

Excel में एक सरणी सूत्र का उपयोग करके हम एक लुकअप फॉर्मूला बना सकते हैं जो किसी डेटाबेस या डेटा की तालिका में जानकारी खोजने के लिए एकाधिक मानदंडों का उपयोग करता है। सरणी सूत्र में INDEX फ़ंक्शन के अंदर MATCH फ़ंक्शन को घोंसले करना शामिल है।

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

08 का 08

डेटा के साथ अपनी एक्सेल कार्यपुस्तिका तैयार करें

इस ट्यूटोरियल में चरणों का पालन करने के लिए, आपको उपरोक्त छवि में दिखाए गए अनुसार, निम्न कक्षों में नमूना डेटा दर्ज करके प्रारंभ करना होगा। इस ट्यूटोरियल के दौरान बनाए गए सरणी सूत्र को समायोजित करने के लिए पंक्तियों 3 और 4 को खाली छोड़ दिया गया है।

• कोशिकाओं में डेटा की शीर्ष सीमा दर्ज करें डी 1 सेवा मेरे F2
• कोशिकाओं में दूसरी रेंज दर्ज करें D5 सेवा मेरे F11

ट्यूटोरियल में छवि में स्वरूपित स्वरूपण शामिल नहीं है, लेकिन यह इस बात को प्रभावित नहीं करेगा कि लुकअप फॉर्मूला कैसे काम करता है। ऊपर दिए गए लोगों के समान प्रारूपण विकल्पों पर जानकारी इस मूल एक्सेल स्वरूपण ट्यूटोरियल में उपलब्ध है।

08 में से 02

Excel में एक INDEX फ़ंक्शन बनाएं

सूचकांक फ़ंक्शन एक्सेल में कुछ में से एक है जिसमें कई रूप हैं। समारोह में एक है ऐरे फॉर्म और ए संदर्भ प्रपत्र। ऐरे फॉर्म डेटा या डेटाबेस की तालिका से वास्तविक डेटा देता है, जबकि संदर्भ फ़ॉर्म आपको तालिका में डेटा संदर्भ या डेटा का स्थान देता है।

इस ट्यूटोरियल में, हम ऐरे फॉर्म का उपयोग करेंगे क्योंकि हम अपने डेटाबेस में इस सप्लायर के सेल संदर्भ के बजाय टाइटेनियम विजेट्स के लिए आपूर्तिकर्ता का नाम जानना चाहते हैं। अपना INDEX फ़ंक्शन बनाने के लिए इन चरणों का पालन करें:

1. सेल पर क्लिक करें F3 इसे सक्रिय सेल बनाने के लिए - यह वह जगह है जहां हम नेस्टेड फ़ंक्शन दर्ज करेंगे।
2. पर क्लिक करें सूत्र रिबन मेनू का टैब।
3. चुनें लुकअप और संदर्भ फ़ंक्शन ड्रॉप डाउन खोलने के लिए रिबन बार से।
4. पर क्लिक करें सूचकांक सूची में लाने के लिए तर्क का चयन करें संवाद बॉक्स।
5. चुनना सरणी, row_num, col_num संवाद बॉक्स में विकल्प।
6. क्लिक करें ठीक INDEX फ़ंक्शन संवाद बॉक्स खोलने के लिए; यह एक्सेल में फॉर्मूला बिल्डर खोल देगा।
7. में फॉर्मूला बिल्डर, पर क्लिक करें सरणी लाइन।
8. कोशिकाओं को हाइलाइट करें D6 सेवा मेरे F11, बिल्डर में सीमा दर्ज करने के लिए वर्कशीट में क्लिक करके और खींचकर।

एक्सेल के पुराने संस्करणों में फॉर्मूला बिल्डर द्वारा प्रतिस्थापित किया जाएगा फंक्शन तर्क खिड़की। विंडो में इस ट्यूटोरियल में उल्लिखित चरणों को लागू करें।

08 का 03

नेस्टेड मैच फ़ंक्शन प्रारंभ करें

जब किसी अन्य फ़ंक्शन को किसी अन्य के अंदर घोंसले करना होता है तो दूसरे तर्कों के फॉर्मूला बिल्डर को खोलना संभव नहीं है o आवश्यक तर्क दर्ज करें। नेस्टेड फ़ंक्शन को पहले फ़ंक्शन में क्रमबद्ध में से एक के रूप में टाइप किया जाना चाहिए।

मैन्युअल रूप से फ़ंक्शंस दर्ज करते समय, फ़ंक्शन के तर्क एक दूसरे से अल्पविराम से अलग होते हैं।

नेस्टेड MATCH फ़ंक्शन में प्रवेश करने का पहला चरण दर्ज करना है पता लगाने का मूल्य तर्क। Lookup_value उस खोज शब्द के लिए स्थान या सेल संदर्भ होगा जिसे हम डेटाबेस में मिलान करना चाहते हैं।

आम तौर पर Lookup_value केवल एक खोज मानदंड या शब्द स्वीकार करता है। एकाधिक मानदंडों को खोजने के लिए, हमें Lookup_value का विस्तार करना होगा; टीएम्पर्सेंड प्रतीक का उपयोग करके दो या दो से अधिक सेल संदर्भों को जोड़कर या जोड़कर किया जाता है - &.

1. में फॉर्मूला बिल्डर, पर क्लिक करें ROW_NUM लाइन।
2. फ़ंक्शन का नाम टाइप करें मैच एक खुले दौर के ब्रैकेट के बाद।
3. सेल पर क्लिक करें डी 3 संवाद बॉक्स में उस सेल संदर्भ दर्ज करने के लिए।
4. सेल संदर्भ के बाद एक ampersand टाइप करें डी 3 एक दूसरा सेल संदर्भ जोड़ने के लिए।
5. सेल पर क्लिक करें E3 इस दूसरे सेल संदर्भ में प्रवेश करने के लिए।
6. सेल संदर्भ के बाद एक अल्पविराम टाइप करें E3 MATCH फ़ंक्शन की प्रविष्टि को पूरा करने के लिए पता लगाने का मूल्य तर्क।

ट्यूटोरियल के अंतिम चरण में, हमारे Lookup_values वर्कशीट के कक्ष डी 3 और ई 3 में प्रवेश किया जाएगा।

08 का 04

नेस्टेड मैच फ़ंक्शन को पूरा करें

यह कदम जोड़ना शामिल है देखें_श्रेणी नेस्टेड के लिए तर्क मैच समारोह। Lookup_array उन कक्षों की श्रेणी है जो MATCH फ़ंक्शन ट्यूटोरियल के पिछले चरण में लुकअप_वल्यू तर्क को खोजने के लिए खोज करेगा।

चूंकि हमने लुकअप_एरेरे तर्क में दो खोज फ़ील्ड की पहचान की है, इसलिए हमें लुकअप_एरे के लिए भी ऐसा ही करना चाहिए। MATCH फ़ंक्शन केवल निर्दिष्ट प्रत्येक शब्द के लिए एक सरणी खोजता है, इस प्रकार एकाधिक सरणी दर्ज करने के लिए हम फिर से एरे को जोड़कर एम्पर्सेंड का उपयोग करते हैं।

पिछले चरण में कॉमा दर्ज करने के बाद इन चरणों को दर्ज किया जाना है ROW_NUM में लाइन सूचकांक समारोह।

1. पर क्लिक करें ROW_NUM वर्तमान प्रविष्टि के अंत में सम्मिलन बिंदु रखने के लिए अल्पविराम के बाद लाइन।
2. कोशिकाओं को हाइलाइट करें D6 सेवा मेरे D11 वर्कशीट में श्रेणी दर्ज करने के लिए - यह पहला सरणी है जिसे फ़ंक्शन खोजना है।
3. सेल संदर्भों के बाद एक एम्पर्सेंड टाइप करें D6: D11 क्योंकि हम फ़ंक्शन को दो सरणी खोजना चाहते हैं।
4. कोशिकाओं को हाइलाइट करें E6 सेवा मेरे E11 वर्कशीट में श्रेणी दर्ज करने के लिए - यह दूसरी सरणी है जिसे फ़ंक्शन खोजना है।
5. सेल संदर्भ के बाद एक अल्पविराम टाइप करें E3 की प्रविष्टि को पूरा करने के लिए मैच समारोह के देखें_श्रेणी तर्क।
6. छुट्टी सूचकांक फ़ोरम में अगले चरण के लिए फ़ंक्शन डायलॉग बॉक्स खुलता है।

05 का 08

MATCH प्रकार तर्क जोड़ें

MATCH फ़ंक्शन का तीसरा और अंतिम तर्क है मिलान के प्रकार तर्क; यह एक्सेल को कैसे मेल खाता है बताता है पता लगाने का मूल्य Lookup_array में मानों के साथ - उपलब्ध विकल्प 1, 0, या -1 हैं।

यह तर्क वैकल्पिक है। यदि यह छोड़ा जाता है तो फ़ंक्शन 1 का डिफ़ॉल्ट मान उपयोग करता है।

• अगर Match_type = 1 या छोड़ा गया है: MATCH को सबसे बड़ा मान मिलता है जो Lookup_value से कम या उसके बराबर है। Lookup_array डेटा आरोही क्रम में क्रमबद्ध किया जाना चाहिए।
• अगर Match_type = 0: MATCH को पहला मान मिलता है जो Lookup_value के बराबर है। Lookup_array डेटा किसी भी क्रम में सॉर्ट किया जा सकता है।
• अगर Match_type = -1: MATCH को सबसे छोटा मान मिलता है जो Lookup_value से अधिक या बराबर है। लुकअप_एरे डेटा को अवरोही क्रम में क्रमबद्ध किया जाना चाहिए।

पिछले चरण में कॉमा दर्ज करने के बाद इन चरणों को दर्ज किया जाना है ROW_NUM में लाइन सूचकांक समारोह।

1. पर अल्पविराम के बाद ROW_NUM लाइन, एक शून्य टाइप करें - 0 - चूंकि हम नेस्टेड फ़ंक्शन को कोशिकाओं में दर्ज शर्तों के सटीक मिलान वापस करना चाहते हैं डी 3 तथा E3.
2. एक समापन दौर ब्रैकेट टाइप करें - ) - पूरा करने के लिए मैच समारोह।
3. छुट्टी फॉर्मूला बिल्डर ट्यूटोरियल में अगले चरण के लिए खुला है।

08 का 06

INDEX फ़ंक्शन को समाप्त करना

अब वह मैच कार्य किया जाता है हम फॉर्मूला बिल्डर की तीसरी पंक्ति में चले जाएंगे और इसके लिए अंतिम तर्क दर्ज करेंगे सूचकांक समारोह। यह तीसरा और अंतिम तर्क है Column_num तर्क जो Excel को श्रेणी में स्तंभ संख्या बताता है D6 सेवा मेरे F11; यह वह जगह है जहां हमें वह जानकारी मिलेगी जिसे हम फ़ंक्शन द्वारा वापस करना चाहते हैं। इस मामले में, एक आपूर्तिकर्ता के लिए टाइटेनियम विजेट्स.

1. पर क्लिक करें Column_num लाइन।
2. नंबर तीन दर्ज करें - 3 - इस लाइन पर हम सीमा के तीसरे कॉलम में डेटा की तलाश में हैं D6 सेवा मेरे F11.
3. फिर, छोड़ दो फॉर्मूला बिल्डर ट्यूटोरियल में अगले चरण के लिए खुला है।

08 का 07

ऐरे फॉर्मूला बनाना

बंद करने से पहले फॉर्मूला बिल्डर, हमें अपने नेस्टेड फ़ंक्शन को सरणी सूत्र में बदलना होगा; यही वह है जो हमारे फ़ंक्शन को डेटा की तालिका में एकाधिक शर्तों की खोज करने की अनुमति देता है। इस ट्यूटोरियल में, हम दो शर्तों से मेल खाते हैं: विजेट कॉलम 1 और से टाइटेनियम कॉलम 2 से।

एक्सेल में एक सरणी सूत्र बनाना, दबाकर किया जाता है CTRL, खिसक जाना, तथा दर्ज एक ही समय में कीबोर्ड पर कुंजी। एक बार दबाए जाने पर, फ़ंक्शन घुंघराले ब्रेसिज़ से घिरा होगा, यह दर्शाता है कि फ़ंक्शन अब एक सरणी है।

1. बंद करो फॉर्मूला बिल्डर क्लिक करके किया हुआ बटन।
2. अगला, सेल का चयन करें F4 और दबाएं दर्ज सूत्र देखने के लिए कुंजी।
3. सूत्र को एक सरणी में रूपांतरित करने के लिए, साथ ही दबाएं CTRL + SHIFT + ENTER अपने कीबोर्ड पर
4. अगर सही ढंग से किया जाता है # N / A त्रुटि सेल F3 में दिखाई देगी - वह सेल जहां हमने फ़ंक्शन दर्ज किया था।
5. # N / A सेल में त्रुटि प्रकट होती है F3 क्योंकि कोशिकाएं डी 3 तथा E3 खाली हैं डी 3 और ई 3 वे कक्ष हैं जहां हमने कार्य को खोजने के लिए कहा था Lookup_values। एक बार इन दो कोशिकाओं में डेटा जोड़ा जाता है, तो त्रुटि डेटाबेस से जानकारी द्वारा प्रतिस्थापित किया जाएगा।

08 का 08

खोज मानदंड जोड़ना

अंत में, हम खोज कार्य को हमारे वर्कशीट में जोड़ देंगे। जैसा कि पिछले चरण में बताया गया है, हम शर्तों से मेल खाते हैं विजेट कॉलम 1 और से टाइटेनियम कॉलम 2 से। यदि, और केवल तभी, हमारे सूत्र को डेटाबेस में उचित कॉलम में दोनों शर्तों के लिए एक मिलान मिलता है, तो यह मान को तीसरे कॉलम से वापस कर देगा।

1. सेल पर क्लिक करें डी 3.
2. प्रकार विजेट और दबाएं दर्ज कीबोर्ड पर कुंजी।
3. सेल पर क्लिक करें E3.
4. प्रकार टाइटेनियम और दबाएं दर्ज कीबोर्ड पर कुंजी।
5. आपूर्तिकर्ता का नाम विजेट इंक सेल F3 में दिखाई देना चाहिए - फ़ंक्शन का स्थान क्योंकि यह एकमात्र सप्लायर सूचीबद्ध है जो टाइटेनियम विजेट बेचता है।
6. जब आप सेल F3 पर क्लिक करते हैं तो वर्कशीट के ऊपर सूत्र बार में पूरा फ़ंक्शन दिखाई देता है।

{= INDEX (डी 6: एफ 11, MATCH (डी 3 और ई 3, डी 6: डी 11 और ई 6: ई 11, 0), 3)}

हमारे उदाहरण में, टाइटेनियम विजेट्स के लिए केवल एक सप्लायर था। यदि एक से अधिक आपूर्तिकर्ता थे, तो डेटाबेस में पहले सूचीबद्ध सप्लायर फ़ंक्शन द्वारा वापस कर दिया जाता है।