Как найти среднее значение если в excel

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

Microsoft 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 для вычисления среднего арифметического с условиями. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

---

You can use the following formula to calculate the average in Excel while ignoring zeros and blank cells:

=AVERAGEIF(B2:B14, "<>0")

This particular formula calculates the average value in the range B2:B14 and ignores cells that are equal to zero or blank.

Note: Excel ignores blank cells by default when calculating an average.

Thus, we only need to specify in the formula that cells should not be equal ( “<>0”) to zero as well.

The following example shows how to use this formula in practice.

Suppose we have the following dataset in Excel that shows the total sales made by various employees at a company:

If we simply used the AVERAGE() formula, we would find the average sales for all of the employees who had a non-blank value:

The average sales per employee who had a non-blank value is 3.

However, suppose we wanted to only calculate the average for employees who had a sales value that was not blank and not equal to zero.

We could type the following formula into cell D2:

=AVERAGEIF(B2:B14, "<>0")

The following screenshot shows how to use this formula in practice:

The average sales for employees who had greater than zero sales was 4.125.

This formula calculated the average by only using the values that were not blank and not equal to zero.

We can confirm that this is correct by manually calculating the average of all values that are not blank or equal to zero:

Average of Values Greater than Zero: (10+4+4+3+2+1+4+5) / 8 = 4.125.

This matches the value calculated by our formula.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

How to Calculate Average If Cell Contains Number in Excel
How to Use AVERAGEIF with Multiple Ranges in Excel
How to Calculate Average Excluding Outliers in Excel