VLOOKUP – одна из самых известных функций Excel. Обычно вы будете использовать его для поиска точных совпадений, таких как идентификатор продуктов или клиентов, но в этой статье мы рассмотрим, как использовать VLOOKUP с диапазоном значений.
Пример первый: использование VLOOKUP для присвоения буквенных баллов баллам экзамена
В качестве примера, скажем, у нас есть список результатов экзамена, и мы хотим назначить оценку каждому баллу. В нашей таблице столбец A показывает фактические результаты экзаменов, а столбец B будет использоваться для отображения рассчитанных буквенных оценок. Мы также создали таблицу справа (столбцы D и E), в которой показаны баллы, необходимые для достижения каждой буквенной оценки.
С помощью VLOOKUP мы можем использовать значения диапазона в столбце D, чтобы присвоить буквенные оценки в столбце E всем фактическим оценкам экзамена.
Формула ВЛУКУП
Прежде чем мы перейдем к применению формулы в нашем примере, давайте кратко напомним о синтаксисе VLOOKUP:
= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
В этой формуле переменные работают так:
- lookup_value: это значение, которое вы ищете. Для нас это оценка в столбце A, начиная с ячейки A2.
- table_array: это часто называют неофициальной таблицей поиска. Для нас это таблица, содержащая оценки и соответствующие оценки (диапазон D2: E7).
- col_index_num: это номер столбца, в который будут помещены результаты. В нашем примере это столбец B, но поскольку команде VLOOKUP требуется число, это столбец 2.
- range_lookup> Это вопрос логического значения, поэтому ответ либо истинный, либо ложный. Вы выполняете поиск диапазона? Для нас ответ – да (или «ИСТИНА» в терминах VLOOKUP).
Заполненная формула для нашего примера показана ниже:
= ВПР (А2, $ D $ 2: $ E $ 7,2, TRUE)
Массив таблицы был исправлен, чтобы остановить его изменение при копировании формулы по ячейкам столбца B.
Что-то, что нужно быть осторожным
При просмотре диапазонов с помощью VLOOKUP важно, чтобы первый столбец массива таблицы (столбец D в этом сценарии) сортировался в порядке возрастания. Формула опирается на этот порядок, чтобы поместить искомое значение в правильный диапазон.
Ниже приведено изображение результатов, которые мы получили бы, если бы отсортировали массив таблиц по буквам оценок, а не по баллам.
Важно понимать, что порядок важен только при поиске по диапазону. Когда вы помещаете False в конец функции VLOOKUP, порядок не так важен.
Пример второй: предоставление скидки в зависимости от того, сколько тратит клиент
В этом примере у нас есть некоторые данные о продажах. Мы хотели бы предоставить скидку от суммы продаж, и процент этой скидки зависит от потраченной суммы.
Таблица поиска (столбцы D и E) содержит скидки в каждой группе расходов.
Приведенную ниже формулу VLOOKUP можно использовать для возврата правильной скидки из таблицы.
= ВПР (А2, $ D $ 2: $ E $ 7,2, TRUE)
Этот пример интересен тем, что мы можем использовать его в формуле для вычитания скидки.
Вы часто будете видеть, как пользователи Excel пишут сложные формулы для этого типа условной логики, но этот VLOOKUP предоставляет краткий способ достижения этого.
Ниже VLOOKUP добавляется в формулу, чтобы вычесть скидку, возвращенную из суммы продаж в столбце A.
= А2-А2 * ВПР (А2, $ D $ 2: $ E $ 7,2, TRUE)
VLOOKUP полезен не только для поиска конкретных записей, таких как сотрудники и продукты. Он более универсален, чем многие знают, и тому, что он возвращается из диапазона значений, является примером этого. Вы также можете использовать его как альтернативу сложным формулам.
