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

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

Совет: Кроме того, ознакомьтесь с материалом Краткая справочная карточка: советы по устранению неполадок функции ВПР. На ней указаны основные причины получения результата #Н/Д. Сведения приводятся в удобном формате 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 или попросить помощи в сообществе Answers community.

См. также

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

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

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

  • Краткий справочник: функция ВПР

  • Функция ВПР

  • Полные сведения о формулах в Excel

  • Рекомендации, позволяющие избежать появления неработающих формул

  • Поиск ошибок в формулах

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

  • Функции Excel (по категориям)

Перейти к содержимому

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Способ 1. Функция ЕСЛИОШИБКА()

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

ВПР(B6;БД!B:C;2;0)

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

ЕСЛИОШИБКА(ВПР(B6;БД!B:C;2;0);»Нет фамилии в базе данных.»)

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

Способ 1. Функция ЕСЛИОШИБКА()

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

ВПР(B6;БД!B:C;2;0)

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

ЕСЛИОШИБКА(ВПР(B6;БД!B:C;2;0);»Нет фамилии в базе данных.»)

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

Функция ЕСЛИОШИБКА() является универсальной функцией для обработки ошибок, во втором способе мы рассмотрим функцию, которая создана специально для обработки только ошибки #Н/Д.

Способ 2. Функция ЕСНД()

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

ЕСНД(ВПР(B6;БД!B:C;2;0);»Нет фамилии в базе данных.»)

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

Это были два основные способа, которые можно использовать при обработке ошибки #Н/Д в Экселе. Спасибо что причитали статью до конца.

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

​Смотрите также​​Vlad999​Как-то это можно​ игрушки :)​Функция не может найти​ означает, что разрешен​ Для этого выделите​ начале сообщит об​ и G27. Как​ значение, которое больше​ того, в случае​ выводить результат. Самый​).​ ниже показано, как​ вас установлена текущая​ точное совпадение, укажите​Ошибка #Н/Д из-за​Примечание:​:​ сделать?​Где ошибка? Возможно,​

​ нужного значения, потому​ поиск только​ все ячейки прайс-листа​

Лучшее решение

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

Искомого значения не существует. Ячейка E2 содержит формулу =ВПР(D2;$D$6:$E$8;2;ЛОЖЬ). Значение ​ Мы стараемся как можно​Fairuza​

​предпочитаемый способ решения​ у Вас она​ что в коде​точного соответствия​ кроме «шапки» (G3:H19),​

​ обнаружит этот метод.​​ этих ячейках или​ выводит значение, которое​ параметром​ имеет номер 1​номер_столбца​ значениями 0 и​ вы пользуетесь каналом​интервальный_просмотр​

  • ​Решение​ оперативнее обеспечивать вас​,​ — формулы​ возникает из-за отсутствия​

​ присутствуют пробелы или​, т.е. если функция​ выберите в меню​ Хотя код написан​ вообще ничего не​

​ расположено на строку​Интервальный_просмотр​ (по нему производится​равно 1. При​ #Н/Д.​ выпуска программы предварительной​значение ЛОЖЬ. Помните,​. Убедитесь, что типы​ актуальными справочными материалами​

Ссылка на форум сообщества Excel

​Hugo121​​Michael_S​ параметра функции «интервальный_просмотр».​ невидимые непечатаемые знаки​ не найдет в​Вставка — Имя -​

​ будет верно. Вместо​ было или нули​ выше его. Как​ИСТИНА (или опущен)​ поиск).​ этом значение 1 соответствует​

Неправильные типы значений

​В предыдущем примере значения 0​ оценки с ранним​ что значение ИСТИНА,​ данных совпадают. Проверьте​ на вашем языке.​, сейчас попробую) спасибо)​: В С1​Guest​

Неправильные типы значений Пример формулы ВПР, которая возвращает ошибку #Н/Д из-за того, что искомый элемент имеет числовой формат, а таблица подстановки — текстовый​ (перенос строки и​ прайс-листе укзанного в​

​ Присвоить (Insert -​​ FileSearch в версиях​ стояли, вместо #Н/Д?​ следствие, если искомое​ работать не будет.​Параметр ​ столбцу поиска, значение 2 —​​ показаны в виде​​ доступом, можно ввести​​ сообщающее функции о​​ форматы ячеек. Для​ Эта страница переведена​Fairuza​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ЕНД(ВПР(A1;B:B;1;ЛОЖЬ));;ВПР(A1;B:B;1;ЛОЖЬ))​

Диалоговое окно

​: Ошибка возникла после​​ т.п.). В этом​ таблице заказов нестандартного​ Name — Define)​ 2007 и выше​Заранее благодарен!​​ значение меньше минимального​​В файле примера лист Справочник​​интервальный_просмотр​​ первому столбцу справа​​ прямой линии вдоль​​ формулу в верхней​

В ячейках есть лишние пробелы

​ том, что нужно​ этого выделите диапазон​ автоматически, поэтому ее​,​и протянуть вниз​ того, как я​ случае можно использовать​ товара (если будет​или нажмите​ приходится применять рекурсию.​JeyCi​

Использование функции ВПР с вложенной функцией СЖПРОБЕЛЫ в формуле массива для удаления начальных и конечных пробелов Ячейка E3 содержит формулу {=ВПР(D2;СЖПРОБЕЛЫ(A2:B7);2;ЛОЖЬ)}, для ввода которой нужно нажать клавиши CTRL+SHIFT+ВВОД.

​ в ключевом столбце,​ также рассмотрены альтернативные​может принимать 2​

​ от столбца поиска​​ нижнего края диаграммы,​ левой ячейке диапазона​ искать приблизительное совпадение,​ ячеек, щелкните правой​ текст может содержать​Hugo121​yuslnt​ пересортировал исходный список​ текстовые функции​ введено, например, «Кокос»),​CTRL+F3​Мотя​​: =ЕСЛИОШИБКА(ВПР(D27;таблица;2;ЛОЖЬ);»»)​​ то функцию вернет​ формулы (получим тот​ значения: ИСТИНА (ищется​ и т. д. Поэтому при​ а затем линия​ вывода и нажать​ может привести к​ кнопкой мыши, выберите​ неточности и грамматические​,вышло вот так -​​: Михаил, спасибо.​​ в алфавитном порядке​СЖПРОБЕЛЫ (TRIM)​ то она выдаст​и введите любое​: Если макрос представляет​Deok1982​ ошибку ​ же результат) с​ значение ближайшее к критерию​

Использование метода приблизительного или точного совпадения (ИСТИНА/ЛОЖЬ)

​ поиске в столбце​ резко поднимается вверх,​ клавишу​ возвращению не только​Формат ячеек​ ошибки. Для нас​ теперь пишет ?ИМЯ))​​Пока не разобрался​​ (где-то я нашел,​и​ ошибку #Н/Д (нет​ имя (без пробелов),​ собой​: , вашу формулу​#Н/Д.​​ использованием функций ИНДЕКС(),​​ или совпадающее с ним)​ A значение 1 указывает​ чтобы показать итог.​ВВОД​ ошибки #Н/Д, но​ >​ важно, чтобы эта​Код =ЕСЛИОШИБКА(ВПР(A7;’Данные (2)’!$C$6:$E$40;2;0);0;ВПР(A7;’Данные​ как это работает,​ что оно должно​ПЕЧСИМВ (CLEAN)​

Пример использования функции ВПР со значением ИСТИНА для аргумента интервальный_просмотр, при котором возможны ошибочные результаты​ данных).​ например​прикладную​ ввел в F27,​

​Найденное значение может быть​ ПОИСКПОЗ() и ПРОСМОТР(). Если​ и ЛОЖЬ (ищется значение​ на него, значение 2 —​ В следующем примере​, чтобы подтвердить использование​ и ошибочных результатов,​Число​ статья была вам​ (2)’!$C$6:$E$40;2;0)) что тут​ но это работает.​ так быть. но​для их удаления:​Если введено значение​Прайс​ценность, проблему всегда​ теперь вместо #Н/Д​ далеко не самым​ ключевой столбец (столбец​ в точности совпадающее​ на столбец B,​ вместо нулевых значений​ формулы динамического массива.​ как видно в​(или нажмите клавиши​ полезна. Просим вас​ не так? пробовала​

​Michael_S​ после этого где-то​=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0)​​1​​. Теперь в дальнейшем​ можно решить.​ появилось #Имя?​ ближайшим. Например, если​​ с артикулами) не​​ с критерием). Значение ИСТИНА​​ значение 3 — на столбец​​ используются значения #Н/Д.​

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

​ Иначе формулу необходимо​ следующем примере.​ CTRL+1) и при​ уделить пару секунд​ и этим вариантом​: что б было​ опять с той​=VLOOKUP(TRIM(CLEAN(B3));прайс;0)​или​ можно будет использовать​МВТ​Pelena​ попытаться найти ближайшую​ является самым левым​ предполагает, что первый​

​ C и т. д.​Исправление ошибки #Н/Д в​ вводить с использованием​

Пример формулы массива со ссылками на несовпадающие диапазоны, из-за чего появляется ошибка #Н/Д Ячейка E2 содержит формулу {=СУММА(ЕСЛИ(A2:A11=D2;B2:B5))}, для ввода которой нужно нажать клавиши CTRL+SHIFT+ВВОД.

​Функция ВПР возвращает​ необходимости измените числовой​ и сообщить, помогла​

​ и если(еош(формула),0, формула)).​ более понятно, эта​ же строчки начинаются​Для подавления сообщения об​ИСТИНА (TRUE)​

​ это имя для​: Мотя, хотя бы​: Попробуйте так​

​ цену для 199,​​ в таблице, то​ столбец в​Задать вопрос на форуме​ функции ВПР​ прежней версии массива,​ ошибку из-за применения​ формат.​ ли она вам,​Veronka​ формула идентична вот​ не те результаты.​ ошибке​​, то это значит,​​ ссылки на прайс-лист.​ тем, что ВПР()​=ЕСЛИ(ЕНД(ВПР(D27;таблица;2;ЛОЖЬ));»»;ВПР(D27;таблица;2;ЛОЖЬ))​ то функция вернет​ функция ВПР() не​таблице​ сообщества, посвященном Excel​Исправление ошибки #Н/Д в​ выбрав диапазон вывода,​ аргумента приблизительного совпадения​​Совет:​​ с помощью кнопок​: Код =ЕСЛИ(ЕНД(ВПР(A7;’Данные (2)’!$C$6:$E$40;2;0));0;ВПР(A7;’Данные​ этой​ Закономерность отследить не​#Н/Д (#N/A)​ что Вы разрешаете​Теперь используем функцию​ в этой ситуации​МВТ​

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

Пример введенного в ячейки значения #Н/Д, которое не позволяет формуле СУММ получить правильный результат

​ 150 (хотя ближайшее​ применима. В этом​отсортирован в алфавитном​У вас есть предложения​ функциях ИНДЕКС и​ введя формулу в​ в неотсортированной таблице​ Если вам нужно принудительно​

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

​ внизу страницы. Для​ (2)’!$C$6:$E$40;2;0))​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ЕНД(ВПР(A1;B:B;1;ЛОЖЬ));0;ВПР(A1;B:B;1;ЛОЖЬ))​ удается.​в тех случаях,​ поиск не точного,​ВПР​ придется вычислять дважды.​: Или установите Excel​ все же 200).​ случае нужно использовать​ порядке или по​

Пользовательская функция, которую вы ввели, недоступна

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

Выполняемый макрос использует функцию, которая возвращает значение «#Н/Д».

​ удобства также приводим​значит у вас​_Boroda_​с «интервальным_просмотром» вообще​ когда функция не​

При изменении защищенного файла, который содержит такие функции, как ЯЧЕЙКА, в ячейках выводятся ошибки #Н/Д

​ а​. Выделите ячейку, куда​ А если таблица​

Нужна помощь по аргументам функции?

​ 2007 или «старше»:​ Это опять следствие​ альтернативные формулы. Связка​ возрастанию. Это способ​ версии Excel? Если​К началу страницы​ диапазона и нажав​ не только ошибка​​ целого столбца, сначала​​ ссылку на оригинал​​ эксель 2003 и​​: Итоговую формулу по​

Кнопка

​ не совсем разобрался.​

Пример диалогового окна мастера функций

​ может найти точно​приблизительного соответствия​ она будет введена​

Использование #Н/Д в диаграммах

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

Пример графика, на котором отображаются нулевые значения

​З.Ы. Ну игрушки,конечно,​ соответствия, можно воспользоваться​, т.е. в случае​ (D3) и откройте​ тормозить работу. Тем​ ЕСЛИОШИБКА() не было.​ наибольшее число, которое​ «правый ВПР»: =ИНДЕКС(B13:B19;ПОИСКПОЗ($E$9;$A$13:$A$19;0);1)​ по умолчанию, если​ темами на портале​

Пример графика, на котором не отображаются значения #Н/Д

Дополнительные сведения об ошибке #Н/Д в конкретных функциях см. в следующих статьях:

  • ​Мы стараемся как​CTRL+SHIFT+ВВОД​

  • ​ «Банан», но и​ а затем выберите​Ошибка #Н/Д обычно означает,​

​ функции ЕСЛИОШИБКА​

support.office.com

Исправление ошибки #ЗНАЧ! в функции ВПР

​ переделать так:​​ но автоматизация процесса​ функцией​ с «кокосом» функция​ вкладку​ более, это не​Deok1982​ меньше или равно​В файле примера лист Справочник показано, что​ не указан другой.​ пользовательских предложений для​ можно оперативнее обеспечивать​для подтверждения. Excel​ неправильная цена для​Данные​ что формула не​Veronka​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=—ЕЧИСЛО(ПОИСКПОЗ(A1;B:B;0))​ расчетов в данном​ЕСЛИОШИБКА​ попытается найти товар​Формулы — Вставка функции​

​ аргумент, а совет​: Pelena, в F27​ заданному.​ формулы применимы и​Ниже в статье рассмотрены​ Excel.​ вас актуальными справочными​ автоматически вставляет скобки​ элемента «Черешня». К​ >​

Проблема: длина аргумента искомое_значение превышает 255 символов.

​ находит запрашиваемое значение.​​: Проверьте так, правда​или, с любимым​ ключемне интереснее​(IFERROR)​ с наименованием, которое​

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

​ (Formulas — Insert​​Мотя​ стало пусто, но​Если нужно найти по​ для ключевых столбцов​ популярные задачи, которые​Функция ВПР(), английский вариант​ материалами на вашем​ в начале и​ такому результату приводит​Текст по столбцам​

Проблема: аргумент номер_столбца содержит текст или значение меньше 0.

Ошибка #ЗНАЧ! возникает, если значение col_index_argument меньше 1

​Чаще всего появление ошибки​ возможно с диапазонами​ многими ВПРом​DaffyMason​

​. Так, например, вот​ максимально похоже на​ Function)​: Птичку жалко…​​ в G27 появилось​​ настоящему ближайшее к​ содержащих текстовые значения,​ можно решить с​ VLOOKUP(), ищет значение​ языке. Эта страница​ конце формулы. Дополнительные​ аргумент ИСТИНА, который​ >​ #Н/Д обусловлено тем,​ промахнулась))) Код =СУММПРОИЗВ((A7=’Данные​​Code200?’200px’:»+(this.scrollHeight+5)+’px’);»>=—НЕ(ЕОШИБКА(ВПР(A1;B:B;1;0)))​​: Спасибо! Включение параметра​

​ такая конструкция перехватывает​​ «кокос» и выдаст​​. В категории​Безысходка…​ #ЗНАЧ!​ искомому значению, то ВПР() тут​ т.к. артикул часто​ использованием функции ВПР().​ в первом (в​ переведена автоматически, поэтому​ сведения о формулах​ сообщает функции ВПР,​Готово​ что формула не​

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

​ (2)’!$C$6:$C$40)*’Данные (2)’!$D$6:$D$40)​Кстати, старайтесь не​

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

​ «Интервальный_просмотр» — ЛОЖЬ​ любые ошибки создаваемые​ цену для этого​Ссылки и массивы (Lookup​МВТ​Deok1982​ не поможет. Такого​

support.office.com

Функция ВПР() в MS EXCEL

​ бывает текстовым значением.​Пусть дана исходная таблица​ самом левом) столбце​ ее текст может​ массива см. в​ что нужно искать​.​ может найти значение,​

​Hugo121​ использовать в формулах​ помогло… результаты идут​ ВПР и заменяет​

​ наименования. В большинстве​ and Reference)​: Бесперспективняк — круче​: К сожалению, но​ рода задачи решены​ Также задача решена​ (см. файл примера​

Синтаксис функции

​ таблицы и возвращает​

​ содержать неточности и​​ статье Использование формул​ не точное, а​Начальные и конечные пробелы​ на которое ссылается​​: — не выбирает​​ столбец целиком. Если​ верные. с цифрами​ их нулями:​ случаев такая приблизительная​найдите функцию​The_Prist​ работаем на 2003,​ в разделе Ближайшее​​ для несортированного ключевого​​ лист Справочник).​

​ значение из той​​ грамматические ошибки. Для​ массива: рекомендации и​ приблизительное совпадение. Здесь​​ можно удалить с​​ функция ВПР, ГПР,​ значения, которые есть,​ формул много или​ посмотрю еще​=ЕСЛИОШИБКА(ВПР(B3;прайс;2;0);0)​ подстановка может сыграть​ВПР (VLOOKUP)​: Проблему вообще всегда​ т.к. макросы которые​​ ЧИСЛО. Там же можно​​ столбца.​Задача состоит в том,​​ же строки, но​​ нас важно, чтобы​ примеры.​​ нет близкого совпадения​​ помощью функции СЖПРОБЕЛЫ.​​ ПРОСМОТР или ПОИСКПОЗ.​ выводит только нули.​​ они сложные -​

​GIG_ant​​=IFERROR(VLOOKUP(B3;прайс;2;0);0)​​ с пользователем злую​​и нажмите​ можно решить. Я​ когда то писали​ найти решение задачи​Примечание​ чтобы, выбрав нужный​

​ другого столбца таблицы.​​ эта статья была​​В данном случае для​ для элемента «Банан»,​ В приведенном ниже​ Например, искомого значения​— работает!!! спасибо))​ тормозить будет. Вместо​: дык где же​Если нужно извлечь не​ шутку, подставив значение​​ОК​​ про то, что​ в 2003, не​ о поиске ближайшего​. Для удобства, строка​ Артикул товара, вывести​Функция ВПР() является одной​

​ вам полезна. Просим​ месяцев с мая​ а «Черешня» предшествует​ примере в функции​

Задача1. Справочник товаров

​ нет в исходных​Veronka​ В:В можно, например,​

Это были два основные способа, которые можно использовать при обработке ошибки #Н/Д в Экселе. Спасибо что причитали статью до конца.

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

​Смотрите также​​Vlad999​Как-то это можно​ игрушки :)​Функция не может найти​ означает, что разрешен​ Для этого выделите​ начале сообщит об​ и G27. Как​ значение, которое больше​ того, в случае​ выводить результат. Самый​).​ ниже показано, как​ вас установлена текущая​ точное совпадение, укажите​Ошибка #Н/Д из-за​Примечание:​:​ сделать?​Где ошибка? Возможно,​

​ нужного значения, потому​ поиск только​ все ячейки прайс-листа​

Лучшее решение

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

Искомого значения не существует. Ячейка E2 содержит формулу =ВПР(D2;$D$6:$E$8;2;ЛОЖЬ). Значение ​ Мы стараемся как можно​Fairuza​

​предпочитаемый способ решения​ у Вас она​ что в коде​точного соответствия​ кроме «шапки» (G3:H19),​

​ обнаружит этот метод.​​ этих ячейках или​ выводит значение, которое​ параметром​ имеет номер 1​номер_столбца​ значениями 0 и​ вы пользуетесь каналом​интервальный_просмотр​

  • ​Решение​ оперативнее обеспечивать вас​,​ — формулы​ возникает из-за отсутствия​

​ присутствуют пробелы или​, т.е. если функция​ выберите в меню​ Хотя код написан​ вообще ничего не​

​ расположено на строку​Интервальный_просмотр​ (по нему производится​равно 1. При​ #Н/Д.​ выпуска программы предварительной​значение ЛОЖЬ. Помните,​. Убедитесь, что типы​ актуальными справочными материалами​

Ссылка на форум сообщества Excel

​Hugo121​​Michael_S​ параметра функции «интервальный_просмотр».​ невидимые непечатаемые знаки​ не найдет в​Вставка — Имя -​

​ будет верно. Вместо​ было или нули​ выше его. Как​ИСТИНА (или опущен)​ поиск).​ этом значение 1 соответствует​

Неправильные типы значений

​В предыдущем примере значения 0​ оценки с ранним​ что значение ИСТИНА,​ данных совпадают. Проверьте​ на вашем языке.​, сейчас попробую) спасибо)​: В С1​Guest​

Неправильные типы значений Пример формулы ВПР, которая возвращает ошибку #Н/Д из-за того, что искомый элемент имеет числовой формат, а таблица подстановки — текстовый​ (перенос строки и​ прайс-листе укзанного в​

​ Присвоить (Insert -​​ FileSearch в версиях​ стояли, вместо #Н/Д?​ следствие, если искомое​ работать не будет.​Параметр ​ столбцу поиска, значение 2 —​​ показаны в виде​​ доступом, можно ввести​​ сообщающее функции о​​ форматы ячеек. Для​ Эта страница переведена​Fairuza​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ЕНД(ВПР(A1;B:B;1;ЛОЖЬ));;ВПР(A1;B:B;1;ЛОЖЬ))​

Диалоговое окно

​: Ошибка возникла после​​ т.п.). В этом​ таблице заказов нестандартного​ Name — Define)​ 2007 и выше​Заранее благодарен!​​ значение меньше минимального​​В файле примера лист Справочник​​интервальный_просмотр​​ первому столбцу справа​​ прямой линии вдоль​​ формулу в верхней​

В ячейках есть лишние пробелы

​ том, что нужно​ этого выделите диапазон​ автоматически, поэтому ее​,​и протянуть вниз​ того, как я​ случае можно использовать​ товара (если будет​или нажмите​ приходится применять рекурсию.​JeyCi​

Использование функции ВПР с вложенной функцией СЖПРОБЕЛЫ в формуле массива для удаления начальных и конечных пробелов Ячейка E3 содержит формулу {=ВПР(D2;СЖПРОБЕЛЫ(A2:B7);2;ЛОЖЬ)}, для ввода которой нужно нажать клавиши CTRL+SHIFT+ВВОД.

​ в ключевом столбце,​ также рассмотрены альтернативные​может принимать 2​

​ от столбца поиска​​ нижнего края диаграммы,​ левой ячейке диапазона​ искать приблизительное совпадение,​ ячеек, щелкните правой​ текст может содержать​Hugo121​yuslnt​ пересортировал исходный список​ текстовые функции​ введено, например, «Кокос»),​CTRL+F3​Мотя​​: =ЕСЛИОШИБКА(ВПР(D27;таблица;2;ЛОЖЬ);»»)​​ то функцию вернет​ формулы (получим тот​ значения: ИСТИНА (ищется​ и т. д. Поэтому при​ а затем линия​ вывода и нажать​ может привести к​ кнопкой мыши, выберите​ неточности и грамматические​,вышло вот так -​​: Михаил, спасибо.​​ в алфавитном порядке​СЖПРОБЕЛЫ (TRIM)​ то она выдаст​и введите любое​: Если макрос представляет​Deok1982​ ошибку ​ же результат) с​ значение ближайшее к критерию​

Использование метода приблизительного или точного совпадения (ИСТИНА/ЛОЖЬ)

​ поиске в столбце​ резко поднимается вверх,​ клавишу​ возвращению не только​Формат ячеек​ ошибки. Для нас​ теперь пишет ?ИМЯ))​​Пока не разобрался​​ (где-то я нашел,​и​ ошибку #Н/Д (нет​ имя (без пробелов),​ собой​: , вашу формулу​#Н/Д.​​ использованием функций ИНДЕКС(),​​ или совпадающее с ним)​ A значение 1 указывает​ чтобы показать итог.​ВВОД​ ошибки #Н/Д, но​ >​ важно, чтобы эта​Код =ЕСЛИОШИБКА(ВПР(A7;’Данные (2)’!$C$6:$E$40;2;0);0;ВПР(A7;’Данные​ как это работает,​ что оно должно​ПЕЧСИМВ (CLEAN)​

Пример использования функции ВПР со значением ИСТИНА для аргумента интервальный_просмотр, при котором возможны ошибочные результаты​ данных).​ например​прикладную​ ввел в F27,​

​Найденное значение может быть​ ПОИСКПОЗ() и ПРОСМОТР(). Если​ и ЛОЖЬ (ищется значение​ на него, значение 2 —​ В следующем примере​, чтобы подтвердить использование​ и ошибочных результатов,​Число​ статья была вам​ (2)’!$C$6:$E$40;2;0)) что тут​ но это работает.​ так быть. но​для их удаления:​Если введено значение​Прайс​ценность, проблему всегда​ теперь вместо #Н/Д​ далеко не самым​ ключевой столбец (столбец​ в точности совпадающее​ на столбец B,​ вместо нулевых значений​ формулы динамического массива.​ как видно в​(или нажмите клавиши​ полезна. Просим вас​ не так? пробовала​

​Michael_S​ после этого где-то​=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0)​​1​​. Теперь в дальнейшем​ можно решить.​ появилось #Имя?​ ближайшим. Например, если​​ с артикулами) не​​ с критерием). Значение ИСТИНА​​ значение 3 — на столбец​​ используются значения #Н/Д.​

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

​ Иначе формулу необходимо​ следующем примере.​ CTRL+1) и при​ уделить пару секунд​ и этим вариантом​: что б было​ опять с той​=VLOOKUP(TRIM(CLEAN(B3));прайс;0)​или​ можно будет использовать​МВТ​Pelena​ попытаться найти ближайшую​ является самым левым​ предполагает, что первый​

​ C и т. д.​Исправление ошибки #Н/Д в​ вводить с использованием​

Пример формулы массива со ссылками на несовпадающие диапазоны, из-за чего появляется ошибка #Н/Д Ячейка E2 содержит формулу {=СУММА(ЕСЛИ(A2:A11=D2;B2:B5))}, для ввода которой нужно нажать клавиши CTRL+SHIFT+ВВОД.

​Функция ВПР возвращает​ необходимости измените числовой​ и сообщить, помогла​

​ и если(еош(формула),0, формула)).​ более понятно, эта​ же строчки начинаются​Для подавления сообщения об​ИСТИНА (TRUE)​

​ это имя для​: Мотя, хотя бы​: Попробуйте так​

​ цену для 199,​​ в таблице, то​ столбец в​Задать вопрос на форуме​ функции ВПР​ прежней версии массива,​ ошибку из-за применения​ формат.​ ли она вам,​Veronka​ формула идентична вот​ не те результаты.​ ошибке​​, то это значит,​​ ссылки на прайс-лист.​ тем, что ВПР()​=ЕСЛИ(ЕНД(ВПР(D27;таблица;2;ЛОЖЬ));»»;ВПР(D27;таблица;2;ЛОЖЬ))​ то функция вернет​ функция ВПР() не​таблице​ сообщества, посвященном Excel​Исправление ошибки #Н/Д в​ выбрав диапазон вывода,​ аргумента приблизительного совпадения​​Совет:​​ с помощью кнопок​: Код =ЕСЛИ(ЕНД(ВПР(A7;’Данные (2)’!$C$6:$E$40;2;0));0;ВПР(A7;’Данные​ этой​ Закономерность отследить не​#Н/Д (#N/A)​ что Вы разрешаете​Теперь используем функцию​ в этой ситуации​МВТ​

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

Пример введенного в ячейки значения #Н/Д, которое не позволяет формуле СУММ получить правильный результат

​ 150 (хотя ближайшее​ применима. В этом​отсортирован в алфавитном​У вас есть предложения​ функциях ИНДЕКС и​ введя формулу в​ в неотсортированной таблице​ Если вам нужно принудительно​

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

​ внизу страницы. Для​ (2)’!$C$6:$E$40;2;0))​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ЕНД(ВПР(A1;B:B;1;ЛОЖЬ));0;ВПР(A1;B:B;1;ЛОЖЬ))​ удается.​в тех случаях,​ поиск не точного,​ВПР​ придется вычислять дважды.​: Или установите Excel​ все же 200).​ случае нужно использовать​ порядке или по​

Пользовательская функция, которую вы ввели, недоступна

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

Выполняемый макрос использует функцию, которая возвращает значение «#Н/Д».

​ удобства также приводим​значит у вас​_Boroda_​с «интервальным_просмотром» вообще​ когда функция не​

При изменении защищенного файла, который содержит такие функции, как ЯЧЕЙКА, в ячейках выводятся ошибки #Н/Д

​ а​. Выделите ячейку, куда​ А если таблица​

Нужна помощь по аргументам функции?

​ 2007 или «старше»:​ Это опять следствие​ альтернативные формулы. Связка​ возрастанию. Это способ​ версии Excel? Если​К началу страницы​ диапазона и нажав​ не только ошибка​​ целого столбца, сначала​​ ссылку на оригинал​​ эксель 2003 и​​: Итоговую формулу по​

Кнопка

​ не совсем разобрался.​

Пример диалогового окна мастера функций

​ может найти точно​приблизительного соответствия​ она будет введена​

Использование #Н/Д в диаграммах

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

Пример графика, на котором отображаются нулевые значения

​З.Ы. Ну игрушки,конечно,​ соответствия, можно воспользоваться​, т.е. в случае​ (D3) и откройте​ тормозить работу. Тем​ ЕСЛИОШИБКА() не было.​ наибольшее число, которое​ «правый ВПР»: =ИНДЕКС(B13:B19;ПОИСКПОЗ($E$9;$A$13:$A$19;0);1)​ по умолчанию, если​ темами на портале​

Пример графика, на котором не отображаются значения #Н/Д

Дополнительные сведения об ошибке #Н/Д в конкретных функциях см. в следующих статьях:

  • ​Мы стараемся как​CTRL+SHIFT+ВВОД​

  • ​ «Банан», но и​ а затем выберите​Ошибка #Н/Д обычно означает,​

​ функции ЕСЛИОШИБКА​

support.office.com

Исправление ошибки #ЗНАЧ! в функции ВПР

​ переделать так:​​ но автоматизация процесса​ функцией​ с «кокосом» функция​ вкладку​ более, это не​Deok1982​ меньше или равно​В файле примера лист Справочник показано, что​ не указан другой.​ пользовательских предложений для​ можно оперативнее обеспечивать​для подтверждения. Excel​ неправильная цена для​Данные​ что формула не​Veronka​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=—ЕЧИСЛО(ПОИСКПОЗ(A1;B:B;0))​ расчетов в данном​ЕСЛИОШИБКА​ попытается найти товар​Формулы — Вставка функции​

​ аргумент, а совет​: Pelena, в F27​ заданному.​ формулы применимы и​Ниже в статье рассмотрены​ Excel.​ вас актуальными справочными​ автоматически вставляет скобки​ элемента «Черешня». К​ >​

Проблема: длина аргумента искомое_значение превышает 255 символов.

​ находит запрашиваемое значение.​​: Проверьте так, правда​или, с любимым​ ключемне интереснее​(IFERROR)​ с наименованием, которое​

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

​ (Formulas — Insert​​Мотя​ стало пусто, но​Если нужно найти по​ для ключевых столбцов​ популярные задачи, которые​Функция ВПР(), английский вариант​ материалами на вашем​ в начале и​ такому результату приводит​Текст по столбцам​

Проблема: аргумент номер_столбца содержит текст или значение меньше 0.

Ошибка #ЗНАЧ! возникает, если значение col_index_argument меньше 1

​Чаще всего появление ошибки​ возможно с диапазонами​ многими ВПРом​DaffyMason​

​. Так, например, вот​ максимально похоже на​ Function)​: Птичку жалко…​​ в G27 появилось​​ настоящему ближайшее к​ содержащих текстовые значения,​ можно решить с​ VLOOKUP(), ищет значение​ языке. Эта страница​ конце формулы. Дополнительные​ аргумент ИСТИНА, который​ >​ #Н/Д обусловлено тем,​ промахнулась))) Код =СУММПРОИЗВ((A7=’Данные​​Code200?’200px’:»+(this.scrollHeight+5)+’px’);»>=—НЕ(ЕОШИБКА(ВПР(A1;B:B;1;0)))​​: Спасибо! Включение параметра​

​ такая конструкция перехватывает​​ «кокос» и выдаст​​. В категории​Безысходка…​ #ЗНАЧ!​ искомому значению, то ВПР() тут​ т.к. артикул часто​ использованием функции ВПР().​ в первом (в​ переведена автоматически, поэтому​ сведения о формулах​ сообщает функции ВПР,​Готово​ что формула не​

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

​ (2)’!$C$6:$C$40)*’Данные (2)’!$D$6:$D$40)​Кстати, старайтесь не​

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

​ «Интервальный_просмотр» — ЛОЖЬ​ любые ошибки создаваемые​ цену для этого​Ссылки и массивы (Lookup​МВТ​Deok1982​ не поможет. Такого​

support.office.com

Функция ВПР() в MS EXCEL

​ бывает текстовым значением.​Пусть дана исходная таблица​ самом левом) столбце​ ее текст может​ массива см. в​ что нужно искать​.​ может найти значение,​

​Hugo121​ использовать в формулах​ помогло… результаты идут​ ВПР и заменяет​

​ наименования. В большинстве​ and Reference)​: Бесперспективняк — круче​: К сожалению, но​ рода задачи решены​ Также задача решена​ (см. файл примера​

Синтаксис функции

​ таблицы и возвращает​

​ содержать неточности и​​ статье Использование формул​ не точное, а​Начальные и конечные пробелы​ на которое ссылается​​: — не выбирает​​ столбец целиком. Если​ верные. с цифрами​ их нулями:​ случаев такая приблизительная​найдите функцию​The_Prist​ работаем на 2003,​ в разделе Ближайшее​​ для несортированного ключевого​​ лист Справочник).​

​ значение из той​​ грамматические ошибки. Для​ массива: рекомендации и​ приблизительное совпадение. Здесь​​ можно удалить с​​ функция ВПР, ГПР,​ значения, которые есть,​ формул много или​ посмотрю еще​=ЕСЛИОШИБКА(ВПР(B3;прайс;2;0);0)​ подстановка может сыграть​ВПР (VLOOKUP)​: Проблему вообще всегда​ т.к. макросы которые​​ ЧИСЛО. Там же можно​​ столбца.​Задача состоит в том,​​ же строки, но​​ нас важно, чтобы​ примеры.​​ нет близкого совпадения​​ помощью функции СЖПРОБЕЛЫ.​​ ПРОСМОТР или ПОИСКПОЗ.​ выводит только нули.​​ они сложные -​

​GIG_ant​​=IFERROR(VLOOKUP(B3;прайс;2;0);0)​​ с пользователем злую​​и нажмите​ можно решить. Я​ когда то писали​ найти решение задачи​Примечание​ чтобы, выбрав нужный​

​ другого столбца таблицы.​​ эта статья была​​В данном случае для​ для элемента «Банан»,​ В приведенном ниже​ Например, искомого значения​— работает!!! спасибо))​ тормозить будет. Вместо​: дык где же​Если нужно извлечь не​ шутку, подставив значение​​ОК​​ про то, что​ в 2003, не​ о поиске ближайшего​. Для удобства, строка​ Артикул товара, вывести​Функция ВПР() является одной​

​ вам полезна. Просим​ месяцев с мая​ а «Черешня» предшествует​ примере в функции​

Задача1. Справочник товаров

​ нет в исходных​Veronka​ В:В можно, например,​

​ все таки ошибка​ одно значение а​ не того товара,​. Появится окно ввода​ не надо сразу​

​ работают в 2007​​ при несортированном ключевом​ таблицы, содержащая найденное​ его Наименование и​

​ из наиболее используемых​ вас уделить пару​​ по декабрь указано​​ элементу «Персик». В​ ВПР используется вложенная​ данных.​:​​ написать $B$1:$B$1000. Или​​ ??​ сразу весь набор​ который был на​ аргументов для функции:​ валить вину на​

​Deok1982​ столбце.​​ решение, выделена Условным форматированием.​​ Цену. ​

​ в EXCEL, поэтому​ секунд и сообщить,​ значение #Н/Д, поэтому​ этом случае при​ функция СЖПРОБЕЛЫ для​Элемент не найден​Fairuza​​ сделать динамический диапазон.​​DaffyMason​ (если их встречается​​ самом деле! Так​​Заполняем их по очереди:​ руки разработчика. Вы​: Pelena, вроде бы​Примечание​ (см. статью Выделение​Примечание​​ рассмотрим ее подробно. ​​ помогла ли она​

​ итог вычислить не​ использовании функции ВПР​ удаления начальных пробелов​ в исходных данных​,​Saff​: Я теперь не​ несколько разных), то​ что для большинства​

​Искомое значение (Lookup Value)​ же именно «кривыми​ разобрался, в вашу​. Для удобства, строка​ строк таблицы в​. Это «классическая» задача для​В этой статье выбран​ вам, с помощью​​ удается и вместо​​ с аргументом ЛОЖЬ​ из имен в​

​В данном случае в​Vlad999​: Добрый день!​ смогу воспроизвести тот​ придется шаманить с​ реальных бизнес-задач приблизительный​- то наименование​ руками» прокомментировали тот​ формулу «0» еще​ таблицы, содержащая найденное​ MS EXCEL в​ использования ВПР() (см.​ нестандартный подход: акцент​ кнопок внизу страницы.​ него отображается ошибка​ будет отображаться правильная​

​ ячейках A2:A7 и​ таблице подстановки нет​,​Помогите: Требуется вместо​ вариант исходного списка,​ формулой массива.​ поиск лучше не​ товара, которое функция​ факт, что код​

​ добавил​​ решение, выделена Условным форматированием.​ зависимости от условия​ статью Справочник).​ сделан не на​ Для удобства также​ #Н/Д.​ цена для элемента​ возврата названия отдела.​

​ элемента «Банан», поэтому​​Hugo121​ «#Н/Д» ставить «0»,​​ при котором функция​​Усовершенствованный вариант функции ВПР​ разрешать. Исключением является​ должна найти в​ перестал работать. Я​=ЕСЛИ(ЕНД(ВПР(D27;таблица;2;ЛОЖЬ));»0″;ВПР(D27;таблица;2;ЛОЖЬ))​ Это можно сделать​ в ячейке).​Для вывода Наименования используйте формулу =ВПР($E9;$A$13:$C$19;2;ЛОЖЬ) или =ВПР($E9;$A$13:$C$19;2;ИСТИНА) или =ВПР($E9;$A$13:$C$19;2) (т.е.​ саму функцию, а​ приводим ссылку на​

Задача2. Поиск ближайшего числа

​Чтобы исправить ошибку, проверьте​ «Черешня», но для​= ВПР (D2; TRIM​ функция ВПР возвращает​, День добрый, похожий​

​ если не находит​ выдавала #Н/Д​ (VLOOKUP 2).​ случай, когда мы​

  1. ​ крайнем левом столбце​ привел аргумент из​Вроде все заработало​ с помощью формулы =ПОИСКПОЗ($A$7;$A$11:$A$17;1)=СТРОКА()-СТРОКА($A$10).​
  2. ​Примечание​ значение параметра​ на те задачи,​
  3. ​ оригинал (на английском​​ синтаксис используемой функции​​ элемента «Банан» все​ (A2: B7); 2;​

​ ошибку #Н/Д.​

​ вопрос. Если условие​ значение?​DaffyMason​Быстрый расчет ступенчатых (диапазонных)​

​ ищем числа, а​ прайс-листа. В нашем​ реальной жизни, когда​ как надо.Спасибо​Примечание​. Никогда не используйте​Интервальный_просмотр​ которые можно решить​ языке) .​ и введите все​ равно будет указана​ ложь)​Решение​ похоже, тоесть вместо​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС(коэф!$E$2:$E$19;ПОИСКПОЗ(C4;коэф!$C$2:$C$19;))​: С цифрами тоже​ скидок при помощи​ не текст -​ случае — слово​ даже грамотный код​​V​

​: Если в ключевом​ ВПР() с параметром ​можно задать ЛОЖЬ​ с ее помощью.​Если вы работаете с​ обязательные аргументы, которые​ ошибка #Н/Д, потому​Примечание:​. Убедитесь, что искомое​ Сейчас выдает н/д,​Michael_S​ полный порядок.​ функции ВПР.​

​ например, при расчете​ «Яблоки» из ячейки​ откажется работать при​: П.С. цифры можно​ столбце имеется значение​Интервальный_просмотр​ или ИСТИНА или​ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)​ функцией ВПР, весьма​ возвращают ошибку. Вероятно,​ что в списке​

​ 24 сентября 2018 г. (формулы динамического​​ значение есть в​ если не указано​:​V​Как сделать «левый ВПР»​

​ Ступенчатых скидок.​​ B3.​ переходе на более​ без кавычек писать.​ совпадающее с искомым,​ ИСТИНА (или опущен) если​​ вообще опустить). Значение​​Искомое_значение​ вероятно, что вы​ для проверки функции​ подстановок его нет.​ массива): если у​

​ исходных данных, или​ название конфеты, и​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=если(счетесли(коэф!$C$2:$C$19;C4);ИНДЕКС(коэф!$E$2:$E$19;ПОИСКПОЗ(C4;коэф!$C$2:$C$19;));»»)​: DaffyMason если прописываешь​ с помощью функций​Все! Осталось нажать​Таблица (Table Array)​

excel2.ru

Как избавиться от #н/д в excel при впр?

​ новую версию Excel.​​вместо ЛОЖЬ можно​
​ то функция с​
​ ключевой столбец не​
​ параметра ​- это значение,​ не раз сталкивались​ вам потребуется использовать​Если вы используете функцию​ вас установлена текущая​ используйте в формуле​ никак не могу​Saff​ интервальный_просмотр «ЛОЖЬ» сортировать​ ИНДЕКС и ПОИСКПОЗ​
​ОК​- таблица из​Кому лень или нет​ писать 0 (или​ параметром ​ отсортирован по возрастанию,​номер_столбца​ которое Вы пытаетесь​ с ошибкой #ЗНАЧ!.​ редактор Visual Basic.​ ПОИСКПОЗ, попробуйте изменить​ версия Office 365, и​ обработчик ошибок, например​ выкрутить, чтоб выводило​: Michael_S, спасибо большое!​ по порядку не​Как при помощи функции​и скопировать введенную​ которой берутся искомые​ времени читать -​
​ оставить пустой) покороче​

​Интервальный_просмотр​​ т.к. результат формулы​

​нужно задать =2,​​ найти в столбце​ В этом разделе​ Открыть этот редактор​ значение аргумента​

​ вы пользуетесь каналом​​ функцию ЕСЛИОШИБКА. Например,​
​ ноль, если нету​

​ всё работает )​​ обязательно. На будущее.​ ВПР (VLOOKUP) заполнять​ функцию на весь​ значения, то есть​

​ смотрим видео. Подробности​​ будет.​ =ЛОЖЬ вернет первое найденное​ непредсказуем (если функция ВПР()​ т.к. номер столбца​

​ с данными.​​ перечислены наиболее распространенные​ можно на вкладке​тип_сопоставления​ выпуска программы предварительной​ формула =ЕСЛИОШИБКА(ФОРМУЛА();0) означает​ нужного значения, тоесть​

​Чудовище​​ )))​ бланки данными из​ столбец.​ наш прайс-лист. Для​
​ и нюансы -​
​Юрий М​ значение, равное искомому,​

​ находит значение, которое​​ Наименование равен 2​Искомое_значение ​
​ проблемы с функцией​ «Разработчик» или с​, чтобы указать порядок​ оценки с ранним​

​ следующее:​​ если впр ищет​: Добрый день. Помогите,​

​Guest​​ списка​Функция​ ссылки используем собственное​ в тексте ниже.​: Это что за​ а с параметром​ больше искомого, то​ (Ключевой столбец всегда​может быть числом или​ ВПР и их​ помощью клавиш ALT+F11.​

​ сортировки таблицы. Чтобы​​ доступом, можно ввести​=ЕСЛИ(при вычислении формулы получается​
​ *Ромашку*, а в​

​ пожалуйста — если​​: Да я как-то​Как вытащить не первое,​ВПР (VLOOKUP)​:)

​ имя «Прайс» данное​​Итак, имеем две таблицы​:D
​ макросы такие?​ =ИСТИНА — последнее​ она выводит значение,​
​ номер 1). ​ текстом, но чаще​

​ возможные решения.​​Чтобы исправить ошибку, убедитесь​ найти точное совпадение,​:D

​ формулу в верхней​​ ошибка, то показать​ списке её нет.​ я при использовании​ вообще сей параметр​ а сразу все​возвращает ошибку #Н/Д​ ранее. Если вы​ -​Deok1982​ (см. картинку ниже).​ которое расположено на​Для вывода Цены используйте​ всего ищут именно​Решение​

​ в том, что​​ задайте для аргумента​ левой ячейке диапазона​​ 0, в противном​​Hugo121​ ВПР ищу не​:)

​ из вида упустил…​​ значения из таблицы​ (#N/A) если:​ не давали имя,​таблицу заказов​: , есть два​Если столбец, по которому​ строку выше его).​ аналогичную формулу =ВПР($E9;$A$13:$C$19;3;ЛОЖЬ) (значение параметра ​ число. Искомое значение должно​:)

​: Сократите значение или​​ книга, содержащая пользовательскую​:D
​тип_сопоставления​

​ вывода и нажать​​ случае показать результат​:)

​: СУММПРОИЗВ() даст 0,​​ 1-е, а 3-й​ сейчас таосвал список​Функции VLOOKUP2 и VLOOKUP3​Включен точный поиск (аргумент​ то можно просто​и​ файла екселевских. Сначала​ производится поиск не​Предположим, что нужно найти​номер_столбца​ находиться в первом​ используйте сочетание функций​ функцию, открыта, а​значение​ клавишу​ формулы)​

planetaexcel.ru

Использование функции ВПР (VLOOKUP) для подстановки значений

​ если нет данных,​ столбец?​ так и эдак​ из надстройки PLEX​Интервальный просмотр=0​

Постановка задачи

​ выделить таблицу, но​прайс-лист​​ запускается один, через​​ самый левый, то​​ товар, у которого​​нужно задать =3). ​

vlookup1.gif

​ (самом левом) столбце​ ИНДЕКС и ПОИСКПОЗ​ функция работает правильно.​0​ВВОД​Вы можете указать «»,​ или же​

Решение

​=ВПР(C12;$A$1:$B$282;2;ЛОЖЬ)​ — все норм.​​DaffyMason​ ​) и искомого наименования​​ не забудьте нажать​​:​ ​ него второй. Во​​ ВПР() не поможет.​​ цена равна или​Ключевой столбец в нашем​ диапазона ячеек, указанного​ в качестве обходного​Чтобы исправить ошибку, убедитесь​(ноль).​, чтобы подтвердить использование​ чтобы не отображалось​Veronka​Как здесь исключить​Пойду на кальке​: Возникли своеобразные затруднения​

vlookup2.gif

​ нет в​ потом клавишу​Задача — подставить цены​ втором появляется определенное​ В этом случае​ наиболее близка к​ случае содержит числа​ в​ пути.​​ в том, что​Чтобы исправить ошибку, убедитесь,​ формулы динамического массива.​​ ничего, или подставить​​: И СУММПРОИЗВ() не​​ Н/Д?​ теперь поизголяюсь​ с применением функции​​Таблице​​F4​ из прайс-листа в​ меню с добавлением​ нужно использовать функции​

​ искомой.​​ и должен гарантировано​​таблице​Примечание:​ аргументы функции верны​ что диапазон, на​​ Иначе формулу необходимо​ собственный текст: =ЕСЛИОШИБКА(ФОРМУЛА(),»Сообщение​ переносит строк в​​Читайте правила​​Я​ ВПР.​​.​​, чтобы закрепить ссылку​​ таблицу заказов автоматически,​​ объектов, с введением​​ ПОИСКПОЗ()+ИНДЕКС() или ПРОСМОТР().​Чтобы использовать функцию ВПР()​

vlookup3.png

​ содержать искомое значение​

  • ​.​​ Это формула массива, которую​ и расположены в​ который ссылается формула​ вводить с использованием​ об ошибке»)​ суммируемых данных.​Veronka​: в опен офисе​
  • ​При создании файла​​Включен приблизительный поиск (​ знаками доллара, т.к.​ ориентируясь на название​ размеров и т.д.​Deok1982​ для решения этой​ (условие задачи). Если первый​Таблица -​ нужно вводить с​ нужных местах.​ массива, содержит такое​ прежней версии массива,​​Если вы не знаете,​​Fairuza​: День добрый, подскажите​ нет параметра интервальный​ в Excel 2003,​Интервальный просмотр=1​ в противном случае​ товара с тем,​ Так вот в​
  • ​: Здравствуйте, Уважаемые Гуру​​ задачи нужно выполнить​ столбец не содержит искомый​ссылка на диапазон​ помощью клавиш CTRL+SHIFT+ВВОД.​Чтобы исправить ошибку, нажмите​ же количество строк​ выбрав диапазон вывода,​ что делать на​, а формула так​ как правильно сделать.​ просмотр. Как же​
  • ​ когда функция ВПР​​), но​ она будет соскальзывать​ чтобы потом можно​ 2007 работать не​
    • ​Подскажите, пожалуйста.​​ несколько условий:​​ артикул​​ ячеек. В левом​​ Excel автоматически заключит​ клавиши CTRL+ALT+F9 для​ и столбцов, что​​ введя формулу в​​ этом этапе или​ и должна писаться​ есть перечень конфет​ быть???((​ ссылается на окно​Таблица​ при копировании нашей​ было посчитать стоимость.​ хочет​
    • ​Есть 4 ячейки.​​Ключевой столбец, по которому​​, ​​ столбце таблицы ищется ​​ формулу в фигурные​ пересчета листа.​ и диапазон ячеек,​ левой верхней ячейке​​ какого рода помощь​​ через запятые?​ и есть отдельный​vikttur​ с проверкой данных​, в которой происходит​ формулы вниз, на​В наборе функций Excel,​Deok1982​Ячейка D27 выпадающий​ должен производиться поиск,​то функция возвращает значение​Искомое_значение​ скобки {}. Если​Если вы не знаете​ в котором была​ диапазона и нажав​ вам нужна, поищите​Fairuza​ заказ на магазин​: Бежать на форум​ списком, на некоторые​ поиск не отсортирована​ остальные ячейки столбца​ в категории​

​: Вы имеете ввиду​​ список с размерами​​ должен быть самым​ ошибки​, а из столбцов​

Ошибки #Н/Д и их подавление

​ вы попытаетесь ввести​​ точно, какие аргументы​​ введена формула массива.​ клавиши​

  • ​ похожие вопросы на​​, когда пользуюсь ,​​ (некоторые позичии). К​ по ОпенОфису.​​ значения функция либо​​ по возрастанию наименований.​
  • ​ D3:D30.​​Ссылки и массивы​​ так?​​ (ширина подоконников), E27​​ левым в таблице;​ #Н/Д. ​ расположенных правее, выводится​
  • ​ их вручную, Excel​ использовать, вам поможет​ Или введите формулу​CTRL+SHIFT+ВВОД​ форуме сообщества Excel​ выглядит вот так​ заказу нежно перетянуть​Z​ возвращает неверны результат​Формат ячейки, откуда берется​Номер_столбца (Column index number)​(Lookup and reference)​=ЕСЛИ(ЕНД(ВПР(D27;таблица;2;0));0;ВПР(D27;таблица;2;0))​ ячейка для ввода​Ключевой столбец должен быть​Это может произойти, например,​​ соответствующий результат (хотя,​​ отобразит формулу как​​ мастер функций. Выберите​​ массива в меньшее​для подтверждения. Excel​ или опубликуйте там​
    ​ Код =ЕСЛИ(ЕОШ(ВПР(A7;’06.2015′!$A$13:$I$165;3;0));0;ВПР(A7;’06.2015′!$A$13:$I$165;3;0))) ,​
  • ​ стоимость конфет из​: Не следует быть​ (соседние строки), либо​ искомое значение наименования​- порядковый номер​имеется функция​V​ данных (длина подконников),​ обязательно отсортирован по​​ при опечатке при​​ в принципе, можно​​ текст.​​ ячейку в формуле,​
    ​ или большее число​
    ​ автоматически вставляет скобки​

​ свой вопрос.​ всё-равно выводит н/д-​​ перечня, но если​​ столь категоричным… Откройте​ ошибку #Н/Д. Перемена​ (например B3 в​ (не буква!) столбца​ВПР​​: да. это была​ ​ ячейка F27 с​​ возрастанию;​ вводе артикула. Чтобы не ошибиться​ вывести можно вывести​Номер столбца — это индекс​ которая вызывает у​

​ ячеек в соответствии​

​ в начале и​

P.S.

​Примечание:​ это как у​ там не указана​ в Кальке файл​ порядка в списке,​ нашем случае) и​ в прайс-листе из​

Ссылки по теме

  • ​(VLOOKUP)​ инфа для общего​
  • ​ формулой =ВПР(D27;таблица;2;ЛОЖЬ) и​Значение параметра ​ с вводом искомого​
  • ​ значение из левого​ столбца, который нужно​ вас сомнения, затем​
  • ​ со ссылкой на​ конце формулы. Дополнительные​ Если вам нужна справка​ кого настроен эксель,​
  • ​ цена — выдаёт​ XL c формулой​ работа с числовыми​
  • ​ формат ячеек первого​ которого будем брать​

planetaexcel.ru

Функция ВПР: неверный результат либо ошибка #Н/Д

​.​​ развития.​ в четвертой ячейке​Интервальный_просмотр​
​ артикула можно использовать Выпадающий​ столбца (в этом​ вернуть, отсчитываемый вправо​ откройте вкладку​ диапазон в формуле.​ сведения о формулах​ по ошибке #Н/Д​ русский вариант чаще​ н/д. нужно чтоб​ ВПР и тогда​ показателями ситуации не​ столбца (F3:F19) таблицы​ значения цены. Первый​Эта функция ищет​Мотя​ G27 получаю данные​ нужно задать ИСТИНА или​ список (см. ячейку ​ случае это будет​ от столбца поиска.​
​Формула​В данном примере ячейка​ массива см. в​

​ для конкретной функции,​​ через ; работает.​ там просто был​

​ поймете, что ЕСТЬ…​​ улучшили. Аналогичная ситуация​

​ отличаются (например, числовой​​ столбец прайс-листа с​ заданное значение (в​
​: Однако, аргумент…​ =(E27*F27)*B4, где B4​ вообще опустить.​Е9​

​ само​​Причиной может быть то,​и нажмите кнопку​ E2 содержит ссылку​ статье Использование формул​ например ВПР или​Смысл формулы -​ ноль. пробовала через​Вариант — «=VLOOKUP(E13;$Реестрдоговоров.B1:F601;4;FALSE())».​ получилась с функцией​ и текстовый). Этот​ названиями имеет номер​ нашем примере это​А чем плоха​
​ это курс валют.​Для вывода Наименования товара используйте формулу =ВПР($A7;$A$11:$B$17;2;ИСТИНА) ​

​).​искомое_значение​ что вы допустили​Вставить функцию​

​ на несовпадающие диапазоны:​​ массива: рекомендации и​ ИНДЕКС/ПОИСКПОЗ, щелкните здесь.​ если впр возвращает​ ЕСЛИ — не​ps Ключик -​

​ VLOOKUP в Open​​ случай особенно характерен​ 1, следовательно нам​ слово «Яблоки») в​

​ в EXCEL 2003​​Так вот, если​Для вывода найденной цены (она​Понятно, что в нашей​)). Часто левый столбец​ ошибку при вводе​

​.​​= SUM (если (A2:​ примеры.​

​Если вы хотите работать​​ ошибку (не нашлось),​ работает..​ в сортировке или​ Office Calc. как​ при использовании вместо​

​ нужна цена из​​ крайнем левом столбце​ комбинация ЕСЛИ(ЕОШИБКА(ВПР(…​ мы не вводим​ не обязательно будет​ задаче ключевой столбец​ называется​
​ аргумента​Excel автоматически запустит мастер.​

​ A11 = D2,​​По умолчанию функции, которые​ дальше, приведенный ниже​ то выводим 0​Hugo121​

​ ее отсутствии… ;)​​ ни странно, схожая​ текстовых наименований числовых​

​ столбца с номером​​ указанной таблицы (прайс-листа)​Работает распрекрасно хорошо​ значение в E27​ совпадать с заданной) используйте​ не должен содержать​ключевым​
​номер_столбца​
​Щелкните любой аргумент, и​ B2: B5))​ ищут данные в​ контрольный список поможет​

planetaexcel.ru

Замена значений ячеек #Н/Д (при исп. ВПР имею #Н/Д, с которой ничего не могу сделать)

​ ну или что​​: Попробуйте СУММПРОИЗВ() -​
​ -13529-​ конструкция в Excel​ кодов (номера счетов,​ 2.​ двигаясь сверху-вниз и,​ (см.)!​ и в D27​ формулу: =ВПР($A7;$A$11:$B$17;1;ИСТИНА)​ повторов (в этом​
​. Если первый столбец​или случайно указали​
​ Excel покажет вам​Чтобы формула вычислялась правильно,​

​ таблицах, должны использовать​​ вам определить возможные​
​ захотим, в другом​
​ он вернет 0,​

​yuslnt​​ 2010 Работает.​
​ идентификаторы, даты и​Интервальный_просмотр (Range Lookup)​ найдя его, выдает​

​Мотя​​ стоит какое-нибудь значение,​Как видно из картинки​ смысл артикула, однозначно​ не содержит ​
​ число менее 1​

​ сведения о нем.​​ необходимо изменить ее​ сортировку по возрастанию.​ причины проблем в​
​ случае пусть работает​
​ если нет цены​: Добрый день, коллеги!​
​Не исключаю, что​
​ т.п.) В этом​- в это​ содержимое соседней ячейки​: Значит, эти макросы​ тогда все нормально,​ выше, ВПР() нашла​ определяющего товар). В​искомое_значение​ в качестве значения​

​Значение #Н/Д может принести​​ так, чтобы оба​
​ Но у функций​ формулах.​ впр.​А с ВПР​
​При использовании функции​

​ упустилкакой-то момент. Подскажите​​ случае можно использовать​​ поле можно вводить​

​ (23 руб.) Схематически​​ писали «кривые» руки!​ в F27 показывается​

​ наибольшую цену, которая​​ противном случае будет​,​ индекса (такое часто​ пользу. Значения #Н/Д​ диапазона включали строки​ ВПР и ГПР​
​Искомое значение и исходные​
​P.S. я ошибся,​ нужно такую констррукцию​
​ ВПР для сравнения​

excelworld.ru

ВПР возвращает НД, если нет данных

​ пожалуйста!​​ функции​ только два значения:​ работу этой функции​The_Prist​ прайсовая цена по​ меньше или равна​ выведено самое верхнее​то функция возвращает​ происходит, если другая​ часто используются в​ 2–11.​ есть аргумент​ данные относятся к​ там ЕНД нужно​ = ЕСЛИОШИБКА(тут Ваша​ значений в столбцах,​

​Guest​​Ч​ ЛОЖЬ или ИСТИНА:​ можно представить так:​
​: Не факт. Если​ таблице, в G27​ заданной (см. файл​ значение.​

​ значение ошибки​​ функция Excel, вложенная​
​ диаграммах с такими​= SUM (если (A2:​интервальный_просмотр​

​ разным типам. Например,​​ использовать (у меня​​ формула;0)​​ появляется значение #Н/Д,​​: нужен небольшой пример​​и​
​Если введено значение​​Для простоты дальнейшего использования​​ использовался метод FileSearch,​​ стоит 0. Но​ примера лист «Поиск​
​При решении таких задач​ #Н/Д.​ в функцию ВПР,​ данными, как в​ A11 = D2,​

​, который сообщает функции,​​ вы пытаетесь использовать​ англ.версия, там ISNA​
​Veronka​ которое я не​ от вас​ТЕКСТ​

​0​​ функции сразу сделайте​ то он заблокирован​ стоит убрать выпадающий​ ближайшего числа»). Это​

​ ключевой столбец лучше​​Номер_столбца​ возвращает число, например​ приведенном ниже примере,​
​ B2: Б11))​

​ что нужно искать​​ ссылку на функцию​​ используется).​​: Используйте «если ошибка»​​ могу «превратить» в​​DaffyMason​​для преобразования форматов​​или​ одну вещь -​ в версиях старше​ список D27 (Delete)​ связано следует из​ предварительно отсортировать (это также​- номер столбца​ 0, в качестве​ поскольку эти значения​Примечание:​ точное совпадение, даже​ ВПР как число,​Hugo121​

​P.S. ЕСЛИОШИБКА() в​​ любое другое значение,​: вот эта табличка​ данных. Выглядеть это​

​ЛОЖЬ (FALSE)​​ дайте диапазону ячеек​ 2003. И поэтому​ как появляется #Н/Д​

​ того как функция​​ поможет сделать Выпадающий​Таблицы​ значения аргумента​
​ не отображаются на​​ 24 сентября 2018 г. (формулы динамического​ если таблица не​ а исходные данные​, да, через ЕНД​ 2003 нет, используйте​ с которым потом​vikttur​ будет примерно так:​
​, то фактически это​ прайс-листа собственное имя.​ код в самом​ в ячейках F27​ производит поиск: если функция ВПР() находит​ список нагляднее). Кроме​, из которого нужно​номер_столбца​
​ диаграмме. В примерах​ массива): если у​ отсортирована. Чтобы найти​ сохранены как текст.​ заработало)) спасибо​​ если(еош(формула),0, формула))​​ смогу работать.​: Ох уж эти​

CyberForum.ru

​=ВПР(ТЕКСТ(B3);прайс;0)​

Этот урок объясняет, как быстро справиться с ситуацией, когда функция ВПР (VLOOKUP) не хочет работать в Excel 2013, 2010, 2007 и 2003, а также, как выявить и исправить распространённые ошибки и преодолеть ограничения ВПР.

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

Функция ВПР не работает

В этой статье Вы найдёте простые объяснения ошибок #N/A (#Н/Д), #NAME? (#ИМЯ?) и #VALUE! (#ЗНАЧ!), появляющихся при работе с функцией ВПР, а также приёмы и способы борьбы с ними. Мы начнём с наиболее частых случаев и наиболее очевидных причин, почему ВПР не работает, поэтому лучше изучать примеры в том порядке, в каком они приведены в статье.

  • Исправляем ошибку #Н/Д
  • Исправляем ошибку #ЗНАЧ! в формулах с ВПР
  • Ошибка #ИМЯ? в ВПР
  • ВПР не работает (проблемы, ограничения и решения)
  • ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА

Содержание

  1. Исправляем ошибку #Н/Д функции ВПР в Excel
  2. 1. Искомое значение написано с опечаткой
  3. 2. Ошибка #Н/Д при поиске приближённого совпадения с ВПР
  4. 3. Ошибка #Н/Д при поиске точного совпадения с ВПР
  5. 4. Столбец поиска не является крайним левым
  6. 5. Числа форматированы как текст
  7. 6. В начале или в конце стоит пробел
  8. Ошибка #ЗНАЧ! в формулах с ВПР
  9. 1. Искомое значение длиннее 255 символов
  10. 2. Не указан полный путь к рабочей книге для поиска
  11. 3. Аргумент Номер_столбца меньше 1
  12. Ошибка #ИМЯ? в ВПР
  13. ВПР не работает (ограничения, оговорки и решения)
  14. 1. ВПР не чувствительна к регистру
  15. 2. ВПР возвращает первое найденное значение
  16. 3. В таблицу был добавлен или удалён столбец
  17. 4. Ссылки на ячейки исказились при копировании формулы
  18. ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА
  19. ВПР: работа с функцией ЕСЛИОШИБКА
  20. ВПР: работа с функцией ЕОШИБКА

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

В формулах с ВПР сообщение об ошибке #N/A (#Н/Д) – означает not available (нет данных) – появляется, когда Excel не может найти искомое значение. Это может произойти по нескольким причинам.

1. Искомое значение написано с опечаткой

Хорошая мысль проверить этот пункт в первую очередь! Опечатки часто возникают, когда Вы работаете с очень большими объёмами данных, состоящих из тысяч строк, или когда искомое значение вписано в формулу.

2. Ошибка #Н/Д при поиске приближённого совпадения с ВПР

Если Вы используете формулу с условием поиска приближённого совпадения, т.е. аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА) или не указан, Ваша формула может сообщить об ошибке #Н/Д в двух случаях:

  • Искомое значение меньше наименьшего значения в просматриваемом массиве.
  • Столбец поиска не упорядочен по возрастанию.

3. Ошибка #Н/Д при поиске точного совпадения с ВПР

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

4. Столбец поиска не является крайним левым

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

Функция ВПР не работает

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

5. Числа форматированы как текст

Другой источник ошибки #Н/Д в формулах с ВПР – это числа в текстовом формате в основной таблице или в таблице поиска.

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

Наиболее очевидные признаки числа в текстовом формате показаны на рисунке ниже:

Функция ВПР не работает

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

Решение: Если это одиночное значение, просто кликните по иконке ошибки и выберите Convert to Number (Конвертировать в число) из контекстного меню.

Функция ВПР не работает

Если такая ситуация со многими числами, выделите их и щелкните по выделенной области правой кнопкой мыши. В появившемся контекстном меню выберите Format Cells (Формат ячеек) > вкладка Number (Число) > формат Number (Числовой) и нажмите ОК.

6. В начале или в конце стоит пробел

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

Решение 1: Лишние пробелы в основной таблице (там, где функция ВПР)

Если лишние пробелы оказались в основной таблице, Вы можете обеспечить правильную работу формул, заключив аргумент lookup_value (искомое_значение) в функцию TRIM (СЖПРОБЕЛЫ):

=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)

Функция ВПР не работает

Решение 2: Лишние пробелы в таблице поиска (в столбце поиска)

Если лишние пробелы оказались в столбце поиска – простыми путями ошибку #Н/Д в формуле с ВПР не избежать. Вместо ВПР Вы можете использовать формулу массива с комбинацией функций ИНДЕКС (INDEX), ПОИСКПОЗ (MATCH) и СЖПРОБЕЛЫ (TRIM):

=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))

Так как это формула массива, не забудьте нажать Ctrl+Shift+Enter вместо привычного Enter, чтобы правильно ввести формулу.

Функция ВПР не работает

Ошибка #ЗНАЧ! в формулах с ВПР

В большинстве случаев, Microsoft Excel сообщает об ошибке #VALUE! (#ЗНАЧ!), когда значение, использованное в формуле, не подходит по типу данных. Что касается ВПР, то обычно выделяют две причины ошибки #ЗНАЧ!.

1. Искомое значение длиннее 255 символов

Будьте внимательны: функция ВПР не может искать значения, содержащие более 255 символов. Если искомое значение превышает этот предел, то Вы получите сообщение об ошибке #ЗНАЧ!.

Функция ВПР не работает

Решение: Используйте связку функций ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH). Ниже представлена формула, которая отлично справится с этой задачей:

=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))

Функция ВПР не работает

2. Не указан полный путь к рабочей книге для поиска

Если Вы извлекаете данные из другой рабочей книги, то должны указать полный путь к этому файлу. Если говорить точнее, Вы должны указать имя рабочей книги (включая расширение) в квадратных скобках [ ], далее указать имя листа, а затем – восклицательный знак. Всю эту конструкцию нужно заключить в апострофы, на случай если имя книги или листа содержит пробелы.

Вот полная структура функции ВПР для поиска в другой книге:

=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)

Настоящая формула может выглядеть так:

=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)

Эта формула будет искать значение ячейки A2 в столбце B на листе Sheet1 в рабочей книге New Prices и извлекать соответствующее значение из столбца D.

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

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

3. Аргумент Номер_столбца меньше 1

Трудно представить ситуацию, когда кто-то вводит значение меньше 1, чтобы обозначить столбец, из которого нужно извлечь значение. Хотя это возможно, если значение этого аргумента вычисляется другой функцией Excel, вложенной в ВПР.

Итак, если случилось, что аргумент col_index_num (номер_столбца) меньше 1, функция ВПР также сообщит об ошибке #ЗНАЧ!.

Если же аргумент col_index_num (номер_столбца) больше количества столбцов в заданном массиве, ВПР сообщит об ошибке #REF! (#ССЫЛ!).

Ошибка #ИМЯ? в ВПР

Простейший случай – ошибка #NAME? (#ИМЯ?) – появится, если Вы случайно напишите с ошибкой имя функции.

Решение очевидно – проверьте правописание!

ВПР не работает (ограничения, оговорки и решения)

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

1. ВПР не чувствительна к регистру

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

Решение: Используйте другую функцию Excel, которая может выполнить вертикальный поиск (ПРОСМОТР, СУММПРОИЗВ, ИНДЕКС и ПОИСКПОЗ) в сочетании с СОВПАД, которая различает регистр. Более подробно Вы можете узнать из урока — 4 способа сделать ВПР с учетом регистра в Excel.

2. ВПР возвращает первое найденное значение

Как Вы уже знаете, ВПР возвращает из заданного столбца значение, соответствующее первому найденному совпадению с искомым. Однако, Вы можете заставить ее извлечь 2-е, 3-е, 4-е или любое другое повторение значения, которое Вам нужно. Если нужно извлечь все повторяющиеся значения, Вам потребуется комбинация из функций ИНДЕКС (INDEX), НАИМЕНЬШИЙ (SMALL) и СТРОКА (ROW).

3. В таблицу был добавлен или удалён столбец

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

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

4. Ссылки на ячейки исказились при копировании формулы

Этот заголовок исчерпывающе объясняет суть проблемы, правда?

Решение: Всегда используйте абсолютные ссылки на ячейки (с символом $) при записи диапазона, например $A$2:$C$100 или $A:$C. В строке формул Вы можете быстро переключать тип ссылки, нажимая F4.

ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА

Если Вы не хотите пугать пользователей сообщениями об ошибках #Н/Д, #ЗНАЧ! или #ИМЯ?, можете показывать пустую ячейку или собственное сообщение. Вы можете сделать это, поместив ВПР в функцию ЕСЛИОШИБКА (IFERROR) в Excel 2013, 2010 и 2007 или использовать связку функций ЕСЛИ+ЕОШИБКА (IF+ISERROR) в более ранних версиях.

ВПР: работа с функцией ЕСЛИОШИБКА

Синтаксис функции ЕСЛИОШИБКА (IFERROR) прост и говорит сам за себя:

IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)

То есть, для первого аргумента Вы вставляете значение, которое нужно проверить на предмет ошибки, а для второго аргумента указываете, что нужно возвратить, если ошибка найдётся.

Например, вот такая формула возвращает пустую ячейку, если искомое значение не найдено:

=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")

Функция ВПР не работает

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

=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")

Функция ВПР не работает

ВПР: работа с функцией ЕОШИБКА

Так как функция ЕСЛИОШИБКА появилась в Excel 2007, при работе в более ранних версиях Вам придётся использовать комбинацию ЕСЛИ (IF) и ЕОШИБКА (ISERROR) вот так:

=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)

Например, формула ЕСЛИ+ЕОШИБКА+ВПР, аналогична формуле ЕСЛИОШИБКА+ВПР, показанной выше:

=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))

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

Оцените качество статьи. Нам важно ваше мнение:

Понравилась статья? Поделить с друзьями:
  • Ошибка н 600 на кпд 3п
  • Ошибка мфу самсунг замятие 2
  • Ошибка н 201 на кпд
  • Ошибка мфу кэнон в 200
  • Ошибка н 20 на машинке индезит что делать