Перейти к основному контенту
Office

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

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

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

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

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

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

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

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

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

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

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

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

Существует несколько преимуществ использования функции индекс и СОВПАДЕНИе вместо функции ВПР.

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

  • С помощью функции индекс и совпадение можно указать либо строку, либо столбец в массиве, либо указать оба значения. Это означает, что вы можете искать значения по вертикали и по горизонтали.

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

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

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

Синтаксис

Чтобы создать синтаксис для функции индекс и ПОИСКПОЗ, необходимо использовать аргумент array/Reference из функцией индекс и вложить в него синтаксис MATCH. Форма будет иметь следующий вид:

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

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

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

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

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

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

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

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

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

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

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

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

Если аргумент Интервальный_просмотр имеет значение истина, а значение подстановки меньше, чем наименьшее значение в массиве, отображается ошибка #N/a. ЗНАЧЕНИЕ TRUE ищет приблизительное совпадение в массиве и возвращает ближайшее значение меньше, чем искомое значение.

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

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

Решение.

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

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

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

Если аргумент Интервальный_просмотр имеет значение истина, а один из столбцов подстановки не отсортирован в порядке возрастания (A – Z), появится ошибка #N/a.

Решение.

  • Измените функцию ВПР для поиска точного совпадения. Для этого задайте для аргумента Интервальный_просмотр значение ложь. Для FALSE нет необходимости в сортировке.

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

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

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

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

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

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

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

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

См. также

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

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

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

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

×