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

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

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

Совет: Кроме того, обратитесь к Краткий справочник: советы по устранению неполадок функции ВПР которого представлены распространенные причины проблем #NA в удобном PDF-файла. Можно поделиться с другими пользователями PDF-ФАЙЛ или распечатать для справки.

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

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

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

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

Так как искомое значение «Kale» появляется во втором столбце (продукты) для аргумента A2:C10 приводит к ошибке # н/д. В этом случае Excel найти его в столбце A, столбец B.

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

Вместо графика рекомендуется использовать индекс и ПОИСКПОЗ

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

Существует несколько преимуществ индекс и ПОИСКПОЗ вместо функции ВПР.

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

  • С индекс и ПОИСКПОЗ, вы можете указать строки или столбца в массиве, или указать оба. Это означает, что вы можете поиск значений по горизонтали и по вертикали.

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

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

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

Синтаксис

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

= INDEX(array or reference, MATCH(lookup_value,lookup_array,[match_type])

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

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

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

= Индекс (возврат значения из C2:C10, которому будут СООТВЕТСТВОВАТЬ (Kale, которая находится там, где в массиве B2: B10, в котором возвращает значение первое значение, соответствующее Kale))

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

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

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

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

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

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

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

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

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

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

Решение.

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

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

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

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

Решение.

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

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

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

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

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

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

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

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

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

См. также

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

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

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

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

×