Функция Excel СРЗНАЧЕСЛИ с примерами

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

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

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

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

СРЗНАЧЕСЛИ(диапазон, критерии, [average_range])

Всего у функции 3 аргумента — первые 2 обязательны, последний опционален:

• Диапазон (обязательно) — диапазон ячеек для проверки на соответствие критериям.
• Критерии (обязательно) — условие, определяющее, какие ячейки усреднять. Он может быть представлен в виде числа, логического выражения, текстового значения или ссылки на ячейку, например, 5, “>5”, “cat” или A2.
• Average_range (необязательно) — ячейки, которые вы действительно хотите усреднить. Если опущено, то диапазон будет усреднен.

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

Excel СРЗНАЧЕСЛИ – о чем нужно помнить!

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

• При расчете среднего значения пустые ячейки, текстовые значения и логические значения ИСТИНА и ЛОЖЬ игнорируются.
• Нулевые значения включены в среднее значение.
• Если ячейка критерия пуста, она рассматривается как нулевое значение (0).
• Если средний_диапазон содержит только пустые ячейки или текстовые значения, #DIV/0! возникает ошибка.
• Если ни одна ячейка в диапазоне не соответствует критериям, #DIV/0! возвращается ошибка.
• Аргумент Average_range не обязательно должен иметь тот же размер, что и диапазон. Однако фактические ячейки, подлежащие усреднению, определяются размером аргумента диапазона. Другими словами, верхняя левая ячейка в диапазоне_усреднения становится начальной точкой, и усредняется столько столбцов и строк, сколько содержится в аргументе диапазона.

формула СРЗНАЧЕСЛИ, основанная на другой ячейке

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

• критерии, применяемые к одному и тому же столбцу
• критерии, применяемые к другому столбцу

В случае, если условие применяется к тому же столбцу, который должен быть усреднен, вы определяете только первые два аргумента: диапазон и критерии. Например, чтобы найти среднее значение продаж в B3:B15, превышающее 120 долларов, используется формула:

=СРЗНАЧЕСЛИ(B3:B15, “>120”)

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

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

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

Где C3:C15 — ячейки для проверки на соответствие критериям, а B3:B15 — ячейки для усреднения.

Как использовать функцию СРЗНАЧЕСЛИ в Excel – примеры

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

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

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

Например, чтобы усреднить числа в столбце B, если столбец A содержит слово «Apple», используется следующая формула:

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

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

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

Преимущество этого подхода заключается в том, что он позволяет усреднить продажи для любого другого товара, просто изменив текстовые критерии в F3, без необходимости вносить какие-либо коррективы в формулу.

Кончик. Чтобы округлить среднее значение до определенного числа знаков после запятой, используйте команду «Увеличить десятичное число» или «Уменьшить десятичное число» на вкладке «Главная» в группе «Число». Это изменит представление среднего значения на дисплее, но не само значение. Чтобы округлить фактическое значение, возвращаемое формулой, используйте СРЗНАЧЕСЛИ вместе с ОКРУГЛ или другими функциями округления. Для получения дополнительной информации см. Как округлить среднее значение в Excel.

логический критерий СРЗНАЧЕСЛИ для числовых значений

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

При включении логического оператора с числом не забудьте заключить всю конструкцию в двойные кавычки. Например, чтобы усреднить числа, которые меньше или равны 120, формула будет выглядеть так:

=СРЗНАЧЕСЛИ(B3:B15, “<=120")

Обратите внимание, что оператор и номер заключены в кавычки.

При использовании критерия «равно» знак равенства (=) можно опустить.

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

=СРЗНАЧЕСЛИ(C3:C15, “9/9/2022”, B3:B15)

Использование AVERAGEIF с датами

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

Давайте посмотрим, как вы можете усреднить продажи до определенной даты, скажем, до 1 ноября 2022 года.

Самый простой способ — заключить логический оператор и дату в двойные кавычки:

=СРЗНАЧЕСЛИ(C3:C15, “<1/11/2022", B3:B15)

Или вы можете заключить оператора и дату в кавычки отдельно и соединить их с помощью знака &:

=СРЗНАЧЕСЛИ(C3:C15, “<"&"1/11/2022", B3:B15)

Чтобы убедиться, что дата введена в формате, понятном Excel, вы можете использовать функцию ДАТА, объединенную с логическим оператором:

=СРЗНАЧЕСЛИ(C3:C15, “<"&ДАТА(2022, 11, 1), B3:B15)

Чтобы усреднить продажи на сегодняшнюю дату, используйте функцию СЕГОДНЯ в критериях:

=СРЗНАЧЕСЛИ(C3:C15, “<"&СЕГОДНЯ(), B3:B15)

На скриншоте ниже показаны результаты:

СРЗНАЧесли больше 0

Функция СРЗНАЧ в Excel пропускает пустые ячейки, но включает в расчеты нулевые значения. Чтобы использовать только средние значения больше нуля, используйте «>0» для критериев.

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

=СРЗНАЧЕСЛИ(B3:B15, “>0”)

Обратите внимание, как результат отличается от обычного среднего в E3:

Среднее, если не 0

Приведенное выше решение хорошо работает для набора положительных чисел. Если у вас есть как положительные, так и отрицательные значения, вы можете усреднить все числа, кроме нулей, используя «<> 0» для критериев.

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

=СРЗНАЧЕСЛИ(B3:B15; “<>0″)

Среднее значение Excel, если оно не равно нулю или пусто

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

=СРЗНАЧЕСЛИ(B3:B15; “<>0″)

Среднее значение, если другая ячейка пуста

Чтобы усреднить ячейки в данном столбце, если ячейка в другом столбце в той же строке пуста, используйте «=» для критериев. Это будут пустые ячейки, которые абсолютно ничего не содержат — ни пробела, ни строки нулевой длины, ни непечатаемых символов и т. д.

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

В целях тестирования мы будем использовать оба критерия для усреднения чисел в B3:B15, у которых нет даты доставки в C3:C15 (т. е. если ячейка в столбце C пуста).

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

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

Поскольку одна из визуально пустых ячеек (C12) на самом деле не пуста — в ней есть строка нулевой длины — формулы дают разные результаты:

Среднее значение, если другая ячейка не пуста

Чтобы усреднить диапазон ячеек, если ячейка в другом диапазоне не пуста, используйте «<>» для критериев.

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

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

Подстановочный знак AVERAGEIF (частичное совпадение)

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

• Знак вопроса (?) для соответствия любому одиночному символу.
• Звездочка

для соответствия любой последовательности символов.

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

=СРЗНАЧЕСЛИ(A3:A15, “*банан”, B3:B15)

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

Формула подстановочного знака СРЗНАЧЕСЛИ

Если ключевое слово может появиться в любом месте ячейки (в начале, в середине или в конце), поставьте звездочку с обеих сторон:

=СРЗНАЧЕСЛИ(A3:A15, “*банан*”, B3:B15)

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

=СРЗНАЧЕСЛИ(A3:A15, “<>*банан*”, B3:B15)

Как рассчитать среднее значение в Excel, исключая определенные ячейки

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

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

=СРЗНАЧЕСЛИ(A3:A15, “<>яблоко”, B3:B15)

Если исключенный элемент находится в предопределенной ячейке (D4), формула принимает следующий вид:

=СРЗНАЧЕСЛИ(A3:A15, “<>“&D4, B3:B15)

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

=СРЗНАЧЕСЛИ(A3:A15, “<>*банан”, B3:B15)

Если исключенный подстановочный элемент находится в отдельной ячейке (D9), соедините логический оператор, подстановочный знак и ссылку на ячейку с помощью амперсанда:
“&”*”&D9, B3:B15)” title=”Рассчитать среднее значение в Excel, за исключением определенных ячеек.”/>

Рассчитать среднее значение в Excel, за исключением определенных ячеек.

Как использовать СРЗНАЧЕСЛИ со ссылкой на ячейку

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

Когда условие по умолчанию равно «равно», вы просто используете ссылку на ячейку в качестве аргумента критерия. Приведенная ниже формула вычисляет среднее значение всех продаж в диапазоне B3:B15, относящихся к элементу в ячейке F4.

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

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

Например, чтобы найти среднее значение продаж в B3:B15, превышающее значение в F9, используйте следующую формулу:

=СРЗНАЧЕСЛИ(B3:B15, “>”&F9)

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

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

СРЗНАЧЕСЛИ со ссылкой на ячейку

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