Функция СРЗНАЧЕСЛИ в Excel: как рассчитать среднее значение на основе многих условий

Функция СРЗНАЧ — это базовая функция Excel , используемая для расчета среднего значения в таблице данных. А при использовании расширенной функции СРЗНАЧ пользователи могут комбинировать ее для расчета различного контента, например функцию СРЗНАЧЕСЛИ для расчета условия среднего плюс 1 или функцию ДАВЕРАЖ . Сегодня мы узнаем о менее популярной, но чрезвычайно полезной функции усреднения — AVERAGEIFS.

СРЗНАЧЕСЛИ в Excel

• Что такое функция СРЗНАЧЕСЛИ?
• Формула функции СРЗНАЧЕСЛИ Excel
• Пример использования функции СРЗНАЧЕСЛИ в Excel

Что такое функция СРЗНАЧЕСЛИ?

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

Если вам интересно, чем функция СРЗНАЧЕСЛИ отличается от СРЗНАЧЕСЛИ, то она очень проста: СРЗНАЧЕСЛИ может вычислять только среднее значение ячеек, которые соответствуют одному условию. Если существует более одного условия, вы должны использовать его в сочетании с другой функцией или просто использовать функцию СРЗНАЧЕСЛИ.

В следующей части статьи будет представлена ​​формула функции СРЗНАЧЕСЛИ с конкретными примерами, чтобы вам было легче ее понять.

Формула функции СРЗНАЧЕСЛИ Excel

Функция СРЗНАЧЕСЛИ в Excel имеет формулу функции СРЗНАЧЕСЛИ(средний_диапазон, критерий_диапазон1, критерии1, [критерий_диапазон2, критерии2], ...) .

Там:

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

Обратите внимание при использовании функции:

• Если значение аргумента содержит логические значения или пустые ячейки, это значение будет игнорироваться.
• Если диапазон пустой или текстовый, функция возвращает значение ошибки #DIV/0.
• Если ячейки в Criteria_range пусты, функция по умолчанию равна 0.
• Если значение в поле данных является логическим значением = True, оно считается 1, если значение False считается 0.
• Если значения в Average_range не могут быть преобразованы в числа, будет сообщено об ошибке #DIV/0.
• Если значение, отвечающее всем условиям, отсутствует, функция сообщает об ошибке #ДЕЛ/0!.
• Можно использовать альтернативные символы, например *.?. в условиях усреднения.

Пример использования функции СРЗНАЧЕСЛИ в Excel

Мы возьмем пример с таблицей данных ниже.

Пример 1. Рассчитайте среднюю зарплату сотрудников с численностью более 3 000 000 и менее 7 000 000.

Из требований к вопросу мы видим, что область для расчета среднего значения составляет от D2 до D6 (D2:D6), условием для расчета среднего является заработная плата более 3 миллионов (D2:D6, «>3000000») и менее 6. миллион (D2:D6, «<7000000»). поэтому формула будет иметь вид "">

=СРЗНАЧЕСЛИ(D2:D6,D2:D6,">3000000",D2:D6,"<>

Нажмите Enter и отобразится средний результат данных на основе заданных условий. Расчетная средняя заработная плата составляет 5 433 333.

Пример 2: Рассчитайте среднюю зарплату и бонус сотрудника-мужчины, чья зарплата составляет 250 000.

Мы видим, что E2:E6 — это область, в которой необходимо вычислить значение, E2:E6, «250000» — это первое условие для значения в этой области. C2:C6, «Мужской» — второе условие с другим диапазоном данных. Тогда формула этой задачи будет:

=СРЗНАЧЕСЛИ(E2:E6,E2:E6,"250000",C2:C6,"Мужской")

При нажатии Enter результат будет 250 000, как показано ниже.

Пример 3: Рассчитайте среднюю зарплату сотрудников мужского пола более 200 000 и зарплату более 4 500 000.

Область данных, в которой необходимо вычислить среднее значение: E2:E6, E2:E6, «>200000» — первое условие в области данных E2:E6, C2:C6, «Мужской» — второе условие в данных области. C2:C6, D2:D6, «>4500000» — третье условие в области данных D2:D6. В поле ввода результата вводим формулу расчета:

=СРЗНАЧЕСЛИ(E2:E6,E2:E6,">200000",C2:C6,"Мужской",D2:D6,">4500000")

Нажмите Enter, и результат будет 375 000, как показано ниже. Потому что всего 2 сотрудника-мужчины соответствуют всем трем условиям требований.

Пример 4: Рассчитайте среднюю зарплату сотрудниц, чья зарплата превышает 7 500 000.

Вы также определяете область данных и условия, аналогичные трем примерам выше, и, наконец, у нас будет формула расчета с функцией СРЗНАЧЕСЛИ:

=СРЗНАЧЕСЛИ(D2:D6,D2:D6,">7500000",C2:C6,"Женский")

затем нажмите Enter.

Результатом будет ошибка #DIV0! Поскольку выполняется только одно условие в формуле, не существует ячейки, удовлетворяющей всем двум условиям в формуле.

Выше описано, как применить функцию СРЗНАЧЕСЛИ в Excel для расчета среднего значения при различных условиях. В общем, использование очень простое: сначала вам нужно ввести область расчета значений, а затем условия для каждой отдельной области данных.

Узнать больше:

• Как объединить функцию Sumif и функцию Vlookup в Excel
• Как использовать функцию поиска в Excel
• Как использовать функцию Vlookup в Excel
• Сводка тригонометрических функций в Excel
• Как автоматически отображать имена при вводе кода в Excel
• Как посчитать возраст в Excel

Желаю вам успехов!