При работе с данными и формулами в Excel вы обязательно столкнетесь с ошибками.
Для обработки ошибок в Excel предусмотрена полезная функция — функция ЕСЛИОШИБКА.
Прежде чем мы поймем механизм использования функции ЕСЛИОШИБКА в Excel, давайте сначала разберемся с различными типами ошибок, с которыми вы можете столкнуться при использовании формул.
Содержание:
Типы ошибок в Excel.
Понимание ошибок в Excel поможет вам лучше определить возможные причины и лучший способ их устранения.
Вот типы ошибок, которые вы можете найти в Excel.
#нет ошибок
Это называется ошибкой «значение недоступно».
Вы видите это, когда используете формулу поиска, а значение не найдено (и, следовательно, недоступно).
Ниже приведен пример, в котором я использую формулу ВПР, чтобы найти цену элемента, но она возвращает ошибку, когда не может найти элемент в массиве таблицы.
#ДЕЛ/0!ошибка
Вы можете увидеть эту ошибку, когда число делится на 0.
Это называется ошибкой деления.В приведенном ниже примере это дает #DIV/0!Ошибка, так как значение количества (делитель в формуле) равно 0.
#СТОИМОСТЬ!ошибка
Ошибки значений возникают при использовании в формуле неправильного типа данных.
Например, в приведенном ниже примере, когда я пытаюсь добавить ячейку с числами и символом A, выдается ошибка значения.
Это происходит потому, что вы можете добавлять только числовые значения, но я пробовал добавлять числа с текстовыми символами.
#REF! ошибка
Это называется ошибкой ссылки, и вы видите эту ошибку, когда ссылка в формуле больше недействительна.Это может произойти, когда формула ссылается на ссылку на ячейку, а эта ссылка на ячейку не существует (это происходит, когда вы удаляете строку/столбец или рабочий лист, на который ссылается формула).
В приведенном ниже примере, хотя исходная формула =A2/B2, когда я удаляю столбец B, все ссылки на него становятся #ССЫЛКА!Это также дало #REF!ошибка из-за формулы.
#ИМЯ? ошибка
Эта ошибка может быть связана с неправильным написанием функции.
Например, если вы по ошибке используете ВПР вместо ВПР, вы получите ошибку имени.
#ЧИСЛО ошибок
Ошибки Num могут возникнуть, если вы попытаетесь вычислить очень большое значение в Excel.Например, = 187 ^ 549 вернет числовую ошибку.
Другая ситуация, в которой может возникнуть ошибка NUM, — это когда вы указываете формулу с недопустимым числовым аргументом.Например, если вы вычисляете квадратный корень, он вернет числовую ошибку, если это число и вы укажете отрицательное число в качестве аргумента.
Например, в случае функции извлечения квадратного корня, если в качестве аргумента указать отрицательное число, будет возвращена числовая ошибка (показана ниже).
Хотя я показал здесь только несколько примеров, может быть много других причин ошибок в Excel.Когда вы получаете ошибку в Excel, вы не можете оставить ее там.Если данные в дальнейшем будут использоваться в расчетах, необходимо обеспечить правильную обработку ошибок.
Функция Excel ЕСЛИОШИБКА — отличный способ обработки всех типов ошибок в Excel.
Функция Excel ЕСЛИОШИБКА — обзор
Используя функцию ЕСЛИОШИБКА, вы можете указать, что вы хотите, чтобы формула возвращала вместо ошибки.Если формула не возвращает ошибок, она возвращает собственный результат.
Синтаксис функции ЕСЛИОШИБКА
=ЕСЛИОШИБКА(значение, значение_если_ошибка)
Входные параметры
- стоимость - Это параметр для проверки ошибок.В большинстве случаев это либо формула, либо ссылка на ячейку.
- значение_если_ошибка – это значение, возвращаемое при возникновении ошибки.Были оценены следующие типы ошибок: #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. Возвращает «Не найдено», когда функция ВПР не может найти значение
При использовании функции ВПР Excel возвращается ошибка #Н/Д, если искомое значение не может быть найдено в указанном диапазоне.
Например, ниже представлен набор данных с именами учащихся и их оценками.Я использовал функцию ВПР, чтобы получить оценки трех учеников (в D2, D3 и D4).
Хотя формула ВПР в приведенном выше примере нашла имена первых двух учеников, она не смогла найти имя Джоша в списке, поэтому вернула ошибку #Н/Д.
Здесь мы можем использовать функцию ЕСЛИОШИБКА, чтобы вернуть пустой или какой-либо осмысленный текст вместо ошибки.
Ниже приведена формула, которая возвращает «Не найдено» вместо ошибки.
=ЕСЛИОШИБКА(ВПР(D2,$A$2:$B$12,2,0),"Не найдено")
Обратите внимание, что вы также можете использовать IFNA вместо IFERROR в функции ВПР.В то время как IFERROR обрабатывает все типы значений ошибок, IFNA применяется только к ошибкам #N/A, а не к другим ошибкам.
Пример 3. Возврат 0 при ошибке
Если вы не укажете значение, которое функция ЕСЛИОШИБКА возвращает в случае ошибки, она автоматически вернет 0.
Например, если я разделю 100 на 0, как показано ниже, будет возвращена ошибка.
Однако, если я использую приведенную ниже функцию ЕСЛИОШИБКА, она возвращает 0.Обратите внимание, что вам все равно нужно использовать запятую после первого параметра.
Пример 4. Использование вложенной функции ЕСЛИОШИБКА с функцией ВПР
Иногда при использовании функции ВПР вам может понадобиться просмотреть таблицу фрагментации для массива.Например, предположим, что у вас есть транзакции продаж на двух отдельных листах, и вы хотите найти номер товара и увидеть его стоимость.
Для этого необходимо использовать вложенную ЕСЛИОШИБКА с функцией ВПР.
Предположим, у вас есть набор данных, который выглядит следующим образом:
В этом случае, чтобы найти оценку Грейс, вам нужно использовать следующую вложенную формулу ЕСЛИОШИБКА:
=IFERROR(VLOOKUP(G3,$A$2:$B$5,2,0),IFERROR(VLOOKUP(G3,$D$2:$E$5,2,0),"Not Found"))
Такое вложение формул гарантирует, что вы получите значения из любой таблицы и обработаете все возвращенные ошибки.
Обратите внимание, что если листы находятся на одном листе, в реальной жизни они могут быть на другом листе.
