Поиск минимального или максимального значения по условию

В Microsoft Excel давно есть в стандартном наборе функции СЧЁТЕСЛИ (COUNTIF), СУММЕСЛИ (SUMIF) и СРЗНАЧЕСЛИ (AVERAGEIF) и их аналоги, позволяющие искать количество, сумму и среднее в таблице по одному или нескольким условиям. Но что если нужно найти не сумму или среднее, а минимум или максимум по условию(ям)? 

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

min-if1.png

Таким образом, условием будет наименование товара (бумага, карандаши, ручки), а диапазоном для выборки - столбец с ценами.

Для будущего удобства, конвертируем исходный диапазон с ценами в "умную таблицу". Для этого выделите его и выберите на вкладке Главная - Форматировать как таблицу (Home - Format as Table) или нажмите Ctrl+T. Наша "поумневшая" таблица автоматически получит имя Таблица1, а к столбцам можно будет, соответственно, обращаться по их именам, используя выражения типа Таблица1[Товар] или Таблица1[Цена]. При желании, стандартное имя Таблица1 можно подкорректировать на вкладке Конструктор (Design), которая появляется, если щелкнуть в любую ячейку нашей "умной" таблицы. Подробнее о таких таблицах и их скрытых возможностях можно почитать здесь.

Способ 1. Функции МИНЕСЛИ и МАКСЕСЛИ в Excel 2016

Начиная с версии Excel 2016 в наборе функции Microsoft Excel наконец появились функции, которые легко решают нашу задачу - это функции МИНЕСЛИ (MINIFS) и МАКСЕСЛИ (MAXIFS). Синтаксис этих функции очень похож на СУММЕСЛИМН (SUMIFS):

=МИНЕСЛИ(Диапазон_чисел; Диапазон_проверки1; Условие1; Диапазон_проверки2; Условие2 ... )

где

  • Диапазон_чисел - диапазон с числами, из которых выбирается минимальное или максимальное
  • Диапазон_проверки - диапазон, который проверяется на выполнение условия
  • Условие - критерий отбора
Например, в нашем случае:

Поиск минимального по условию функцией МИНЕСЛИ

Просто, красиво, изящно. Одна проблема - функции МИНЕСЛИ и МАКСЕСЛИ появились только начиная с 2016 версии Excel. Если у вас (или тех, кто будет потом работать с вашим файлом) более старые версии, то придется шаманить другими способами.

Способ 2. Формула массива

min-if2.png

В английской версии это будет, соответственно =MIN(IF(Table1[Товар]=F4;Table1[Цена]))

Не забудьте после ввода этой формулы в первую зеленую ячейку G4 нажать не Enter, а Ctrl+Shift+Enter, чтобы ввести ее как формулу массива. Затем формулу можно скопировать на остальные товары в ячейки G5:G6.

Давайте разберем логику работы этой формулы поподробнее. Функция ЕСЛИ проверяет каждую ячейку массива из столбца Товар на предмет равенства текущему товару (Бумага). Если это так, то выдается соответствующее ему значение из столбца Цена. В противном случае – логическое значение ЛОЖЬ (FALSE).

Таким образом внешняя функция МИН (MIN) выбирает минимальное не из всех значений цен, а только из тех, где товар был Бумага, т.к. ЛОЖЬ функцией МИН игнорируется. При желании, можно выделить мышью всю функцию ЕСЛИ(…) в строке формул

min-if3.png

... и нажать на клавиатуре F9, чтобы наглядно увидеть тот самый результирующий массив, из которого потом функция МИН и выбирает минимальное значение:

min-if4.png

Способ 3. Функция баз данных ДМИН

Этот вариант использует малоизвестную (и многими, к сожалению, недооцененную) функцию ДМИН (DMIN) из категории Работа с базой данных (Database) и требует небольшого изменения результирующей таблицы:

min-if5.png

Как видите, зеленые ячейки с результатами транспонированы из столбца в строку и над ними добавлена мини-таблица (F4:H5) с условиями. Логика работы этой функции следующая:

min-if6.png

  • База_данных - вся наша таблица вместе с заголовками.
  • Поле - название столбца из шапки таблицы, из которого выбирается минимальное значение.
  • Критерий - таблица с условиями отбора, состоящая (минимально) из двух ячеек: названия столбца, по которому идет проверка (Товар) и критерия (Бумага, Карандаши, Ручки).

Это обычная формула (не формула массива), т.е. можно вводить и использовать ее привычным образом. Кроме того, в той же категории можно найти функции БДСУММ (DSUM), ДМАКС (DMAX), БСЧЁТ (DCOUNT), которые используются совершенно аналогично, но умеют находить не только минимум, но и сумму, максимум и количество значений по условию.

Способ 4. Сводная таблица

Если в исходной таблице очень много строк, но данные меняются не часто, то удобнее будет использовать сводную таблицу, т.к. формула массива и функция ДМИН могут сильно тормозить Excel.

Установите активную ячейку в любое место нашей умной таблицы и выберите на вкладке Вставка - Сводная таблица (Insert - Pivot Table). В появившемся окне нажмите ОК:

min-if7.png

В конструкторе сводной таблицы перетащите поле Товар в область строк, а Цену в область значений. Чтобы заставить сводную вычислять не сумму (или количество), а минимум щелкните правой кнопкой мыши по любому числу и выберите в контекстном меню команду Итоги по - Минимум:

min-if8.png

Вытаскивать данные из сводной в дальнейшие расчеты теперь можно с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GET.PIVOT.DATA), которую мы подробно разбирали ранее:

min-if9.png

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



08.04.2016 22:41:20
=АГРЕГАТ(15;6;Таблица1[Цена]/(Таблица1[Товар]=F4);1)  
Тоже очень удобно. Можно вытаскивать 2-е, 3-е наименьшее
19.04.2017 08:55:12
Оригинально!
22.08.2018 10:09:18
Очень сильно.
Отличное решение.
02.04.2019 11:02:51
нереально круто! спасибо
A A
14.04.2020 15:28:45
Спасибо! Превосходное решение)
09.09.2020 08:47:29
Гениально! добавляя в формулу /(массив=значение) можно добавлять дополнительные условия выбора. Спасибо!
24.02.2021 15:16:17
Ни черта не могу пока понять всех особенностей этой функции, но, во-первых, отлично сработало после грубого копипаста, а во-вторых, прям интересно стало, как этот зверь работает и в каких задачах еще может оказаться полезным. Ушел читать. Спасибо вам за наводку
30.09.2021 10:20:51
Зашел в поисках наименьшего по условию (Excel 2013).
Виктор, это просто офигенно, и, главное - насколько все изящно и компактно!
16.11.2022 10:14:14
Добрый день, а как вытаскивать 2-е, 3-е значение игнорируя дубликаты?
27.09.2023 18:18:02
Я бы сделал отдельный столбец, с флагами для дубликатов, а потом в формуле Виктора добавил бы условие для исключения строк с флагами из расчета наименьшего/наибольшего значения.
14.03.2024 23:04:12
Прекрасно. Применил функцию Агрегат
20.12.2016 12:57:15
Здравствуйте, Николай, и все кто может помочь!
Подскажите, пожалуйста, есть ли такая возможность, решить простенькую задачку.
Исходные данные:
Столбик с данными. (Одномерный массив)
Ищу в нем Сумму ТОП-10 с макс. значением (воспользовался функцией из PLEX "сумма ТОП-5", модернизировав ее).
То, что найдены топ-10 значений и просуммированы, это хорошо...
Вопрос, а как их (эти ТОП-10) теперь додсветить каким-либо цветом?
Может быть использовать формулы условного форматирования, или есть какой-то другой вариант (способ)?
С уважением,
Андрей Кузнецов
27.02.2017 13:30:51
Коллеги, не подскажете, как быть, если нужно найти из столбца данных минимальное значение, но за исключением, например, 0 или отрицательных чисел... Спасибо!
24.03.2017 14:15:02
А как можно суммировать например первые 2 или 3 максимальных(минимальных) значения?
24.03.2017 15:19:27
=СУММ(НАИБОЛЬШИЙ(C2:C16;{1;2;3}))
31.03.2017 11:44:46
Подскажите плиз, а как посчитать наименьшую/наибольшую цену не только по одному параметру - "Товар" но и "Поставщик". Другими словами, если у Поставщика несколько видов Бумаги, то что бы находило самую дешевую/дорогую бумагу по каждому Поставщику.
19.04.2017 08:57:39
Можно через формулу массива задать несколько условий, например:

{=МИН(
 ЕСЛИ(Таблица1[Товар]=F20;
 ЕСЛИ(Таблица1[Поставщик]="Писарь и Ко";
 Таблица1[Цена]
)))}
08.06.2017 12:52:41
Всем доброго дня.
К сожалению на установленном Excel 2016 не нашел функций МИНЕСЛИ и МАКСЕСЛИ.
08.06.2017 13:58:18
Когда ориентация не вертикальная и необходимо сравнить один и тот же товар у множества постовщиков по критерию цена, написал такую формулу -{=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(CO2:FF2=DL7;CO3:FF3;"");1);"")} Николай, если не сложно то прокомментируйте пожалуйста, возможно есть куда более легкий вариант.
16.03.2018 10:56:08
Александр 08.06.2017 12:52:41
Всем доброго дня.
К сожалению на установленном Excel 2016 не нашел функций МИНЕСЛИ и МАКСЕСЛИ.
Также после установки не нашел данной функции. На сайте майкрософт написано: "Примечание: Эта функция доступна только при наличии подписки на Office 365. Если у вас есть подписка на Office 365, убедитесь, что у вас установлена последняя версия Office. " Даже  не знаю что и делать. Формула массива сильно грузит файл, ДМИН работает с условием представленным в виде столбца, а структура сводной таблицы не позволяет корректно отобразить требуемые данные. Буду рад если кто-нибудь что-нибудь подскажет.
04.06.2019 17:59:03
Николай Павлов внесите, пожалуйста, правку в статью:

Способ 1. Функции МИНЕСЛИ и МАКСЕСЛИ в Excel 2019
А вот и сама ссылка
Excel для Office 365 Excel для Office 365 для Mac Excel 2019 Excel 2019 для Mac Excel Online Excel для iPad Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Excel Mobile
12.12.2018 17:47:59
Подскажите, пожалуйста, как воспользоваться формулой массива, что бы она не учитывала пустые ячейки?
24.03.2019 06:07:29
Скажите пожалуйста а как найти максимальное значение даты по нескольким условиям в гугл таблице или в экселе же?
05.09.2019 18:04:13
Николай, мне на работе установили Excel 2016, но функций МИНЕСЛИ и МАКСЕСЛИ;)
06.02.2020 11:54:20
Добрый!
Супер формула.
а как можно еще выдернуть названия поставщика у которого мин цена
на эксель 2007?
Спасибо!
10.02.2021 18:50:37
Возможно, глупый вопрос.

В массиве  Excel2007  три столбца чисел. Количество строк исчисляется десятками тысяч. Как правильно нарисовать  формулу для массива, чтобы в четвёртом столбце получить максимум из чисел в  каждой строке?
21.08.2021 12:39:11
Здравствуйте. Есть простой вопрос, но не могу с ним справиться.
Есть  РЯД ПАРАМЕТРОВ 11, 25, -3, 0, 165, 300, -20, Как excel 2010 находить MAX и MIN, НО ! только среди чисел >5?
Пробовал через если, но не получается. если не трудно подскажите пожалуйста.  
09.09.2021 15:59:44
Добрый день!
Николай, скажите, пожалуйста, как можно решить подобную задачу, но, в которой требуется вытащить минимальное число во весь столбец по заданному условию? Через минесли, наимен не получается
Код Товара АналогценаЦена с учетом аналогов
2000003845298,5
2000003846      2489,5
2000003856      2125,9
200000484627,0
ЦТ0023260      314,7
ЦТ0023261      547,9
DI0007950869,8
ЦО000106891     169,9
ЦО000106892     169,9
ЦО00010689334,9
ЦО000106894     549,9
ЦО000106895     357,7
ЦО00010689699,9
12.12.2021 21:51:11
Добрый вечер, поломал голову, прошу помощи, как найти мкс или мин значение между двумя датами? Понимаю вопрос может быть и смешным, но ни как не могу потянуть его....
Столбец 2 - цена
Столбец 1 - дата
B1 и D1 - диапазон дат "с" - "по"
Сделал вот так: =МАКСЕСЛИ(Таблица4[Столбец2];Таблица4[Столбец1];"B1<";Таблица4[Столбец1];"D1>";)
12.12.2021 23:44:00
Получилось
столбец "А" - даты
столбец "В" - значения
$В$1 и $D$7 - диапазон дат


=МАКС(ЕСЛИ((A5:A17<=$D$1)*(A5:A17>=$B$1);B5:B17))
08.06.2022 16:05:27
Добрый день!
а PQ как реализовать?
Нужно в категории найти первую цену.
20.07.2022 18:17:07
У К. Карлсберга динамические имена можно было задавать принудительно:
=СМЕЩ($A$1;1;0;СЧЁТ($A:$A);4) == база данных курсов валют (дата; USD; EUR; GBR) <Ctrl+F3> задать динамическое имя (_RateCBR) диапазону a1:d180 и использовать её в формулах ВПР и др.
=СМЕЩ($A$1;ПОИСКПОЗ(МАКС(A:A)-1;A:A;1)-1;0) -- дата в базе данных в курсов валют == максимальное значение в таблице.
наверняка подобные методы, но более изящные Вы сможете предложить.
Добрый день!
Коллеги! У меня затык((( Помогите!!!
Дано столбец с цифровыми значениями, Значения представлены как интервалами чисел (1,2,3,4 и т.д) так и единичными значениями например 15. То есть числовой ряд выглядит так: 1,2,3,4,5,6,15,20,21,22,23,24.
Как вывести в отдельную ячейку начало и конец интервала: 1 - 6; 15; 20 - 24.
07.01.2024 16:48:47
Необходимо реализовать задачу, условно обозначенную "раздача почетных грамот передовикам производства по цехам". Или "участникам соревнований по видам спорта с 1 по 3 место".
То есть столбцы: список / цех / показатели. В итоговом столбце нужно раздать всем отличившимся медальки/места/грамоты/премии.
С помощью формулы Наибольший в матрице удалось проранжировать только по одному типу в фильтре, указанному в формуле. Как сделать универсальную и простую формулу, не множа условия по каждому виду, не могу решить

upd. Сам нашел. Все, оказывается, нормально сработало в виде:
=ЕСЛИ(H12=НАИБОЛЬШИЙ((цех=фильтр)*показатели;1);премия1;ЕСЛИ(H12=НАИБОЛЬШИЙ((цех=фильтр)*показатели;2);премия2;ЕСЛИ(H12=НАИБОЛЬШИЙ((цех=фильтр)*показатели;3);премия3))
Наверх