Исправление ошибки #Н/Д в функции ВПР

В этой статье описаны наиболее распространенные причины возникновения ошибочных результатов функции, а также предлагаются способы использования функций индекс и ПОИСКПОЗ .

Совет: Кроме того, ознакомьтесь с кратким справочником: советы по устранению неполадок , в которых описываются распространенные причины #NA проблем в УДОБНОМ PDF-файле. Вы можете предоставить общий доступ к PDF-файлу другим пользователям или принтерам для собственных ссылок.

Проблема: искомое значение не находится в первом столбце аргумента таблица

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

В следующей таблице нам нужно узнать количество проданной капусты.

Ошибка #Н/Д в функции ВПР: искомое значение не находится в первом столбце массива таблицы

Ошибка #N/A, так как значение подстановки "капусты" появляется во втором столбце (результатом) Table_array аргумента a2: C10. В этом случае Excel ищет его в столбце A, а не в столбце B.

Решение. Вы можете попытаться устранить эту проблему, НАСТРОИВ функцию ВПР таким образом, чтобы она ссылалась на правильный столбец. Если это невозможно, попробуйте переместить столбцы. Это также может быть очень импрактикабле, если у вас есть большие или сложные электронные таблицы, в которых значения ячеек являются результатами других вычислений, или, возможно, есть и другие логические причины, по которым просто не удается переместить столбцы. Решением является использование сочетания функций индекс и ПОИСКПОЗ, которые могут искать значения в столбце независимо от положения в таблице подстановки. Просмотрите следующий раздел.

Вместо этого используйте индекс и сопоставление

Функции индекс и совпадение являются хорошим вариантом для многих случаев, в которых функция ВПР не отвечает вашим требованиям. Ключевое преимущество функции индекс и СОВПАДЕНИе заключается в том, что вы можете найти значение в столбце в любом месте таблицы подстановки. Функция индекс возвращает значение из указанной таблицы или диапазона (в зависимости от его положения). Функция MATCH Возвращает относительное расположение значения в таблице или диапазоне. Использование ИНДЕКСов и СОВПАДЕНИй в формуле для поиска значения в таблице или массиве путем указания относительного положения значения в таблице или массиве.

Вместо функции ВПР есть несколько преимуществ использования функций индекс и соответствие.

  • При использовании функции индекс и СОВПАДЕНИе возвращаемое значение не должно находиться в том же столбце, что и столбец подстановки. Это отличается от функции ВПР, в которой возвращаемое значение должно находиться в указанном диапазоне. Почему это важно? При использовании функции ВПР вам нужно знать номер столбца, содержащего значение. Несмотря на то, что это может показаться несложным, это может оказаться громоздким, если у вас есть таблица большого размера и нужно подсчитать количество столбцов. Кроме того, при добавлении или удалении столбца в таблице необходимо пересчитать и обновить аргумент col_index_num . При использовании функций ИНДЕКС и ПОИСКПОЗ не нужно подсчитывать столбцы.

  • С помощью функции индекс и СОВПАДЕНИе можно указать либо строку или столбец в массиве, либо указать оба значения. Это означает, что значения можно искать по вертикали и по горизонтали.

  • С помощью функций ИНДЕКС и ПОИСКПОЗ можно находить значения в любом столбце. В отличие от функции ВПР, в которой можно найти значение в первом столбце таблицы: INDEX и MATCH будет работать, если искомое значение находится в первом столбце, в последнем или в любом месте между.

  • Функция индекс и сопоставление предлагают гибкие возможности создания динамической ссылки на столбец, содержащий возвращаемое значение.Это означает, что вы можете добавлять столбцы в таблицу, не нарушая индекс и СОВПАДАЮЩую. С другой стороны, функция ВПР прерывается, если вам нужно добавить столбец в таблицу, так как это приводит к статической ссылке на таблицу.

  • Предметный указатель и сопоставление обеспечивает более гибкие возможности при использовании совпадений.Функция индекс и СОВПАДЕНИе может находить точное совпадение или значение, которое больше или меньше значения подстановки. ВПР ищет только наиболее близкое (по умолчанию) или точное значение. Кроме того, функция ВПР предполагает, что первый столбец в таблице отсортирован в алфавитном порядке, и возвращает первое наиболее близкое совпадение, поэтому вы можете получить не те данные, которые ожидали.

Синтаксис

Для создания синтаксиса для функции индекс/MATCH необходимо использовать аргумент массив/ссылка из функцией индекс и вложить в него синтаксис MATCH. Рассмотрим следующую форму:

=ИНДЕКС(массив или ссылка; ПОИСКПОЗ(искомое_значение;массив;[тип_совпадения])

Использование функции индекс и соответствие для замены функции ВПР из приведенного выше примера. Синтаксис будет выглядеть следующим образом:

=ИНДЕКС(C2:C10;ПОИСКПОЗ(B13;B2:B10;0))

Что означает:

= INDEX (возвращают значение из C2: C10, которое будет соответствовать (капусты), которое находится где-то в массиве B2: B10, где возвращаемое значение является первым значением, соответствующим капусты).

Функции ИНДЕКС и ПОИСКПОЗ можно использовать вместо функции ВПР

Формула ищет в C2:C10 первое значение, соответствующее значению Капуста (B7), и возвращает значение в ячейке C7 (100).

Проблема: не найдено точное совпадение

Если аргумент range_lookup имеет значение ложь, а ВПР не может найти точное совпадение в данных, она возвращает ошибку #N/a.

Решение: Если вы уверены в том, что в вашей таблице есть нужные данные, и функция ВПР не передается, проверьте, нет ли скрытых пробелов и непечатаемых символов в указанных ячейках. Кроме того, убедитесь, что ячейки имеют правильный тип данных. Например, ячейки с числами должны отформатированы как число, а не как текст.

Кроме того, можно использовать функцию очистки или монтажа для очистки данных в ячейках.

Проблема: искомое значение меньше, чем наименьшее значение в массиве

Если аргумент range_lookup имеет значение истина, а значение подстановки меньше, чем наименьшее значение в массиве, вы увидите ошибку #N/a. Функция ищет приблизительное совпадение в массиве и возвращает ближайшее значение, которое меньше искомого.

В приведенном ниже примере искомое значение равно 100, но в диапазоне B2:C10 нет значений меньше 100, поэтому возникает ошибка.

Ошибка #Н/Д в функции ВПР, если искомое значение меньше, чем наименьшее значение в массиве

Решение.

  • Исправьте искомое значение.

  • Если вы не можете изменить значение подстановки и вам нужно больше гибкости с совпадающими значениями, рассмотрите возможность использования функции индекс и СОВПАДЕНИе вместо функции ВПР — ознакомьтесь с разделом выше в этой статье. Они позволяют находить значения больше или меньше искомого, а также равные ему. Дополнительные сведения см. в предыдущем разделе этой статьи.

Проблема: столбец подстановки не отсортирован в порядке возрастания

Если для аргумента range_lookup установлено значение true, а один из столбцов подстановки не отсортирован в возрастающем порядке (A-Z), вы увидите ошибку #N/a.

Решение.

  • Измените функцию ВПР так, чтобы искать точное совпадение. Для этого укажите для аргумента диапазон_поиска значение ЛОЖЬ. Для "ложь" не требуется сортировка.

  • Для поиска значения в несортированной таблице можно также использовать функции ИНДЕКС и ПОИСКПОЗ.

Проблема: значение является большим числом с плавающей запятой

При наличии в ячейках значений времени или больших десятичных чисел Excel возвращает ошибку "#Н/Д" из-за точности чисел с плавающей запятой. Числа с плавающей запятой включают цифры после десятичной запятой. (Значения времени хранятся в Excel в виде чисел с плавающей запятой.) Excel не может хранить числа с очень большими плавающими точками, поэтому для правильной работы функции необходимо округлить число с плавающей запятой до 5 десятичных разрядов.

Решение. Округлите числа до 5 десятичных разрядов с помощью функции ОКРУГЛ.

У вас есть вопрос об определенной функции?

Задать вопрос на форуме сообщества, посвященном Excel

Помогите нам улучшить Excel

У вас есть предложения по улучшению следующей версии Excel? Если да, ознакомьтесь с темами на портале пользовательских предложений для Excel.

См. также

Примечание:  Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Нужна дополнительная помощь?

Совершенствование навыков работы с Office
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×