Как исправить ошибки #N/A в формулах Excel, таких как ВПР

Microsoft Excel может возвращать ошибку, когда вы вводите значение или пытаетесь выполнить действие, которое не понимает. Существует несколько типов ошибок, и каждая ошибка связана с определенными типами ошибок, которые вы могли совершить.

Ошибка #N/A является стандартной ошибкой Excel . Он появляется, когда вы неправильно сослались на данные. Например, ссылка на данные, которые не существуют или существуют за пределами таблицы поиска, допустила орфографическую ошибку в значении поиска или добавила лишний символ в значение поиска (запятую, апостроф или даже символ пробела).

Поскольку ошибка возникает, когда вы неправильно указали искомое значение, она чаще всего связана с такими функциями поиска, как ПРОСМОТР(LOOKUP) , ВПР(VLOOKUP) , ГПР(HLOOKUP) и функция ПОИСКПОЗ(MATCH) . Давайте рассмотрим причины, пример и некоторые исправления ошибки #Н/Д.

Причины ошибки #Н/Д

Ниже приведены причины, которые могут вызвать ошибку #Н/Д на вашем листе:

  • Вы неправильно написали искомое значение (или вставили лишний пробел)
  • Вы допустили ошибку в значении в таблице поиска (или вставили лишний пробел)
  • Диапазон поиска был введен неправильно в формулу
  • Вы использовали тип данных для значения поиска, отличный от того, который существует в таблице поиска (т. е. использовали текст вместо чисел(text instead of numbers) ) .
  • Введенное вами значение поиска не найдено в таблице поиска.

Пример ошибки #Н/Д

Давайте используем функцию ВПР(use the VLOOKUP function) в качестве примера, чтобы понять, как вы можете получить ошибку #Н/Д после использования функций Excel , таких как (Excel)ПРОСМОТР(LOOKUP) , ГПР(HLOOKUP) или ПОИСКПОЗ(MATCH) , поскольку они имеют схожую синтаксическую структуру.

Например, предположим, что у вас есть длинный список сотрудников и их бонусов, перечисленных в рабочей книге Excel

Вы используете формулу VLOOKUP , вводите соответствующее [lookup_value] , для которого вы вставляете ссылку на ячейку (ячейка D4), определяете [table_array] (A2: B7) и определяете [col_index_num] (2). 

Для последнего аргумента, называемого [range_lookup] , вы должны использовать 1 (или TRUE ), чтобы указать Excel на получение точного совпадения. Установка его на 2 (или FALSE ) даст указание Excel искать приблизительное совпадение, что может дать вам неверный результат.

Предположим(Suppose) , вы установили формулу для получения бонусов для нескольких избранных сотрудников, но ошиблись в искомом значении. Вы получите ошибку #N/A, потому что Excel не сможет найти точное совпадение значения в таблице поиска.

Итак, что вы должны сделать, чтобы исправить эту ошибку? 

Как исправить ошибку #Н/Д

Существует несколько способов устранения ошибки #Н/Д, но исправления в основном можно разделить на два подхода:

  1. Исправление входных данных(Correcting the inputs)
  2. Перехват ошибки(Trapping the error)

Исправление входных данных(Correcting the inputs)

В идеале вы должны определить причину ошибки, используя причины, перечисленные ранее в этом руководстве. Устранение причины гарантирует, что вы не только избавитесь от ошибки, но и получите правильный вывод.

Вы должны начать с использования причин, перечисленных в этом руководстве, в качестве контрольного списка. Это поможет вам найти неправильный ввод, который необходимо исправить, чтобы устранить ошибку. Например, это может быть значение с ошибкой, лишний пробел или значения с неправильным типом данных в таблице поиска.

Перехват ошибки(Trapping the error)

В качестве альтернативы, если вы хотите просто(just ) исключить ошибки из своего рабочего листа, не беспокоясь об индивидуальной проверке ошибок, вы можете использовать несколько формул Excel . Некоторые функции были созданы специально для перехвата ошибок, в то время как другие могут помочь вам построить логический синтаксис, используя несколько функций для устранения ошибок.

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

  • ЕСЛИОШИБКА Функция(IFERROR Function)
  • Функция ИФНА(IFNA Function)
  • Комбинация функции ЕОШИБКА и функции ЕСЛИ(A Combination of ISERROR Function and IF Function)
  • Функция ТРИМ(TRIM Function)

1. Функция ЕСЛИОШИБКА(1. IFERROR Function)

Функция ЕСЛИОШИБКА(IFERROR) была создана с единственной целью изменить вывод для ячейки, которая возвращает ошибку.

Использование функции ЕСЛИОШИБКА(IFERROR) позволяет вам ввести конкретное значение, которое вы хотите, чтобы ячейка отображала вместо ошибки. Например, если у вас есть ошибка #Н/Д в ячейке E2 при использовании функции ВПР(VLOOKUP) , вы можете вложить всю формулу в функцию ЕСЛИОШИБКА(IFERROR) , например:

ЕСЛИОШИБКА(ВПР(E4,B2:C7,2,1),"Сотрудник не найден"(IFERROR(VLOOKUP(E4,B2:C7,2,1),“Employee not found”)

Если функция ВПР(VLOOKUP) приводит к ошибке, она автоматически отображает текстовую строку « Сотрудники(Employees) не найдены» вместо ошибки.

Вы также можете использовать пустую строку, просто вставив две кавычки («»), если вы хотите отображать пустую ячейку, когда формула возвращает ошибку.

Обратите внимание, что функция ЕСЛИОШИБКА(IFERROR) работает для всех ошибок. Так, например, если формула, которую вы вложили в функцию ЕСЛИОШИБКА(IFERROR) , возвращает ошибку # DIV , ЕСЛИОШИБКА(IFERROR) все равно перехватит ошибку и вернет значение в последнем аргументе.

2. Функция ИФНА(IFNA Function)

Функция IFNA является более специфической версией функции IFERROR , но работает точно(exactly) так же. Единственная разница между этими двумя функциями заключается в том, что функция ЕСЛИОШИБКА перехватывает (IFERROR)все(all) ошибки, а функция IFNA перехватывает(IFNA) только ошибки #N/A.

Например, следующая формула будет работать, если у вас есть ошибка VLOOKUP #N/A, но не для ошибки # VALUE :

IFNA(VLOOKUP(E4,B2:C7,2,1),"Сотрудник не найден"(IFNA(VLOOKUP(E4,B2:C7,2,1),“Employee not found”)

3. Комбинация функции ЕОШИБКА и функции ЕСЛИ(A Combination of the ISERROR Function and the IF Function)

Другой способ перехватить ошибку — использовать функцию ЕОШИБКА(ISERROR) вместе с функцией ЕСЛИ. По сути, она работает так же, как функция ЕСЛИОШИБКА(IFERROR) , поскольку она использует функцию ЕОШИБКА(ISERROR) для обнаружения ошибки и функцию ЕСЛИ для вывода вывода на основе логического теста.

Эта комбинация работает со всеми(all) ошибками, такими как функция ЕСЛИОШИБКА(IFERROR) , а не только с функцией #Н/Д. Вот пример того, как будет выглядеть синтаксис при перехвате ошибки Excel VLOOKUP #N/A с помощью функций ЕСЛИ и ЕОШИБКА(ISERROR) :

=IF(ISERROR(VLOOKUP(E4,B2:C7,2,1)),VLOOKUP(E4,B2:C8,2,1),”Employee not found”)

4. Функция ОБРЕЗКИ(TRIM Function)

Ранее мы обсуждали, что символ пробела, случайно вставленный в значение поиска, может привести к ошибке #Н/Д. Однако, если у вас есть длинный список значений поиска, уже заполненных на вашем листе, вы можете использовать функцию TRIM вместо удаления символа пробела из каждого значения поиска по отдельности.

Сначала создайте еще один столбец, чтобы обрезать начальные и конечные пробелы в именах с помощью функции TRIM :

Затем используйте новый столбец имен в качестве значений поиска в функции ВПР(VLOOKUP) .

Исправить ошибку #N/A в макросах

Не существует конкретной формулы или ярлыка, который можно использовать для исправления ошибок #Н/Д в макросе. Поскольку вы, вероятно, добавили несколько функций в свой макрос при его создании(macro when you created it) , вам необходимо проверить аргументы, используемые для каждой функции, и убедиться, что они верны, чтобы исправить ошибку #N/A в макросе.

#Н/Д Исправлены ошибки

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


Related posts