Функция 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;»») — русская версия
В данном случае функция проверит, выдает ли формула в ячейке ошибку, и, при её наличии, выдаст пустой результат.
В качестве результата формулы, исправляющей ошибки, вы можете указать любой текст или значение, например, с помощью следующей формулы:
=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”:
Функция
ЕСЛИОШИБКА()
, английский вариант IFERROR(), п
роверяет выражение на равенство значениям
#Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО! Если проверяемое выражение или значение в ячейке содержит ошибку, то функция возвращает определенное для этого случая значение, в противном случае — результат вычисления выражения или содержимое ячейки.
Функция
ЕСЛИОШИБКА()
впервые появилась в EXCEL 2007.
Синтаксис функции
ЕСЛИОШИБКА
(
значение
;
значение_при_ошибке
)
Значение
— аргумент, проверяемый на возникновение ошибок.
Значение_при_ошибке
— значение, возвращаемое при ошибке.
Для обработки ошибок #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО! обычно используют формулу вида (см.
файл примера
):
=ЕСЛИОШИБКА(A2/B2;»Ошибка расчете»)
Формула проверяет на предмет ошибки результат вычисления
A2/B2.
Если результат вычисления не является ошибкой
#Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО!
, то формула возвращает результат вычисления
A2/B2
, если обнаруживает, то возвращает строку
Ошибка в расчете.
Функция
ЕСЛИОШИБКА()
vs
ЕОШИБКА()
В отличие от функции
ЕСЛИОШИБКА()
функция
ЕОШИБКА()
не умеет самостоятельно обрабатывать ошибку — приходится задействовать функцию
ЕСЛИ()
:
=ЕСЛИ(ЕОШИБКА(A2/B2);»Ошибка в расчетах»;A2/B2)
Т.к. функция
ЕСЛИОШИБКА()
впервые появилась в EXCEL 2007, то в более ранних версиях придется использовать функцию
ЕОШИБКА()
.
Обратите внимание, что использована именно
ЕОШИБКА()
, а не
ЕОШ()
, т.к. для последней ошибка #Н/Д ошибкой не является (см. рисунок выше).
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for 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 или попросить помощи в сообществе Answers community.
Нужна дополнительная помощь?
- Что делает ЕСЛИОШИБКА?
- Синтаксис
-
Пример 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 поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!
Тот, кто никогда не ошибался — опасен.
(Книга самурая)
Ошибки случаются. Вдвойне обидно, когда они случаются не по твоей вине. Так в Microsoft Excel, некоторые функции и формулы могут выдавать ошибки не потому, что вы накосячили при вводе, а из-за временного отсутствия данных или копирования формул «с запасом» на избыточные ячейки. Классический пример — ошибка деления на ноль при вычислении среднего:
Причем заметьте, что итоги в нашей таблице тоже уже не считаются — одна ошибка начинает порождать другие, передаваясь по цепочке от одной зависимой формулы к другой. Так что из-за одной ошибочной ячейки, в конце концов, может перестать работать весь расчет.
Для лечения подобных ситуаций в Microsoft Excel есть мегаполезная функция ЕСЛИОШИБКА (IFERROR), которая умеет проверять заданную формулу или ячейку и, в случае возникновения любой ошибки, выдавать вместо нее заданное значение: ноль, пустую текстовую строку «» или что-то еще.
Синтаксис функции следующий:
=ЕСЛИОШИБКА(Что_проверяем; Что_выводить_вместо_ошибки)
Так, в нашем примере можно было бы все исправить так:
Все красиво и ошибок больше нет.
Обратите внимание, что эта функция появилась только с 2007 версии Microsoft Excel. В более ранних версиях приходилось использовать функции ЕОШ (ISERROR) и ЕНД (ISNA). Эти функции похожи на ЕСЛИОШИБКА, но они только проверяют наличие ошибок и не умеют заменять их на что-то еще. Поэтому приходилось использовать их обязательно в связке с функцией проверки ЕСЛИ (IF), создавая вложенные конструкции типа:
Такой вариант ощутимо медленне работает и сложнее для понимания, так что лучше использовать новую функцию ЕСЛИОШИБКА, если это возможно.
Функция ЕСЛИОШИБКА является достаточно полезной при обработке возможных ошибок в вычислениях, позволяет поддерживать порядок на листе Excel. Например, если случается деление на 0, можно вывести запись о том, что знаменатель еще не рассчитан.
Описание функции ЕСЛИОШИБКА
Данная функция возвращает указанное значение, если вычисление по формуле вызывает ошибку; в противном случае функция возвращает результат формулы. Функция ЕСЛИОШИБКА позволяет перехватывать и обрабатывать ошибки в формулах.
Синтаксис
=ЕСЛИОШИБКА(значение; значение_при_ошибке)Аргументы
значениезначение_при_ошибке
Обязательный аргумент. Проверяемый аргумент на возникновение ошибок.
Обязательный аргумент. Значение, возвращаемое при ошибке при вычислении по формуле. Возможны следующие типы ошибок: #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? и #ПУСТО!.
Замечания
- Если «значение» или «значение_при_ошибке» является пустой ячейкой, функция ЕСЛИОШИБКА рассматривает их как пустые строковые значения («»).
- Если «значение» является формулой массива, функция ЕСЛИОШИБКА возвращает массив результатов для каждой ячейки диапазона, указанного в значении. См. второй пример ниже.
Пример
Видео работы функции
Подсчет уникальных записей диапазона в ExcelПриведение расчетов в порядок
Дополнительные материалы
Подсчет уникальных записей диапазона в Excel
#Руководства
- 18 янв 2023
-
0
Показали, как работать с логическими функциями Excel: ИСТИНА, ЛОЖЬ, И, ИЛИ, НЕ, ЕСЛИ, ЕСЛИОШИБКА, ЕОШИБКА, ЕПУСТО.
Иллюстрация: Merry Mary для Skillbox Media
Рассказывает просто о сложных вещах из мира бизнеса и управления. До редактуры — пять лет в банке и три — в оценке имущества. Разбирается в Excel, финансах и корпоративной жизни.
Логические функции в Excel проверяют, выполняются ли заданные условия в выбранном диапазоне таблицы. Пользователь указывает критерии, которые нужно проверить, — функции проверяют эти критерии и выдают результат: ИСТИНА или ЛОЖЬ.
Также логические функции используют в сложных расчётах для того, чтобы убрать промежуточные шаги вычисления и объединить несколько действий в одной ячейке. Это значительно сокращает время на обработку данных.
В статье рассмотрим основные логические функции Excel — расскажем, для чего они нужны, и на примерах покажем, как они работают.
- Функции ИСТИНА и ЛОЖЬ
- Функции И и ИЛИ
- Функция НЕ
- Функция ЕСЛИ
- Функция ЕСЛИОШИБКА
- Функция ЕОШИБКА
- Функция ЕПУСТО
В конце расскажем, как узнать больше о работе в Excel.
Функции ИСТИНА и ЛОЖЬ не используют самостоятельно — только внутри других логических функций. Они нужны, чтобы отмечать значения как истинные или ложные.
Функция ИСТИНА возвращает только истинные значения. Её синтаксис: =ИСТИНА ().
Функция ЛОЖЬ — возвращает только ложные значения. Её синтаксис: =ЛОЖЬ ().
Функции И и ИЛИ нужны для того, чтобы показать связь между условиями пользователя. Они определяют, будут соблюдены все критерии или только некоторые из них.
Функция И. Её используют, чтобы показать, что указанные число или текст должны соответствовать одновременно всем критериям. В этом случае функция возвращает значение ИСТИНА. Если один из критериев не соблюдается, функция И возвращает значение ЛОЖЬ.
Синтаксис функции И такой: =И(логическое_значение1;логическое_значение2;…), где «логическое_значение» — критерии пользователя, которые функция будет проверять. Всего может быть до 255 критериев.
Пример работы функции И. Проверим, соблюдены ли два условия:
- число 662 больше 300;
- число 8626 больше 9000.
Для этого выберем любую ячейку и в строке формул введём: =И(A1>300;A2>9000),
где А1 — ячейка с числом 662, А2 — ячейка с числом 8626.
Нажмём Enter. Функция возвращает значение ЛОЖЬ — один из критериев не соблюдён (число 8626 < 9000).
Скриншот: Excel / Skillbox Media
Проверим другие критерии:
- число 662 меньше 666;
- число 8626 больше 5000.
Снова выберем любую ячейку и в строке формул введём: =И(A1<666;A2>5000).
Функция возвращает значение ИСТИНА — оба критерия соблюдены.
Скриншот: Excel / Skillbox Media
Функция ИЛИ. Её используют, чтобы показать, что указанные число или текст должны соответствовать одному из критериев. Если хотя бы один критерий соблюдён, функция возвращает значение ИСТИНА. Если все критерии не соблюдены, функция ИЛИ возвращает значение ЛОЖЬ.
Синтаксис функции ИЛИ: =ИЛИ(логическое_значение1;логическое_значение2;…).
Максимальное количество логических значений (критериев) — тоже 255.
Пример работы функции ИЛИ. Проверим три критерия:
- число 662 меньше 666;
- число 8626 больше 5000;
- число 567 больше 786.
В строке формул введём: =ИЛИ(A1<666; A2>5000;A3>786).
Функция возвращает значение ИСТИНА, несмотря на то, что один критерий не соблюдён (число 567 < 786).
Скриншот: Excel / Skillbox Media
Проверим другие критерии:
- число 662 меньше 500;
- число 8626 больше 9000;
- число 567 больше 600.
В строке формул введём: =ИЛИ(A1<500;A2>9000;A3>600).
Функция возвращает значение ЛОЖЬ, так как ни один из критериев не соблюдён.
Скриншот: Excel / Skillbox Media
С помощью этой функции возвращают значения, которые противоположны по отношению к заданному параметру.
Если в качестве параметра функции НЕ указать ложное значение — она вернёт значение ИСТИНА. Наоборот, если указать истинное значение, функция вернёт ЛОЖЬ.
Синтаксис функции НЕ: =НЕ(логическое_значение), где «логическое_значение» — выражение, которое нужно проверить на соответствие значениям ИСТИНА или ЛОЖЬ. В этой функции можно использовать только одно такое выражение.
Пример работы функции НЕ. Проверим выражение «662 меньше 500». Выберем любую ячейку и в строке формул введём: =НЕ(A1<500), где А1 — ячейка с числом 662.
Нажмём Enter.
Выражение «662 меньше 500» ложное. Но функция НЕ поменяла значение на противоположное и вернула значение ИСТИНА.
Скриншот: Excel / Skillbox Media
Функцию ЕСЛИ используют, когда нужно сравнить данные таблицы с критериями пользователя.
У этой функции также два результата: ИСТИНА и ЛОЖЬ. Первый результат функция выдаёт, когда значение ячейки совпадает с заданным условием, второй — когда значение условию не соответствует.
Например, если нужно определить в таблице значения меньше 1000, то значение 700 будет отмечено функцией как истинное, а значение 3500 — как ложное.
Можно задавать несколько условий одновременно. Например, найти значения меньше 300, но больше 200. В этом случае функция определит значение 100 как ложное, а 250 — как истинное. Так можно проверять не только числовые значения, но и текст.
Синтаксис функции ЕСЛИ: =ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь), где:
- лог_выражение — запрос пользователя, который функция будет проверять;
- значение_если_истина — результат, который функция принесёт в ячейку, если значение совпадёт с запросом пользователя;
- значение_если_ложь — результат, который функция принесёт в ячейку, если значение не совпадёт с запросом пользователя.
Пример работы функции ЕСЛИ. Предположим, из столбца с ценами нам нужно выбрать значения до 2 млн рублей.
Создадим отдельный столбец для результатов работы функции и выберем первую ячейку.
Скриншот: Excel / Skillbox Media
В строке формул введём: =ЕСЛИ(A2<2000000;»Подходит»;»Не подходит»)
Скриншот: Excel / Skillbox Media
Дальше нажимаем Enter и растягиваем результат из первой ячейки вниз до конца таблицы.
Скриншот: Excel / Skillbox Media
Функция показала, какие значения соответствуют условию «меньше 2000000», и отметила их как «Подходит». Значения, которые не соответствуют этому условию, отмечены как «Не подходит».
В Skillbox Media есть статья, где подробно объясняли, как использовать функцию ЕСЛИ в Excel — в частности, как запустить функцию ЕСЛИ с несколькими условиями.
Эту функцию используют, чтобы определить, есть ли ошибки в значениях таблицы или в формулах.
Синтаксис функции ЕСЛИОШИБКА: =ЕСЛИОШИБКА(значение;значение_если_ошибка), где:
- значение — выражение, которое нужно проверить;
- значение_если_ошибка — текст, число или формула, которые будут выводиться или выполняться в случае, если в результате проверки аргумента «значение» получен результат ЛОЖЬ.
Если ошибка есть, возвращается значение второго аргумента. Если ошибки нет — первого.
Пример работы функции ЕСЛИОШИБКА. Предположим, нам нужно разделить значения ячеек столбца A на значения ячеек столбца B. Проверим, будут ли ошибки в этих выражениях.
Выделим первую ячейку столбца C и введём: =ЕСЛИОШИБКА(A1/B1;»Ошибка в расчёте»)
Скриншот: Excel / Skillbox Media
Дальше нажмём Enter и растянем результат из первой ячейки вниз до конца таблицы.
Скриншот: Excel / Skillbox Media
В первой строке функция не нашла ошибок в выражении (360/60), поэтому провела расчёт и показала результат (6).
Во второй строке функция тоже не нашла ошибок (деление 0 на 76) — и показала результат расчёта (0).
В третьей строке функция нашла ошибку — делить на 0 нельзя. Поэтому вместо результата расчёта показала второй аргумент функции: «Ошибка в расчёте».
Эта функция проверяет, не содержат ли заданные ячейки ошибочных значений:
- #Н/Д
- #ЗНАЧ
- #ЧИСЛО!
- #ДЕЛ/0!
- #ССЫЛКА!
- #ИМЯ?
- #ПУСТО!
Синтаксис функции ЕОШИБКА: =ЕОШИБКА(значение), где «значение» — ячейка или диапазон ячеек, которые нужно проверить.
Если функция находит ошибочные значения — возвращает значение ИСТИНА. Если не находит — возвращает значение ЛОЖЬ.
Пример работы функции ЕОШИБКА. Обычно функцию ЕОШИБКА применяют в работе с большими диапазонами, где искать ошибочные значения самостоятельно долго и энергозатратно. Но для примера покажем, как она работает на небольшом диапазоне.
Выберем любую ячейку, в которой функция должна будет вывести результат. В строке формул введём: =ЕОШИБКА(A1:A6), где A1:A6 — диапазон, который нужно проверить.
Скриншот: Excel / Skillbox Media
Нажимаем Enter — функция возвращает значение ИСТИНА. Это значит, что она нашла ошибку в выделенном диапазоне.
Скриншот: Excel / Skillbox Media
Дальше эту функцию используют для выполнения других действий.
Например, при возникновении ошибки можно использовать функцию ЕОШИБКА в сочетании с функцией ЕСЛИ: =ЕСЛИ( ЕОШИБКА(B1);»Произошла ошибка»;B1*6).
Эта формула проверит наличие ошибки в ячейке B1. При возникновении ошибки функция ЕСЛИ возвращает сообщение «Произошла ошибка». Если ошибки отсутствуют, функция ЕСЛИ вычисляет произведение B1*6.
Функция ЕПУСТО проверяет, есть ли в выбранных ячейках какие-либо значения или эти ячейки пустые. Если ячейка пустая, функция возвращает значение ИСТИНА, если в ячейке есть данные — ЛОЖЬ.
Синтаксис функции ЕПУСТО: =ЕПУСТО(значение), где значение — ячейка или диапазон ячеек, которые нужно проверить.
Пример работы функции ЕОШИБКА. Так же как и предыдущую функцию, ЕПУСТО есть смысл применять при работе с большими диапазонами, где самостоятельно искать пустые ячейки слишком долго. Но для примера покажем, как она работает на небольшом диапазоне.
Выберем любую ячейку и в строке формул введём: =ЕПУСТО(A1:A6), где A1:A6 — диапазон, который нужно проверить.
Скриншот: Excel / Skillbox Media
Нажимаем Enter — функция возвращает значение ИСТИНА. Это значит, что она нашла пустую ячейку в выделенном диапазоне.
Скриншот: Excel / Skillbox Media
Как и в случае с функцией ЕОШИБКА, эту функцию можно использовать для выполнения других действий. Например, в сочетании с функцией ЕСЛИ.
- В Excel много функций, которые упрощают и ускоряют работу с таблицами. В этой подборке перечислили 15 статей и видео об инструментах Excel, необходимых в повседневной работе.
- В Skillbox есть курс «Excel + Google Таблицы с нуля до PRO». Он подойдёт как новичкам, которые хотят научиться работать в Excel с нуля, так и уверенным пользователям, которые хотят улучшить свои навыки. На курсе учат быстро делать сложные расчёты, визуализировать данные, строить прогнозы, работать с внешними источниками данных, создавать макросы и скрипты.
- Кроме того, Skillbox даёт бесплатный доступ к записи онлайн-интенсива «Экспресс-курс по Excel: осваиваем таблицы с нуля за 3 дня». Он подходит для начинающих пользователей. На нём можно научиться создавать и оформлять листы, вводить данные, использовать формулы и функции для базовых вычислений, настраивать пользовательские форматы и создавать формулы с абсолютными и относительными ссылками.
Другие материалы Skillbox Media по Excel
Учись бесплатно:
вебинары по программированию, маркетингу и дизайну.
Участвовать
Научитесь: Excel + Google Таблицы с нуля до PRO
Узнать больше
Функция ЕСЛИОШИБКА() в MS EXCEL
Смотрите также Знак # или функции. Если непонятно, принціп работы формулы формулах массива пустой ячейкой, функция при вычислении по=ЕСЛИОШИБКА(значение;значение_при_ошибке) значение. выше). придется использовать функцию обрабатывать ошибку - формула возвращает результатФормула проверяет на предмет при ошибке.
в EXCEL 2007. содержит ошибку, то
Синтаксис функции
Функция ЕСЛИОШИБКА(), английский вариант ЧИСЛО. и вместо
покажите пример и ЕСЛИОШИБКАВ КАКОЙ ВЕРСИИ
рассматривает их как формуле. Возможны следующиеАРГУМЕНТЫ:
Вложение функции в функциюОбзор ЕОШИБКА(). приходится задействовать функцию вычисления A2/B2, если ошибки результат вычисления
Для обработки ошибок #Н/Д,
ЕСЛИОШИБКАзначениезначение_при_ошибке функция возвращает определенное IFERROR(), проверяет выражение
ошибки выдает то объяснения, где чтоМожно конкретный пример EXCEL РАБОТАЕТ: пустые строковые значения типы ошибок: #Н/Д,1. значение —Функция ЕСЛИФункция СРЗНАЧЕСЛИ возвращает среднееОбратите внимание, что использована
Функция ЕСЛИОШИБКА() vs ЕОШИБКА()
ЕСЛИ(): обнаруживает, то возвращает A2/B2. #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!,) для этого случая
на равенство значениям
что хотите. например: не получается. и доступное объяснение?Начиная с версии («»). #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!,
обязательный аргумент, проверяемыйФункция СУММЕСЛИМН значение всех ячеек, именно ЕОШИБКА(), а=ЕСЛИ(ЕОШИБКА(A2/B2);»Ошибка в расчетах»;A2/B2) строку Ошибка вЕсли результат вычисления не
excel2.ru
Функции СРЗНАЧЕСЛИМН и ЕСЛИОШИБКА
#ЧИСЛО!, #ИМЯ? или
Значение значение, в противном #Н/Д, #ЗНАЧ!, #ССЫЛКА!,=ЕСЛИОШИБКО(ваша формула);»») выдастcasiumvikttur Excel 2007.
Хотите узнать больше?
ОГРАНИЧЕНИЯ:
#ЧИСЛО!, #ИМЯ? и
на возникновение ошибок.
Функция СЧЁТЕСЛИМН
которые удовлетворяют нескольким
не ЕОШ(), т.к.
support.office.com
ЕСЛИОШИБКА() IFERROR()
Т.к. функция ЕСЛИОШИБКА() впервые
расчете.
является ошибкой #Н/Д,
#ПУСТО! обычно используют- аргумент, проверяемый случае — результат
#ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? пустую ячейку. столбец: Перед любой формулой: ЕСЛИОШИБКА() — неПосмотреть другие функциинет #ПУСТО!.2. значение_при_ошибке —Функция СРЗНАЧЕСЛИМН
условиям. Если результатом
для последней ошибка появилась в EXCELВ отличие от функции #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, формулу вида (см. на возникновение ошибок.
вычисления выражения или
или #ПУСТО! Если
при этом суммируется
ставьте она не
формула, это пока этой категории.
ПРИМЕРЫ ИСПОЛЬЗОВАНИЯ:КОММЕНТАРИИ:
обязательный аргумент. Значение,Функция ЕСЛИОШИБКА
является ошибка, функция #Н/Д ошибкой не
excelworld.ru
Функция ЕСЛИОШИБКА(). Какой принцип работы?
2007, то в ЕСЛИОШИБКА() функция ЕОШИБКА() #ЧИСЛО!, #ИМЯ? или файл примера):
Значение_при_ошибке содержимое ячейки.
проверяемое выражение или а при наличии помешает, сработает только что только функция.
СеляФОРМУЛЫ МАССИВА:Если аргументы значение возвращаемое при ошибкеСИНТАКСИС:
ЕСЛИОШИБКА возвращает указанное является (см. рисунок более ранних версиях не умеет самостоятельно #ПУСТО! , то=ЕСЛИОШИБКА(A2/B2;»Ошибка расчете») — значение, возвращаемоеФункция ЕСЛИОШИБКА() впервые появилась значение в ячейке
ошибки нет если ответ ошибка.Почитайте справку по: Не могу понятьФункция работает в
planetaexcel.ru
или значение_при_ошибке является
Английское название функции IFERROR() было переведено на 18 языка/языков. Для всех остальных языков применяется английское название функции. Существуют различия в переводах в разных Excel версиях
Наличие
| Excel Версии | Наличие | Категория |
|---|---|---|
| Excel (Office 365) | Да | Логические |
| Excel 2013 | Да | Логические |
| Excel 2010 | Да | Логические |
| Excel 2007 | Да | Логические |
| Excel 2003 | Нет | — |
Ссылка язык
| язык | Обозначение |
|---|---|
| English | IFERROR |
Переводы
| язык | Обозначение |
|---|---|
| Basque | BALDIN.ERROREA |
| Catalan | SIERROR |
| Danish | HVIS.FEJL |
| Dutch | ALS.FOUT |
| Finnish | JOSVIRHE |
| French | SIERREUR |
| Galician | SEERRO |
| German | WENNFEHLER |
| Hungarian | HAHIBA |
| Italian | SE.ERRORE |
| Norwegian | HVISFEIL |
| Polish | JEŻELI.BŁĄD |
| Portuguese, Brazil | SEERRO |
| Portuguese, Portugal | SE.ERRO |
| Russian | ЕСЛИОШИБКА |
| Spanish | SI.ERROR |
| Swedish | OMFEL |
| Turkish | EĞERHATA |
Отличающиеся переводы для Excel 2010
| язык | Обозначение |
|---|---|
| Czech | CHYBHODN |
| Danish | HVISFEJL |
| Spanish | SIERROR |
Ссылки на службу поддержки Microsoft для функции ЕСЛИОШИБКА()
Примечание: Microsoft находится в процессе переработки ссылок и содержания службы поддержки . Поэтому в настоящее время не исключено, что некоторые из указанных ссылок не работают или ведут на ошибочную страницу. Ссылки будут актуализированы как только это будет возможно.
| язык | |||
|---|---|---|---|
| Arabic | Finnish | Kazakh | Russian |
| Basque | French | Konkani | Serbian |
| Bulgarian | Galician | Korean | Slovak |
| Catalan | German | Latvian | Slovenian |
| Chinese — Simplified | Greek | Lithuanian | Spanish |
| Chinese — Traditional | Gujarati | Malaysian | Swedish |
| Croatian | Hebrew | Marathi | Thai |
| Czech | Hindi | Norwegian | Turkish |
| Danish | Hungarian | Polish | Ukrainian |
| Dutch | Indonesian | Portuguese, Brazil | Vietnamese |
| English | Italian | Portuguese, Portugal | — |
| Estonian | Japanese | Romanian | — |
ЕСЛИОШИБКА функция excel используется для обработки ошибок в Excel, эта функция используется для отображения результата при возникновении ошибки в любой формуле или аргументе, мы используем эту функцию с другой функцией, чтобы уведомить пользователя о том, что может быть ошибочно в формуле, эта функция принимает два аргумента, которые имеет значение, если ошибки нет, и при ее наличии отображается другое значение.
Синтаксис
- значение может быть любым значением, ссылкой или даже формулой
- value_if_error это значение, которое возвращается в качестве вывода при возникновении ошибки
Примеры обработки ошибок в Excel с помощью функции ЕСЛИОШИБКА
Вы можете скачать этот шаблон Excel с функцией ЕСЛИОШИБКА здесь — Шаблон Excel с функцией ЕСЛИОШИБКА
Пример # 1 — Обработка ошибки # Н / Д
Предположим, у нас есть два списка сотрудников, в столбце A и B, в столбце B, есть некоторые сотрудники, которых нет в столбце A, и мы хотим узнать имена тех сотрудников, которых нет в списке A.
Итак, мы применили формулу ВПР к правому соседнему столбцу списка B, чтобы найти общие имена в обоих списках и выделить имена, которых нет в списке A.
Формула ВПР в C2 и перетаскивая ее вниз, мы имеем
= ВПР (B2; $ A: $ A; 1,0)
Как вы можете видеть выше, некоторые имена не могут быть найдены с помощью формулы ВПР; следовательно, они сгенерировали ошибку # Н / Д, что означает, что ВПР не удалось найти эти имена в списке сотрудников в столбце A.
Теперь, если мы хотим, чтобы # N / A было заменено другим значением, например, ‘Имя отсутствует в списке A, ‘ поэтому мы воспользуемся функцией ЕСЛИОШИБКА, чтобы заменить ошибку # N / A,
Итак, передадим формулу поиска, ВПР (B2; $ A: $ A; 1,0) как первый аргумент и Имя отсутствует в списке А как второй аргумент функции ЕСЛИОШИБКА. Итак, формула принимает вид
= ЕСЛИОШИБКА (ВПР (B2; $ A: $ A; 1,0); «Имя отсутствует в списке A»)
Перетаскивая эту формулу в последнюю ячейку, мы получаем замененное значение # N / A с переданным значением.
Пример №2 — Обработка # DIV / 0! ошибка
У нас есть суммы в столбце А, которые необходимо распределить поровну между указанным числом людей.
Итак, когда мы применяем формулу = Количество / Количество людей
Получаем ошибку # DIV / 0! в C4, когда количество людей равно 0. Когда мы делим числовое значение на ноль, мы получаем эту ошибку.
Теперь мы хотим заменить эту ошибку значением ‘Кол-во лиц <1‘, мы будем использовать функцию ЕСЛИОШИБКА.
= ЕСЛИОШИБКА ((A2 / B2)); Число лиц <1 ‘)
Пример № 3 — Обработка #NAME? ошибка
Предположим, у нас есть некоторые числа, которые нужно сложить, и по ошибке, если кто-то напишет неправильную формулу для суммы, мы получим # ИМЯ? ошибка.
Вместо SUM, если пользователь напишет неправильную формулу, #NAME? генерируется ошибка.
Используя функцию Excel ЕСЛИОШИБКА, мы можем обработать эту ошибку, просто передав второму аргументу значение, например ‘Введена неправильная формула. ‘
Пример # 4 — Обработка #NULL! ошибка
Предположим, у нас есть 3 значения в ячейках A2, A3 и A4, и мы хотим их суммировать.
В ячейке A5 мы использовали оператор «плюс», чтобы добавить их, A2 + A3 A4, и вместо того, чтобы помещать оператор «+» между A3 и A4, мы ввели разделитель, нажав пробел, мы получили #NULL! Ошибка.
Итак, чтобы исправить эту ошибку, мы можем использовать функцию ЕСЛИОШИБКА, изменив формулу на
= ЕСЛИОШИБКА (A2 + A3 A4); (СУММ (A2: A4)))
Как видите, мы использовали другую формулу, СУММ, для вычисления суммы трех заданных значений и передали ее в качестве второго аргумента функции ЕСЛИОШИБКА.
Пример # 5 — Обработка ошибки #NUM
У нас есть некоторые числа, указанные в столбце A, и мы хотим найти квадратные корни из этих чисел.
В Excel у нас есть SQRT в excel для вычисления квадратного корня из числа, когда мы применяем эту формулу для получения квадратного корня из чисел.
Получаем ошибку # ЧИСЛО! для отрицательных значений, поскольку мы не можем найти квадратный корень из отрицательного числа. Итак, excel выбрасывает # ЧИСЛО! ошибка, когда функция КОРЕНЬ применяется к отрицательному числу.
Используя функцию Excel ЕСЛИОШИБКА, мы можем передать значение ‘Отрицательное число‘для отрицательных значений, чтобы избежать этой ошибки.
Пример №6 — Обработка #REF! ошибка
Предположим, у нас есть два числа в ячейках A2 и A3, и мы хотим разделить значение ячейки A на значение ячейки B.
Теперь, если мы удалим третью строку, выбрав 3rd row полностью, мы получаем #REF! ошибка в ячейке C2
Поскольку ссылка была удалена, Excel не может найти справочное значение в формуле; следовательно, он выдает #REF! ошибка.
Используя функцию Excel IFERROR, мы можем передать value_if_error, ‘Ссылка удалена. ‘
Пример №7 — Обработка #VALUE! ошибка
У нас есть два значения в ячейках A2 и A3, A2, и мы хотим добавить эти два значения в ячейки A2 и A3,
A2 содержит числовое значение, а A3 — текстовое значение. Когда мы складываем эти два значения, мы получаем #VALUE! Ошибка.
Поскольку в формуле используется неправильный тип данных, она вернула #VALUE! ошибка
Исправляя это с помощью ЕСЛИОШИБКА, мы имеем
Итак, мы можем видеть, что функция ЕСЛИОШИБКА может быть очень полезна при ошибках в Excel и настройке вывода, когда ошибка возникает в соответствии с потребностями или требованиями.
УЗНАТЬ БОЛЬШЕ >>
Post Views: 1 062