Функция IFERROR (ЕСЛИОШИБКА) в Excel лучше всего подходит для обработки случаев, когда формулы возвращают ошибку. Используя эту функцию, вы можете указать, какое значение функция должна возвращать вместо ошибки. Если функция в ячейке не возвращает ошибку, то возвращается её собственный результат.
Содержание
- Видеоурок
- Что возвращает функция
- Синтаксис
- Аргументы функции
- Дополнительная информация
- Примеры использования функции IFERROR (ЕСЛИОШИБКА) в Excel
- Пример 1. Заменяем ошибки в ячейке на пустые значения
- Пример 2. Заменяем значения без данных при использовании функции VLOOKUP (ВПР) на “Не найдено”
- Пример 3. Возвращаем значение “0” вместо ошибок формулы
Видеоурок
Что возвращает функция
Указанное вами значение, в случае если в ячейке есть ошибка.
Синтаксис
=IFERROR(value, value_if_error) — английская версия
=ЕСЛИОШИБКА(значение;значение_если_ошибка) — русская версия
Аргументы функции
- value (значение) — это аргумент, который проверяет, есть ли в ячейке ошибка. Обычно, ошибкой может быть результат какого либо вычисления;
- value_if_error (значение_если_ошибка) — это аргумент, который заменяет ошибку в ячейке (в случае её наличия) на указанное вами значение. Ошибки могут выглядеть так: #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME?, #NULL! (английская версия Excel) или #ЗНАЧ!, #ДЕЛ/0, #ИМЯ?, #Н/Д, #ССЫЛКА!, #ЧИСЛО!, #ПУСТО! (русская версия Excel).
Дополнительная информация
- Если вы используете кавычки («») в качестве аргумента value_if_error (значение_если_ошибка), ячейка ничего не отображает в случае ошибки.
- Если аргумент value (значение) или value_if_error (значение_если_ошибка) ссылается на пустую ячейку, она рассматривается как пустая.
Примеры использования функции IFERROR (ЕСЛИОШИБКА) в Excel
Пример 1. Заменяем ошибки в ячейке на пустые значения
Если вы используете функции, которые могут возвращать ошибку, вы можете заключить ее в функцию и указать пустое значение, возвращаемое в случае ошибки.
В примере, показанном ниже, результатом ячейки D4 является # DIV/0!.
Для того, чтобы убрать информацию об ошибке в ячейке используйте эту формулу:
=IFERROR(A1/A2,””) — английская версия
=ЕСЛИОШИБКА(A1/A2;»») — русская версия
В данном случае функция проверит, выдает ли формула в ячейке ошибку, и, при её наличии, выдаст пустой результат.
В качестве результата формулы, исправляющей ошибки, вы можете указать любой текст или значение, например, с помощью следующей формулы:
Больше лайфхаков в нашем Telegram Подписаться
=IFERROR(A1/A2,”Error”) — английская версия
=ЕСЛИОШИБКА(A1/A2;»») — русская версия
Если вы пользуетесь версией Excel 2003 или ниже, вы не найдете функцию IFERROR (ЕСЛИОШИБКА). Вместо нее вы можете использовать обычную функцию IF или ISERROR.
Пример 2. Заменяем значения без данных при использовании функции VLOOKUP (ВПР) на “Не найдено”
Когда мы используем функцию VLOOKUP (ВПР), часто сталкиваемся с тем, что при отсутствии данных по каким либо значениям, формула выдает ошибку “#N/A”.
На примере ниже, мы хотим с помощью функции VLOOKUP (ВПР) для выбранных студентов подставить данные из результатов экзамена.
На примере выше, в списке студентов с результатами экзамена нет данных по имени Иван, в результате, при использовании функции VLOOKUP (ВПР), формула нам выдает ошибку.
Как раз в этом случае мы можем воспользоваться функцией IFERROR (ЕСЛИОШИБКА), для того, чтобы результат вычислений выглядел корректно, без ошибок. Добиться этого мы можем с помощью формулы:
=IFERROR(VLOOKUP(D2,$A$2:$B$12,2,0),”Не найдено”) — английская версия
=ЕСЛИОШИБКА(ВПР(D2;$A$2:$B$12;2;0);»Не найдено») — русская версия
Пример 3. Возвращаем значение “0” вместо ошибок формулы
Если у вас нет конкретного значения, которое вы бы хотели использовать для замены ошибок — оставляйте аргумент функции value_if_error (значение_если_ошибка) пустым, как показано на примере ниже и в случае наличия ошибки, функция будет выдавать “0”:
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше
Функцию ЕСЛИERROR можно использовать для перебора и обработки ошибок в формуле. Если же формула возвращает значение, определяемую формулой, возвращается ошибка; в противном случае возвращается результат формулы.
Синтаксис
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Аргументы функции ЕСЛИОШИБКА описаны ниже.
-
значение Обязательный аргумент. Проверяемая на ошибку аргумент.
-
value_if_error — обязательный аргумент. Значение, возвращаемая, если формула возвращает ошибку. Вычисляются следующие типы ошибок: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?или #NULL!.
Замечания
-
Если значение или value_if_error пустая ячейка, то если ЕСЛИЕROR рассматривает его как пустую строковую строку («»).
-
Если значение является формулой массива, то функции ЕСЛИERROR возвращают массив результатов для каждой ячейки в диапазоне, указанном в значении. См. второй пример ниже.
Примеры
Скопируйте данные из таблицы ниже и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — ВВОД.
|
Котировка |
Единиц продано |
|
|---|---|---|
|
210 |
35 |
|
|
55 |
0 |
|
|
23 |
||
|
Формула |
Описание |
Результат |
|
=ЕСЛИОШИБКА(A2/B2;»Ошибка при вычислении») |
Выполняет проверку на предмет ошибки в формуле в первом аргументе (деление 210 на 35), не обнаруживает ошибок и возвращает результат вычисления по формуле |
6 |
|
=ЕСЛИОШИБКА(A3/B3;»Ошибка при вычислении») |
Выполняет проверку на предмет ошибки в формуле в первом аргументе (деление 55 на 0), обнаруживает ошибку «деление на 0» и возвращает «значение_при_ошибке» |
Ошибка при вычислении |
|
=ЕСЛИОШИБКА(A4/B4;»Ошибка при вычислении») |
Выполняет проверку на предмет ошибки в формуле в первом аргументе (деление «» на 23), не обнаруживает ошибок и возвращает результат вычисления по формуле. |
0 |
Пример 2
|
Котировка |
Единиц продано |
Отношение |
|---|---|---|
|
210 |
35 |
6 |
|
55 |
0 |
Ошибка при вычислении |
|
23 |
0 |
|
|
Формула |
Описание |
Результат |
|
=C2 |
Выполняет проверку на предмет ошибки в формуле в первом аргументе в первом элементе массива (A2/B2 или деление 210 на 35), не обнаруживает ошибок и возвращает результат вычисления по формуле |
6 |
|
=C3 |
Выполняет проверку на предмет ошибки в формуле в первом аргументе во втором элементе массива (A3/B3 или деление 55 на 0), обнаруживает ошибку «деление на 0» и возвращает «значение_при_ошибке» |
Ошибка при вычислении |
|
=C4 |
Выполняет проверку на предмет ошибки в формуле в первом аргументе в третьем элементе массива (A4/B4 или деление «» на 23), не обнаруживает ошибок и возвращает результат вычисления по формуле |
0 |
|
Примечание. Если у вас есть текущая версия Microsoft 365 ,вы можете ввести формулу в левую верхнюю ячейку диапазона выходных данных, а затем нажать ввод, чтобы подтвердить формулу как формулу динамического массива. В противном случае формула должна быть введена как формула массива устаревшей. Для этого сначала выберем диапазон вывода, введите формулу в левую верхнюю ячейку диапазона, а затем нажмите CTRL+SHIFT+ВВОД, чтобы подтвердить ее. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры. |
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
|
Сделать ячейку пустой при ошибке формулы |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
Здравствуйте! Возникла похожая проблема. Есть формула: =ЕСЛИОШИБКА((ВПР(A10;Лист5!$A$547:$D$550;3;ЛОЖЬ));»»). В общем, если функция ВПР нашла значение, то оно выводится в ячейку, если нет, то выводилась ошибка #НД (нет данных), что бы ее обработать и убрать добавила обработку ЕСЛИОШИБКА и заменила пустые значения на «» (без пробела внутри кавычек). Такой результат устроил на этом этапе. Далее эти ячейки копировались и вставлялись в другое место, где выбираются параметры вставки «вставить только значения», это для того, что бы даже если исходные данные будут изменены или удалены, то в этой таблице остались бы все значения (уже без формул, без ссылок, только значения). В ячейках, где было изначально значение «», после копирования выглядит, как пустая ячейка. НО, потом эти ячейки со значениями используются далее для подсчета сумм, формулы простые «+» и «-«, но всегда выдается ошибка «#Знач». Если выделяю эти пустые ячейки и нажимаю Del, то последующие формулы вычисляются верно и ошибок не возникает.
Т.е. Изначально «» не дает пустую ячейку, дальнейшее копирование и вставка значений опять не дает пустую ячейку, «0» нас тоже не устраивает (если только скрыть их отображение???).
Вопрос: как при выполнении/невыполнении условия в функциях Если или ЕслиОшибка (это не важно) выводилась именно пустая ячейка? Ведь ячейка с формулой уже изначально не может считаться пустой. Но как поступить с ними, я не придумаю. Помогите, пожалуйста.
- Что делает ЕСЛИОШИБКА?
- Синтаксис
- Пример 1: ЕСЛИОШИБКА + ВПР
- Пример 2: ЕСЛИОШИБКА + деление на ноль
- Пример 3: ЕСЛИОШИБКА в формулах массива
- Другие логические функции
| Раздел функций | Логические |
| Название на английском | IFERROR |
| Волатильность | Не волатильная |
| Похожие функции | ЕСЛИ, ЕОШ |
Что делает ЕСЛИОШИБКА?
Часто при использовании формул, если результат возвращает ошибку, нужно обрабатывать ее, а если нет – возвращать результат вычисления.
Именно эту задачу и решает функция ЕСЛИОШИБКА.
Функция проверяет входящее значение/вычисление на ошибочность, если ошибки нет, возвращает его само.
Оцениваются следующие типы ошибок: #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? и #ПУСТО!
Эквивалентным сочетанием была бы формула на основе комбинации функции ЕСЛИ и ЕОШ.
Такая комбинация дважды использует вычислительные ресурсы – один раз чтобы проверить результат на наличие ошибки, а второй уже для произведения вычисления, если ошибки нет.
ЕСЛИ(ЕОШ(вычисление);"результат-если-ошибка";вычисление)
Отличие ЕСЛИОШИБКА в том, что она производит вычисление однократно и экономит ресурсы.
Также использование функции упрощает синтаксис формул.
Синтаксис
Синтаксис функции ЕСЛИОШИБКА предполагает всего два аргумента, оба – обязательные:
=ЕСЛИОШИБКА(Значение-Или-Вычисление;Значение-если-ошибка)
Пример 1: ЕСЛИОШИБКА + ВПР
Наиболее характерный пример использования – в паре с функцией ВПР при поиске данных в больших таблицах.
=ЕСЛИОШИБКА(ВПР(ячейка-или-значение;диапазон;номер-столбца;0);"ошибка") =ЕСЛИОШИБКА(ВПР(ячейка-или-значение;диапазон;номер-столбца;0);"")
Первый вариант вернет текстовое значение ошибки, второй оставит строку пустой.
Поскольку ВПР может изрядно загрузить процессор, функция ЕСЛИОШИБКА здесь весьма кстати.
Пример 2: ЕСЛИОШИБКА + деление на ноль
Задача маркетолога – произвести оценку эффективности рекламных кампаний. Один из ключевых показателей – стоимость привлечения клиента. Рассчитывается он довольно просто – расходы по рекламным кампаниям делятся на количество приведенных ими клиентов.
Что делать, когда кампания не привела ни одного? Вычисление выдаст ошибку
#ДЕЛ/0!
Оставлять ее не стоит, ведь деньги на рекламу были потрачены, и нужно это учитывать.
Лучший вариант – представить, что один клиент был приведен, т.к. рано или поздно это произойдет, а эффективность нужно смотреть уже сейчас. Функция ЕСЛИОШИБКА дает возможность вернуть весь расход на кампанию, если возникает ошибка деления на ноль.
Наиболее наглядна польза от такой формулы – если использовать условное форматирование с цветовой шкалой. Сразу бросаются в глаза эффективные и неэффективные кампании.
Пример 3: ЕСЛИОШИБКА в формулах массива
Проблема функций МИН и МАКС при работе с массивами в том, что, если в массиве присутствует хотя бы одна ошибка, они также возвращают ошибку.
Поэтому, если есть вероятность такого события, функцию ЕСЛИОШИБКА нужно использовать как обработчик, чтобы избежать результирующей ошибки.
Формула для поиска позиции первого символа латиницы:
{=МИН(ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА(65:90));A1);""))}
Механика ее работы такова:
- Функция СИМВОЛ создает массив букв английского алфавита;
- Функция ПОИСК ищет позицию каждой буквы в строке;
- Если буква не найдена, функция вернет ошибку Н/Д;
- Функция ЕСЛИОШИБКА возвращает пустую строку в таких случаях, а числа оставляет как они есть;
- Функция МИН пропускает пустые строки и возвращает минимальное число;
- Если весь массив будет состоять из пустых строк, функция МИН вернет 0.
А такая формула использует массив констант и ищет позицию первой цифры:
{=МИН(ЕСЛИОШИБКА(ПОИСК({1:2:3:4:5:6:7:8:9:0};A1);""))}Читайте подробнее в статье про формулы массива.
Другие логические функции
ЕСЛИ, И, ИЛИ, НЕ
Понравилась статья? Поддержите ее автора!
Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!