Функция AVERAGEIFS в Excel

Функция в AVERAGEIFS (СРЗНСЕСЛИ) в Excel возвращает среднее значение ячеек, соответствующих нескольким условиям, называемым критериями. Для определения критериев AVERAGEIFS поддерживает логические операторы (>,<,<>,=) и подстановочные знаки (*,?,~) и может использоваться с ячейками, содержащими даты, числа и текст.

Что делает функция Excel в AVERAGEIFS?

Функция Excel в AVERAGEIFS возвращает среднее знаечение ячейки, соответствующие нескольким критериям.

Возвращаемое значение

Среднее значение ячеек, соответствующих всем критериям.

Аргументы

• avg_rng — диапазон усреднения.
• range1 — первый диапазон для оценки.
• criteria1 — критерии для использования в диапазоне 1.
• range2 — [необязательно] Второй диапазон для оценки.
• criteria2 – [необязательно] Критерии, используемые в диапазоне2.

Синтаксис

=AVERAGEIFS(avg_rng, range1, criteria1, [range2], [criteria2], …)

Примечания по использованию

Функция СУММИФС вычисляет среднее значение ячеек в диапазоне, которые соответствуют нескольким условиям, называемым критериями. Для каждого условия предусмотрен отдельный диапазон и критерии . Для определения критериев AVERAGEIFS поддерживает различные логические операторы (>,<,<>,=) и подстановочные знаки (*,?,~).

Функция СРЗНАЧЕСЛИ — широко используемая функция в Excel, ее можно использовать для усреднения ячеек на основе дат, текстовых значений и чисел. Однако синтаксис, используемый для применения условий, немного сложен, поскольку он необычен для Excel.

Синтаксис

Синтаксис функции AVERAGEIFS зависит от оцениваемых критериев. Для каждого отдельного условия потребуется  диапазон и критерий. Общий синтаксис AVERAGEIFS выглядит следующим образом:

=AVERAGEIFS(avg_range,range1,criteria1) // 1 положение
=AVERAGEIFS(avg_range,range1,criteria1,range2,criteria2) // 2 положение

Первый аргумент, avg_range, — это диапазон ячеек для усреднения, который должен содержать числовые значения. Второй аргумент, range1, — это диапазон, к которому должно применяться первое условие. Третий аргумент, критерии1, содержит условие, которое должно быть применено к диапазону1, а также любые логические операторы. Дополнительные условия применяются путем предоставления дополнительных аргументов диапазона/критерия. При использовании AVERAGEIFS имейте в виду следующее:

• В окончательный результат будут включены только ячейки, соответствующие всем условиям.
• Все диапазоны должны быть одинакового размера, иначе AVERAGEIFS вернет #VALUE! ошибка.
• Функция не будет включать пустые ячейки в среднее значение, даже если критерии совпадают.
• СРЗНАЧЕСЛИ вернет #DIV/0! ошибка, если ни одна ячейка не соответствует критериям.

Применение критериев

Функция СРЗНАЧЕСЛИ поддерживает логические операторы (>,<,<>,=) и подстановочные знаки (*,?) для частичного сопоставления. Поскольку AVERAGEIFS входит в группу из восьми функций, которые разделяют логические критерии на две части, синтаксис немного сложен. Для каждого условия требуется отдельный диапазон и критерии, а операторы необходимо заключать в двойные кавычки («»). В таблице ниже показаны некоторые распространенные примеры:

Обратите внимание, что в последних двух примерах используется конкатенация с символом амперсанда (&). Если аргумент критерия включает значение из другой ячейки или результат формулы, логические операторы, такие как «<», должны быть объединены конкатенацией. Это связано с тем, что Excel необходимо сначала оценить ссылки на ячейки и формулы, чтобы получить значение, прежде чем это значение можно будет соединить с помощью оператора.

Примеры

В показанном примере формулы H5:H7 имеют вид:

=AVERAGEIFS(C5:C15,C5:C15,">0")
=AVERAGEIFS(C5:C15,C5:C15,">0",C5:C15,"<500000")
=AVERAGEIFS(C5:C15,D5:D15,">=2",E5:E15,">1")

Эти формулы возвращают среднюю цену недвижимости, где:

1. цены больше нуля.
2. цены больше нуля и меньше 500 000 долларов США.
3. в объектах недвижимости есть как минимум 2 спальни и более 1 ванной комнаты.

Двойные кавычки («») в критериях

Обычно текстовые значения в критериях заключаются в двойные кавычки («»), а числа — нет. Однако если с числом включен логический оператор , и число, и оператор должны быть заключены в кавычки. Обратите внимание на разницу в двух примерах ниже. Поскольку во второй формуле используется оператор «больше или равно» (>=), оператор и число заключены в двойные кавычки.

=AVERAGEIFS(C5:C15,D5:D15,2) // 2 bedrooms
=AVERAGEIFS(C5:C15,D5:D15,">=2") // 2+ bedrooms

Двойные кавычки также используются для текстовых значений. Например, чтобы усреднить значения в B1:B10, когда значения в A1:A10 равны «красному», вы можете использовать такую ​​формулу:

=AVERAGEIFS(B1:B10,A1:A10,"red")

Несколько критериев

Введите критерии парами [диапазон, критерии]. Например, чтобы усреднить значения в A1:A10, где B1:B10 = «A» и C1:C10 > 5, используйте:

=AVERAGEIFS(A1:A10,B1:B10,"A",C1:C10,">5")

Значение из другой ячейки

Значение из другой ячейки можно включить в критерии с помощью конкатенации. В приведенном ниже примере СРЗНАЧЕСЛИ вернет среднее значение чисел в A1:A10, которые меньше значения в ячейке B1. Обратите внимание, что оператор «меньше» (текстовый) заключен в кавычки.

=AVERAGEIFS(A1:A10,A1:A10,"<"&B1) // средние значения меньше, чем B1

Подстановочные знаки

В критериях можно использовать подстановочные знаки: вопросительный знак (?), звездочку (*) или тильду (~ ) . Знак вопроса (?) соответствует любому одному символу, а звездочка (*) соответствует нулю или более символам любого типа. Например, чтобы усреднить значения в B1:B10, когда значения в A1:A10 содержат текст «красный», вы можете использовать такую ​​формулу:

=AVERAGEIFS(B1:B10,A1:A10,"*red*")

Тильда (~) — это escape-символ, позволяющий находить буквальные подстановочные знаки. Например, чтобы соответствовать буквальному вопросительному знаку (?), звездочке (*) или тильде (~), добавьте тильду перед подстановочным знаком (т. е. ~?, ~*, ~~).

Примечание. Порядок аргументов в AVERAGEIFS и AVERAGEIF различен. Диапазон усреднения всегда является первым аргументом в AVERAGEIFS.

Примечания

• Все диапазоны должны быть одинакового размера, иначе AVERAGEIFS в эксель вернет #VALUE! ошибка.
• В окончательный результат будут включены только значения, соответствующие всем условиям.
• СРЗНАЧЕСЛИ не будет включать пустые ячейки в среднее значение, даже если критерии совпадают.
• Значения TRUE и FALSE игнорируются при вычислении среднего значения.
• Функция вернет #DIV/0! ошибка, если ни одна ячейка не соответствует критериям.
• Для AVERAGEIFS требуется диапазон, вы не можете заменить массив.
• Логические операторы и текстовые значения следует заключать в двойные кавычки («»).
• AVERAGEIFS поддерживает подстановочные знаки , но не учитывает регистр.