विषय-सूची
हमने हाल ही में सबसे उपयोगी एक्सेल फंक्शन्स में से एक को एक लेख समर्पित किया है जिसे कहा जाता है VPR और दिखाया कि इसका उपयोग किसी डेटाबेस से आवश्यक जानकारी को वर्कशीट सेल में निकालने के लिए कैसे किया जा सकता है। हमने यह भी उल्लेख किया है कि फ़ंक्शन के लिए दो उपयोग के मामले हैं VPR और उनमें से केवल एक डेटाबेस प्रश्नों से संबंधित है। इस लेख में, आप फ़ंक्शन का उपयोग करने का एक और कम ज्ञात तरीका सीखेंगे VPR Excel में
यदि आपने अभी तक ऐसा नहीं किया है, तो फ़ंक्शन के बारे में अंतिम लेख अवश्य पढ़ें VPR, क्योंकि नीचे दी गई सभी जानकारी यह मानती है कि आप पहले लेख में वर्णित सिद्धांतों से पहले से ही परिचित हैं।
डेटाबेस के साथ काम करते समय, कार्य VPR एक विशिष्ट पहचानकर्ता पारित किया जाता है, जिसका उपयोग उस जानकारी की पहचान करने के लिए किया जाता है जिसे हम खोजना चाहते हैं (उदाहरण के लिए, उत्पाद कोड या ग्राहक पहचान संख्या)। यह अद्वितीय कोड डेटाबेस में मौजूद होना चाहिए, अन्यथा VPR त्रुटि की रिपोर्ट करेगा। इस लेख में, हम फ़ंक्शन का उपयोग करने के इस तरीके को देखेंगे VPRजब आईडी डेटाबेस में बिल्कुल मौजूद नहीं है। मानो समारोह VPR अनुमानित मोड पर स्विच किया जाता है, और यह चुनता है कि जब हम कुछ खोजना चाहते हैं तो हमें कौन सा डेटा प्रदान करना है। कुछ परिस्थितियों में, यह वही है जो आवश्यक है।
जीवन से एक उदाहरण। हमने कार्य निर्धारित किया है
आइए इस लेख को वास्तविक जीवन के उदाहरण के साथ स्पष्ट करें - बिक्री मीट्रिक की एक विस्तृत श्रृंखला के आधार पर कमीशन की गणना करना। हम एक बहुत ही सरल विकल्प के साथ शुरू करेंगे, और फिर हम इसे धीरे-धीरे जटिल करेंगे जब तक कि समस्या का एकमात्र तर्कसंगत समाधान फ़ंक्शन का उपयोग करना नहीं है। VPR. हमारे काल्पनिक कार्य के लिए प्रारंभिक परिदृश्य इस प्रकार है: यदि कोई विक्रेता एक वर्ष में बिक्री में $30000 से अधिक कमाता है, तो उसका कमीशन 30% है। अन्यथा, कमीशन केवल 20% है। आइए इसे एक तालिका के रूप में रखें:
विक्रेता सेल B1 में अपना बिक्री डेटा दर्ज करता है, और सेल B2 में सूत्र सही कमीशन दर निर्धारित करता है जिसकी विक्रेता अपेक्षा कर सकता है। बदले में, विक्रेता को प्राप्त होने वाले कुल कमीशन की गणना करने के लिए सेल बी 3 में परिणामी दर का उपयोग किया जाता है (बस कोशिकाओं बी 1 और बी 2 को गुणा करना)।
तालिका का सबसे दिलचस्प हिस्सा बी 2 सेल में निहित है - यह कमीशन दर निर्धारित करने का सूत्र है। इस सूत्र में एक एक्सेल फ़ंक्शन होता है जिसे कहा जाता है IF (यदि)। उन पाठकों के लिए जो इस फ़ंक्शन से परिचित नहीं हैं, मैं समझाऊंगा कि यह कैसे काम करता है:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
शर्त एक फ़ंक्शन तर्क है जो या तो का मान लेता है सही कोड (सच या असत्य (असत्य)। उपरोक्त उदाहरण में, व्यंजक B1
क्या यह सच है कि B1, B5 से छोटा है?
या आप इसे अलग तरह से कह सकते हैं:
क्या यह सच है कि वर्ष के लिए बिक्री की कुल राशि सीमा मूल्य से कम है?
अगर हम इस सवाल का जवाब हाँ (TRUE), फिर फ़ंक्शन वापस आ जाता है मूल्य अगर सच है (मान अगर सही है)। हमारे मामले में, यह सेल B6 का मान होगा, यानी कमीशन दर जब कुल बिक्री सीमा से नीचे होगी। यदि हम प्रश्न का उत्तर दें नहीं (FALSE) फिर लौटता है मूल्य अगर गलत है (मान यदि FALSE है)। हमारे मामले में, यह सेल B7 का मान है, यानी कमीशन दर जब कुल बिक्री सीमा से ऊपर होती है।
जैसा कि आप देख सकते हैं, यदि हम $20000 की कुल बिक्री लेते हैं, तो हमें सेल B2 में 20% कमीशन दर प्राप्त होती है। यदि हम $40000 का मान दर्ज करते हैं, तो कमीशन दर 30% से बदल जाएगी:
इस प्रकार हमारी तालिका काम करती है।
हम कार्य को जटिल करते हैं
आइए चीजों को थोड़ा और कठिन बनाते हैं। आइए एक और सीमा निर्धारित करें: यदि विक्रेता $40000 से अधिक कमाता है, तो कमीशन दर बढ़कर 40% हो जाती है:
सब कुछ सरल और स्पष्ट प्रतीत होता है, लेकिन सेल बी 2 में हमारा सूत्र अधिक जटिल हो जाता है। यदि आप सूत्र को बारीकी से देखते हैं, तो आप देखेंगे कि फ़ंक्शन का तीसरा तर्क IF (आईएफ) एक और पूर्ण कार्य में बदल गया IF (यदि)। इस निर्माण को कार्यों का एक दूसरे में घोंसला बनाना कहा जाता है। एक्सेल खुशी से इन निर्माणों की अनुमति देता है, और वे काम भी करते हैं, लेकिन उन्हें पढ़ना और समझना बहुत कठिन होता है।
हम तकनीकी विवरण में नहीं जाएंगे - यह क्यों और कैसे काम करता है, और हम नेस्टेड कार्यों को लिखने की बारीकियों में नहीं जाएंगे। आखिरकार, यह समारोह को समर्पित एक लेख है VPR, एक्सेल के लिए एक पूर्ण गाइड नहीं।
जो भी हो, सूत्र और जटिल हो जाता है! क्या होगा अगर हम उन विक्रेताओं के लिए 50% की कमीशन दर के लिए एक और विकल्प पेश करते हैं जो बिक्री में $50000 से अधिक कमाते हैं। और अगर किसी ने 60000 डॉलर से अधिक की बिक्री की है, तो क्या वे 60% कमीशन देंगे?
अब सेल बी 2 में सूत्र, भले ही त्रुटियों के बिना लिखा गया हो, पूरी तरह से अपठनीय हो गया है। मुझे लगता है कि कुछ ऐसे हैं जो अपनी परियोजनाओं में नेस्टिंग के 4 स्तरों वाले सूत्रों का उपयोग करना चाहते हैं। कोई आसान तरीका होना चाहिए ?!
और एक ऐसा तरीका है! समारोह हमारी मदद करेगा VPR.
हम समस्या को हल करने के लिए VLOOKUP फ़ंक्शन लागू करते हैं
आइए अपनी टेबल के डिज़ाइन को थोड़ा बदल दें। हम सभी समान फ़ील्ड और डेटा रखेंगे, लेकिन उन्हें एक नए, अधिक कॉम्पैक्ट तरीके से व्यवस्थित करेंगे:
कुछ समय लें और सुनिश्चित करें कि नई तालिका दर तालिका पिछली थ्रेशोल्ड तालिका के समान डेटा शामिल है।
मुख्य विचार फ़ंक्शन का उपयोग करना है VPR तालिका के अनुसार वांछित टैरिफ दर निर्धारित करने के लिए दर तालिका बिक्री की मात्रा के आधार पर। कृपया ध्यान दें कि विक्रेता उस राशि पर सामान बेच सकता है जो तालिका में पांच थ्रेसहोल्ड में से एक के बराबर नहीं है। उदाहरण के लिए, वह $34988 में बेच सकता था, लेकिन ऐसी कोई राशि नहीं है। आइए देखें कि फंक्शन कैसे होता है VPR ऐसी स्थिति से निपट सकते हैं।
VLOOKUP फ़ंक्शन सम्मिलित करना
सेल B2 चुनें (जहां हम अपना फॉर्मूला डालना चाहते हैं) और खोजें VLOOKUP (VLOOKUP) एक्सेल फंक्शंस लाइब्रेरी में: सूत्र (सूत्र) > समारोह लाइब्रेरी (फ़ंक्शन लाइब्रेरी) > लुकअप और संदर्भ (संदर्भ और सरणियाँ)।
एक डायलॉग बॉक्स दिखाई देता है कार्य तर्क (फ़ंक्शन तर्क)। हम तर्कों के मूल्यों को एक-एक करके भरते हैं, शुरू करते हैं पता लगाने का मूल्य (पता लगाने का मूल्य)। इस उदाहरण में, यह सेल B1 से बिक्री की कुल राशि है। कर्सर को फील्ड में रखें पता लगाने का मूल्य (लुकअप_वैल्यू) और सेल B1 चुनें।
अगला, आपको कार्यों को निर्दिष्ट करने की आवश्यकता है VPRडेटा की तलाश कहां करें। हमारे उदाहरण में, यह एक तालिका है दर तालिका. कर्सर को फील्ड में रखें तालिका सरणी (तालिका) और संपूर्ण तालिका का चयन करें दर तालिकाहेडर को छोड़कर।
इसके बाद, हमें यह निर्दिष्ट करने की आवश्यकता है कि हमारे सूत्र का उपयोग करके कौन सा कॉलम डेटा निकालना है। हम कमीशन दर में रुचि रखते हैं, जो तालिका के दूसरे कॉलम में है। इसलिए, तर्क के लिए Col_index_num (Column_number) मान 2 दर्ज करें।
और अंत में, हम अंतिम तर्क पेश करते हैं - रेंज देखना (अंतराल_लुकअप)।
महत्वपूर्ण: यह इस तर्क का उपयोग है जो फ़ंक्शन को लागू करने के दो तरीकों के बीच अंतर करता है VPR. डेटाबेस के साथ काम करते समय, तर्क रेंज देखना (रेंज_लुकअप) का हमेशा एक मान होना चाहिए असत्य (FALSE) सटीक मिलान खोजने के लिए। समारोह के हमारे उपयोग में VPR, हमें इस फ़ील्ड को खाली छोड़ना होगा, या एक मान दर्ज करना होगा सही कोड (सच)। इस विकल्प को सही ढंग से चुनना बेहद जरूरी है।
इसे स्पष्ट करने के लिए, हम परिचय देंगे सही कोड (सच) क्षेत्र में रेंज देखना (अंतराल_लुकअप)। हालाँकि, यदि आप फ़ील्ड को खाली छोड़ देते हैं, तो यह कोई त्रुटि नहीं होगी, क्योंकि सही कोड इसका डिफ़ॉल्ट मान है:
हमने सभी मापदंडों को भर दिया है। अब हम दबाते हैं OK, और एक्सेल एक फ़ंक्शन के साथ हमारे लिए एक सूत्र बनाता है VPR.
यदि हम कुल बिक्री राशि के लिए कई अलग-अलग मूल्यों के साथ प्रयोग करते हैं, तो हम यह सुनिश्चित करेंगे कि सूत्र सही ढंग से काम करता है।
निष्कर्ष
जब समारोह VPR डेटाबेस के साथ काम करता है, तर्क रेंज देखना (रेंज_लुकअप) को स्वीकार करना चाहिए असत्य (असत्य)। और मान दर्ज किया गया पता लगाने का मूल्य (लुकअप_वैल्यू) डेटाबेस में मौजूद होना चाहिए। दूसरे शब्दों में, यह एक सटीक मिलान की तलाश में है।
इस लेख में हमने जो उदाहरण देखा है, उसमें सटीक मिलान प्राप्त करने की कोई आवश्यकता नहीं है। यह मामला है जब समारोह VPR वांछित परिणाम वापस करने के लिए अनुमानित मोड पर स्विच करना होगा।
उदाहरण के लिए: हम यह निर्धारित करना चाहते हैं कि $34988 की बिक्री मात्रा वाले विक्रेता के लिए कमीशन गणना में किस दर का उपयोग किया जाए। समारोह VPR हमें 30% का मान देता है, जो बिल्कुल सही है। लेकिन सूत्र ने ठीक 30% वाली पंक्ति का चयन क्यों किया और 20% या 40% नहीं? अनुमानित खोज से क्या तात्पर्य है? चलो स्पष्ट हो।
जब तर्क रेंज देखना (अंतराल_लुकअप) का एक मान है सही कोड (TRUE) या छोड़ा गया, फ़ंक्शन VPR पहले कॉलम के माध्यम से पुनरावृति करता है और सबसे बड़े मान का चयन करता है जो लुकअप मान से अधिक नहीं है।
महत्वपूर्ण बिंदु: इस योजना के काम करने के लिए, तालिका के पहले कॉलम को आरोही क्रम में क्रमबद्ध किया जाना चाहिए।