Формула среднего арифметического значения в Excel

Формула среднего арифметического в Excel - функции и примеры

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

Средняя зарплата... Средняя продолжительность жизни... Каждый день мы слышим эти фразы, служащие для описания количества одним числом. Но как ни странно, «среднее значение» — довольно коварное понятие, которое часто вводит в заблуждение обычного человека, неискушенного в математической статистике. Например, в абстрактной компании работают десять сотрудников. Девять из них получают зарплату около 50 000 рублей, а один — 1 500 000 рублей (по странному стечению обстоятельств он же является и генеральным директором этой компании).

В математике среднее значение называется средним арифметическим и рассчитывается путем сложения группы чисел и последующего деления на количество этих чисел.

В примере выше средняя зарплата равна: 195 000 рублей = (50 000 * 9 + 1 500 000) / 10. Вряд ли это число соответствует реальному положению, но это расчеты среднего арифметического.

И все же в большинстве случаев знание среднего значения весьма полезно.

Оглавление

1. СРЕДНЯЯ функция
   • Пример 1. Вычисление среднего значения нескольких чисел.
   • Пример 2. Расчет среднего процента.
   • Пример 3. Расчет среднего времени.
2. Чем отличается функция СРЗНАЧ?
3. Условное среднее - функция СРЗНАЧЕСЛИ
   • Среднее, если пусто.
   • Средний, если не пустой.
4. Среднее по нескольким условиям — функция СРЗНАЧЕСЛИМН
   • Пример 1. Среднее арифметическое количество ячеек по нескольким параметрам (текст и числа)
   • Пример 2: Среднее арифметическое на основе критериев даты
   • Пример 1: Среднее с логикой ИЛИ на основе нескольких текстовых критериев
   • Пример 2: Усреднение с помощью логического ИЛИ на основе числовых критериев с операторами сравнения
   • Пример 3: Среднее с логикой ИЛИ на основе пустых/непустых ячеек

Как посчитать среднее арифметическое в Excel? Вам не нужно писать сложные математические выражения. Есть несколько функций «на все случаи жизни».

• AVERAGE (СРЗНАЧ - в английской версии) - вычислить среднее арифметическое по ячейкам с числами.
• СРЗНАЧ (или СРЗНАЧА) - найти среднее арифметическое ячеек со всеми данными (числами, логическими и текстовыми значениями).
• AVERAGEIF (или AVERAGEIF) - среднее значение по заданному критерию.
• СРЗНАЧЕСЛИМН (или СРЗНАЧЕСЛИМН) — это среднее значение ячеек, соответствующих нескольким критериям.

Функция СРЗНАЧ.

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

СРЕДНИЙ номер 1; [Номер 2];…)

Число 1, 2 и так далее — это числа, для которых вы хотите найти среднее значение. Первый аргумент обязателен, последующие нет. В одну формулу можно включить до 255 аргументов. Они могут быть представлены в виде чисел, ссылок на ячейки или диапазонов.

Использование функции СРЗНАЧ – на примерах.

СРЗНАЧ — одна из самых простых и удобных в использовании функций Excel, и следующие примеры доказывают это.

Пример 1. Расчет среднего из нескольких чисел.

Вы можете указать номера напрямую. Например,

 =СРЗНАЧ(1;2;3;4) возвращает результат 2.5.

Чтобы вычислить среднее значение столбца, обратитесь ко всему столбцу:
=СРЕДНЕЕ(А:А)

Чтобы получить построчно, введите ссылку на него:
=СРЕДНЕЕ(1:1)

Для расчета в любом диапазоне укажите его:
=СРЕДНЕЕ(A1:C20)

Чтобы получить среднее значение несмежных ячеек, введите каждую ячейку отдельно, например
=СРЗНАЧ(A1;C1;D1)

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

=СРЗНАЧ(B3:B5; C7:D9; B11)

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

= КРУГЛЫЙ(СРЕДНИЙ(B3:B5; B7:B9; B11); 0)

 

 

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

Пример 2. Расчет среднего процента.

Если в вашей электронной таблице есть процентный столбец, как вы получите средний процент завершения? Возьмем обычную формулу Excel для среднего :)

 

 

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

Пример 3. Расчет среднего времени.

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

 

 

Важные особенности СРЗНАЧ!

Как вы только что видели, использовать AVERAGE очень просто. Тем не менее, у него есть несколько особенностей, о которых вам нужно знать.

• В расчет включаются нулевые значения (0.
• Текстовые строки, логические значения TRUE и FALSE, а также пустые ячейки игнорируются. Если вы хотите включить в расчет логическое и текстовое представление чисел, используйте СРЗНАЧ.
• Булевы значения, которые вы вводите непосредственно в выражение, учитываются. Например, =СРЗНАЧ(ИСТИНА; ЛОЖЬ) возвращает 0,5, что является средним значением 1 и 0.

Примечание. При использовании СРЗНАЧ в электронных таблицах Excel помните о разнице между ячейками, содержащими нулевые значения, и теми, которые на самом деле пусты. Нули учитываются, пробелы нет. Это может быть особенно важно, если на этом листе не включен параметр «Показывать ноль в ячейках, содержащих нулевые значения». То есть ячейки с нулями будут внешне пустыми. Вы можете найти эту опцию в Excel: «Параметры» > «Дополнительно» > «Параметры отображения листа .

Чем отличается функция СРЗНАЧА?

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

СРЗНАЧ(значение1; [значение2];…)

value1, 2,.. — это числа, массивы, ссылки на ячейки или диапазоны, которые вы хотите усреднить. Первый аргумент обязателен, остальные (до 255) необязательны.

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

• Пустые ячейки таблицы игнорируются.
• Текст, включая пустые строки (""), возвращаемые другими формулами, оценивается как 0.
• Логическое значение TRUE оценивается как 1, а FALSE оценивается как 0.

Например, =СРЗНАЧ(2, ЛОЖЬ) дает нам 1, что является средним значением между 2 и 0.

=СРЗНАЧ(2, ИСТИНА) возвращает 1,5, что является средним значением 2 и 1.

На следующем снимке экрана показаны два варианта расчета среднего значения в Excel и разные результаты, которые они возвращают:

 

 

Поэтому, если вы не хотите включать логические выражения и текстовые строки в свои вычисления, используйте СРЗНАЧ вместо СРЗНАЧ.

Среднее по условию - функция СРЗНАЧЕСЛИ

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

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

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

• Диапазон - диапазон ячеек, которые будут проверяться по заданным параметрам.
• Критерии — условие, используемое для определения того, какие ячейки следует срезать. Они могут быть представлены в виде числа, логического выражения, текста или строки, например 5, > 5, «кошка» или A2.
• Средний диапазон - то, что вы хотите усреднить (это необязательно). Если он опущен, функция будет учитывать диапазон условия.

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

Точное соответствие критериям

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

В этом примере давайте просто рассчитаем средние продажи (B2:B8) для заказов на бананы (A2:A8):

=СРЗНАЧЕСЛИ($A$2:$A$8, $B$10, $B$2:$B$8)

Вместо того, чтобы вводить условие непосредственно в формулу, вы можете ввести его в отдельную ячейку ($B$10) и затем обратиться к нему. Это очень известный и широко используемый метод.

 

 

Примечание. Чтобы округлить возвращенное число до определенного числа знаков после запятой, используйте одну из функций округления Excel или используйте диалоговое окно «Формат ячеек», чтобы изменить только отображение числа на экране.

Например, чтобы округлить приведенный выше результат до 2 знаков после запятой, вы можете обернуть его функцией ОКРУГЛ следующим образом:

=ОКРУГЛ(СРЗНАЧ.ЕСЛИ(A2:A8;"бананы", B2:B8),2)

Либо выберите ячейку с формулой (в данном примере E1), нажмите Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек». Затем перейдите на вкладку «Числовой» или «Валюта» и выберите количество знаков после запятой, которое вы хотите отобразить. Помните, что в этом случае фактическое сохраненное значение не изменится, и во всех расчетах будет использоваться точное неокругленное число, когда вы ссылаетесь на него в других формулах.

Частичное соответствие критериям (с символами подстановки).

Вы можете использовать подстановочные знаки в аргументе условия для выбора ячеек на основе частичного совпадения:

• Вставьте знак вопроса (?), чтобы заменить один символ.
• Вставьте звездочку (*), чтобы заменить ею последовательность символов.
• Чтобы найти настоящий вопросительный знак или звездочку, введите тильду (~) перед символом (то есть ~? или ~*).

Предположим, у вас есть 3 разных вида бананов, и вы хотите найти среднее значение их продаж:

=СРЗНАЧ.ЕСЛИ($A$2:$A$8, "*бананы", $B$2:$B$8)

 

 

Если перед и/или после слова поиска может стоять другой символ, добавьте звездочку * до и после слова.

=СРЗНАЧ.ЕСЛИ($A$2:$A$8, "*бананы*", $B$2:$B$8)

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

=СРЗНАЧ.ЕСЛИ($A$2:$A$8, "*бананы*", $B$2:$B$8)

Как применить числовые условия и логические операторы.

Довольно часто вам может понадобиться усреднить ячейки, число которых больше или меньше определенного числа. Например, в столбце А есть список чисел, и мы хотим найти среднее число чисел, превышающих 10.

Правильный подход заключается в заключении логического оператора и числа в двойные кавычки. 

Итак, мы получили это:

=СРЗНАЧЕСЛИ($A$2:$A$7;">10")

 

 

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

=СРЗНАЧ.ЕСЛИ($A$2:$A$7,"0")

Как вы, возможно, заметили, мы не используем третий аргумент [средний диапазон] ни в одном из приведенных выше выражений, потому что мы хотим, чтобы подсчет превышал исходный диапазон.

Для пустых или непустых ячеек

При анализе данных в Excel часто требуется найти среднее значение чисел, соответствующих либо пустым, либо непустым областям.

Среднее, если пусто.

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

Например, давайте посчитаем среднее значение ячеек C2:C8, если ячейка в столбце B в той же строке полностью пуста:

=СРЗНАЧЕСЛИ($B$2:$B$8, "=", $C$2:$C$8)

Для усреднения значений, соответствующих визуально пустым ячейкам, в том числе содержащим пустые строки, возвращаемые другими функциями (например, с формулой вида = ""), введите "". Например:

=СРЗНАЧЕСЛИ($B$2:$B$8, "", $C$2:$C$8)

 

 

Среднее, если не пустое.

Чтобы найти среднее значение, соответствующее непустым ячейкам, введите «».

Например, вот как мы вычисляем среднее арифметическое C2:C8, если соответствующая позиция в столбце B не пуста:

=СРЗНАЧЕСЛИ($B$2:$B$8, "", $C$2:$C$8)

Использование ссылок и других функций в описании условий.

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

Если ссылка является критерием точного соответствия, просто укажите его в аргументе:

=СРЗНАЧЕСЛИ($B$2:$B$8,E1, $C$2:$C$8)

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

Например, чтобы рассчитать средние продажи (C2:C8) больше, чем E4:

=СРЗНАЧЕСЛИ($C$2:$C$8, ">"&$B$13)

С датами в B2:B8 следующее возвращает среднее значение продаж (C2:C8), которые произошли до текущей даты:

=СРЗНАЧ.ЕСЛИ($B$2:$B$8; "

 

 

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

=СРЗНАЧЕСЛИ($B$2:$B$8, "

Среднее по нескольким условиям - функция СРЗНАЧЕСЛИМН

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

СРЗНАЧЕСЛИМН(диапазон подсчета; диапазон критериев_1; критерий_1; [диапазон критериев_2; критерий_2],…)

Он имеет следующие аргументы:

• Count_range — диапазон ячеек, который вы хотите усреднить.
• критерии_диапазона_1, 2 ,… - от 1 до 127 диапазонов для проверки соответствия заданным параметрам. Первый обязателен, остальные необязательны.
• Критерии_1, 2,... - от 1 до 127 критериев, определяющих, какие ячейки следует усреднить. Они могут быть в виде числа, логического выражения, текста или ссылки. Первый необходим, дополнительные - при необходимости.

Функция СРЗНАЧЕСЛИМН доступна в Excel 2016, Excel 2013, Excel 2011 для Mac, Excel 2010 и 2007.

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

Пример 1. Среднеарифметическое количество ячеек по нескольким параметрам (текст и число)

Допустим, у вас есть список товаров в столбце A и итоговые данные о продажах в столбце B. Давайте найдем, что средний объем продаж бананов превышает 100 единиц.

Здесь:

• С2:С8 - ячейки, которые нужно усреднить, если выполняются оба условия;
• Первое условие: A2:A8 (столбец «Товар»), а критерий_1 — «бананы»;
• Второе условие — С2:С8 (столбец «Продажи»), а критерий_2 — «> 100».

Собираем вышеуказанные компоненты воедино. А если заменить условия ссылками, то получится что-то вроде этого:

=СРЗНАЧЕСЛИ($C$2:$C$8; $A$2:$A$8;$C$10; $C$2:$C$8; ">"&$C$11)

 

 

Как видите, только две ячейки (С3 и С5) удовлетворяют обоим условиям, поэтому для расчета берутся только они.

Пример 2. Среднеарифметическое на основе критерия даты

В данном примере посчитаем среднее количество доставленных до 22 июля 2020 г товаров, статус которых определен (позиция в соответствующем столбце не пуста). С количеством в столбце D, датами в столбце B (критерий_1) и статусом в C (критерий_2) формула выглядит следующим образом:

=СРЗНАЧЕСЛИ($D$2:$D$8, $B$2:$B$8, "")

В критерии1 вы вводите дату с оператором сравнения. В критерии2 введите «», что указывает на включение в столбец «Статус» только непустых ячеек».

 

 

Примечание. При использовании числа или даты в сочетании с логическим оператором в условии необходимо заключить эту комбинацию в кавычки - "

Что вспомнить!

СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН имеют много общего, в частности:

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

ХАРАКТЕРИСТИКИ

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

=СРЗНАЧЕСЛИ($B$2:$B$10, "", $C$2:$C$8)

тогда мы все равно найдем среднее значение по $C$2:$C$8.

• В отличие от AVERAGEIF, AVERAGEIFS требует, чтобы площадь каждого критерия была того же размера, что и подсчитываемая площадь. В противном случае мы получим ошибку #ЗНАЧ!.

Как усреднить числа по нескольким параметрам с помощью логики ИЛИ?

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

Другими словами, мы составим выражение для вычисления среднего арифметического в Excel при соблюдении любого из указанных требований.

Пример 1. Среднее с логикой ИЛИ на основе нескольких текстовых критериев

Предположим, вы хотите получить средние продажи (C2:C8) как для бананов, так и для яблок (A2:A8). Чтобы вычислить это, вам нужна формула массива, которая включает несколько функций Excel:

{=СРЗНАЧ(ЕСЛИ(ЧИСЛО(СООТВЕТСТВИЕ(A2:A8,{"бананы","яблоки"},0)),B2:B8))}

Помните, что формулы массива нужно вводить с помощью Ctrl + Shift + Enter, а не только ENTER.

 

 

Если элементы, которые мы ищем, записаны отдельно (скажем, в E1 и E2), наш расчет будет выглядеть так:

{=СРЗНАЧ(ЕСЛИ(ЧИСЛО(СООТВЕТСТВИЕ(A2:A8,E1:E2,0)),B2:B8))}

Из всех рассмотренных до сих пор формул средних значений Excel эта самая сложная (хотя осталось еще два примера ;)

Как это работает?

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

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

ПОИСКПОЗ берет исходные данные в A2:A8 в качестве условий поиска и сравнивает каждый из них с матрицей поиска, представленной критериями в E1:E2. 

третий аргумент, установленный в 0, указывает на проверку точных совпадений:

ПОИСКПОЗ(A2:A8;E1:E2;0)

Когда совпадение найдено, программа возвращает относительную позицию в массиве поиска, в противном случае — ошибка #Н/Д!.

{#СЕЙЧАС!; 1; 2; 1; 2; #СЕЙЧАС!; 1}

ISNUMBER преобразует все числа в TRUE, а ошибки в FALSE:

{ЛОЖЬ; ПОДЛИННЫЙ; ПОДЛИННЫЙ; ПОДЛИННЫЙ; ПОДЛИННЫЙ; ЛОЖЬ; ПОДЛИННЫЙ}

Этот массив передается на логический тест IF. 

 Он заменяет позиции, соответствующие TRUE в приведенном выше массиве, фактическими значениями из B2:B8:

{ЛОЖЬ; 80; 130; 120; 70; ЛОЖЬ; 90}

Этот последний массив передается в AVERAGE, который оценивает игнорирование логических значений.

Пример 2. Среднее с логикой ИЛИ на основе числовых критериев с операторами сравнения

Если вы хотите найти среднее арифметическое на основе большего количества числовых критериев и большего/меньшего количества условий, чем в сочетании с логикой ИЛИ, формула, рассмотренная в предыдущем примере, не будет работать. В конце концов, вы не можете поместить логические выражения в массив. Решение состоит в использовании функции СУММ в формуле массива.

Предположим, у вас есть количество в столбце B и сумма в столбце C, и вы хотите усреднить суммы, превышающие 50 в столбце B или C. Делая это, вы избежите дублирования, т.е не подсчитывая строку дважды, если число больше чем 50 в обоих столбцах.

Вот рабочий пример:

{=СУММ(ЕСЛИ((C2:C8+D2:D8>50),D2:D8,0))/СУММ(--((C2:C8+D2:D8)>50))}

Помните, что это формула массива и поэтому вы должны нажать Ctrl+Shift+Enter и ввести ее правильно.

 

 

Как видите, выражение состоит из 2 частей. В первой части вы используете функцию ЕСЛИ с оператором ИЛИ в аргументе (C2:C8> 50) + (D2:D8> 50). Как вы, наверное, знаете, в формулах массива плюс (+) действует как оператор ИЛИ.

Таким образом, первая часть суммирует числа в столбце C, если выполняется какое-либо из условий. 

Вторая часть возвращает количество таких ячеек, а затем вы делите сумму на число, чтобы найти среднее арифметическое.

И, конечно же, вы можете указать разные условия для каждой области. Например, если столбец C больше 50 или столбец D больше 100, используйте следующее выражение: (C2: C8 > 50) + (D2: D8 > 100). Все это будет выглядеть так:

=СУММ(ЕСЛИ((C2:C8>50)+(D2:D8>100),D2:D8,0))/СУММ(--((C2:C8>50)+(D2:D8>100)))

Пример 3. Среднее с логикой ИЛИ на основе пустых/непустых ячеек

Средняя формула с несколькими условиями ИЛИ, соответствующими пустым и непустым ячейкам, очень похожа на ту, которую мы только что обсуждали.

Для непустых ячеек.

Следующая формула массива находит среднее значение (столбец B), если указана либо дата (столбец B), либо статус (столбец C), т е если столбцы B или C не пусты.

Чтобы сделать его более компактным, вы можете объединять диапазоны с помощью амперсанда (&):

{=СУММ(ЕСЛИ((B2:B8&C2:C8)"";D2:D8;0))/СУММ(--((B2:B8&C2:C8)""))}

 

 

Слишком пустые ячейки.

Чтобы усреднить числа в столбце D, соответствующие пустым ячейкам в B или C, замените непустой оператор ( "") пустым оператором (= ""). 

Объединение диапазонов амперсандом в этом случае не работает. 

Это тоже формула массива, поэтому не забудьте нажать Ctrl+Shift+Enter.

{=СУММ(ЕСЛИ(--((B2:B8="") + (C2:C8=""))>0;D2:D8;0)) / СУММ(--(((B2:B8=" ") + (C2:C8=""))>0))}