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