Приблизительное совпадение с помощью функции ВПР (VLOOKUP)
Чтобы находить приблизительные совпадения в Excel — например, для расчёта почтовых тарифов по весу, оценки результатов сотрудников или расчёта подоходного налога в зависимости от уровня зарплаты и т.д., — используйте функцию ВПР (VLOOKUP), которая вернёт нужное приблизительное значение.
Как найти приблизительные совпадения с помощью функции ВПР (VLOOKUP)?
Чтобы выставить оценку за балл студента согласно шкале из таблицы (см. скриншот выше), воспользуйтесь функцией ВПР (VLOOKUP). По умолчанию эта функция выполняет приблизительный поиск, поэтому отсутствие аргумента range_lookup или его значение TRUE дают один и тот же результат. Важно: для корректного поиска приблизительных совпадений с помощью ВПР (VLOOKUP) значения в первом столбце таблицы (там, где указаны искомые значения) должны быть отсортированы по возрастанию.
Общий синтаксис
=VLOOKUP(lookup_value,table_array,col_num,range_lookup)
- lookup_value: Значение, которое нужно найти. В данном случае оно соответствует указанному баллу.
- table_array: Диапазон ячеек, в котором выполняется поиск приблизительных совпадений. Он включает все данные — как баллы, так и информацию об оценках.
- col_num: Номер столбца в диапазоне table_array, из которого нужно получить соответствующее значение.
- range_lookup: TRUE или опущен. Указывает функции ВПР (VLOOKUP) искать первое приблизительное совпадение.
Чтобы выставить оценку за балл 82, скопируйте или введите приведённую ниже формулу в ячейку F6 и нажмите клавишу Enter, чтобы получить результат:
=ВПР()82,B5:C9,2)
Или используйте Ссылка на ячейку, чтобы сделать формулу динамической:
=ВПР()F5,B5:C9,2)
Пояснение формулы
=VLOOKUP(F5,B5:C9,2)
- Аргумент range_lookup отсутствует, что указывает на приблизительное совпадение и требует, чтобы значения в первом столбце таблицы (столбец B, где перечислены искомые значения) были отсортированы по возрастанию.
- Функция ВПР (VLOOKUP) найдёт наибольшее значение, которое меньше или равно 82, то есть значение в ячейке F5, в диапазоне B5:C9. Таким образом, она сопоставит значение 80 и выполнит поиск в 4-й строке диапазона.
- Затем функция ВПР (VLOOKUP) вернёт значение из 2-го столбца 4-й строки, то есть B.
Связанные функции
Функция ВПР (VLOOKUP) в Excel ищет значение, сопоставляя его с первым столбцом таблицы, и возвращает соответствующее значение из указанного столбца той же строки.
Связанные формулы
Приблизительное совпадение с помощью функций ИНДЕКС и ПОИСКПОЗ
Иногда в Excel необходимо находить приблизительные совпадения — например, чтобы оценить результаты работы сотрудников, выставить студентам оценки на основе их баллов или рассчитать стоимость почтовых отправлений по весу. В этом руководстве мы покажем, как с помощью функций ИНДЕКС и ПОИСКПОЗ получить именно те результаты, которые вам нужны.
Точное совпадение с помощью функции ВПР (VLOOKUP)
Чтобы быстро найти в Excel информацию о конкретном товаре, фильме, человеке и т.д., используйте функцию ВПР (VLOOKUP).
Частичное совпадение с помощью функции ВПР (VLOOKUP)
Иногда Excel нужно найти данные на основе лишь частичной информации. С этой задачей отлично справляется формула ВПР (VLOOKUP) в сочетании с подстановочными знаками — звёздочкой (*) и вопросительным знаком (?).
Поиск влево с помощью функции ВПР (VLOOKUP)
В этом руководстве мы покажем, как использовать функцию ВПР (VLOOKUP) для поиска информации, расположенной в самом левом столбце Excel, о заданном элементе, находящемся справа. Поскольку функция ВПР (VLOOKUP) не умеет искать влево, для решения этой задачи понадобится поддержка функции ВЫБОР (CHOOSE).
Лучшие инструменты для повышения продуктивности в Office
Kutools для Excel — Помогает вам выделиться из толпы
Kutools для Excel предлагает более 300 функций,гарантируя, что всё необходимое всегда под рукой…
Office Tab — Включает вкладки для чтения и редактирования в Microsoft Office (включая Excel)
- Переключайтесь между десятками открытых документов всего за секунду!
- Сократите сотни кликов мышью каждый день и забудьте о «мышечной руке».
- Повышает продуктивность на 50 % при одновременном просмотре и редактировании нескольких документов.
- Добавляет удобство работы с вкладками в Office (включая Excel) — как в Chrome, Edge и Firefox.