Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше
В этой статье описаны синтаксис формулы и использование С AVERAGEIF
в Microsoft Excel.
Описание
Возвращает среднее значение (среднее арифметическое) всех ячеек в диапазоне, которые соответствуют данному условию.
Синтаксис
СРЗНАЧЕСЛИ(диапазон, условия, [диапазон_усреднения])
Аргументы функции СРЗНАЧЕСЛИ указаны ниже.
-
Диапазон. Обязательный. Одна или несколько ячеек для вычисления среднего, включающих числа или имена, массивы или ссылки, содержащие числа.
-
Условие. Обязательный. Условие в форме числа, выражения, ссылки на ячейку или текста, которое определяет ячейки, используемые при вычислении среднего. Например, условие может быть выражено следующим образом: 32, «32», «>32», «яблоки» или B4.
-
Диапазон_усреднения. Необязательный. Фактическое множество ячеек для вычисления среднего. Если этот параметр не указан, используется диапазон.
Замечания
-
Ячейки в диапазоне, которые содержат значения ИСТИНА или ЛОЖЬ, игнорируются.
-
Если ячейка в «диапазоне_усреднения» пустая, функция СРЗНАЧЕСЛИ игнорирует ее.
-
Если диапазон является пустым или текстовым значением, то #DIV0! значение ошибки #ЗНАЧ!.
-
Если ячейка в условии пустая, «СРЗНАЧЕСЛИ» обрабатывает ее как ячейки со значением 0.
-
Если ни одна из ячеек в диапазоне не соответствует условиям, то #DIV/0! значение ошибки #ДЕЛ/0!.
-
В этом аргументе можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одиночному символу; звездочка — любой последовательности символов. Если нужно найти сам вопросительный знак или звездочку, то перед ними следует поставить знак тильды (~).
-
Значение «диапазон_усреднения» не обязательно должно совпадать по размеру и форме с диапазоном. При определении фактических ячеек, для которых вычисляется среднее, в качестве начальной используется верхняя левая ячейка в «диапазоне_усреднения», а затем добавляются ячейки с совпадающим размером и формой. Например:
|
Если диапазон равен |
И «диапазон_усреднения» |
Обрабатываемые ячейки |
|
A1:A5 |
B1:B5 |
B1:B5 |
|
A1:A5 |
B1:B3 |
B1:B5 |
|
A1:B4 |
C1:D4 |
C1:D4 |
|
A1:B4 |
C1:C2 |
C1:D4 |
Примечание: Функция СРЗНАЧЕСЛИ измеряет среднее значение, то есть центр набора чисел в статистическом распределении. Существует три наиболее распространенных способа определения среднего значения: :
-
Среднее значение — это среднее арифметическое, которое вычисляется путем сложения набора чисел с последующим делением полученной суммы на их количество. Например, средним значением для чисел 2, 3, 3, 5, 7 и 10 будет 5, которое является результатом деления их суммы, равной 30, на их количество, равное 6.
-
Медиана — это число, которое является серединой множества чисел, то есть половина чисел имеют значения большие, чем медиана, а половина чисел имеют значения меньшие, чем медиана. Например, медианой для чисел 2, 3, 3, 5, 7 и 10 будет 4.
-
Мода — это число, наиболее часто встречающееся в данном наборе чисел. Например, модой для чисел 2, 3, 3, 5, 7 и 10 будет 3.
При симметричном распределении множества чисел все три значения центральной тенденции будут совпадать. При смещенном распределении множества чисел значения могут быть разными.
Примеры
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
|
Стоимость имущества |
Комиссионные |
|
|
100000 |
7000 |
|
|
200000 |
14000 |
|
|
300000 |
21000 |
|
|
400000 |
28000 |
|
|
Формула |
Описание |
Результат |
|
=СРЗНАЧЕСЛИ(B2:B5;»<23000″) |
Среднее значение всех комиссионных меньше 23 000. Этому условию удовлетворяют три из четырех значений, сумма которых составляет 42 000. |
14000 |
|
=СРЗНАЧЕСЛИ(A2:A5;»<250000″) |
Среднее среди всех значений стоимости имущества меньше 250 000. Этому условию удовлетворяет два из четырех значений, их сумма составляет 300 000. |
150000 |
|
=СРЗНАЧЕСЛИ(A2:A5;»<95000″) |
Среднее среди всех значений стоимости имущества меньше 95 000. Так как количество значений стоимости имущества, удовлетворяющих этому условию, равно 0, функция СРЗНАЧЕСЛИ вернет ошибку #ДЕЛ/0! из-за попытки деления на 0. |
#ДЕЛ/0! |
|
=СРЗНАЧЕСЛИ(A2:A5;»>250000″;B2:B5) |
Среднее значение всех комиссионных для стоимости имущества более 250 000. Этому условию удовлетворяют два значения, сумма которых составляет 49 000. |
24500 |
Пример 2
|
Регион |
Доходы (в тысячах) |
|
|
Восточный |
45678 |
|
|
Западный |
23789 |
|
|
Северный |
-4789 |
|
|
Южная (новое представительство) |
0 |
|
|
Средний Запад |
9678 |
|
|
Формула |
Описание |
Результат |
|
=СРЗНАЧЕСЛИ(A2:A6;»=*Западная»;B2:B6) |
Среднее значение всех доходов для Западной и Средне-Западной областей. |
16733,5 |
|
=СРЗНАЧЕСЛИ(A2:A6;»<>*(Новое представительство)»;B2:B6) |
Среднее значение всех доходов для всех областей, за исключением новых представительств. |
18589 |
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
Для расчета среднего значения по условию в Excel используется функция СРЗНАЧЕСЛИ. Кроме суммирования и подсчета количества значений, вычисление среднего значения по условию – это одна из самый часто выполняемых операций с диапазонами ячеек в Excel. Поэтому стоит научится эффективно использовать такие востребованные функции.
Как найти среднее арифметическое число по условию в Excel
Ниже на рисунке представлен список медалистов зимних олимпийских игр за 1972-ой год. Допустим нам необходимо узнать средний показатель результатов призеров из Швейцарии. Название страны для условия записано в отдельной ячейке, благодаря чему можно легко менять его на другое. Формула решения:
Для решения данной задачи Excel предлагает специальную функцию СРЗНАЧЕСЛИ. Принцип работы данной функции очень похож на родственную ей СУММЕСЛИ для суммирования по условию. Также использует те же обязательные для заполнения аргументы: Диапазон и Условие. Отличается только опциональный аргумент: Диапазон_усреднения.
В данном примере каждое значение в диапазоне … проверяется: будет или не будет оно учтено при расчете среднего показателя после выборки. Все зависит от того выполняет ли условие значение текущей ячейки согласно с логическим выражением, указанным в критерии отбора. При этом если не найдется ни одной ячейки удовлетворяющей условие выборки, тогда функция СРЗНАЧЕСЛИ возвращает ошибку #ДЕЛ/0!
Альтернативная формула для расчета среднего числа с условием
При желании можно составить альтернативную формулу без использования функции СРЗНАЧЕСЛИ. Среднее значение в математике называется среднее арифметическое – является суммой всех значений, разделенной на их количество. Аналогично функция СРЗНАЧЕСЛИ суммирует все значения в диапазоне, указанном в первом аргументе с учетом заданного критерия выборки значений указанном уже во втором аргументе. А затем результат разделяется на количество выбранных значений. Такой же самый результат мы получим, используя комбинацию двух родственных функций, разделив результат вычисления СУММЕСЛИ на результат СЧЁТЕСЛИ. Поэтому для вычисления среднего значения по условию анализа выше приведенной таблицы можно применить другую формулу:
В программе Excel 90% задач можно решить несколькими правильными решениями.
Найдем среднее всех ячеек, значения которых соответствуют определенному условию. Для этой цели в MS EXCEL существует простая и эффективная функция
СРЗНАЧЕСЛИ()
, которая впервые появилась в EXCEL 2007. Рассмотрим случай, когда критерий применяется
к диапазону содержащему текстовые значения.
Пусть дана таблица с наименованием фруктов и количеством ящиков на каждом складе (см.
файл примера
).
Рассмотрим 2 типа задач:
-
Найдем среднее только тех значений, у которых соответствующие им ячейки (расположенные в той же строке)
точно
совпадают с критерием (например, вычислим среднее для значений,соответствующих названию фрукта «яблоки»); -
Найдем среднее только тех значений, у которых соответствующие им ячейки (расположенные в той же строке)
приблизительно
совпадают с критерием (например, вычислим среднее для значений, которые соответствуют названиям фруктов
начинающихся
со слова «груши»). В критерии применяютсяподстановочные знаки (*, ?)
.
Рассмотрим эти задачи подробнее.
Критерий точно соответствует значению
Как видно из рисунка выше, яблоки бывают 2-х сортов: обычные яблоки и яблоки RED ЧИФ. Найдем среднее количество на складах ящиков с обычными яблоками. В качестве критерия функции
СРЗНАЧЕСЛИ()
будем использовать слово
«яблоки»
.
=СРЗНАЧЕСЛИ($A$6:$A$16;»яблоки»;$B$6:$B$16)
При расчете среднего, функция
СРЗНАЧЕСЛИ()
учтет только значения 2; 4; 5; 6; 8; 10; 11, т.е. значения в строках 6, 9-14. В этих строках в столбце
А
содержится слово
«яблоки»
, точно совпадающее с критерием.
В качестве диапазона усреднения можно указать лишь первую ячейку диапазона — функция
СРЗНАЧЕСЛИ()
вычислит все правильно:=
СРЗНАЧЕСЛИ($A$6:$A$16;»яблоки»;B6)
Критерий
«яблоки»
можно поместить в ячейку
D
8
, тогда формулу можно переписать следующим образом:=
СРЗНАЧЕСЛИ($A$6:$A$16;D8;B6)
В критерии применяются подстановочные знаки (*, ?)
Найдем среднее содержание в ящиках с грушами. Теперь название ящика не обязательно должно совпадать с критерием «груша», а должно начинаться со слова «груша» (см. строки 15 и 16 на рисунке).
Для решения этой задачи используем
подстановочные знаки (*, ?)
. Подход заключается в том, что для отбора текстовых значений в качестве критерия задается лишь часть текстовой строки. Например, для отбора всех ячеек, содержащих слова
яблоки
(
свежие
яблоки
,
яблоки местные
и пр.) можно использовать критерии с
подстановочным знаком
* (звездочка). Для этого нужно использовать конструкцию *
яблоки*
.
Решение задачи выглядит следующим образом (учитываются значения содержащие слово
груши
в начале названия ящика):=
СРЗНАЧЕСЛИ($A$6:$A$16; «груши*»;B6)
Примеры, приведенные ниже, иллюстрируют другие применения
подстановочных знаков
.
|
|
|
|
Значения, удовлетворяющие критерию |
|
|
заканчиваются на слово |
*яблоки |
= |
Свежие яблоки |
Использован подстановочный знак * (перед значением) |
|
содержат слово |
*яблоки* |
= |
яблоки местные , свежие яблоки или Лучшие яблоки на свете |
Использован подстановочный знак * (перед и после значения) |
|
начинаются с |
гру?? |
= |
груша или груши |
Использован подстановочный знак ? |
Как посчитать среднее значение в Excel? Выделите необходимую группу чисел, жмите на кнопку суммы на рабочей панели (∑), а в выпадающем списке жмите на «Среднее». При желании можно вручную вести параметр «=СРЗНАЧ» с указанием диапазона ячеек. Ниже рассмотрим, как правильно рассчитать параметр для разных случаев — с текстом, с условием, средневзвешенное и т. д.
Простой расчет
Наиболее простой метод, как вычислить среднее значение в Excel — сделать это с помощью специального значка на панели инструментов Майкрософт Эксель. Для этого сделайте следующее:
- Выделите группу числе в столбце, для которых нужно рассчитать среднее значение в Экселе.
- Перейдите в раздел «Главная».
- Найдите значок «∑» на рабочей панели в блоке редактирования.
- В выпадающем списке жмите на «Среднее».
- Получите результат, который выводится в качестве усредненного арифметического набора чисел.
Этот метод подходит легкостью и удобством применения, но у него имеются и недостатки. В частности, с его помощью можно подсчитать усредненный параметр для чисел, которые находятся в одном столбце / строке. Если же нужно работать с массивом данных или ячейками, которые находятся в разных участках, расчет среднего значения в Excel будет затруднен.
Альтернативный вариант — ручной ввод необходимой опции с помощью функции «СРЗНАЧ». Чтобы посчитать нужные данные, введите команду по следующему алгоритму: «=СРЗНАЧ(адрес_диапазона_ячеек(цифра); адрес_диапазона_ячеек(цифра)). Он не такой быстрый, как рассмотрен выше, но позволяет найти среднее число в Excel даже в сложных ситуациях.
Как посчитать для нескольких ячеек
Если нужно посчитать среднее значение в Excel для большой группы ячеек, можно использовать «Мастер функций». Он позволяет считать среднее значение в Excel с помощью озвученной выше формулы, но сам процесс проходит немного проще. Алгоритм действий следующий:
- Кликните по ячейке, где нужно посчитать требуемое значение в Эксель.
- Жмите на «Вставить функцию», находящуюся с левой стороны от строки формул или же кликните Shift+F3.
- Запустите «Мастер функций», в перечне доступных опций найдите «СРЗНАЧ».
- Выделите его и кликните на «ОК».
- В графе «Число» укажите аргументы функции, а именно цифры или адреса секций, где они находятся. Для большего удобства кликните на клавишу, которая находится с правой стороны от поля ввода данных.
- Выделите те ячейки, для которых нужно посчитать данные, а после кликните на клавишу с левой стороны от поля ввода данных для возврата к окну аргументов функции.
Если нужно высчитать среднее значение в Excel от цифр, которые расположены в разных ячейках, сделайте рассмотренные выше действия, но в графе «Число 2». Действовать нужно до тех пор, пока все группы не окажутся выделенными. Результат указывается в той ячейке, которая была выделена перед входом в «Мастер функций».
Через формулы
Следующий способ, как найти среднее значение в Excel — через формулы. Для запуска параметра «СРЗНАЧ» сделайте следующее:
- Кликните на раздел «Формулы».
- Выделите ячейку, где нужно получить результат.
- В секции «Библиотека функций» ленты кликните «Другие …».
- Кликните на «Статические» и «СРЗНАЧ».
- Выполните остальную работу по принципу, который рассмотрен выше.
Как посчитать с условием
Нередко возникает вопрос, как найти среднее арифметическое в Excel с помощью опции условия. В такой ситуации для расчета берутся лишь те цифры из диапазона, соответствующего определенному условию, к примеру, если параметр больше или меньше определенного числа.
Для такой опции применяется функция «СРЗНАЧЕСЛИ». Для ее запуска можно использовать три пути:
- «Панель формул».
- «Мастер функций».
- «Ручной ввод в ячейку».
После открытия окна аргументов остается ввести параметры, а в поле «Диапазон» ввести пределы ячеек, которые будут участвовать для определения среднего арифметического. Здесь принцип действия такой же, как в случае с обычным «СРЗНАЧ».
Чтобы в Экселе сделать среднее значение с условием, необходимо в графе «Условие» указать определенный параметр, в отношении которого будет осуществляться сравнение. К примеру, это может быть «<=10000». В этом случае можно посчитать данные для тех секций Excel, в которых находятся числа меньше или равные 10 000. Если это нужно, кроме определенной цифры можно указать адрес, где находится специальное число.
Графу «Диапазон усреднения» можно не заполнять. Вводить в нее данные обязательно только с применением ячеек с текстом. Как только все сведения указаны, кликните на «ОК». Среднее значение в Excel будет показано для определенного диапазона, кроме данных, которые не соответствуют заявленному условию.
С текстом
Бывают ситуации, когда необходимо посчитать среднее значение в Эксель вместе с текстом. В этом случае используется функция, имеющая следующий вид: =СРЗНАЧЕСЛИ($A$2:$A$13;A7;$B$2:$B$13). Здесь в графа «А» содержится текст, а «В» — цифры. В качестве критерия поиска может использоваться определенный адрес ячейки с необходимым словом. Как вариант, его можно написать в необходимом месте. При этом расчет среднего значения производится для второй колонки.
Как посчитать средневзвешенное значение
Следующий вопрос — как вывести среднее значение в Excel для средневзвешенного параметра. Чтобы посчитать эти данные, потребуются функции СУММПРОИЗВ и СУММ. Для расчета средневзвешенного параметра используется следующая формула (пример):
=СУММПРОИЗВ(C2:C13;B2:B13)/СУММ(C2:C13).
Расчет в этом случае выполняется в два шага:
- СУММПРОИЗВ позволяет определить сумму.
- Применение СУММ позволяет определить общее количество каких-либо единиц, к примеру, реализованной продукции.
Расчет без игнорирования нулевых значений
Итого в Экселе приходится посчитать среднее значение столбцов с учетом нулевых параметров. В таком случае нужно использовать опции С AVERAGE и ЕСЛИ. При этом сам запрос может иметь следующий вид =С AVERAGEIF(A2:A7;»<>0″).
Как видно из сказанного, посчитать среднее значение в 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 для вычисления среднего арифметического с условиями. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!