Находим ближайший номер

Содержание:

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

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

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

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

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

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

Метод 1: вложенные IF

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

Находим ближайший номер

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

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

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

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

Находим ближайший номер

в котором

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

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

Находим ближайший номер

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

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

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

Функция ВПР здесь не поможет, поэтому придется воспользоваться ее аналогом — набором функций ИНДЕКС. (ПОКАЗАТЕЛЬ) и БОЛЬШЕ ОТКРЫТОГО (СООТВЕТСТВОВАТЬ):

Находим ближайший номер

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

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

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

Находим ближайший номер

Вот:

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

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

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

Способ 5. Power Query

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

Сначала проведем подготовительную работу:

Давайте преобразуем наши исходные таблицы в динамические (умные) с помощью сочетания клавиш. Ctrl+T или команда Главная – Форматировать в виде таблицы (Главная страница — Форматировать как таблицу).
Для наглядности дадим им имена. Продажа и Скидки таб Конструктор (Дизайн).
Загрузите каждую из таблиц по очереди в Power Query с помощью кнопки Из таблицы/диапазона таб Данные (Данные — из таблицы/диапазона). В последних версиях Excel эта кнопка была переименована в С листьями (Из листа).
Если таблицы имеют разные названия столбцов с количествами, как в нашем примере («Количество товаров» и «Количество из…»), то их необходимо переименовать в Power Query и назвать одинаково.
После этого вы можете вернуться обратно в Excel, выбрав команду в окне редактора Power Query. На главную — Закрыть и загрузить — Закрыть и загрузить… (Главная страница — Закрыть и загрузить — Закрыть и загрузить в…) и тогда вариант Просто создайте соединение (Только создать соединение).
Дальше начинается самое интересное. Если у вас есть опыт работы в Power Query, то предполагаю, что дальнейший ход мыслей должен быть в сторону объединения этих двух таблиц с помощью запроса соединения (слияния) а-ля VLOOKUP, как это было в предыдущем способе. На самом деле нам нужно будет выполнить слияние в режиме добавления, что на первый взгляд совсем не очевидно. Выбрать на вкладке Excel Данные – Получить данные – Объединить запросы – Добавить (Данные — Получить данные — Объединить запросы — Добавить) а затем наши столы Продажа и Скидки в появившемся окне:
После нажатия на OK наши столы будут склеены в единое целое – друг под другом. Обратите внимание, что столбцы с количеством товаров в этих таблицах попали друг под друга, т.к. у них одинаковое имя:
Если для вас важна исходная последовательность строк в таблице продаж, то для того, чтобы после всех последующих преобразований вы могли ее потом восстановить, добавьте в нашу таблицу нумерованный столбец с помощью команды Добавление столбца – индексный столбец (Добавить столбец — Индексный столбец). Если последовательность строк для вас не имеет значения, то этот шаг можно пропустить.
Теперь, используя выпадающий список в шапке таблицы, отсортируйте ее по столбцам Количество По возрастанию:
И главный трюк: щелкните правой кнопкой мыши по заголовку столбца скидка выбрать команду Заполнить – вниз (Заполнить — Вниз). Пустые ячейки с нуль автоматически заполняется предыдущими значениями скидок:
Осталось восстановить исходную последовательность строк путем сортировки по столбцу Индекс (можете потом спокойно удалить) и избавьтесь от ненужных строк с помощью фильтра нуль по столбцу Код транзакции: