[Решено:] функция ВПР в excel не работает при протягивании формулы что делать?

В данной статье вы узнаете почему у вас может не работать формула ВПР когда вы протягиваете ее на другие ячейки. Давайте разберемся что произошло с excel и как это исправить. Данная инструкция актуальна на 2021 и 2022 год. Если у вас есть свои варианты делитесь и я опубликую ваш вариант.

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

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

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

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

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

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

Как вызвать функцию ВПР. Функция ВПР в Excel

В первую очередь разберемся, как вызвать данную функцию. Выбираем закладку Формулы. Находим кнопку Вставить функцию. И нажимаем ее. Так же, можно вызвать функцию ВПР, сочетанием клавиш Shift + F3.

Функция ВПР в MS Excel. Описание и примеры использования.

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

Функция ВПР в MS Excel. Описание и примеры использования.

Теперь перейдем непосредственно к вариантам применения функции ВПР.

Зафиксируйте ссылки на таблицу

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

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

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

Решение

Аргумент table_array (таблица) – это таблица, которую ВПР использует для поиска и извлечения информации. Чтобы корректно скопировать функцию ВПР, в аргументе table_array (таблица) должна быть абсолютная ссылка на диапазон ячеек.

Кликните по адресу ссылки внутри формулы и нажмите F4 на клавиатуре, чтобы превратить относительную ссылку в абсолютную. Формула должна быть записана так:

=VLOOKUP($H$3,$B$3:$F$11,4,FALSE)
=ВПР(($H$3;$B$3:$F$11;4;ЛОЖЬ)

В этом примере ссылки в аргументах lookup_value (искомое_значение) и table_array (таблица) сделаны абсолютными. Иногда достаточно зафиксировать только аргумент table_array (таблица).

Первый вариант использования функции ВПР. 

Для примера возьмем две таблице. В одной Таблице №1 будет перечень с названиями конфет и будет указана их цена за кг. В другой, Таблица №2, тот же перечень, но с указанием их количества в кг. Наша задача добавить в Таблицу №2, в столбец Цена, цену конфет из Таблицы №1, чтобы в итоге получить стоимость. Названия конфет в разных таблицах находятся в разных местах, поэтому просто скопировать цену конфет с одной таблице в другую не получиться.

Первый вариант использования функции ВПР

Перед тем, как вызвать функцию ВПР, выбираем нужную нам ячейку, в которой будет находиться наша формула функции и соответственно значение, которое мы хотим увидеть. В нашем случае это ячейка G3. Эта ячейка находиться в столбце Цена, Таблица №2. Функция ВПР позволит взять из Таблицы №1 цену Конфеты А и вставить эту цену в столбец Цена, Таблицы №2, напротив Конфеты А.

Вызываем функцию ВПР, как описано выше.

Синтаксис

Синтаксис функции ЕСЛИОШИБКА предполагает всего два аргумента, оба — обязательные:

=ЕСЛИОШИБКА(Значение-Или-Вычисление;Значение-если-ошибка)

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

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

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

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

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

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

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

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

Отличного Вам дня!

Пример 1: ЕСЛИОШИБКА + ВПР

Наиболее характерный пример использования — в паре с функцией ВПР при поиске данных в больших таблицах.

=ЕСЛИОШИБКА(ВПР(ячейка-или-значение;диапазон;номер-столбца;0);”ошибка”)=ЕСЛИОШИБКА(ВПР(ячейка-или-значение;диапазон;номер-столбца;0);””)

Первый вариант вернет текстовое значение ошибки, второй оставит строку пустой.

Поскольку ВПР может изрядно загрузить процессор, функция ЕСЛИОШИБКА здесь весьма кстати.

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

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

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

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

Решение.

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

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

ВПР не может смотреть влево

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

Решение

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

Пример, приведённый ниже, был использован для извлечения информации из колонки слева от той, по которой производится поиск:

=INDEX(B3:B13,MATCH(H3,C3:C13,0))
=ИНДЕКС(B3:B13;ПОИСКПОЗ(H3;C3:C13;0))

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

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

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

Другие логические функции

ЕСЛИ, И, ИЛИ, НЕ

ВПР без забот

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

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: https://www.ablebits.com/office-addins-blog/2014/04/09/why-excel-vlookup-not-working/
Перевел: Антон Андронов
Правила перепечатки
Еще больше уроков по Microsoft Excel

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

См. также

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

  • ВLOOKUP: больше нет #NA

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

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

  • Функция ВПР

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

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

  • Обнаружение ошибок в формулах

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

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

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...