Резюме
Функция Excel СРЗНАЧЕСЛИМН вычисляет среднее число чисел в диапазоне, удовлетворяющем одному или нескольким критериям. Критерии, используемые для СРЗНАЧЕСЛИМН, могут включать логические операторы (>, <,, =) и символы подстановки (*,?) Для частичного совпадения.
Цель
Средние ячейки, соответствующие нескольким критериямВозвращаемое значение
Среднее значение ячеек, соответствующих всем критериямСинтаксис
= СРЕДНЕЛИМН (средн_значение; диапазон1; критерий1; (диапазон2); (критерий2);…)Аргументы
- avg_rng - средний диапазон.
- диапазон1 - первый диапазон для оценки.
- критерий1 - критерий для использования в диапазоне1.
- диапазон2 - (необязательно) Второй диапазон для оценки.
- критерий2 - (необязательно) критерий для использования в диапазоне2.
Версия
Excel 2007Примечания по использованию
Функция СРЗНАЧЕСЛИМН вычисляет среднее значение чисел в диапазоне, удовлетворяющем заданным критериям. Первый диапазон, передаваемый в СРЗНАЧЕСЛИМН, - это диапазон, содержащий числа для усреднения. Критерии предоставляются парами (диапазон, критерии), и требуется только первая пара. Для каждого дополнительного критерия укажите другую пару диапазон / критерий. Критерии, заданные для СРЗНАЧЕСЛИМН, применяются с логикой И - все критерии должны быть истинными, чтобы число можно было включить в среднее.
Критерии могут быть представлены в виде чисел, строк или ссылок. Например, допустимые критерии могут быть 10, «> 10», A1 или «<» & A1. Критерии также могут включать подстановочные знаки для частичного соответствия.
СРЗНАЧЕСЛИМН входит в группу из восьми функций Excel, которые разделяют логические критерии на две части (диапазон + критерий). В результате синтаксис, используемый для построения критериев, отличается, а для параметра AVERAGEIFS требуется диапазон ячеек для аргументов диапазона - вы не можете использовать массив.
AVERAGEIFS автоматически игнорирует пустые ячейки, даже если критерии совпадают. Другими словами, СРЗНАЧЕСЛИМН не будет включать в среднее значение пустые ячейки. AVERAGEIFS возвращает # DIV / 0! если ни одна ячейка не соответствует критериям.
Примеры
В показанном примере формулы в H5: H7:
=AVERAGEIFS(C5:C15,C5:C15,">0") =AVERAGEIFS(C5:C15,C5:C15,">0",C5:C15,"=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) // average values less than B1
Подстановочные знаки
В критериях можно использовать подстановочные знаки вопросительного знака (?), Звездочки (*) или тильды (~). Знак вопроса (?) Соответствует любому одному символу, а звездочка (*) соответствует нулю или более символов любого типа. Например, для усреднения значений в B1: B10, когда значения в A1: A10 содержат текст «красный», вы можете использовать следующую формулу:
=AVERAGEIFS(B1:B10,A1:A10,"*red*")
Тильда (~) - это escape-символ, позволяющий находить буквальные символы подстановки. Например, чтобы сопоставить буквальный знак вопроса (?), Звездочку (*) или тильду (~), добавьте тильду перед подстановочным знаком (т.е. ~ ?, ~ *, ~~).
Примечание: порядок аргументов в СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИ разный. Диапазон средних значений всегда является первым аргументом в СРЗНАЧЕСЛИМН.
Заметки
- Если никакие данные не соответствуют критериям, СРЗНАЧЕСЛИМН возвращает # ДЕЛ0! ошибка
- Каждый дополнительный диапазон должен иметь такое же количество строк и столбцов, что и средний_ диапазон .
- Нечисловые критерии должны быть заключены в двойные кавычки, а числовые - нет. Например: 100, «100», «> 32», «jim» или A1 (где A1 содержит число).
- Подстановочные знаки? и * можно использовать в критериях. Знак вопроса соответствует любому одному символу, а звездочка соответствует нулю или нескольким символам любого типа.
- Чтобы найти буквальный вопросительный знак или звездочку, используйте тильду (~) перед вопросительным знаком или звездочкой (т.е. ~ ?, ~ *).