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

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

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

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

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

stage1.gif

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

stage2.gif

где

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

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



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)
28.11.2014 08:55:55
Здравствуйте,
Имеется таблица где в  столбцах указаны имена клиентов, даты оплат и суммы до погащения долгов. как можно с этой таблицы по названиям клиентов вывести количесто дней между первой и последней даты оплаты?
25.02.2015 17:07:08
Ах, вот для чего нужно не точное совпадение;)
11.12.2015 10:52:45
Добрый день, хочу поблагодарить за Ваш сайт! Спасибо!
подскажите, пожалуйста, если имеется список диапазонов, при этом следующий диапазон как может быть так и не может быть продолжением предыдущего, как его уменьшить, объединив идущие по порядку диапазоны в один?
11.12.2015 10:58:21
Beg. Serial NumberEnd. Serial Number
465215600465235599
465235600465241599
465245600465265599
465265600465271599
465271600465275599
465275600465295599
465295600465300599
465301600465305599
465305600465315599
465315600465335599
465335600465349599
465349600465350599
465352600465354599
465354600465355599
465355600465360599
465360600465365599
465365600465375599
465375600465385599
465386600465388599
465388600465389599
465389600465395599
465395600465405599
465405600465415599
465415600465435599
465435600465445599
465445600465447599
465447600465452599
465452600465452699
465453500465453539
465453544465453551
18.08.2016 11:17:02
Добрый день!
А как быть в случае, когда нижний диапазон не имеет границы, то есть меньше 0.
Тогда, наверное, без ЕСЛИ не обойтись (Если меньше 0, то значение, Иначе ВПР(...))
Наверх