Когда вы работаете с данными и формулами в Excel, вы обязательно столкнетесь с ошибками.
Для обработки ошибок в Excel предусмотрена полезная функция - ЕСЛИОШИБКА.
Прежде чем мы перейдем к механике использования функции ЕСЛИОШИБКА в Excel, давайте сначала рассмотрим различные типы ошибок, с которыми вы можете столкнуться при работе с формулами.
Типы ошибок в Excel
Знание ошибок в Excel поможет вам определить возможную причину и лучший способ их устранения.
Ниже приведены типы ошибок, которые вы можете найти в Excel.
# Н / Д Ошибка
Это называется ошибкой «Значение недоступно».
Вы увидите это, если воспользуетесь формулой поиска и не сможете найти значение (следовательно, недоступно).
Ниже приведен пример, в котором я использую формулу ВПР для нахождения цены элемента, но она возвращает ошибку, если не может найти этот элемент в массиве таблицы.
# DIV / 0! Ошибка
Вы, вероятно, увидите эту ошибку, когда число разделится на 0.
Это называется ошибкой деления. В приведенном ниже примере это дает # DIV / 0! погрешность, так как значение величины (делитель в формуле) равно 0.
#СТОИМОСТЬ! Ошибка
Ошибка значения возникает при использовании неправильного типа данных в формуле.
Например, в приведенном ниже примере, когда я пытаюсь добавить ячейки с числами и символом A, возникает ошибка значения.
Это происходит потому, что вы можете добавлять только числовые значения, но вместо этого я попытался добавить число с текстовым символом.
# ССЫЛКА! Ошибка
Это называется ошибкой ссылки, и вы увидите это, когда ссылка в формуле больше не действительна. Это может быть тот случай, когда формула ссылается на ссылку на ячейку, а эта ссылка на ячейку не существует (происходит, когда вы удаляете строку / столбец или рабочий лист, на который ссылается формула).
В приведенном ниже примере, хотя исходная формула была = A2 / B2, когда я удалил столбец B, все ссылки на него стали #REF! и он также дал #REF! ошибка в результате формулы.
# ИМЯ ОШИБКА
Эта ошибка, вероятно, является результатом неправильного написания функции.
Например, если вместо VLOOKUP вы по ошибке используете VLOKUP, это выдаст ошибку имени.
#NUM ERROR
Ошибка числа может возникнуть, если вы попытаетесь вычислить очень большое значение в Excel. Например, = 187 549 вернет числовую ошибку.
Другая ситуация, когда вы можете получить ошибку ЧИСЛО, - это когда вы передаете в формулу недопустимый числовой аргумент. Например, если вы вычисляете квадратный корень, если число и вы указываете отрицательное число в качестве аргумента, он вернет числовую ошибку.
Например, в случае функции квадратного корня, если вы укажете отрицательное число в качестве аргумента, она вернет числовую ошибку (как показано ниже).
Хотя я привел здесь только несколько примеров, может быть много других причин, которые могут привести к ошибкам в Excel. Когда вы получаете ошибки в Excel, вы не можете просто оставить их там. Если данные в дальнейшем используются в расчетах, необходимо убедиться, что ошибки обрабатываются правильно.
Функция Excel ЕСЛИОШИБКА - отличный способ справиться со всеми типами ошибок в Excel.
Функция ЕСЛИОШИБКА в Excel - обзор
Используя функцию ЕСЛИОШИБКА, вы можете указать, что должна возвращать формула вместо ошибки. Если формула не возвращает ошибку, возвращается ее собственный результат.
Синтаксис функции ЕСЛИОШИБКА
= ЕСЛИОШИБКА (значение; значение_если_ошибка)
Входные аргументы
- стоимость - это аргумент, который проверяется на наличие ошибки. В большинстве случаев это либо формула, либо ссылка на ячейку.
- value_if_error - это значение, которое возвращается в случае ошибки. Оценивались следующие типы ошибок: # N / A, #REF !, # DIV / 0 !, #VALUE !, #NUM !, #NAME? И #NULL !.
Дополнительные замечания:
- Если вы используете «» в качестве аргумента value_if_error, ячейка ничего не отображает в случае ошибки.
- Если аргумент value или value_if_error относится к пустой ячейке, он обрабатывается функцией Excel ЕСЛИОШИБКА как пустое строковое значение.
- Если аргумент значения является формулой массива, ЕСЛИОШИБКА вернет массив результатов для каждого элемента в диапазоне, указанном в значении.
Функция ЕСЛИОШИБКА в Excel - Примеры
Вот три примера использования функции ЕСЛИОШИБКА в Excel.
Пример 1 - вернуть пустую ячейку вместо ошибки
Если у вас есть функции, которые могут возвращать ошибку, вы можете заключить ее в функцию ЕСЛИОШИБКА и указать пустое значение в качестве значения, возвращаемого в случае ошибки.
В примере, показанном ниже, результатом в D4 будет # DIV / 0! ошибка, поскольку делитель равен 0.
В этом случае вы можете использовать следующую формулу, чтобы вернуть пустое значение вместо уродливой ошибки DIV.
= ЕСЛИОШИБКА (A1 / A2; ””)
Эта функция ЕСЛИОШИБКА проверяет, приводит ли расчет к ошибке. Если это так, он просто возвращает пробел, как указано в формуле.
Здесь вы также можете указать любую другую строку или формулу для отображения вместо пустого поля.
Например, приведенная ниже формула вернет текст «Ошибка» вместо пустой ячейки.
= ЕСЛИОШИБКА (A1 / A2; «Ошибка»)
Примечание. Если вы используете Excel 2003 или более раннюю версию, вы не найдете в нем функции ЕСЛИОШИБКА. В таких случаях необходимо использовать комбинацию функций ЕСЛИ и ЕСТЬ ОШИБКА.
Пример 2. Возвращает «Not Found», если функция VLOOKUP не может найти значение
Когда вы используете функцию Excel VLOOKUP, и она не может найти искомое значение в указанном диапазоне, она вернет ошибку # N / A.
Например, ниже представлен набор данных с именами учащихся и их оценками. Я использовал функцию ВПР, чтобы получить оценки трех студентов (в D2, D3 и D4).
Хотя формула ВПР в приведенном выше примере находит имена первых двух учеников, она не может найти имя Джоша в списке и, следовательно, возвращает ошибку # Н / Д.
Здесь мы можем использовать функцию ЕСЛИОШИБКА, чтобы вернуть пустой или значимый текст вместо ошибки.
Ниже приведена формула, по которой вместо ошибки будет возвращено «Не найдено».
= ЕСЛИОШИБКА (ВПР (D2; $ A $ 2: $ B $ 12,2,0); «Не найдено»)
Обратите внимание, что вы также можете использовать IFNA вместо IFERROR с VLOOKUP. IFERROR будет обрабатывать все типы значений ошибок, IFNA будет работать только с ошибками # N / A и не будет работать с другими ошибками.
Пример 3 - возврат 0 в случае ошибки
Если вы не укажете значение, возвращаемое функцией ЕСЛИОШИБКА в случае ошибки, она автоматически вернет 0.
Например, если я разделю 100 на 0, как показано ниже, будет возвращена ошибка.
Однако, если я использую приведенную ниже функцию ЕСЛИОШИБКА, вместо этого она вернет 0. Обратите внимание, что вам все равно нужно использовать запятую после первого аргумента.
Пример 4 - Использование вложенной ЕСЛИОШИБКИ с функцией ВПР
Иногда при использовании ВПР может потребоваться просмотреть фрагментированную таблицу массивов. Например, предположим, что у вас есть записи о продажах на двух отдельных листах, и вы хотите найти номер позиции и увидеть ее стоимость.
Это требует использования вложенной ЕСЛИОШИБКИ с функцией ВПР.
Предположим, у вас есть набор данных, как показано ниже:
В этом случае, чтобы найти оценку Grace, вам необходимо использовать следующую вложенную формулу ЕСЛИОШИБКА:
= ЕСЛИОШИБКА (ВПР (G3, $ A $ 2: $ B $ 5,2,0), ЕСЛИОШИБКА (ВПР (G3, $ D $ 2: $ E $ 5,2,0), «Не найдено»))
Такой тип вложения формул гарантирует, что вы получите значение из любой из таблиц, и любая возвращенная ошибка будет обработана.
Обратите внимание, что в случае, если таблицы находятся на одном листе, однако в реальном примере они могут быть на разных листах.
Функция ЕСЛИОШИБКА в Excel - ВИДЕО
- Excel И функция.
- Функция ИЛИ в Excel.
- Excel НЕ Функция.
- Функция ЕСЛИ в Excel.
- Функция Excel IFS.
- Функция Excel FALSE.
- Функция Excel TRUE.
