इस एक्सेल फ़ंक्शन का अनुप्रयोग और परिभाषा
VLOOKUP एक एक्सेल फ़ंक्शन है जिसके साथ उपयोगकर्ता तालिका सामग्री को खोज और मूल्यांकन कर सकता है। यह फ़ंक्शन Windows और Mac के लिए Excel 2007 के संस्करणों में उपलब्ध है।
व्लुकअप क्या है?
VLOOKUP के संभावित उपयोगों को यहां एक उदाहरण का उपयोग करके समझाया जाना है: इसमें आप साहित्य के बहुत बड़े प्रशंसक हैं और इसलिए आपने अपनी खुद की एक्सेल स्प्रेडशीट बनाई है जिसमें आप अपने द्वारा एकत्र की गई पुस्तकों को ध्यान से क्रमबद्ध कर सकते हैं। प्रत्येक कार्य को निम्नलिखित श्रेणियों की जानकारी के साथ दर्ज किया जाता है:
-
लेखक
-
शीर्षक
-
पृष्ठ संख्या
-
प्रकाशन वर्ष
अब आप किसी मित्र को अपनी अगली मुलाकात में अपने साथ ले जाने के लिए एक पुस्तक टिप देना चाहेंगे। दुर्भाग्य से, आप केवल लेखक के बारे में सोच सकते हैं, पुस्तक के शीर्षक के बारे में नहीं। यह वह जगह है जहां VLOOKUP चलन में आता है, क्योंकि यह इस इनपुट मान का उपयोग उस जानकारी को बाहर निकालने के लिए कर सकता है जिसे आप ढूंढ रहे हैं।
VLOOKUP का उपयोग कैसे किया जाता है?
सूत्र बनाने के बारे में सोचने से पहले, यह निर्धारित किया जाना चाहिए कि इनपुट फ़ील्ड और विभिन्न आउटपुट फ़ील्ड बाद में कहाँ स्थित होंगे। ऐसा करने के लिए, यह एक अलग तालिका बनाने के लिए समझ में आता है जो शुरू में खाली है और इस प्रकार उल्लिखित जानकारी के लिए जगह की अनुमति देता है। यदि आप इस नई तालिका को मौजूदा तालिका के उदाहरण के आधार पर डिज़ाइन करते हैं, तो आपको बाद में समय बचाने का लाभ मिलेगा।
इस आधार पर, VLOOKUP फॉर्मूला या तो मैन्युअल रूप से बनाया जा सकता है या एक्सेल द्वारा स्वचालित रूप से जेनरेट किया जा सकता है। शुरुआती लोगों के लिए, सूत्र की संरचना और प्रभाव को धीरे-धीरे जानने के लिए बाद के दृष्टिकोण का उपयोग करना सार्थक है। ऐसा करने के लिए, "सूत्र" टैब पर "फ़ंक्शन सम्मिलित करें" के लिए बटन का चयन किया जाता है। VLOOKUP खुलने वाली विंडो में छिपा है। पुष्टि के बाद, एक विंडो फिर से खुलती है जिसमें सूत्र के चार पैरामीटर भरे जा सकते हैं। य़े हैं:
-
खोज मानदंड
-
आव्यूह
-
कॉलम इंडेक्स
-
क्षेत्र_संदर्भ
इसलिए सूत्र का कच्चा मसौदा इस तरह दिखता है:
= VLOOKUP (खोज मानदंड, मैट्रिक्स, कॉलम इंडेक्स, रेंज_लिंक)
और इस तरह एक संभावित आवेदन में:
= वीलुकअप (H3; A3: E40; 5)
खोज मानदंड
ताकि फ़ंक्शन को पता चले कि प्रारंभिक बिंदु के रूप में किस मान का उपयोग किया जाना चाहिए, दो चरण पहले इनपुट फ़ील्ड के रूप में चुनी गई रेखा "खोज मानदंड" फ़ील्ड में नोट की जाती है। हमारे उदाहरण में, पुस्तक के लेखक "फिलिप पुलमैन" का नाम वहाँ दर्ज किया गया है। यह सूत्र को लचीला बनाता है और जैसे ही दर्ज किया गया मान बदलता है, उसे फिर से समायोजित करने की आवश्यकता नहीं होती है।
आव्यूह
"मैट्रिक्स" इनपुट फ़ील्ड उस तालिका का वर्णन करता है जिसमें आउटपुट की जाने वाली जानकारी मिल सकती है। इस प्रकार इस विशेष मैट्रिक्स में पुस्तक शीर्षक, पृष्ठ संख्या और प्रकाशन के वर्ष के कॉलम भी शामिल हैं।
शीर्ष बाएँ से नीचे दाएँ हाशिये पर शीर्षकों के बिना मैट्रिक्स को पूरी तरह से एक बार चुना जाता है। इस तरह, एक्सेल जानता है कि मूल्यांकन करते समय किस सामग्री को ध्यान में रखा जाना चाहिए।
कॉलम इंडेक्स
"कॉलम इंडेक्स" के लिए इनपुट फ़ील्ड उपयोगकर्ता को मैट्रिक्स के कॉलम को परिभाषित करने के लिए प्रेरित करता है जिसमें केवल मांगा गया मान सूचीबद्ध होता है। कॉलम के असाइनमेंट को कालानुक्रमिक रूप से क्रमांकित किया गया है। इसका मतलब यह है कि तालिका के पहले कॉलम को मान 1, दूसरा मान 2, आदि प्राप्त होता है। हमारे उदाहरण में, यह लेखक के लिए कॉलम इंडेक्स 1, शीर्षक के लिए कॉलम इंडेक्स 2, पेज नंबर के लिए कॉलम इंडेक्स 3 से मेल खाता है। और प्रकाशन के वर्ष के लिए कॉलम इंडेक्स 4।
तालिका को यथासंभव लचीला रखने के लिए, संख्या के बजाय कॉलम शीर्षक को जोड़ा जा सकता है। इसका यह लाभ है कि सूत्र को बिना किसी समस्या के अन्य पंक्तियों में भी स्थानांतरित किया जा सकता है, क्योंकि स्तंभ शीर्षक को हर बार लचीले ढंग से अनुकूलित किया जा सकता है।
ध्यान: VLOOKUP मैट्रिक्स को बाएं से दाएं पढ़ता है, यही कारण है कि फ़ंक्शन द्वारा ध्यान में रखे जाने के लिए कॉलम इंडेक्स को खोज मानदंड के लिए कॉलम के दाईं ओर रखा जाना चाहिए!
क्षेत्र_संदर्भ
"रेंज_लुकअप" पैरामीटर उस सटीकता को निर्दिष्ट करके VLOOKUP सूत्र को पूरा करता है जिसके साथ तालिका का मूल्यांकन किया जाता है। हालाँकि, यह सूत्र के पहले बताए गए घटकों से भिन्न है क्योंकि यह वैकल्पिक है। यदि मान 0 "गलत" के लिए दर्ज किया गया है, तो एक्सेल केवल उस मान की खोज करता है जिसे खोज मानदंड के रूप में निर्दिष्ट किया गया था। "सत्य" के लिए मान 1 के साथ, हालांकि, स्पष्ट मूल्यों के लिए खोज जारी है यदि सटीक मान नहीं मिल सका।
इस पैरामीटर को निर्दिष्ट करना वैकल्पिक है क्योंकि मान 1 डिफ़ॉल्ट रूप से सेट है। यह सेटिंग बाद में कई खोज मानदंडों के साथ उन्नत VLOOKUP में उपयोगी होगी।
विलय
जैसे ही सभी आवश्यक पैरामीटर सेट हो जाते हैं, VLOOKUP का उपयोग किया जा सकता है। खोज मानदंड दर्ज करने और फ़ंक्शन की पुष्टि करने के बाद, आप जो मान खोज रहे हैं वह उस पंक्ति में दिखाई देता है जिसे आउटपुट फ़ील्ड के रूप में परिभाषित किया गया है।
हमारे उदाहरण में, पुस्तक का शीर्षक "द गोल्डन कंपास" अब प्रदर्शित किया गया है, जो दर्ज किए गए लेखक के अनुरूप है। पृष्ठ संख्या और प्रकाशन के वर्ष का शीघ्रता से पता लगाने के लिए, मौजूदा VLOOKUP सूत्र को बाद के कक्षों में खींचने के अलावा और कुछ करने की आवश्यकता नहीं है। यह इतना आसान है क्योंकि VLOOKUP के कॉलम इंडेक्स को पहली टेबल के कॉलम हेडिंग से जोड़ा गया है और दूसरी टेबल को भी उसी क्रम में संरचित किया गया है।
इस घटना में कि तालिकाएँ एक-दूसरे से भिन्न होनी चाहिए या सब कुछ के बावजूद कोई त्रुटि होती है, VLOOKUP सूत्र को मैन्युअल रूप से भी बदला जा सकता है। ऐसा करने के लिए, कॉलम इंडेक्स के लिए अंतिम अंक आउटपुट होने के लिए नए मान के कॉलम से मेल खाना चाहिए।
कई खोज मानदंडों के साथ VLOOKUP
अक्सर ऐसा होता है कि एक बड़ी एक्सेल तालिका का सटीक मूल्यांकन करने के लिए एक एकल खोज मानदंड पर्याप्त नहीं है। फिर VLOOKUP को कई खोज मानदंडों के साथ चलाना समझ में आता है। ऐसा करने के लिए, मौजूदा सूत्र को एक अतिरिक्त IF फ़ंक्शन के साथ पूरक होना चाहिए। इस तरह, आवेदन के दौरान आठ अलग-अलग खोज मानदंडों को ध्यान में रखा जा सकता है।
एकाधिक एक्सेल स्प्रेडशीट में VLOOKUP
यदि खोज मानदंड न केवल एक तालिका में पाया जा सकता है, बल्कि संभवतः दूसरे में भी पाया जा सकता है, तो VLOOKUP सूत्र को तदनुसार समायोजित किया जा सकता है। इसके लिए एक if फंक्शन और एक ISERROR फंक्शन दोनों को मौजूदा फॉर्मूले के सामने रखा जाना चाहिए। इसके लिए पांच मापदंडों की आवश्यकता है:
-
खोज मानदंड
-
मैट्रिक्स 1 और मैट्रिक्स 2
-
कॉलम इंडेक्स 1 और कॉलम इंडेक्स 2
परिणाम इस तरह दिखता है:
= IF (ISERROR (VLOOKUP (खोज-मानदंड, मैट्रिक्स1, कॉलम-इंडेक्स1, 0));
वीलुकअप (खोज मानदंड; मैट्रिक्स 2; कॉलम इंडेक्स 2,0); वीलुकअप (खोज मानदंड; मैट्रिक्स 1; कॉलम इंडेक्स 1;))
और इस तरह एक संभावित आवेदन में:
= IF (ISERROR (VLOOKUP (E5, A5: B9,2, 0)), VLOOKUP (E5, A13: B17,2, 0), VLOOKUP (E5, A5: B9,2, 0))
खोज मानदंड का उपयोग उस मान को सम्मिलित करने के लिए किया जाता है जिसे दो तालिकाओं में खोजा जाना है। मैट्रिक्स 1 और मैट्रिक्स 2 दो तालिकाओं के संबंधित सेल क्षेत्रों को परिभाषित करते हैं। कॉलम इंडेक्स 1 और कॉलम इंडेक्स 2 का उपयोग अधिक विस्तार से परिभाषित करने के लिए किया जाता है कि संबंधित तालिकाओं के कौन से कॉलम खोजे जाने चाहिए।
यदि आप जिस मान की तलाश कर रहे हैं वह दोनों तालिकाओं में होता है, तो एक्सेल पहली तालिका से परिणाम आउटपुट करेगा। हालाँकि, यदि मान दोनों में से किसी भी तालिका में नहीं मिलता है, तो एक त्रुटि संदेश प्रकट होता है। सूत्र का लाभ यह है कि दो सूचियों में समान संरचना या समान आकार नहीं होना चाहिए।
VLOOKUP . का उपयोग करके श्रेणियों को मान असाइन करें
VLOOKUP का एक अतिरिक्त कार्य सूचीबद्ध मानों को आपकी पसंद के अक्षरों और विधेय में स्वचालित रूप से विभाजित करने की अनुमति देता है। हमारे पिछले उदाहरण में, पुस्तक प्रकार के लिए एक अतिरिक्त तालिका स्तंभ सम्मिलित किया जाना चाहिए। ५० पृष्ठों तक की पुस्तकों को लघुकथा की शैली में आना चाहिए, जबकि ५१ से १५० पृष्ठों की पुस्तकें उपन्यास को और १५१ पृष्ठों से उपन्यास को सौंपी जाती हैं। इसे संभव बनाने के लिए, VLOOKUP में किसी अतिरिक्त सूत्र की आवश्यकता नहीं है, केवल घुंघराले कोष्ठक "{}" का उपयोग। तैयार सूत्र इस तरह दिखता है:
= वीलुकअप (बी१; {१. "लघु कहानी"; ५१. "उपन्यास"; १५१. "उपन्यास"}; २)
घुंघराले कोष्ठक की सामग्री एक मैट्रिक्स को इंगित करती है जो संबंधित पुस्तक प्रकार के क्षेत्र को परिभाषित करती है। इसलिए उपयुक्त जीनस के लिए साइड की लंबाई का असाइनमेंट घुंघराले कोष्ठक के भीतर रखा गया है। सूत्र मानों के जोड़े का उपयोग करता है, प्रत्येक को एक बिंदु से अलग किया जाता है। मैट्रिक्स {1. "लघु कहानी"; 51. "उपन्यास"; 151. "उपन्यास"} निम्नानुसार पढ़ा जाता है:
"1 शो से एक लघु कहानी, 51 से एक उपन्यास दिखाते हैं, 151 से एक उपन्यास दिखाते हैं।"
इस मैट्रिक्स को आसानी से विभिन्न कार्यों के लिए अनुकूलित किया जा सकता है। यह एक तरफ मैट्रिक्स के आकार और संख्या के साथ-साथ उनके पदनाम से संबंधित है। इसलिए अलग-अलग अक्षरों के बजाय स्ट्रिंग्स या संख्याओं को आउटपुट करना संभव है। आपको केवल सूत्र में अक्षरों को समायोजित करना है।
एकाधिक कार्यपत्रकों में VLOOKUP
VLOOKUP का एक अन्य कार्य अपने उपयोगकर्ताओं को विभिन्न स्प्रेडशीट पर स्थित सामग्री को लिंक करने की अनुमति देता है। हमारे उदाहरण के लिए, यह विकल्प तब उपयोगी हो सकता है जब जानकारी को पहले विभिन्न कार्यपत्रकों में क्रमबद्ध किया जाता है और फिर सारांश तालिका में अद्यतन किया जाता है।
कल्पना करें कि, आपकी पुस्तकों के अतिरिक्त, आप अपनी एकत्रित फिल्मों को एक एक्सेल स्प्रेडशीट में भी सूचीबद्ध करते हैं। फिर आप दोनों संग्रहों को एक बड़ी तालिका में संयोजित करें।
इस प्रक्रिया का लाभ न केवल बढ़े हुए क्रम में है, बल्कि संभावित त्रुटियों से बचने में भी है। यदि आप एक नई प्रविष्टि बनाना चाहते हैं या किसी मौजूदा को अपडेट करना चाहते हैं, तो आपको बड़ी तालिका में खोज करने की आवश्यकता नहीं है, बल्कि इसके बजाय आप छोटी तालिका तक पहुंच सकते हैं। फिर मानों को स्वचालित रूप से सारांशित एक्सेल तालिका में स्थानांतरित कर दिया जाता है। यह बड़ी तालिका में पुनर्लेखन को अनावश्यक बनाता है, जो एक दुर्भाग्यपूर्ण कदम और त्रुटि संदेशों की बाद की श्रृंखला से बचा जाता है।
सूत्र कैसा दिखता है?
यह फ़ंक्शन एक और सूत्र सम्मिलित करके फिर से संभव बनाया गया है। एकाधिक मानदंडों के साथ खोज करते समय एक अतिरिक्त IF सूत्र की आवश्यकता होती है, एकाधिक कार्यपत्रकों के साथ काम करने के लिए एक अप्रत्यक्ष सूत्र की आवश्यकता होती है। यह VLOOKUP मैट्रिक्स के लिए किसी अन्य स्प्रेडशीट की श्रेणी को निर्दिष्ट करने की अनुमति देता है।
= VLOOKUP (खोज मानदंड; अप्रत्यक्ष (मैट्रिक्स); कॉलम इंडेक्स; रेंज_लिंक)
ध्यान: यह सूत्र केवल तभी काम करेगा जब विभिन्न शीटों में अलग-अलग तालिकाओं के नाम सामान्य तालिका के स्तंभ शीर्षकों के समान हों। सेल ग्रिड के ऊपर बाईं ओर "नाम फ़ील्ड" में संपूर्ण तालिकाओं का नाम दिया जा सकता है। जिन तालिकाओं को पहले ही नाम दिया जा चुका है, उन्हें कुंजी संयोजन Ctrl + F3 के साथ देखा जा सकता है।
उभरते हुए त्रुटि संदेशों से निपटना
लिंक की गई एक्सेल टेबल के साथ काम करने से अवांछित समस्याएं हो सकती हैं। इसमें विशेष रूप से गलत मानों का आउटपुट शामिल है। इस घटना में कि गलत मान 0 आउटपुट है, एक्सेल की सेटिंग्स में एक छोटी सी समस्या है, जिसे जल्दी से ठीक किया जा सकता है।
दूसरी ओर, सामान्य त्रुटि संदेश #NV, VLOOKUP का एक जानबूझकर कार्य है, जो उपयोगकर्ता को इंगित करता है कि आवश्यक मान उपलब्ध नहीं है। इस नोट को एक फॉर्मूले की मदद से अलग तरह से डिजाइन किया जा सकता है।
VLOOKUP - एक सिंहावलोकन
VLOOKUP एक उपयोगी एक्सेल फ़ंक्शन है जिसका उपयोग तालिकाओं को खोजने और मूल्यांकन करने के लिए किया जा सकता है। इसके फायदे इसके उपयोगकर्ता के अनुकूल और लचीले अनुप्रयोग में स्पष्ट हैं। इस तरह, जो कोई भी नियमित रूप से एक्सेल टेबल के साथ काम करता है, वह फ़ंक्शन से लाभान्वित हो सकता है। चाहे वह निजी संग्राहक हो जो अपनी छोटी टेबल बनाता है, या बड़ी कंपनी जो महत्वपूर्ण रूप से अधिक महत्वपूर्ण डेटा सेट संसाधित करती है।
यदि, दूसरी ओर, आपके पास अभी भी अनुत्तरित अनुरोध हैं जो VLOOKUP को पूरा नहीं कर सके, तो आप एक अतिरिक्त एक्सेल विकल्प की प्रतीक्षा कर सकते हैं: Microsoft Excel 365 उपयोगकर्ताओं को 2022-2023 की शुरुआत से नया XLOOKUP पेश कर रहा है। यह VLOOKUP की दक्षताओं का निर्माण करता है और उन्हें अतिरिक्त, कभी-कभी और भी सरल, कार्यों के साथ पूरक करता है। इसलिए, इस बिंदु पर डेटा मूल्यांकन में एक नई दिनचर्या भी खुलती है।
