Как использовать ЕСЛИОШИБКА в Excel с примерами формул
В этом руководстве показано, как использовать ЕСЛИОШИБКА в Excel для обнаружения ошибок и замены их пустой ячейкой, другим значением или настраиваемым сообщением. Вы узнаете, как использовать функцию ЕСЛИОШИБКА с функциями визуального просмотра и сопоставления индексов, а также как она сравнивается с ЕСЛИ ОШИБКА и IFNA.
«Дайте мне точку опоры, и я переверну землю», — сказал однажды Архимед. «Дайте мне формулу, и я заставлю ее вернуть ошибку», — сказал бы пользователь Excel. В этом руководстве мы не будем рассматривать, как возвращать ошибки в Excel, мы скорее узнаем, как предотвратить их, чтобы ваши листы были чистыми, а формулы — прозрачными.
Функция Excel ЕСЛИОШИБКА — синтаксис и основные способы использования
Функция ЕСЛИОШИБКА в Excel предназначена для обнаружения и устранения ошибок в формулах и вычислениях. Более конкретно, ЕСЛИОШИБКА проверяет формулу и, если она дает ошибку, возвращает другое значение, которое вы укажете; в противном случае возвращает результат формулы.
Синтаксис функции Excel ЕСЛИОШИБКА следующий:
ЕСЛИОШИБКА(значение, значение_если_ошибка)
Где:
- Значение (обязательно) — что проверять на наличие ошибок. Это может быть формула, выражение, значение или ссылка на ячейку.
- Value_if_error (обязательно) — что возвращать при обнаружении ошибки. Это может быть пустая строка (пустая ячейка), текстовое сообщение, числовое значение, другая формула или вычисление.
Например, при делении двух столбцов чисел можно получить кучу разных ошибок, если в одном из столбцов есть пустые ячейки, нули или текст.
Чтобы этого не произошло, используйте функцию ЕСЛИОШИБКА, чтобы перехватывать и обрабатывать ошибки нужным вам образом.
Если ошибка, то пусто
Укажите пустую строку («») для значение_если_ошибка аргумент для возврата пустой ячейки в случае обнаружения ошибки:
=ЕСЛИОШИБКА(A2/B2,»»)
Если ошибка, то показать сообщение
Вы также можете отобразить собственное сообщение вместо стандартной записи ошибок Excel:
=ЕСЛИОШИБКА(A2/B2, «Ошибка вычисления»)
5 вещей, которые вы должны знать о функции ЕСЛИОШИБКА в Excel
- Функция ЕСЛИОШИБКА в Excel обрабатывает все типы ошибок, включая #ДЕЛ/0!, #Н/Д, #ИМЯ?, #NULL!, #ЧИСЛО!, #ССЫЛКА! и #ЗНАЧ!.
- В зависимости от содержания в значение_если_ошибка аргумент, ЕСЛИОШИБКА может заменить ошибки вашим текстовым сообщением, числом, датой или логическим значением, результатом другой формулы или пустой строкой (пустой ячейкой).
- Если ценность аргумент представляет собой пустую ячейку, она обрабатывается как пустая строка (»’), но не как ошибка.
- ЕСЛИОШИБКА появилась в Excel 2007 и доступна во всех последующих версиях Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021 и Excel 365.
- Чтобы перехватывать ошибки в Excel 2003 и более ранних версиях, используйте функцию ЕОШИБКА в сочетании с ЕСЛИ, как показано на этот пример.
Примеры формулы ЕСЛИОШИБКА
В следующих примерах показано, как использовать ЕСЛИОШИБКА в Excel в сочетании с другими функциями для выполнения более сложных задач.
Excel ЕСЛИОШИБКА с функцией ВПР
Одним из наиболее распространенных применений функции ЕСЛИОШИБКА является сообщение пользователям, что искомое значение не существует в наборе данных. Для этого вы заключаете формулу ВПР в ЕСЛИОШИБКА следующим образом:
ЕСЛИОШИБКА(ВПР(…),»Не найден»)
Если искомое значение отсутствует в таблице, которую вы просматриваете, обычная формула ВПР вернет ошибку #Н/Д:
Для спокойствия пользователей заключите функцию ВПР в ЕСЛИОШИБКА и отобразите более информативное и удобное для пользователя сообщение:
=ЕСЛИОШИБКА(ВПР(A2, ‘Таблица поиска’!$A$2:$B$4, 2,ЛОЖЬ), «Не найдено»)
На снимке экрана ниже показана эта формула Iferror в Excel:
Если вы хотите перехватывать только ошибки #Н/Д, но не все ошибки, используйте функцию IFNA вместо IFERROR.
Дополнительные примеры формул Excel ЕСЛИОШИБКА ВПР можно найти в следующих руководствах:
Вложенные функции ЕСЛИОШИБКА для выполнения последовательных ВПР в Excel
В ситуациях, когда вам нужно выполнить несколько операций ВПР в зависимости от того, была ли предыдущая ВПР успешной или неудачной, вы можете вложить две или более функции ЕСЛИОШИБКА одну в другую.
Предположим, у вас есть несколько отчетов о продажах из региональных отделений вашей компании, и вы хотите получить сумму по определенному идентификатору заказа. С A2 в качестве значения поиска на текущем листе и A2: B5 в качестве диапазона поиска в 3 листах поиска (отчет 1, отчет 2 и отчет 3) формула выглядит следующим образом:
=ЕСЛИОШИБКА(ВПР(A2,’Отчет 1′!A2:B5,2,0),ЕСЛИОШИБКА(ВПР(A2,’Отчет 2′!A2:B5,2,0), ЕСЛИОШИБКА(ВПР(A2,’Отчет 3) ‘!A2:B5,2,0),»не найдено»)))
Результат будет выглядеть примерно так:
Подробное объяснение логики формулы см. в разделе Как выполнять последовательные ВПР в Excel.
ЕСЛИОШИБКА в формулах массива
Как вы, наверное, знаете, формулы массива в Excel предназначены для выполнения нескольких вычислений в рамках одной формулы. Если вы указываете формулу или выражение массива, результатом которого является массив в ценность аргумент функции ЕСЛИОШИБКА, она вернет массив значений для каждой ячейки в указанном диапазоне. В приведенном ниже примере показаны детали.
Скажем, у вас есть Общий в столбце Б и Цена в столбце C, и вы хотите рассчитать Общая численность. Это можно сделать с помощью следующей формулы массива, которая делит каждую ячейку в диапазоне B2:B4 на соответствующую ячейку в диапазоне C2:C4, а затем суммирует результаты:
=СУММ($B$2:$B$4/$C$2:$C$4)
Формула работает нормально, пока в диапазоне делителей нет нулей или пустых ячеек. Если есть хотя бы одно значение 0 или пустая ячейка, функция #DIV/0! возвращается ошибка:
Чтобы исправить эту ошибку, просто выполните деление в функции ЕСЛИОШИБКА:
=СУММ(ЕСЛИОШИБКА($B$2:$B$4/$C$2:$C$4,0))
Что делает формула, так это делит значение в столбце B на значение в столбце C в каждой строке (100/2, 200/5 и 0/0) и возвращает массив результатов {50; 40; #ДЕЛ/0!}. Функция ЕСЛИОШИБКА перехватывает все #DIV/0! ошибки и заменяет их нулями. Затем функция СУММ суммирует значения в результирующем массиве {50; 40; 0} и выводит окончательный результат (50+40=90).
Примечание. Помните, что формулы массива должны быть завершены нажатием сочетания клавиш Ctrl + Shift + Enter.
ЕСЛИ ОШИБКА против ЕСЛИ ОШИБКА
Теперь, когда вы знаете, как легко использовать функцию ЕСЛИОШИБКА в Excel, вы можете удивиться, почему некоторые люди все еще склоняются к использованию комбинации ЕСЛИ НЕОШИБКА. Есть ли у него преимущества по сравнению с IFERROR? Никто. В старые недобрые времена Excel 2003 и более ранних версий, когда ЕСЛИ ОШИБКА не существовало, ЕСЛИ ОШИБКА была единственным возможным способом перехвата ошибок. В Excel 2007 и более поздних версиях это просто немного более сложный способ достижения того же результата.
Например, чтобы отловить ошибки Vlookup, вы можете использовать любую из приведенных ниже формул.
В Excel 2007 — Excel 2016:
ЕСЛИОШИБКА(ВПР(…), «Не найден»)
Во всех версиях Excel:
ЕСЛИ(ЕОШИБКА(ВПР(…)), «Не найдено», ВПР(…))
Обратите внимание, что в формуле IF IERROR VLOOKUP вам нужно дважды выполнить Vlookup. На простом английском формула может быть прочитана следующим образом: Если ВПР приводит к ошибке, вернуть «Не найдено», в противном случае вывести результат ВПР.
А вот реальный пример формулы Excel If Iserror Vlookup:
=ЕСЛИ(ЕОШИБКА(ВПР(D2, A2:B5,2,ЛОЖЬ)),»Не найдено», ВПР(D2, A2:B5,2,ЛОЖЬ))
Дополнительные сведения см. в разделе Использование функции ЕОШИБКА в Excel.
IFERROR против IFNA
Представленная в Excel 2013, IFNA — это еще одна функция для проверки формулы на наличие ошибок. Его синтаксис похож на синтаксис IFERROR:
IFNA(значение, значение_если_на)
Чем IFNA отличается от IFERROR? Функция IFNA перехватывает только ошибки #N/A, тогда как ЕСЛИОШИБКА обрабатывает все типы ошибок.
В каких ситуациях вы можете использовать IFNA? Когда неразумно скрывать все ошибки. Например, при работе с важными или конфиденциальными данными вы можете захотеть получать предупреждения о возможных ошибках в вашем наборе данных, а стандартные сообщения об ошибках Excel с символом «#» могут быть яркими визуальными индикаторами.
Давайте посмотрим, как можно создать формулу, отображающую сообщение «Не найдено» вместо ошибки «Н/Д», которая появляется, когда искомое значение отсутствует в наборе данных, но обращает ваше внимание на другие ошибки Excel.
Предположим, вы хотите получить Qty. из таблицы поиска в сводную таблицу, как показано на снимке экрана ниже. С использованием Формула Excel Iferror Vlookup приведет к эстетически приятному результату, что технически неверно, поскольку Лимоны существуют в таблице поиска:
Чтобы поймать #N/A, но отобразить ошибку #DIV/0, используйте функцию IFNA в Excel 2013 и Excel 2016:
=IFNA(ВПР(F3,$A$3:$D$6,4,ЛОЖЬ), «Не найдено»)
Или комбинация IF ISNA в Excel 2010 и более ранних версиях:
=ЕСЛИ(ИСНА(ВПР(F3,$A$3:$D$6,4,ЛОЖЬ)),»Не найдено», ВПР(F3,$A$3:$D$6,4,ЛОЖЬ))
Синтаксис формул IFNA VLOOKUP и IFNA ISNA VLOOKUP аналогичен синтаксису формул ЕСЛИ ОШИБКА ВПР а также ЕСЛИ ОШИБКА ВПР обсуждалось ранее.
Как показано на снимке экрана ниже, формула Ifna Vlookup возвращает «Не найдено» только для элемента, которого нет в таблице поиска (Персики). За Лимоны, он показывает #DIV/0! что указывает на то, что наша таблица поиска содержит ошибку деления на ноль:
Дополнительные сведения см. в разделе Использование функции IFNA в Excel.
Рекомендации по использованию ЕСЛИОШИБКА в Excel
К настоящему моменту вы уже знаете, что функция ЕСЛИОШИБКА — это самый простой способ отлавливать ошибки в Excel и маскировать их пустыми ячейками, нулевыми значениями или собственными сообщениями. Однако это не означает, что вы должны обернуть каждую формулу обработкой ошибок. Следующие простые рекомендации могут помочь вам сохранить равновесие.
- Не ловите ошибки без причины.
- Оберните наименьшую возможную часть формулы в ЕСЛИОШИБКА.
- Чтобы обрабатывать только определенные ошибки, используйте функцию обработки ошибок с меньшей областью действия:
- IFNA или IF ISNA для обнаружения только ошибок #N/A.
- ISERR для обнаружения всех ошибок, кроме #N/A.
Вот как вы используете функцию ЕСЛИОШИБКА в Excel для перехвата и обработки ошибок. Чтобы поближе познакомиться с формулами, обсуждаемыми в этом руководстве, вы можете загрузить наш образец ЕСЛИОШИБКА Рабочая книга Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе.
