ВПР (VLOOKUP) с интервальным просмотром

Допустим, имеется магазин. Магазин дает скидки на оптовые закупки, причем процент скидки зависит от количества купленного товара. Например, при покупке от 5 до 20 штук дается скидка 2%, при покупке от 20 до 50 штук - 6% и при закупке партии от 50 и более штук - 10%. Как быстро и красиво вычислить процент скидки при вводе количества купленного товара?

Можно было бы пойти классическим путем и использовать вложенные функции ЕСЛИ для проверки попадания значения ячейки в заданный диапазон, но это, во-первых, будет очень громоздкая формула, а, во-вторых, поскольку нельзя вкладывать функции ЕСЛИ друг в друга больше 7 раз (в новых версиях Excel - 64 раза), то и проверять мы можем максимум 7 (64) условий. А если их больше?

Есть другой способ. Простой и красивый.

Нам потребуется таблица скидок следующего вида (диаграмму строить не нужно - она здесь для наглядности):

Для расчета процента скидки используем следующую формулу:

где

• ВПР -  функция, которая проверяет значение первого столбца таблицы скидок и ищет в нем значение максимально похожее на количество купленного товара (С1)
• B6:C9 - ссылка на таблицу скидок (без "шапки")
• 2 - порядковый номер столбца в таблице скидок, из которого мы хотим получить значение скидки
• ИСТИНА - здесь и зарыта "собака". Если в качестве последнего аргумента функции ВПР указать ЛОЖЬ или 0, то функция будет искать строгое совпадение в столбце количества (и в случае на рисунке выдаст ошибку, поскольку значения 22 в таблице скидок нет). А вот если вместо ЛОЖЬ написать ИСТИНА или 1, то функция будет искать не точное, а ближайшее наименьшее значение и выдаст процент скидки для него. Что и требуется!

Ссылки по теме

• Использование функции ВПР (VLOOKUP) для поиска и подстановки данных
• Использование функции ВПР (VLOOKUP) с учетом регистра
• Двумерный ВПР (VLOOKUP)





vikttur

25.01.2013 16:47:55

Колонка B6:B9 должна быть отсортирована от А до Я8)

Ссылка

Николай Павлов

29.01.2013 01:21:16

Само-собой - как в примере.

Родитель Ссылка

Елена Клокова

30.01.2013 21:33:54

Спасибо огромное за великолепный сайт!!! Только Вашими советами и спасаюсь.  

Ссылка

Дмитрий Сакович

28.07.2013 03:39:10

D'accordo! sicuramente! Абсолютно согласен с Вами Элена

Родитель Ссылка

Алексей Беляев

21.02.2013 14:11:18

Подскажите Плиз. Уже голову сломал.
ВПР не находит числовое значение в массиве выдает Н/Д
Спасибо.

Ссылка

Николай Павлов

21.02.2013 23:00:27

ВПР может выдавать НД по разным причинам. Посмотрите примечания тут - http://planetaexcel.ru/techniques/2/106/

Родитель Ссылка

Ирина

24.03.2013 12:40:23

Подскажите пожалуйста, если в таблице скидок будут стоять суммы 0-0%;2000-3%;10000-5%;15000-10%, как сделать так чтобы ВПР расчет делала скидок не со всей суммы а как бы начинала новый счет после каждой скидки, но уже по новым % . ПРИМЕР: 2000-4999=3%, а 5000-9999=5% , 10000-15000=10%. Пробовала как показано в примере, но когда подошла сумма к 5 %, то и начисляла формула со всей суммы 5 %. Заранее спасибо.

Ссылка

Николай Павлов

04.08.2013 16:40:08

Нужна другая формула. Не видя вашего файла - не смогу ответить точно. Лучше создайте тему на форуме и приложите файлик.

Родитель Ссылка

Дмитрий Усенко

12.10.2013 22:59:19

Спасибо, доступно объяснили.

Ссылка

Павел Файвусович

11.03.2014 12:45:01

Добрый день! Очень хороший сайт, спасибо Вам за то что Вы делаете. С данным примером также хорошо справится функция =ПРОСМОТР(C1;B6:B9;C6:C9)

Ссылка

Max Babayeva

28.11.2014 08:55:55

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

Ссылка

Светлана Макарова

25.02.2015 17:07:08

Ах, вот для чего нужно не точное совпадение;)

Ссылка

Kuralai Rabatova

11.12.2015 10:52:45

Добрый день, хочу поблагодарить за Ваш сайт! Спасибо!
подскажите, пожалуйста, если имеется список диапазонов, при этом следующий диапазон как может быть так и не может быть продолжением предыдущего, как его уменьшить, объединив идущие по порядку диапазоны в один?

Ссылка

Kuralai Rabatova

11.12.2015 10:58:21

Beg. Serial Number End. Serial Number 465215600 465235599 465235600 465241599 465245600 465265599 465265600 465271599 465271600 465275599 465275600 465295599 465295600 465300599 465301600 465305599 465305600 465315599 465315600 465335599 465335600 465349599 465349600 465350599 465352600 465354599 465354600 465355599 465355600 465360599 465360600 465365599 465365600 465375599 465375600 465385599 465386600 465388599 465388600 465389599 465389600 465395599 465395600 465405599 465405600 465415599 465415600 465435599 465435600 465445599 465445600 465447599 465447600 465452599 465452600 465452699 465453500 465453539 465453544 465453551

Родитель Ссылка

АлекСер

18.08.2016 11:17:02

Добрый день!
А как быть в случае, когда нижний диапазон не имеет границы, то есть меньше 0.
Тогда, наверное, без ЕСЛИ не обойтись (Если меньше 0, то значение, Иначе ВПР(...))

Ссылка