В этом руководстве показано, как использовать функцию Excel ЕСЛИОШИБКА для обнаружения ошибок формулы, заменяя их другой формулой, пустым значением, 0 или настраиваемым сообщением.
Обзор функции ЕСЛИОШИБКА
Функция ЕСЛИОШИБКА Проверяет, приводит ли формула к ошибке. Если ЛОЖЬ, вернуть исходный результат формулы. Если ИСТИНА, вернуть другое указанное значение.
ЕСЛИОШИБКА Синтаксис
Чтобы использовать функцию таблицы Excel ЕСЛИОШИБКА, выберите ячейку и введите:= ЕСЛИОШИБКА (
Обратите внимание, как появляются входные данные формулы ЕСЛИОШИБКА:
Синтаксис и входные данные функции ЕСЛИОШИБКА:
| 1 | = ЕСЛИОШИБКА (ЗНАЧЕНИЕ; значение_если_ошибка) |
ценить - Выражение. Пример: 4 / A1
value_if_error - Значение или расчет для выполнения, если предыдущий ввод привел к ошибке. Пример 0 или «» (пусто)
Что такое функция ЕСЛИОШИБКА?
Функция ЕСЛИОШИБКА относится к категории логических функций в Microsoft Excel, которая включает ISNA, ISERROR и ISERR. Все эти функции помогают обнаруживать и обрабатывать ошибки формул.
ЕСЛИОШИБКА позволяет выполнить расчет. Если расчет не приведет к ошибке, затем отобразится результат расчета. Если расчет делает приводит к ошибке, тогда выполняется другое вычисление (или выводится статическое значение, такое как 0, пробел или какой-то текст).
Когда бы вы использовали функцию ЕСЛИОШИБКА?
- При делении чисел во избежание ошибок, связанных с делением на 0
- При выполнении поиска для предотвращения ошибок, если значение не найдено.
- Если вы хотите выполнить другое вычисление, если первое приводит к ошибке (например, поиск значения в 2nd table, если его нет в первой таблице)
Необработанные ошибки формул могут вызвать ошибки в вашей книге, но видимые ошибки также делают вашу электронную таблицу менее привлекательной.
Если ошибка, то 0
Давайте посмотрим на простой пример. Ниже вы делите два числа. Если вы попытаетесь разделить на ноль, вы получите сообщение об ошибке:
Вместо этого вставьте вычисление в функцию ЕСЛИОШИБКА, и если вы разделите на ноль, вместо ошибки будет выведено 0:
| 1 | = ЕСЛИОШИБКА (A2 / B2; 0) |
Если ошибка, то пусто
Вместо того, чтобы устанавливать для ошибок значение 0, вы можете установить их как «пустые» с двойными кавычками («»):
| 1 | = ЕСЛИОШИБКА (A2 / B2; "") |
Мы рассмотрим больше случаев использования ЕСЛИОШИБКИ с функцией ВПР …
ЕСЛИ ОШИБКА с ВПР
Функции поиска, такие как VLOOKUP, будут генерировать ошибки, если значение поиска не будет найдено. Как показано выше, вы можете использовать функцию ЕСЛИОШИБКА для замены ошибок пробелами («») или нулями:
| 1 | = ЕСЛИОШИБКА (ВПР (A2, LookupTable1! $ A $ 2: $ B $ 4,2; FALSE), «не найдено») |
Если ошибка, то сделайте что-нибудь еще
Функцию ЕСЛИОШИБКА также можно использовать для выполнения второго вычисления, если первое вычисление приводит к ошибке:
| 12 | = ЕСЛИОШИБКА (ВПР (A2; LookupTable1! $ A $ 2: $ B $ 4,2; FALSE),ВПР (A2, LookupTable2! $ A $ 2: $ B $ 4,2, FALSE)) |
Здесь, если данные не найдены в «LookupTable1», вместо этого выполняется ВПР для «LookupTable2».
Дополнительные примеры формул ЕСЛИОШИБКА
Вложенная ЕСЛИОШИБКА - ВПР на нескольких листах
Вы можете вложить ЕСЛИОШИБКУ в другую ЕСЛИОШИБКА, чтобы выполнить 3 отдельных вычисления. Здесь мы будем использовать два IFERROR для выполнения ВПР на 3 отдельных листах:
| 123 | = ЕСЛИОШИБКА (ВПР (A2; LookupTable1! $ A $ 2: $ B $ 4,2; FALSE),ЕСЛИОШИБКА (ВПР (A2; LookupTable2! $ A $ 2: $ B $ 4,2; FALSE),ВПР (A2, LookupTable3! $ A $ 2: $ B $ 4,2, FALSE))) |
Индекс / соответствие и XLOOKUP
Конечно, IFERROR также будет работать с формулами Index / Match и XLOOKUP.
ЕСЛИ ОШИБКА XLOOKUP
Функция XLOOKUP - это расширенная версия функции VLOOKUP.
| 1 | = ЕСЛИОШИБКА (XLOOKUP (A2, LookupTable1! $ A $ 2: $ A $ 4, LookupTable1! $ B $ 2: $ B $ 4), «Не найдено») |
ИНДЕКС ЕСЛИ ОШИБКА / СООТВЕТСТВИЕ
ИНДЕКС и ПОИСКПОЗ можно использовать для создания более мощных ВПР (аналогично тому, как работает новая функция XLOOKUP) в Excel.
| 1 | = ЕСЛИОШИБКА (ИНДЕКС (LookupTable1! $ B $ 2: $ B $ 4, MATCH (A3, LookupTable1! $ A $ 2: $ A $ 4,0)), «Не найдено») |
ЕСЛИОШИБКА в массивах
Формулы массива в Excel используются для выполнения нескольких вычислений с помощью одной формулы. Предположим, есть три столбца: Год, Продажи и Средняя цена. Вы можете узнать общее количество по следующей формуле в столбце E.
| 1 | {= СУММ ($ B $ 2: $ B $ 4 / $ C $ 2: $ C $ 4)} |
Формула работает хорошо до тех пор, пока она не попытается разделить на ноль, в результате чего получится # DIV / 0! ошибка.
Вы можете использовать функцию ЕСЛИОШИБКА для устранения ошибки следующим образом:
| 1 | {= СУММ (ЕСЛИОШИБКА ($ B $ 2: $ B $ 4 / $ C $ 2: $ C $ 4,0))} |
Обратите внимание, что функция ЕСЛИОШИБКА должна быть вложена в функцию СУММ, иначе ЕСЛИОШИБКА будет применяться к общей сумме, а не к каждому отдельному элементу в массиве.
IFNA против ЕСЛИ ОШИБКА
Функция IFNA работает точно так же, как функция ЕСЛИОШИБКА, за исключением того, что функция IFNA выявляет только ошибки # Н / Д. Это чрезвычайно полезно при работе с функциями поиска: обычные ошибки формул по-прежнему будут обнаруживаться, но ошибки не появятся, если значение поиска не найдено.
| 1 | = IFNA (ВПР (A2; LookupTable1! $ A $ 2: $ B $ 4,2; FALSE); «Не найдено») |
Если ISERROR
Если вы все еще используете Microsoft Excel 2003 или более старую версию, вы можете заменить IFERROR комбинацией IF и ISERROR. Вот краткий пример:
| 1 | = ЕСЛИ (ЕСТЬ ОШИБКА (A2 / B2); 0; A2 / B2) |
ЕСЛИ ОШИБКА в Google Таблицах
Функция ЕСЛИОШИБКА работает в Google Таблицах точно так же, как и в Excel:
ЕСЛИОШИБКА Примеры в VBA
VBA не имеет встроенной функции ЕСЛИОШИБКА, но вы также можете получить доступ к функции ЕСЛИОШИБКА Excel из VBA:
| 12 | Dim n до тех пор, покаn = Application.WorksheetFunction.IfError (Значение, значение_если_ошибка) |
Application.WorksheetFunction дает вам доступ ко многим (не всем) функциям Excel в VBA.
Обычно ЕСЛИОШИБКА используется при чтении значений из ячеек. Если ячейка содержит ошибку, VBA может выдать сообщение об ошибке при попытке обработать значение ячейки. Попробуйте это с помощью приведенного ниже примера кода (где ячейка B2 содержит ошибку):
| 1234567891011 | Sub IFERROR_VBA ()Dim n по длине, м по длинеЕСЛИ ОШИБКАn = Application.WorksheetFunction.IfError (Диапазон ("b2"). Значение, 0)«Нет ЕСЛИОШИБКИm = Диапазон ("b2"). ЗначениеКонец подписки |
Код присваивает ячейку B2 переменной. Второе присвоение переменной вызывает ошибку, потому что значение ячейки # Н / Д, но первое работает нормально из-за функции ЕСЛИОШИБКА.
Вы также можете использовать VBA для создания формулы, содержащей функцию ЕСЛИОШИБКА:
| 1 | Диапазон ("C2"). FormulaR1C1 = "= ЕСЛИОШИБКА (RC [-2] / RC [-1], 0)" |
Обработка ошибок в VBA сильно отличается от обработки ошибок в Excel. Обычно для обработки ошибок в VBA используется обработка ошибок VBA. Обработка ошибок VBA выглядит так:
| 12345678910111213141516171819 | Sub TestWS ()MsgBox DoesWSExist («тест»)Конец подпискиФункция DoesWSExist (wsName As String) As BooleanDim ws как рабочий листПри ошибке Возобновить ДалееУстановить ws = Sheets (wsName)'Если ошибка WS не существуетЕсли Err.Number 0, тоDoesWSExist = FalseЕщеDoesWSExist = TrueКонец, еслиПри ошибке GoTo -1Конечная функция |
Обратите внимание, что мы используем Если Err.Number 0, то чтобы определить, произошла ли ошибка. Это типичный способ отлова ошибок в VBA. Однако функция ЕСЛИОШИБКА имеет некоторые применения при взаимодействии с ячейками Excel.
