Поиск ближайшего числа

На практике весьма часто возникают случаи, когда нам с вами нужно найти ближайшее значение в наборе (таблице) по отношению к заданному числу. Это может быть, например:

  • Расчет скидки в зависимости от объема.
  • Вычисление размера бонусов в зависимости от выполнения плана.
  • Калькуляция тарифов на доставку в зависимости от расстояния.
  • Подбор подходящей тары для товара и т.д.

Причем окргуление может требоваться как в меньшую, так и в большую сторону - в зависимости от ситуации.

Есть несколько способов - очевидных и не очень - для решения такой задачи. Давайте рассмотрим их последовательно.

Для начала, представим себе поставщика, который дает скидки на опт, причем процент скидки зависит от количества купленного товара. Например, при покупке свыше 5 штук дается скидка 2%, а при покупке от 20 штук - уже 6% и и т.д.

Как же быстро и красиво вычислить процент скидки при вводе количества купленного товара?

Исходные данные

Способ 1. Вложенные ЕСЛИ

Способ из серии "а что тут думать - прыгать надо!". Используем вложенные функции ЕСЛИ (IF) для последовательной проверки попадания значения ячейки в каждый из интервалов и вывода скидки для соответствующего диапазона. Но формула при этом может получиться весьма громоздкой: 

Исходные данные 

Думаю, очевидно, что отлаживать такую "матрёшку-монстра" или пытаться спустя какое-то время добавить в неё парочку новых условий - это весело.

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

Способ 2. ВПР с интервальным просмотром

Этот способ гораздо компактнее. Для расчета процента скидки используем легендарную функцию ВПР (VLOOKUP) в режиме приблизительного поиска:

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

где

  • B4 - значение количества товара в первой сделке, для которого мы ищем скидку
  • $G$4:$H$8 - ссылка на таблицу скидок - без "шапки" и с закрепленными значком $ адресами.
  • 2 - порядковый номер столбца в таблице скидок, из которого мы хотим получить значение скидки
  • ИСТИНА - здесь и зарыта "собака". Если в качестве последнего аргумента функции ВПР указать ЛОЖЬ (FALSE) или 0, то функция будет искать строгое совпадение в столбце количества (и в нашем случае выдаст ошибку #Н/Д, поскольку значения 49 в таблице скидок нет). А вот если вместо ЛОЖЬ написать ИСТИНА (TRUE) или 1, то функция будет искать не точное, а ближайшее наименьшее значение и выдаст нужный нам процент скидки.

Минусом этого способа является необходимость обязательной сортировки таблицы скидок по возрастанию по первому столбцу. Если такой сортировки нет (или она выполнена в обратном порядке), то наша формула работать не будет:

Поиск не работает без сортировки

Соответственно, использовать этот подход можно только для поиска ближайшего наименьшего значения. Если же необходимо найти ближайшее наибольшее, то придется использовать другой подход.

Способ 3. Поиск ближайшего наибольшего функциями ИНДЕКС и ПОИСКПОЗ

Теперь давайте рассмотрим нашу задачу с другой стороны. Предположим, что мы продаём несколько моделей промышленных насосов различной мощности. В таблице продаж слева указана требуемая для клиента мощность. Нам необходимо подобрать насос ближайшей наибольшей или равной мощности, но не меньше, чем требуется по проекту.

Функция ВПР тут не поможет, так что придётся использовать её аналог - связку функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH):

Поиск ближайшего наибольшего

Здесь функция ПОИСКПОЗ с последним аргументом -1 работает в режиме поиска ближайшего наибольшего значения, а функция ИНДЕКС затем извлекает нужное нам название модели из соседнего столбца.

Способ 4. Новая функция ПРОСМОТРХ (XLOOKUP)

Если у вас версия Office 365 со всеми установленными обновлениями, то вместо ВПР (VLOOKUP) можно использовать её аналог - функцию ПРОСМОТРХ (XLOOKUP), которую я уже подробно разбирал:

Поиск ближайшего с функцией ПРОСМОТРХ

Здесь:

  • B4 - исходное значение количества товара, для которого мы ищем скидку
  • $G$4:$G$8 - диапазон, где мы ищем совпадения
  • $H$4:$H$8 - диапазон результатов, откуда нужно вернуть скидку
  • Четвёртый аргумент (-1) включает нужный нам поиск ближайшего наименьшего числа вместо точного совпадения.

В плюсах у такого способа - отсутствие необходимости сортировки таблицы скидок и возможность искать, если нужно, не только ближайшее наименьшее, но и ближайшее наибольшее значение. Последний аргумент в этом случае будет равен 1.

Но, к сожалению, эта функция пока далеко не у всех - только у счастливых обладателей Office 365.

Способ 5. Power Query

Если вы ещё не знакомы с мощной и при этом совершенно бесплатной надстройкой Power Query для Excel, то вам сюда. Если уже знакомы, то давайте попробуем использовать её для решения нашей задачи.

Сначала выполним подготовительные операции:

  1. Преобразуем наши исходные таблицы в динамические (умные) с помощью сочетания клавиш Ctrl+T или командой Главная - Форматировать как таблицу (Home - Format as Table).
  2. Для наглядности дадим им имена Продажи и Скидки на вкладке Конструктор (Design).
  3. По очереди загрузим каждую из таблиц в Power Query используя кнопку Из таблицы/диапазона на вкладке Данные (Data - From table/range). В последних версиях Excel эту кнопку переименовали в С листа (From sheet).
  4. Если у таблиц различаются названия столбцов с количеством как в нашем примере ("Количество товара" и "Количество от..."), то их в Power Query необходимо переименовать и назвать одинаково.
  5. После этого можно вернуться обратно в Excel, выбрав в окне редактора Power Query команду Главная - Закрыть и загрузить - Закрыть и загрузить в... (Home - Close&Load - Close&Load to...) и затем вариант Только создать подключение (Only create connection).

    Загруженные в Power Query исходные данные
  6. Дальше начинается самое интересное. Если у вас есть опыт работы в Power Query, то, предполагаю, дальнейший ход мыслей должен быть в сторону слияния этих двух таблиц запросом объединения (merge) а-ля ВПР, как это было в предыдущем способе. На самом деле, нам потребуется слияние в режиме добавления, что на первый взгляд совсем не очевидно. Выбираем в Excel на вкладке Данные - Получить данные - Объединить запросы - Добавить (Data - Get Data - Combine queries - Append) и затем наши таблицы Продажи и Скидки в появившемся окне:

    Склейка таблиц

  7. После нажатия на ОК наши таблицы будут склеены в единое целое - друг под друга. Обратите внимание, что столбцы с количеством товара в этих таблицах встали друг под друга, т.к. у них одинаковые названия:

    Добавленные друг под друга таблицы

  8. Если вам важна исходная последовательность строк в таблице продаж, то, чтобы после всех последующих преобразований потом можно было её восстановить, добавим к нашей таблице столбец с нумерацией, используя команду Добавление столбца - Столбец индекса (Add column - Index column). Если последовательность строк для вас роли не играет, то этот шаг можно пропустить.
  9. Теперь с помощью выпадающего списка в шапке таблицы отсортируем её по столбцу Количество по возрастанию:

    Отсортированная таблица

  10. И главный трюк: щёлкаем правой кнопкой мыши по заголовку столбца Скидка выбираем команду Заполнить - Вниз (Fill - Down). Пустые ячейки с null автоматически заполнятся предыдущими значениями скидок:

    Заполняем пустые ячейки скидками

  11. Осталось восстановить исходную последовательность строк сортировкой по столбцу Индекс (его потом можно смело удалить) и избавиться от ненужных строк фильтром null по столбцу Код сделки:

    Готовый результат

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


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
06.05.2021 11:37:14
Создай тему на форуме, тоже интересует ответ на данный вопрос
18.08.2016 11:17:02
Добрый день!
А как быть в случае, когда нижний диапазон не имеет границы, то есть меньше 0.
Тогда, наверное, без ЕСЛИ не обойтись (Если меньше 0, то значение, Иначе ВПР(...))
25.04.2020 14:09:55
Здравствуйте! Как сохранить B6:C9  при копировании формулы вниз по столбцу? иначе получается в следующей ячейке вниз уже B7:C10.
06.05.2021 11:40:54
Есть способ как-то ускорить способ с PQ для больших таблиц, Использую такой же способ, но для таблицы с более 100 млн. строк и справочника на 350 тыс. строк. Считает очень очень долго.
Наверх