ЕСЛИОШИБКА

Функция ЕСЛИОШИБКА в Excel возвращает результат формулы, а если формула выдала ошибку (#Н/Д, #ДЕЛ/0!, #ЗНАЧ! и другие) — указанное вами значение: =ЕСЛИОШИБКА(A2/B2;0) вернёт 0 вместо #ДЕЛ/0!, =ЕСЛИОШИБКА(ВПР(E2;A2:B100;2;0);"нет в списке") — подпись вместо #Н/Д, а =ЕСЛИОШИБКА(ВПР(E2;A2:B100;2;0);"") — пустую ячейку. Первый аргумент — проверяемая формула, второй — что показать при ошибке. На английском функция называется IFERROR.

Что делает ЕСЛИОШИБКА?

Часто при использовании формул, если результат возвращает ошибку, нужно обрабатывать ее, а если нет - возвращать результат вычисления.

Именно эту задачу и решает функция ЕСЛИОШИБКА.

Функция проверяет входящее значение/вычисление на ошибочность, если ошибки нет, возвращает его само.

Оцениваются следующие типы ошибок: #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? и #ПУСТО!

Эквивалентным сочетанием была бы формула на основе комбинации функции ЕСЛИ и ЕОШ.

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

ЕСЛИ(ЕОШ(вычисление);"результат-если-ошибка";вычисление)

Отличие ЕСЛИОШИБКА в том, что она производит вычисление однократно и экономит ресурсы.

Также использование функции упрощает синтаксис формул.

Синтаксис функции ЕСЛИОШИБКА

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

=ЕСЛИОШИБКА(Значение-Или-Вычисление;Значение-если-ошибка)

Пример 1: ЕСЛИОШИБКА + ВПР

Наиболее характерный пример использования - в паре с функцией ВПР при поиске данных в больших таблицах.

Вариант "если ошибка, то своё значение":

=ЕСЛИОШИБКА(ВПР(ячейка-или-значение;диапазон;номер-столбца;0);"ошибка")

Вариант "Если ошибка, то пусто":

=ЕСЛИОШИБКА(ВПР(ячейка-или-значение;диапазон;номер-столбца;0);"")

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

Пример 2: ЕСЛИОШИБКА + деление на ноль

Задача маркетолога - произвести оценку эффективности рекламных кампаний. Один из ключевых показателей - стоимость привлечения клиента. Рассчитывается он довольно просто - расходы по рекламным кампаниям делятся на количество приведенных ими клиентов.

Что делать, когда кампания не привела ни одного? Вычисление выдаст ошибку

#ДЕЛ/0!

Оставлять ее не стоит, ведь деньги на рекламу были потрачены, и нужно это учитывать.

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

Наиболее наглядна польза от такой формулы - если использовать условное форматирование с цветовой шкалой. Сразу бросаются в глаза эффективные и неэффективные кампании.

Пример 3: ЕСЛИОШИБКА в формулах массива

Проблема функций МИН и МАКС при работе с массивами в том, что, если в массиве присутствует хотя бы одна ошибка, они также возвращают ошибку.

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

Формула для поиска позиции первого символа латиницы:

={МИН(ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА(65:90));A1);""))}

Механика ее работы такова:

• Функция СИМВОЛ создает массив букв английского алфавита;
• Функция ПОИСК ищет позицию каждой буквы в строке;
• Если буква не найдена, функция вернет ошибку #ЗНАЧ!;
• Функция ЕСЛИОШИБКА возвращает пустую строку в таких случаях, а числа оставляет как они есть;
• Функция МИН пропускает пустые строки и возвращает минимальное число;
• Если весь массив будет состоять из пустых строк, функция МИН вернет 0.

А такая формула использует массив констант и ищет позицию первой цифры:

={МИН(ЕСЛИОШИБКА(ПОИСК({1:2:3:4:5:6:7:8:9:0};A1);""))}

Читайте подробнее в статье про формулы массива.

ЕСЛИОШИБКА или ЕСНД

ЕСЛИОШИБКА перехватывает любые ошибки, в том числе те, о которых лучше знать: опечатку в имени (#ИМЯ?) или битую ссылку (#ССЫЛКА!). Если нужно обработать только «не найдено» от ВПР и ПОИСКПОЗ, используйте ЕСНД — она реагирует только на #Н/Д.

Другие логические функции

ЕСЛИ, И, ИЛИ, НЕ

Почему ЕСЛИОШИБКА не работает — частые ошибки

• Excel пишет «В формуле обнаружена ошибка». Не хватает скобки или точки с запятой между аргументами: =ЕСЛИОШИБКА(формула;значение).
• Пустая строка "" мешает расчётам. Ячейка с "" не пустая: СЧЁТЗ её считает, а сложение с ней даёт #ЗНАЧ!. Если результат будет использоваться в вычислениях, возвращайте 0.
• Ошибка спрятана, а данные неверные. ЕСЛИОШИБКА скрывает и опечатки в формуле. При отладке временно уберите её или используйте ЕСНД.
• Формула медленная. ЕСЛИОШИБКА не ускоряет ВПР сама по себе — на больших таблицах сократите диапазоны поиска.

ЕСЛИОШИБКА — частые вопросы

Что значит ЕСЛИОШИБКА в Excel?

Это функция «если ошибка, то»: она возвращает результат формулы, а при ошибке — значение из второго аргумента.

Как сделать, чтобы вместо ошибки была пустая ячейка?

Укажите вторым аргументом пустые кавычки: =ЕСЛИОШИБКА(A2/B2;"").

Как использовать ЕСЛИОШИБКА с ВПР?

Оберните ВПР целиком: =ЕСЛИОШИБКА(ВПР(E2;A2:B100;2;0);"нет").

Как называется ЕСЛИОШИБКА на английском?

IFERROR. Синтаксис тот же: =IFERROR(value,value_if_error).

Чем ЕСЛИОШИБКА отличается от ЕСНД?

ЕСЛИОШИБКА перехватывает все ошибки, ЕСНД — только #Н/Д.

Итоги: функция ЕСЛИОШИБКА в Excel

Надеюсь, на этой странице вы нашли то, что искали. ЕСЛИОШИБКА делает таблицы аккуратными, но прячет и настоящие ошибки — используйте её осознанно.