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),”Не найдено”)))
Итак, наша формула «сцепленных ВПР» выполняет поиск в трех разных листах в том порядке, в котором мы их вложили в формулу, и выводит первое найденное совпадение:
Вот как вы используете ЕСЛИОШИБКА с функцией ВПР в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Доступные загрузки
Примеры формулы Excel ЕСЛИОШИБКА ВПР
