Функция Excel СРЗНАЧЕСЛИМН с несколькими критериями

В этом руководстве показано, как использовать функцию Excel СРЗНАЧЕСЛИМН для расчета среднего значения с несколькими условиями.

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

Функция СРЗНАЧЕСЛИ в Excel

Функция Excel СРЗНАЧЕСЛИМН вычисляет среднее арифметическое всех ячеек в диапазоне, которые соответствуют заданным критериям.

Синтаксис следующий:

СРЗНАЧЕСЛИМН(диапазон_средних,диапазон_критериев1, критерий1, [criteria_range2, criteria2]…)

Где:

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

Критерии_диапазон1 / критерии1 обязательны, последующие необязательны. В одной формуле можно использовать от 1 до 127 пар диапазон/критерий.

Функция СРЗНАЧЕСЛИМН доступна в Excel 2007 — Excel 365.

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

Функция СРЗНАЧЕСЛИМН – примечания по использованию

Чтобы получить четкое представление о том, как работает функция, и избежать ошибок, обратите внимание на следующие факты:

• В аргументе range_range игнорируются пустые ячейки, логические значения TRUE/FALSE и текстовые значения. Нулевые значения включены.
• Если критерием является пустая ячейка, она рассматривается как нулевое значение.
• Если средний_диапазон не содержит ни одного числового значения, #DIV/0! возникает ошибка.
• Если ни одна ячейка не соответствует всем указанным критериям, #DIV/0! возвращается ошибка.
• Критерии AVERAGEIFS могут применяться как к одному диапазону, так и к разным диапазонам.
• Каждый критерий_диапазон должен иметь тот же размер и форму, что и средний_диапазон, иначе #VALUE! возникает ошибка.

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

формула Excel СРЗНАЧЕСЛИМН

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

1. В первом аргументе укажите диапазон, который вы хотите усреднить.
2. В последующих аргументах укажите пары диапазон/критерий. Пары могут быть расположены в любом порядке, но критерии всегда соответствуют диапазону, к которому они относятся.
3. Формула СРЗНАЧЕСЛИМН всегда должна содержать нечетное число аргументов: среднее_диапазон + одна или несколько пар критерии_диапазон/критерий.

СРЗНАЧСЛИМН с текстовыми критериями

Чтобы получить среднее значение чисел в одном столбце, если другой столбец (столбцы) содержит определенный текст, используйте этот текст для критериев.

В качестве примера найдем среднее значение продаж «Apple» в регионе «Север». Для этого составим формулу СРЗНАЧЕСЛИМН с двумя критериями:

• Average_range — это C3:C15 (ячейки усредняются).
• Критерий_диапазон1 — это A3:A15 (элементы для проверки), а критерий 1 — «яблоко».
• Критерий_диапазон2 — B3:B15 (регионы для проверки), а критерий2 — «север».

Сложив аргументы вместе, получим следующую формулу:

=СРЗНАЧЕСЛИ(C3:C15, A3:A15, “яблоко”, B3:B15, “север”)

С критериями в предопределенных ячейках (F3 и F4) формула принимает следующий вид:

=СРЗНАЧЕСЛИ(C3:C15, A3:A15, F3, B3:B15, F4)

СРЗНАЧЕСЛИ с логическими операторами

Когда критерий по умолчанию равен «равно», знак равенства можно опустить, и вы просто помещаете целевой текст (заключенный в кавычки) или число (без кавычек) в соответствующий аргумент, как показано в предыдущем примере.

При использовании других логических операторов, таких как «больше» (>), «меньше» (<), не равно (<>) и других с числом или датой, вы заключаете всю конструкцию в двойные кавычки.

Например, для средних продаж, превышающих ноль, до 1 октября 2022 года, используется следующая формула:

=СРЗНАЧЕСЛИ(C3:C15, B3:B15, “<1.10.2022", C3:C15, ">0″)

Когда критерии находятся в отдельных ячейках, вы заключаете логический оператор в кавычки и объединяете его со ссылкой на ячейку с помощью амперсанда (&). Например:

=СРЗНАЧЕСЛИ(C3:C15, B3:B15, “<"&F3, C3:C15, ">“&F4)

СРЗНАЧЕСЛИ с подстановочными знаками

Чтобы усреднить ячейки на основе частичного совпадения текста, используйте подстановочные знаки в критериях — вопросительный знак (?) для соответствия любому отдельному символу или звездочке.

для соответствия любому количеству символов.

В приведенной ниже таблице предположим, что вы хотите усреднить “оранжевые” продажи во всех “южных” регионах, включая “юго-запад” и “юго-восток”. Для этого включим звездочку во второй критерий:

=СРЗНАЧЕСЛИ(C3:C15, A3:A15, F3, B3:B15, “юг*”)

Если в ячейку вводится критерий частичного совпадения текста, соедините подстановочный знак со ссылкой на ячейку. В нашем случае формула принимает такой вид:

Формула с подстановочными знаками AVERAGEIFS на основе частичного совпадения текста

Среднее, если между двумя значениями

Чтобы получить среднее значение значений, попадающих между двумя конкретными значениями, используйте одну из следующих общих формул:

Среднее между двумя значениями включительно:

СРЗНАЧЕСЛИМН(диапазон_средних,диапазон_критериев,”>=значение1″,диапазон_критериев,”<=значение2")

Среднее, если между двумя значениями, исключая:

СРЗНАЧЕСЛИМН(диапазон_средних,диапазон_критериев,”>значение1″,диапазон_критериев,”<значение2")

В первой формуле используются логические операторы больше или равно (>=) и меньше или равно (<=), поэтому граничные значения включаются в среднее значение.

Во второй формуле логические критерии больше (>) и меньше (<) исключают граничные значения из среднего.

Эти формулы хорошо работают или в обоих сценариях — когда ячейки для усреднения и ячейки для проверки находятся в одном и том же столбце или в двух разных столбцах.

Например, чтобы рассчитать среднее значение продаж от 100 до 130 включительно, вы можете использовать эту формулу:

=СРЗНАЧЕСЛИ(C3:C15, C3:C15, “>=100”, C3:C15, “<=130")

С граничными значениями в ячейках E3 и F3 формула принимает следующий вид:

=СРЗНАЧЕСЛИ(C3:C15, C3:C15, “>=”&E3, C3:C15, “<="&F3)

Среднее, если между двумя значениями.

Чтобы усреднить ячейки в заданном столбце, если значения в другом столбце находятся между двумя значениями, укажите другой диапазон для аргументов Average_range и критерии_диапазона.

Например, чтобы усреднить продажи в столбце C, если дата в столбце B находится между 1 сентября и 30 октября, используйте следующую формулу:

=СРЗНАЧЕСЛИ(C3:C15, B3:B15, “>=01.09.2022”, B3:B15, “<=30.10.2022")

Со ссылками на ячейки:
=”&E3, B3:B15, “<="&F3)" title="Усреднение ячеек в заданном столбце, если значения в другом столбце находятся между двумя значениями."/>

Усреднение ячеек в заданном столбце, если значения в другом столбце находятся между двумя значениями.

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

Практическая рабочая тетрадь Функция СРЗНАЧЕСЛИ в Excel – примеры

(файл .xlsx)

Вас также могут заинтересовать