Минимум если в эксель
Обновлено: 07.07.2024
Подсчет максимального и минимального значения выполняется известными функциями МАКС и МИН. Бывает, что вычисления нужно произвести по группам или в зависимости от условия, как в СУММЕСЛИ.
Долгое время в Excel не было аналога СУММЕСЛИ или СРЗНАЧЕСЛИ для расчета максимального и минимального значения, поэтому использовали формулу массивов.
Пусть имеются данные
Нужно подсчитать максимальное значение в указанной группе. Название группы (критерий) введем в отдельную ячейку (D2). Пусть для начала это будет группа Б. Рядом введем следующую формулу:
Это формула массивов, поэтому ввести ее нужно комбинацией Ctrl + Shift + Enter.
Теперь, меняя название группы, можно без всяких фильтров и сводных таблиц видеть максимальное значение внутри этой группы.
Как это работает? Очень просто. Первым делом нужно указать диапазон, который будет использоваться в качестве аргумента функции МАКС, то есть только те ячейки, которые соответствуют указанной группе. Так как мы заранее позаботились об удобстве использования функции, то название группы указали не внутри формулы, а в отдельной ячейке (гораздо легче менять группу). Тогда формула для нужного диапазона выглядит так.
Указанное выражение отбирает только те значения, для которых название группы совпадает с условием в ячейке D2. Вот, как это видит Excel
На следующем этапе укажем функцию МАКС, аргументом которой выступает полученный выше массив. Excel воспринимает примерно так.
Видно, что максимальное значение внутри массива равно 31. Его и мы и увидим в ячейке с формулой. Нужно только не забыть итоговую функцию ввести комбинацией клавиш Ctrl + Shift + Enter, иначе ничего не получится. В строке формул формула массива отображается внутри фигурных скобок. Добавляются сами, специально дорисовывать не нужно.
Если функцию МАКС заменить на МИН, то по указанному условию (названию группы) будет выдаваться минимальное значение.
Функции Excel 2016 МАКСЕСЛИ (MAXIFS) и МИНЕСЛИ (MINIFS)
Есть ряд значений, каждое из которых входит в некоторую группу. Нужно рассчитать максимальное значение по группе А. Используем формулу МАКСЕСЛИ.
Все очень просто. Как и у СУММЕСЛИМН вначале указываем диапазон, где находится искомое максимальное значение (колонка В), затем диапазон с критериями (колонка А) и далее сам критерий (в ячейке D2). Можно указать сразу несколько условий. Таким же способом легко рассчитать минимальное значение по условию. Найдем, к примеру, минимум внутри группы Б.
Ниже показан ролик, как рассчитать максимальное и минимальное значение по условию.
Подcвечивать максимум (минимум) автоматически
В стандартных инструментах условного форматирования EXCEL отсутствует возможность АВТОМАТИЧЕСКИ форматировать только те ячейки, которые содержат максимальные или минимальные значения.
Для того, чтобы это сделать, необходимо создать определенное выражение (формулу), результатом которого будет являться либо пара ИСТИНА/ЛОЖЬ , либо значения 1/0 и поместить его в модуль настройки условного форматирования.
Для лучшего понимания, разберем выражение в отдельной ячейке, а потом перенесем его в модуль.
В результате нам необходимо «подсветить» ячейку с наибольшим среднедневным потреблением кофе в странах, измеренным в чашках на одного человека:
Для этого:
1. Будем использовать вариант с результатом ИСТИНА/ЛОЖЬ , и в ячейку B1 (может быть любая другая) внесем следующее выражение:
=B3=МАКС($B$3:$B$35)
B3 – первая проверяемая на максимум ячейка, она будет изменяться после прменения условного форматирования к диапазону.
=B3=МАКС($B$3:$B$35) – фукнция максимума, выдающая (или как говорят – «возвращающая») максимум из диапазон, содержащего все значения к-ва чашек.
Фиксация необходима для того, чтобы адреса диапазонов не «съехали» при применении форматирования на весь диапазон.
Скопируем полностью выражение из строки формул в буфер обмена.
2. Выделим весь диапазон со значениями (от B3 до B35), и на вкладке «Главная», нажмем кнопку «Условное форматирование»
3. В открывшемся меню выберем «Создать правило» .
4. В типе правила выберем «Использовать формулу для определения форматируемых ячеек»
5. В поле формул вставим скопированное выражение и, нажав кнопку «Формат» настроим желаемое форматирование максимального значения (в нашем случае – это будет заливка желтым).
6. Сохраним изменения в каждом окне, нажав на «ОК» .
7. В результате максимальное значение будет подсвечено желтым цветом.
Выражение в условном форматировании проверяет равенство выбранной ячейки максимуму из диапазона, и если вдруг она будет равна максимуму – выдаст результат «ИСТИНА», что в свою очередь даст сигнал включения условного форматирования, которое мы настроили.
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее "перевести")
Читайте также: