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

В этой статье описаны наиболее распространенные причины сбоя функции ВПР.

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

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

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

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

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

Сообщение об ошибке возникает из-за того, что искомое значение Капуста находится во втором столбце (Продукты) аргумента таблица (A2:C10), а Excel ищет его в столбце A.

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

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

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

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

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

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

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

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

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

Синтаксис

Необходимо вложить синтаксис функции ПОИСКПОЗ в аргумент массива или ссылки функции ИНДЕКС. Готовая формула выглядит примерно так:

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

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

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

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

=ИНДЕКС(нужно вернуть значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение "Капуста" в массиве B2:B10))

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

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

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

Если аргумент диапазон_поиска имеет значение ЛОЖЬ, а функции ВПР не удается найти точное совпадение, возвращается ошибка #Н/Д.

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

Используйте функции ПЕЧСИМВ или СЖПРОБЕЛЫ для очистки данных в ячейках.

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

Если аргумент диапазон_поиска имеет значение ИСТИНА, а искомое значение меньше наименьшего значения в массиве, возвращается ошибка #Н/Д. Функция ищет приблизительное совпадение в массиве и возвращает ближайшее значение, которое меньше искомого.

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

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

Решение.

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

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

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

Если аргумент диапазон_поиска имеет значение ИСТИНА и один из столбцов не отсортирован по возрастанию (от А до Я), появится ошибка #Н/Д.

Решение.

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

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

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

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

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

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

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

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

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

См. также

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

ВПР: как избавиться от "#Н/Д"

Функции ВПР, ГПР, ПРОСМОТР возвращают неправильные значения в Excel

Вычисления с числами с плавающей запятой могут давать неточные результаты в Excel

Краткий справочник по ВПР

Функция ВПР

Обзор формул в Excel

Как избежать появления неработающих формул

Исправление распространенных ошибок в формулах с помощью функции проверки ошибок

Все функции Excel (по алфавиту)

Все функции Excel (по категориям)

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

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

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

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

×