Формула еслиошибка в excel примеры

Перехват ошибок в формулах функцией ЕСЛИОШИБКА (IFERROR)

Тот, кто никогда не ошибался — опасен.
(Книга самурая)

Ошибки случаются. Вдвойне обидно, когда они случаются не по твоей вине. Так в Microsoft Excel, некоторые функции и формулы могут выдавать ошибки не потому, что вы накосячили при вводе, а из-за временного отсутствия данных или копирования формул «с запасом» на избыточные ячейки. Классический пример — ошибка деления на ноль при вычислении среднего:

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

Для лечения подобных ситуаций в Microsoft Excel есть мегаполезная функция ЕСЛИОШИБКА (IFERROR), которая умеет проверять заданную формулу или ячейку и, в случае возникновения любой ошибки, выдавать вместо нее заданное значение: ноль, пустую текстовую строку «» или что-то еще.

Синтаксис функции следующий:

=ЕСЛИОШИБКА( Что_проверяем ; Что_выводить_вместо_ошибки )

Так, в нашем примере можно было бы все исправить так:

Все красиво и ошибок больше нет.

Обратите внимание, что эта функция появилась только с 2007 версии Microsoft Excel. В более ранних версиях приходилось использовать функции ЕОШ (ISERROR) и ЕНД (ISNA) . Эти функции похожи на ЕСЛИОШИБКА, но они только проверяют наличие ошибок и не умеют заменять их на что-то еще. Поэтому приходилось использовать их обязательно в связке с функцией проверки ЕСЛИ (IF) , создавая вложенные конструкции типа:

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

Функция IFERROR (ЕСЛИОШИБКА) в Excel. Как использовать?

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

Что возвращает функция

Указанное вами значение, в случае если в ячейке есть ошибка.

=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;””) – русская версия

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

В качестве результата формулы, исправляющей ошибки, вы можете указать любой текст или значение, например, с помощью следующей формулы:

=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”:

Функция ЕСЛИОШИБКА() в MS EXCEL

Функция ЕСЛИОШИБКА() , английский вариант IFERROR(), проверяет выражение на равенство значениям #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО! Если проверяемое выражение или значение в ячейке содержит ошибку, то функция возвращает определенное для этого случая значение, в противном случае — результат вычисления выражения или содержимое ячейки.

Функция ЕСЛИОШИБКА() впервые появилась в EXCEL 2007.

Синтаксис функции

ЕСЛИОШИБКА(значение;значение_при_ошибке)

Значение — аргумент, проверяемый на возникновение ошибок.

Значение_при_ошибке — значение, возвращаемое при ошибке.

Для обработки ошибок #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО! обычно используют формулу вида (см. файл примера ):
=ЕСЛИОШИБКА(A2/B2;»Ошибка расчете»)

Формула проверяет на предмет ошибки результат вычисления A2/B2.

Если результат вычисления не является ошибкой #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО! , то формула возвращает результат вычисления A2/B2 , если обнаруживает, то возвращает строку Ошибка в расчете.

Функция ЕСЛИОШИБКА() vs ЕОШИБКА()

В отличие от функции ЕСЛИОШИБКА() функция ЕОШИБКА() не умеет самостоятельно обрабатывать ошибку — приходится задействовать функцию ЕСЛИ() :
=ЕСЛИ(ЕОШИБКА(A2/B2);»Ошибка в расчетах»;A2/B2)

Т.к. функция ЕСЛИОШИБКА() впервые появилась в EXCEL 2007, то в более ранних версиях придется использовать функцию ЕОШИБКА() .

Обратите внимание, что использована именно ЕОШИБКА() , а не ЕОШ() , т.к. для последней ошибка #Н/Д ошибкой не является (см. рисунок выше).

ЕСЛИОШИБКА (функция ЕСЛИОШИБКА)

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

Аргументы функции ЕСЛИОШИБКА описаны ниже.

значение Обязательный аргумент. Аргумент, проверяемый на наличие ошибки.

валуе_иф_еррор Обязательный. Возвращаемое значение, если формула возвращает ошибку. Оцениваются следующие типы ошибок: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? и #NULL!.

Если «значение» или «валуе_иф_еррор» является пустой ячейкой, ЕСЛИОШИБКА рассчитает ее как пустую строку («»).

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

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — ВВОД.

=ЕСЛИОШИБКА(A2/B2;»Ошибка при вычислении»)

Выполняет проверку на предмет ошибки в формуле в первом аргументе (деление 210 на 35), не обнаруживает ошибок и возвращает результат вычисления по формуле

=ЕСЛИОШИБКА(A3/B3;»Ошибка при вычислении»)

Выполняет проверку на предмет ошибки в формуле в первом аргументе (деление 55 на 0), обнаруживает ошибку «деление на 0» и возвращает «значение_при_ошибке»

Ошибка при вычислении

=ЕСЛИОШИБКА(A4/B4;»Ошибка при вычислении»)

Выполняет проверку на предмет ошибки в формуле в первом аргументе (деление «» на 23), не обнаруживает ошибок и возвращает результат вычисления по формуле.

Ошибка при вычислении

Выполняет проверку на предмет ошибки в формуле в первом аргументе в первом элементе массива (A2/B2 или деление 210 на 35), не обнаруживает ошибок и возвращает результат вычисления по формуле

Выполняет проверку на предмет ошибки в формуле в первом аргументе во втором элементе массива (A3/B3 или деление 55 на 0), обнаруживает ошибку «деление на 0» и возвращает «значение_при_ошибке»

Ошибка при вычислении

Выполняет проверку на предмет ошибки в формуле в первом аргументе в третьем элементе массива (A4/B4 или деление «» на 23), не обнаруживает ошибок и возвращает результат вычисления по формуле

Примечание. Если у вас установлена текущая версия Office 365, вы можете ввести формулу в левую верхнюю ячейку диапазона вывода, а затем нажмите клавишу ВВОД, чтобы подтвердить формулу как формулу динамических массивов. В противном случае необходимо ввести формулу в качестве устаревшей формулы массива, сначала выделив диапазон вывода, введите формулу в верхнюю левую ячейку выходного диапазона, а затем нажмите клавиши CTRL + SHIFT + ВВОД, чтобы подтвердить его. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Примеры формул ЕСЛИОШИБКА и ЕДН для обработки ошибок в Excel

В поисковых функциях Excel: ВПР, ГПР, ПОИСКПОЗ чаще всего в третьем аргументе используется значение ЛОЖЬ или 0. Так пользователь заставляет искать в исходной таблице только точные совпадения значений при поиске. Если в поисковой функции будет в третьем аргументе определено точное совпадение, а искомое значение не будет найдено в таблице, тогда функция возвращает ошибку с кодом #Н/Д!

Формула ЕСЛИОШИБКА обработки ошибок функции ВПР в Excel

Ошибка #Н/Д! пригодится в анализе моделей данных Excel, так как информирует пользователя и программу о том, что не было найдено соответственное значение. Однако если большая часть такой модели данных будет использована в отчетах, то код ошибки #Н/Д! будет смотреться некорректно. Для этого Excel предлагает функции, которые проверяют результаты вычислений на ошибки и позволяют возвращать другие альтернативные значения.

Ниже на рисунке представлена таблица фирм с фамилиями их руководителей. Вторая таблица содержит те же фамилии и соответствующие им оклады. Функция ВПР используется для соединения двух таблиц в одну. Но не по всем руководителям имеются данные об их окладах, поэтому часто встречается код ошибки #Н/Д! в результатах вычисления функции ВПР.

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

Пользователи часто называют эту функцию «скрывающая ошибки». Так как она позволяет определить и укрыть любые ошибки, которые можно после этого воспринимать по-другому. А не сметить этими некрасивыми кодами в отчетах для презентации.

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

В данном примере альтернативным значением является пустая строка (двойные кавычки без каких-либо символов между ними). Благодаря этому отчет более читабельный и имеет презентабельный вид. Данная функция может возвращать любое значение, например, «Нет данных» или число 0.

Функции для работы с кодами ошибок в Excel

Функция ЕСЛИОШИБКА проверяет каждую ошибку, которую способна вернуть формула в Excel. Но следует использовать ее с определенной осторожностью. Ведь она способна скрывать все ошибки без разбора и даже такие важные как: #ДЕЛ/0! или #ЧИСЛО! и т.п.

Чтобы скрывать только определенную группу ошибок Excel предлагает еще 3 других функций:

1. ЕОШИБКА – возвращает логическое значение ИСТИНА если ее аргумент содержит ошибку: #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО.
2. ЕОШ – функция возвращает ИСТИНА если ее аргумент содержит любую ошибку, кроме #Н/Д!
3. ЕНД – возвращает значение ИСТИНА если ее аргумент содержит ошибку с кодом #Н/Д! или ЛОЖЬ если аргумент содержит любое значение или любую другую ошибку.

Три выше описанные функции для обработки ошибок в Excel возвращают логические значения ИСТИНА или ЛОЖЬ наиболее часто используются вместе с функцией ЕСЛИ.

Формула ЕСЛИ и ЕДН для ошибок ВПР без функции ЕСЛИОШИБКА в Excel

Бескомпромиссная функция обработки ошибок ЕСЛИОШИБКА появилась в программе Excel начиная с 2010-й версии. Для проверки ошибок в старших версиях Excel наиболее часто использовалась функция ЕНД:

Функция ЕНД возвращает логическое значение ИСТИНА если в ее аргументе находится только один тип ошибок – #Н/Д! Или же значение ЛОЖЬ при любых других значениях. В данной формуле функция ЕСЛИ помогает функции ЕНД. Если была получена ошибка #Н/Д! формула возвращает пустую строку – указано во втором аргументе функции ЕСЛИ. В противные случаи возвращается результат вычисления функции ВПР – указано в третьем аргументе ЕСЛИ.

Главным недостатком такой формулы является необходимость дублировать функцию ВПР:

• первый разу внутри функции ЕНД;
• второй раз в третьем аргументе ЕСЛИ.

Это значит, что Excel должен два раза выполнять функцию ВПР для одной и той же ячейки. Если на листе содержится множество таких формул, тогда их пересчет требует много времени и системных ресурсов. Очень неудобно будет работать с такими файлами. Возникнет необходимость отключения автоматического пересчета формул: «ФОРМУЛЫ»-«Вычисления»-«Параметры вычислений»-«Вручную».