Закрывать
Меню

Как найти данные с помощью ВПР в Excel

Что нужно знать

  • Добавлять lookup_value > table_array > col_index_num > range_lookup и нажмите Входить.
  • Аргумент range_lookup необязателен. Использовать ПРАВДА для близкого матча и ЛОЖНЫЙ на точное совпадение.
  • Ошибки # N / A и #REF возникают из-за отсутствия или неправильных аргументов для lookup_value, table_array или range_lookup.

ВПР в Excel функция, что означает «вертикальный поиск», ищет значение в первом столбце диапазона и возвращает значение в любом другом столбце той же строки. Если вы не можете определить, какая ячейка содержит определенные данные, ВПР - эффективный способ найти эти данные. Это особенно полезно в гигантских таблицах, где сложно найти информацию. Эти инструкции применимы к Excel для Microsoft 365, Excel 2019, 2016, 2013, 2010, Excel для Mac и Excel Online.

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

ВПР обычно возвращает одно поле данных в качестве вывода.

Как это работает:

  1. Вы указываете имя или lookup_value это сообщает VLOOKUP, в какой строке таблицы данных искать нужные данные.
  2. Вы поставляете столбец число как col_index_num аргумент, который сообщает ВПР, какой столбец содержит искомые данные.
  3. Функция ищет lookup_value в первом столбце таблицы данных.
  4. Затем ВПР находит и возвращает информацию из столбца с номером, который вы определили в col_index_numиз той же строки, что и искомое значение.

Аргументы и синтаксис функции ВПР

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

Синтаксис функции ВПР: 

= ВПР (значение_просмотра, массив_таблиц, номер_столбца, поиск_диапазона)

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

Четыре аргумента функции ВПР следующие:

lookup_value (обязательно): Значение для поиска в первом столбце массива таблицы.

table_array (обязательно) - Это таблица данных (диапазон ячеек), в которой функция VLOOKUP ищет нужную информацию.

  • Table_array должен содержать как минимум два столбца данных
  • Первый столбец должен содержать lookup_value

col_index_num (обязательно) - Это номер столбца значения, которое вы хотите найти.

  • Нумерация начинается с столбца 1.
  • Если вы укажете число, превышающее количество столбцов в массиве таблицы, функция вернет # ССЫЛКА! ошибка

range_lookup (необязательно) - Указывает, попадает ли искомое значение в диапазон, содержащийся в массиве таблицы. Аргумент range_lookup имеет значение «ИСТИНА» или «ЛОЖЬ». Используйте TRUE для приблизительного совпадения и FALSE для точного совпадения. Если не указано, по умолчанию используется значение ИСТИНА.

Если аргумент range_lookup имеет значение ИСТИНА, то:

  • Lookup_value - это значение, которое вы хотите проверить, попадает ли оно в диапазон, определенный table_array.
  • Table_array содержит все диапазоны и столбец, содержащий значение диапазона (например, высокий, средний или низкий).
  • Аргумент col_index_num - это результирующее значение диапазона.

Как работает аргумент Range_Lookup

Функция ВПР в Excel

Использование необязательного range_lookup Аргумент сложен для понимания многих людей, поэтому стоит взглянуть на небольшой пример.

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

Пример показывает, что скидка на покупку 19 предметов составляет 2%, потому что 19 находятся между 11 и 21 в Количество столбец таблицы поиска.

В результате функция ВПР возвращает значение из второго столбца таблицы поиска, поскольку эта строка содержит минимум этого диапазона. Другой способ настроить таблицу поиска диапазона - создать второй столбец для максимума, и этот диапазон будет иметь минимум 11 и максимум 20. Но результат работает точно так же.

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

= ВПР (C2; $ C $ 5: $ D $ 8,2; ИСТИНА)

  • C2: Это значение поиска, которое может находиться в любой ячейке электронной таблицы.
  • 5 канадских долларов: 8 канадских долларов: Это фиксированная таблица, содержащая все диапазоны, которые вы хотите использовать.
  • 2: Это столбец в таблице поиска диапазона, который вы хотите вернуть функцией ПРОСМОТР.
  • ПРАВДА: Включает range_lookup особенность этой функции.

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

Аргумент range_lookup - отличный способ разделить столбец со смешанными числами на различные категории.

Ошибки ВПР: # N / A и #REF

Пример файла VLOOKUP #REF! ошибка в Excel.

Функция ВПР может возвращать следующие ошибки.

# N / A является ошибкой «значение недоступно» и возникает при следующих условиях:

  • В поиск _value не найден в первом столбце аргумента table_array
  • В Table_arrayаргумент неточный. Например, аргумент может включать пустые столбцы в левой части диапазона.
  • В Range_lookup для аргумента установлено значение FALSE, и точное совпадение аргумента lookup_value не может быть найдено в первом столбце table_array
  • В range_lookup аргумент установлен в TRUE, и все значения в первом столбце table_array больше, чем lookup_value

# ССЫЛКА! («ссылка вне диапазона») ошибка возникает, если col_index_num больше, чем количество столбцов в table_array.