Функция AVERAGEIF в Excel

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

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

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

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

Число, представляющее среднее значение.

Аргументы

• range — одна или несколько ячеек, включая числа или имена, массивы или ссылки.
• criteria — число, выражение, ссылка на ячейку или текст.
• Average_range — [необязательный] Ячейки для усреднения. Если этот параметр опущен, используется диапазон.

Синтаксис

=AVERAGEIF(range, criteria, [аverage_range])

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

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

Синтаксис

Общий синтаксис для AVERAGEIF выглядит следующим образом:

=AVERAGEIF(range,criteria,average_range)

Функция AVERAGEIF принимает три аргумента: диапазон, критерий и средний_диапазон. Диапазон — это диапазон ячеек, к которым применяется условие. Критерий — это условие применения, а также любые необходимые логические операторы. Аргумент Average_range не является обязательным. Если средний_диапазон не указан, СРЗНАЧЕСЛИ будет усреднять значения в аргументе диапазона. Если  указан  средний_диапазон, AVERAGEIF будет усреднять значения в average_range. При использовании СРЗНАЧЕСЛИ имейте в виду следующее:

• Не будет включать пустые ячейки в среднее значение, даже если критерии совпадают.
• Вернет #DIV/0! ошибка, если ни одна ячейка не соответствует критериям.
• Критерии могут включать логические операторы (>,<,<>,=) и подстановочные знаки (*,?) для частичного соответствия.
• СРЗНАЧЕСЛИ может применять только один  критерий.

Критерии

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

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

Примеры

В показанном примере формулы в H5:H8 выглядят следующим образом:

=AVERAGEIF(C5:C15,">0") // цена больше, чем $0
=AVERAGEIF(C5:C15,">200000") // цена превышает 200 тысяч
=AVERAGEIF(D5:D15,">=2",C5:C15) // 2+ спальни
=AVERAGEIF(D5:D15,">=3",C5:C15) // 3+ спальни

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

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

=AVERAGEIF(D5:D15,2,C5:C15) // 2 спальни
=AVERAGEIF(D5:D15,">=2",C5:C15) // 2+ спальни

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

=AVERAGEIF(A1:A10,"red",B1:B10) // только средний "красный"

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

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

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

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

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

=AVERAGEIF(A1:A10,"*red*",B1:B10) // содержит "красный"

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

Внимание: средний диапазон

AVERAGEIF делает определенные предположения о размере  среднего_диапазона, по существу изменяя его размер, когда необходимо, чтобы он соответствовал аргументу диапазона, используя верхнюю левую ячейку диапазона в качестве начала координат. В некоторых случаях такое поведение может привести к результату, который кажется разумным, но на самом деле является неверным.

Примечания

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