В больших таблицах Excel VLOOKUP может помочь вам найти любые данные
Функция Excel VLOOKUP, которая обозначает «вертикальный поиск», будет искать значение в первом столбце диапазона и возвращать значение в любом другом столбце в той же строке.
Если вы не можете определить, какая ячейка содержит конкретные данные, VLOOKUP – очень эффективный способ найти эти данные. Это особенно полезно в гигантских электронных таблицах, где трудно найти информацию.
Инструкции в этой статье относятся к Excel 2016, 2013, 2010; Excel для Mac и Excel для 365/Интернет.
Как работает функция 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.
- ИСТИНА . Включает функцию range_lookup этой функции.
После того как вы нажали Ввод и результат вернется в первую ячейку, вы можете автоматически заполнить весь столбец, чтобы просмотреть результаты диапазона для остальных ячеек в столбце поиска.
Аргумент range_lookup – это убедительный способ сортировки столбца смешанных чисел по различным категориям.
Ошибки VLOOKUP: # N/A и #REF
Функция VLOOKUP может возвращать следующие ошибки.
# Н/Д – это ошибка «значение недоступно», которая возникает при следующих условиях:
- lookup _value не найдено в первом столбце аргумента table_array
- Аргумент Table_array является неточным. Например, аргумент может содержать пустые столбцы в левой части диапазона
- Для аргумента Range_lookup установлено значение FALSE, и точное совпадение с аргументом lookup_value невозможно найти в первом столбце table_array .
- Аргумент range_lookup имеет значение TRUE, и все значения в первом столбце table_array больше, чем lookup_value
Ошибка #REF! («ссылка вне диапазона») возникает, если столбец col_index_num превышает число столбцов в таблице.
