В этом руководстве показано, как использовать Отлel MAXяФункции FS и MINIFSтионы в Excel для подсчета данных, соответствующих определенным критериям.
MAXIFS Обзор функций
Вы можете использовать функцию MAXIFS в Excel для подсчета ячеек, содержащих определенное значение, подсчета ячеек, которые больше или равны значению и т. Д.
(Обратите внимание, как появляются входные данные формулы)
MAXIFS Синтаксис и аргументы функции:
= MAXIFS (макс_диапазон; диапазон1; критерий1; [диапазон2]; [критерий2];…)
max_range - Диапазон значений, используемых для определения максимума.
диапазон1 - Первый диапазон для оценки.
критерии1 - Критерии для использования в диапазоне1.
диапазон2 - [необязательно] Второй диапазон для оценки.
диапазон2 - [необязательно] Второй диапазон для оценки.
Что такое функции MAXIFS и MINIFS?
В то время как Microsoft смогла на раннем этапе предоставить нам способ найти сумму или подсчет диапазона ячеек на основе критериев, MAXIFS и MINIFS появились позже в жизни электронных таблиц. К счастью, теперь они у нас есть, и они очень похожи по структуре и использованию. Проще говоря, две функции могут сканировать диапазон ячеек, проверяя определенный критерий, а затем выдавать максимальное или значений в диапазоне, которые соответствуют этим значениям. Поскольку они были созданы после большого обновления 2007 года, более старые «MAXIF / MINIF» не о чем беспокоиться.
Если вы еще этого не сделали, вы можете просмотреть большую часть аналогичной структуры и примеры в статье СЧЁТЕСЛИМН.
Базовый пример
Рассмотрим эту таблицу:
Если мы хотим узнать максимальное количество собранных бананов, мы можем использовать функцию MAXIFS. Мы всегда будем указывать диапазон чисел, из которого мы хотим вернуть результат, в качестве первого аргумента. Затем мы перечислим диапазоны критериев и соответствующие им критерии. В нашем примере мы можем поместить эту формулу в D2:
= МАКСЕСЛИМН (B2: B7; A2: A7; D1)
Точно так же, если мы хотим найти наименьшее / минимальное количество собранных бананов, мы можем просто изменить имя функции, оставив остальные без изменений.
= МИНИМНЕС (B2: B7; A2: A7; D1)
В оставшейся части статьи мы будем стараться приводить примеры с использованием MAXIFS или MINIFS, а не перечислять и то и другое каждый раз. Просто помните, что вы можете легко переключаться между ними в зависимости от того, какого результата вы пытаетесь достичь.
Работа с датами, несколько критериев
При работе с датами в электронной таблице, хотя можно ввести дату непосредственно в формулу, лучше всего иметь дату в ячейке, чтобы вы могли просто ссылаться на ячейку в формуле. Например, это помогает компьютеру понять, что вы хотите использовать дату 27.05.2020, а не число 5, разделенное на 27, разделенное на 2022 год.
Давайте посмотрим на нашу следующую таблицу, в которой регистрируется количество посетителей сайта каждые две недели.
Мы можем указать начальную и конечную точки диапазона, который мы хотим посмотреть в D2 и E2. Наша формула для поиска максимального количества посетителей в этом диапазоне может быть следующей:
= MAXIFS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2)
Обратите внимание, как мы смогли объединить сравнения «=» со ссылками на ячейки для создания критериев. Кроме того, хотя оба критерия применялись к одному и тому же диапазону ячеек (A2: A7), вам необходимо записать диапазон дважды, по одному разу для каждого критерия.
Несколько столбцов
При использовании нескольких критериев вы можете применить их к тому же диапазону, что и в предыдущем примере, или вы можете применить их к разным диапазонам. Давайте объединим наши образцы данных в эту таблицу:
Мы настроили несколько ячеек, чтобы пользователь мог вводить то, что он хочет искать, в ячейках с E2 по G2. Таким образом, нам нужна формула, которая суммирует наименьшее количество яблок, собранных в феврале. Наша формула выглядит так:
= MINIFS (C2: C7, B2: B7, «> =» & F2, B2: B7, «<=» & G2, A2: A7, E2)
MAXIFS / MINIFS с логикой типа ИЛИ
До этого момента все примеры, которые мы использовали, основывались на сравнении на основе И, когда мы ищем строки, соответствующие всем нашим критериям. Теперь рассмотрим случай, когда вы хотите найти возможность строки, соответствующей тому или иному критерию.
Давайте посмотрим на этот список продаж:
Мы хотим найти максимальные продажи для Адама и Боба. Самый простой - взять два MAXIFS, а затем взять MAX обеих этих функций.
= МАКСЕСЛИМН (B2: B7; A2: A7; «Адам»); МАКСЕСЛИМН (B2: B7; A2: A7; «Боб»))
Здесь компьютер подсчитывает наши индивидуальные баллы, а затем мы складываем их.
Наш следующий вариант подходит, когда у вас больше диапазонов критериев, так что вам не нужно повторно переписывать всю формулу. В предыдущей формуле мы вручную указали компьютеру вычислить два разных MAXIFS. Однако вы также можете сделать это, записав свои критерии внутри массива, например:
= MAX (MAXIFS (B2: B7; A2: A7; {"Адам", "Боб"}))
Посмотрите, как построен массив в фигурных скобках. Когда компьютер вычисляет эту формулу, он будет знать, что мы хотим вычислить функцию MAXIFS для каждого элемента в нашем массиве, создав таким образом массив чисел. Затем внешняя функция MAX возьмет этот массив чисел и превратит его в одно число. Пошаговая оценка формулы будет выглядеть так:
= MAX (MAXIFS (B2: B7, A2: A7, {"Адам", "Боб"})) = MAX (14548, 24956) = 24956
Получаем тот же результат, но формулу удалось записать более лаконично.
Работа с пробелами
Иногда в вашем наборе данных есть пустые ячейки, которые вам нужно либо найти, либо избежать. Настройка критериев для них может быть немного сложной, поэтому давайте рассмотрим другой пример.
Обратите внимание, что ячейка A3 действительно пуста, а в ячейке A5 есть формула, возвращающая строку нулевой длины «». Если мы хотим найти общее среднее значение действительно пустые ячейки, мы использовали бы критерий «=», и наша формула выглядела бы так:
= МАКСЕСЛИМН (B2: B7; A2: A7; "=")
С другой стороны, если мы хотим получить среднее значение для всех ячеек, которые визуально выглядят пустыми, мы изменим критерий на «», и формула будет выглядеть так:
= МАКСЕСЛИМН (B2: B7; A2: A7; "")
Давайте перевернем: что, если вы хотите найти среднее значение непустых ячеек? К сожалению, текущий дизайн не позволяет избежать строки нулевой длины. Вы можете использовать критерий «», но, как вы можете видеть в примере, он по-прежнему включает значение из строки 5.
= МАКСЕСЛИМН (B2: B7; A2: A7; "")
Если вам нужно не подсчитывать ячейки, содержащие строки нулевой длины, вы можете рассмотреть возможность использования функции LEN внутри СУММПРОИЗВ.
MAXIFS и MINIIFS в Google Таблицах
Функция MAXIFS и MINIIFS работает в Google Таблицах точно так же, как и в Excel:
