Мундариҷа
Ин дарс мефаҳмонад, ки чӣ тавр ба зудӣ бо вазъияте, ки функсия кор мекунад VPR (VLOOKUP) намехоҳад дар Excel 2013, 2010, 2007 ва 2003 кор кунад ва чӣ гуна хатогиҳои умумиро муайян ва ислоҳ кардан ва маҳдудиятҳоро бартараф кардан мумкин аст VPR.
Дар якчанд мақолаҳои қаблӣ мо ҷанбаҳои гуногуни ин функсияро омӯхта будем VPR дар Excel. Агар шумо онҳоро бодиққат хонда бошед, акнун шумо бояд мутахассиси ин соҳа бошед. Бо вуҷуди ин, бесабаб нест, ки бисёре аз коршиносони Excel боварӣ доранд VPR яке аз хусусиятхои мураккабтар. Он дорои як қатор маҳдудиятҳо ва хусусиятҳоест, ки манбаи мушкилот ва хатогиҳои зиёд мешаванд.
Дар ин мақола шумо шарҳҳои оддии хатогиҳоро хоҳед ёфт #AT (#Н/А), #NAME? (#NAME?) ва # АРЗИШ! (#VALUE!), ки ҳангоми кор бо функсия пайдо мешаванд VPR, инчунин техника ва усулхои мубориза бо онхо. Мо бо ҳолатҳои маъмултарин ва сабабҳои равшантарин оғоз хоҳем кард. VPR кор намекунад, аз ин рӯ беҳтар аст, ки мисолҳоро бо тартиби дар мақола овардашуда омӯзем.
Ислоҳи хатои #N/A дар функсияи VLOOKUP дар Excel
Дар формулаҳо бо VPR хабари хато #AT (#Н/А) маънои онро дорад дастрас нест (бе маълумот) - вақте пайдо мешавад, ки Excel арзиши ҷустуҷӯи шуморо наёбад. Ин метавонад бо якчанд сабаб рӯй диҳад.
1. Арзиши дилхоҳ хато навишта шудааст
Идеяи хубест, ки аввал ин ашёро тафтиш кунед! Хатогиҳои ҳарфӣ аксар вақт ҳангоми кор кардан бо миқдори хеле зиёди маълумот, ки аз ҳазорҳо сатр иборатанд ё вақте ки арзиши ҷустуҷӯи шумо дар формула навишта мешавад, рух медиҳад.
2. #Н/А хатогӣ ҳангоми ҷустуҷӯи мувофиқати тахминӣ бо VLOOKUP
Агар шумо формуларо бо шарти ҷустуҷӯи тахминии мувофиқ, яъне аргумент истифода баред диапазон_ҷустуҷӯ (диапазони_ҷустуҷӯ) ДУРУСТ аст ё муайян нашудааст, формулаи шумо метавонад дар бораи хато хабар диҳад # Н / А дар ду ҳолат:
- Қимати ҷустуҷӯ аз арзиши хурдтарин дар массиве, ки ҷустуҷӯ мешавад, камтар аст.
- Сутуни ҷустуҷӯ бо тартиби афзоиш мураттаб карда нашудааст.
3. #Н/А хатогӣ ҳангоми ҷустуҷӯи мувофиқати дақиқ бо VLOOKUP
Агар шумо дар ҷустуҷӯи мувофиқати дақиқ, яъне далел диапазон_ҷустуҷӯ (range_lookup) FALSE аст ва арзиши дақиқ ёфт нашуд, формула инчунин дар бораи хато хабар медиҳад # Н / А. Дар бораи чӣ гуна ҷустуҷӯ кардани мувофиқати дақиқ ва тахминӣ бо функсия бештар маълумот гиред VPR.
4. Сутуни ҷустуҷӯ аз тарафи чап нест
Тавре ки шумо эҳтимол медонед, яке аз маҳдудиятҳои муҳимтарин VPR он аст, ки он наметавонад ба тарафи чап рӯ ба рӯ шавад, аз ин рӯ сутуни ҷустуҷӯ дар ҷадвали шумо бояд аз тарафи чап бошад. Дар амал мо аксар вакт инро фаромуш мекунем, ки ин ба формулаи корношоям ва хатой рох медихад. # Н / А.
Қарор: Агар тағир додани сохтори додаҳо имконнопазир бошад, то сутуни ҷустуҷӯ аз тарафи чап ҷойгир бошад, шумо метавонед маҷмӯи функсияҳоро истифода баред INDEX (ИНДЕКС) ва БЕШТАР ФОШ (MATCH) ҳамчун алтернативаи фасењтар барои VPR.
5. Рақамҳо ҳамчун матн формат карда мешаванд
Манбаи дигари хато # Н / А дар формулаҳо бо VPR рақамҳо дар формати матнӣ дар ҷадвали асосӣ ё ҷадвали ҷустуҷӯ мебошанд.
Ин одатан вақте рух медиҳад, ки шумо маълумотро аз пойгоҳи додаҳои беруна ворид мекунед ё вақте ки шумо пеш аз адад апостроф менависед, то сифри аввалро нигоҳ доред.
Аломатҳои равшантарини рақам дар формати матнӣ дар расми зерин нишон дода шудаанд:
Илова бар ин, рақамҳоро метавон дар формат нигоҳ дошт генерал (Умумй). Дар ин ҳолат, танҳо як хусусияти намоён вуҷуд дорад - рақамҳо ба канори чапи ячейка, дар ҳоле ки ба таври нобаёнӣ онҳо ба канори рост рост карда мешаванд.
Қарор: Агар он як арзиши ягона бошад, танҳо тасвири хаторо клик кунед ва интихоб кунед Ба рақам табдил диҳед (Табдил додан ба рақам) аз менюи контекстӣ.
Агар ин ҳолат бо шумораи зиёди рақамҳо бошад, онҳоро интихоб кунед ва дар майдони интихобшуда тугмаи ростро клик кунед. Дар менюи контекстие, ки пайдо мешавад, интихоб кунед Чашмакҳоро формат кунед (Формат ячейка) > ҷадвал шумора (Рақам) > формат шумора (рақамӣ) ва пахш кунед OK.
6. Дар аввал ё дар охир фосила мавҷуд аст
Ин камтарин сабаби равшани хато аст. # Н / А дар функсия VPR, зеро дидани ин ҷойҳои иловагӣ аз ҷиҳати визуалӣ душвор аст, махсусан ҳангоми кор бо ҷадвалҳои калон, вақте ки аксари маълумотҳо берун аз экран мебошанд.
Ҳалли 1: Ҷойҳои иловагӣ дар ҷадвали асосӣ (дар он ҷо функсияи VLOOKUP мавҷуд аст)
Агар дар ҷадвали асосӣ ҷойҳои иловагӣ пайдо шаванд, шумо метавонед боварӣ ҳосил кунед, ки формулаҳо дуруст кор мекунанд. ҷустуҷӯ_арзиш (арзиши_ҷустуҷӯ) ба функсия TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Ҳалли 2: Ҷойҳои иловагӣ дар ҷадвали ҷустуҷӯ (дар сутуни ҷустуҷӯ)
Агар ҷойҳои иловагӣ дар сутуни ҷустуҷӯ бошанд - роҳҳои оддӣ # Н / А дар формула бо VPR пешгирй кардан мумкин нест. Ба ҷои VPR Шумо метавонед формулаи массивро бо маҷмӯи функсияҳо истифода баред 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!) вақте ки арзиши дар формула истифодашуда ба навъи додаҳо мувофиқат намекунад. Дар бораи VPR, пас одатан ду сабаби хато вуҷуд дорад # АРЗИШ!.
1. Арзише, ки шумо ҷустуҷӯ мекунед, аз 255 аломат дарозтар аст
Эҳтиёт бошед: функсия VPR арзишҳои дорои зиёда аз 255 аломатро ҷустуҷӯ карда наметавонанд. Агар арзиши ҷустуҷӯи шумо аз ин маҳдудият зиёд бошад, шумо паёми хатогиро хоҳед гирифт. # АРЗИШ!.
Қарор: Як қатор хусусиятҳоро истифода баред ИНДЕКС+МАТЧ (ИНДЕКС + МУВОФИЦАТ). Дар зер формулае оварда шудааст, ки барои ин вазифа хеле хуб кор мекунад:
=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 дар варақ Лавҳаи 1 дар дафтарчаи мехнатй Нархҳои нав ва арзиши мувофиқро аз сутун ҷудо кунед D.
Агар ягон қисми роҳи ҷадвал партофта шуда бошад, вазифаи шумо VPR кор намекунад ва дар бораи хато хабар медиҳад # АРЗИШ! (ҳатто агар китоби корӣ бо ҷадвали ҷустуҷӯ ҳоло кушода бошад ҳам).
Барои маълумоти бештар дар бораи функсия VPRбо истинод ба файли дигари Excel, ба дарс нигаред: Ҷустуҷӯи китоби дигари корӣ бо истифода аз VLOOKUP.
3. Аргументи Сутуни_рақам аз 1 камтар аст
Тасаввур кардан душвор аст, ки вазъияте, ки касе ба арзиши камтар аз он ворид мешавад 1барои нишон додани сутуне, ки аз он арзиш бароварда мешавад. Ҳарчанд имконпазир аст, агар арзиши ин аргумент аз ҷониби функсияи дигари Excel дар дохили он ҷойгиршуда ҳисоб карда шавад VPR.
Пас, агар он рӯй медиҳад, ки баҳс col_index_um (сутуни_рақам) камтар аз 1функсия VPR инчунин дар бораи хато хабар медиҳад # АРЗИШ!.
Агар бахс col_index_um (рақами_сутун) аз шумораи сутунҳои массиви додашуда зиёд аст, VPR дар бораи хато хабар медиҳад #REF! (#SSYL!).
Хатогии #NAME? дар VLOOKUP
Ҳолати соддатарин хатост #NAME? (#NAME?) - пайдо мешавад, агар шумо тасодуфан номи функсияро бо хато нависед.
Ҳалли равшан аст - имлои худро тафтиш кунед!
VLOOKUP кор намекунад (маҳдудиятҳо, огоҳиҳо ва қарорҳо)
Илова ба синтаксиси хеле мураккаб, VPR нисбат ба дигар функсияҳои Excel маҳдудиятҳои бештар дорад. Аз сабаби ин маҳдудиятҳо, формулаҳои ба назар оддӣ бо VPR аксар вакт ба натичахои гайричашмдошт оварда мерасонад. Дар зер шумо роҳҳои ҳалли якчанд сенарияи маъмулро хоҳед ёфт VPR нодуруст аст.
1. VLOOKUP ба ҳарфи калон ҳассос нест
функсия VPR байни ҳарфҳо фарқ намекунад ва ҳарфҳои хурду калонро якхела қабул мекунад. Аз ин рӯ, агар дар ҷадвал якчанд унсурҳо вуҷуд дошта бошанд, ки танҳо дар ҳолат фарқ мекунанд, функсияи VLOOKUP элементи аввалини ёфтшударо новобаста аз ҳолат бармегардонад.
Қарор: Функсияи дигари Excel-ро истифода баред, ки метавонад ҷустуҷӯи амудиро (Ҷустуҷӯ, Ҷустуҷӯ, ИНДЕКС ва Мутобиқат) дар якҷоягӣ бо ЭЗОҲA, ки парвандаро фарқ мекунад. Барои тафсилоти бештар, шумо метавонед аз дарс омӯзед - 4 роҳи ҳассос кардани VLOOKUP дар Excel.
2. VLOOKUP арзиши аввалини ёфтшударо бармегардонад
Тавре ки шумо аллакай медонед, VPR арзиши аз сутуни додашуда, ки ба бозии аввалини ёфтшуда мувофиқ аст, бармегардонад. Бо вуҷуди ин, шумо метавонед онро 2, 3, 4 ё ҳар гуна такрори арзиши дилхоҳатонро истихроҷ кунед. Агар ба шумо лозим ояд, ки ҳамаи арзишҳои такрориро истихроҷ кунед, ба шумо маҷмӯи функсияҳо лозим мешавад INDEX (ИНДЕКС), ИЛТИМОС (ХУРД) ва LINE (КАТР).
3. Ба ҷадвал сутун илова ё хориҷ карда шуд
Мутаассифона, формулахо VPR ҳар дафъае, ки сутуни нав ба ҷадвали ҷустуҷӯ илова ё хориҷ карда мешавад, корро қатъ кунед. Ин аз сабаби синтаксис рӯй медиҳад VPR аз шумо талаб мекунад, ки доираи пурраи ҷустуҷӯ ва рақами сутуни мушаххасро барои истихроҷи маълумот муайян кунед. Табиист, ки ҳангоми нест кардани сутун ё ворид кардани сутун ҳам диапазони додашуда ва ҳам рақами сутун тағир меёбанд.
Қарор: Ва боз функсияҳо ба кӯмак шитоб мекунанд INDEX (ИНДЕКС) ва БЕШТАР ФОШ (БОЗЙ). Дар формула ИНДЕКС+МАТЧ Шумо сутунҳои ҷустуҷӯ ва ҷустуҷӯро алоҳида муайян мекунед ва дар натиҷа, шумо метавонед бе ташвиш дар бораи навсозии ҳама формулаҳои ҷустуҷӯии алоқаманд ҳар қадар сутунҳоро, ки мехоҳед, нест кунед ё ворид кунед.
4. Ҳангоми нусхабардории формула истинодҳои ҳуҷайраҳо вайрон мешаванд
Ин сарлавҳа моҳияти мушкилотро пурра шарҳ медиҳад, дуруст?
Қарор: Ҳамеша истинодҳои мутлақи ҳуҷайраҳоро истифода баред (бо аломати $) оид ба сабти диапазон, масалан $ A $ 2: $ C $ 100 or $A: $C. Дар сатри формулаҳо шумо метавонед бо пахш кардани тугма зуд намуди пайвандро иваз кунед F4.
VLOOKUP – кор бо функсияҳои IFERROR ва ISERROR
Агар шумо нахоҳед, ки корбаронро бо паёмҳои хато тарсонед # Н / А, # АРЗИШ! or #NAME?, шумо метавонед ячейкаи холӣ ё паёми шахсии худро нишон диҳед. Шумо метавонед инро тавассути ҷойгиркунӣ иҷро кунед VPR ба функсия ХАТО (IFERROR) дар Excel 2013, 2010 ва 2007 ё як қатор функсияҳоро истифода баред АГАР+ХАТО (АГАР+ISERROR) дар версияҳои қаблӣ.
VLOOKUP: кор бо функсияи 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
Азбаски функсия ХАТО дар Excel 2007 пайдо шуд, ҳангоми кор дар версияҳои қаблӣ шумо бояд ин комбинатсияро истифода баред IF (АГАР) ва ЕОШИБКА (ISERROR) чунин:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",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 ва формулаҳои худро дуруст кор кунед.