Функция ВПР (VLOOKUP) — одна из самых популярных функций в категории «Ссылки и массивы» в Excel. Это также одна из наиболее сложных функций Excel, где ужасная ошибка # N/A (# N/A) может стать обычным явлением. В этой статье мы рассмотрим 6 наиболее распространенных причин, по которым функция ВПР не работает.
Вам нужно точное совпадение
Последний аргумент функции ВПР, известный как range_lookup, спрашивает, хотите ли вы приблизительное или точное совпадение.
В большинстве случаев люди ищут конкретный продукт, заказ, сотрудника или клиента, а затем хотят получить точное соответствие. Если вы ищете уникальное значение, range_lookup должно иметь значение FALSE).
Этот аргумент является необязательным, но если не указан, будет использоваться ИСТИНА. В этом случае для корректной работы функции данные должны быть отсортированы по возрастанию.
На следующем рисунке показана функция ВПР с опущенным аргументом range_lookup (range_lookup), возвращающая неверный результат.
Решение
Если вы ищете уникальное значение, установите последний аргумент в FALSE. Функция ВПР в приведенном выше примере должна выглядеть так:
= ВПР (H3; B3: F11,2; ЛОЖЬ)
= ВПР (H3; B3: F11; 2; ЛОЖЬ)
Зафиксируйте ссылки на таблицу
Вы можете использовать несколько функций ВПР одновременно, чтобы извлечь дополнительную информацию. Если вы собираетесь скопировать функцию ВПР в несколько ячеек, вам необходимо исправить некоторые аргументы.
На следующем рисунке показан пример неправильно введенной функции ВПР. Введены недопустимые диапазоны ячеек для lookup_value (lookup_value) и table_array (table.
Решение
Table_array (таблица) — это таблица, используемая функцией VLOOKUP для поиска и извлечения информации. Чтобы успешно скопировать функцию ВПР, аргумент table_array (table) должен содержать абсолютную ссылку на диапазон ячеек.
Щелкните адрес ссылки в формуле и нажмите F4 на клавиатуре, чтобы преобразовать относительную ссылку в абсолютную. Формула должна выглядеть так:
= ВПР ($ H $ 3, $ B $ 3: $ F $ 11,4, FALSE)
= ВПР (($ H $ 3; $ B $ 3: $ F $ 11; 4; ЛОЖЬ)
В этом примере ссылки в аргументах lookup_value (lookup_value) и table_array (table) сделаны абсолютными. Иногда достаточно просто захватить аргумент table_array (таблица).
Вставлен столбец
Аргумент col_index_num (номер_столбца) используется функцией VLOOKUP, чтобы указать, какую информацию извлекать из записи.
Из-за того, что аргумент вводится как числовой индекс, он не очень надежен. Если вы вставите в таблицу новый столбец, функция ВПР может перестать работать. На следующем рисунке показан именно такой сценарий.
Столбец «Количество» был третьим по счету, но после добавления нового столбца он стал четвертым. Однако функция ВПР не обновлялась автоматически.
Решение 1
Одним из решений было бы защитить таблицу, чтобы пользователи не могли вставлять новые столбцы. В случае, если пользователи запросят такую возможность, решение будет нежизнеспособным.
Решение 2
Другой вариант — вставить функцию ПОИСКПОЗ в аргумент col_index_num (column_number) функции ВПР.
Функцию ПОИСКПОЗ можно использовать для поиска и возврата необходимого номера столбца. Это сделает аргумент col_index_num (column_number) динамическим, то есть можно будет вставлять новые столбцы в таблицу, не затрагивая функцию VLOOKUP.
Приведенная ниже формула может использоваться в этом примере для решения описанной выше проблемы.
= ВПР (I3; B3: G11; ПОИСКПОЗ (J2; B2: G2,0); ЛОЖЬ)
= ВПР (I3; B3: G11; ПОИСКПОЗ (J2; B2: G2; 0); ЛОЖЬ)
Таблица стала больше
Когда в таблицу добавляются новые строки, может потребоваться настроить функцию ВПР, чтобы гарантировать, что новые строки в таблице охватываются формулой. На следующем рисунке показана ситуация, когда функция ВПР не просматривает всю таблицу в поисках желаемого фрукта.
Решение
Отформатируйте диапазон ячеек как таблицу (Excel 2007+) или как именованный диапазон. Это гарантирует, что функция ВПР всегда будет обрабатывать всю таблицу.
Чтобы отформатировать диапазон как таблицу, выберите диапазон ячеек, который вы хотите использовать для аргумента table_array. На ленте меню щелкните Главная> Форматировать как таблицу и выберите стиль из галереи. Щелкните вкладку «Работа с таблицами»> «Конструктор» и измените имя таблицы на соответствующее поле.
Формула на следующем рисунке использует имя таблицы FruitList.
ВПР не может смотреть влево
У функции ВПР есть одно такое ограничение: она не может извлекать информацию из столбцов слева от первого. Находит желаемое значение в крайнем левом столбце указанной таблицы и возвращает информацию справа.
Решение
Решение этой проблемы — вообще не использовать ВПР. Он использует комбинацию функций ИНДЕКС и ПОИСКПОЗ, которая стала привычной альтернативой ВПР. Это гораздо более гибкое решение
Следующий пример был использован для извлечения информации из столбца слева от искомого:
= ИНДЕКС (B3: B13; СООТВЕТСТВИЕ (H3; C3: C13,0))
= ИНДЕКС (B3: B13; ПОИСК (H3; C3: C13,0))
Данные в таблице дублируются
ВПР может получить только одну запись. Он вернет первую найденную запись, которая соответствует введенному поисковому запросу.
Если таблица содержит повторяющиеся значения, функция ВПР не выполнит свою работу правильно.
Решение 1
Вам нужны повторяющиеся данные в списке? Если нет, удалите их. Это можно сделать быстро с помощью кнопки «Удалить дубликаты» на вкладке «Данные).
Решение 2
Вы решили сохранить дубликаты? OK! В этом случае функция ВПР не нужна. Для таких случаев идеально подходит сводная таблица, позволяющая выбрать значение и увидеть результаты.
В таблице ниже представлен список заказов. Допустим, вы хотите найти все заказы на определенный фрукт.
Сводная таблица позволяет выбрать значение из столбца идентификатора в фильтре, которое соответствует конкретному фрукту, и получить список всех связанных заказов. В нашем примере ID установлен на 23 (бананы).
