Excel: как использовать ЕСЛИОШИБКА, затем пусто
Вы можете использовать следующие методы в Excel, чтобы вернуть пустое значение вместо значения ошибки, когда допустимое значение не возвращается из формулы:
Способ 1: ЕСЛИОШИБКА, затем пробел с некоторой формулой
=IFERROR( B2 / A2 , "") Метод 2: ЕСЛИОШИБКА, затем пустое значение с функцией ВПР
=IFERROR(VLOOKUP( E2 , $A$2:$C$12 , 3, FALSE), "") В следующих примерах показано, как использовать каждый метод на практике.
Пример 1: ЕСЛИОШИБКА, затем пробел с некоторой формулой
Предположим, мы используем следующую формулу, чтобы разделить значения в столбце B на значения в столбце A в этой конкретной электронной таблице Excel:
= B2 / A2 
Обратите внимание, что для каждой ячейки в столбце C, где мы пытаемся разделить на пустое значение, мы получаем #DIV/0! как результат.
Чтобы вернуть пустое значение вместо значения ошибки, мы можем ввести следующую формулу в ячейку C2 :
=IFERROR( A2 / B2 , "") Затем мы можем скопировать и вставить эту формулу в каждую оставшуюся ячейку в столбце C:
Теперь для каждой ячейки в столбце C, где мы пытаемся разделить на пустое значение, в результате мы просто получаем пустое значение.
Пример 2: ЕСЛИОШИБКА, затем пустое значение с функцией ВПР
Предположим, мы используем следующую формулу ВПР для поиска имени игрока в столбце А и возврата значения подборов в столбце С:
VLOOKUP( F2 , $A$2:$C$12 , 3, FALSE) 
Обратите внимание, что для каждой ячейки в столбце G, где мы встречаем пустое значение в функции ВПР , в результате мы получаем #Н/Д .
Чтобы вернуть пустое значение вместо значения #Н/Д , мы можем ввести следующую формулу в ячейку F2 :
=IFERROR(VLOOKUP( F2 , $A$2:$C$12 , 3, FALSE), "") Затем мы можем скопировать и вставить эту формулу в каждую оставшуюся ячейку в столбце F:
Теперь для каждой ячейки, где мы встречаем пустое значение в функции ВПР , мы просто получаем в результате пустое значение.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Функция Функция IFERROR
Возвращает первое значение, если оно не является ошибочным; в противном случае возвращает второе значение.
Пример использования
IFERROR([Имя задачи]15; «Требуются данные»)
value_if_error
значение, как правило, формула, позволяющая проверить наличие ошибки;
value_if_error
значение или формула, возвращаемые, если первое значение оказалось ошибочным.
Примечания об использовании
- Использование этой функции похоже на функцию ISERROR, вложенную в функцию IF. Отличие заключается в том, что при работе с функцией IFERROR не требуется повторно вводить или проверять выражение.
- Функция не подавляет ошибки, вызванные неверно сконструированными формулами. Примеры: #UNPARSEABLE, #CIRCULAR REFERENCE, #BLOCKED, #DATE EXPECTED, #CONTACT EXPECTED.
В примере ниже используется информация из этой таблицы.
Вот несколько примеров использования функции IFERROR применительно к таблице выше.
=IFERROR(SUM([Цена за ед.]:[Цена за ед.]), «Цена ошибочна»)
Суммирует значения в столбце Цена за ед. При наличии ошибки формула выдаёт значение Цена ошибочна.
=COUNTIF([Дата отправки]:[Дата отправки], IFERROR(MONTH(@cell) = 3, » «))
Пересчитывает значения в столбце Дата отправки, если значение, указывающее на месяц, равняется 3 (март). При наличии ошибки в критерии формула выдаёт пустое значение.
Критерию отвечают строки 2 и 3 столбца Дата отправки.
=IFERROR(VLOOKUP(«Куртка», [Предмет одежды]:[Артикул], 2, false), » «)
Ищет значение Куртка в столбце Предмет одежды. Если формула его находит, то она выдаёт значение из столбца Артикул. При наличии ошибки (#No Match) выпадает пустое значение.
Остались вопросы?
Используйте шаблон Руководство по работе с формулами, чтобы просмотреть дополнительные ресурсы и изучить более 100 формул. Руководство содержит глоссарий, описывающий каждую функцию, обращение с которой вы сможете отработать на практике. Также в нём представлены примеры как часто используемых, так и более сложных функций.
Примеры реального использования функций можно посмотреть и в Сообществе Smartsheet. Там же можно задать интересующие вас вопросы другим пользователям программы.
Функции Error, IfError, IsError, IsBlankOrError
Обнаруживает ошибки и предоставляет альтернативное значение или выполняет действие. Создает пользовательскую ошибку или проходит через ошибку.
- Поведение, описанное в этой статье, доступно только при включенной предварительной версии функции Управление ошибками на уровне формул в Настройки > Предстоящие функции > Предварительная версия.
IfError
Функция IfError проверяет значения, пока не обнаружит ошибку. Если функция обнаруживает ошибку, она оценивает и возвращает соответствующее значение замены и останавливает дальнейшую оценку. Также можно указать значение по умолчанию, если ошибок не обнаружено. Структура IfError напоминает структуру функции If: функция IfError проверяет на наличие ошибки, а функция If проверяет на значение true.
Использование функции IfError заменяет ошибку допустимым значением, чтобы дальнейшие вычисления могли продолжаться. Например, используйте эту функцию, если пользовательский ввод может привести к делению на ноль:
IfError( 1/x, 0 ) Эта формула возвращает 0 , если значение x равно нулю, так как 1/x выдаст ошибку. Если x не ноль, то 1/x возвращается.
Остановка дальнейшей обработки
Patch( DS1, . ); Patch( DS2, . ) Для второй функции Patch применительно к DS2 будет предпринята попытка, даже если Patch для DS1 даст сбой. Область действия ошибки ограничен каждой формулой в цепочке.
Используйте IfError, чтобы выполнить действие и продолжить обработку, только если действие было успешным. Применение IfError к этому примеру:
IfError( Patch( DS1, . ), Notify( "problem in the first action" ), Patch( DS2, . ), Notify( "problem in the second action" ) ) Если функция Patch для DS1 имеет проблему, выполняется первая функция Notify. Никакой дальнейшей обработки не происходит, включая вторую функция Patch для DS2 . Если первая функция Patch выполнена успешно, вторая функция Patch выполняется.
Если указан, необязательный аргумент DefaultResult возвращается, если ошибок не обнаружено. Без этого аргумента последний аргумент Value возвращается.
Основываясь на последнем примере, возвращаемое значение из функции IfError можно проверить, чтобы определить наличие проблем:
IfError( Patch( DS1, . ), Notify( "problem in the first action" ); false, Patch( DS2, . ), Notify( "problem in the second action" ); false, true ) Совместимость типов
IfError вернет значение одного из своих аргументов. Типы всех значений, которые могут быть возвращены IfError, должны быть совместимыми.
В последнем примере функция Patch вернет запись, которая не совместима с логическими значениями, используемыми для формул Replacement или DefaultResult. Это нормально, так как нет ситуации, в которой возвращаемое значение из этих вызовов функции Patch будут возвращены функцией IfError.
В то время как поведение в процессе изменения, типы всех аргументов для IfError должны быть совместимыми в настоящее время.
В простом примере, описанном ранее:
IfError( 1/x, 0 ) Типы 1/x и 0 были совместимы, поскольку оба были числами. Если это не так, второй аргумент будет приведен в соответствие с типом первого аргумента.
Excel отобразит #ДЕЛ/0!, когда происходит деление на ноль.
Рассмотрите функцию IfError вместо этого:
IfError( 1/x, "#DIV/0!" ) Приведенная выше формула не сработает. Текстовая строка «#DIV/0!» будет приведен к типу первого аргумента функции IfError, который является числом. Результат функции IfError будет еще одной ошибкой, так как текстовая строка не может быть приведена. В качестве исправления преобразуйте первый аргумент в текстовую строку, чтобы функция IfError всегда возвращала текстовую строку:
IfError( Text( 1/x ), "#DIV/0!" ) Как видно выше, функция IfError может вернуть ошибку, если Replacement или DefaultResult являются ошибкой.
FirstError / AllErrors
Внутри формул замены информация о найденных ошибках доступна в записи FirstError и в таблице AllErrors. AllErrors представляет собой таблицу записей информации об ошибках с FirstError как ярлык для первой записи этой таблицы. FirstError всегда будет возвращать то же значение, что и First( AllErrors ).
Записи об ошибках включают:
| Поле | Тип | Описание |
|---|---|---|
| Вид | Перечисление ErrorKind (число) | Категория ошибки. |
| Сообщение | Текстовая строка | Сообщение об ошибке, подходящее для отображения конечному пользователю. |
| Источник | Текстовая строка | Местоположение, в котором возникла ошибка, используется для отчетов. Например, для формулы, связанной со свойством элемента управления, это будет форма ControlName.PropertyName. |
| Наблюдается | Текстовая строка | Местоположение, в котором пользователю сообщается об ошибке, используется для создания отчетов. Например, для формулы, связанной со свойством элемента управления, это будет форма ControlName.PropertyName. |
| Подробности | Запись | Сведения об ошибке. В настоящее время подробности предоставляются только для сетевых ошибок. Эта запись включает HttpStatusCode, который содержит код состояния HTTP и HttpResponse, который содержит текст ответа от соединителя или службы. |
Например, рассмотрите следующую формулу как свойство OnSelect элемента управления Button:
Set( a, 1/0 ) И эта формула в свойстве OnSelect второго элемента управления Button:
IfError( a, Notify( "Internal error: originated on " & FirstError.Source & ", surfaced on " & FirstError.Observed ) ) В приведенном выше примере формулы будет отображаться следующий баннер при последовательном нажатии двух кнопок:
Как правило, будет только одна ошибка, с которой может достаточно работать FirstError. Однако существуют сценарии, в которых может быть возвращено несколько ошибок. Например, при использовании оператора цепочки формул или функции Concurrent. Даже в таких случаях отчетов FirstError может быть достаточно, чтобы выявить проблему, вместо того, чтобы перегружать пользователя множеством ошибок. Если у вас по-прежнему есть потребность работать с каждой ошибкой по отдельности, вы можете использовать таблицу AllErrors.
IsError
Функция IsError проверяет на значение ошибки.
Возвращаемое значение является логическим true или false.
Использование функции IsError предотвратит дальнейшую обработку ошибки.
IsBlankOrError
Функция IsBlankOrError тестирует на пустое значение или значение ошибки и эквивалентна Or( IsBlank( X ), IsError( X ) ) .
При включении обработки ошибок для существующих приложений подумайте о замене функции IsBlank функцией IsBlankOrError, чтобы сохранить существующее поведение приложения. До добавления обработки ошибок использовалось пустое значение для представления как значений NULL из баз данных, так и значений ошибок. Обработка ошибок разделяет эти две интерпретации значения пусто, что может изменить поведение существующих приложений, которые продолжают использовать IsBlank.
Возвращаемое значение является логическим true или false.
Использование функции IsBlankOrError предотвратит дальнейшую обработку ошибки.
Используйте функцию Error для создания пользовательской ошибки и сообщения о ней. Например, у вас может быть логика для определения того, допустимо ли какое-либо заданное значение для вашего контекста или нет — что-то не проверяется в случае проблемы автоматически. Вы можете создать и вернуть свою собственную ошибку вместе с Вид и Сообщение, используя ту же запись, описанную выше для функции IfError.
В контексте IfError используйте функцию Error для повторного генерирования или прохождения ошибки. Например, ваша логика в IfError может решить, что в некоторых случаях ошибку можно безопасно игнорировать, но в других случаях важно передать сообщение об ошибке. В IfError или App.OnError используйте Error( FirstError ) для прохода через ошибку.
Функция Error также может быть передать таблицу ошибок, как это было бы найдено в таблице AllErrors. Используйте Error(AllErrors ), чтобы повторно выдать все ошибки а не только первую.
Пустая запись или пустая таблица, переданная в Error, не приводит к ошибке.
Синтаксис
Error( ErrorRecord )
Error( ErrorTable )
- ErrorRecord — обязательный аргумент. Запись информации об ошибках, в том числе Вид, Сообщение и другие поля. Вид — обязательный аргумент. FirstError можно пройти напрямую.
- ErrorTable — обязательный аргумент. Таблица записей информации об ошибках. AllErrors можно пройти напрямую.
IfError( Value1, Replacement1 [, Value2, Replacement2, . [, DefaultResult ] ] )
- Значения Value — обязательные аргументы. Формулы, которые необходимо проверить на значение ошибки.
- Значения Replacement — обязательные аргументы. Формулы, которые необходимо вычислить и значения для которых необходимо возвратить, если соответствующие аргументы Value вернули ошибку.
- DefaultResult — необязательный аргумент. Формулы для оценки, если формула не находит ошибок.
IsError( Value )
IsBlankOrError( Value )
- Value — обязательный аргумент. Формула для тестирования.
Примеры
Простая функция IfError
| Формула | Description | Результат |
|---|---|---|
| IfError( 1, 2 ) | Первый аргумент не является ошибкой. У функции нет других ошибок для проверки и нет возвращаемого значения по умолчанию. Функция возвращает последний оцененный аргумент Value. | 1 |
| IfError( 1/0, 2 ) | Первый аргумент возвращает значение ошибки (из-за деления на ноль). Функция оценивает второй аргумент и возвращает его как результат. | 2 |
| IfError( 10, 20, 30 ) | Первый аргумент не является ошибкой. У функции нет других ошибок для проверки, но есть возвращаемое значение по умолчанию. Функция возвращает аргумент DefaultResult. | 30 |
| IfError( 10, 11, 20, 21, 300 ) | Первый аргумент 10 не является ошибкой, поэтому функция не оценивает соответствующую замену этого аргумента 11. Третий аргумент 20 также не является ошибкой, поэтому функция не оценивает соответствующую замену этого аргумента 21. Пятый аргумент 300 не имеет соответствующей замены и является результатом по умолчанию. Функция возвращает этот результат, потому что формула не содержит ошибок. | 300 |
| IfError( 1/0, Notify( «Возникла внутренняя проблема» ) ) | Первый аргумент возвращает значение ошибки (из-за деления на ноль). Функция вычисляет второй аргумент и отображает сообщение для пользователя. Возвращаемое значение функции IfError представляет собой возвращаемое значение функции Notify, приведенное к типу первого аргумента функции IfError (число). | 1 |
Простая функция IsError
| Формула | Описание | Результат |
|---|---|---|
| IsError( 1 ) | Аргумент не является ошибкой. | false |
| IsError( Blank() ) | Аргумент пустой, но не ошибка. | false |
| IsError( 1/0 ) | Аргумент является ошибкой. | true |
| If( IsError( 1/0 ), Notify( «Возникла внутренняя проблема» ) ) | Аргумент функции IsError возвращает значение ошибки (из-за деления на ноль). Эта функция возвращает true, что приводит к тому, что If отображает сообщение для пользователя с помощью функции Notify. Возвращаемое значение функции If представляет собой возвращаемое значение функции Notify, приведенное к типу первого аргумента функции If (логическое значение). | true |
Простой пример IsBlankOrError
| Формула | Description | Результат |
|---|---|---|
| IsBlankOrError( 1 ) | Аргумент не является ошибкой и не пустой. | false |
| IsBlankOrError( Blank() ) | Аргумент пуст. | true |
| IsBlankOrError( 1/0 ) | Аргумент является ошибкой. | true |
Простая ошибка
В этом примере даты проверяются относительно друг друга, что приводит к ошибке в случае возникновения проблемы.
If( StartDate > EndDate, Error( < Kind: ErrorKind.Validation, Message: "Start Date must be before End Date" >) ) В этом примере некоторым ошибкам разрешено проходить, в то время как другие отменяются и заменяются значением. В первом случае b будет в состоянии ошибки, потому что функция Value имеет недопустимый аргумент. Поскольку это неожиданно для автора формулы, оно передается, чтобы пользователь его увидел. Во втором случае по той же формуле b будет иметь значение 0, что приведет к делению на ноль. В этом случае автор формулы может знать, что это приемлемо для этой логики, отменить ошибку (баннер не отображается) и вместо этого вернуть –1.
With( , IfError( a/b, If( FirstError.Kind <> ErrorKind.Div0, Error( FirstError ), -1 ) ) ) // returns an error with Kind = ErrorKind.InvalidArgument With( ) IfError( a/b, If( FirstError.Kind <> ErrorKind.Div0, Error( FirstError ), -1 ) ) ) // returns -1 Таблицу AllErrors можно фильтровать, как и любую другую таблицу. При использовании с функцией Error ожидаемые ошибки могут быть удалены, а оставшиеся ошибки сохранены и зарегистрированы. Например, если бы мы знали, что деление на ноль не будет проблемой в определенном контексте, эти ошибки можно было бы отфильтровать, оставив все остальные ошибки без изменений, с помощью следующей формулы:
Error( Filter( AllErrors, Kind <> ErrorKind.Div0 ) ) Пошаговое руководство
- Добавьте элемент управления Text input с именем TextInput1 (возможно, такое имя будет указано по умолчанию).
- Добавьте элемент управления Label с именем Label1 (возможно, такое имя будет указано по умолчанию).
- Установите следующую формулу для свойства Text элемента управления Label1:
IfError( Value( TextInput1.Text ), -1 ) Перехват ошибок в формулах функцией ЕСЛИОШИБКА (IFERROR)
Ошибки случаются. Вдвойне обидно, когда они случаются не по твоей вине. Так в Microsoft Excel, некоторые функции и формулы могут выдавать ошибки не потому, что вы накосячили при вводе, а из-за временного отсутствия данных или копирования формул «с запасом» на избыточные ячейки. Классический пример — ошибка деления на ноль при вычислении среднего:
Причем заметьте, что итоги в нашей таблице тоже уже не считаются — одна ошибка начинает порождать другие, передаваясь по цепочке от одной зависимой формулы к другой. Так что из-за одной ошибочной ячейки, в конце концов, может перестать работать весь расчет.
Для лечения подобных ситуаций в Microsoft Excel есть мегаполезная функция ЕСЛИОШИБКА (IFERROR), которая умеет проверять заданную формулу или ячейку и, в случае возникновения любой ошибки, выдавать вместо нее заданное значение: ноль, пустую текстовую строку «» или что-то еще.
Синтаксис функции следующий:
=ЕСЛИОШИБКА( Что_проверяем ; Что_выводить_вместо_ошибки )
Так, в нашем примере можно было бы все исправить так:
Все красиво и ошибок больше нет.
Обратите внимание, что эта функция появилась только с 2007 версии Microsoft Excel. В более ранних версиях приходилось использовать функции ЕОШ (ISERROR) и ЕНД (ISNA) . Эти функции похожи на ЕСЛИОШИБКА, но они только проверяют наличие ошибок и не умеют заменять их на что-то еще. Поэтому приходилось использовать их обязательно в связке с функцией проверки ЕСЛИ (IF) , создавая вложенные конструкции типа:
Такой вариант ощутимо медленне работает и сложнее для понимания, так что лучше использовать новую функцию ЕСЛИОШИБКА, если это возможно.