Как убрать нд в впр в excel

Ошибки #ЗНАЧ и #Н/Д в функции ВПР() Excel и как сними бороться.

В данной статье расскажу о двух ошибках которые может выдать функция ВПР() :

  • Ошибка #Н/Д;
  • Ошибка #ЗНАЧ.

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

Когда возникает ошибка #Н/Д и как от нее избавиться при использовании ВПР().

Сообщение об ошибке Н/Д можно расшифровать как аббревиатуру (НД) – нет данных, то есть функции ВПР() нечего отобразить, и она как бы сообщает: «нет данных для отображения».

Почему возникает ошибка Н/Д (НД)?

  1. Ошибка может возникать потому, что в Вашем списке (диапазоне) для сравнения нет искомого функцией ВПР() значения.
  2. Ошибка может возникать потому, что в Вашем списке (диапазоне) для сравнения значения ячеек имеют ошибки. Иногда ошибки нельзя увидеть «не вооружённым глазом», например, если в ячейке добавлен лишний пробел или едва заметная точка. ВПР() воспринимает значение ячейки без пробела и с пробелом как совершенно разные данные и выдает ошибку «Н/Д».
  3. Ошибка может возникать потому, что в искомой ячейке уже стоит значение «Н/Д», то есть ВПР() подтягивает эту ошибку из другой ячейки (искомой).

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

  1. Первый способ – применить обработку ошибок – функцию ЕСЛИОШИБКА(ВПР(*;*;*;0);”Здесь была ошибка”). Эта функция заменяет сообщение об ошибке на любое значение, которое Вы укажете.
  2. Способ №2 – удалить все пробелы и, по возможности, знаки препинания из ячеек. Для этого нужно нажатием клавиш ctrl+H вызвать окно замены значений, потом в поле «Найти» ввести пробел или знак препинания, а в поле «Заменить на:» не вводить ничего и нажить кнопку «Заменить все».Заменить на
  3. Способ №3 – поставить в функции ВПР() допуск ошибки. Как нам извесчтно 4 –й аргумент функции это число ошибок которые может допускать в сравниваемой строке функция ВПР(). То есть, если поставить число «1», то допускается 1 ошибка при сравнении [ВПР(*;*;*;1)]. В таком случае строка без пробела и с одним пробелом будут считаться идентичными. Но в таком способе есть подвох — очень высока вероятность неверных результатов, например, слово «полка» и «палка» имеют отличие всего в один знак и будут восприняты функцией, как одно и то же.

Когда возникает ошибка #ЗНАЧ и как от нее избавиться при использовании ВПР().

Ошибка #ЗНАЧ может выводиться функцией ВПР(), если введенные значения аргументов функции некорректны и функция не может их обработать.

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

Появляется ошибка #ЗНАЧ в функции ВПР() тогда, когда длина строки сравниваемой функцией слишком большая и не может быть обработана. Например, в Excel 2010 максимальная длина строки обрабатываемой функцией всего 255 символов, и если Вы будете сравнивать строки длиной 256 и более символов, то получите ошибку #ЗНАЧ.

Исправить ошибку #ЗНАЧ в таком случае можно уменьшив длины сравниваемых строк.

Еще ошибка #ЗНАЧ может возникнуть если Вы пропустили(не указали) один из аргументов в функции.

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

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

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

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

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

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

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

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

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

Вместо них можно использовать индекс и MATCH

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

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

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

С помощью функций ИНДЕКС и MATCH можно указать либо строку, либо столбец в массиве, либо оба этих столбца. Это означает, что значения можно искать по вертикали и по горизонтали.

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

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

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

Синтаксис

Для построения синтаксиса функций ИНДЕКС и MATCH необходимо использовать аргумент массива или ссылки функции ИНДЕКС и вложенный в нее синтаксис ФУНКЦИИ MATCH. В этом случае будет отформатовка:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Если range_lookup имеет вид ИСТИНА и один из столбцов подзагоков не отсортен в порядке возрастания (A–Z), вы увидите ошибку #N/A.

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

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

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

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

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

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

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

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

Ссылка на основную публикацию