Формула Excel: как исправить ошибку # N / A -

Содержание

Общая формула

=IFERROR(FORMULA(),"message")

Резюме

Ошибка # N / A обычно появляется, когда что-то не удается найти или идентифицировать. Однако ошибки # N / A также могут быть вызваны лишними пробелами, орфографическими ошибками или неполной таблицей поиска. Функции, на которые чаще всего влияет ошибка # Н / Д, - это классические функции поиска, включая ВПР, ГПР, ПРОСМОТР и ПОИСКПОЗ. См. Ниже дополнительную информацию и инструкции по устранению.

Объяснение

Об ошибке # N / A

Ошибка # N / A появляется, когда что-то не удается найти или идентифицировать. Часто это полезная ошибка, потому что она сообщает вам, что чего-то важного не хватает - продукта, который еще не доступен, имени сотрудника написано с ошибкой, варианта цвета, которого не существует, и т. Д.

Однако ошибки # N / A также могут быть вызваны лишними пробелами, орфографическими ошибками или неполной таблицей поиска. Функции, на которые чаще всего влияет ошибка # Н / Д, - это классические функции поиска, включая ВПР, ГПР, ПРОСМОТР и ПОИСКПОЗ.

Лучший способ предотвратить ошибки # Н / Д - убедиться, что значения поиска и таблицы поиска верны и полны. Если вы видите неожиданную ошибку # N / A, сначала проверьте следующее:

  1. Значение поиска написано правильно и не содержит лишних пробелов.
  2. Значения в таблице поиска написаны правильно и не содержат лишнего места.
  3. Таблица поиска содержит все необходимые значения.
  4. Диапазон поиска, предоставленный функции, является полным (т.е. данные не «отсекаются»).
  5. Тип значения поиска = тип таблицы поиска (т. Е. Оба являются текстовыми, оба являются числами и т. Д.)
  6. Соответствие (приблизительное или точное) установлено правильно.

Примечание: если вы получили неправильный результат, когда вы должны увидеть ошибку # N / A, убедитесь, что вы правильно настроили точное соответствие. Режим приблизительного совпадения с радостью вернет все виды результатов, которые полностью неверны :)

Перехват ошибки # N / A с помощью IFERROR

Одним из вариантов улавливания ошибки # Н / Д является функция ЕСЛИОШИБКА. ЕСЛИОШИБКА может аккуратно перехватить любую ошибку и вернуть альтернативный результат.

В показанном примере ошибка # Н / Д появляется в ячейке F5, потому что «мороженое» не существует в таблице поиска, которая представляет собой именованный диапазон «данные» (B5: C9).

=VLOOKUP(E5,data,2,0) // "ice cream" is not found

Чтобы обработать эту ошибку, функция ЕСЛИОШИБКА оборачивается вокруг формулы ВПР следующим образом:

=IFERROR(VLOOKUP(E7,data,2,0),"Not found")

Если функция ВПР возвращает ошибку, функция ЕСЛИОШИБКА «улавливает» эту ошибку и возвращает «Не найдено».

Улавливание ошибки # N / A с помощью IFNA

Функция IFNA также может специально отлавливать и обрабатывать ошибки # N / A. Синтаксис использования такой же, как с ЕСЛИОШИБКА:

=IFERROR(VLOOKUP(A1,table,column,0),"Not found") =IFNA(VLOOKUP(A1,table,column,0),"Not found")

Преимущество функции IFNA заключается в том, что она более хирургическая и позволяет устранить только # N / A ошибок. С другой стороны, функция ЕСЛИОШИБКА обнаружит любую ошибку. Например, даже если вы неправильно написали ВПР, ЕСЛИОШИБКА вернет «Не найдено».

Нет сообщений

Если вы не хотите отображать какое-либо сообщение при перехвате ошибки # N / A (т.е. вы хотите отображать пустую ячейку), вы можете использовать пустую строку ("") следующим образом:

=IFERROR(VLOOKUP(E7,data,2,0),"")

ИНДЕКС и МАТЧ

Функция ПОИСКПОЗ также возвращает # Н / Д, если значение не найдено. Если вы используете ИНДЕКС и ПОИСКПОЗ вместе, вы можете таким же образом отловить ошибку # Н / Д. Основываясь на приведенном выше примере, формула в F5 будет выглядеть так:

=IFERROR(INDEX(C5:C9,MATCH(E5,B5:B9,0)),"Not found")

Узнайте больше об ИНДЕКС и МАТЧ.

Принудительная ошибка # N / A

Если вы хотите принудительно выдать ошибку # N / A на листе, вы можете использовать функцию NA. Например, если в ячейке отображается # N / A, если A1 равно нулю, вы можете использовать следующую формулу:

=IF(A1=0, NA())

Интересные статьи...