Когато използвате формули за VLOOKUP в Excel, понякога може да срещнете грозни #N/A грешки.Това се случва, когато формулата ви не може да намери търсената стойност.
В този урок ще ви покажа различните начини да използвате IFERROR и VLOOKUP за справяне с тези #N/A грешки в работни листове.
Използвайте IFERROR в комбинация с VLOOKUP, за да покажете нещо смислено на мястото на грешка #N/A (или друга грешка).
Преди да влезем в подробности как да използваме тази комбинация, нека да разгледаме бързо функцията IFERROR, за да видим как работи.
Съдържание
Описание на функцията IFERROR
Използвайки функцията IFERROR, можете да посочите какво трябва да се случи, когато препратка към формула или клетка върне грешка.
Това е синтаксисът на функцията IFERROR.
=АКО ГРЕШКА(стойност, стойност_ако_грешка)
- стойност - Това е параметърът за проверка за грешки.В повечето случаи това е или формула, или препратка към клетка.Когато използвате VLOOKUP с IFERROR, формулата VLOOKUP ще бъде този параметър.
- стойност_ако_грешка – Това е стойността, върната при възникване на грешка.Бяха оценени следните типове грешки: #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),"Не е намерено")
Горната формула връща текста „Не е намерен“ вместо грешка #N/A.Можете също да използвате същата формула, за да върнете празни места, нули или друг смислен текст.
Вложена VLOOKUP с функция IFERROR
Ако използвате VLOOKUP и вашите справочни таблици са разпределени в един и същ работен лист или различни работни листове, трябва да проверите стойността на VLOOKUP през всички тези таблици.
Например, в набора от данни, показан по-долу, има две отделни таблици с имена на ученици и резултати.
Ако трябва да намеря резултата на Грейс в този набор от данни, трябва да използвам функцията 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:
=АКО(ISERROR(A3),"Не е намерено",A3)
Частта ISERROR на формулата проверява за грешки (включително #N/A грешки) и връща TRUE, ако е открита грешка, FALSE в противен случай.
- Ако е TRUE (показва грешка), функцията IF връща определената стойност („Не е намерено“ в този случай).
- Ако FALSE (което означава, че няма грешка), функцията IF ще върне тази стойност (A3 в примера по-горе).
IFERROR и IFNA
IFERROR обработва всички видове грешки, докато IFNA обработва само #N/A грешки.
Когато се справяте с грешки, причинени от VLOOKUP, трябва да сте сигурни, че използвате правилната формула.
Когато искате да обработвате различни грешки, моляИзползвайте IFERROR.Грешките вече могат да бъдат причинени от различни фактори (като грешни формули, грешно изписани наименовани диапазони, стойности за търсене не са намерени и грешни стойности, върнати от справочните таблици).АКО ГРЕШКА няма значение, заменя всички тези грешки с посочената стойност.
Използвайте IFNA, когато искате да обработвате само #N/A грешки, което по-вероятно е причинено от това, че формулата VLOOKUP не може да намери търсената стойност.
