Функция впр работает не правильно

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

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

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

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

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

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

Ошибка #N/A, так как искомый аргумент «Ели» отображается во втором столбце (Продукты) аргумента table_array A2:C10. В этом случае Excel искать его в столбце A, а не в столбце B.

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

Вместо нее можно использовать индекс или СОВПАДЕНИЕ

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

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

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

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

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

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

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

Синтаксис

Чтобы создать синтаксис функции ИНДЕКС или НАЙТИВКА, необходимо использовать аргумент массива или ссылки функции ИНДЕКС и вложенный в нее синтаксис ФУНКЦИИ НАЙТИВ. Это форма:

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

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

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

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

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

Если range_lookup ложь и не удается найти точное совпадение в данных, возвращается #N/A.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Источник

Не работает ВПР в Excel: с чем это связано

Не работает ВПР в Excel

Windows 11

Не отображается текст в ячейке Excel

Как отобразить строки в Excel

Как закрыть Эксель, если не закрывается

Как сделать, чтобы Эксель не округлял числа

Не работает ВПР в Excel н д? Причина в том, что формула не может найти необходимое значение, к примеру, из-за отсутствия искомого параметра в файле. Убедитесь, что такой показатель имеется в первоначальных данных, проверьте тип значений, удалите лишние пробелы, используйте способы точного / ориентировочного совпадения, задействуйте правильные аргументы и т. д. Ниже рассмотрим, в чем могут быть причины, и как действовать для восстановления работоспособности Эксель.

Причины и пути решения в Excel

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

Наиболее эффективный метод

В ситуации, когда не работает функция ВПР в Excel, проверьте наличие элемента на листе или задействуйте в формуле функцию обработки ошибок, к примеру, =ЕСЛИОШИБКА(ФОРМУЛА();0). В таком случае при появлении сбоев в расчете показывается ноль, а в ином случае — результат формулы. Можно дополнить запись “”, чтобы ничего не показывалось, или внести в скобки какую-либо запись.

Ошибка в типе параметров

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

• Выберите ячейку (одну или несколько).
• Жмите правой кнопкой мышки.
• Выберите формат ячеек, а дальше Число.

Для принудительного внесения изменений нужно изменить формат для всего столбца. Для начала примените требуемое форматирование, а после выберите «Данные», «Текст по столбцам» и «Готово». После этого проверьте, появляется Н Д или нет.

Лишние пробелы

Распространенная причина, почему не работает формула ВПР в Excel, состоит в наличии пробелов. Для их удаления используйте функцию СЖПРОБЕЛЫ.

Ошибки метода поиска совпадения

Следующее объяснение, почему не срабатывает ВПР в Excel и возвращается Н Д — ошибки в применении метода совпадения. По умолчанию у опции ВПР имеется аргумент «интервальный просмотр», который дает команду на поиск точного совпадения даже при отсутствии сортировки данных в таблице.

Для поиска точного совпадения введите для аргумента «интервальный_просмотр» показатель ЛОЖЬ.

При этом учтите, что ИСТИНА, которое дает возможность поиска приблизительного параметра, может вернуть ошибку Н / Д. При использовании опции ПОИСКПОЗ попробуйте поменять параметр аргумента «тип_сопоставления» для указания порядке сортировки таблицы.

Не соответствие числа строк / столбцов заданному диапазону

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

В ячейке введена надпись Н / Д или Н Д

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

Другие ошибки

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

1. В используемой формуле нет одного или более аргументов. Для исправления проблемы введите все необходимые документы и проверьте, работает опция или нет. Для контроля можно использовать Visual Basic.
2. Пользовательская опция недоступна. Для исправления проблемы убедитесь, что документ Excel с пользовательской функцией открыт, а опция работает корректно.
3. Макрос имеет функцию, которая возвращает Н Д. Если ВПР не работает по этой причине, для исправления ошибки убедитесь в правдивости аргументов и их нахождении в нужных местах.
4. Изменение защищенного файл с опцией ЯЧЕЙКА. Для исправления ситуации, когда ВПР в Excel не работает, жмите на комбинацию Ctrl+Alt+F9.
5. Столбец не является первым слева дли поискового диапазона. Для решения проблемы нужно ввести соответствующий параметр и проверить, появляется ли Н Д. Как вариант, можно использовать функции ИНДЕКС и ПОИСКПОК в качестве гибкой альтернативы для ВПР.
6. Неправильное форматирование числа. Бывают ситуации, когда цифры указаны в текстовом формате. Это часто происходит при импортировании сведений из внешней базы данных или при вводе апострофа перед числом для сохранения нуля в начале. Для решения проблемы жмите по ошибке и укажите Convert to Number. При появлении Н Д для многих чисел выделите их и жмите правой кнопкой мышки, а после выберите Format Cells и вкладку Число и Числовой.

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

Что за функция

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

К примеру, в вас есть две таблицы. Первая — цены и названиями, вторая — заказ на покупку продукции. Осуществлять поиск в первом документе и пытаться вписать цену в заказ трудно. Необходимо, чтобы работа проходила автоматически. Для этого достаточно найти нужное значение в 1-м столбце и вернуть его содержимое из столбца той же строки, где находится название.

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

Источник