Функция ВПР в Excel ищет значение в диапазоне ячеек, а затем возвращает значение, которое находится в той же строке, что и значение, которое вы ищете.
ВПР, что означает «Вертикальный поиск», представляет собой функцию поиска, которая ищет значение в крайнем левом столбце (первом столбце) диапазона и возвращает параллельное значение из столбца справа от него. Функция ВПР только просматривает значение вверх (сверху вниз) в таблице, расположенной вертикально.
Например, предположим, что у нас есть инвентарный список на листе с таблицей, в которой указаны названия предметов, дата покупки, количество и цена. Затем мы могли бы использовать ВПР на другом листе, чтобы извлечь количество и цену для определенного имени элемента из рабочего листа инвентаризации.
Функция ВПР поначалу может показаться устрашающей, но на самом деле ее довольно легко использовать, если вы понимаете, как она работает. В этом руководстве мы покажем вам, как использовать функцию ВПР в Excel.
Синтаксис и аргументы ВПР
Если вы собираетесь использовать функцию ВПР, вам необходимо знать ее синтаксис и ее аргументы.
Синтаксис функции ВПР:
= ВПР (значение_просмотра, массив_таблиц, номер_столбца, [поиск_диапазона])Эта функция состоит из 4 параметров или аргументов:
- lookup_value: Это указывает значение, которое вы ищете в первом столбце данного массива таблиц. Значение подстановки всегда должно быть в крайнем левом (столбце таблицы поиска.
- table_array: Это таблица (диапазон ячеек), в которой вы хотите найти значение. Эта таблица (таблица поиска) может находиться на том же или другом листе или даже в другой книге.
- col_index_num: Это указывает номер столбца массива таблиц, который имеет значение, которое вы хотите извлечь.
- [range_lookup]: Этот параметр указывает, хотите ли вы извлечь точное или приблизительное совпадение. Это ИСТИНА или ЛОЖЬ, введите «ЛОЖЬ», если вы хотите получить точное значение, или введите «ИСТИНА», если приблизительное значение вас устраивает.
Использование функции ВПР в Excel
Давайте узнаем, как использовать ВПР в Microsoft Excel.
Базовый пример
Чтобы использовать ВПР, сначала вам нужно создать свою базу данных или таблицу (см. Ниже).
Затем создайте таблицу или диапазон, в котором вы хотите найти, и извлеките значения из таблицы поиска.
Затем выберите ячейку, в которой вы хотите извлечь значение, и введите следующую формулу ВПР. Например, мы хотим найти телефонный номер Ena, затем мы должны ввести значение поиска как B13, A2: E10 как массив таблицы, 5 для номера столбца номера телефона и FALSE, чтобы вернуть точное ценность. Затем нажмите «Enter», чтобы завершить формулу.
= ВПР (B13; A2: E10,5; ЛОЖЬ)Вам не нужно вводить диапазон таблицы вручную, вы можете просто выбрать диапазон или таблицу с помощью мыши для аргумента table_array. И он будет добавлен к аргументу автоматически.
Помните, чтобы это работало, значение поиска должно находиться в самом левом углу нашей таблицы поиска (A2: E10). Кроме того, Lookup_value не обязательно должен находиться в столбце A рабочего листа, это просто должен быть крайний левый столбец диапазона, в котором вы хотите выполнить поиск.
Vlookup выглядит правильно
Функция ВПР может смотреть только вправо от таблицы. Он ищет значение в первом столбце таблицы или диапазона и извлекает совпадающее значение из столбца справа.
Точное соответствие
Функция ВПР в Excel имеет два метода сопоставления: точное и приблизительное. Параметр «range_lookup» в функции ВПР указывает, какого типа вы ищете, точный или приблизительный.
Если вы введете range_lookup как «FALSE» или «0», формула будет искать значение, в точности равное lookup_value (это может быть число, текст или дата).
= ВПР (A9; A2: D5,3; ЛОЖЬ)
Если точное совпадение не найдено в таблице, будет возвращена ошибка # Н / Д. Когда мы попытались найти "Япония" и вернуть соответствующее значение в столбце 4, возникает ошибка # Н / Д, потому что в первом столбце таблицы нет "Япония".
В последний аргумент можно ввести либо число «0», либо «ЛОЖЬ». Оба они означают одно и то же в Excel.
Приблизительное соответствие
Иногда точное совпадение не требуется, достаточно лучшего совпадения. В таких случаях можно использовать режим приблизительного совпадения. Установите последний аргумент функции на «ИСТИНА», чтобы найти приблизительное совпадение. Значение по умолчанию - ИСТИНА. Это означает, что если вы не добавите последний аргумент, функция по умолчанию будет использовать приблизительное соответствие.
= ВПР (B10; A2: B7,2; ИСТИНА)В этом примере нам не нужна точная оценка, чтобы подобрать подходящую. Все, что нам нужно, это чтобы оценки были в этом диапазоне.
Если функция ВПР находит точное совпадение, она вернет это значение. В приведенном выше примере, если формула не может найти искомое значение 89 в первом столбце, она вернет следующее по величине значение (80).
Первый матч
Если крайний левый столбец таблицы содержит дубликаты, ВПР найдет и вернет первое совпадение.
Например, функция ВПР настроена на поиск фамилии по имени «Миа». Поскольку существует 2 записи с именем «Миа», функция возвращает фамилию для первой записи «Бена».
Подстановочный знак
Функция ВПР позволяет найти частичное совпадение по указанному значению с помощью подстановочных знаков. Если вы хотите найти значение, которое содержит искомое значение в любой позиции, добавьте знак амперсанда (&), чтобы присоединить к нашему искомому значению подстановочный знак (*). Используйте знаки «$» для создания абсолютных ссылок на ячейки и добавьте знак подстановки «*» перед или после значения поиска.
В этом примере у нас есть только часть поискового значения (Vin) в ячейке B13. Итак, чтобы выполнить частичное совпадение данных символов, добавьте подстановочный знак «*» после ссылки на ячейку.
= ВПР ($ B $ 13 & "*", $ A $ 2: $ E $ 10,3, FALSE)
Множественные поиски
Функция ВПР позволяет создавать динамический двусторонний поиск с сопоставлением как по строкам, так и по столбцам. В следующем примере VLOOKUP настроен для выполнения поиска на основе имени (Mayra) и города. Синтаксис в B14:
= ВПР (B13; A2: E10; ПОИСКПОЗ (A14; A1: E1,0); 0)
Как выполнить ВПР с другого листа в Excel
Обычно функция ВПР используется для возврата совпадающих значений из отдельного рабочего листа и редко используется с данными на одном и том же листе.
Чтобы выполнить Vlookup из другого листа Excel, но в той же книге, введите имя листа перед table_array с восклицательным знаком (!).
Например, чтобы найти значение ячейки A2 рабочего листа «Продукты» в диапазоне A2: B8 на листе «ItemPrices» и вернуть соответствующее значение из столбца B:
= ВПР (A2; ItemPrices! $ A $ 2: $ C $ 8,2; FALSE)На рисунке ниже показана таблица в таблице «ItemPrices».
Когда мы вводим формулу ВПР в столбец C рабочего листа «Продукты», она извлекает соответствующие данные из рабочего листа «ItemPrices».
Как выполнить ВПР из другой книги в Excel
Вы также можете найти значение в совершенно другой книге. Если вы хотите выполнить ВПР из другой книги, вам нужно заключить имя книги в квадратные скобки, а затем имя листа перед table_array восклицательным знаком (!) (Как показано ниже).
Например, используйте эту формулу для поиска значения ячейки A2 другого рабочего листа на листе с именем «ItemPrices» в книге «Item.xlsx»:
= ВПР (A2; [Item.xls] ItemPrices! $ A $ 2: $ B $ 8,2; FALSE)Сначала откройте обе книги, затем начните вводить формулу в ячейке C2 рабочего листа (рабочий лист продукта), а когда вы дойдете до аргумента table_array, перейдите в основную книгу данных (Item.xlsx) и выберите диапазон таблицы. Таким образом, вам не нужно вводить книгу и имя рабочего листа вручную. Введите остальные аргументы и нажмите клавишу «Enter», чтобы завершить функцию.
Даже если вы закроете книгу, содержащую таблицу поиска, формула ВПР будет продолжать работать, но теперь вы можете увидеть полный путь к закрытой книге, как показано на следующем снимке экрана.
Использование функции ВПР с ленты Excel
Если вы не можете запомнить формулы, вы всегда можете получить доступ к функции ВПР с ленты Excel. Чтобы получить доступ к ВПР, перейдите на вкладку «Формулы» на ленте Excel и щелкните значок «Поиск и ссылка». Затем выберите опцию «ВПР» внизу раскрывающегося списка.
Затем введите аргументы в диалоговом окне «Аргументы функции». Затем нажмите кнопку «ОК».
В этом примере мы искали имя «Шерилл» в таблице, чтобы вернуть его соответствующее состояние в столбце D.
Мы надеемся, что вы узнали из этой статьи, как использовать функцию ВПР в Excel. Если вы хотите узнать больше о том, как использовать Excel, ознакомьтесь с другими нашими статьями, связанными с Excel.