सामग्री
हा धडा समजावून सांगतो की फंक्शन असलेल्या परिस्थितीला त्वरीत कसे सामोरे जावे व्हीपीआर (VLOOKUP) एक्सेल 2013, 2010, 2007 आणि 2003 मध्ये काम करू इच्छित नाही आणि सामान्य त्रुटी कशा ओळखायच्या आणि त्यांचे निराकरण कसे करावे आणि मर्यादांवर मात कशी करावी व्हीपीआर.
मागील अनेक लेखांमध्ये, आम्ही फंक्शनच्या विविध पैलूंचा शोध घेतला आहे व्हीपीआर एक्सेल मध्ये. जर तुम्ही ते काळजीपूर्वक वाचले असेल, तर तुम्ही आता या क्षेत्रातील तज्ञ व्हावे. तथापि, अनेक एक्सेल तज्ञांच्या मते हे विनाकारण नाही व्हीपीआर अधिक जटिल वैशिष्ट्यांपैकी एक. यात अनेक मर्यादा आणि वैशिष्ट्ये आहेत जी अनेक समस्या आणि त्रुटींचे स्रोत बनतात.
या लेखात आपल्याला त्रुटींचे सोपे स्पष्टीकरण सापडेल #AT (#N/A), # नावे? (#NAME?) आणि #मूल्य! (#VALUE!) जे फंक्शनसह कार्य करताना दिसतात व्हीपीआर, तसेच त्यांना हाताळण्याचे तंत्र आणि पद्धती. आम्ही सर्वात सामान्य प्रकरणे आणि सर्वात स्पष्ट कारणांसह प्रारंभ करू. व्हीपीआर कार्य करत नाही, म्हणून लेखात ज्या क्रमाने उदाहरणे दिली आहेत त्या क्रमाने अभ्यास करणे चांगले.
Excel मध्ये VLOOKUP फंक्शनमधील #N/A त्रुटीचे निराकरण करणे
सह सूत्रांमध्ये व्हीपीआर त्रुटी संदेश #AT (#N/A) म्हणजे उपलब्ध नाही (डेटा नाही) – जेव्हा एक्सेल तुम्हाला शोधत असलेले मूल्य शोधू शकत नाही तेव्हा दिसते. हे अनेक कारणांमुळे होऊ शकते.
1. इच्छित मूल्य चुकीचे लिहिले आहे
प्रथम हा आयटम तपासण्याची चांगली कल्पना आहे! जेव्हा तुम्ही हजारो ओळींचा समावेश असलेल्या मोठ्या प्रमाणातील डेटासह काम करता किंवा जेव्हा तुम्ही शोधत असलेले मूल्य सूत्रामध्ये लिहिलेले असते तेव्हा अनेकदा टायपोस होतात.
2. VLOOKUP सह अंदाजे जुळणी शोधताना #N/A त्रुटी
तुम्ही अंदाजे जुळणार्‍या शोध स्थितीसह सूत्र वापरत असल्यास, म्हणजे युक्तिवाद range_lookup (range_lookup) सत्य आहे किंवा निर्दिष्ट नाही, तुमचे सूत्र त्रुटी नोंदवू शकते # एन / ए दोन प्रकरणांमध्ये:
- वर पाहायचे मूल्य हे पाहिले जात असलेल्या अॅरेमधील सर्वात लहान मूल्यापेक्षा कमी आहे.
- शोध स्तंभ चढत्या क्रमाने लावलेला नाही.
3. VLOOKUP सह अचूक जुळणी शोधताना #N/A त्रुटी
जर तुम्ही अचूक जुळणी शोधत असाल, म्हणजे युक्तिवाद range_lookup (range_lookup) FALSE आहे आणि अचूक मूल्य आढळले नाही, सूत्र देखील त्रुटी नोंदवेल # एन / ए. फंक्शनसह अचूक आणि अंदाजे जुळण्या कसे शोधायचे याबद्दल अधिक जाणून घ्या व्हीपीआर.
4. शोध स्तंभ सर्वात डावीकडे नाही
आपल्याला कदाचित माहित आहे की, सर्वात लक्षणीय मर्यादांपैकी एक व्हीपीआर हे असे आहे की ते डावीकडे तोंड करू शकत नाही, म्हणून तुमच्या टेबलमधील लुकअप स्तंभ सर्वात डावीकडे असणे आवश्यक आहे. सराव मध्ये, आम्ही बर्याचदा याबद्दल विसरतो, ज्यामुळे एक गैर-कार्यरत सूत्र आणि त्रुटी येते. # एन / ए.
निर्णय: डेटा संरचना बदलणे शक्य नसल्यास शोध स्तंभ सर्वात डावीकडे असेल, तर तुम्ही फंक्शन्सचे संयोजन वापरू शकता. INDEX (INDEX) आणि अधिक उघड (MATCH) साठी अधिक लवचिक पर्याय म्हणून व्हीपीआर.
5. संख्या मजकूर म्हणून स्वरूपित केल्या आहेत
त्रुटीचा आणखी एक स्रोत # एन / ए सह सूत्रांमध्ये व्हीपीआर मुख्य टेबल किंवा लुकअप टेबलमधील मजकूर स्वरूपात संख्या आहेत.
हे सहसा घडते जेव्हा तुम्ही बाह्य डेटाबेसमधून माहिती आयात करता किंवा जेव्हा तुम्ही अग्रगण्य शून्य ठेवण्यासाठी एखाद्या संख्येच्या आधी अॅपोस्ट्रॉफी टाइप करता.
मजकूर स्वरूपातील संख्येची सर्वात स्पष्ट चिन्हे खालील आकृतीमध्ये दर्शविली आहेत:
याव्यतिरिक्त, क्रमांक स्वरूपात संग्रहित केले जाऊ शकतात जनरल (सर्वसाधारण). या प्रकरणात, फक्त एक लक्षात घेण्याजोगा वैशिष्ट्य आहे - संख्या सेलच्या डाव्या काठावर संरेखित केल्या जातात, तर डीफॉल्टनुसार ते उजव्या काठावर संरेखित केले जातात.
निर्णय: हे एकच मूल्य असल्यास, फक्त त्रुटी चिन्हावर क्लिक करा आणि निवडा क्रमांकामध्ये रूपांतरित करा संदर्भ मेनूमधून (नंबरमध्ये रूपांतरित करा).
जर ही अनेक संख्यांची परिस्थिती असेल, तर त्यांना निवडा आणि निवडलेल्या क्षेत्रावर उजवे-क्लिक करा. दिसत असलेल्या संदर्भ मेनूमध्ये, निवडा सेल सेल (सेल्सचे स्वरूप) > टॅब संख्या (संख्या) > स्वरूप संख्या (अंकीय) आणि दाबा OK.
6. सुरुवातीला किंवा शेवटी एक जागा आहे
त्रुटीचे हे सर्वात कमी स्पष्ट कारण आहे. # एन / ए कार्यात व्हीपीआर, कारण या अतिरिक्त जागा पाहणे दृष्यदृष्ट्या कठीण आहे, विशेषत: मोठ्या तक्त्यांसह काम करताना, जेव्हा बहुतांश डेटा ऑफ-स्क्रीन असतो.
उपाय 1: मुख्य टेबलमधील अतिरिक्त जागा (जेथे VLOOKUP फंक्शन आहे)
मुख्य सारणीमध्ये अतिरिक्त जागा दिसल्यास, तुम्ही युक्तिवाद संलग्न करून सूत्रे योग्यरित्या कार्य करत असल्याची खात्री करू शकता. लुकअप_मूल्य (lookup_value) फंक्शन मध्ये टीआरआयएम (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
उपाय 2: लुकअप टेबलमध्ये अतिरिक्त जागा (लुकअप कॉलममध्ये)
शोध स्तंभात अतिरिक्त जागा असल्यास - सोप्या मार्गांनी # एन / ए सह सूत्र मध्ये व्हीपीआर टाळता येत नाही. च्या ऐवजी व्हीपीआर तुम्ही फंक्शन्सच्या संयोजनासह अॅरे फॉर्म्युला वापरू शकता INDEX (INDEX), अधिक उघड (सामना) и टीआरआयएम (TRIM):
=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 + एंटर करा नेहमीच्या ऐवजी प्रविष्ट करासूत्र योग्यरित्या प्रविष्ट करण्यासाठी.
त्रुटी #VALUE! VLOOKUP सह सूत्रांमध्ये
बहुतेक प्रकरणांमध्ये, मायक्रोसॉफ्ट एक्सेल त्रुटी नोंदवते #मूल्य! (#VALUE!) जेव्हा सूत्रामध्ये वापरलेले मूल्य डेटा प्रकाराशी जुळत नाही. संबंधित व्हीपीआर, नंतर त्रुटीची सहसा दोन कारणे असतात #मूल्य!.
1. तुम्ही शोधत असलेले मूल्य २५५ वर्णांपेक्षा मोठे आहे
सावधगिरी बाळगा: कार्य व्हीपीआर 255 पेक्षा जास्त वर्ण असलेली मूल्ये शोधू शकत नाही. आपण शोधत असलेले मूल्य या मर्यादेपेक्षा जास्त असल्यास, आपल्याला एक त्रुटी संदेश प्राप्त होईल. #मूल्य!.
निर्णय: वैशिष्ट्यांचा एक समूह वापरा INDEX+MATCH (INDEX + सामना). खाली एक सूत्र आहे जे या कार्यासाठी चांगले करेल:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. शोध कार्यपुस्तिकेचा पूर्ण मार्ग निर्दिष्ट केलेला नाही
जर तुम्ही दुसर्‍या वर्कबुकमधून डेटा पुनर्प्राप्त करत असाल, तर तुम्ही त्या फाइलचा पूर्ण मार्ग निर्दिष्ट केला पाहिजे. अधिक विशिष्टपणे, तुम्ही कार्यपुस्तिकेचे नाव (विस्तारासह) चौकोनी कंसात समाविष्ट केले पाहिजे [ ], त्यानंतर शीटचे नाव, त्यानंतर उद्गार चिन्ह. पुस्तक किंवा पत्रकाच्या नावात मोकळी जागा असल्यास हे सर्व बांधकाम अपोस्ट्रॉफीमध्ये बंद केलेले असणे आवश्यक आहे.
येथे फंक्शनची संपूर्ण रचना आहे व्हीपीआर दुसऱ्या पुस्तकात शोधण्यासाठी:
=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 शीट वर पत्रक 1 कार्यपुस्तिकेत नवीन किमती आणि स्तंभातून संबंधित मूल्य काढा D.
टेबल पाथचा कोणताही भाग वगळल्यास, तुमचे कार्य व्हीपीआर कार्य करणार नाही आणि त्रुटीची तक्रार करेल #मूल्य! (जरी लुकअप टेबलसह कार्यपुस्तिका सध्या उघडली आहे).
फंक्शनबद्दल अधिक माहितीसाठी व्हीपीआरदुसर्‍या एक्सेल फाईलचा संदर्भ देत, धडा पहा: VLOOKUP वापरून दुसरी कार्यपुस्तिका शोधत आहे.
3. वितर्क स्तंभ_संख्या 1 पेक्षा कमी आहे
एखाद्या व्यक्तीपेक्षा कमी मूल्य प्रविष्ट करते अशा परिस्थितीची कल्पना करणे कठीण आहे 1ज्या स्तंभातून मूल्य काढायचे आहे ते दर्शवण्यासाठी. जरी या युक्तिवादाचे मूल्य आत नेस्ट केलेल्या दुसर्‍या एक्सेल फंक्शनद्वारे मोजले गेले तर हे शक्य आहे व्हीपीआर.
त्यामुळे असे झाले तर वाद col_index_num (स्तंभ_संख्या) पेक्षा कमी 1कार्य व्हीपीआर त्रुटीची तक्रार देखील करेल #मूल्य!.
तर वाद col_index_num (स्तंभ_संख्या) दिलेल्या अॅरेमधील स्तंभांच्या संख्येपेक्षा जास्त आहे, व्हीपीआर त्रुटीची तक्रार करेल #REF! (#SSYL!).
त्रुटी #NAME? VLOOKUP मध्ये
सर्वात सोपा केस एक चूक आहे # नावे? (#NAME?) – तुम्ही चुकून एखादे फंक्शन नाव त्रुटीसह लिहिल्यास दिसेल.
उपाय स्पष्ट आहे - तुमचे शब्दलेखन तपासा!
VLOOKUP काम करत नाही (मर्यादा, सावधगिरी आणि निर्णय)
ऐवजी क्लिष्ट वाक्यरचना व्यतिरिक्त, व्हीपीआर इतर कोणत्याही एक्सेल फंक्शनपेक्षा अधिक मर्यादा आहेत. या मर्यादांमुळे, वरवर साधी सूत्रे सह व्हीपीआर अनेकदा अनपेक्षित परिणाम होतात. खाली तुम्हाला अनेक सामान्य परिस्थितींसाठी उपाय सापडतील व्हीपीआर चुकीचे आहे.
1. VLOOKUP केस संवेदनशील नाही
कार्य व्हीपीआर केसमध्ये फरक करत नाही आणि लोअरकेस आणि अपरकेस वर्ण समान म्हणून स्वीकारतो. म्हणून, जर सारणीमध्ये अनेक घटक असतील जे फक्त बाबतीत भिन्न असतील तर, VLOOKUP फंक्शन केसची पर्वा न करता आढळलेला पहिला घटक परत करेल.
निर्णय: इतर Excel फंक्शन वापरा जे अनुलंब शोध (LOOKUP, SUMPRODUCT, INDEX, आणि MATCH) च्या संयोजनात करू शकते अचूककेस वेगळे करणारा A. अधिक तपशिलांसाठी, तुम्ही धड्यातून शिकू शकता – Excel मध्ये VLOOKUP केस-सेन्सिटिव्ह बनवण्याचे ४ मार्ग.
2. VLOOKUP प्रथम सापडलेले मूल्य परत करते
जसे तुम्हाला आधीच माहित आहे, व्हीपीआर मिळालेल्या पहिल्या जुळणीशी संबंधित दिलेल्या स्तंभातील मूल्य परत करते. तथापि, तुम्ही ते 2रा, 3रा, 4था किंवा तुम्हाला हव्या असलेल्या मूल्याची इतर कोणतीही पुनरावृत्ती काढू शकता. तुम्हाला सर्व डुप्लिकेट मूल्ये काढायची असल्यास, तुम्हाला फंक्शन्सचे संयोजन आवश्यक असेल INDEX (INDEX), कमीतकमी (लहान) आणि लाइन (ROW).
3. टेबलमध्ये एक स्तंभ जोडला किंवा काढला गेला
दुर्दैवाने, सूत्रे व्हीपीआर प्रत्येक वेळी लुकअप टेबलमध्ये नवीन स्तंभ जोडला किंवा काढला जातो तेव्हा काम करणे थांबवा. हे घडते कारण वाक्यरचना व्हीपीआर शोधाची संपूर्ण श्रेणी आणि डेटा काढण्यासाठी विशिष्ट स्तंभ क्रमांक निर्दिष्ट करणे आवश्यक आहे. साहजिकच, तुम्ही जेव्हा एखादा स्तंभ हटवता किंवा नवीन टाकता तेव्हा दिलेली श्रेणी आणि स्तंभ क्रमांक दोन्ही बदलतात.
निर्णय: आणि पुन्हा फंक्शन्स मदतीसाठी घाईत आहेत INDEX (INDEX) आणि अधिक उघड (मॅच). सूत्रात INDEX+MATCH तुम्ही शोध आणि पुनर्प्राप्ती स्तंभ स्वतंत्रपणे परिभाषित करता आणि परिणामी, तुम्ही सर्व संबंधित शोध सूत्रे अद्यतनित करण्याची काळजी न करता तुम्हाला हवे तितके स्तंभ हटवू किंवा घालू शकता.
4. फॉर्म्युला कॉपी करताना सेल संदर्भ चुकीचे आहेत
हे शीर्षक समस्येचे सार संपूर्णपणे स्पष्ट करते, बरोबर?
निर्णय: नेहमी परिपूर्ण सेल संदर्भ वापरा (चिन्हासह $) श्रेणी रेकॉर्डवर, उदाहरणार्थ $A$2:$C$100 or $A:$C. फॉर्म्युला बारमध्ये, तुम्ही क्लिक करून लिंक प्रकार पटकन स्विच करू शकता F4.
VLOOKUP – IFERROR आणि ISERROR फंक्शन्ससह कार्य करणे
जर तुम्ही वापरकर्त्यांना एरर मेसेजने घाबरवू इच्छित नसाल # एन / ए, #मूल्य! or # नावे?, तुम्ही रिक्त सेल किंवा तुमचा स्वतःचा संदेश दर्शवू शकता. आपण ठेवून हे करू शकता व्हीपीआर फंक्शन मध्ये IFERROR (IFERROR) Excel 2013, 2010 आणि 2007 मध्ये किंवा फंक्शन्सचा एक समूह वापरा IF+ISERROR (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;ЛОЖЬ);"")
आपण फंक्शनच्या मानक त्रुटी संदेशाऐवजी आपला स्वतःचा संदेश प्रदर्शित करू इच्छित असल्यास व्हीपीआर, ते कोट्समध्ये ठेवा, जसे की:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: ISERROR फंक्शनसह कार्य करणे
फंक्शन पासून IFERROR एक्सेल 2007 मध्ये दिसू लागले, पूर्वीच्या आवृत्त्यांमध्ये काम करताना तुम्हाला संयोजन वापरावे लागेल IF (IF) आणि इओशिबका (ISERROR) यासारखे:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
उदाहरणार्थ, सूत्र IF+ISERROR+VLOOKUP, सूत्राप्रमाणे IFERROR+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;ЛОЖЬ))
आजसाठी एवढेच. मला आशा आहे की हे लहान ट्यूटोरियल तुम्हाला सर्व संभाव्य चुका हाताळण्यास मदत करेल. व्हीपीआर आणि तुमची सूत्रे बरोबर काम करा.