Check-moscow.ru

Финансы и учет
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как выделить ближайшее к заданному числу в списке значение в Excel?

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

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

  • Расчет скидок в зависимости от количества.
  • Расчет премий в зависимости от выполнения плана.
  • Расчет тарифов на доставку в зависимости от расстояния.
  • Выбор соответствующей упаковки для товаров и т.д.

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

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

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

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

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

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

Это и есть «о чем подумать — прыгай! Мы используем вложенные функции IF, чтобы последовательно проверить, находится ли значение ячейки в каждом из диапазонов, и выдать скидку для соответствующего диапазона. Но это может сделать формулу очень громоздкой:

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

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

Кроме того, Microsoft Excel установил предел вложенности для функции IF: 7 раз в старых версиях и 64 раза в новых. А если вам нужно больше?

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

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

Читайте так же:
Как выбрать весь столбец, кроме заголовка / первой строки в Excel?

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

  • B4 — значение количества товара в первой сделке, для которого мы ищем скидку
  • $G$4:$H$8 — ссылка на таблицу скидок — без «шапки» и со знаком $ по адресам.
  • 2 — порядковый номер столбца в таблице скидок, из которого мы хотим получить значение скидки
  • TRUE — здесь хранится «собака». Если мы укажем FALSE или 0 в качестве последнего аргумента в функции FFT, функция будет искать близкое соответствие в столбце количества (и в нашем случае она вернет ошибку #N/D, поскольку в таблице скидок нет значения 49). Однако если вместо FALSE ввести TRUE или 1, то функция будет искать не точное, а ближайшее наименьшее значение и выдаст нам желаемый процент скидки.

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

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

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

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

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

В этом случае БПФ бесполезно, поэтому мы должны использовать его аналог в сочетании с ИНДЕКСОМ и МАТЧ:

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

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

Читайте так же:
Как вставить и удалить верхний, нижний колонтитул и изображение верхнего колонтитула в Excel?

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

Если в Office 365 установлены все обновления, то можно воспользоваться аналогом VLOOKUP — SPACE (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) .

Поиск ближайшего значения без массива в Excel

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

использование вспомогательного столбца.

Конечно, решение нашей задачи будет найдено в той строке, где это значение наименьшее.

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

выбрать соответствующее значение.

Название самого близкого значения:

выбрать соответствующее ему имя.

Где столбец «D» — это наш столбец помощи, а столбец «B» — это столбец имени. Сразу добавлю (чтобы было понятно), что столбец «С» — это столбец со значениями баллов.

Чтобы получить позицию последнего совпадения (т.е. последнего вхождения) из значения поиска, можно использовать формулу, основанную на функциях ЕСЛИ, СТРЕЛКА, ИНДЕКС, ПОИСК и MAX.

Получить позицию последнего совпадения

В примере формула находится в G6 :

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

Пример использования поиска решений

Давайте рассмотрим эту функцию более подробно.

1) Выполните следующие действия для активации функции «Поиск решения»:

  • Нажмите Параметры Excel, а затем выберите категорию «Дополнения»;
  • В разделе Администрирование выберите Дополнения Excel, а затем нажмите Перейти;
  • В разделе «Доступные дополнения» установите флажок рядом с «Найти решение», а затем нажмите кнопку OK.

2019-08-06 18.58.30.jpg

2019-08-06 18.58.37.jpg

2) Теперь организуйте данные в таблицу, показывающую взаимосвязи между ячейками. Рекомендуется использовать цветовое кодирование: в примере объективная функция выделена красным цветом, ограничения — бежевым, а ячейки, которые можно изменить, выделены желтым.

tg_image_2790408830.jpeg

Не забудьте ввести формулы. Стоимость заказа рассчитывается как «Затраты труда на одно изделие». умноженное на «Количество единиц товара, подлежащего доставке». Чтобы узнать «Время выполнения задания», разделите «Количество сдаваемых изделий» на «Производительность».

tg_image_954796317.jpeg
tg_image_2790408830.jpeg

tg_image_4145344377.jpeg

3) Выберите целевую ячейку, которая должна отображать минимальное, максимальное или конкретное значение при заданных условиях. Для этого нажмите «Данные» на панели и выберите функцию «Найти решения» (обычно в правом верхнем углу).

tg_image_2089575366.jpeg

4) Заполните параметры «Поиск решений» и нажмите на «Найти решение».

Расчет общей стоимости 1000 изделий рассчитывается путем включения затрат на все изделия от всех рабочих. Эта ячейка (E13) представляет целевую функцию. D9:D12 — это изменяемые ячейки. Поиск решения заключается в нахождении их оптимального значения, чтобы целевая функция достигла минимума при заданных ограничениях.

В нашем примере действуют следующие ограничения

  • Общее количество изделий составляет 1000 штук ($D$13 = $D$3);
  • Количество отрезов, переданных на работу — целое число и больше или равно нулю ($D$9:$D$12 = целое число, $D$9 : $D$12 > = 0);
  • Количество дней меньше или равно 30 ($F$9:$F$12 < = $D$6, или как в примере в ячейку F13 поместить функцию MAX(F9:F12) и поставить ограничение $F$13 < = $D$6).

tg_image_2951437605.jpeg

5) В конце сверьте данные с целевым значением. Если что-то не сходится, проверьте исходные данные, формулы и ограничения.

tg_image_1895334008.jpeg

Вы хотите научиться решать задачи в Excel так, как это делают крупные компании? Приходите на наш онлайн-курс, где вы освоите этот инструмент на профессиональном уровне. Вы пройдете обучение у экспертов-профессионалов и получите возможность добавить мощную строчку в свое резюме после окончания курса — зарегистрируйтесь сейчас!

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector