हाल के एक लेख में, हमने एक्सेल फ़ंक्शन को बुलाया VLOOKUP और समझाया कि इसका उपयोग स्थानीय वर्कशीट में किसी सेल से डेटाबेस में जानकारी पुनर्प्राप्त करने के लिए कैसे किया जा सकता है। उस लेख में हमने उल्लेख किया कि VLOOKUP के लिए दो उपयोग थे, और उनमें से केवल एक क्वेरीिंग डेटाबेस से निपटा था। इस आलेख में, VLOOKUP श्रृंखला में दूसरा और अंतिम, हम इस अन्य, VLOOKUP फ़ंक्शन के लिए कम ज्ञात उपयोग की जांच करते हैं।
यदि आपने पहले से ऐसा नहीं किया है, तो कृपया पहले VLOOKUP आलेख को पढ़ें - यह आलेख मान लेगा कि उस आलेख में समझाए गए कई अवधारणाएं पहले ही पाठक को जानी जाती हैं।
डेटाबेस के साथ काम करते समय, VLOOKUP को "अद्वितीय पहचानकर्ता" पारित किया जाता है जो यह पहचानने के लिए कार्य करता है कि हम डेटाबेस में कौन सा डेटा रिकॉर्ड खोजना चाहते हैं (उदा। उत्पाद कोड या ग्राहक आईडी)। यह अद्वितीय पहचानकर्ता जरूर डेटाबेस में मौजूद है, अन्यथा VLOOKUP हमें एक त्रुटि देता है। इस आलेख में, हम VLOOKUP का उपयोग करने के तरीके की जांच करेंगे जहां पहचानकर्ता को डेटाबेस में मौजूद होने की आवश्यकता नहीं है। यह लगभग है जैसे VLOOKUP हम जिस डेटा को खोज रहे हैं उसे वापस करने के लिए "पर्याप्त पर्याप्त पर्याप्त पर्याप्त" दृष्टिकोण अपना सकते हैं। कुछ परिस्थितियों में, यह है ठीक ठीक हमें क्या चाहिये।
हम इस आलेख को वास्तविक दुनिया के उदाहरण के साथ चित्रित करेंगे - बिक्री आंकड़ों के सेट पर उत्पन्न आयोगों की गणना करने के लिए। हम एक बहुत ही सरल परिदृश्य से शुरू करेंगे, और फिर प्रगतिशील रूप से इसे अधिक जटिल बनाते हैं, जब तक समस्या का एकमात्र तर्कसंगत समाधान VLOOKUP का उपयोग नहीं करना है। हमारी कल्पित कंपनी में शुरुआती परिदृश्य इस प्रकार काम करता है: यदि कोई विक्रेता किसी दिए गए वर्ष में 30,000 डॉलर से अधिक की बिक्री करता है, तो वह बिक्री उन आय पर कमाई 30% है। अन्यथा उनका कमीशन केवल 20% है। अब तक यह एक बहुत ही सरल वर्कशीट है:
सेल बी 2 में इस वर्कशीट का एकमात्र दिलचस्प हिस्सा है - यह तय करने के लिए सूत्र कि किस कमीशन दर का उपयोग करना है: एक नीचे $ 30,000 की सीमा, या एक ऊपर दहलीज़। यह सूत्र Excel नामक फ़ंक्शन का उपयोग करता है अगर । उन पाठकों के लिए जो आईएफ से परिचित नहीं हैं, यह इस तरह काम करता है:
IF(condition,value if true,value if false)
जहां शर्त एक अभिव्यक्ति है जो या तो मूल्यांकन करती है सच या असत्य । उपरोक्त उदाहरण में, शर्त अभिव्यक्ति है बी 1, जिसे "बी 5 से कम बी 1" के रूप में पढ़ा जा सकता है, या, एक और तरीका डालें, "क्या कुल बिक्री थ्रेसहोल्ड से कम है"। अगर इस प्रश्न का उत्तर "हां" (सत्य) है, तो हम इसका उपयोग करते हैं यदि सही हो तो मूल्य समारोह का पैरामीटर, अर्थात् बी -6 इस मामले में - अगर कुल बिक्री की कमीशन दर थी नीचे दहलीज़। यदि प्रश्न का उत्तर "नहीं" (झूठा) है, तो हम इसका उपयोग करते हैं झूठा अगर मूल्य समारोह का पैरामीटर, अर्थात् बी 7 इस मामले में - अगर कुल बिक्री की कमीशन दर थी ऊपर दहलीज़।
जैसा कि आप देख सकते हैं, $ 20,000 की बिक्री कुल का उपयोग करके हमें सेल बी 2 में 20% की कमीशन दर मिलती है। अगर हम $ 40,000 का मूल्य दर्ज करते हैं, तो हमें एक अलग कमीशन दर मिलती है:
चलो इसे और अधिक जटिल बनाते हैं। आइए दूसरी थ्रेसहोल्ड पेश करें: यदि विक्रेता $ 40,000 से अधिक कमाता है, तो उनकी कमीशन दर 40% तक बढ़ जाती है:
वास्तविक दुनिया में समझने में काफी आसान है, लेकिन सेल बी 2 में हमारा सूत्र अधिक जटिल हो रहा है। यदि आप सूत्र पर बारीकी से देखते हैं, तो आप देखेंगे कि मूल IF फ़ंक्शन का तीसरा पैरामीटर ( झूठा अगर मूल्य) अब अपने पूरे अधिकार में एक संपूर्ण IF फ़ंक्शन है। इसे एक कहा जाता है नेस्टेड समारोह (एक समारोह के भीतर एक समारोह)। यह Excel में पूरी तरह से मान्य है (यह भी काम करता है!), लेकिन इसे पढ़ने और समझना मुश्किल है।
हम नट्स और बोल्ट में नहीं जा रहे हैं कि यह कैसे और क्यों काम करता है, न ही हम नेस्टेड कार्यों की बारीकियों की जांच करेंगे। यह सामान्य रूप से एक्सेल पर नहीं, VLOOKUP पर एक ट्यूटोरियल है।
वैसे भी, यह बदतर हो जाता है! जब हम निर्णय लेते हैं कि क्या वे $ 50,000 से अधिक कमाते हैं तो वे 50% कमीशन के हकदार हैं, और यदि वे $ 60,000 से अधिक कमाते हैं तो वे 60% कमीशन के हकदार हैं?
निश्चित रूप से है। बचाव के लिए VLOOKUP!
चलिए वर्कशीट को थोड़ा सा फिर से डिजाइन करें। हम सभी एक ही आंकड़े रखेंगे, लेकिन इसे एक नए तरीके से व्यवस्थित करेंगे, और भी बहुत कुछ तालिका का मार्ग:
एक पल लें और खुद को सत्यापित करें कि नया दर तालिका उपरोक्त थ्रेसहोल्ड की श्रृंखला के समान ही काम करता है।
संकल्पनात्मक रूप से, हम जो करने जा रहे हैं वह वलोडुक का उपयोग दर तालिका में विक्रेता की बिक्री कुल (बी 1 से) देखने और हमें संबंधित कमीशन दर पर लौटने के लिए है। ध्यान दें कि विक्रेता ने वास्तव में बिक्री की हो सकती है नहीं दर तालिका में पांच मूल्यों में से एक ($ 0, $ 30,000, $ 40,000, $ 50,000 या $ 60,000)। उन्होंने $ 34,988 की बिक्री की हो सकती है। यह ध्यान रखना महत्वपूर्ण है कि $ 34,988 करता है नहीं दर तालिका में दिखाई देते हैं। चलो देखते हैं कि क्या VLOOKUP हमारी समस्या को हल कर सकता है …
हम सेल बी 2 (वह स्थान जिसे हम अपना फॉर्मूला रखना चाहते हैं) का चयन करते हैं, और उसके बाद से VLOOKUP फ़ंक्शन डालें सूत्र टैब:
फंक्शन तर्क VLOOKUP के लिए बॉक्स प्रकट होता है। हम तर्कों (पैरामीटर) को एक-एक करके भरते हैं, जिसमें से शुरू होता है पता लगाने का मूल्य, जो इस मामले में, सेल बी 1 से बिक्री कुल है। हम कर्सर को अंदर रखते हैं पता लगाने का मूल्य फ़ील्ड और फिर सेल बी 1 पर एक बार क्लिक करें:
इसके बाद हमें इस डेटा को देखने के लिए कौन सी तालिका VLOOKUP को निर्दिष्ट करने की आवश्यकता है। इस उदाहरण में, यह दर तालिका है। हम कर्सर को अंदर रखते हैं तालिका सरणी फ़ील्ड, और फिर संपूर्ण दर तालिका को हाइलाइट करें - शीर्षक को छोड़कर:
इसके बाद हमें यह निर्दिष्ट करना होगा कि तालिका में कौन से कॉलम में वह जानकारी है जो हम चाहते हैं कि हमारे फॉर्मूला हमारे पास वापस आएं। इस मामले में हम कमीशन दर चाहते हैं, जो तालिका में दूसरे कॉलम में पाई जाती है, इसलिए हम एक दर्ज करते हैं 2 में Col_index_num खेत:
अंत में हम एक मूल्य दर्ज करते हैं रेंज देखना खेत।
महत्वपूर्ण: यह इस क्षेत्र का उपयोग है जो VLOOKUP का उपयोग करने के दो तरीकों को अलग करता है। डेटाबेस के साथ VLOOKUP का उपयोग करने के लिए, यह अंतिम पैरामीटर, रेंज देखना, हमेशा सेट किया जाना चाहिए असत्य, लेकिन इस अन्य उपयोग के साथ VLOOKUP, हमें या तो इसे खाली छोड़ देना चाहिए या उसका मूल्य दर्ज करना होगा सच । VLOOKUP का उपयोग करते समय, यह महत्वपूर्ण है कि आप इस अंतिम पैरामीटर के लिए सही विकल्प बनाते हैं।
स्पष्ट होने के लिए, हम एक मूल्य दर्ज करेंगे सच में रेंज देखना खेत। इसे खाली छोड़ना भी ठीक होगा, क्योंकि यह डिफ़ॉल्ट मान है:
हमने सभी मानकों को पूरा कर लिया है। अब हम क्लिक करें ठीक बटन, और एक्सेल हमारे लिए हमारे VLOOKUP सूत्र बनाता है:
निष्कर्ष
VLOOKUP के "डेटाबेस" संस्करण में, जहां रेंज देखना पैरामीटर है असत्य, पहले पैरामीटर में पारित मूल्य (पता लगाने का मूल्य) जरूर डेटाबेस में उपस्थित रहें। दूसरे शब्दों में, हम एक सटीक मिलान की तलाश में हैं।
लेकिन VLOOKUP के इस अन्य उपयोग में, हम जरूरी नहीं कि एक सटीक मैच की तलाश कर रहे हों। इस मामले में, "पर्याप्त पास पर्याप्त है"। लेकिन "पर्याप्त पास" से हमारा क्या मतलब है? आइए उदाहरण का उपयोग करें: $ 34,988 की कुल बिक्री पर कमीशन दर की खोज करते समय, हमारे VLOOKUP फॉर्मूला हमें 30% का मान देगा, जो सही उत्तर है। तालिका में पंक्ति को 30% युक्त क्यों चुना गया? वास्तव में, इस मामले में "पर्याप्त पास" का मतलब क्या है? चलो सटीक हो:
When Range_lookup is set to TRUE (or omitted), VLOOKUP will look in column 1 and match the highest value that is not greater than the Lookup_value parameter.
यह भी ध्यान रखना महत्वपूर्ण है कि इस प्रणाली के लिए काम करने के लिए, तालिका को स्तंभ 1 पर आरोही क्रम में क्रमबद्ध किया जाना चाहिए!
यदि आप VLOOKUP के साथ अभ्यास करना चाहते हैं, तो इस आलेख में चित्रित नमूना फ़ाइल यहां से डाउनलोड की जा सकती है।
