ВПР в Excel функция , которая выступает за «вертикальный поиск» будет искать значение в первом столбце диапазона, и возвращает значение в любом другом столбце в той же строке.
Если вы не можете определить, какая ячейка содержит определенные данные, VLOOKUP — очень эффективный способ найти эти данные. Это особенно полезно в гигантских электронных таблицах, где трудно найти информацию.
Инструкции в этой статье относятся к Excel для Office 365, Excel 2019, 2016, 2013, 2010, Excel для Mac и Excel Online.
Как работает функция VLOOKUP
VLOOKUP обычно возвращает одно поле данных в качестве вывода.
Как это работает:
- Вы предоставляете имя или значение lookup_value, которое сообщает VLOOKUP , в какой строке таблицы данных искать нужные данные.
- Номер столбца указывается в качестве аргумента col_index_num , который сообщает VLOOKUP, в каком столбце содержатся искомые данные.
- Функция ищет значение lookup_value в первом столбце таблицы данных.
- Затем VLOOKUP находит и возвращает информацию из номера столбца, определенного вами в col_index_num , из той же строки, что и значение поиска.
VLOOKUP Функциональные аргументы и синтаксис
Синтаксис для функции VLOOKUP:
= ВПР (искомое_значение, таблица_массив, номер_столбец, интервальный_просмотр)
Функция VLOOKUP может показаться запутанной, поскольку содержит четыре аргумента, но ее легко использовать.
Вот четыре аргумента для функции VLOOKUP:
lookup_value (обязательно) : значение для поиска в первом столбце массива таблицы.
table_array (обязательно) — это таблица данных (диапазон ячеек), которую VLOOKUP ищет, чтобы найти нужную вам информацию.
- Таблица_array должна содержать как минимум два столбца данных
- Первый столбец должен содержать lookup_value
col_index_num (обязательно) — это номер столбца значения, которое вы хотите найти.
- Нумерация начинается со столбца 1
- Если вы ссылаетесь на число, превышающее количество столбцов в массиве таблицы, функция вернет #REF! ошибка
range_lookup (необязательно) — указывает, попадает ли значение поиска в диапазон, содержащийся в массиве таблицы. Аргумент range_lookup имеет значение «ИСТИНА» или «ЛОЖЬ». Используйте TRUE для приблизительного совпадения и FALSE для точного совпадения. Если опущено, значение TRUE по умолчанию.
Если аргумент range_lookup равен TRUE, то:
- Lookup_value — это значение, которое вы хотите проверить, попадает ли оно в диапазон, определенный table_array.
- Аргумент table_array содержит все диапазоны и столбец, содержащий значение диапазона (например, высокий, средний или низкий).
- Аргумент col_index_num является результирующим значением диапазона.
Как работает аргумент Range_Lookup
Использование необязательного аргумента range_lookup сложно понять многим, поэтому стоит взглянуть на быстрый пример.
Пример на изображении выше использует функцию VLOOKUP, чтобы найти ставку дисконтирования в зависимости от количества приобретенных товаров.
В примере показано, что скидка на покупку 19 товаров составляет 2%, поскольку 19 находится между 11 и 21 в столбце « Количество» справочной таблицы.
В результате VLOOKUP возвращает значение из второго столбца таблицы поиска, поскольку в этой строке содержится минимум этого диапазона. Другой способ настроить таблицу поиска диапазона — это создать второй столбец для максимума, и этот диапазон будет иметь минимум 11 и максимум 20. Но результат работает так же.
В примере используется следующая формула, содержащая функцию VLOOKUP, чтобы найти скидку на количество приобретенных товаров.
= ВПР (С2, $ C $ 5: $ D $ 8,2, TRUE),
- C2 : это значение поиска, которое может находиться в любой ячейке электронной таблицы.
- $ C $ 5: $ D $ 8 : это фиксированная таблица, содержащая все диапазоны, которые вы хотите использовать.
- 2 : Это столбец в таблице поиска диапазона, который вы хотите вернуть функции LOOKUP.
- TRUE : включает функцию range_lookup этой функции.
После того как вы нажали Enter и результат вернулся в первую ячейку, вы можете автоматически заполнить весь столбец, чтобы просмотреть результаты диапазона для остальных ячеек в столбце поиска.
Аргумент range_lookup — это убедительный способ сортировки столбца смешанных чисел по различным категориям.
Ошибки VLOOKUP: # N / A и #REF
Функция VLOOKUP может возвращать следующие ошибки.
# N / A является ошибкой «значение недоступно» и возникает при следующих условиях:
- Значение _upue поиска не найдено в первом столбце аргумента table_array
- Таблица_массив аргумент является неточным. Например, аргумент может содержать пустые столбцы в левой части диапазона
- Диапазон_просмотра аргумент установлен в FALSE, а точное соответствие для Lookup_Value аргумента не может быть найдено в первом столбце table_array
- Интервальный_просмотр аргумент установлен в значение TRUE, а все значения в первом столбце table_array больше , чем lookup_value
#REF! («ссылка вне диапазона») ошибка возникает, если col_index_num больше, чем число столбцов в table_array.