අන්තර්ගතය
මෙම පාඩම මඟින් කාර්යයක් ඇති අවස්ථාවක ඉක්මනින් කටයුතු කරන්නේ කෙසේද යන්න පැහැදිලි කරයි VPR (VLOOKUP) Excel 2013, 2010, 2007 සහ 2003 හි වැඩ කිරීමට අවශ්‍ය නැත, සහ පොදු දෝෂ හඳුනාගෙන ඒවා නිවැරදි කරන්නේ කෙසේද සහ සීමාවන් ජය ගන්නේ කෙසේද VPR.
පෙර ලිපි කිහිපයකදී, අපි ශ්‍රිතයේ විවිධ පැතිකඩ ගවේෂණය කර ඇත්තෙමු VPR එක්සෙල් හි. ඔබ ඒවා හොඳින් කියවා ඇත්නම්, ඔබ දැන් මෙම ක්ෂේත්‍රයේ ප්‍රවීණයෙකු විය යුතුය. කෙසේ වෙතත්, බොහෝ එක්සෙල් විශේෂඥයින් විශ්වාස කරන්නේ හේතුවක් නොමැතිව නොවේ VPR වඩාත් සංකීර්ණ ලක්ෂණ වලින් එකක්. එය බොහෝ ගැටළු සහ දෝෂ වල මූලාශ්‍රය බවට පත්වන සීමාවන් සහ විශේෂාංග රාශියක් ඇත.
මෙම ලිපියෙන් ඔබට දෝෂ පිළිබඳ සරල පැහැදිලි කිරීම් සොයාගත හැකිය #හිදී (#N/A), #නාමය? (#නම සහ #අගය! (#VALUE!) ශ්‍රිතය සමඟ වැඩ කරන විට දිස්වේ VPR, මෙන්ම ඔවුන් සමඟ කටයුතු කිරීමේ ශිල්පීය ක්රම සහ ක්රම. අපි වඩාත් පොදු අවස්ථා සහ වඩාත් පැහැදිලි හේතු සමඟ ආරම්භ කරමු. VPR ක්රියා නොකරයි, එබැවින් ඒවා ලිපියේ දක්වා ඇති අනුපිළිවෙලෙහි උදාහරණ අධ්යයනය කිරීම වඩා හොඳය.
Excel හි VLOOKUP ශ්‍රිතයේ #N/A දෝෂය නිවැරදි කිරීම
සමඟ සූත්රවල VPR දෝෂ පණිවිඩය #හිදී (#N/A) යනු ලද නොහැක (දත්ත නැත) - Excel ඔබ සොයන අගය සොයාගත නොහැකි වූ විට දිස්වේ. මෙය හේතු කිහිපයක් නිසා සිදු විය හැක.
1. අපේක්ෂිත අගය වැරදි ලෙස අක්ෂර වින්‍යාස කර ඇත
පළමුව මෙම අයිතමය පරීක්ෂා කිරීම හොඳ අදහසකි! බොහෝ විට ඔබ ඉතා විශාල දත්ත ප්‍රමාණයක් සමඟ වැඩ කරන විට, පේළි දහස් ගණනකින් සමන්විත වන විට, හෝ ඔබ සොයන අගය සූත්‍රයක ලියා ඇති විට බොහෝ විට යතුරු ලියනය සිදුවේ.
2. VLOOKUP සමඟ ආසන්න ගැළපීමක් සෙවීමේදී #N/A දෝෂයකි
ඔබ ආසන්න ගැළපුම් සෙවුම් තත්වයක් සහිත සූත්රයක් භාවිතා කරන්නේ නම්, එනම් තර්කය පරාසය_සොයන්න (range_lookup) සත්‍ය හෝ නිශ්චිතව දක්වා නැත, ඔබගේ සූත්රය දෝෂයක් වාර්තා කළ හැක # එන් / ඒ අවස්ථා දෙකකදී:
- ඉහළට බැලිය යුතු අගය, උඩ බලන අරාවේ කුඩාම අගයට වඩා අඩුය.
- සෙවුම් තීරුව ආරෝහණ අනුපිළිවෙලට වර්ග කර නැත.
3. VLOOKUP සමඟ නිශ්චිත ගැලපීමක් සොයන විට #N/A දෝෂයකි
ඔබ හරියටම ගැළපීමක් සොයන්නේ නම්, එනම් තර්කය පරාසය_සොයන්න (range_lookup) අසත්‍ය වන අතර නියම අගය සොයාගත නොහැකි විය, සූත්රය ද දෝෂයක් වාර්තා කරයි # එන් / ඒ. ශ්‍රිතයක් සමඟ නිවැරදි සහ ආසන්න ගැළපීම් සොයන ආකාරය ගැන තව දැන ගන්න VPR.
4. සෙවුම් තීරුව වම්පස නැත
ඔබ දන්නා පරිදි, වඩාත්ම වැදගත් සීමාවන්ගෙන් එකකි VPR එය වමට මුහුණ දිය නොහැක, එබැවින් ඔබගේ වගුවේ සෙවීම් තීරුව වම්පස විය යුතුය. ප්රායෝගිකව, අපි බොහෝ විට මේ ගැන අමතක කරමු, එය වැඩ නොකරන සූත්රයක් සහ දෝෂයක් ඇති කරයි. # එන් / ඒ.
තීරණ: සෙවුම් තීරුව වම්පස ඇති පරිදි දත්ත ව්‍යුහය වෙනස් කිරීමට නොහැකි නම්, ඔබට ශ්‍රිතවල එකතුවක් භාවිතා කළ හැක. ඉන්ඩෙක්ස් (INDEX) සහ වඩාත් නිරාවරණය (MATCH) සඳහා වඩාත් නම්‍යශීලී විකල්පයක් ලෙස VPR.
5. ඉලක්කම් පෙළ ලෙස ෆෝමැට් කර ඇත
දෝෂයේ තවත් මූලාශ්‍රයක් # එන් / ඒ සමඟ සූත්රවල VPR ප්‍රධාන වගුවේ හෝ බැලීමේ වගුවේ පෙළ ආකෘතියේ අංක වේ.
මෙය සාමාන්යයෙන් සිදු වන්නේ ඔබ බාහිර දත්ත සමුදායෙන් තොරතුරු ආයාත කරන විට හෝ ප්‍රමුඛ බිංදුව තබා ගැනීමට සංඛ්‍යාවකට පෙර අපෝස්ට්‍රොෆි ටයිප් කරන විටය.
පෙළ ආකෘතියේ අංකයක වඩාත් පැහැදිලි සලකුණු පහත රූපයේ දැක්වේ:
ඊට අමතරව, අංක ආකෘතියෙන් ගබඩා කළ හැකිය ජනරාල් (ජනරාල්). මෙම අවස්ථාවෙහිදී, එක් කැපී පෙනෙන ලක්ෂණයක් පමණක් පවතී - සංඛ්යා සෛලයේ වම් කෙළවරට පෙළගස්වා ඇති අතර, පෙරනිමියෙන් ඒවා දකුණු කෙළවරට පෙලගැසී ඇත.
තීරණ: එය තනි අගයක් නම්, දෝෂ නිරූපකය මත ක්ලික් කර තෝරන්න අංකයට පරිවර්තනය කරන්න (අංකයට පරිවර්තනය කරන්න) සන්දර්භය මෙනුවෙන්.
බොහෝ අංක සමඟ තත්වය මෙය නම්, ඒවා තෝරා තෝරාගත් ප්රදේශය මත දකුණු-ක්ලික් කරන්න. දිස්වන සන්දර්භය මෙනුවේ, තෝරන්න ආකෘති සෛල (ආකෘතිය කොටු) > ටැබය අංකය (අංක)> ආකෘතිය අංකය (සංඛ්යා) සහ ඔබන්න OK.
6. ආරම්භයේ හෝ අවසානයේ අවකාශයක් ඇත
දෝෂය සඳහා අවම වශයෙන් පැහැදිලි හේතුව මෙයයි. # එන් / ඒ කාර්යයේ VPR, විශේෂයෙන්ම විශාල වගු සමඟ වැඩ කරන විට, බොහෝ දත්ත තිරයෙන් පිටත පවතින විට, මෙම අමතර අවකාශයන් දැකීම දෘශ්‍යමය වශයෙන් අපහසු බැවින්.
විසඳුම 1: ප්‍රධාන වගුවේ අමතර හිස්තැන් (VLOOKUP ශ්‍රිතය ඇති තැන)
ප්‍රධාන වගුවේ අමතර හිස්තැන් දිස්වන්නේ නම්, තර්කය ඇතුළත් කිරීමෙන් සූත්ර නිවැරදිව ක්රියා කරන බවට ඔබට සහතික විය හැක. බලන්න_ වටිනාකම (lookup_value) ශ්‍රිතයක් බවට TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
විසඳුම 2: සෙවීම් වගුවේ අමතර ඉඩ (සෙවුම් තීරුවේ)
අමතර අවකාශයන් සෙවුම් තීරුවේ තිබේ නම් - සරල ක්රම # එන් / ඒ සමඟ සූත්රයෙහි VPR මග හැරිය නොහැක. වෙනුවට VPR ඔබට ශ්‍රිත එකතුවක් සහිත අරා සූත්රයක් භාවිතා කළ හැක ඉන්ඩෙක්ස් (INDEX), වඩාත් නිරාවරණය (MATCH) සහ 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 + ඇතුල් කරන්න සුපුරුදු වෙනුවට ඇතුලත් කරන්නසූත්‍රය නිවැරදිව ඇතුළත් කිරීමට.
දෝෂය #VALUE! VLOOKUP සමඟ සූත්රවල
බොහෝ අවස්ථාවලදී, Microsoft Excel දෝෂයක් වාර්තා කරයි #අගය! (#VALUE!) සූත්‍රයේ භාවිතා කරන අගය දත්ත වර්ගයට නොගැලපෙන විට. සම්බන්ධයෙනි VPR, එවිට දෝෂය සඳහා සාමාන්යයෙන් හේතු දෙකක් තිබේ #අගය!.
1. ඔබ සොයන අගය අක්ෂර 255 ට වඩා දිගු වේ
ප්රවේශම් වන්න: කාර්යය VPR අක්ෂර 255 ට වඩා වැඩි අගයන් සෙවිය නොහැක. ඔබ සොයන අගය මෙම සීමාව ඉක්මවා ගියහොත්, ඔබට දෝෂ පණිවිඩයක් ලැබෙනු ඇත. #අගය!.
තීරණ: විශේෂාංග සමූහයක් භාවිතා කරන්න 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 වැඩ නොකරන අතර දෝෂයක් වාර්තා කරනු ඇත #අගය! (සෙවුම් වගුව සහිත වැඩපොත දැනට විවෘතව තිබුණත්).
කාර්යය පිළිබඳ වැඩි විස්තර සඳහා VPRවෙනත් Excel ගොනුවක් යොමු කරමින්, පාඩම බලන්න: VLOOKUP භාවිතයෙන් වෙනත් වැඩපොතක් සෙවීම.
3. තර්ක තීරු_සංඛ්‍යාව 1 ට වඩා අඩුය
කෙනෙකුට වඩා අඩු අගයක් ඇතුළත් කරන තත්වයක් ගැන සිතීම දුෂ්කර ය 1අගය උකහා ගත යුතු තීරුව දැක්වීමට. මෙම තර්කයේ අගය තවත් එක්සෙල් ශ්‍රිතයක් මඟින් ගණනය කළහොත් එය කළ හැකි වුවද VPR.
ඉතින් එහෙම උනොත් තර්කය col_index_num (තීරු_අංකය) ට වඩා අඩුය 1ක්රියාව VPR දෝෂයක් ද වාර්තා කරනු ඇත #අගය!.
තර්කය නම් col_index_num (column_number) ලබා දී ඇති අරාවේ ඇති තීරු ගණනට වඩා වැඩිය, VPR දෝෂයක් වාර්තා කරනු ඇත #REF! (#SSYL!).
දෝෂය #NAME? VLOOKUP හි
සරලම නඩුව වැරදීමකි #නාමය? (#NAME?) - ඔබ වැරදීමකින් ශ්‍රිත නාමයක් දෝෂයක් සමඟ ලිව්වහොත් දිස්වනු ඇත.
විසඳුම පැහැදිලිය - ඔබේ අක්ෂර වින්යාසය පරීක්ෂා කරන්න!
VLOOKUP ක්රියා නොකරයි (සීමාවන්, අවවාද සහ තීරණ)
තරමක් සංකීර්ණ වාක්‍ය ඛණ්ඩයට අමතරව, VPR වෙනත් ඕනෑම Excel කාර්යයකට වඩා වැඩි සීමාවන් ඇත. මෙම සීමාවන් නිසා, පෙනෙන පරිදි සරල සූත්ර VPR බොහෝ විට අනපේක්ෂිත ප්රතිඵලවලට තුඩු දෙයි. පහත දැක්වෙන පොදු අවස්ථා කිහිපයක් සඳහා ඔබට විසඳුම් සොයාගත හැකිය VPR වැරදියි.
1. VLOOKUP සිද්ධි සංවේදී නොවේ
කාර්යයන් VPR නඩුව අතර වෙනස හඳුනා නොගන්නා අතර කුඩා අකුරු සහ ලොකු අකුරු එකම ලෙස පිළිගනී. එම නිසා, වගුවේ ඇති අවස්ථාවකදී පමණක් වෙනස් වන මූලද්‍රව්‍ය කිහිපයක් තිබේ නම්, VLOOKUP ශ්‍රිතය, සිද්ධිය කුමක් වුවත්, සොයාගත් පළමු මූලද්‍රව්‍යය ආපසු ලබා දෙනු ඇත.
තීරණ: සමඟ ඒකාබද්ධව සිරස් සෙවුමක් (LOOKUP, SUMPRODUCT, INDEX, සහ MATCH) සිදු කළ හැකි වෙනත් Excel ශ්‍රිතයක් භාවිතා කරන්න නිවැරදිනඩුව වෙන්කර හඳුනා ගන්නා A. වැඩි විස්තර සඳහා, ඔබට පාඩමෙන් ඉගෙන ගත හැකිය - එක්සෙල් හි VLOOKUP කේස්-සංවේදී කිරීමට ක්රම 4 ක්.
2. VLOOKUP සොයාගත් පළමු අගය ලබා දෙයි
ඔබ දැනටමත් දන්නා පරිදි, VPR සොයාගත් පළමු ගැලපුමට අනුරූප වන දී ඇති තීරුවෙන් අගය ලබා දෙයි. කෙසේ වෙතත්, ඔබට එය ඔබට අවශ්‍ය අගයේ 2, 3, 4, හෝ වෙනත් ඕනෑම පුනරාවර්තනයක් උකහා ගත හැක. ඔබට සියලුම අනුපිටපත් අගයන් උකහා ගැනීමට අවශ්‍ය නම්, ඔබට ශ්‍රිතවල එකතුවක් අවශ්‍ය වේ ඉන්ඩෙක්ස් (INDEX), අවම (කුඩා) සහ ලයින් (ROW).
3. වගුවට තීරුවක් එකතු කර හෝ ඉවත් කර ඇත
අවාසනාවට, සූත්ර VPR සෙවීම් වගුවට නව තීරුවක් එකතු කරන විට හෝ ඉවත් කරන සෑම අවස්ථාවකම වැඩ කිරීම නවත්වන්න. මෙය සින්ටැක්ස් නිසා සිදු වේ VPR සෙවුමේ සම්පූර්ණ පරාසය සහ දත්ත නිස්සාරණය සඳහා නිශ්චිත තීරු අංකය සඳහන් කිරීමට ඔබට අවශ්‍ය වේ. ස්වාභාවිකවම, ඔබ තීරුවක් මකා දැමූ විට හෝ අලුත් එකක් ඇතුළු කළ විට දී ඇති පරාසය සහ තීරු අංකය යන දෙකම වෙනස් වේ.
තීරණ: නැවතත් කාර්යයන් උදව් කිරීමට ඉක්මන් වේ ඉන්ඩෙක්ස් (INDEX) සහ වඩාත් නිරාවරණය (තරගය). සූත්‍රයේ INDEX+MATCH ඔබ සෙවුම් සහ නැවත ලබා ගැනීමේ තීරු වෙන වෙනම නිර්වචනය කරන අතර, එහි ප්‍රතිඵලයක් වශයෙන්, ඔබට අදාළ සියලුම සෙවුම් සූත්ර යාවත්කාලීන කිරීම ගැන කරදර නොවී ඔබට අවශ්‍ය තරම් තීරු මකා දැමීමට හෝ ඇතුළු කිරීමට හැකිය.
4. සූත්රයක් පිටපත් කිරීමේදී සෛල යොමු කිරීම් අවුල් වේ
මෙම මාතෘකාව ගැටලුවේ සාරය සවිස්තරාත්මකව පැහැදිලි කරයි, හරිද?
තීරණ: සෑම විටම නිරපේක්ෂ සෛල යොමු භාවිතා කරන්න (සංකේතය සමඟ $) වාර්තා වල පරාසය, උදාහරණයක් ලෙස $A$2:$C$100 or $A:$C. සූත්ර තීරුවේ, ක්ලික් කිරීමෙන් ඔබට සබැඳි වර්ගය ඉක්මනින් මාරු කළ හැකිය F4.
VLOOKUP - IFERROR සහ ISERROR යන කාර්යයන් සමඟ වැඩ කිරීම
ඔබට දෝෂ පණිවිඩ මගින් පරිශීලකයින් බිය ගැන්වීමට අවශ්‍ය නැතිනම් # එන් / ඒ, #අගය! or #නාමය?, ඔබට හිස් කොටුවක් හෝ ඔබේම පණිවිඩයක් පෙන්විය හැක. තැබීමෙන් ඔබට මෙය කළ හැකිය 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 Excel 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 සහ ඔබේ සූත්ර නිවැරදිව ක්‍රියාත්මක කරන්න.