Поиск ближайшего числа
На практике весьма часто возникают случаи, когда нам с вами нужно найти ближайшее значение в наборе (таблице) по отношению к заданному числу. Это может быть, например:
- Расчет скидки в зависимости от объема.
- Вычисление размера бонусов в зависимости от выполнения плана.
- Калькуляция тарифов на доставку в зависимости от расстояния.
- Подбор подходящей тары для товара и т.д.
Причем окргуление может требоваться как в меньшую, так и в большую сторону - в зависимости от ситуации.
Есть несколько способов - очевидных и не очень - для решения такой задачи. Давайте рассмотрим их последовательно.
Для начала, представим себе поставщика, который дает скидки на опт, причем процент скидки зависит от количества купленного товара. Например, при покупке свыше 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, то вам сюда. Если уже знакомы, то давайте попробуем использовать её для решения нашей задачи.
Сначала выполним подготовительные операции:
- Преобразуем наши исходные таблицы в динамические (умные) с помощью сочетания клавиш Ctrl+T или командой Главная - Форматировать как таблицу (Home - Format as Table).
- Для наглядности дадим им имена Продажи и Скидки на вкладке Конструктор (Design).
- По очереди загрузим каждую из таблиц в Power Query используя кнопку Из таблицы/диапазона на вкладке Данные (Data - From table/range). В последних версиях Excel эту кнопку переименовали в С листа (From sheet).
- Если у таблиц различаются названия столбцов с количеством как в нашем примере ("Количество товара" и "Количество от..."), то их в Power Query необходимо переименовать и назвать одинаково.
- После этого можно вернуться обратно в Excel, выбрав в окне редактора Power Query команду Главная - Закрыть и загрузить - Закрыть и загрузить в... (Home - Close&Load - Close&Load to...) и затем вариант Только создать подключение (Only create connection).
- Дальше начинается самое интересное. Если у вас есть опыт работы в Power Query, то, предполагаю, дальнейший ход мыслей должен быть в сторону слияния этих двух таблиц запросом объединения (merge) а-ля ВПР, как это было в предыдущем способе. На самом деле, нам потребуется слияние в режиме добавления, что на первый взгляд совсем не очевидно. Выбираем в Excel на вкладке Данные - Получить данные - Объединить запросы - Добавить (Data - Get Data - Combine queries - Append) и затем наши таблицы Продажи и Скидки в появившемся окне:
- После нажатия на ОК наши таблицы будут склеены в единое целое - друг под друга. Обратите внимание, что столбцы с количеством товара в этих таблицах встали друг под друга, т.к. у них одинаковые названия:
- Если вам важна исходная последовательность строк в таблице продаж, то, чтобы после всех последующих преобразований потом можно было её восстановить, добавим к нашей таблице столбец с нумерацией, используя команду Добавление столбца - Столбец индекса (Add column - Index column). Если последовательность строк для вас роли не играет, то этот шаг можно пропустить.
- Теперь с помощью выпадающего списка в шапке таблицы отсортируем её по столбцу Количество по возрастанию:
- И главный трюк: щёлкаем правой кнопкой мыши по заголовку столбца Скидка выбираем команду Заполнить - Вниз (Fill - Down). Пустые ячейки с null автоматически заполнятся предыдущими значениями скидок:
- Осталось восстановить исходную последовательность строк сортировкой по столбцу Индекс (его потом можно смело удалить) и избавиться от ненужных строк фильтром null по столбцу Код сделки:
Ссылки по теме
ВПР не находит числовое значение в массиве выдает Н/Д
Спасибо.
Имеется таблица где в столбцах указаны имена клиентов, даты оплат и суммы до погащения долгов. как можно с этой таблицы по названиям клиентов вывести количесто дней между первой и последней даты оплаты?
подскажите, пожалуйста, если имеется список диапазонов, при этом следующий диапазон как может быть так и не может быть продолжением предыдущего, как его уменьшить, объединив идущие по порядку диапазоны в один?
А как быть в случае, когда нижний диапазон не имеет границы, то есть меньше 0.
Тогда, наверное, без ЕСЛИ не обойтись (Если меньше 0, то значение, Иначе ВПР(...))
Николай, у меня остался 1 мелкий но важный для меня вопрос на который в ролике нет ответа
Пример с Квери выдает нагора не проценты а просто числа даже если в Квери числа изменить в проценты.
Можно ли решить эту проблему без последующего форматирования ячеек?
Спасибо!