В этом руководстве показано, как использовать функции Excel AVERAGEIF и AVERAGEIFS в Excel и Google Sheets для усреднения данных, соответствующих определенным критериям.
Обзор функции СРЗНАЧЕСЛИ
Вы можете использовать функцию СРЗНАЧЕСЛИ в Excel для подсчета ячеек, содержащих определенное значение, подсчета ячеек, которые больше или равны значению и т. Д.
Чтобы использовать функцию рабочего листа Excel СРЗНАЧЕСЛИ, выберите ячейку и введите:
(Обратите внимание, как появляются входные данные формулы)
Синтаксис и аргументы функции СРЗНАЧЕСЛИ:
= СРЗНАЧЕСЛИ (диапазон; критерии; [средний_ диапазон])
диапазон - Диапазон подсчитываемых ячеек.
критерии - Критерии, определяющие, какие ячейки следует подсчитывать.
средний_ диапазон - [необязательно] Ячейки для усреднения. Если не указано, используется диапазон.
Что такое функция СРЗНАЧЕСЛИ?
Функция СРЗНАЧЕСЛИ - одна из старых функций, используемых в электронных таблицах. Он используется для сканирования диапазона ячеек, проверяя определенный критерий, а затем выдает среднее значение (также называемое математическим средним) для значений в диапазоне, которые соответствуют этим значениям. Исходная функция СРЗНАЧЕСЛИ ограничивалась только одним критерием. После 2007 года была создана функция СРЗНАЧЕСЛИМН, которая позволяет использовать множество критериев. В основном они используются одинаково, но есть некоторые важные различия в синтаксисе, которые мы обсудим в этой статье.
Если вы еще этого не сделали, вы можете просмотреть большую часть аналогичной структуры и примеры в статье СЧЁТЕСЛИМН.
Базовый пример
Давайте рассмотрим этот список зарегистрированных продаж, и мы хотим знать средний доход.
Поскольку у нас были расходы, отрицательное значение, мы не можем просто вычислить базовое среднее значение. Вместо этого мы хотим усреднить только те значения, которые больше 0. «Больше 0» - это то, что будет нашим критерием в функции СРЗНАЧЕСЛИ. Наша формула для утверждения, что это
= СРЗНАЧЕСЛИ (A2: A7; "> 0")
Пример с двумя столбцами
Хотя исходная функция СРЗНАЧЕСЛИ была разработана, чтобы позволить вам применить критерий к диапазону чисел, которые вы хотите суммировать, большую часть времени вам потребуется применить один или несколько критериев к другим столбцам. Рассмотрим эту таблицу:
Теперь, если мы воспользуемся исходной функцией СРЗНАЧЕСЛИ, чтобы узнать, сколько бананов у нас в среднем. Мы поместим наши критерии в ячейку D1, и нам нужно будет указать диапазон, который мы хотим в среднем в качестве последнего аргумента, и поэтому наша формула будет
= СРЗНАЧЕСЛИ (A2: A7; D1; B2: B7)
Однако, когда программисты в конце концов поняли, что пользователи хотят давать более одного критерия, была создана функция СРЗНАЧЕСЛИМН. Чтобы создать одну структуру, которая будет работать для любого количества критериев, для СРЕДНЕЛИН необходимо, чтобы диапазон сумм был указан первым. В нашем примере это означает, что формула должна быть
= СРЕДНЕЛИН (B2: B7; A2: A7; D1)
ПРИМЕЧАНИЕ. Эти две формулы дают одинаковый результат и могут выглядеть примерно одинаково, поэтому обратите особое внимание на то, какая функция используется, чтобы убедиться, что вы перечислили все аргументы в правильном порядке.
Работа с датами, несколько критериев
При работе с датами в электронной таблице, хотя можно ввести дату непосредственно в формулу, лучше всего иметь дату в ячейке, чтобы вы могли просто ссылаться на ячейку в формуле. Например, это помогает компьютеру понять, что вы хотите использовать дату 27.05.2020, а не число 5, разделенное на 27, разделенное на 2022 год.
Давайте посмотрим на нашу следующую таблицу, в которой регистрируется количество посетителей сайта каждые две недели.
Мы можем указать начальную и конечную точки диапазона, который мы хотим посмотреть в D2 и E2. Наша формула для определения среднего числа посетителей в этом диапазоне может быть:
= AVERAGEIFS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2)
Обратите внимание, как мы смогли объединить сравнения «=» со ссылками на ячейки для создания критериев. Кроме того, хотя оба критерия применялись к одному и тому же диапазону ячеек (A2: A7), вам необходимо записать диапазон дважды, по одному разу для каждого критерия.
Несколько столбцов
При использовании нескольких критериев вы можете применить их к тому же диапазону, что и в предыдущем примере, или вы можете применить их к разным диапазонам. Давайте объединим наши образцы данных в эту таблицу:
Мы настроили несколько ячеек, чтобы пользователь мог вводить то, что он хочет искать, в ячейках с E2 по G2. Таким образом, нам нужна формула, которая суммирует общее количество яблок, собранных в феврале. Наша формула выглядит так:
= AVERAGEIFS (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2)
AVERAGEIFS с логикой типа ИЛИ
До этого момента все примеры, которые мы использовали, основывались на сравнении на основе И, когда мы ищем строки, соответствующие всем нашим критериям. Теперь рассмотрим случай, когда вы хотите найти возможность строки, соответствующей тому или иному критерию.
Давайте посмотрим на этот список продаж:
Мы хотели бы сложить средние продажи для Адама и Боба. Во-первых, быстрое обсуждение усреднения. Если у вас нечетное количество вещей, например 3 записи для Адама и 2 для Боба, вы не можете просто взять среднее значение продаж каждого человека. Это называется усреднением средних значений, и в итоге вы придаете несправедливый вес элементу, в котором мало записей. Если это так с вашими данными, вам нужно будет вычислить среднее значение «вручную»: возьмите сумму всех ваших товаров, разделенную на количество ваших товаров. Чтобы узнать, как это сделать, вы можете ознакомиться со статьями здесь:
Теперь, если количество записей такое же, как в нашей таблице, у вас есть несколько вариантов, которые вы можете сделать. Самый простой - сложить два СРЗНАЧЕСЛИМН вместе, вот так, а затем разделить на 2 (количество элементов в нашем списке).
= (СРЕДНЕНОМН (B2: B7; A2: A7; «Адам») + СРЕДНЕМН (B2: B7; A2: A7; «Боб»)) / 2
Здесь компьютер подсчитывает наши индивидуальные баллы, а затем мы складываем их.
Наш следующий вариант подходит, когда у вас больше диапазонов критериев, так что вам не нужно повторно переписывать всю формулу. В предыдущей формуле мы вручную приказали компьютеру сложить два разных СРЕДНЕГО РАЗМНЕНИЯ. Однако вы также можете сделать это, записав свои критерии внутри массива, например:
= СРЕДНИЙ (СРЕДНЕЛИ (B2: B7; A2: A7; {"Адам"; "Боб"}))
Посмотрите, как построен массив в фигурных скобках. Когда компьютер вычисляет эту формулу, он будет знать, что мы хотим вычислить функцию СРЗНАЧЕСЛИМН для каждого элемента в нашем массиве, создав таким образом массив чисел. Затем внешняя функция AVERAGE возьмет этот массив чисел и превратит его в одно число. Пошаговая оценка формулы будет выглядеть так:
= СРЕДНИЙ (СРЕДНИЙ (B2: B7, A2: A7, {"Адам", "Боб"})) = СРЕДНИЙ (13701, 21735) = 17718
Получаем тот же результат, но формулу удалось записать более лаконично.
Работа с пробелами
Иногда в вашем наборе данных есть пустые ячейки, которые вам нужно либо найти, либо избежать. Настройка критериев для них может быть немного сложной, поэтому давайте рассмотрим другой пример.
Обратите внимание, что ячейка A3 действительно пуста, а в ячейке A5 есть формула, возвращающая строку нулевой длины «». Если мы хотим найти общее среднее значение действительно пустые ячейки, мы использовали бы критерий «=», и наша формула выглядела бы так:
= СРЗНАЧЕСЛИМН (B2: B7; A2: A7; "=")
С другой стороны, если мы хотим получить среднее значение для всех ячеек, которые визуально выглядят пустыми, мы изменим критерий на «», и формула будет выглядеть так:
= СРЗНАЧЕСЛИМН (B2: B7; A2: A7; "")
Давайте перевернем: что, если вы хотите найти среднее значение непустых ячеек? К сожалению, текущий дизайн не позволяет избежать строки нулевой длины. Вы можете использовать критерий «», но, как вы можете видеть в примере, он по-прежнему включает значение из строки 5.
= СРЗНАЧЕСЛИМН (B2: B7; A2: A7; "")
Если вам нужно не подсчитывать ячейки, содержащие строки нулевой длины, вы можете рассмотреть возможность использования функции LEN внутри СУММПРОИЗВ.
AVERAGEIF в Google Таблицах
Функция СРЗНАЧЕСЛИ работает в Google Таблицах точно так же, как и в Excel:
