Почему не работает если ошибка - Не ошибается лишь тот, кто ничего не делает!

Функция ЕСЛИОШИБКА – примеры формул

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

«Дайте мне точку опоры, и я переверну землю», — сказал однажды Архимед. «Дайте мне формулу, и я заставлю ее вернуть ошибку», — сказал бы пользователь Excel. Здесь мы не будем рассматривать, как получить ошибки в Excel. Мы узнаем, как предотвратить их, чтобы ваши таблицы были чистыми, а формулы — понятными и точными.

Итак, вот о чем мы поговорим:

Что означает функция Excel ЕСЛИОШИБКА
Если ошибка, то пусто
Если ошибка, то показать сообщение
5 фактов, которые нужно знать о функции ЕСЛИОШИБКА в Excel
ЕСЛИОШИБКА с функцией ВПР
Вложенные функции ЕСЛИОШИБКА для выполнения последовательных ВПР
ЕСЛИОШИБКА в формулах массива
ЕСЛИОШИБКА или ЕСЛИ + ЕОШИБКА?
ЕСЛИОШИБКА против ЕСНД
Рекомендации по использованию ЕСЛИОШИБКА в Excel

Что означает функция Excel ЕСЛИОШИБКА

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

Синтаксис функции Excel ЕСЛИОШИБКА следующий:

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

Где:

Значение (обязательно) — что проверять на наличие ошибок. Это может быть формула, выражение или ссылка на ячейку.
Значение_если_ошибка (обязательно) — что возвращать при обнаружении ошибки. Это может быть пустая строка (получится пустая ячейка), текстовое сообщение, числовое значение, другая формула или вычисление.

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

Рассмотрим простой пример:

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

Если ошибка, то пусто

Укажите пустую строку (“”) в аргументе значение_если_ошибка, чтобы вернуть пустую ячейку, если обнаружена ошибка:

=ЕСЛИОШИБКА(A4/B4; «»)

Вернемся к нашему примеру и используем ЕСЛИОШИБКА:

Как видите по сравнению с первым скриншотом, вместо стандартных сообщений мы видим просто пустые ячейки.

Если ошибка, то показать сообщение

Вы также можете отобразить собственное сообщение вместо стандартного обозначения ошибок Excel:

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

Перед вами – третий вариант нашей небольшой таблицы.

5 фактов, которые нужно знать о функции ЕСЛИОШИБКА в Excel

ЕСЛИОШИБКА в Excel обрабатывает все типы ошибок, включая #ДЕЛ/0!, #Н/Д, #ИМЯ?, #NULL!, #ЧИСЛО!, #ССЫЛКА! и #ЗНАЧ!.
В зависимости от содержимого аргумента значение_если_ошибка функция может заменить ошибки вашим текстовым сообщением, числом, датой или логическим значением, результатом другой формулы или пустой строкой (пустой ячейкой).
Если аргумент значение является пустой ячейкой, он обрабатывается как пустая строка (»’), но не как ошибка.
ЕСЛИОШИБКА появилась в Excel 2007 и доступна во всех последующих версиях Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021 и Excel 365.
Чтобы перехватывать ошибки в Excel 2003 и более ранних версиях, используйте функцию ЕОШИБКА в сочетании с функцией ЕСЛИ, например как показано ниже:

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

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

ЕСЛИОШИБКА с функцией ВПР

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

ЕСЛИОШИБКА(ВПР( … );»Не найдено»)

Если искомое значение отсутствует в таблице, которую вы просматриваете, обычная формула ВПР вернет ошибку #Н/Д:

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

=ЕСЛИОШИБКА(ВПР(D3; $A$3:$B$5; 2;ЛОЖЬ); «Не найдено»)

На скриншоте ниже показан пример ЕСЛИОШИБКА вместе с ВПР в Excel:

Если вы хотите перехватывать только #Н/Д, но не все подряд ошибки, используйте функцию ЕНД вместо ЕСЛИОШИБКА. Она просто возвращает ИСТИНА или ЛОЖЬ в зависимости от появления ошибки #Н/Д. Поэтому нам здесь еще понадобится функция ЕСЛИ, чтобы обработать эти логические значения:

=ЕСЛИ(ЕНД(ВПР(D3; $A$3:$B$5; 2;ЛОЖЬ)); «Не найдено»;ВПР(D3; $A$3:$B$5; 2;ЛОЖЬ))

Дополнительные примеры формул Excel ЕСЛИОШИБКА ВПР можно также найти в нашей статье Как убрать сообщение #Н/Д в ВПР?

Вложенные функции ЕСЛИОШИБКА для выполнения последовательных ВПР

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

Предположим, у вас есть несколько отчетов о продажах из региональных отделений вашей компании, и вы хотите получить сумму по определенному идентификатору заказа. С ячейкой В9 в качестве критерия поиска (номер заказа) и тремя небольшими таблицами поиска (таблица 1, 2 и 3), формула выглядит следующим образом:

=ЕСЛИОШИБКА(ВПР(B9;A3:B6;2;0);ЕСЛИОШИБКА(ВПР(B9;D3:E6;2;0);ЕСЛИОШИБКА(ВПР(B9;G3:H6;2;0);»Не найден»)))

Результат будет выглядеть примерно так, как на рисунке ниже:

То есть, если поиск завершился неудачей (то есть, ошибкой) первой таблице, начинаем искать во второй, и так далее. Если нигде ничего не нашли, получим сообщение «Не найден».

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

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

Допустим, у вас есть Сумма в столбце B и Цена в столбце C, и вы хотите вычислить Количество. Это можно сделать с помощью следующей формулы массива, которая делит каждую ячейку в диапазоне B2:B4 на соответствующую ячейку в диапазоне C2:C4, а затем суммирует результаты:

=СУММ(($B$2:$B$4/$C$2:$C$4))

Формула работает нормально, пока в диапазоне делителей нет нулей или пустых ячеек. Если есть хотя бы одно значение 0 или пустая строка, то возвращается ошибка: #ДЕЛ/0! Из-за одной некорректной позиции мы не можем получить итоговый результат.

Чтобы исправить эту ситуацию, просто вложите деление внутрь формулы ЕСЛИОШИБКА:

=СУММ(ЕСЛИОШИБКА($B$2:$B$4/$C$2:$C$4;0))

Что делает эта формула? Делит значение в столбце B на значение в столбце C в каждой строке (3500/100, 2000/50 и 0/0) и возвращает массив результатов {35; 40; #ДЕЛ/0!}. Функция ЕСЛИОШИБКА перехватывает все ошибки #ДЕЛ/0! и заменяет их нулями. Затем функция СУММ суммирует значения в итоговом массиве {35; 40; 0} и выводит окончательный результат (35+40=75).

Примечание. Помните, что ввод формулы массива должен быть завершен нажатием комбинации Ctrl + Shift + Enter (если у вас не Office365 или Excel2021 – они понимают формулы массива без дополнительных телодвижений).

ЕСЛИОШИБКА или ЕСЛИ + ЕОШИБКА?

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

В старые недобрые времена Excel 2003 и более ранних версий, когда ЕСЛИОШИБКА не существовало, совместное использование ЕСЛИ и ЕОШИБКА было единственным возможным способом перехвата ошибок. Это просто немного более сложный способ достижения того же результата.

Например, чтобы отловить ошибки ВПР, вы можете использовать любую из приведенных ниже формул.

В Excel 2007 — Excel 2016:

ЕСЛИОШИБКА(ВПР( … ); «Не найдено»)

Во всех версиях Excel:

ЕСЛИ(ЕОШИБКА(ВПР(…)); «Не найдено»; ВПР(…))

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

А вот простой пример формулы Excel ЕСЛИ ЕОШИБКА ВПР:

=ЕСЛИ(ЕОШИБКА(ВПР(D2; A2:B5;2;ЛОЖЬ)); «Не найдено»; ВПР(D2; A2:B5;2;ЛОЖЬ ))

ЕСЛИОШИБКА против ЕСНД

Представленная в Excel 2013, ЕСНД (IFNA в английской версии) — это еще одна функция для проверки формулы на наличие ошибок. Его синтаксис похож на синтаксис ЕСЛИОШИБКА:

ЕСНД(значение; значение_если_НД)

Чем ЕСНД отличается от ЕСЛИОШИБКА? Функция ЕСНД перехватывает только ошибки #Н/Д, тогда как ЕСЛИОШИБКА обрабатывает все типы ошибок.

В каких ситуациях вы можете использовать ЕСНД? Когда нецелесообразно скрывать все ошибки. Например, при работе с важными данными вы можете захотеть получать предупреждения о возможных ошибках в вашем наборе данных (случайном делении на ноль и т.п.), а стандартные сообщения об ошибках Excel с символом «#» могут быть яркими визуальными индикаторами проблем.

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

Предположим, вы хотите получить Количество из таблицы поиска в таблицу с результатами, как показано на рисунке ниже. Проще всего было бы использовать ЕСЛИОШИБКА с ВПР. Таблица приобрела бы красивый вид, но при этом за надписью «Не найдено» были бы скрыты не только ошибки поиска, но и все другие ошибки. И мы не заметили бы, что в исходной таблице поиска у нас есть ошибка деления на ноль, так как не заполнена цена персиков. Поэтому более разумно использовать ЕСНД, чтобы с ее помощью обработать только ошибки поиска:

=ЕСНД(ВПР(F3; $A$3:$D$6; 4;ЛОЖЬ); «Не найдено»)

Или подойдет комбинация ЕСЛИ ЕНД для старых версий Excel:

=ЕСЛИ(ЕНД(ВПР(F3; $A$3:$D$6; 4;ЛОЖЬ));»Не найдено»; ВПР(F3; $A$3:$D$6; 4;ЛОЖЬ))

Как видите, формула ЕСНД с ВПР возвращает «Не найдено» только для товара, которого нет в таблице поиска (Сливы). Для персиков она показывает #ДЕЛ/0! что указывает на то, что наша таблица поиска содержит ошибку деления на ноль.

Примечания

Excel позволяет использовать до 64 вложенных функций ЕСЛИ, но это вовсе не означает, что так и надо делать. Почему?

Нужно очень крепко подумать, чтобы выстроить последовательность из множества операторов ЕСЛИ и обеспечить их правильную отработку по каждому условию на протяжении всей цепочки. Если при вложении вы допустите в формуле малейшую неточность, она может сработать в 75 % случаев, но вернуть непредвиденные результаты в остальных 25 %. К сожалению, шансов отыскать эти 25 % немного.
Работа с множественными операторами ЕСЛИ может оказаться чрезвычайно трудоемкой, особенно если вы вернетесь к ним через какое-то время и попробуете разобраться, что пытались сделать вы или, и того хуже, кто-то другой.

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

Давайте посмотрим, как правильно создавать операторы с несколькими вложенными функциями ЕСЛИ и как понять, когда пора переходить к другим средствам из арсенала Excel.

Примеры

Ниже приведен пример довольно типичного вложенного оператора ЕСЛИ, предназначенного для преобразования тестовых баллов учащихся в их буквенный эквивалент.

=ЕСЛИ(D2>89;»A»;ЕСЛИ(D2>79;»B»;ЕСЛИ(D2>69;»C»;ЕСЛИ(D2>59;»D»;»F»))))

Этот сложный оператор с вложенными функциями ЕСЛИ следует простой логике:

Если тестовых баллов (в ячейке D2) больше 89, учащийся получает оценку A.
Если тестовых баллов больше 79, учащийся получает оценку B.
Если тестовых баллов больше 69, учащийся получает оценку C.
Если тестовых баллов больше 59, учащийся получает оценку D.
В противном случае учащийся получает оценку F.

Этот частный пример относительно безопасен, поскольку взаимосвязь между тестовыми баллами и буквенными оценками вряд ли будет меняться, так что дополнительных изменений не потребуется. Но что если вам потребуется разделить оценки на A+, A и A– (и т. д.)? Теперь ваши четыре условных оператора ЕСЛИ нужно переписать с учетом 12 условий! Вот так будет выглядеть ваша формула:

=ЕСЛИ(B2>97;»A+»;ЕСЛИ(B2>93;»A»;ЕСЛИ(B2>89;»A-«;ЕСЛИ(B2>87;»B+»;ЕСЛИ(B2>83;»B»;ЕСЛИ(B2>79;»B-«; ЕСЛИ(B2>77;»C+»;ЕСЛИ(B2>73;»C»;ЕСЛИ(B2>69;»C-«;ЕСЛИ(B2>57;»D+»;ЕСЛИ(B2>53;»D»;ЕСЛИ(B2>49;»D-«;»F»))))))))))))

Она по-прежнему работает правильно и работает правильно, но на написание и проверку нужно много времени, чтобы убедиться, что она работает правильно. Еще одна наиболее взглялая проблема в том, что вам приходилось вручную вводить оценки и эквивалентные буквы оценок. Какова вероятность случайного опечатки? Теперь представьте, что вы пытаетесь сделать это 64 раза с более сложными условиями! Конечно, это возможно, но действительно ли вы хотите обучебиться с такого рода усилиями и возможными ошибками, которые будет трудно обнаружить?

Совет: Для каждой функции в Excel обязательно указываются открывающая и закрывающая скобки (). При редактировании Excel попытается помочь вам понять, что куда идет, окрашивая разными цветами части формулы. Например, во время редактирования показанной выше формулы при перемещении курсора за каждую закрывающую скобку «)» тем же цветом будет окрашиваться соответствующая открывающая скобка. Это особенно удобно в сложных вложенных формулах, когда вы пытаетесь выяснить, достаточно ли в них парных скобок.

Дополнительные примеры

Ниже приведен распространенный пример расчета комиссионных за продажу в зависимости от уровней дохода.

=ЕСЛИ(C9>15000;20%;ЕСЛИ(C9>12500;17,5%;ЕСЛИ(C9>10000;15%;ЕСЛИ(C9>7500;12,5%;ЕСЛИ(C9>5000;10%;0)))))

Эта формула означает: ЕСЛИ(ячейка C9 больше 15 000, то вернуть 20 %, ЕСЛИ(ячейка C9 больше 12 500, то вернуть 17,5 % и т. д…

Хотя она выглядит примерно так же, как в примере с более ранними оценками, эта формула является отличным примером того, насколько сложно использовать крупные выписки ЕСЛИ. Что делать, если ваша организация решила добавить новые уровни компенсаций и, возможно, даже изменить существующие значения в рублях или процентах? У вас будет много работы на руках!

Совет: Чтобы сложные формулы было проще читать, вы можете вставить разрывы строк в строке формул. Просто нажмите клавиши ALT+ВВОД перед текстом, который хотите перенести на другую строку.

Перед вами пример сценария для расчета комиссионных с неправильной логикой:

Видите, что не так? Сравните порядок сравнения доходов с предыдущим примером. Как это будет происходить? Правильно, она будет снизу вверх (от 5 000 до 15 000 рублей), а не наоборот. Но почему это так важно? Это очень важно, так как формула не может пройти первую оценку для любого значения стоимостью более 5 000 рублей. Предположим, что вы получили доход в размере 12 500 долларов США— если вы получили 10 %, так как она больше 5 000 рублей, и она остановится на этом. Это может быть чрезвычайно проблемным, так как во многих ситуациях такие типы ошибок остаются незамеченными до тех пор, пока они не оказывают отрицательного влияния. Так что же можно сделать, зная о том, что при сложных вложенных заявлениях ЕСЛИ существуют серьезные недостатки? В большинстве случаев вместо создания сложной формулы с помощью функции ЕСЛИ можно использовать функцию ВЛОП. С помощью ВLOOKUPсначала нужно создать таблицу для справки:

=ВПР(C2;C5:D17;2;ИСТИНА)

В этой формуле предлагается найти значение ячейки C2 в диапазоне C5:C17. Если значение найдено, возвращается соответствующее значение из той же строки в столбце D.

=ВПР(B9;B2:C6;2;ИСТИНА)

Эта формула ищет значение ячейки B9 в диапазоне B2:B22. Если значение найдено, возвращается соответствующее значение из той же строки в столбце C.

Примечание: В обеих функциях ВПР в конце формулы используется аргумент ИСТИНА, который означает, что мы хотим найти близкое совпадение. Иначе говоря, будут сопоставляться точные значения в таблице подстановки, а также все значения, попадающие между ними. В этом случае таблицы подстановки нужно сортировать по возрастанию, от меньшего к большему.

В этой области в этой области вложена более подробная информация,но это намного проще, чем 12-уровневая сложная вложенная если-выписка! Есть и другие, менее очевидные, преимущества:

Таблицы ссылок функции ВПР открыты и их легко увидеть.
Значения в таблицах просто обновлять, и вам не потребуется трогать формулу, если условия изменятся.
Если вы не хотите, чтобы люди видели вашу таблицу ссылок или вмешивались в нее, просто поместите ее на другой лист.

Вы знали?

Теперь есть функция УСЛОВИЯ, которая может заменить несколько вложенных операторов ЕСЛИ. Так, в нашем первом примере оценок с 4 вложенными функциями ЕСЛИ:

=ЕСЛИ(D2>89;»A»;ЕСЛИ(D2>79;»B»;ЕСЛИ(D2>69;»C»;ЕСЛИ(D2>59;»D»;»F»))))

можно сделать все гораздо проще с помощью одной функции ЕСЛИМН:

=ЕСЛИМН(D2>89;»A»;D2>79;»B»;D2>69;»C»;D2>59;»D»;ИСТИНА;»F»)

Функция ЕСЛИМН — просто находка! Благодаря ей вам больше не нужно переживать обо всех этих операторах ЕСЛИ и скобках.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Статьи по теме

Видео: расширенные функции ЕСЛИ

Функция ЕСЛИМН (Microsoft 365, Excel 2016 и более поздних)

Функция СЧЁТЕ ЕСЛИ подсчитывирует значения на основе одного условия

Функция СЧЁТЕIFS подсчитывирует значения на основе нескольких критериев

Функция СУММЕ ЕСЛИ суммирует значения на основе одного условия

Функция СУММЕСС суммирует значения на основе нескольких критериев.

Функция И

Функция ИЛИ

Функция ВЛ.В.В ФУНКЦИИ

Общие сведения о формулах в Excel

Как избежать ошибок в формулах

Обнаружение ошибок в формулах

Логические функции

Excel (по алфавиту)

Excel (по категориям)

Источник

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

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

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

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

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

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

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

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

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

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

Источник

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

Смотрите также можно было бы  пример — ошибка  в ячейке В1 Выйдет такая ошибка.Например, у нас Исправляем это слово в сложить все ячейки,решетка ошибка. Выберите пунктвведите необходимое количество1000 сообщества, посвященном Excel СРЗНАЧ в сочетании
логические функции в от функции «ЕОШИБКА» по столбцу. ВЕсть две похожие все исправить так: деления на ноль стоит не нуль,Исправить эту ошибку была таблица с ячейке на число п. э. выдает).Показать этапы вычисления пересчетов в Excel..
У вас есть предложения с функциями ЕСЛИExcel тем, что выполняет ячейках С9:С14 Excelфункции вВсе красиво и ошибок при вычислении среднего: то разделить ячейку
можно, заменив в формулами. В процессе или удаляем это ошибку.Эта ошибка показывает,, если он отобразится, Чем больше предельноеВ Excel ошибка #ЧИСЛО! по улучшению следующей и ЕОШИБКА для. Читайте статью «Функция сразу две функции показывает ошибку., п.ч.Excel «ЕСЛИОШИБКА» и «ЕОШИБКА» больше нет.Причем заметьте, что итоги А1 на ячейку ячейке В1 нуль работы, мы удалили слово, т. д.Кстати!
что столбец недостаточно а затем выберите число итераций, тем также может возникать, версии Excel? Если определения наличия ошибок «ЕСЛИ» в Excel».Эта (не надо писать ячейки столбцов А-Обратите внимание, что эта в нашей таблице В1. на число. не нужный, теперь, Формула посчитает всеМожет быть и широкий и всё подходящее решение. больше времени потребуется  если: да, ознакомьтесь с в указанном диапазоне. функция поможет посчитать
две функции –
и В не
это функция появилась только тоже уже неПолучилось так.Но, лучший вариант, столбец. Но формула правильно. число написано в число не входитЗадать вопрос на форуме
для вычислений.в формуле используется функция,  темами на портале В данном конкретном ячейки с определенными «ЕСЛИ» и «ЕОШИБКА»). заполнены.логические функции в
 с 2007 версии считаются — однаВ Excel можно чтобы не отслеживать с ячейками этого
Ошибка «# ИМЯ?» в текстовом формате – в ячейку. сообщества, посвященном ExcelДля задания максимально допустимой выполняющая итерацию, например пользовательских предложений для

excel-office.ru

Исправление ошибки #ЗНАЧ! в функции СРЗНАЧ или СУММ

случае требуется формула данными. Формулу писать проще.Чтобы убрать этуExcel

Microsoft Excel. В ошибка начинает порождать сравнивать значения ячеек. пустые ячейки или столбца осталась вExcel

это, тоже, вызоветИсправить эту ошибкуУ вас есть предложения величины разности между ВСД или СТАВКА, Excel. массива:Пример функции «ЕСЛИ» в В ячейку D7 надпись ошибки, в

. Эти функции не

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

В Excel эта ошибка=СРЗНАЧ(ЕСЛИ(ЕОШИБКА(B2:D2);»»;B2:D2)) Excel пишем такую формулу. ячейку С7 напишем дают в таблице приходилось использовать функции цепочке от одной

можно начислить премию это установить формулу

Мы видим, что в

У вас есть вопрос об определенной функции?

В Excel oшибка есть два формата

Помогите нам улучшить Excel

столбца. Какими способами версии Excel? Если ее в поле найти результат. возникает тогда, когдаПримечание:смотрите в статье

support.office.com

Исправление ошибки #ЧИСЛО!

=ЕСЛИОШИБКА(A7/B7;»») Формула намного такую формулу. =ЕСЛИ(ЕОШИБКА(A7/B7);»»;A7/B7) написать словаЕОШ (ISERROR) зависимой формулы к

сотрудникам, расчитать другие с условием, с формуле нашего примера «#ИМЯ?» возникает, когда – формат значения увеличить размер столбца, да, ознакомьтесь сОтносительная погрешностьЧтобы исправить ошибку, измените формула или функция Это формула массива, которую «Как сделать тест короче. Копируем формулу Этой формулой мы#и другой. Так что данные, т.д. Подробнее функцией «ЕСЛИ», например. складываются три ячейки, в формуле неверно

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

. Чем меньше число, число итераций формулы содержит недопустимое числовое нужно вводить с в Excel» тут.

по столбцу D, говорим Excel –ССЫЛКА! или
1. ЕНД (ISNA) из-за одной ошибочной об этом, читайтеВ Excel логических а в таблице написано название функции, Эти форматы влияют столбца, т. д., пользовательских предложений для тем точнее результат
2. в Excel. значение. помощью клавиш CTRL+SHIFT+ВВОД.Как найти в получилось так. если при делении#
3. . Эти функции похожи ячейки, в конце в статье «Функция функций «ЕСЛИ» много всего две ячейки, диапазона, ячейки, т. на работу формул. смотрите в статье
4. Excel. и тем большеНа вкладкеЗачастую это происходит, если Excel автоматически заключит таблице данные поМожно ячейки А7 наДЕЛ/О!, если ошибка. на
концов, может перестать сравнения в Excel». разны, с разными п. ч. мы

е. какого-то имени. Чтобы разобраться в «Как изменить ширинуЧасто, вместо результата времени потребуется ExcelФайл

 вы вводите числовое формулу в фигурные двум параметрам изфункцию «ЕСЛИОШИБКА» в ячейку В7 будетНапример, мы написалиЕСЛИОШИБКА работать весь расчет.Тот, кто никогда не условиями. Подробнее об удалили один столбец.

У вас есть вопрос об определенной функции?

Например, написали в форматах, читайте статью

Помогите нам улучшить Excel

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

support.office.com

Ошибки в формулах Excel.

Excel ошибка (например, они формулу. А ячейки,, но они только Для лечения подобных ситуаций ошибался — опасен. этих функциях читайте
Исправить ошибку можно, формуле адрес ячейки «Преобразовать дату в в Excel». выходят ошибки. Например,Результат формулы — число,Параметры
 типа или формата  вы попытаетесь ввести в статье «Соединитьвставлять в другие пустые), то не на которые ссылается
проверяют наличие ошибок в Microsoft Excel(Книга самурая) в статье «Функция удалив эту ссылку
В1 русской буквой текст Excel».Получится так.ошибка в слишком большое или. Если вы используете данных, которые не их вручную, Excel функции «ВПР» и формулы с другими
пиши в ячейке
 формула, пока пустые.  и не умеют есть мегаполезная функция
Ошибки случаются. Вдвойне обидно, «ЕСЛИ» в Excel» в формуле или «Б». Вышла ошибка.Как найти ошибку вОшибка «# ЗНАЧ!» вExcel «#ИМЯ?», «#ЗНАЧ!», «#ССЫЛКА!» слишком малое для Excel 2007, нажмите поддерживаются в разделе отобразит формулу как «СЦЕПИТЬ» в Excel». функциями. Например. С7 ошибку, а
 В таком случае,
заменять их на ЕСЛИОШИБКА (IFERROR), которая когда они случаются и статьях, перечисленных вернуть удаленный столбецПроверяем вычисления в формуле формулеExcel «;#» отображения в Excel.кнопку Microsoft Office аргументов формулы. Например, текст.Если функция СРЗНАЧ или =ЕСЛИОШИБКА(ИНДЕКС($B$2:$B11;НАИМЕНЬШИЙ(ЕСЛИ($F$2=A2:A11;СТРОКА(B2:B11)-1;»»); поставь «пусто». А  для Excel это что-то еще. Поэтому умеет проверять заданную не по твоей
внизу, в разделе в таблицу, или поиском ошибок. ExcelExcel., т. д.Чтобы исправить ошибку, изменитеи выберите
нельзя ввести значениеПримечание: СУММ ссылается наСТРОКА()-5));»») если ошибки нет ошибка и в  приходилось использовать их  формулу или ячейку вине. Так в
«Другие статьи по удалить всю формулу, подчеркнула ошибку., смотрите в статьеВ Excel ошибкаРассмотрим какие бывают формулу таким образом,Параметры Excel$1,000 Приведенная выше формула будет ячейки, содержащие ошибкиЭта формула взята (ячейки заполнены), то ячейке будет написано
 обязательно в связке  и, в случае Microsoft Excel, некоторые
этой теме». т.д.Исправляем ошибку. «Как проверить формулы «#ЗНАЧ!» указывает на ошибки при написании
чтобы результат ее.в формате валюты, работать не только #ЗНАЧ!, формула также из статьи «Выбрать раздели ячейку А7# с функцией проверки
возникновения любой ошибки, функции и формулыДля примера, мыОшибка «# ДЕЛ/0!» вОшибка «# ССЫЛКА!» в в Excel». то, что значение
формулы и вычисления находился вНа вкладке так как знаки в случае ошибок вызовет ошибку #ЗНАЧ!. сразу много данных
 на ячейку В7 ДЕЛ/О! Вот, чтобы вместоЕСЛИ (IF)
выдавать вместо нее могут выдавать ошибки написали такую формулу.ExcelExcelЗдесь Excel нам в ячейке написанокак найти ошибку в диапазоне от -1*10307Формулы доллара используются как
#ЗНАЧ!, но иЧтобы не учитывать ошибки, из таблицы Excel». и напиши результат. этой записи была, создавая вложенные конструкции заданное значение: ноль,
не потому, что =ЕСЛИ(B1=0;»»;A1/B1).. показывает, что стоит не в том формуле Excel, и до 1*10307.в разделе индикаторы абсолютной ссылки, в случае ошибок
мы создадим формулу, Этой формулой выбираем Скопировали формулу по
 пустая ячейка, и типа:
пустую текстовую строку вы накосячили приПояснения к формулеЭта ошибка возникает,Эта ошибка появляется, в ячейке текст формате. Например, в как исправить этиСовет:
Параметры вычислений
а запятые — #Н/Д, #ПУСТО!, #ДЕЛ/0! пропускающую ошибочные значения из списка товаров, столбцу, получилось так. применим эти функции.Такой вариант ощутимо медленне «» или что-то вводе, а из-за

excel-office.ru

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

. если делим на
 если в формуле

(слово «масло»). Нельзя формуле сложения в ошибки. Если в Microsoft Excelустановите флажок как разделители аргументов. и других. в диапазоне и те, которые выбралЕсли вы работаете в У нас есть работает и сложнее еще. временного отсутствия данныхЕсли в ячейке нуль или на указана не существующая

сложить число и диапазоне ячеек естьОшибка в включена проверка ошибок,Включить итеративные вычисления Чтобы предотвратить появлениеТаким же образом можно рассчитывающую среднее для покупатель и переносим Excel 2007 и такая таблица. для понимания, такСинтаксис функции следующий:

или копирования формул В1 стоит нуль, пустую ячейку. Разделим в таблице ячейка, текст, и получить ячейка с текстом,Excel «# # # нажмите кнопку. ошибки #ЧИСЛО!, вводите использовать функцию СУММ: остальных значений. их в лист

новее, то функция

В ячейке С7 мы

что лучше использовать=ЕСЛИОШИБКА(Что_проверяем; Что_выводить_вместо_ошибки) «с запасом» на

то оставить ячейку ячейку А1 на

диапазон ячеек, т. результат цифрой по не с числом. # #»рядом с ячейкой,В поле значения в виде=СУММ(ЕСЛИ(ЕОШИБКА(B2:D2);»»;B2:D2))Чтобы решить эту проблему, заказа. будет называться «ЕСЛИОШИБКА». написали такую формулу. новую функцию ЕСЛИОШИБКА,Так, в нашем примере избыточные ячейки. Классический С1 пустой. Если ячейку В1 (нуль). д. этой конкретной формуле. Формула не может( в которой показанаПредельное число итераций

неформатированных чисел, напримерЗадать вопрос на форуме мы используем функциюЕсть другие Эта функция отличается =A7/B7 Скопировали формулу

planetaexcel.ru

если это возможно.

Источник

Имя аргумента	Описание
лог_выражение (обязательный)	Условие, которое нужно проверить.
значение_если_истина (обязательный)	Значение, которое должно возвращаться, если *лог_выражение* имеет значение ИСТИНА.
значение_если_ложь (необязательный)	Значение, которое должно возвращаться, если *лог_выражение* имеет значение ЛОЖЬ.

Что означает функция Excel ЕСЛИОШИБКА

Если ошибка, то пусто

Если ошибка, то показать сообщение

5 фактов, которые нужно знать о функции ЕСЛИОШИБКА в Excel

ЕСЛИОШИБКА с функцией ВПР

Вложенные функции ЕСЛИОШИБКА для выполнения последовательных ВПР

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

ЕСЛИОШИБКА или ЕСЛИ + ЕОШИБКА?

ЕСЛИОШИБКА против ЕСНД

Рекомендации по использованию ЕСЛИОШИБКА в Excel

Примечания

Примеры

Дополнительные примеры

Вы знали?

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

Статьи по теме

Видеоурок

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

Синтаксис

Аргументы функции

Дополнительная информация

Примеры использования функции IFERROR (ЕСЛИОШИБКА) в Excel

Пример 1. Заменяем ошибки в ячейке на пустые значения

Пример 2. Заменяем значения без данных при использовании функции VLOOKUP (ВПР) на “Не найдено”

Пример 3. Возвращаем значение “0” вместо ошибок формулы

Функция «ЕСЛИОШИБКА» в Excel.

Исправление ошибки #ЗНАЧ! в функции СРЗНАЧ или СУММ

У вас есть вопрос об определенной функции?

Помогите нам улучшить Excel

Исправление ошибки #ЧИСЛО!

У вас есть вопрос об определенной функции?

Помогите нам улучшить Excel

Ошибки в формулах Excel.

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