Функция ВПР как замена нескольких условий функции ЕСЛИ

Допустим нам надо применить прогрессивную скидку в зависимости от суммы заказа.

Можно было бы применить несколько условий вложенных функций ЕСЛИ, но рассмотрим другой пример, в котором формула будет намного короче.

Для начала занесем наши условия в отдельную таблицу (для более наглядного построения формулы сделаем эту таблицу на том же листе, что и таблицу с заказами, но можно так же расположить ее на отдельном листе).

Обязательно нужно прописать первую строчку, иначе наша формула с заказами меньше 15000 руб будет выдавать ошибку.

Условия для ВПР

Для построения формулы встанем в нужную ячейку и нажмем на кнопочку Вставить функцию (слева от строки формул). В появившемся окне в поле Категория выбрать Ссылки и массивы. В поле Выберете функцию выбрать ВПР.

Где найти функцию ВПР

Появится окно с аргументами функции ВПР.


Искомое значение: Сумма заказа по которой будет определятся скидка. (В2)

Таблица: для правильного отображения формулы должно соблюдаться несколько условий.

  1. Таблица должна быть отсортирована по возрастанию.
  2. По 1-му столбцу таблицы будет определяться искомое значение.
  3. Для того, чтобы протягивать формулы на следующие заказы, диапазон таблицы не должен изменяться, поэтому для этого поля делаем ссылки абсолютными.
    Для этого мышкой выделяем диапазон таблицы (F2:G8) и начинаем нажимать клавишу F4, пока диапазон не изменится на $F$2:$G$8. Если у вас ноутбук, нужно одновременно нажать на клавиши Fn+F4. Знак доллара можно ввести и вручную.

Номер столбца: Столбец из которого необходимо вернуть значения. Отсчет будет вестись, начиная с первого столбца, по которому будет определяться искомое значение.


Интервальный просмотр: Логическое значение по которому будет определяться точно(ЛОЖЬ или 0) или приблизительно (ИСТИНА или 1) должен производится поиск в первом столбце. Если этот аргумент отсутствует, Excel будет определять приблизительные значения.


В нашем случае нужны приблизительные значения, так как первый столбец указывает не точную сумму, а диапазон (от 15 000 до 20 000, от 20 000 до 25 000 и т.д.). Поэтому это поле можно не заполнять или написать ИСТИНА или 1.

Построение функции ВПР

Далее протягиваем формулу для следующих заказов.

Протягивание функции ВПР

Мы вывели скидку в отдельную колонку. Также можно функцию ВПР сразу использовать в формуле и выводить уже готовый результат.


Для этого в ячейке пропишем


=B2*(100-ВПР(B2;$G$2:$H$8;2))/100

ВПР в составе формулы

Добавить комментарий