सामग्रीहरू
यस पाठले फंक्शन भएको अवस्थालाई चाँडै कसरी सामना गर्ने भनेर बताउँछ VPR (VLOOKUP) Excel 2013, 2010, 2007 र 2003 मा काम गर्न चाहँदैन, र कसरी सामान्य त्रुटिहरू पहिचान गर्ने र समाधान गर्ने र सीमितताहरू पार गर्ने VPR.
धेरै अघिल्लो लेखहरूमा, हामीले प्रकार्यका विभिन्न पक्षहरू अन्वेषण गरेका छौं VPR Excel मा। यदि तपाईंले तिनीहरूलाई ध्यानपूर्वक पढ्नुभएको छ भने, तपाईं अब यस क्षेत्रमा विशेषज्ञ हुनुपर्दछ। यद्यपि, धेरै एक्सेल विशेषज्ञहरूले विश्वास गर्ने कारण बिना यो होइन VPR थप जटिल सुविधाहरू मध्ये एक। यसमा सीमितता र सुविधाहरूको गुच्छा छ जुन धेरै समस्या र त्रुटिहरूको स्रोत बन्छ।
यस लेखमा तपाईले त्रुटिहरूको सरल व्याख्या पाउनुहुनेछ #AT (#N/A), #NAME? (#NAME?) र #VALUE! (#VALUE!) जुन प्रकार्यसँग काम गर्दा देखा पर्दछ VPR, साथै तिनीहरूसँग व्यवहार गर्ने प्रविधिहरू र विधिहरू। हामी सबैभन्दा सामान्य केसहरू र सबैभन्दा स्पष्ट कारणहरूको साथ सुरु गर्नेछौं। VPR काम गर्दैन, त्यसैले यो लेखमा दिइएको क्रम मा उदाहरणहरू अध्ययन गर्न राम्रो छ।
एक्सेलमा VLOOKUP प्रकार्यमा #N/A त्रुटि फिक्स गर्दै
संग सूत्र मा VPR त्रुटि सन्देश #AT (#N/A) को अर्थ उपलब्ध छैन (कुनै डाटा छैन) - देखा पर्दछ जब एक्सेल तपाईले खोजिरहनु भएको मान फेला पार्न सक्दैन। यो धेरै कारणले हुन सक्छ।
1. इच्छित मान गलत हिज्जे छ
यो वस्तु पहिले जाँच गर्न राम्रो विचार! तपाईंले हजारौं रेखाहरू मिलेर धेरै ठूलो मात्रामा डाटा प्रयोग गर्दा वा तपाईंले खोजिरहनुभएको मानलाई सूत्रमा लेख्दा टाइपोहरू प्रायः हुन्छन्।
2. VLOOKUP सँग अनुमानित मेल खोज्दा #N/A त्रुटि
यदि तपाइँ अनुमानित मिल्दो खोज अवस्थाको साथ सूत्र प्रयोग गर्नुहुन्छ, जस्तै तर्क दायरा_लुकअप (range_lookup) TRUE हो वा निर्दिष्ट गरिएको छैन, तपाईंको सूत्रले त्रुटि रिपोर्ट गर्न सक्छ # N / A दुई अवस्थामा:
- हेर्नको लागि मान एरेमा हेरिएको सबैभन्दा सानो मान भन्दा कम छ।
- खोज स्तम्भ बढ्दो क्रममा क्रमबद्ध गरिएको छैन।
3. VLOOKUP सँग ठ्याक्कै मिल्दो खोज्दा #N/A त्रुटि
यदि तपाइँ एक सटीक मिलान खोज्दै हुनुहुन्छ, अर्थात् तर्क दायरा_लुकअप (range_lookup) FALSE छ र सही मान फेला परेन, सूत्रले त्रुटि पनि रिपोर्ट गर्नेछ। # N / A। प्रकार्यसँग सटीक र अनुमानित मिल्दोहरू कसरी खोज्ने भन्ने बारे थप जान्नुहोस् VPR.
4. खोज स्तम्भ सबैभन्दा बायाँ छैन
तपाईलाई थाहा छ, सबैभन्दा महत्त्वपूर्ण सीमाहरू मध्ये एक VPR यो हो कि यो बायाँ तिर अनुहार गर्न सक्दैन, त्यसैले तपाईको तालिकामा लुकअप स्तम्भ बायाँ हुनुपर्छ। अभ्यासमा, हामी प्राय: यस बारे बिर्सन्छौं, जसले एक गैर-कार्यकारी सूत्र र त्रुटि निम्त्याउँछ। # N / A.
निर्णय: यदि खोजी स्तम्भ बायाँपट्टि हुने गरी डाटा संरचना परिवर्तन गर्न सम्भव छैन भने, तपाईंले प्रकार्यहरूको संयोजन प्रयोग गर्न सक्नुहुन्छ। INDEX (INDEX) र थप खुलासा (MATCH) को लागि थप लचिलो विकल्पको रूपमा VPR.
5. संख्याहरू पाठको रूपमा ढाँचामा छन्
त्रुटिको अर्को स्रोत # N / A संग सूत्रहरु मा VPR मुख्य तालिका वा लुकअप तालिकामा पाठ ढाँचामा नम्बरहरू हुन्।
यो सामान्यतया तब हुन्छ जब तपाइँ बाह्य डाटाबेसहरूबाट जानकारी आयात गर्नुहुन्छ, वा जब तपाइँ अग्रगामी शून्य राख्नको लागि संख्याको अगाडि apostrophe टाइप गर्नुहुन्छ।
पाठ ढाँचामा संख्याको सबैभन्दा स्पष्ट संकेतहरू तलको चित्रमा देखाइएको छ:
थप रूपमा, नम्बरहरू ढाँचामा भण्डारण गर्न सकिन्छ सामान्य (सामान्य)। यस अवस्थामा, त्यहाँ एउटा मात्र ध्यानयोग्य सुविधा छ - संख्याहरू सेलको बायाँ किनारामा पङ्क्तिबद्ध छन्, जबकि पूर्वनिर्धारित रूपमा तिनीहरू दायाँ किनारामा पङ्क्तिबद्ध छन्।
निर्णय: यदि यो एकल मान हो भने, केवल त्रुटि प्रतिमामा क्लिक गर्नुहोस् र चयन गर्नुहोस् नम्बरमा रूपान्तरण गर्नुहोस् सन्दर्भ मेनुबाट (नम्बरमा रूपान्तरण गर्नुहोस्)।
यदि यो धेरै संख्याहरूको साथ स्थिति हो भने, तिनीहरूलाई चयन गर्नुहोस् र चयन गरिएको क्षेत्रमा दायाँ-क्लिक गर्नुहोस्। देखा पर्ने सन्दर्भ मेनुमा, चयन गर्नुहोस् ढाँचा कक्षहरू (ढाँचा कक्षहरू) > ट्याब नम्बर (नम्बर) > ढाँचा नम्बर (संख्यात्मक) र थिच्नुहोस् OK.
6. सुरु वा अन्त्यमा एक ठाउँ छ
यो त्रुटि को लागी कम से कम स्पष्ट कारण हो। # N / A कार्यमा VPR, किनकि यी अतिरिक्त खाली ठाउँहरू हेर्न कठिन हुन्छ, विशेष गरी ठूला टेबलहरूसँग काम गर्दा, अधिकांश डाटा अफ-स्क्रिन हुँदा।
समाधान १: मुख्य तालिकामा अतिरिक्त खाली ठाउँहरू (जहाँ VLOOKUP प्रकार्य छ)
यदि मुख्य तालिकामा अतिरिक्त खाली ठाउँहरू देखा पर्छन् भने, तपाईँले तर्कलाई संलग्न गरेर सूत्रहरूले सही रूपमा काम गरेको सुनिश्चित गर्न सक्नुहुन्छ। look_value (lookup_value) प्रकार्यमा TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
समाधान २: लुकअप तालिकामा अतिरिक्त खाली ठाउँहरू (लुकअप स्तम्भमा)
यदि अतिरिक्त खाली ठाउँहरू खोज स्तम्भमा छन् भने - सरल तरिकाहरू # N / A संग सूत्र मा VPR टार्न सकिँदैन। सट्टामा VPR तपाईं प्रकार्यहरूको संयोजनको साथ एरे सूत्र प्रयोग गर्न सक्नुहुन्छ INDEX (INDEX), थप खुलासा (म्याच) र TRIM (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+Enter सामान्य को सट्टा प्रविष्ट गर्नुहोस्सूत्र सही रूपमा प्रविष्ट गर्न।
त्रुटि #VALUE! VLOOKUP सँग सूत्रहरूमा
धेरैजसो अवस्थामा, Microsoft Excel ले त्रुटि रिपोर्ट गर्छ #VALUE! (#VALUE!) जब सूत्रमा प्रयोग गरिएको मान डेटा प्रकारसँग मेल खाँदैन। सम्बन्धमा VPR, त्यसपछि त्यहाँ त्रुटि को लागी सामान्यतया दुई कारणहरु छन् #VALUE!.
1. तपाईले खोज्नु भएको मान 255 वर्ण भन्दा लामो छ
सावधान रहनुहोस्: कार्य VPR 255 वर्णहरू भन्दा बढी भएको मानहरू खोज्न सक्दैन। यदि तपाईले खोज्नु भएको मानले यो सीमा नाघ्यो भने, तपाईले त्रुटि सन्देश प्राप्त गर्नुहुनेछ। #VALUE!.
निर्णय: सुविधाहरूको गुच्छा प्रयोग गर्नुहोस् INDEX+MATCH (INDEX + MATCH)। तल एक सूत्र छ जुन यो कार्यको लागि ठीक हुनेछ:
=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 पानामा पाना २ कार्यपुस्तिकामा नयाँ मूल्यहरू र स्तम्भबाट सम्बन्धित मान निकाल्नुहोस् D.
यदि तालिका पथको कुनै पनि भाग हटाइएको छ भने, तपाईंको प्रकार्य VPR काम गर्दैन र त्रुटि रिपोर्ट गर्नेछ #VALUE! (भले पनि लुकअप तालिका संग कार्यपुस्तिका हाल खुला छ)।
कार्यको बारेमा थप जानकारीको लागि VPRअर्को एक्सेल फाइल सन्दर्भ गर्दै, पाठ हेर्नुहोस्: VLOOKUP प्रयोग गरेर अर्को कार्यपुस्तिका खोज्दै।
3. तर्क स्तम्भ_संख्या १ भन्दा कम छ
यो एक स्थिति को कल्पना गर्न गाह्रो छ जहाँ कसैले एक भन्दा कम मूल्य प्रविष्ट गर्दछ 1मान निकाल्ने स्तम्भ संकेत गर्न। यद्यपि यो सम्भव छ यदि यो तर्कको मान अर्को एक्सेल प्रकार्य द्वारा गणना गरिएको छ VPR.
त्यसोभए, यदि यो हुन्छ कि तर्क col_index_num (column_number) भन्दा कम 1समारोह VPR त्रुटि पनि रिपोर्ट गर्नेछ #VALUE!.
यदि तर्क col_index_num (column_number) दिइएको array मा स्तम्भहरूको संख्या भन्दा ठूलो छ, VPR त्रुटि रिपोर्ट गर्नेछ #REF! (#SSYL!)
त्रुटि #NAME? VLOOKUP मा
सरल मामला एक गल्ती छ #NAME? (#NAME?) - देखा पर्नेछ यदि तपाईंले गल्तिले त्रुटिको साथ प्रकार्य नाम लेख्नुभयो भने।
समाधान स्पष्ट छ - आफ्नो हिज्जे जाँच!
VLOOKUP ले काम गर्दैन (सीमाहरू, चेतावनी र निर्णयहरू)
बरु जटिल वाक्य रचना को अतिरिक्त, VPR अन्य कुनै पनि एक्सेल प्रकार्य भन्दा धेरै सीमितताहरू छन्। यी सीमितताहरूको कारण, सरल सूत्रहरू संग VPR अक्सर अप्रत्याशित नतिजा निम्त्याउँछ। तल तपाईले धेरै सामान्य परिदृश्यहरूको समाधान पाउनुहुनेछ जहाँ VPR गलत छ।
1. VLOOKUP केस संवेदनशील छैन
समारोह VPR केस बीच भेद गर्दैन र लोअरकेस र अपरकेस क्यारेक्टरहरू समान रूपमा स्वीकार गर्दछ। त्यसकारण, यदि त्यहाँ तालिकामा धेरै तत्वहरू छन् जुन केसमा मात्र फरक छ भने, VLOOKUP प्रकार्यले फेला परेको पहिलो तत्व फिर्ता गर्नेछ, केसको परवाह नगरी।
निर्णय: अर्को एक्सेल प्रकार्य प्रयोग गर्नुहोस् जसले ठाडो खोज (LOOKUP, SUMPRODUCT, INDEX, र MATCH) सँग संयोजनमा प्रदर्शन गर्न सक्छ। अचूकA जसले केसलाई फरक पार्छ। थप विवरणहरूको लागि, तपाईंले पाठबाट सिक्न सक्नुहुन्छ - एक्सेलमा VLOOKUP केस-सेन्सेटिभ बनाउने ४ तरिकाहरू।
2. VLOOKUP ले भेटिएको पहिलो मान फर्काउँछ
तपाईंलाई पहिले नै थाहा छ, VPR फेला परेको पहिलो मेलसँग मिल्दोजुल्दो दिइएको स्तम्भबाट मान फर्काउँछ। जे होस्, तपाईले यसलाई 2nd, 3rd, 4th, वा तपाईले चाहानु भएको मानको कुनै अन्य पुनरावृत्ति निकाल्न सक्नुहुन्छ। यदि तपाइँ सबै डुप्लिकेट मानहरू निकाल्न आवश्यक छ भने, तपाइँलाई प्रकार्यहरूको संयोजन चाहिन्छ INDEX (INDEX), कम (सानो) र लाइन (ROW)।
3. तालिकामा स्तम्भ थपियो वा हटाइयो
दुर्भाग्यवश, सूत्रहरू VPR लुकअप तालिकामा नयाँ स्तम्भ थप्दा वा हटाउँदा प्रत्येक पटक काम गर्न रोक्नुहोस्। यो सिन्ट्याक्सको कारणले हुन्छ VPR तपाईले खोजीको पूर्ण दायरा र डेटा निकासीको लागि निर्दिष्ट स्तम्भ नम्बर निर्दिष्ट गर्न आवश्यक छ। स्वाभाविक रूपमा, तपाईंले स्तम्भ मेटाउँदा वा नयाँ घुसाउँदा दिइएको दायरा र स्तम्भ नम्बर दुवै परिवर्तन हुन्छ।
निर्णय: र फेरि प्रकार्यहरू मद्दत गर्न हतारमा छन् INDEX (INDEX) र थप खुलासा (MATCH)। सूत्र मा INDEX+MATCH तपाईले खोज र पुन: प्राप्ति स्तम्भहरू अलग-अलग परिभाषित गर्नुहुन्छ, र परिणाम स्वरूप, तपाईले सबै सम्बन्धित खोज सूत्रहरू अद्यावधिक गर्ने बारे चिन्ता नगरी आफूले चाहेको जति स्तम्भहरू मेटाउन वा सम्मिलित गर्न सक्नुहुन्छ।
4. सूत्र प्रतिलिपि गर्दा सेल सन्दर्भहरू विकृत हुन्छन्
यो शीर्षकले समस्याको सारलाई पूर्ण रूपमा व्याख्या गर्छ, हैन?
निर्णय: सधैं निरपेक्ष कक्ष सन्दर्भहरू प्रयोग गर्नुहोस् (चिन्हको साथ $) दायरा रेकर्डमा, उदाहरणका लागि $A$2:$C$100 or $A:$C। सूत्र पट्टीमा, तपाईं क्लिक गरेर द्रुत रूपमा लिङ्क प्रकार स्विच गर्न सक्नुहुन्छ F4.
VLOOKUP - IFERROR र ISERROR प्रकार्यहरूसँग काम गर्दै
यदि तपाइँ त्रुटि सन्देशहरूसँग प्रयोगकर्ताहरूलाई डराउन चाहनुहुन्न भने # N / A, #VALUE! or #NAME?, तपाईं खाली सेल वा आफ्नो सन्देश देखाउन सक्नुहुन्छ। तपाईं राखेर यो गर्न सक्नुहुन्छ VPR एक समारोह मा 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;ЛОЖЬ);"")
यदि तपाइँ प्रकार्यको मानक त्रुटि सन्देशको सट्टा आफ्नै सन्देश प्रदर्शन गर्न चाहनुहुन्छ भने VPR, यसलाई उद्धरणहरूमा राख्नुहोस्, जस्तै:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: ISERROR प्रकार्यसँग काम गर्दै
समारोह देखि IFERROR एक्सेल 2007 मा देखा पर्यो, अघिल्लो संस्करणहरूमा काम गर्दा तपाईंले संयोजन प्रयोग गर्नुपर्नेछ IF (IF) र EOSHIBKA (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;ЛОЖЬ))
आजको लागि यति हो। मलाई आशा छ कि यो छोटो ट्यूटोरियलले तपाईलाई सबै सम्भावित गल्तीहरूको सामना गर्न मद्दत गर्नेछ। VPR र तपाईंको सूत्रहरू सही रूपमा काम गर्नुहोस्।