Excel में VLOOKUP, भाग 2 डेटाबेस के बिना VLOOKUP का उपयोग करना

हाल के एक लेख में, हमने एक्सेल फ़ंक्शन को बुलाया VLOOKUP और समझाया कि इसका उपयोग किसी डेटाबेस से स्थानीय कार्यपत्रक में सेल में जानकारी प्राप्त करने के लिए कैसे किया जा सकता है। उस लेख में हमने उल्लेख किया है कि VLOOKUP के लिए दो उपयोग थे, और उनमें से केवल एक ही डेटाबेस से क्वेरी करता था। इस आलेख में, VLOOKUP श्रृंखला में दूसरा और अंतिम, हम इस दूसरे की जांच करते हैं, VLOOKUP फ़ंक्शन के लिए कम ज्ञात उपयोग.

यदि आपने पहले से ऐसा नहीं किया है, तो कृपया पहला VLOOKUP लेख पढ़ें - यह लेख यह मान लेगा कि उस लेख में बताई गई कई अवधारणाएँ पहले से ही पाठक को ज्ञात हैं.

डेटाबेस के साथ काम करते समय, VLOOKUP को एक "विशिष्ट पहचानकर्ता" पास किया जाता है जो यह पहचानने के लिए कार्य करता है कि हम डेटाबेस में कौन सा डेटा रिकॉर्ड ढूंढना चाहते हैं (उदाहरण के लिए उत्पाद कोड या ग्राहक आईडी)। यह अद्वितीय पहचानकर्ता जरूर डेटाबेस में मौजूद है, अन्यथा VLOOKUP हमें एक त्रुटि देता है। इस लेख में, हम VLOOKUP का उपयोग करने के एक तरीके की जांच करेंगे जहां पहचानकर्ता को डेटाबेस में मौजूद होने की आवश्यकता नहीं है। यह लगभग वैसा ही है जैसे वीएलबुक एक "निकट पर्याप्त अच्छा पर्याप्त है" को अपनाए हुए डेटा को वापस लेने के दृष्टिकोण को अपना सकता है। कुछ परिस्थितियों में, यह है ठीक ठीक हमें क्या चाहिये.

हम इस लेख को एक वास्तविक दुनिया उदाहरण के साथ चित्रित करेंगे - जो बिक्री के आंकड़ों के सेट पर उत्पन्न होने वाले कमीशन की गणना करते हैं। हम एक बहुत ही सरल परिदृश्य के साथ शुरुआत करेंगे, और फिर उत्तरोत्तर इसे और अधिक जटिल बना देंगे, जब तक समस्या का एकमात्र तर्कसंगत समाधान एचएमयूएलयूपी का उपयोग नहीं करना है। हमारी काल्पनिक कंपनी में प्रारंभिक परिदृश्य इस तरह से काम करता है: यदि एक विक्रेता किसी दिए गए वर्ष में $ 30,000 से अधिक मूल्य की बिक्री करता है, तो उन बिक्री पर जो कमीशन कमाते हैं, वह 30% है। अन्यथा उनका कमीशन केवल 20% है। अब तक यह एक बहुत ही सरल वर्कशीट है:

इस कार्यपत्रक का उपयोग करने के लिए, विक्रेता सेल बी 1 में अपनी बिक्री के आंकड़े दर्ज करता है, और सेल बी 2 में सूत्र प्राप्त करने के लिए सही कमीशन दर की गणना करता है, जिसका उपयोग सेल बी 3 में किया जाता है, जो कुल कमीशन की गणना करने के लिए बकाया है (जो बकाया है B1 और B2 का सरल गुणन है).

सेल बी 2 में इस वर्कशीट का एकमात्र दिलचस्प हिस्सा है - यह तय करने का सूत्र कि किस कमीशन की दर का उपयोग करना है: एक नीचे $ 30,000 की दहलीज, या एक ऊपर दहलीज़। यह सूत्र नामक एक्सेल फ़ंक्शन का उपयोग करता है अगर. उन पाठकों के लिए जो आईएफ से परिचित नहीं हैं, यह इस तरह से काम करता है:

अगर(शर्त, मान यदि सत्य है, तो मूल्य यदि असत्य है)

जहां शर्त एक अभिव्यक्ति है जो या तो मूल्यांकन करती है सच या असत्य. ऊपर के उदाहरण में, शर्त अभिव्यक्ति है बी 1, जिसे "बी 1 बी 5 से कम है?" के रूप में पढ़ा जा सकता है, या, एक और तरीका है, "क्या कुल बिक्री थ्रेशो से कम है"। यदि इस प्रश्न का उत्तर "हां" (सत्य) है, तो हम इसका उपयोग करते हैं अगर सच है तो मूल्य फ़ंक्शन का पैरामीटर, अर्थात् बी -6 इस मामले में - बिक्री कुल थी तो कमीशन दर नीचे दहलीज़। यदि प्रश्न का उत्तर "नहीं" (झूठा) है, तो हम इसका उपयोग करते हैं मूल्य अगर झूठ है फ़ंक्शन का पैरामीटर, अर्थात् बी 7 इस मामले में - बिक्री कुल थी तो कमीशन दर ऊपर दहलीज़.

जैसा कि आप देख सकते हैं, $ 20,000 की कुल बिक्री का उपयोग करके सेल B2 में हमें 20% की कमीशन दर मिलती है। यदि हम $ 40,000 का मूल्य दर्ज करते हैं, तो हमें एक अलग कमीशन दर मिलती है:

इसलिए हमारी स्प्रेडशीट काम कर रही है.

इसे और जटिल बनाते हैं। आइए एक दूसरी दहलीज पेश करते हैं: यदि विक्रेता $ 40,000 से अधिक कमाता है, तो उनकी कमीशन दर बढ़कर 40% हो जाती है:

वास्तविक दुनिया में समझने के लिए पर्याप्त आसान है, लेकिन सेल बी 2 में हमारा सूत्र अधिक जटिल हो रहा है। यदि आप सूत्र को बारीकी से देखते हैं, तो आप देखेंगे कि मूल IF फ़ंक्शन का तीसरा पैरामीटर (a) मूल्य अगर झूठ है) अब अपने आप में संपूर्ण IF फ़ंक्शन है। इसे ए कहते हैं नेस्टेड फ़ंक्शन (एक समारोह के भीतर एक समारोह)। यह एक्सेल में पूरी तरह से मान्य है (यह भी काम करता है!), लेकिन इसे पढ़ना और समझना कठिन है.

हम नट और बोल्ट में नहीं जा रहे हैं कि यह कैसे और क्यों काम करता है, न ही हम नेस्टेड फ़ंक्शन की बारीकियों की जांच करेंगे। यह VLOOKUP पर एक ट्यूटोरियल है, सामान्य रूप से एक्सेल पर नहीं.

वैसे भी, यह बदतर हो जाता है! जब हम तय करते हैं कि अगर वे $ 50,000 से अधिक कमाते हैं तो वे 50% कमीशन के हकदार हैं, और यदि वे $ 60,000 से अधिक कमाते हैं तो वे 60% कमीशन के हकदार हैं।?

अब सेल बी 2 में सूत्र, जबकि सही है, लगभग अपठनीय हो गया है। किसी को भी फॉर्मूले नहीं लिखने चाहिए, जहाँ फ़ंक्शंस चार स्तर गहरे हों! निश्चित रूप से एक सरल तरीका होना चाहिए?

वहाँ निश्चित रूप से है। बचाव के लिए VLOOKUP!

आइए कार्यपत्रक को थोड़ा नया स्वरूप दें। हम सभी समान आंकड़े रखेंगे, लेकिन इसे नए तरीके से व्यवस्थित करेंगे, अधिक तालिका का मार्ग:

एक पल लें और अपने लिए सत्यापित करें कि नया दर तालिका ऊपर दहलीज की श्रृंखला के समान ही काम करता है.

वैचारिक रूप से, हम जो करने वाले हैं, वह दर तालिका में विक्रेता की बिक्री (बी 1 से) देखने के लिए VLOOKUP का उपयोग करते हैं और हमें उसी कमीशन दर पर वापस जाते हैं। ध्यान दें कि विक्रेता ने वास्तव में बिक्री की हो सकती है नहीं दर तालिका में पाँच मूल्यों में से एक ($ 0, $ 30,000, $ 40,000, $ 50,000 या $ 60,000)। उन्होंने $ 34,988 की बिक्री बनाई हो सकती है। यह ध्यान रखना महत्वपूर्ण है कि $ 34,988 करता है नहीं दर तालिका में दिखाई देते हैं। चलो देखते हैं कि क्या VLOOKUP हमारी समस्या को हल कर सकता है ...

हम सेल B2 (वह स्थान जिसे हम अपने सूत्र को रखना चाहते हैं) का चयन करते हैं, और फिर से VLOOKUP फ़ंक्शन डालें सूत्र टैब:

कार्य तर्क VLOOKUP के लिए बॉक्स दिखाई देता है। हम एक-एक करके तर्कों (मापदंडों) को एक-एक करके भरते हैं पता लगाने का मूल्य, जो है, इस मामले में, सेल बी 1 से कुल बिक्री। हम कर्सर को अंदर रखते हैं पता लगाने का मूल्य फ़ील्ड और फिर सेल B1 पर एक बार क्लिक करें:

इसके बाद हमें इस डेटा को देखने के लिए VLOOKUP को किस तालिका में निर्दिष्ट करना होगा। इस उदाहरण में, यह दर तालिका है, ज़ाहिर है। हम कर्सर को अंदर रखते हैं तालिका सरणी फ़ील्ड, और फिर संपूर्ण दर तालिका को हाइलाइट करें - शीर्षकों को छोड़कर:

आगे हमें यह निर्दिष्ट करना होगा कि तालिका में कौन सा कॉलम है जिसमें वह जानकारी है जो हम चाहते हैं कि हमारा सूत्र हमारे पास लौटे। इस मामले में हम कमीशन दर चाहते हैं, जो तालिका में दूसरे कॉलम में मिलती है, इसलिए हम इसलिए प्रवेश करते हैं 2 में Col_index_num खेत:

अंत में हम एक मान दर्ज करते हैं रेंज देखना खेत.

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

स्पष्ट होने के लिए, हम एक मान दर्ज करेंगे सच में रेंज देखना खेत। इसे खाली छोड़ना भी ठीक रहेगा, क्योंकि यह डिफ़ॉल्ट मान है:

हमने सभी मापदंडों को पूरा कर लिया है। अब हम क्लिक करते हैं ठीक बटन, और एक्सेल हमारे लिए हमारे VLOOKUP सूत्र का निर्माण करते हैं:

यदि हम कुछ अलग-अलग बिक्री कुल मात्रा के साथ प्रयोग करते हैं, तो हम खुद को संतुष्ट कर सकते हैं कि सूत्र काम कर रहा है.

निष्कर्ष

VLOOKUP के "डेटाबेस" संस्करण में, जहाँ रेंज देखना पैरामीटर है असत्य, मान पहले पैरामीटर में पारित हुआ (पता लगाने का मूल्य) जरूर डेटाबेस में मौजूद रहें। दूसरे शब्दों में, हम एक सटीक मिलान की तलाश कर रहे हैं.

लेकिन VLOOKUP के इस अन्य उपयोग में, हम जरूरी नहीं कि एक सटीक मैच की तलाश कर रहे हैं। इस मामले में, "पास काफी अच्छा है"। लेकिन हमें "पर्याप्त पास" से क्या मतलब है? आइए एक उदाहरण का उपयोग करें: $ 34,988 की कुल बिक्री पर कमीशन दर की खोज करते समय, हमारा वीएलबुक फॉर्म हमें 30% का मान लौटाएगा, जो कि सही उत्तर है। इसने 30% वाली तालिका में पंक्ति का चयन क्यों किया? वास्तव में, इस मामले में "पर्याप्त पास" का क्या मतलब है? चलो सटीक हो:

कब रेंज देखना इस पर लगा है सच (या छोड़ा गया), VLOOKUP कॉलम 1 और मैच में दिखेगा उच्चतम मूल्य जो इससे अधिक नहीं है पता लगाने का मूल्य पैरामीटर.

यह भी ध्यान रखना महत्वपूर्ण है कि इस प्रणाली को काम करने के लिए, तालिका को स्तंभ 1 पर आरोही क्रम में क्रमबद्ध किया जाना चाहिए!

यदि आप VLOOKUP के साथ अभ्यास करना चाहते हैं, तो इस लेख में चित्रित नमूना फ़ाइल यहाँ से डाउनलोड की जा सकती है.