विषय-सूची
यह पाठ बताता है कि कैसे जल्दी से एक ऐसी स्थिति से निपटने के लिए जहां एक समारोह VPR (VLOOKUP) एक्सेल 2013, 2010, 2007 और 2003 में काम नहीं करना चाहता है, और सामान्य त्रुटियों को कैसे पहचानें और ठीक करें और सीमाओं को दूर करें VPR.
पिछले कई लेखों में, हमने फ़ंक्शन के विभिन्न पहलुओं की खोज की है VPR एक्सेल में। यदि आपने उन्हें ध्यान से पढ़ लिया है, तो अब आपको इस क्षेत्र में विशेषज्ञ होना चाहिए। हालांकि, यह अकारण नहीं है कि कई एक्सेल विशेषज्ञ मानते हैं VPR अधिक जटिल विशेषताओं में से एक। इसमें सीमाओं और विशेषताओं का एक समूह है जो कई समस्याओं और त्रुटियों का स्रोत बन जाता है।
इस लेख में आपको त्रुटियों की सरल व्याख्या मिलेगी #एटी (# लागू नहीं), # नाम? (#NAME?) और #VALUE! (#VALUE!) जो फ़ंक्शन के साथ काम करते समय दिखाई देते हैं VPR, साथ ही तकनीक और उनसे निपटने के तरीके। हम सबसे सामान्य मामलों और सबसे स्पष्ट कारणों से शुरू करेंगे। VPR काम नहीं करता है, इसलिए उदाहरणों का अध्ययन उस क्रम में करना बेहतर है जिसमें उन्हें लेख में दिया गया है।
एक्सेल में VLOOKUP फ़ंक्शन में #N/A त्रुटि को ठीक करना
सूत्रों में के साथ VPR त्रुटि संदेश #एटी (#N/A) का अर्थ है अनुपलब्ध (कोई डेटा नहीं) - तब प्रकट होता है जब एक्सेल वह मान नहीं ढूंढ पाता जिसे आप ढूंढ रहे हैं। ऐसा कई कारणों से हो सकता है।
1. वांछित मान गलत वर्तनी है
इस आइटम को पहले जांचना अच्छा विचार है! टाइपो अक्सर तब होते हैं जब आप बहुत बड़ी मात्रा में डेटा के साथ काम करते हैं, जिसमें हजारों लाइनें होती हैं, या जब आप जिस मूल्य की तलाश कर रहे हैं वह एक सूत्र में लिखा जाता है।
2. #N/A त्रुटि VLOOKUP के साथ अनुमानित मिलान की खोज करते समय
यदि आप एक अनुमानित मिलान खोज शर्त वाले सूत्र का उपयोग करते हैं, अर्थात तर्क रेंज देखना (रेंज_लुकअप) सही है या निर्दिष्ट नहीं है, आपका सूत्र त्रुटि की रिपोर्ट कर सकता है # N / A दो मामलों में:
- देखने के लिए मान, देखे जा रहे सरणी में सबसे छोटे मान से कम है।
- खोज कॉलम को आरोही क्रम में क्रमबद्ध नहीं किया गया है।
3. #N/A त्रुटि VLOOKUP के साथ सटीक मिलान की तलाश करते समय
यदि आप एक सटीक मिलान की तलाश कर रहे हैं, अर्थात तर्क रेंज देखना (रेंज_लुकअप) गलत है और सटीक मान नहीं मिला, सूत्र भी एक त्रुटि की रिपोर्ट करेगा # N / A. किसी फ़ंक्शन के साथ सटीक और अनुमानित मिलान खोजने के तरीके के बारे में और जानें VPR.
4. सर्च कॉलम सबसे बाईं ओर नहीं है
जैसा कि आप शायद जानते हैं, सबसे महत्वपूर्ण सीमाओं में से एक VPR यह है कि यह बाईं ओर नहीं हो सकता है, इसलिए आपकी तालिका में लुकअप कॉलम सबसे बाईं ओर होना चाहिए। व्यवहार में, हम अक्सर इसके बारे में भूल जाते हैं, जो एक गैर-कार्यशील सूत्र और एक त्रुटि की ओर जाता है। # N / A.
फेसला: यदि डेटा संरचना को बदलना संभव नहीं है ताकि खोज कॉलम सबसे बाईं ओर हो, तो आप कार्यों के संयोजन का उपयोग कर सकते हैं सूचकांक (इंडेक्स) और अधिक उजागर (MATCH) के लिए अधिक लचीले विकल्प के रूप में VPR.
5. संख्याओं को टेक्स्ट के रूप में स्वरूपित किया जाता है
त्रुटि का एक अन्य स्रोत # N / A के साथ सूत्रों में VPR मुख्य तालिका या लुकअप तालिका में पाठ स्वरूप में संख्याएँ हैं।
यह आमतौर पर तब होता है जब आप बाहरी डेटाबेस से जानकारी आयात करते हैं, या जब आप अग्रणी शून्य रखने के लिए किसी संख्या से पहले एपॉस्ट्रॉफी टाइप करते हैं।
टेक्स्ट प्रारूप में किसी संख्या के सबसे स्पष्ट संकेत नीचे दिए गए चित्र में दिखाए गए हैं:
इसके अलावा, संख्याओं को प्रारूप में संग्रहीत किया जा सकता है सामान्य जानकारी (सामान्य)। इस मामले में, केवल एक ध्यान देने योग्य विशेषता है - संख्याएं सेल के बाएं किनारे से संरेखित होती हैं, जबकि डिफ़ॉल्ट रूप से वे दाएं किनारे पर संरेखित होती हैं।
फेसला: यदि यह एकल मान है, तो बस त्रुटि आइकन पर क्लिक करें और चुनें संख्या में कनवर्ट करें (संख्या में कनवर्ट करें) संदर्भ मेनू से।
यदि कई नंबरों के साथ यही स्थिति है, तो उन्हें चुनें और चयनित क्षेत्र पर राइट-क्लिक करें। दिखाई देने वाले संदर्भ मेनू में, चुनें प्रारूप प्रकोष्ठों (प्रारूप कक्ष) > टैब नंबर (संख्या)> प्रारूप नंबर (संख्यात्मक) और दबाएं OK.
6. शुरुआत में या अंत में एक जगह होती है
यह त्रुटि का कम से कम स्पष्ट कारण है। # N / A समारोह में VPR, चूंकि इन अतिरिक्त स्थानों को देखना दृष्टिगत रूप से कठिन है, विशेष रूप से बड़ी तालिकाओं के साथ कार्य करते समय, जब अधिकांश डेटा ऑफ-स्क्रीन होता है।
समाधान 1: मुख्य तालिका में अतिरिक्त स्थान (जहां VLOOKUP फ़ंक्शन है)
यदि मुख्य तालिका में अतिरिक्त स्थान दिखाई देते हैं, तो आप तर्क को संलग्न करके सुनिश्चित कर सकते हैं कि सूत्र सही ढंग से काम करते हैं पता लगाने का मूल्य (लुकअप_वैल्यू) एक फ़ंक्शन में ट्रिम (काट-छांट करना):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
समाधान 2: लुकअप तालिका में अतिरिक्त स्थान (लुकअप कॉलम में)
यदि खोज कॉलम में अतिरिक्त स्थान हैं - सरल तरीके # N / A के साथ सूत्र में VPR टाला नहीं जा सकता। के बजाय VPR आप कार्यों के संयोजन के साथ एक सरणी सूत्र का उपयोग कर सकते हैं सूचकांक (अनुक्रमणिका), अधिक उजागर (मैच) और ट्रिम (काट-छांट करना):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
चूंकि यह एक सरणी सूत्र है, इसलिए दबाना न भूलें Ctrl + Shift + Enter सामान्य के बजाय दर्जसूत्र को सही ढंग से दर्ज करने के लिए।
त्रुटि #VALUE! VLOOKUP . के साथ सूत्रों में
ज्यादातर मामलों में, माइक्रोसॉफ्ट एक्सेल एक त्रुटि की रिपोर्ट करता है #VALUE! (#VALUE!) जब सूत्र में प्रयुक्त मान डेटा प्रकार से मेल नहीं खाता। विषय में VPR, तो आमतौर पर त्रुटि के दो कारण होते हैं #VALUE!.
1. आप जिस मान की तलाश कर रहे हैं वह 255 वर्णों से अधिक लंबा है
सावधान रहें: समारोह VPR 255 से अधिक वर्णों वाले मानों की खोज नहीं कर सकता। यदि आप जिस मान की तलाश कर रहे हैं वह इस सीमा से अधिक है, तो आपको एक त्रुटि संदेश प्राप्त होगा। #VALUE!.
फेसला: सुविधाओं का एक समूह का प्रयोग करें इंडेक्स+मैच (इंडेक्स + मैच)। नीचे एक सूत्र है जो इस कार्य के लिए ठीक काम करेगा:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. खोज कार्यपुस्तिका का पूरा पथ निर्दिष्ट नहीं है
यदि आप किसी अन्य कार्यपुस्तिका से डेटा पुनर्प्राप्त कर रहे हैं, तो आपको उस फ़ाइल का पूरा पथ निर्दिष्ट करना होगा। अधिक विशेष रूप से, आपको वर्गाकार कोष्ठकों में कार्यपुस्तिका का नाम (विस्तार सहित) शामिल करना होगा [ ], उसके बाद पत्रक का नाम, उसके बाद विस्मयादिबोधक बिंदु। पुस्तक या शीट के नाम में रिक्त स्थान होने की स्थिति में यह सभी निर्माण एपोस्ट्रोफ में संलग्न होना चाहिए।
यहाँ समारोह की पूरी संरचना है VPR किसी अन्य पुस्तक में खोजने के लिए:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
वास्तविक सूत्र इस तरह दिख सकता है:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
यह सूत्र सेल मान को देखेगा A2 एक कॉलम में B शीट पर Sheet1 कार्यपुस्तिका में नई कीमतें और कॉलम से संबंधित मान निकालें D.
यदि तालिका पथ का कोई भाग छोड़ा गया है, तो आपका कार्य VPR काम नहीं करेगा और एक त्रुटि की रिपोर्ट करेगा #VALUE! (भले ही लुकअप टेबल वाली कार्यपुस्तिका वर्तमान में खुली हो)।
समारोह के बारे में अधिक जानकारी के लिए VPRकिसी अन्य Excel फ़ाइल को संदर्भित करते हुए, पाठ देखें: VLOOKUP का उपयोग करके किसी अन्य कार्यपुस्तिका की खोज करना।
3. तर्क Column_num 1 . से कम है
ऐसी स्थिति की कल्पना करना कठिन है जहां कोई व्यक्ति . से कम मूल्य में प्रवेश करता है 1उस कॉलम को इंगित करने के लिए जिससे मूल्य निकाला जाए। यद्यपि यह संभव है यदि इस तर्क के मूल्य की गणना किसी अन्य एक्सेल फ़ंक्शन द्वारा की जाती है, जो अंदर निहित है VPR.
तो, अगर ऐसा होता है कि तर्क col_index_num (स्तंभ_संख्या) . से कम 1समारोह VPR एक त्रुटि की भी रिपोर्ट करेगा #VALUE!.
यदि तर्क col_index_num (column_number) दिए गए ऐरे में कॉलम की संख्या से बड़ा है, VPR एक त्रुटि की रिपोर्ट करेगा #REF! (#एसएसवाईएल!)
त्रुटि #NAME? VLOOKUP . में
सबसे आसान मामला एक गलती है # नाम? (#NAME?) - यदि आप गलती से किसी फ़ंक्शन का नाम त्रुटि के साथ लिखते हैं तो दिखाई देगा।
समाधान स्पष्ट है - अपनी वर्तनी जांचें!
VLOOKUP काम नहीं करता है (सीमाएं, चेतावनी और निर्णय)
बल्कि जटिल वाक्य रचना के अलावा, VPR किसी भी अन्य एक्सेल फ़ंक्शन की तुलना में अधिक सीमाएं हैं। इन सीमाओं के कारण, प्रतीत होता है सरल सूत्र VPR अक्सर अप्रत्याशित परिणाम देते हैं। नीचे आपको कई सामान्य परिदृश्यों के समाधान मिलेंगे जहां VPR गलत है।
1. VLOOKUP केस संवेदी नहीं है
समारोह VPR केस के बीच अंतर नहीं करता है और लोअरकेस और अपरकेस वर्णों को समान रूप से स्वीकार करता है। इसलिए, यदि तालिका में ऐसे कई तत्व हैं जो केवल मामले में भिन्न हैं, तो VLOOKUP फ़ंक्शन पाया गया पहला तत्व लौटाएगा, चाहे मामला कुछ भी हो।
फेसला: एक अन्य एक्सेल फ़ंक्शन का उपयोग करें जो संयोजन में लंबवत खोज (लुकअप, समप्रॉडक्ट, इंडेक्स और मैच) कर सकता है सटीकए जो मामले को अलग करता है। अधिक विवरण के लिए, आप पाठ से सीख सकते हैं - एक्सेल में VLOOKUP केस-संवेदी बनाने के 4 तरीके।
2. VLOOKUP पाया गया पहला मान लौटाता है
जैसा कि आप पहले से जानते हैं, VPR दिए गए कॉलम से मिले पहले मैच के अनुरूप मान लौटाता है। हालाँकि, आप इसे अपने इच्छित मान के दूसरे, तीसरे, चौथे, या किसी अन्य दोहराव को निकालने के लिए प्राप्त कर सकते हैं। यदि आपको सभी डुप्लिकेट मान निकालने की आवश्यकता है, तो आपको कार्यों के संयोजन की आवश्यकता होगी सूचकांक (अनुक्रमणिका), कम से कम (छोटा) और लाइन (पंक्ति)।
3. तालिका में एक कॉलम जोड़ा या हटाया गया था
दुर्भाग्य से, सूत्र VPR हर बार लुकअप टेबल में कोई नया कॉलम जोड़े या हटाए जाने पर काम करना बंद कर दें। ऐसा इसलिए होता है क्योंकि वाक्य रचना VPR आपको डेटा निष्कर्षण के लिए खोज की पूरी श्रृंखला और विशिष्ट कॉलम संख्या निर्दिष्ट करने की आवश्यकता है। स्वाभाविक रूप से, जब आप किसी कॉलम को हटाते हैं या एक नया कॉलम डालते हैं, तो दी गई रेंज और कॉलम नंबर दोनों बदल जाते हैं।
फेसला: और फिर से कार्य मदद करने की जल्दी में हैं सूचकांक (इंडेक्स) और अधिक उजागर (मिलान)। सूत्र में इंडेक्स+मैच आप खोज और पुनर्प्राप्ति कॉलम को अलग-अलग परिभाषित करते हैं, और परिणामस्वरूप, आप सभी संबंधित खोज फ़ार्मुलों को अपडेट करने की चिंता किए बिना जितने चाहें उतने कॉलम हटा या सम्मिलित कर सकते हैं।
4. सूत्र की प्रतिलिपि बनाते समय सेल संदर्भ विकृत हो जाते हैं
यह शीर्षक समस्या का सार पूरी तरह से समझाता है, है ना?
फेसला: हमेशा निरपेक्ष सेल संदर्भों का उपयोग करें (प्रतीक के साथ $) रिकॉर्ड पर रेंज, उदाहरण के लिए $ए$2:$सी$100 or $ए:$सी. फ़ॉर्मूला बार में, आप लिंक प्रकार को तुरंत क्लिक करके स्विच कर सकते हैं F4.
VLOOKUP - कार्यों के साथ काम करना IFERROR और ISERROR
यदि आप उपयोगकर्ताओं को त्रुटि संदेशों से डराना नहीं चाहते हैं # N / A, #VALUE! or # नाम?, आप एक खाली सेल या अपना संदेश दिखा सकते हैं। आप इसे रखकर कर सकते हैं VPR एक समारोह में IFERROR (IFERROR) एक्सेल 2013, 2010 और 2007 में या कार्यों के एक समूह का उपयोग करें अगर + इसरर (IF+ISERROR) पुराने संस्करणों में।
VLOOKUP: IFERROR फ़ंक्शन के साथ कार्य करना
फ़ंक्शन सिंटैक्स IFERROR (IFERROR) सरल है और अपने लिए बोलता है:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
यही है, पहले तर्क के लिए आप एक त्रुटि के लिए जाँच करने के लिए मान सम्मिलित करते हैं, और दूसरे तर्क के लिए आप निर्दिष्ट करते हैं कि यदि कोई त्रुटि पाई जाती है तो क्या वापस करना है।
उदाहरण के लिए, यदि आप जिस मान की तलाश कर रहे हैं वह नहीं मिलता है, तो यह सूत्र एक खाली सेल लौटाता है:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
यदि आप फ़ंक्शन के मानक त्रुटि संदेश के बजाय अपना स्वयं का संदेश प्रदर्शित करना चाहते हैं VPR, इसे उद्धरणों में रखें, जैसे:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: ISERROR फ़ंक्शन के साथ कार्य करना
समारोह के बाद से IFERROR एक्सेल 2007 में दिखाई दिया, पुराने संस्करणों में काम करते समय आपको संयोजन का उपयोग करना होगा IF (अगर व ईओशिबका (ISERROR) इस तरह:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
उदाहरण के लिए, सूत्र अगर+ISERROR+VLOOKUP, सूत्र के समान इफरर+वीलुकअपऊपर दिखाया गया है:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
आज के लिए इतना ही। मुझे उम्मीद है कि यह छोटा ट्यूटोरियल आपको सभी संभावित गलतियों से निपटने में मदद करेगा। VPR और अपने सूत्रों को सही ढंग से काम करने दें।