Подсчет максимального и минимального значения выполняется известными функциями МАКС и МИН. Бывает, что вычисления нужно произвести по группам или в зависимости от условия, как в СУММЕСЛИ.
Долгое время в Excel не было аналога СУММЕСЛИ или СРЗНАЧЕСЛИ для расчета максимального и минимального значения, поэтому использовали формулу массивов.
Пусть имеются данные
Нужно подсчитать максимальное значение в указанной группе. Название группы (критерий) введем в отдельную ячейку (D2). Пусть для начала это будет группа Б. Рядом введем следующую формулу:
=МАКС(ЕСЛИ(A2:A13=D2;B2:B13))
Это формула массивов, поэтому ввести ее нужно комбинацией Ctrl + Shift + Enter.
Теперь, меняя название группы, можно без всяких фильтров и сводных таблиц видеть максимальное значение внутри этой группы.
Как это работает? Очень просто. Первым делом нужно указать диапазон, который будет использоваться в качестве аргумента функции МАКС, то есть только те ячейки, которые соответствуют указанной группе. Так как мы заранее позаботились об удобстве использования функции, то название группы указали не внутри формулы, а в отдельной ячейке (гораздо легче менять группу). Тогда формула для нужного диапазона выглядит так.
ЕСЛИ(A2:A13=D2;B2:B13)
Указанное выражение отбирает только те значения, для которых название группы совпадает с условием в ячейке D2. Вот, как это видит Excel
На следующем этапе укажем функцию МАКС, аргументом которой выступает полученный выше массив. Excel воспринимает примерно так.
Видно, что максимальное значение внутри массива равно 31. Его и мы и увидим в ячейке с формулой. Нужно только не забыть итоговую функцию ввести комбинацией клавиш Ctrl + Shift + Enter, иначе ничего не получится. В строке формул формула массива отображается внутри фигурных скобок. Добавляются сами, специально дорисовывать не нужно.
Если функцию МАКС заменить на МИН, то по указанному условию (названию группы) будет выдаваться минимальное значение.
Функции Excel 2016 МАКСЕСЛИ (MAXIFS) и МИНЕСЛИ (MINIFS)
В MS Excel добавили новые статистические функции — МАКСЕСЛИ и МИНЕСЛИ. Обе функции имеют возможность учитывать несколько условий и некоторое время в их названиях в конце были буквы -МН. Потом убрали, хотя в скриншотах ниже используется вариант названий с -МН.
Есть ряд значений, каждое из которых входит в некоторую группу. Нужно рассчитать максимальное значение по группе А. Используем формулу МАКСЕСЛИ.
Все очень просто. Как и у СУММЕСЛИМН вначале указываем диапазон, где находится искомое максимальное значение (колонка В), затем диапазон с критериями (колонка А) и далее сам критерий (в ячейке D2). Можно указать сразу несколько условий. Таким же способом легко рассчитать минимальное значение по условию. Найдем, к примеру, минимум внутри группы Б.
Ниже показан ролик, как рассчитать максимальное и минимальное значение по условию.
К сожалению, такую формулу нельзя протянуть. Есть ли решение такой проблемы?
Функция протягивается, что бы диапазоны поиска не съезжали можно их зафиксировать:
=МАКС(ЕСЛИ($A$2:$A$13=D2;$B$2:$B$13))
Соответственно, при протягивание вниз будет меняться только ячейка сравнения D2 (на D3, D4 и т.д.)
да
Очень простой и понятно изложенный пример! Спасибо, помог.
А как эта формула работает, если условий больше чем одно? В вашем примере условие выполняется, если группа равна «Б». А если нужно два условия: группа равна «Б» и еще какое-то. Попробовал в формуле записать условие через И, но не получилось.
Спасибо, очень помогло, не знал про Ctrl + Shift + Enter.
Есть ли возможность ссылаться на соответствующую ячейку?Например, у меня есть 2 столбца: 1 — с датами, 2 — с температурой; мне надо, чтобы в столбце с температурой посчитался минимум, а рядом прописалась дата, которая соответствует данному показателю температуры. Такое возможно?
Да, например, с помощью ВПР.
уже радует, что можно. А можно поподробнее как это сделать, или ссылку где можно об этом почитать?
Вот целая статья с видео https://statanaliz.info/excel/funktsii-i-formuly/funktsiya-excel-vpr-vlookup-i-gpr-hlookup/
Спасибо огромное
Уважаемые Эксперты Excel ))
Возобновляю тему…
Не получается вывести Наименования с наибольшими показателями в случае, если существует несколько одинаковых наибольших.
Очень рассчитываю на вашу помощь в решении ))
Пример с описанием в приложении..
Заранее признательна!
Как выбрать второе наибольшее значение из разных столбцов стоящих в разноброс
Функция НАИБОЛЬШИЙ
В MS Office 2016 функций МАКСЕСЛИ (MAXIFS) и МИНЕСЛИ (MINIFS) нет. https://support.microsoft.com/ru-ru/office/%D0%BC%D0%B0%D0%BA%D1%81%D0%B5%D1%81%D0%BB%D0%B8-dfd611e6-da2c-488a-919b-9b6376b28883
Спасибо большое!
здраствуйте, подскажите пожалуйста, надо чтобы эксель сама нашла максимальный результат и в ячейке написала «ПРЕМИЯ»
А как найти наибольшее значение среди всех остальных , кроме указанного в ячейке с условием? Нигде о таком не говорят. Почему в формуле в кавычках можно указать условие меньше или больше некоего числа, а указать на ячейку с условием + меньше-равно выдает «значение», ошибку?