ЕСЛИ ОШИБКА Формула ВПР в Excel и ее альтернативы
В этом руководстве мы рассмотрим, как использовать ЕОШИБКУ с ВПР в Excel для продуктивной обработки всех видов ошибок.
ВПР — одна из самых запутанных функций Excel, из-за которой возникает множество проблем. В какой бы таблице вы ни искали, ошибки #N/A являются обычным явлением, а #NAME и #VALUE также появляются время от времени. Использование функции ВПР с ЕОШИБКА может помочь вам обнаружить все возможные ошибки и обработать их наиболее подходящим для вашей ситуации способом.
Почему ВПР выдает ошибку?
Наиболее распространенной ошибкой в формулах ВПР является ошибка #Н/Д, возникающая, когда искомое значение не найдено. Это может происходить по разным причинам:
- Значение поиска не существует в массиве поиска.
- Искомое значение написано с ошибкой.
- В искомом значении или столбце поиска есть начальные или конечные пробелы.
- Столбец подстановки не является самым левым столбцом массива таблиц.
Кроме того, вы можете нарваться на #ЗНАЧ! ошибка, например, когда искомое значение содержит более 255 символов. Если в имени функции есть орфографическая ошибка, #NAME? появится ошибка.
Полную информацию см. в нашем предыдущем сообщении о том, почему функция ВПР в Excel не работает.
ЕСЛИ ОШИБКА Формула VLOOKUP для замены ошибок пользовательским текстом
Чтобы скрыть все возможные ошибки, которые могут быть вызваны функцией ВПР, вы можете поместить ее в формулу ЕСЛИ ОШИБКА следующим образом:
ЕСЛИ(ОШИБКА(ВПР(…)), “text_if_error“, ВПР(…))
В качестве примера вытянем названия предметов, по которым ученики группы А провалили тесты:
=ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ)
В результате вы получаете кучу ошибок #Н/Д, что может создать впечатление, что формула повреждена.
На самом деле эти ошибки просто указывают на то, что некоторые значения поиска (A3:A14) не найдены в списке поиска (D3:D9). Чтобы четко передать эту мысль, вложите формулу ВПР в конструкцию ЕСЛИ ОШИБКА:
=ЕСЛИ(ЕОШИБКА(ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ)), “Нет”, ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ))
Это уловит ошибки и вернет ваше собственное текстовое сообщение:
Советы и примечания:
- Основное преимущество этой формулы в том, что она прекрасно работает во всех версиях Excel 2000 по Excel 365. В современных версиях проще и компактнее альтернативы доступны.
- Функция ЕОШИБКА перехватывает абсолютно все ошибки, такие как #Н/Д, #ИМЯ, #ЗНАЧ и т. д. ЕСЛИ ВПР ЕСЛИ ЕСТЬ (во всех версиях) или ЕСЛИ ВПР (в Excel 2013 и более поздних версиях).
ISERROR VLOOKUP для возврата пустой ячейки в случае ошибки
Чтобы иметь пустую ячейку при возникновении ошибки, заставьте формулу возвращать пустую строку (“”) вместо пользовательского текста:
ЕСЛИ(ЕОШИБКА(ВПР(…)), “”, ВПР(…))
В нашем случае формула принимает такой вид:
=ЕСЛИ(ЕОШИБКА(ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ)), “”, ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ))
Результат именно такой, как и ожидалось — пустая ячейка, если имя учащегося не найдено в таблице поиска.
Кончик. Аналогичным образом вы можете заменить ошибки ВПР нулями, тире или любым другим символом, который вам нравится. Просто используйте нужный символ вместо пустой строки.
ЕСЛИ ОШИБКА ВПР Формула Да/Нет
В какой-то ситуации вы можете что-то искать, но вместо того, чтобы тянуть спички, просто хотите вернуться Да (или другой текст, если искомое значение найдено) и Нет (если искомое значение не найдено). Чтобы это сделать, вы можете использовать эту общую формулу:
ЕСЛИ(ОШИБКА(ВПР(…)), “text_if_not_found“, “text_if_found“)
Предположим, что в нашем образце данных вы хотите узнать, какие учащиеся провалили тест, а какие нет. Для этого подайте уже знакомую формулу ВПР ЕОШИБКА для логической проверки ЕСЛИ и скажите ей выводить «Нет», если значение не найдено (ВПР ЕОШИБКА возвращает ИСТИНА), и «Да», если найдено (ВПР ЕОШИБКА возвращает ЛОЖЬ):
=ЕСЛИ(ЕОШИБКА(ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ)), “Нет”, “Да”)
IERROR VLOOKUP альтернативы
Комбинация ЕСЛИ ОШИБКА — это старейший проверенный временем метод ВПР без ошибок в Excel. Со временем появились новые функции, обеспечивающие более простые способы выполнения той же задачи. Ниже мы обсудим другие возможные решения и когда каждое из них лучше всего применять.
ЕСЛИ ОШИБКА ВПР
Доступно в Excel 2007 и выше
Начиная с версии 2007, в Excel есть специальная функция ЕСЛИОШИБКА, которая проверяет формулу на наличие ошибок и возвращает собственный текст (или запускает альтернативную формулу) при обнаружении какой-либо ошибки.
ЕСЛИОШИБКА(ВПР(…), “text_if_error“)
Реальная формула выглядит следующим образом:
=ЕСЛИОШИБКА(ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ), “Нет”)
На первый взгляд это выглядит как укороченный аналог формулы ЕСЛИ ОШИБКА ВПР. Однако есть существенное отличие:
- ЕСЛИОШИБКА ВПР предполагает, что вам всегда нужен результат ВПР, если это не ошибка.
- ЕСЛИ ОШИБКА VLOOKUP позволяет указать, что возвращать, если ошибка и что, если ошибки нет.
Дополнительные сведения см. в разделе Использование ЕСЛИОШИБКА с функцией ВПР в Excel.
ЕСЛИ ISNA ВПР
Работает в Excel 2000 и более поздних версиях
В ситуации, когда вы хотите перехватывать только #N/A, не перехватывая никаких других ошибок, вам пригодится функция ISNA. Синтаксис такой же, как у IF IERROR VLOOKUP:
ЕСЛИ(ИСНА(ВПР(…)), “text_if_error“, ВПР(…))
Но при определенных обстоятельствах эта, казалось бы, идентичная формула может давать разные результаты:
=ЕСЛИ(ИСНА(ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ)), “Нет”, ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ))
На изображении ниже ячейка A13 содержит много пробелов в конце, из-за чего общая длина искомого значения превышает 255 символов. В результате формула вызывает ошибку #ЗНАЧ! ошибка, привлекая ваше внимание к этой ячейке и призывая разобраться в причинах. IERROR VLOOKUP в этом случае вернет «Нет», что только скроет проблему и даст абсолютно неверный результат.
Когда использовать:
Эта формула прекрасно работает в ситуации, когда вы хотите отобразить некоторый текст только тогда, когда значение поиска не найдено, и не хотите маскировать основные проблемы с самой формулой ВПР, например, когда имя функции введено с ошибкой (#ИМЯ?) или не указан полный путь к книге поиска (#ЗНАЧ!).
Для получения дополнительной информации см. функцию ISNA в Excel с примерами формул.
IFNA VLOOKUP
Доступно в Excel 2013 и выше
Это современная замена комбинации IF ISNA, которая упрощает обработку ошибок #N/A.
ЕСЛИНА(ВПР(…), “text_if_error“)
Вот сокращенный эквивалент нашей формулы IF ISNA VLOOKUP:
=ЕСЛИНА(ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ), “Нет”)
Когда использовать:
Это идеальное решение для перехвата и обработки ошибок #N/A в современных версиях Excel (2013–365).
Для получения полной информации см. Функция Excel IFNA.
XLOOKUP
Поддерживается в Excel 2021 и Excel 365.
Благодаря встроенной функции «если ошибка» функция XLOOKUP — это самый простой способ поиска без ошибок #N/A в Excel. Просто введите удобный для пользователя текст в необязательный 4-й аргумент с именем если_не_найдено.
Например:
=XLOOKUP(A3, $D$3:$D$9, $E$3:$E$9, “Для”)
Ограничение: он перехватывает только ошибки #N/A, игнорируя другие типы.
Для получения дополнительной информации ознакомьтесь с функцией XLOOKUP в Excel.
Как видите, Excel предоставляет довольно много различных возможностей для обработки ошибок ВПР. Надеюсь, этот урок пролил свет на то, как их эффективно использовать. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Доступные загрузки
IERROR с примерами VLOOKUP (файл .xlsx)
