Как использовать функцию Excel СРЗНАЧЕСЛИМН -

Содержание

Резюме

Функция 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")

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

  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) // average values less than B1

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

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

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

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

Примечание: порядок аргументов в СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИ разный. Диапазон средних значений всегда является первым аргументом в СРЗНАЧЕСЛИМН.

Заметки

  • Если никакие данные не соответствуют критериям, СРЗНАЧЕСЛИМН возвращает # ДЕЛ0! ошибка
  • Каждый дополнительный диапазон должен иметь такое же количество строк и столбцов, что и средний_ диапазон .
  • Нечисловые критерии должны быть заключены в двойные кавычки, а числовые - нет. Например: 100, «100», «> 32», «jim» или A1 (где A1 содержит число).
  • Подстановочные знаки? и * можно использовать в критериях. Знак вопроса соответствует любому одному символу, а звездочка соответствует нулю или нескольким символам любого типа.
  • Чтобы найти буквальный вопросительный знак или звездочку, используйте тильду (~) перед вопросительным знаком или звездочкой (т.е. ~ ?, ~ *).

Интересные статьи...