03 का 01
Excel के VLOOKUP के साथ डेटा पर अनुमानित मिलान खोजें
VLOOKUP फ़ंक्शन कैसे काम करता है
एक्सेल के VLOOKUP फ़ंक्शन , जो लंबवत लुकअप के लिए खड़े हैं , का उपयोग डेटा या डेटाबेस की तालिका में स्थित विशिष्ट जानकारी को देखने के लिए किया जा सकता है।
VLOOKUP सामान्य रूप से डेटा के एक फ़ील्ड को इसके आउटपुट के रूप में देता है। यह कैसे करता है:
- आप एक नाम या lookup_value प्रदान करते हैं जो VLOOKUP को वांछित डेटा देखने के लिए डेटा तालिका की पंक्ति या रिकॉर्ड में बताता है
- आप जो कॉलम नंबर चाहते हैं, उसे कॉलम नंबर - col_index_num के रूप में जाना जाता है
- फ़ंक्शन डेटा तालिका के पहले कॉलम में lookup_value की तलाश करता है
- VLOOKUP तब आपूर्ति किए गए कॉलम नंबर का उपयोग करके उसी रिकॉर्ड के किसी अन्य फ़ील्ड से प्राप्त जानकारी को रेखांकित करता है और देता है
पहले डेटा छंटनी
यद्यपि हमेशा जरूरी नहीं है, यह आमतौर पर डेटा की सीमा को क्रमबद्ध करने के लिए सबसे अच्छा होता है जो VLOOKUP सॉर्ट कुंजी के लिए श्रेणी के पहले कॉलम का उपयोग करके आरोही क्रम में खोज रहा है।
यदि डेटा सॉर्ट नहीं किया गया है, तो VLOOKUP गलत परिणाम लौटा सकता है।
VLOOKUP फ़ंक्शन का सिंटेक्स और तर्क
फ़ंक्शन का सिंटैक्स फ़ंक्शन के लेआउट को संदर्भित करता है और इसमें फ़ंक्शन का नाम, ब्रैकेट और तर्क शामिल होता है ।
VLOOKUP फ़ंक्शन के लिए वाक्यविन्यास है:
= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
लुकअप _value - (आवश्यक) खोज करने के लिए मूल्य - जैसे ऊपर की छवि में बेची गई मात्रा
table_array - (आवश्यक) यह डेटा की तालिका है कि VLOOKUP आपके द्वारा दी जाने वाली जानकारी को ढूंढने के लिए खोज करता है।
- Table_array में कम से कम दो कॉलम डेटा होना चाहिए
- पहले कॉलम में आमतौर पर lookup_value होता है
col_index_num - (आवश्यक) उस मान का कॉलम नंबर जो आप चाहते हैं।
- क्रमांकन खोज_की कॉलम के साथ कॉलम 1 के रूप में शुरू होता है
- यदि col_index_num table_array तर्क में चयनित कॉलम की संख्या से अधिक संख्या में सेट किया गया है तो #REF! फ़ंक्शन द्वारा त्रुटि लौटा दी जाती है
range_lookup - (वैकल्पिक) इंगित करता है कि सीमा आरोही क्रम में क्रमबद्ध है या नहीं।
- पहले कॉलम में डेटा सॉर्ट कुंजी के रूप में उपयोग किया जाता है
- एक बूलियन मान - सत्य या गलत एकमात्र स्वीकार्य मान हैं
- यदि छोड़ा गया है, तो मान डिफ़ॉल्ट रूप से TRUE पर सेट किया गया है
- यदि सत्य या छोड़ा गया है और सीमा का पहला स्तंभ आरोही क्रम में क्रमबद्ध नहीं है, तो गलत परिणाम हो सकता है
- यदि सत्य या छोड़ा गया है और लुकअप _value के लिए एक सटीक मिलान नहीं मिला है, तो आकार या मान में छोटा निकटतम मिलान खोज_की के रूप में उपयोग किया जाता है
- यदि गलत पर सेट किया गया है, तो VLOOKUP केवल लुकअप _value के लिए एक सटीक मिलान स्वीकार करता है। यदि कई मिलान मूल्य हैं, तो पहला मिलान मान वापस कर दिया गया है
- यदि FALSE पर सेट किया गया है और search_key के लिए कोई मिलान मान नहीं मिला है, तो फ़ंक्शन द्वारा # एन / ए त्रुटि लौटा दी जाती है
उदाहरण: खरीदी गई मात्रा के लिए डिस्काउंट दर पाएं
उपर्युक्त छवि में उदाहरण VLOOKUP फ़ंक्शन का उपयोग छूट की दर को खोजने के लिए करता है जो खरीदे गए सामानों की मात्रा के आधार पर भिन्न होता है।
उदाहरण से पता चलता है कि 1 9 वस्तुओं की खरीद के लिए छूट 2% है। ऐसा इसलिए है क्योंकि मात्रा कॉलम में मानों की श्रृंखला होती है। नतीजतन, VLOOKUP एक सटीक मिलान नहीं मिल सकता है। इसके बजाय, सही छूट दर वापस करने के लिए अनुमानित मिलान मिलना चाहिए।
अनुमानित मिलान खोजने के लिए:
- आरोही क्रम में table_array में डेटा सॉर्ट करें;
- TRUE को range_lookup तर्क सेट करें
उदाहरण में, VLOOKUP फ़ंक्शन वाले निम्नलिखित सूत्र का उपयोग खरीदे गए सामानों की मात्रा के लिए छूट खोजने के लिए किया जाता है।
= VLOOKUP (सी 2, $ सी $ 5: $ डी $ 8.2, TRUE)
भले ही यह सूत्र केवल वर्कशीट सेल में टाइप किया जा सके, एक और विकल्प, जैसा कि नीचे सूचीबद्ध चरणों के साथ प्रयोग किया गया है, अपने तर्क दर्ज करने के लिए फ़ंक्शन के संवाद बॉक्स का उपयोग करना है।
- संवाद बॉक्स का उपयोग करना अक्सर फ़ंक्शन के तर्कों को सही ढंग से दर्ज करना आसान बनाता है।
VLOOKUP संवाद बॉक्स खोलना
सेल बी 2 में उपरोक्त छवि में दिखाए गए VLOOKUP फ़ंक्शन में प्रवेश करने के लिए उपयोग किए गए चरण निम्न हैं:
- इसे सक्रिय सेल बनाने के लिए सेल बी 2 पर क्लिक करें - वह स्थान जहां VLOOKUP फ़ंक्शन के परिणाम प्रदर्शित होते हैं
- फॉर्मूला टैब पर क्लिक करें।
- फ़ंक्शन ड्रॉप डाउन सूची खोलने के लिए रिबन से लुकअप और संदर्भ चुनें
- फ़ंक्शन के संवाद बॉक्स को लाने के लिए सूची में VLOOKUP पर क्लिक करें
03 में से 02
एक्सेल के VLOOKUP फ़ंक्शन के तर्क दर्ज करना
सेल संदर्भों को इंगित करना
VLOOKUP फ़ंक्शन के लिए तर्क उपरोक्त छवि में दिखाए गए अनुसार संवाद बॉक्स की अलग-अलग पंक्तियों में दर्ज किए गए हैं।
तर्क के रूप में उपयोग किए जाने वाले सेल संदर्भों को सही रेखा में टाइप किया जा सकता है, या, नीचे दिए गए चरणों में किए गए अनुसार, पॉइंटिंग, जिसमें माउस पॉइंटर के साथ कोशिकाओं की वांछित सीमा को हाइलाइट करना शामिल है, उन्हें डायलॉग बॉक्स में दर्ज करने के लिए उपयोग किया जा सकता है ।
पॉइंटिंग का उपयोग करने के फायदे में शामिल हैं:
- यह टाइपिंग से तेज़ है;
- सही सेल संदर्भों में प्रवेश करने में कम गलतियां की जाती हैं।
तर्क के साथ सापेक्ष और पूर्ण सेल संदर्भों का उपयोग करना
डेटा की एक ही तालिका से अलग-अलग जानकारी वापस करने के लिए VLOOKUP की कई प्रतियों का उपयोग करना असामान्य नहीं है। ऐसा करना आसान बनाने के लिए, अक्सर एक सेल से दूसरे सेल में कॉपी किया जा सकता है। जब अन्य कोशिकाओं में फ़ंक्शंस की प्रतिलिपि बनाई जाती है, तो यह सुनिश्चित करने के लिए देखभाल की जानी चाहिए कि परिणामी सेल संदर्भ फ़ंक्शन के नए स्थान के बाद सही हैं।
उपर्युक्त छवि में, डॉलर संकेत ( $ ) table_array तर्क के लिए सेल संदर्भों को घेरे हुए हैं जो दर्शाते हैं कि वे पूर्ण सेल संदर्भ हैं , जिसका अर्थ यह है कि यदि फ़ंक्शन किसी अन्य सेल पर कॉपी किया गया है तो वे नहीं बदलेंगे। यह वांछनीय है क्योंकि VLOOKUP की कई प्रतियां डेटा के समान तालिका को सूचना के स्रोत के रूप में संदर्भित करती हैं।
दूसरी तरफ lookup_value के लिए उपयोग किया जाने वाला सेल संदर्भ डॉलर संकेतों से घिरा नहीं है, जो इसे एक सापेक्ष सेल संदर्भ बनाता है। सापेक्ष सेल संदर्भ तब बदलते हैं जब वे प्रतिलिपि किए गए डेटा की स्थिति के सापेक्ष अपने नए स्थान को प्रतिबिंबित करने के लिए प्रतिलिपि बनाई जाती हैं।
फ़ंक्शन तर्क दर्ज करना
- VLOOKUP संवाद बॉक्स में लुकअप _value लाइन पर क्लिक करें
- Search_key तर्क के रूप में इस सेल संदर्भ में प्रवेश करने के लिए वर्कशीट में सेल C2 पर क्लिक करें
- संवाद बॉक्स की Table_array लाइन पर क्लिक करें
- Table_array तर्क के रूप में इस श्रेणी में प्रवेश करने के लिए वर्कशीट में कक्ष C5 से D8 को हाइलाइट करें - तालिका शीर्षलेख शामिल नहीं हैं
- सीमा को पूर्ण सेल संदर्भों में बदलने के लिए कीबोर्ड पर F4 कुंजी दबाएं
- संवाद बॉक्स के Col_index_num लाइन पर क्लिक करें
- Col_index_num तर्क के रूप में इस पंक्ति पर 2 टाइप करें, क्योंकि छूट दरें Table_array तर्क के कॉलम 2 में स्थित हैं
- संवाद बॉक्स की Range_lookup लाइन पर क्लिक करें
- शब्द को Range_lookup तर्क के रूप में सही टाइप करें
- संवाद बॉक्स को बंद करने के लिए कीबोर्ड पर एंटर कुंजी दबाएं और वर्कशीट पर वापस आएं
- उत्तर 2% (खरीदी गई मात्रा के लिए छूट दर) वर्कशीट के सेल डी 2 में दिखाई देनी चाहिए
- जब आप सेल डी 2 पर क्लिक करते हैं, तो पूर्ण फ़ंक्शन = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE) वर्कशीट के ऊपर सूत्र बार में दिखाई देता है
क्यों परिणाम के रूप में VLOOKUP 2% वापस आ गया
- उदाहरण में, मात्रा कॉलम में 19 के search_key मान के लिए सटीक मिलान नहीं है।
- चूंकि is_sorted तर्क सत्य पर सेट है, VLOOKUP को search_key मान का अनुमानित मिलान मिलेगा।
- आकार में निकटतम मान जो अभी भी 1 9 की खोज_की मान से छोटा है 11 है।
- इसलिए, VLOOKUP, 11 युक्त पंक्ति में छूट प्रतिशत की तलाश करता है, और परिणामस्वरूप, 2% की छूट दर देता है।
03 का 03
एक्सेल VLOOKUP काम नहीं कर रहा है: # एन / ए और #REF त्रुटियां
VLOOKUP त्रुटि संदेश
निम्नलिखित त्रुटि संदेश VLOOKUP से जुड़े हुए हैं।
ए # एन / ए ("मान उपलब्ध नहीं है") त्रुटि प्रदर्शित की जाती है यदि:
- लुकअप _value रेंज तर्क के पहले कॉलम में नहीं मिला है
- Table_array तर्क गलत है। उदाहरण के लिए, तर्क में सीमा के बाईं ओर खाली कॉलम शामिल हो सकते हैं
- Range_lookup तर्क FALSE पर सेट है और search_key तर्क के लिए एक सटीक मिलान श्रेणी के पहले कॉलम में नहीं पाया जा सकता है
- Range_lookup तर्क सत्य पर सेट है और श्रेणी के पहले कॉलम में सभी मान search_key से बड़े हैं
एक #REF! ("सीमा से बाहर संदर्भ") त्रुटि प्रदर्शित की जाती है यदि:
- Col_index_num तर्क Table_array में कॉलम की संख्या से अधिक है