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

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

Но бывает, что нужно рассчитать по условию максимальное или минимальное значение. Готовой функции в Excel нет. Однако провести такой расчет можно, если воспользоваться комбинацией функций.

Например, пусть имеются данные

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

Нужно подсчитать максимальное значение, соответствующее группе А. Или минимальное значение в группе Б.

Одним из вариантов решения этой задачи является использование формулы массива. Критерий, по которому будут отбираться данные, то есть название группы, введем в отдельную ячейку (D2). Пусть для начала это будет группа Б. Рядом введем следующую формулу:

=МАКС(ЕСЛИ(A2:A13=D2;B2:B13))

Это формула массива, поэтому ввести ее нужно комбинацией Ctrl + Shift + Enter.

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

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

Как это работает? Очень просто. Первым делом нужно указать диапазон, который будет использоваться в качестве аргумента функции МАКС, то есть только те ячейки, которые соответствуют указанной группе. Так как мы заранее позаботились об удобстве использования функции, то название группы указали не внутри формулы, а в отдельной ячейке (гораздо легче менять группу). Тогда формула для нужного диапазона выглядит так.

ЕСЛИ(A2:A13=D2;B2:B13)

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

Создание массива для функции МАКС

На следующем этапе укажем функцию МАКС, аргументом которой выступает полученный выше массив. Excel воспринимает примерно так.

Массив внутри функции МАКС

Видно, что максимальное значение внутри массива равно 31. Его и мы и увидим в ячейке с формулой. Нужно только не забыть итоговую функцию ввести комбинацией клавиш Ctrl + Shift + Enter, иначе ничего не получится. В строке формул формула массива отображается внутри фигурных скобок. Добавляются сами, специально дорисовывать не нужно.

Для понимания еще раз про алгоритм расчета. Вначале с помощью функции ЕСЛИ создается массив из тех данных, которые удовлетворяют условию. Затем уже в полученном массиве происходит поиск максимального значения. Собственно, все.

Если функцию МАКС заменить на МИН, то по указанному условию (названию группы) будет выдаваться минимальное значение. Оставим это упражнение в качестве домашнего задания.

Ниже можно посмотреть ролик.

Скачать файл с примером.

Поделиться в социальных сетях:
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •