Поиск ЧИСЛА ближайшего к заданному. Несортированный список в EXCEL
Для поиска ЧИСЛА ближайшего к заданному, в EXCEL существует специальные функции, например, ВПР() , ПРОСМОТР() , ПОИСКПОЗ() , но они работают только если исходный список сортирован по возрастанию или убыванию. Используя формулы массива создадим аналогичные формулы, но работающие и в случае несортированного списка.
Решение задачи поиска ближайшего числового значения в случае сортированного списка приведена в статье Поиск ЧИСЛА ближайшего к заданному. Сортированный список .
Рассмотрим задачу в более общем виде. Пусть имеется несортированный список чисел (в диапазоне A4:A15 ). (см. Файл примера ).
В качестве критерия для поиска используем любое число, введем его в ячейку С4 . Найдем значение из диапазона, ближайшее к критерию с помощью формул массива :
Решение
Как происходит поиск
Примечание
ищется наибольшее значение, которое меньше, чем искомое значение (ближайшее сверху)
Если заданное значение меньше минимального, то выдается ошибка #Н/Д
= ИНДЕКС(A4:A15;ПОИСКПОЗ( МИН(ЕСЛИ(A4:A15>=C4;A4:A15;»»)); $A$4:$A$15;0);1)
ищется наименьшее значение, которое больше, чем искомое значение (ближайшее снизу)
Если заданное значение больше максимального, то выдается ошибка #Н/Д
= ИНДЕКС(A4:A15;ПОИСКПОЗ( МИН(ABS(A4:A15-C4)); ABS(A4:A15-C4);0))
ищется ближайшее к критерию число
если ближайшее снизу и ближайшее сверху отстоят на одинаковое расстояние от критерия, то берется ближайшее число, расположенное первым в списке (например, ближайшее к 5 в списке 2; 4 ; 6 ; 8 будет 4, а в списке 2; 6 ; 4 ; 8 — будет 6), т.е. предсказать будет ли число ближайшим сверху или снизу невозможно
ищется ближайшее к критерию число
если обнаружено 2 ближайших числа (одно больше, другое меньше критерия), то выводится то, которое больше
ищется ближайшее к критерию число
если обнаружено 2 ближайших числа (одно больше, другое меньше критерия), то выводится то, которое меньше
СОВЕТ: Для пошагового просмотра хода вычислений формул используйте клавишу F9 .
Поиск ближайшего числа
Есть несколько способов — очевидных и не очень — для решения такой задачи. Давайте рассмотрим их последовательно.
Для начала, представим себе поставщика, который дает скидки на опт, причем процент скидки зависит от количества купленного товара. Например, при покупке свыше 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) и затем наши таблицы Продажи и Скидки в появившемся окне:
Ссылки по теме
- Использование функции ВПР (VLOOKUP) для поиска и подстановки данных
- Использование функции ВПР (VLOOKUP) с учетом регистра
- Двумерный ВПР (VLOOKUP)
Поиск минимального или максимального значения по условию
В Microsoft Excel давно есть в стандартном наборе функции СЧЁТЕСЛИ (COUNTIF) , СУММЕСЛИ (SUMIF) и СРЗНАЧЕСЛИ (AVERAGEIF) и их аналоги, позволяющие искать количество, сумму и среднее в таблице по одному или нескольким условиям. Но что если нужно найти не сумму или среднее, а минимум или максимум по условию(ям)?
Предположим, нам нужно найти минимальную цену для каждого товара в базе данных по поставщикам:
Таким образом, условием будет наименование товара (бумага, карандаши, ручки), а диапазоном для выборки — столбец с ценами. Для будущего удобства, конвертируем исходный диапазон с ценами в «умную таблицу». Для этого выделите его и выберите на вкладке Главная — Форматировать как таблицу (Home — Format as Table) или нажмите Ctrl+T. Наша «поумневшая» таблица автоматически получит имя Таблица1, а к столбцам можно будет, соответственно, обращаться по их именам, используя выражения типа Таблица1[Товар] или Таблица1[Цена]. При желании, стандартное имя Таблица1 можно подкорректировать на вкладке Конструктор (Design) , которая появляется, если щелкнуть в любую ячейку нашей «умной» таблицы. Подробнее о таких таблицах и их скрытых возможностях можно почитать здесь.
Способ 1. Функции МИНЕСЛИ и МАКСЕСЛИ в Excel 2016
- Диапазон_чисел — диапазон с числами, из которых выбирается минимальное или максимальное
- Диапазон_проверки — диапазон, который проверяется на выполнение условия
- Условие — критерий отбора
Просто, красиво, изящно. Одна проблема — функции МИНЕСЛИ и МАКСЕСЛИ появились только начиная с 2016 версии Excel. Если у вас (или тех, кто будет потом работать с вашим файлом) более старые версии, то придется шаманить другими способами.
Способ 2. Формула массива
В английской версии это будет, соответственно =MIN(IF(Table1[Товар]=F4;Table1[Цена]))
Не забудьте после ввода этой формулы в первую зеленую ячейку G4 нажать не Enter , а Ctrl + Shift + Enter , чтобы ввести ее как формулу массива. Затем формулу можно скопировать на остальные товары в ячейки G5:G6.
Давайте разберем логику работы этой формулы поподробнее. Функция ЕСЛИ проверяет каждую ячейку массива из столбца Товар на предмет равенства текущему товару (Бумага). Если это так, то выдается соответствующее ему значение из столбца Цена. В противном случае – логическое значение ЛОЖЬ (FALSE) .
Таким образом внешняя функция МИН (MIN) выбирает минимальное не из всех значений цен, а только из тех, где товар был Бумага, т.к. ЛОЖЬ функцией МИН игнорируется. При желании, можно выделить мышью всю функцию ЕСЛИ(…) в строке формул
. и нажать на клавиатуре F9, чтобы наглядно увидеть тот самый результирующий массив, из которого потом функция МИН и выбирает минимальное значение:
Способ 3. Функция баз данных ДМИН
Этот вариант использует малоизвестную (и многими, к сожалению, недооцененную) функцию ДМИН (DMIN) из категории Работа с базой данных (Database) и требует небольшого изменения результирующей таблицы:
Как видите, зеленые ячейки с результатами транспонированы из столбца в строку и над ними добавлена мини-таблица (F4:H5) с условиями. Логика работы этой функции следующая:
- База_данных — вся наша таблица вместе с заголовками.
- Поле — название столбца из шапки таблицы, из которого выбирается минимальное значение.
- Критерий — таблица с условиями отбора, состоящая (минимально) из двух ячеек: названия столбца, по которому идет проверка (Товар) и критерия (Бумага, Карандаши, Ручки).
Это обычная формула (не формула массива), т.е. можно вводить и использовать ее привычным образом. Кроме того, в той же категории можно найти функции БДСУММ (DSUM) , ДМАКС (DMAX) , БСЧЁТ (DCOUNT) , которые используются совершенно аналогично, но умеют находить не только минимум, но и сумму, максимум и количество значений по условию.
Способ 4. Сводная таблица
Если в исходной таблице очень много строк, но данные меняются не часто, то удобнее будет использовать сводную таблицу, т.к. формула массива и функция ДМИН могут сильно тормозить Excel.
Установите активную ячейку в любое место нашей умной таблицы и выберите на вкладке Вставка — Сводная таблица (Insert — Pivot Table) . В появившемся окне нажмите ОК:
В конструкторе сводной таблицы перетащите поле Товар в область строк, а Цену в область значений. Чтобы заставить сводную вычислять не сумму (или количество), а минимум щелкните правой кнопкой мыши по любому числу и выберите в контекстном меню команду Итоги по — Минимум:
Вытаскивать данные из сводной в дальнейшие расчеты теперь можно с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GET.PIVOT.DATA) , которую мы подробно разбирали ранее:
Ссылки по теме
- Зачем нужна функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
- Выборочное вычисление суммы, среднего и т.д. по одному или нескольким критериям
- Что такое «умные таблицы» в Excel и как с ними работать
Функция ЦЕЛОЕ
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
В этой статье описаны синтаксис формулы и использование функции ЦЕЛОЕ в Microsoft Excel.
Описание
Округляет число до ближайшего меньшего целого.
Синтаксис
Аргументы функции ЦЕЛОЕ описаны ниже.
- Число — обязательный аргумент. Вещественное число, округляемое до ближайшего меньшего целого.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.