VLOOKUP в Excel: использование диапазонов для оценивания и расчёта скидок

Кратко

VLOOKUP удобно использовать не только для точного поиска, но и для поиска по диапазонам — например, чтобы автоматически присваивать буквенные оценки по набранным баллам или рассчитывать процент скидки по сумме покупки. В статье есть два практических примера, советы по сортировке таблицы поиска и альтернативы, если VLOOKUP не подходит.

Быстрые ссылки

• Пример 1: Использование VLOOKUP для присвоения буквенных оценок по баллам
• Пример 2: Применение скидки в зависимости от суммы покупки

VLOOKUP — одна из самых известных функций Excel. Обычно её используют для поиска точного совпадения, например ID товара или клиента. В этой статье мы покажем, как применять VLOOKUP к диапазонам значений, чтобы возвращать результат по интервалу, а не по точному совпадению.

Пример 1: присвоение буквенных оценок по результатам экзамена

Предположим, у нас есть список баллов экзаменов в столбце A, а в столбце B мы хотим показать соответствующую буквенную оценку. Справа (в столбцах D и E) находится таблица, где указан минимальный балл для каждой буквенной оценки и сама оценка.

С помощью VLOOKUP мы используем значения в столбце D как границы диапазонов и возвращаем буквы из столбца E для всех фактических баллов.

Синтаксис VLOOKUP

Напомним синтаксис VLOOKUP:

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Кратко о параметрах:

• lookup_value — искомое значение. В нашем случае это балл в столбце A, например A2.
• table_array — таблица поиска; здесь диапазон D2:E7.
• col_index_num — номер столбца внутри table_array, который содержит возвращаемое значение. В нашем случае это 2 (столбец E).
• range_lookup — логическое значение: хотите ли вы поиск по диапазону (TRUE) или точный поиск (FALSE). Для этого сценария используйте TRUE (в русской версии Excel ИСТИНА).

Готовая формула для первой строки выглядит так:

=VLOOKUP(A2,$D$2:$E$7,2,TRUE)

Обратите внимание: в формуле мы зафиксировали диапазон таблицы поиска с помощью абсолютных ссылок ($D$2:$E$7), чтобы при копировании формулы вниз диапазон не смещался.

Важный момент: сортировка таблицы поиска

При поиске по диапазону (range_lookup = TRUE) очень важно, чтобы первый столбец table_array был отсортирован по возрастанию. VLOOKUP полагается на эту упорядоченность, чтобы корректно определить, в какой интервал попадает lookup_value.

На рисунке ниже показан пример неправильных результатов, когда таблица поиска отсортирована по буквам оценок, а не по минимальным баллам.

Важно: требование сортировки относится только к варианту с range_lookup = TRUE. При точном поиске (range_lookup = FALSE) порядок не влияет.

Пример 2: скидка по сумме покупки

Во втором примере у нас есть данные по продажам. Мы хотим предоставлять скидку в зависимости от суммы, потраченной клиентом. В таблице справа (D и E) указаны минимальные суммы и соответствующий процент скидки.

Формула, возвращающая процент скидки, та же, что и в первом примере:

=VLOOKUP(A2,$D$2:$E$7,2,TRUE)

Её можно вложить в арифметическое выражение, чтобы вычислить итоговую цену после скидки:

=A2-A2*VLOOKUP(A2,$D$2:$E$7,2,TRUE)

Этот подход часто проще и читаемее, чем длинные условные формулы через IF/IFS; VLOOKUP даёт компактное решение для логики «если сумма ≥ X, то скидка Y».

Когда VLOOKUP не подходит (и что выбрать вместо него)

• Нужно искать значение влево от столбца-ключа. VLOOKUP всегда ищет в первом столбце table_array и возвращает значение справа — используйте INDEX+MATCH или XLOOKUP.
• Нужен поиск с более гибкими условиями (несколько критериев). Рассмотрите INDEX+MATCH с массивными критериями, SUMIFS или таблицу с вычислениями.
• Предпочитаете более понятный синтаксис и возможность искать в любом направлении — XLOOKUP (в новых версиях Excel) заменяет VLOOKUP и поддерживает поиск по диапазону и точный поиск с более гибкой работой с отсутствующими значениями.

Короткое сравнение:

• VLOOKUP: простой синтаксис, ограничение на направление поиска, требует сортировки при range_lookup=TRUE.
• INDEX+MATCH: более гибкий, работает для «поиск влево». Немного длиннее по синтаксису.
• XLOOKUP: современная замена (если доступна), позволяет искать в любом направлении и задавать значение по умолчанию.

Практическая методология: как правильно использовать VLOOKUP по диапазону

1. Подготовьте таблицу поиска: первый столбец — минимальные значения интервалов (например 0, 60, 70, 80 и т.д.). Второй столбец — результат для интервала.
2. Сортируйте первый столбец по возрастанию.
3. Используйте абсолютные ссылки на table_array ($D$2:$E$7), чтобы зафиксировать диапазон при копировании.
4. Укажите range_lookup = TRUE для поиска по диапазону; при необходимости используйте FALSE для точного сопоставления.
5. Проверьте несколько граничных значений: меньше минимального, равное границе, чуть больше границы.

Чек-лист перед применением формулы

• Таблица поиска содержит минимальные значения интервалов в первом столбце.
• Первый столбец таблицы поиска отсортирован по возрастанию.
• Абсолютные ссылки ($) установлены для table_array.
• В проверочных ячейках совпадают ожидаемые результаты для границ интервалов.

Роль-ориентированные подсказки:

• Аналитик: используйте тестовые строки с отрицательными и крайними значениями, чтобы проверить поведение формулы.
• Бухгалтер: проверьте правильность округления итоговых сумм после применения скидки.
• Разработчик отчётов: документируйте, какие столбцы используются как ключи и почему применён поиск по диапазону.

Критерии приёмки

• Для заданных тестовых значений формула возвращает ожидаемый результат для каждого интервала.
• При добавлении новой границы в таблицу поиска формула корректно работает без изменения всех ссылок (диапазон при необходимости расширяется и фиксируется).
• Нет случайных сдвигов диапазона при копировании формулы (использованы абсолютные ссылки).

Частые ошибки и как их исправлять

• Неправильный порядок таблицы поиска: решается сортировкой по первому столбцу.
• Забыт знак $ в table_array: приводит к сдвигу диапазона при копировании — замените на абсолютные ссылки.
• Ожидание точного совпадения при TRUE: при TRUE VLOOKUP ищет ближайшее меньшее или равное значение — используйте FALSE для точного поиска.

Быстрые подсказки и эвристики

• Если результат иногда #N/A при очевидном совпадении — проверьте типы данных (число vs текст).
• Для процентных скидок храните процент в виде десятичной дроби (например 0.1 = 10%), чтобы формула умножения работала корректно.
• При больших таблицах поиска отдавайте предпочтение INDEX+MATCH по производительности и гибкости.

Глоссарий (одно предложение на термин)

• VLOOKUP: функция Excel для вертикального поиска значения в таблице и возврата связанного результата.
• table_array: диапазон, где Excel ищет значение и откуда берёт возвращаемое значение.
• range_lookup: флаг TRUE/ FALSE, определяющий поиск по диапазону или точное совпадение.

Краткий итог

VLOOKUP — простой и мощный инструмент для поиска по диапазонам при условии правильно организованной таблицы поиска. Он отлично подходит для задач вроде присвоения оценок по интервалам и расчёта скидок по сумме покупки. Если нужна дополнительная гибкость (поиск влево, несколько критериев, более дружелюбный синтаксис), рассмотрите INDEX+MATCH или XLOOKUP.

Важно

Используйте тестовые значения по краям интервалов и документируйте логику таблицы поиска, чтобы избежать неожиданных результатов при изменении данных.