Когато използвате формулата VLOOKUP в Excel, понякога може да се окажете с грозната грешка #N/A. Това се случва, когато формулата ви не може да намери стойността за търсене.
В този урок ще ви покажа различни начини да използвате IFERROR с VLOOKUP за справяне с тези #N/A грешки, изскачащи във вашия работен лист.
Използването на комбинацията от IFERROR с VLOOKUP ви позволява да покажете нещо смислено вместо грешката #N/A (или всяка друга грешка по този въпрос).
Преди да влезем в подробности относно използването на тази комбинация, нека първо преминем бързо през функцията IFERROR и да видим как работи.
Обяснена функция IFERROR
С функцията IFERROR можете да посочите какво трябва да се случи в случай, че формула или препратка към клетка връща грешка.
Ето синтаксиса на функцията IFERROR.
= IFERROR (стойност, value_if_error)
- стойност - това е аргументът, който се проверява за грешка. В повечето случаи това е или формула, или препратка към клетка. Когато използвате VLOOKUP с IFERROR, формулата VLOOKUP ще бъде този аргумент.
- value_if_error - това е стойността, която се връща, ако има грешка. Оценени са следните типове грешки: #N/A, #REF !, #DIV/0 !, #VALUE !, #NUM !, #NAME ?, и #NULL !.
Възможни причини за VLOOKUP връщане на грешка #N/A
Функцията VLOOKUP може да върне грешка #N/A поради някоя от следните причини:
- Стойността за търсене не е намерена в масива за търсене.
- В стойността за търсене (или в масива на таблици) има водещо, задно или двойно пространство.
- Има правописна грешка в стойността за търсене или стойностите в масива за търсене.
Можете да се справите с всички тези причини за грешка с комбинацията от IFERROR и VLOOKUP. Трябва обаче да следите за причина 2 и 3 и да ги коригирате в изходните данни, вместо да оставите IFERROR да ги обработва.
Забележка: IFERROR ще третира грешка, независимо от причината. Ако искате само да лекувате грешките, причинени от това, че VLOOKUP не може да намери стойността за търсене, използвайте вместо това IFNA. Това ще гарантира, че грешки, различни от #N/A, не се третират и можете да проучите тези други грешки.
Можете да третирате водещи, последни и двойни интервали с помощта на функцията TRIM.
Замяна на грешка VLOOKUP #N/A със смислен текст
Да предположим, че имате набор от данни, както е показано по -долу:
Както можете да видите, че формулата VLOOKUP връща грешка, тъй като стойността за търсене не е в списъка. Търсим да получим резултата за Глен, който не е в таблицата с резултати.
Въпреки че това е много малък набор от данни, може да получите огромни набори от данни, където трябва да проверите появата на много елементи. За всеки случай, когато стойността не е намерена, ще получите грешка #N/A.
Ето формулата, която можете да използвате, за да получите нещо смислено вместо грешката #N/A.
= ГРЕШКА (VLOOKUP (D2, $ A $ 2: $ B $ 10,2,0), "Не е намерено")
Горната формула връща текста „Not Found“ вместо грешката #N/A. Можете също да използвате същата формула, за да върнете празен, нулев или друг смислен текст.
Вложен VLOOKUP с функция IFERROR
В случай, че използвате VLOOKUP и вашата таблица за търсене е фрагментирана на същия работен лист или различни работни листове, трябва да проверите стойността на VLOOKUP през всички тези таблици.
Например в показания по -долу набор от данни има две отделни таблици с имената на учениците и резултатите.
Ако трябва да намеря оценката на Grace в този набор от данни, трябва да използвам функцията VLOOKUP, за да проверя първата таблица и ако стойността не е намерена в нея, проверете втората таблица.
Ето вложената формула IFERROR, която мога да използвам, за да търся стойността:
= IFERROR (VLOOKUP (G3, $ A $ 2: $ B $ 5,2,0), IFERROR (VLOOKUP (G3, $ D $ 2: $ E $ 5,2,0), "Not Found"))
Използване на VLOOKUP с IF и ISERROR (версии преди Excel 2007)
Функцията IFERROR е въведена в Excel 2007 за Windows и Excel 2016 в Mac.
Ако използвате предишните версии, функцията IFERROR няма да работи във вашата система.
Можете да възпроизведете функционалността на функцията IFERROR, като използвате комбинацията от функцията IF и функцията ISERROR.
Позволете ми бързо да ви покажа как да използвате комбинацията от IF и ISERROR вместо IFERROR.
В горния пример, вместо да използвате IFERROR, можете също да използвате формулата, показана в клетка B3:
= IF (ISERROR (A3), „Not Found“, A3)
Частта ISERROR във формулата проверява за грешките (включително грешката #N/A) и връща TRUE, ако е открита грешка, и FALSE, ако не.
- Ако е TRUE (което означава, че има грешка), функцията IF връща определената стойност („В този случай не е намерено“).
- Ако е FALSE (което означава, че няма грешка), функцията IF връща тази стойност (A3 в горния пример).
IFERROR VS IFNA
IFERROR третира всички видове грешки, докато IFNA третира само грешката #N/A.
Когато боравите с грешки, причинени от VLOOKUP, трябва да се уверите, че използвате правилната формула.
Използвайте IFERROR когато искате да лекувате всякакви грешки. Сега грешка може да бъде причинена от различни фактори (като грешна формула, неправилно изписан именен диапазон, не намиране на стойността за търсене и връщане на стойността на грешката от таблицата за търсене). Няма да има значение за IFERROR и той ще замени всички тези грешки с посочената стойност.
Използвайте IFNA когато искате да третирате само #N/A грешки, които е по -вероятно да бъдат причинени от формулата VLOOKUP, която не може да намери стойността за търсене.
Може да намерите полезни и следните уроци по Excel:
- Как да направим VLOOKUP чувствителен към регистъра.
- VLOOKUP Vs. ИНДЕКС/МАТЧ - Дебатът приключва тук!
- Използвайте VLookup, за да получите последния номер в списък в Excel.
- Как да използвате VLOOKUP с множество критерии
- Грешка #NAME в Excel - какво я причинява и как да я поправя!
