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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция 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!.

Для того, чтоб убрать информацию о ошибке в ячейке используйте эту формулу:

=ЕСЛИОШИБКА(A1/A2;””) – российская версия

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

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

=ЕСЛИОШИБКА(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”:

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

Синтаксис

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

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

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

Замечания

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

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

Примеры

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

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

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

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

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

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

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

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

Пример 2

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

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

Делает проверку на предмет ошибки в формуле в первом аргументе во 2-м элементе массива (A3/B3 либо деление 55 на 0), обнаруживает ошибку "деление на 0" и возвращает "значение_при_ошибке"

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

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

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

Доп сведения

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

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

Get expert help now

Don’t have time to figure this out? Our expert partners at Excelchat can do it for you, 24/7.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• 1-ый разу снутри функции ЕНД;
• 2-ой раз в 3-ем аргументе ЕСЛИ.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Синтаксис

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

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

значение Неотклонимый аргумент. Проверяемая на ошибку аргумент.

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

Замечания

Если значение либо value_if_error пустая ячейка, то если ЕСЛИЕROR разглядывает его как пустую строковую строчку («»).

Если значение является формулой массива, то функции ЕСЛИERROR возвращают массив результатов для каждой ячейки в спектре, обозначенном в значении. См. 2-ой пример ниже.

Примеры

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