Excel ЕСЛИОШИБКА И ВПР - ловушка #Н/Д и другие ошибки

В этом руководстве мы рассмотрим, как использовать функции ЕСЛИОШИБКА и ВПР вместе для перехвата и обработки различных ошибок. Кроме того, вы узнаете, как выполнять последовательный поиск в Excel путем вложения нескольких функций ЕСЛИОШИБКА одна в другую.

Excel ВПР и ЕСЛИОШИБКА — эти две функции довольно сложно понять по отдельности, не говоря уже об их сочетании. В этой статье вы найдете несколько простых для понимания примеров, которые относятся к распространенным случаям использования и ясно иллюстрируют логику формул.

Если у вас нет большого опыта работы с функциями ЕСЛИОШИБКА и ВПР, может быть хорошей идеей сначала изучить их основы, следуя приведенным выше ссылкам.

Формула IFERROR VLOOKUP для обработки #N/A и других ошибок

Когда Excel Vlookup не может найти значение поиска, он выдает ошибку #N/A, например:

В зависимости от потребностей вашего бизнеса вы можете замаскировать ошибку собственным текстом, нулем или пустой ячейкой.

Пример 1. ЕСЛИОШИБКА с формулой ВПР для замены ошибок собственным текстом

Если вы хотите заменить стандартную нотацию ошибок своим собственным текстом, оберните формулу ВПР в ЕСЛИОШИБКА и введите любой текст, который вы хотите, во втором аргументе (значение_если_ошибка), например “Не найдено”:

ЕСЛИОШИБКА(ВПР(…),”Не найден”)

Со значением поиска в B2 в основной таблице и диапазоном поиска A2:B4 в таблице поиска формула принимает следующий вид:

=ЕСЛИОШИБКА(ВПР(B2,’Таблица поиска’!$A$2:$B$5, 2, ЛОЖЬ), “Не найдено”)

На приведенном ниже снимке экрана показана наша формула Excel ЕСЛИОШИБКА ВПР в действии:

Результат выглядит гораздо более понятным и гораздо менее пугающим, не так ли?

Аналогичным образом вы можете использовать ПОИСКПОЗ ИНДЕКС вместе с ЕСЛИОШИБКА:

=ЕСЛИОШИБКА(ИНДЕКС(‘Таблица поиска’!$B$2:$B$5,MATCH(B2,’Таблица поиска’!$A$2:$A$5,0)), “Не найдено”)

Формула IFERROR INDEX MATCH особенно полезна, когда вы хотите получить значения из столбца, который находится слева от столбца поиска (левый поиск), и вернуть свой собственный текст, когда ничего не найдено.

Пример 2. ЕСЛИОШИБКА с функцией ВПР возвращает пустое значение или 0, если ничего не найдено.

Если вы не хотите ничего показывать, когда искомое значение не найдено, пусть ЕСЛИОШИБКА отобразит пустую строку (“”):

ЕСЛИОШИБКА(ВПР(…),””)

В нашем примере формула выглядит следующим образом:

=ЕСЛИОШИБКА(ВПР(B2;’Таблица поиска’!$A$2:$B$5, 2, ЛОЖЬ), “”)

Как видите, он ничего не возвращает, если искомое значение отсутствует в списке поиска.

Если вы хотите заменить ошибку на нулевое значение, поставьте 0 в последний аргумент:

=ЕСЛИОШИБКА(ВПР(B2;’Таблица поиска’!$A$2:$B$5, 2, ЛОЖЬ), 0)

Слово предостережения! Функция Excel ЕСЛИОШИБКА отлавливает все виды ошибок, а не только #N/A. Это хорошо или плохо? Все зависит от вашей цели. Если вы хотите замаскировать все возможные ошибки, IFERROR Vlookup — это то, что вам нужно. Но во многих ситуациях это может оказаться неразумным приемом.

Например, если вы создали именованный диапазон для данных таблицы и неправильно написали это имя в формуле ВПР, ЕСЛИОШИБКА поймает ошибку #ИМЯ? error и замените его на «Не найдено» или любым другим текстом, который вы укажете. В результате вы никогда не узнаете, что ваша формула дает неправильные результаты, если только вы сами не заметите опечатку. В таком случае более разумным подходом будет отлавливание только ошибок #Н/Д. Для этого используйте формулу ВПР IFNA в Excel 2013 и выше, IFNA ВПР во всех версиях Excel.

Суть такова: будьте очень внимательны при выборе компаньона для своей формулы ВПР 🙂

Вложите IFERROR в VLOOKUP, чтобы всегда что-то находить

Представьте себе следующую ситуацию: вы ищете определенное значение в списке и не находите его. Какой выбор у вас есть? Либо получите ошибку N/A, либо покажите собственное сообщение. Собственно, есть и третий вариант — если ваш первичный vlookup спотыкается, то ищите что-то еще, что точно есть!

Продолжая наш пример, давайте создадим своего рода панель инструментов для наших пользователей, которая будет показывать им добавочный номер определенного офиса. Что-то вроде этого:

Итак, как же получить расширение из столбца B на основе номера офиса в D2? С помощью этой обычной формулы Vlookup:

=ВПР($D$2,$A$2:$B$7,2,ЛОЖЬ)

И это будет хорошо работать до тех пор, пока ваши пользователи вводят правильный номер в D2. Но что, если пользователь введет несуществующее число? В таком случае пусть звонят в центральный офис! Для этого вы вставляете приведенную выше формулу в ценность аргумент ЕСЛИОШИБКА, и поместите другой ВПР в значение_если_ошибка аргумент.

Полная формула немного длинновата, но работает отлично:

=ЕСЛИОШИБКА(ВПР(“офис”&$D$2,$A$2:$B$7,2,ЛОЖЬ),ВПР(“центральный офис”,$A$2:$B$7,2,ЛОЖЬ))

Если номер офиса найден, пользователь получает соответствующий добавочный номер:

Если номер офиса не найден, отображается добавочный номер центрального офиса:

Чтобы сделать формулу немного более компактной, вы можете использовать другой подход:

Во-первых, проверьте, присутствует ли число в D2 в столбце поиска (обратите внимание, что мы установили col_index_num на 1, чтобы формула искала и возвращала значение из столбца A): ВПР(D2,$A$2:$B$7,1,FALSE)

Если указанный номер офиса не найден, то ищем строку «центральный офис», которая обязательно есть в списке поиска. Для этого вы оборачиваете первую функцию ВПР в ЕСЛИОШИБКА и вкладываете всю эту комбинацию в другую функцию ВПР:

=ВПР(ЕСЛИОШИБКА(ВПР(D2,$A$2:$B$7,1,ЛОЖЬ),”центральный офис”),$A$2:$B$7,2)

Ну немного другая формула, тот же результат:

Но зачем искать «центральный офис», спросите вы. Почему бы не указать добавочный номер непосредственно в IFERROR? Потому что расширение может измениться в какой-то момент в будущем. Если это произойдет, вам придется обновить свои данные только один раз в исходной таблице, не беспокоясь об обновлении каждой из ваших формул ВПР.

Как сделать последовательные ВПР в Excel

В ситуациях, когда вам нужно выполнить так называемые последовательные или цепные ВПР в Excel, в зависимости от того, был ли предыдущий поиск успешным или неудачным, вложите две или более функции ЕСЛИОШИБКА для запуска ваших ВПР один за другим:

ЕСЛИОШИБКА(ВПР(…), ЕСЛИОШИБКА(ВПР(…), ЕСЛИОШИБКА(ВПР(…),”Не найден”)))

Формула работает по следующей логике:

Если первая ВПР ничего не находит, первая функция ЕСЛИОШИБКА перехватывает ошибку и запускает другую ВПР. Если вторая ВПР не удалась, вторая функция ЕСЛИОШИБКА перехватывает ошибку и запускает третью ВПР и так далее. Если все Vlookups спотыкаются, последнее IFERROR возвращает ваше сообщение.

Эта вложенная формула ЕСЛИОШИБКА особенно полезна, когда вам нужно выполнять визуальный поиск на нескольких листах, как показано в приведенном ниже примере.

Допустим, у вас есть три списка однородных данных на трех разных листах (в данном примере это номера офисов), и вы хотите получить расширение для определенного номера.

Предполагая, что значение поиска находится в ячейке A2 на текущем листе, а диапазон поиска составляет A2: B5 на 3 разных листах (север, юг и запад), следующая формула работает:

=ЕСЛИОШИБКА(ВПР(A2,Север!$A$2:$B$5,2,ЛОЖЬ), ЕСЛИОШИБКА(ВПР(A2,Юг!$A$2:$B$5,2,ЛОЖЬ), ЕСЛИОШИБКА(ВПР(A2,Запад) !$A$2:$B$5,2,FALSE),”Не найдено”)))

Итак, наша формула «сцепленных ВПР» выполняет поиск в трех разных листах в том порядке, в котором мы их вложили в формулу, и выводит первое найденное совпадение: