Как посчитать среднее значение 🚩 Найти среднее арифметическое всех целых чисел от 1 до 1000 🚩 Математика

Автор КакПросто!

Как посчитать среднее значение

В математике и статистике среднее арифметическое (или просто среднее) набора чисел — это сумма всех чисел в этом наборе, поделённая на их количество. Среднее арифметическое является наиболее общим и самым распространённым понятием средней величины.

Как посчитать среднее значение

Инструкция

Пусть дан набор из четырех чисел. Необходимо найти среднее значение этого набора. Для этого

сначала

найдем сумму всех этих чисел. Допустим эти

числа

1, 3, 8, 7. Их сумма

равна

S = 1 + 3 + 8 + 7 = 19. Набор чисел должен состоять из чисел одного знака, в противном случае

смысл

в вычислении среднего значения теряется.

Среднее значение набора чисел равно сумме чисел S, деленной на количество этих чисел. То есть получается, что среднее значение равно: 19/4 = 4.75.

Для набора

числе

также можно найти не только среднее арифметическое, но и среднее геометрическое. Средним геометрическим нескольких положительных вещественных чисел называется такое число, которым можно заменить каждое из этих чисел так, чтобы их произведение не изменилось. Среднее геометрическое G ищется по формуле: корень N-ой

степени

из произведения набора чисел, где N - количество числе в наборе. Рассмотрим тот же набор чисел: 1, 3, 8, 7. Найдем их среднее геометрическое. Для этого посчитаем произведение: 1*3*8*7 = 168. Теперь из числа 168 необходимо извлечь корень 4-ой степени: G = (168)^1/4 = 3.61. Таким образом среднее геометрическое набора чисел равно 3.61.

Обратите внимание

Среднее значение не может быть больше самого большого числа в наборе и меньше самого маленького.

Полезный совет

В математической статистике среднее значение величины называется математическим ожиданием.

Источники:

  • как вычислить среднее значение
  • Найти среднее арифметическое всех целых чисел от 1 до 1000
  • [Turbo Pascal] Нахождение среднего геометрического

Похожие советы

  • Как рассчитать среднюю величину Как рассчитать среднюю величину
  • Как найти среднее время Как найти среднее время
  • Как найти среднее квадратическое отклонение Как найти среднее квадратическое отклонение
  • Как вычислить средний процент Как вычислить средний процент
  • Как считать среднее в 2017 году Как считать среднее в 2017 году
  • Как найти среднюю и дисперсию Как найти среднюю и дисперсию
  • Как рассчитать среднее арифметическое Как рассчитать среднее арифметическое
  • Как посчитать среднее арифметическое Как посчитать среднее арифметическое
  • Как вычислить среднее значение Как вычислить среднее значение
  • Как рассчитать среднее число Как рассчитать среднее число
  • Как вычислить среднее арифметическое число Как вычислить среднее арифметическое число
  • Как определить среднее арифметическое Как определить среднее арифметическое
  • Как найти среднее арифметическое значение Как найти среднее арифметическое значение
  • Как найти среднее геометрическое Как найти среднее геометрическое
  • Как найти среднюю величину Как найти среднюю величину
  • Как вычислить средний балл Как вычислить средний балл
  • Как считать среднюю численность Как считать среднюю численность
  • Как вычислить среднюю цену Как вычислить среднюю цену
  • Что такое функция СРЗНАЧ в Excel и для чего она нужна Что такое функция СРЗНАЧ в Excel и для чего она нужна
  • Как определить средний возраст сотрудников Как определить средний возраст сотрудников
  • Как вычислить среднее арифметическое Как вычислить среднее арифметическое
  • Как считать средний балл Как считать средний балл
  • Как вычислить среднюю скорость Как вычислить среднюю скорость

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

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

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

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

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

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

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

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

СРЗНАЧ применяется для возврата среднего арифметического указанных ячеек.

СРЗНАЧ число 1; [число 2];…)

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

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

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

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

Вы можете указать числа непосредственно. Например,

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

Чтобы вычислить среднее по столбцу, укажите ссылку на него целиком:=СРЗНАЧ(A:A)

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

Чтобы вычислить в каком-то диапазоне, укажите его:=СРЗНАЧ(A1:C20)

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

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

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

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

=ОКРУГЛ(СРЗНАЧ(B3:B5; B7:B9; B11);0)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Пустые клетки таблиц игнорируются.
  • Текст, включая пустые строки (""), возвращаемые другими формулами, оценивается как 0.
  • Логическое ИСТИНА оценивается как 1, а ЛОЖЬ оценивается как 0.

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

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

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

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

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

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

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

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

  • Диапазон - диапазон ячеек, которые будут проверены по заданным параметрам.
  • Критерии - условие, применяемое для определения, какие ячейки усреднять. Они могут быть представлены в виде числа, логического выражения, текста или ссылки, например, 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)

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

Довольно часто вам может потребоваться усреднить ячейки, в которых количество больше или меньше определенного числа. Например, есть перечень чисел в столбце A, и мы хотим найти среднее из тех из них, которые больше 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)

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

Например, чтобы рассчитать средние продажи (C2: C8), превышающие указанное в E4:

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

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

=СРЗНАЧЕСЛИ($B$2:$B$8; "<="&"22-07-2020"; $C$2:$C$8)

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

=СРЗНАЧЕСЛИ($B$2:$B$8; "<="&СЕГОДНЯ(); $C$2:$C$8)

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

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

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

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

  • Диапазон_подсчёта - область ячеек, которые вы хотите усреднить.
  • диапазон_критерия_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, датами в колонке В (критерий_1) и статусом в С (критерий_2), формула выглядит следующим образом:

=СРЗНАЧЕСЛИМН($D$2:$D$8; $B$2:$B$8; "<=22-07-2020"; $C$2:$C$8; "<>")

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

Примечание. Когда вы используете число или дату в сочетании с логическим оператором в указании условия, то заключайте эту комбинацию в двойные кавычки -  “<=22.07.2020”.

Что нужно запомнить!

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

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

ОСОБЕННОСТИ

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

=СРЗНАЧЕСЛИ($B$2:$B$10; «<>»; $C$2:$C$8)

то в любом случае находить среднее мы будем по $C$2:$C$8.

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

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

Поскольку СРЗНАЧЕСЛИМН работает с логикой «И», а СРЗНАЧЕСЛИ допускает только 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. 

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

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

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

{#Н/Д!; 1; 2; 1; 2; #Н/Д!; 1}

ЕЧИСЛО преобразует любые числа в ИСТИНА, а ошибки в ЛОЖЬ:

{ЛОЖЬ; ИСТИНА; ИСТИНА; ИСТИНА; ИСТИНА; ЛОЖЬ; ИСТИНА}

Этот массив отправляется на логическую проверку ЕСЛИ. 

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

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

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

Пример 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))}

Вот как рассчитывается среднее в Excel. 

Я благодарю вас за чтение и надеюсь еще не раз увидеть вас в нашем блоге!

Понятие среднего арифметического

Среднее арифметическое нескольких чисел — это сумма этих чисел, которую разделили на количество слагаемых. Вот так:

что такое среднее арифметическое

Например, найдем среднее арифметическое чисел 5, 6 и 7. Обозначим среднее значение латинской буквой «m» и посчитаем сумму этих чисел.

Разделим результат на количество чисел в задании, то есть на три.

Так получилась формула среднего арифметического:

формула среднего арифметического

Способы вычисления среднего арифметического

Стандартная формула. Чтобы найти среднее арифметическое, нужно сложить все числа и поделить эту сумму на их количество. Формула выглядит так:

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

где

  • x — среднее арифметическое;
  • xⁿ — конкретное значение;
  • n — количество значений.

Преимущества:

  • подходит при нормальном распределении значений в выборке;
  • легко считать;
  • интуитивно доступно.

Недостатки:

  • сложно представить распределение значений;
  • можно запутаться в разных величинах.

Вычисление моды или наиболее часто встречающегося значения. Формула такая:

Формула вычисление наиболее часто встречающегося значения

где

  • M₀ — мода;
  • x₀ — нижняя граница интервала, который содержит моду;
  • n — величина интервала;
  • fm — частота (сколько раз в ряду встречается то или иное значение);
  • fm-1 — частота интервала предшествующего модальному;
  • fm+1 — частота интервала следующего за модальным.

Преимущества:

  • подходит для формирования общественного мнения;
  • подходит для нечисловых данных;
  • доступно для понимания.

Недостатки:

  • моды может не быть при отсутствии повторов;
  • мод может быть несколько (многомодальное распределение).

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

В современной школе Skysmart верят, что математика — для всех, просто каждому нужен свой подход. Запишите ребенка на бесплатный пробный урок, чтобы начать заниматься в своем темпе, с внимательным личным учителем и в удовольствие.

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

формула вычисление медианы

где

  • Mₑ — медиана;
  • x₀ — нижняя граница интервала, который содержит медиану;
  • h — величина интервала;
  • f i — частота (сколько раз в ряду встречается то или иное значение);
  • Sm-1 — сумма частот интервалов предшествующих медианному;
  • fm — число значений в медианном интервале (его частота).

Преимущества:

  • дает самую реалистичную оценку;
  • устойчива к выбросам.

Недостатки:

  • сложнее вычислить из-за необходимости упорядочивать.

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

Средняя скорость движения — это весь пройденный путь, поделенный на время движения. Формула:

формула средней скорости движения

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

Примеры расчета среднего арифметического

Пример 1. Вычислить среднее арифметическое 33,3 и 55,5.

Как решаем:

Чтобы найти среднее арифметическое двух чисел, надо сложить эти числа и результат разделить на 2: (33,3 + 55,5) : 2 = 88,8 : 2 = 44,4.

Пример 2. Посчитать среднее арифметическое 7,5 и 8 и 0,5.

Как решаем:

Чтобы найти среднее арифметическое трех чисел, надо сложить эти числа и результат разделить на 3: (7,5 + 8 + 0,5) : 3 = 16 : 3 = 5,33.

Пример 3. Найти среднее арифметическое 202, 105, 67 и 9.

Как решаем:

Чтобы найти среднее арифметическое трех чисел, надо сложить эти числа и результат разделить на 4: (202 + 105 + 67 + 9) : 4 = 383 : 4 = 95,75.

Пример 4. Сколько в среднем тратит школьник денег в неделю, если в понедельник он потратил 80 рублей, во вторник 75 рублей, в среду и четверг по 100 рублей, в пятницу 50 рублей.

Как решаем:

Чтобы найти сколько в среднем школьник потратил за пять дней, надо сложить эти суммы и результат разделить на 5: (80 + 75 + 100 + 100 + 50) : 5 = 405 : 5 = 81.

Ответ: школьник в неделю тратит в среднем 81 рубль.

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

Что такое среднее арифметическое

Среднее арифметическое — это сумма всех чисел в ряду, разделённая на количество слагаемых.

Как найти среднее арифметическое

Например, перед вами ряд чисел «1, 2, 3, 4, 5, 6». Как следует из определения, чтобы узнать среднее арифметическое, нужно сложить все данные вам числа, а потом разделить получившийся результат на количество этих чисел. В приведённом примере — на шесть. Вот как это выражается формулой:

Как найти среднее арифметическое

Допустим, вам нужно определить среднее арифметическое для чисел 4, 5 и 6. Складываем 4 + 5 + 6 = 15. Теперь делим 15 на 3 и получаем 5. Это и будет среднее арифметическое.

Как найти среднее арифметическое

Таким же образом оно подсчитывается для десятичных и обыкновенных дробей.

Пример расчёта среднего арифметического для обыкновенных дробей будет выглядеть так:

Пример расчёта среднего арифметического для обыкновенных дробей

А это пример, как найти среднее арифметическое для десятичных дробей:

Вычисление среднего арифметического для десятичных дробей

Как это пригодится в жизни

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

Я уже много раз говорил о том, что

усреднение - опасная штука

. Когда цифр много, то так и хочется упростить реальность и сказать: а в среднем все так и этак. Казалось бы, ну что уж тут сложного: это плюс то делим на сё - вот тебе и среднее. И в большинстве случаев именно здесь и начинаются грубые, но совсем незаметные ошибки.

Как же нужно правильно считать среднее?

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

Существует много видов средних, но в каждой ситуации только одно из них правильное. Только один вид среднего следует использовать в каждом конкретном случае, и ошибка может вам стоить очень дорого.

Среднее арифметическое

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

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

Дело в том, что в основе такого усреднения лежит закон больших чисел и допущение, что исходная величина

распределена нормально

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

Проблема в том, что

в бизнесе нормальное распределение встречается довольно редко

.

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

не

нормально, и их, как правило, не стоит усреднять с помощью средего арифметического.

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

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

Среднее гармоническое

Первые 100 км автомобиль проехал со скоростью 50 км/ч, а следующие 100 км - со скоростью 80 км/ч. С какой средней скоростью двигался автомобиль на всем пути?

Сначала, наверное, может показаться, что правильное значение 65 км/ч, потому что (50+80)/2 = 65.

Однако быстро становится понятно, что если бы другой автомобиль двигался со средней скоростью, то он провел бы в пути столько же времени, что и первый. Именно в этом смысл усреднения в данном случае.

И вот тут на помощь приходит среднее гармоническое:

Для нашей задачи искомое среднее равно 2/(1/50+1/80)=61.54 км/ч. И действительно в первом случае автомобиль затратил 2 часа на преодоление 100 км со скоростью 50 км/ч и еще 1.25 часа ему потребовалось на следующие 100 км, потому что скорость возросла до 80 км/ч. Таким образом, всего ушло 3.25 часа.

Если бы автомобиль все 200 км двигался со скоростью 61.54 км/ч, то у него также ушло бы на дорогу 3.25 часа.

Близость значений 65 и 61.54 не должна вас обманывать. Среднее гармоническое в данном случае не просто дает более точный результат.

Это единственно правильный способ усреднения

, потому что он соответствует физическому смыслу измеряемых явлений.

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

Обычно задачи этого типа связаны со временем и производительностью: например, сотрудник А выполняет операцию за 10 минут, а сотрудник Б - за 25 минут; сколько в среднем им требуется времени на выполнение операции, если бы они работали вместе?

Правильный ответ: 14 мин 17 сек. А отличие от среднего арифметического (17 мин 30 сек) уже превышает 20%. Именно на столько вы бы ошиблись, если бы усредняли неправильно.

Среднее геометрическое

В 2009 году выручка выросла на 12% от уровня 2008 года, а в в 2010 - на 42% от уровня 2009. На сколько росла выручка в среднем за год?

Можно предположить, что есть несколько вариантов усреднения. Во-первых, среднее арифметическое: (12+42)/2 = 27%. Во-вторых, сложный процент: 1.12*1.42=1.5904, т.е. 59.04% за 2 года или 28.02% за год.

Но "в среднем" означает, что применив это значение к каждому году, мы получим тот же самый результат, что и при использовании множества исходных значений.

Проверяем. Среднее арифметическое: 1.27*1.27=1.6129 (на 61.29%). Сложный процент: 1.2802*1.2802=1.6389 (на 63.89%). Результаты мало того, что разные, так и оба неправильные, потому что выручка за 2 года выросла на 59.04%.

В данном случае есть только один осмысленный способ усреднения - это среднее геометрическое

Таким образом, для нашей задачи средний прирост за год составит 26.11% (проверка 1.2611*1.2611=1.5904)

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

Вместо заключения

Повторю главные моменты:

- среднее арифметическое далеко не всегда соответствует смыслу и физической сущности усредняемого показателя;

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

Средняя зарплата… Средняя продолжительность жизни… Практически каждый день мы с вами слышим эти словосочетания, используемые для описания множества одним единственным числом. Но как ни странно, «среднее значение» — достаточно коварное понятие, часто вводящее в заблуждение обычного, неискушенного в математической статистике, человека.

В чем проблема?

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

Давайте обратимся к классическому примеру со средней зарплатой.

В какой-то абстрактной компании работает десять сотрудников. Девять из них получают зарплату около 50 000 рублей, а один 1 500 000 рублей (по странному совпадению он же является генеральным директором этой компании).

Выборка зарплат

Средним значением в данном случае будет 195 150 рублей, что согласитесь, неправильно.

Какие способы вычисления среднего бывают?

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

Формула:

  • x– среднее арифметическое;
  • x– конкретное значение;
  • n – количество значений.

Плюсы:

  • Хорошо работает при нормальном распределении значений в выборке;
  • Легко вычислить;
  • Интуитивно понятно.

Минусы:

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

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

Формула:

  • M– мода;
  • x– нижняя граница интервала, который содержит моду;
  • n – величина интервала;
  • fm– частота (сколько раз в ряду встречается то или иное значение);
  • fm-1 – частота интервала предшествующего модальному;
  • fm+1 – частота интервала следующего за модальным.

Плюсы:

  • Прекрасно подходит для получения представления об общественном мнении;
  • Хорошо подходит для нечисловых данных (цвета сезона, хиты продаж, рейтинги);
  • Проста для понимания.

Минусы:

  • Моды может просто не быть (нет повторов);
  • Мод может быть несколько (многомодальное распределение).

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

Формула:

Медиана формула

  • Me – медиана;
  • x– нижняя граница интервала, который содержит медиану;
  • h – величина интервала;
  • f – частота (сколько раз в ряду встречается то или иное значение);
  • Sm-1 – сумма частот интервалов предшествующих медианному;
  • fm – число значений в медианном интервале (его частота).

Плюсы:

  • Дает самую реалистичную и репрезентативную оценку;
  • Устойчива к выбросам.

Минусы:

  • Сложнее вычислить, так как перед вычислением выборку нужно упорядочить.

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

А теперь давайте вернемся к нашему примеру и посчитаем все три варианта среднего при помощи специальных функций Excel:

  • СРЗНАЧ(число1;[число2];…) — функция для определения среднего арифметического;
  • МОДА.ОДН(число1;[число2];...) — функция моды (в более старых версиях Excel использовалась МОДА(число1;[число2];...));
  • МЕДИАНА(число1;[число2];...) — функция для поиска медианы.

И вот какие значения у нас получились:

Расчет среднего арифметического, моды и медианы

В данном случае мода и медиана гораздо лучше характеризуют среднюю зарплату в компании.

Но что делать, когда в выборке не 10 значений, как в примере, а миллионы? В Excel это не посчитать, а вот в базе данных где хранятся ваши данные, без проблем.

Вычисляем среднее арифметическое на SQL

Тут все достаточно просто, так как в SQL предусмотрена специальная агрегатная функция AVG.

И чтобы ее использовать достаточно написать вот такой запрос:

/* Здесь и далее salary - столбец с зарплатами, а employees - таблица сотрудников в нашей базе данных */

SELECT AVG(salary) AS 'Средняя зарплата'
FROM employees

Вычисляем моду на SQL

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

Напишем запрос:

/* WITH TIES необходимо добавлять к TOP() если множество многомодально, то есть у множества несколько мод */
SELECT TOP(1) WITH TIES salary AS 'Мода зарплаты'
FROM employees
GROUP BY salary
ORDER BY COUNT(*) DESC

Вычисляем медиану на SQL

Как и в случае с модой, в SQL нет встроенной функции для вычисления медианы, зато есть универсальная функция для вычисления процентилей PERCENTILE_CONT.

Выглядит все это так:

/* В данном случае процентиль 0.5 и будет являться медианой */

SELECT TOP(1) PERCENTILE_CONT(0.5)
       WITHIN GROUP (ORDER BY salary)
       OVER() AS 'Медианная зарплата'
FROM employees

Подробнее о работе функции PERCENTILE_CONT лучше почитать в справке Microsoft и Google BigQuery.

Какой способ все-таки использовать?

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

Но это не всегда так. Если вы работаете со средним, то остерегайтесь многомодального распределения:

Бимодальное распределение

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

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

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

Вывод:

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

Окончательный выбор меры центральной тенденции всегда лежит на аналитике.

Полезные ссылки:

Роман Романчук

Digital-аналитик и иногда спортсмен. Руководитель отдела аналитики финансового маркетплейса Sravni.ru

Добавить комментарий