Функции МАКС и МИН в Excel по условию | statanaliz.info

Функции МАКС и МИН в Excel по условию

Подсчет максимального и минимального значения выполняется известными функциями МАКС и МИН. Бывает, что вычисления нужно произвести по группам или в зависимости от условия, как в СУММЕСЛИ.

Долгое время в 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 — с температурой; мне надо, чтобы в столбце с температурой посчитался минимум, а рядом прописалась дата, которая соответствует данному показателю температуры. Такое возможно?

    • Езепов Дмитрий

      Да, например, с помощью ВПР.

  • Аноним

    Уважаемые Эксперты 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

Комментарии для сайта Cackle
Пролистать наверх

40 приемов и трюков Excel

Чтобы работать, как профессионал

Конфиденциальность данных гарантирована.
Никакого спама.