Функция 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 поддерживает логические операторы (>,<,<>,=) и подстановочные знаки (*,?) для частичного сопоставления. Поскольку СРЗНАЧЕСЛИ входит в группу из восьми функций, которые разделяют логические критерии на две части, синтаксис немного сложен. Диапазон и критерии предоставляются отдельно, а операторы в критериях необходимо заключать в двойные кавычки («»). В таблице ниже показаны некоторые распространенные примеры:
Цель | Критерии |
---|---|
Ячейки больше 75 | «>75» |
Ячейки равны 100 | 100 или «100» |
Ячейки меньше или равны 100 | «<=100» |
Ячейки, равные «красному» | «красный» |
Ячейки, не равные «красному» | «<>красный» |
Ячейки пустые «» | «» |
Ячейки, которые не пусты | «<>» |
Ячейки, начинающиеся с «X» | «Икс*» |
Ячейки меньше A1 | «<«&A1 |
Клеток меньше, чем сегодня | «<«&СЕГОДНЯ() |
Обратите внимание, что в последних двух примерах используется конкатенация с символом амперсанда (&). Если аргумент критерия включает значение из другой ячейки или результат формулы, логические операторы, такие как «<», должны быть объединены конкатенацией. Это связано с тем, что 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 используется в качестве отправной точки, а ячейки, соответствующие ячейкам в диапазоне, усредняются.
- Функция поддерживает подстановочные знаки, но не учитывает регистр.