ВПР в Excel функция , которая выступает за вертикального поиска , может быть использован для поиска информации , специфичной , расположенный в таблице данных или базы данных.
VLOOKUP обычно возвращает одно поле данных в качестве вывода. Как это происходит:
- Вы предоставляете имя или Lookup _value, который сообщает VLOOKUP , в какой строке или записи таблицы данных искать нужную информацию
- Вы указываете номер столбца — известный как Col_index_num — данных, которые вы ищете
- Функция ищет значение Lookup _value в первом столбце таблицы данных
- VLOOKUP затем находит и возвращает информацию, которую вы ищете из другого поля той же записи, используя предоставленный номер столбца
Инструкции в этой статье применяются к Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel для Mac и Excel Online.
Найти информацию в базе данных с VLOOKUP
На изображении, показанном выше, VLOOKUP используется для определения цены за единицу товара по его названию. Имя становится значением поиска, которое VLOOKUP использует для поиска цены, расположенной во втором столбце.
Синтаксис и аргументы функции VLOOKUP
Синтаксис для функции VLOOKUP:
= VLOOKUP (lookup_value, Table_array, Col_index_num, Range_lookup)
Lookup _value — (обязательно) значение, которое вы хотите найти в первом столбце аргумента Table_array .
Table_array — (обязательно) это таблица данных, которую VLOOKUP ищет для поиска нужной вам информации
— Table_array должен содержать как минимум два столбца данных;
— первый столбец обычно содержит значение Lookup_value.
Col_index_num — (обязательно) номер столбца значения, которое вы хотите найти
— нумерация начинается со столбца Lookup_value в качестве столбца 1;
— если Col_Index_Num установлен на число , большее число столбцов , выбранных в диапазон_просмотра аргумента #REF! ошибка возвращается функцией.
Range_lookup — (необязательно) указывает, сортируется ли диапазон в порядке возрастания
— данные в первом столбце используются в качестве ключа сортировки
— логическое значение — TRUE или FALSE являются единственными допустимыми значениями
— если не указано, значение устанавливается по умолчанию TRUE
— если установлено в TRUE или опущено, и точное совпадение для значения _ для поиска не найдено, то в качестве поискового ключа используется ближайшее совпадение, которое меньше по размеру или значению
— если установлено в TRUE или опущено, а первый столбец диапазон не сортируется в порядке возрастания, может возникнуть неправильный результат
— если установлено значение FALSE, VLOOKUP принимает только точное совпадение для Lookup _value .
Сортировка данных в первую очередь
Хотя это не всегда требуется, обычно лучше сначала отсортировать диапазон данных, которые VLOOKUP ищет в порядке возрастания, используя первый столбец диапазона для ключа сортировки .
Если данные не отсортированы, VLOOKUP может вернуть неверный результат.
Точные и приблизительные матчи
VLOOKUP может быть установлен так, чтобы он возвращал только информацию, которая точно соответствует значению Lookup _, или он может быть установлен для возврата приблизительных совпадений .
Определяющим фактором является аргумент Range_lookup :
- при значении FALSE он возвращает только информацию, относящуюся к точным совпадениям, к значению Lookup _value
- установлен в TRUE или опущен, он возвращает точную или приблизительную информацию, относящуюся к Lookup _value
В приведенном выше примере для Range_lookup установлено значение FALSE, поэтому VLOOKUP должна найти точное совпадение для термина Widgets в таблице данных, чтобы вернуть цену за единицу для этого элемента. Если точное совпадение не найдено, функция возвращает ошибку # N / A.
VLOOKUP не чувствителен к регистру — и виджеты, и виджеты являются приемлемым написанием для приведенного выше примера.
Если имеется несколько совпадающих значений — например, виджеты перечислены более одного раза в столбце 1 таблицы данных — функция, связанная с первым найденным совпадающим значением, идущим сверху вниз, возвращается функцией.
Ввод аргументов функции VLOOKUP в Excel с помощью наведения
В первом примере изображения выше, следующая формула, содержащая функцию VLOOKUP, используется для поиска цены за единицу для виджетов, расположенных в таблице данных.
= ВПР (А2, $ $ 5: $ B $ 8,2, ЛОЖЬ)
Несмотря на то, что эту формулу можно просто ввести в ячейку рабочего листа, другой вариант, используемый с шагами, перечисленными ниже, заключается в использовании диалогового окна функции , показанного выше, для ввода ее аргументов.
- Использование диалогового окна часто облегчает правильный ввод аргументов функции и устраняет необходимость ввода запятых между аргументами.
Следующие шаги были использованы для ввода функции VLOOKUP в ячейку B2 с помощью диалогового окна функции.
Открытие диалогового окна VLOOKUP
- Выберите ячейку B2, чтобы сделать ее активной ячейкой — место, где отображаются результаты функции VLOOKUP.
- Выберите вкладку Формулы .
- Выберите Lookup & Reference на ленте, чтобы открыть раскрывающийся список функций
- Выберите VLOOKUP в списке, чтобы вызвать диалоговое окно функции
Данные, введенные в четыре пустые строки диалогового окна, формируют аргументы для функции VLOOKUP.
Указывая на ссылки на ячейки
Аргументы для функции VLOOKUP вводятся в отдельные строки диалогового окна, как показано на рисунке выше.
Ссылки на ячейки, которые будут использоваться в качестве аргументов, могут быть введены в правильную строку или, как показано в приведенных ниже шагах, с помощью указателя и щелчка — что подразумевает выделение нужного диапазона ячеек указателем мыши — могут использоваться для ввода их в диалоговое окно.
Использование относительных и абсолютных ссылок на ячейки с аргументами
Нередко используют несколько копий VLOOKUP для возврата различной информации из одной и той же таблицы данных.
Чтобы сделать это проще, часто VLOOKUP можно копировать из одной ячейки в другую. Когда функции копируются в другие ячейки, необходимо позаботиться о том, чтобы полученные в результате ссылки на ячейки были правильными с учетом нового расположения функции.
На изображении выше знаки доллара ( $ ) окружают ссылки на ячейки для аргумента Table_array, указывая на то, что они являются абсолютными ссылками на ячейки , что означает, что они не изменятся, если функция будет скопирована в другую ячейку.
Это желательно, поскольку все копии VLOOKUP будут ссылаться на одну и ту же таблицу данных в качестве источника информации.
Ссылка на ячейку, используемая для lookup_value — A2, с другой стороны , не окружена знаками доллара, что делает ее относительной ссылкой на ячейку . Относительные ссылки на ячейки изменяются, когда они копируются, чтобы отразить их новое местоположение относительно положения данных, к которым они относятся.
Относительные ссылки на ячейки позволяют искать несколько элементов в одной и той же таблице данных, копируя VLOOKUP в несколько мест и вводя разные lookup_values .
Ввод аргументов функции
- Выберите _value Уточняющий строку в ВПР диалоговом окне
- Выберите ячейку A2 на листе, чтобы ввести эту ссылку на ячейку в качестве аргумента search_key
- Выберите строку Table_array диалогового окна
- Выделите ячейки от A5 до B8 на листе, чтобы ввести этот диапазон в качестве аргумента Table_array — заголовки таблицы не включены
- Нажмите клавишу F4 на клавиатуре, чтобы изменить диапазон на абсолютные ссылки на ячейки.
- Выберите строку Col_index_num диалогового окна
- Введите 2 в этой строке в качестве аргумента Col_index_num , поскольку ставки дисконтирования находятся в столбце 2 аргумента Table_array.
- Выберите строку Range_lookup диалогового окна
- Введите слово False в качестве аргумента Range_lookup
- Нажмите клавишу ввода на клавиатуре, чтобы закрыть диалоговое окно и вернуться к рабочему листу.
Ответ 14.76 $ — цена за единицу для виджета — должен появиться в ячейке B2 рабочего листа.
Если щелкнуть ячейку B2, полная функция = VLOOKUP (A2, $ A $ 5: $ B $ 8,2, FALSE) появится на панели формул над рабочим листом.
Сообщения об ошибках Excel VLOOKUP
Следующие сообщения об ошибках связаны с VLOOKUP:
Ошибка # N / A («значение не доступно») отображается, если:
- Значение _ для поиска не найдено в первом столбце аргумента диапазона
- Таблица_массив аргумент является неточным. Например, аргумент может содержать пустые столбцы в левой части диапазона
- Для аргумента Range_lookup установлено значение FALSE, и точное совпадение аргумента search_key невозможно найти в первом столбце диапазона
- Интервальный_просмотр аргумент установлен в значение TRUE , и все значения в первом столбце диапазона больше , чем SEARCH_KEY
#REF! Ошибка отображается, если:
- Номер_столбца аргумент больше , чем число столбцов в массиве таблиц.