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

ВПР без ошибки #н/д

ение, следует использовать функцию ЕСЛИОШИБКА, чтобы поймать эту ошибку и вернуть любое значение или любое Вам нужное.

Как работает формула

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

Если у вас есть подстановки значений в ячейки А1 и поиск значений в диапазон с именем таблицы, и вы хотите, чтобы ячейки были пустыми, если не поиска, не найдено, вы можете использовать:

Если вы хотите вернуть сообщение «нет», когда нет совпадения, используйте:

В предыдущих версиях Excel, отсутствие функцию ЕСЛИОШИБКА, вам потребуется повторить ВПР внутри функции ЕСЛИ:

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel для Windows Phone 10 Еще. Меньше

В этом разделе описаны наиболее распространенные причины ВПР для ошибочного результата функции и приведены рекомендации по использованию INDEX и MATCH .

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

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

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

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

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

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

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

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

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

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

Синтаксис

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

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

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

=ИНДЕКС(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 Tech Community или получить поддержку в сообществах.

См. также

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

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

Есть прайс, в столбце «Теги» я прописываю через ВПР значения и сравниваю. Прайс и формула прилагается, вопрос вот в чем, почему вылезают #Н/Д, которые приходиться изменять вручную.

Пользователь
Сообщений: 2308 Регистрация: 23.10.2014
18.11.2016 19:50:59

Цитата
pirogkoff написал: почему вылезают #Н/Д, которые приходиться изменять вручную.

потому что нет искомого текста. Например на листе 1, в ячейке «D131» есть текст: Животные A1003-1 (72шт/3) дикие, 2 вида, в пакете 25см
Этого текста нет на листе 2, в столбце D. Можете это проверить с помощью «Найти и заменить».

Пользователь
Сообщений: 21 Регистрация: 25.04.2016
18.11.2016 19:55:09

Ага, тоесть это новая позиция, потому и нет. Ок, а как подставить на оборот, из «название» в «теги» название продукта?

Пользователь
Сообщений: 2308 Регистрация: 23.10.2014
18.11.2016 19:59:57

Приведите пример на конкретной строке, что нужно сделать, иначе не понятно задание.
PS. Полностью посты не цитируйте (об этом сказано в правилах). Если хотите к кому-то обратиться, то пишите ник.

Пользователь
Сообщений: 21 Регистрация: 25.04.2016
18.11.2016 20:08:58

Karataev, Ну, мы нашли причину Н/Д, как ее заменить на текст из Тегов, а не заменять Н/Д копированием в ручную на тег!?

Пользователь
Сообщений: 2308 Регистрация: 23.10.2014
18.11.2016 20:19:26
А по какому принципу заменять? Какой тег надо подставить? Откуда тег взять?
Пользователь
Сообщений: 21 Регистрация: 25.04.2016
18.11.2016 20:23:55
Karataev,Ну так есть же столбец с тегами, короче руками пока копирую)) вместо Н/Д ставлю тег
Пользователь
Сообщений: 2308 Регистрация: 23.10.2014
18.11.2016 20:25:22
Вам нужно принцип рассказать, по которому подставлять тег.
Пользователь
Сообщений: 4093 Регистрация: 01.01.1970
18.11.2016 20:30:11
Вариант для ячейки K2.

=ЕСЛИОШИБКА(ВПР(D2;Лист2!D:K;8;);K1)

Пользователь
Сообщений: 21 Регистрация: 25.04.2016
18.11.2016 20:37:06
gling,Работает только не идеально, не все Н/Д вылечила формула, спасибо
Пользователь
Сообщений: 4093 Регистрация: 01.01.1970
18.11.2016 20:55:57

Интересно, в каком случае не «вылечивает»? Даже при отсутствии значений в столбце D, подставляется значение предыдущей ячейки.

Прикрепленные файлы

• Импорт 7Тойс от 18.11.2016.xlsx (15.53 КБ)

Пользователь
Сообщений: 21 Регистрация: 25.04.2016
18.11.2016 21:05:08

gling,Я подставил формулу и прокрутил ниже по списку, были н/д.. Короче все равно спасибо за формулу, я в след раз ее подставлю и попытаюсь разобрать что за чем идет.. Хотя мне тяжело понять ход выполнения формулы, что за чем идет =ЕСЛИОШИБКА(ВПР(D2;Лист2!D:K;8;);K1)

Изменено: pirogkoff — 18.11.2016 21:08:30
Пользователь
Сообщений: 4093 Регистрация: 01.01.1970
18.11.2016 21:27:53

В файле 2 варианта, с ошибкой и заменой ошибки на значение из ячейки выше ошибочной. В столбце L, без исправлений.

Пользователь
Сообщений: 4 Регистрация: 14.03.2018
19.03.2018 14:32:59

Друзья, здравствуйте! Помогите пожалуйста исправить ошибку #Н/Д в таблице (прилагается).

Я пытался сделать подстановку цены с помощью ВПР, в столбцах ETP и OFM получилось , а в столбце MOD почему-то выходит ошибка #Н/Д , хотя номера Кат. номеров (критерий поиска совпадений) совпадают.

Прикрепленные файлы

• Сравнительная.xlsx (20.78 КБ)

Пользователь
Сообщений: 7650 Регистрация: 15.02.2016
19.03.2018 14:42:37

Цитата
a.i.mershik написал: Зачем Вы пишите в разные тему если можете создать свою!?

А разве это запрещено правилами форума?

Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл

Пользователь
Сообщений: 47199 Регистрация: 15.09.2012
19.03.2018 14:47:02

FINSAD, перед тем, как создать сообщение в чужой теме, следует все сообщения ПРОЧИТАТЬ. Решение есть выше.
=ВПР(C5;$P$4:$R$32;3;0)
Значения С5 нет в указанном диапазоне столбца Р

Задача данной темы: избавиться от ошибки при отсутствии данных. Вам нужно то же? Если нет, нужно было создать отдельную тему

Пользователь
Сообщений: 8277 Регистрация: 03.12.2015
19.03.2018 14:49:09

Bema, ну не знаю кажется когда то было такое в правилах, сейчас не нашел, но когда тема как минимум не касается вопроса, то считаю, что это мешает общему восприятию обсуждаемого в данной теме вопроса.

Не бойтесь совершенства. Вам его не достичь.
Пользователь
Сообщений: 7650 Регистрация: 15.02.2016
19.03.2018 14:54:23

Автор 2 раза задал свой вопрос в соответствующих старых темах. Правда второй раз немного промахнулся, но быстро исправился.

Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл

Пользователь
Сообщений: 4 Регистрация: 14.03.2018
19.03.2018 14:55:05
Друзья, извините пожалуйста за мою временную Неопытность, что доставило Вам неудобств.
Пользователь
Сообщений: 8277 Регистрация: 03.12.2015
19.03.2018 14:55:30
Bema, ну вот я и написал.
Не бойтесь совершенства. Вам его не достичь.
Пользователь
Сообщений: 6111 Регистрация: 21.12.2012
Win 10, MSO 2013 SP1
19.03.2018 15:15:28

Цитата
FINSAD написал: извините пожалуйста за мою временную Неопытность

OFF Вам, имхо, лучше открыть свою тему, но предварительно разобраться с тем, ЧТО, ИЗ ЧЕГО, К ЧЕМУ необходимо ВэПээРить.
У меня лично не получилось до конца разобраться в зависимостях.

«Ctrl+S» — достойное завершение ваших гениальных мыслей. 😉
Страницы: 1
Читают тему

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Автор Master Of Exc Опубликовано 11.09.2019 Рубрики Начинающим

Добавить комментарий Отменить ответ

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.