Функция ЕОШИБКА в Excel с примерами формул

В учебнике рассматривается практическое использование функции ЕОШИБКА Excel и показано, как проверять различные формулы на наличие ошибок.

Когда вы пишете формулу, которую Excel не понимает или не может вычислить, она привлекает ваше внимание к проблеме, показывая сообщение об ошибке. Функция ЕОШИБКА может помочь вам отловить ошибки и предоставить альтернативу при обнаружении ошибки.

ЕОШИБКА функция в Excel

Функция Excel ЕОШИБКА перехватывает все типы ошибок, в том числе #РАССЧ!, #ДЕЛ/0!, #Н/Д, #ИМЯ?, #ЧИСЛО!, #NULL!, #ССЫЛКА!, #ЗНАЧ! . Результатом является логическое значение: TRUE, если обнаружена ошибка, FALSE в противном случае.

Эта функция доступна во всех версиях Excel 2000–2021 и Excel 365.

Синтаксис функции ЕОШИБКА так же прост:

ЕОШИБКА(значение)

Где ценность значение ячейки или формула, которые необходимо проверить на наличие ошибок.

Формула Excel ЕОШИБКА

Чтобы создать формулу ЕОШИБКА в ее простейшей форме, укажите ссылку на ячейку, которую вы хотите проверить на наличие ошибок. Например:

=ОШИБКА(A2)

В случае обнаружения какой-либо ошибки вы получите TRUE. Если в тестируемой ячейке нет ошибок, вы получите ЛОЖЬ:
Функция Excel ЕОШИБКА

ЕСЛИ ОШИБКА формула в 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)

Вас также могут заинтересовать

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *