Функция ЕОШИБКА в Excel с примерами формул
В учебнике рассматривается практическое использование функции ЕОШИБКА Excel и показано, как проверять различные формулы на наличие ошибок.
Когда вы пишете формулу, которую Excel не понимает или не может вычислить, она привлекает ваше внимание к проблеме, показывая сообщение об ошибке. Функция ЕОШИБКА может помочь вам отловить ошибки и предоставить альтернативу при обнаружении ошибки.
ЕОШИБКА функция в Excel
Функция Excel ЕОШИБКА перехватывает все типы ошибок, в том числе #РАССЧ!, #ДЕЛ/0!, #Н/Д, #ИМЯ?, #ЧИСЛО!, #NULL!, #ССЫЛКА!, #ЗНАЧ! . Результатом является логическое значение: TRUE, если обнаружена ошибка, FALSE в противном случае.
Эта функция доступна во всех версиях Excel 2000–2021 и Excel 365.
Синтаксис функции ЕОШИБКА так же прост:
ЕОШИБКА(значение)
Где ценность значение ячейки или формула, которые необходимо проверить на наличие ошибок.
Формула Excel ЕОШИБКА
Чтобы создать формулу ЕОШИБКА в ее простейшей форме, укажите ссылку на ячейку, которую вы хотите проверить на наличие ошибок. Например:
=ОШИБКА(A2)
В случае обнаружения какой-либо ошибки вы получите TRUE. Если в тестируемой ячейке нет ошибок, вы получите ЛОЖЬ:
ЕСЛИ ОШИБКА формула в Excel
Чтобы вернуть пользовательское сообщение или выполнить другой расчет при возникновении ошибки, используйте ЕОШИБКА вместе с функцией ЕСЛИ. Общая формула выглядит следующим образом:
ЕСЛИ(ЕОШИБКА(формула(…), text_or_calculation_if_error, формула())
В переводе на человеческий язык это гласит: если основная формула приводит к ошибке, вывести указанный текст или запустить другой расчет, в противном случае вернуть нормальный результат формулы.
На изображении ниже деление суммы на количество приводит к нескольким ошибкам в Цена столбец:
Чтобы заменить все различные коды ошибок настраиваемым текстом, вы можете использовать следующую формулу ЕСЛИ ОШИБКА:
=ЕСЛИ(ЕОШИБКА(A2/B2), “Неизвестно”, A2/B2)
В Excel 2007 и более поздних версиях того же результата можно добиться с помощью встроенной функции ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(A2/B2, “Неизвестно”)
Следует отметить, что формула ЕСЛИОШИБКА работает немного быстрее, поскольку вычисление A2/B2 выполняется только один раз. В то время как ЕСЛИ ОШИБКА вычисляет его дважды – сначала, чтобы увидеть, не генерирует ли он ошибку, а затем еще раз, если тест ЛОЖЬ.
ЕСЛИ ОШИБКА формула ВПР
Использование ЕОШИБКИ с ВПР фактически является частным случаем формулы ЕСЛИ ОШИБКА, рассмотренной выше. Когда функция ВПР не может найти искомое значение или по какой-либо другой причине происходит сбой, вы отображаете пользовательское текстовое сообщение, используя следующий синтаксис:
ЕСЛИ(ОШИБКА(ВПР(…)), “custom_text“, ВПР(…))
В этом примере давайте перетащим время из таблицы поиска (D3:E10) в основную таблицу (A3:B15). Если значение поиска (имя участника) не существует в таблице поиска, мы вернем «Не квалифицировано».
=ЕСЛИ(ЕОШИБКА(ВПР(A3, $D$3:$E$10, 2, ЛОЖЬ)), “Не определено”, ВПР(A3, $D$3:$E$10, 2, ЛОЖЬ))
Кончик. Если вы хотите отображать настраиваемый текст только в том случае, если значение поиска не найдено (ошибка #Н/Д), игнорируя другие ошибки, используйте формулу ВПР IFNA в Excel 2013 и более поздних версиях или IFNA ВПР в более ранних версиях.
ЕСЛИ ОШИБКА ИНДЕКС СОВПАДЕНИЯ формула
При выполнении поиска с помощью комбинации ПОИСКПОЗ ИНДЕКС (или формулы ПОИСКПОЗ ИНДЕКС в Excel 365) вы можете перехватывать и обрабатывать любые возможные ошибки, используя тот же метод — функция ЕОШИБКА проверяет наличие ошибок, а ЕСЛИ отображает указанный текст, когда любой возникает ошибка.
ЕСЛИ(ОШИБКА(ИНДЕКС (return_columnСООТВЕТСТВИЕ (искомое_значение, lookup_column0)))), “custom_text“, ИНДЕКС (return_columnСООТВЕТСТВИЕ (искомое_значение, lookup_column0)))
Предположим, что в таблице поиска есть время в первом столбце. Поскольку функция ВПР не может смотреть влево, мы используем формулу ПОИСКПОЗ ИНДЕКС, чтобы получить время из столбца D:
=ИНДЕКС($D$3:$D$10, ПОИСКПОЗ(A3, $E$3:$E$10, 0))
А затем вы вставляете его в вышеупомянутую общую формулу, чтобы заменить обнаруженные ошибки любым текстом, который вы хотите:
=ЕСЛИ(ОШИБКА(ИНДЕКС($D$3:$D$10, ПОИСКПОЗ(A3, $E$3:$E$10, 0))), “Не соответствует требованиям”, ИНДЕКС($D$3:$D$10, ПОИСКПОЗ(A3 , $Е$3:$Е$10, 0)))
Примечание. Как и в случае с формулой IF IERROR VLOOKUP, имеет смысл перехватывать только ошибки #N/A и не маскировать потенциальные проблемы с самой формулой. Для этого оберните формулу ИНДЕКС МАТЕМАТИКА в IFNA в Excel 2013 и выше или в IF ISNA в более ранних версиях.
ЕСЛИ ОШИБКА Формула Да/Нет
Во всех предыдущих примерах ЕСЛИ ОШИБКА возвращал результат основной формулы, если это не ошибка. Однако это может работать и по-другому — возвращать что-то, если ошибка, и что-то еще, если нет ошибки.
ЕСЛИ(ЕОШИБКА(формула(…)), «text_if_error“, “text_if_no_error“)
В нашем примере набора данных предположим, что вас не интересует точное время, вы просто хотите знать, какие участники из группы A квалифицированы, а какие нет. Для этого используйте функцию ПОИСКПОЗ, чтобы сравнить имя в столбце А со списком квалифицированных участников в столбце D, а затем передать результаты в ЕОШИБКА. Если имя недоступно в столбце D (ПОИСКПОЗ возвращает ошибку), заставьте функцию ЕСЛИ отображать «Нет» или «Не квалифицировано». Если имя появляется в столбце D (ошибки нет), верните «Да» или «Квалифицировано».
= ЕСЛИ (ЕОШИБКА (ПОИСКПОЗ (A3, $D$3:$D$10, 0)), “Нет”, “Да”)
Как посчитать количество ошибок
Чтобы получить количество ошибок в определенном столбце, вам нужно проверить диапазон, а не только одну ячейку. Для этого «накормите» целевой диапазон ISERROR и преобразуйте возвращенные логические значения в 1 и 0 с помощью двойного унарного оператора (–). Функция СУММ или СУММПРОИЗВ может складывать числа и выдавать окончательный результат.
Например:
=СУММ(–ЕОШИБКА(C2:C10))
Обратите внимание, что это работает как обычная формула только в Excel 365 и Excel 2021, которые поддерживают динамические массивы. В Excel 2019 и более ранних версиях вам нужно нажать Ctrl + Shift + Enter, чтобы создать формулу массива (не вводите фигурные скобки вручную, это не сработает!):
{=СУММ(–ЕОШИБКА(C2:C10))}
В качестве альтернативы вы можете использовать функцию СУММПРОИЗВ, которая изначально обрабатывает массивы, поэтому формулу можно завершить с помощью обычной клавиши Enter во всех версиях:
=СУММПРОИЗВ(–ОШИБКА(C2:C10))
Разница между ЕОШИБКА и ЕСЛИОШИБКА в Excel
Обе функции ЕОШИБКА и ЕСЛИОШИБКА используются для перехвата и обработки ошибок в Excel. Разница заключается в следующем:
- В чистом виде ISERROR просто проверяет, является ли значение ошибкой или нет. Он доступен во всех версиях Excel.
- Функция ЕСЛИОШИБКА предназначена для подавления или маскировки ошибок: при обнаружении ошибки она возвращает другое значение, указанное вами. Он доступен в Excel 2007 и выше.
На первый взгляд ЕСЛИОШИБКА выглядит как сокращенная альтернатива формуле ЕСЛИ НЕОШИБКА. Однако при ближайшем рассмотрении можно заметить разницу:
- ЕСЛИОШИБКА позволяет указать только значение_если_ошибка. Если ошибки нет, он всегда возвращает результат проверенного значения/формулы.
- ЕСЛИ ОШИБКА обеспечивает большую гибкость и позволяет обрабатывать обе ситуации — что должно произойти, если ошибка и что, если ошибки нет.
Чтобы лучше проиллюстрировать это, рассмотрим следующие формулы:
=ЕСЛИОШИБКА(A1, “Ошибка вычисления”)
=ЕСЛИ(ЕОШИБКА(A1), “Ошибка вычисления”, A1)
Эти две формулы эквивалентны — обе проверяют значение, заданное формулой, в ячейке A1 и возвращают «Ошибка вычисления», если это ошибка, в противном случае — возвращают значение.
Но что, если вы хотите выполнить некоторые вычисления, если значение в ячейке A1 не является ошибкой? Функция ЕСЛИОШИБКА этого сделать не может. В случае ЕСЛИ ОШИБКА просто введите желаемое вычисление в последний аргумент. Например:
=ЕСЛИ(ЕОШИБКА(A1), “Ошибка вычисления”, A1*2)
Как видите, этот более длинный вариант формулы ЕСЛИОШИБКА, который часто считается устаревшим, все еще может быть полезен 🙂
Доступные загрузки
Примеры формул ЕОШИБКА (файл .xlsx)