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