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

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

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

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

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

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

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

Среднее арифметическое  (μ для совокупности, x̄ для выборки; англ. Arithmetic Mean) – показатель описательной статистики, сумма элементов Датасета (Dataset), разделенная на их количество. Рассчитывается с помощью формулы:

$$μ = \frac{Σ_{i=1}^n a_i}{n}, где$$
$$μ\space{–}\space{среднее,}$$
$$Σ_{i=1}^n a_i\space{–}\space{сумма}\space{всех}\space{элементов}\space{выборки},$$
$$n\space{–}\space{количество}\space{наблюдений}$$

По умолчанию рассматривают именно с среднее арифметическое, остальные разновидности среднего рассматривают реже:

Разновидности среднего значения

В данной статье рассматриваются простые средние значения без Весовой функции (Weight Function).

Пример. Для небольшого списка средним арифметическим будет:

$$μ = \frac{1 + 6 + 3 + 2}{4} = \frac{12}{4} = 3$$

Понятие используется в Науке о данных множеством способов:

• В сочетании с другими показателями описательной статистики для первичного представления о признаке (Feature)
• Для визуальной оценки скошенности признака:

Данные скошены влево, и одного Среднего для описания признака уже недостаточно

Для индикации Выбросов (Outlier) и проч.

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

Рассчитать среднее автоматически позволит библиотека statistics. Установим библиотеку и импортируем ее для начала:

Инициализируем список:

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

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

Формула:

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

Плюсы:

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

Минусы:

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

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

Формула:

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

Плюсы:

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

Минусы:

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

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

Формула:

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

Плюсы:

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

Минусы:

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

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

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

•  — функция для определения среднего арифметического;
•  — функция моды (в более старых версиях Excel использовалась );
•  — функция для поиска медианы.

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

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

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

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

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

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

/* Здесь и далее 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 нет встроенной функции для вычисления медианы, зато есть универсальная функция для вычисления процентилей .

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

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

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

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

Мода

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

Ну, скажем, вы планируете вечеринку, и вам нужно выбрать день для её проведения. Дни недели — такой же числовой ряд, что и любой другой. Это всего лишь числа от 1 до 7. Среднее арифметическое и медиана тут не помогут (Лиза и Паша могут в пятницу, а Коля и Петя — в воскресенье; поэтому назначим субботу). Что делать в таком случае? Конечно, выбрать тот день, который выберет большинство.

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

Преимущества моды: — Прекрасно работает для получения представления об общественном мнении; — Даёт представление о потребностях большой части людей (там, где среднее арифметическое даёт лишь осечку); — Проста для понимания.

Недостатки моды: — Для её вычисления требуется больше усилий (нужно собрать мнения и обработать их); — Победителю достаётся всё: мода выявляет только одного лидера.

Может ли так произойти, что среднее арифметическое станет равным среднему геометрическому?

Конечно, может. Но только в двух случаях. Если имеется ряд чисел, состоящий только либо из единиц, либо из нулей. Примечательно также то, что ответ не зависит от их количества.

Доказательство с единицами: (1 + 1 + 1) / 3 = 3 / 3 = 1 (среднее арифметическое).

∛(1 × 1 × 1) = ∛1 = 1(среднее геометрическое).

Доказательство с нулями: (0 + 0) / 2=0 (среднее арифметическое).

√(0 × 0) = 0 (среднее геометрическое).

Другого варианта нет и быть не может.

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

Одной из особенностей расчётов данного значения является простота процедуры. Провести расчёты сможет любой желающий. Для этого не надо иметь специальное образование. Часто нет необходимости применять и вычислительную технику.

В качестве ответа на вопрос как найти среднее арифметическое рассмотрим ряд ситуаций.

Самым простым вариантом расчёта данной величины есть подсчёт её для двух чисел. Процедура проведения расчёта в этом случае является очень простой:

1. Первоначально требуется провести операцию сложения выбранных чисел. Это часто можно сделать, как говорится, вручную, не используя электронную технику.
2. После того как сложение произведено и получен его результат необходимо произвести деление. Данная операция подразумевает разделение суммы двух сложенных чисел на два – количество сложенных чисел. Именно такое действие и позволит получить требуемую величину.

Формула

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

В этой формуле применяется следующее обозначение:

А и В – это заранее выбранные числа, для которых необходимо находить значение.

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

Среднее геометрическое (Geometric Mean) – корень N-й степени из произведения всех значений:

$$x̅_{geom} = \sqrt{x_1 × x_1 ×… × x_n},\space{где}$$
$$x̅_{geom}\space{–}\spaceсреднее\space{геометрическое,}$$
$$x_n\space{–}\space{n-й}\space{элемент}\space{выборки}$$

Если Выборка (Sample) содержит два значения, мы извлекаем квадратный корень из перемноженных элементов. Для трех значений используется кубический корень и так далее.

Пример. Как построить квадрат той же площади, что и прямоугольник 2 x 18? Вычислим среднее геометрическое:

$$x̅_{geom} = \sqrt{2 × 18} = 6$$

Площади равны

Наш квадрат будет иметь ту же площадь (36), и ребра, равные 6.

В Машинном обучении (ML) Критерий G-Mean (Geometric Mean) – это Среднее геометрическое, определяющее качество классификации большинства и меньшинства. Низкий G-Mean-критерий является признаком плохой работы Модели (Model) в Бинарной классификации (Binary Classification) для положительных случаев.

Средневзвешенное значение — что это и как его вычислить?

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

Средние величины: смысл и различия

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

Часто в рамках школьного курса изучают и среднее геометрическое. Расчет данного значения базируется на извлечении корня n-ной степени из произведения n-членов. Если брать те же числа: 27, 22, 34 и 37, то результат вычислений будет равен 29,4.

Среднее гармоническое в общеобразовательной школе обычно не является предметом изучения. Тем не менее оно используется довольно часто. Эта величина обратна среднему арифметическому и рассчитывается как частное от n — количества значений и суммы 1/a₁+1/a₂+…+1/a_n. Если снова брать тот же ряд чисел для расчета, то гармоническое составит 29,6.

Средневзвешенное значение: особенности

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

Прежде всего, стоит рассказать, что подразумевается под «весом» того или иного значения. Проще всего объяснить это на конкретном примере. Два раза в день в больнице происходит замер температуры тела у каждого пациента. Из 100 больных в разных отделениях госпиталя у 44 будет нормальная температура — 36,6 градусов. У еще 30 будет повышенное значение — 37,2, у 14 — 38, у 7 — 38,5, у 3 — 39, и у двух оставшихся — 40. И если брать среднее арифметическое, то эта величина в общем по больнице будет составлять больше 38 градусов! А ведь почти у половины пациентов совершенно нормальная температура. И здесь корректнее будет использовать средневзвешенное значение, а «весом» каждой величины будет количество людей. В этом случае результатом расчета будет 37,25 градусов. Разница очевидна.

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

Разновидности

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

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

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

Способы расчета

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

Проще всего будет рассмотреть вычисление на конкретном примере.

Заработная плата (тыс. руб.)	Число рабочих (чел.)
32	20
33	35
34	14
40	6

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

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

x = (a₁*w₁+a₂*w₂+…+a_n*w_n)/(w₁+w₂+…+w_n)

Для примера же вычисление будет таким:

x = (32*20+33*35+34*14+40*6)/(20+35+14+6) = (640+1155+476+240)/75 = 33,48

Очевидно, что нет особых сложностей с тем, чтобы вручную рассчитать средневзвешенное значение. Формула же для вычисления этой величины в одном из самых популярных приложений с формулами — Excel — выглядит как функция СУММПРОИЗВ (ряд чисел; ряд весов)/СУММ (ряд весов).

fb.ru

Среднее время (Форматирование/Formattings)

​А​​ больше 23, оно​Рассмотрим функцию ВРЕМЯ(), у​Поле «Диапазон усреднения» не​ ручного ввода в​​Дальнейшие действия точно такие​ проделывайте в поле​​ его, и жмем​ нельзя.​ расположенных в столбце​ не пересчитывает, остается​ 28.02.2015.​​ время стоянки, по​Вр. выполнения работы​ поле 20,5 минуты.​ таком формате (НЕ​​С помощью формулы =ВРЕМЯ(0;20;35) введем в​

​содержатся часы, в​​ делится на 24;​ ​ которой 3 аргумента:​ ​ обязательно для заполнения.​ ячейку. После того,​ же.​ «Число 2». И​​ на кнопку «OK».​Например, если выделить два​

​ или в строке​​ та же цифра​​Как сделать так​​ данной формуле: =E40*60/D40,​ — время когда​buchlotnik​ в десятичных долях)​ ячейку​​ столбце​ остаток от деления​​ часы, минуты, секунды.​ Ввод в него​ как открылось окно​Но, не забывайте, что​ так до тех​Открывается окно аргументов данной​ столбца, и вышеописанным​ документа. Находясь во​ =(​ чтобы можно было​

​ но там где​​ обращение выполнили​ ​: значит так​

​Среднее время -​​А1 ​​B​ будет соответствовать значению​ Записав формулу =ВРЕМЯ(10;30;0),​ данных является обязательным​

​ аргументов функции, нужно​​ всегда при желании​ пор, пока все​ функции. В поля​ способом вычислить среднее​​ вкладке «Главная», жмем​​chumich, отдельно пересчитал​ просто указать период​​ время стоянки меньше​

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

Среднее гармоническое представить сложнее, чем предыдущих представителей «средних», но оно не менее полезно. Между прочим, само понятие «гармоники» в математике связано с обратными числами (1/2, 1/3 и т.д.). Среднее гармоническое помогает нам вычислить среднее арифметическое в рядах чисел, заданных обратными значениями. Это случается чаще, чем вы можете подумать.

Например, если я еду со скоростью 30 км/ч, это значит, что я получаю определённый результат (30 км) за какую-либо единицу времени (1 час). Когда мы хотим узнать среднее значение для нескольких скоростей (Х и Y), нужно думать о результате и единицах измерения, а не об исходных цифрах.

средняя скорость = общий результат/общая единица измерения

Возьмём двух работников: Х и Y. Оба работают в одном проекте и выполняют одинаковое количество работы, но скорость их работы разная. Какова средняя скорость их работы?

Скажем, работник Х кладёт 30 кирпичей в час, а работник Y — 60 кирпичей в час. Значит, на один кирпич у каждого работника уходит:

• У работника X укладка одного кирпича займёт 1/X времени (1/30);
• У работника Y укладка одного кирпича займёт 1/Y времени (1/60)

Складываем результаты и единицы измерения:

Общий результат: 2 кирпича (Х и Y уложили по одному) Общая единица времени: 1/X + 1/Y (у каждого уходит разное количество времени)

Средней скоростью обоих работников будет:

Если бы у нас было 3 работника (X, Y и Z), их средняя скорость вычислялась бы по формуле:

Здорово же иметь одну формулу вместо того, чтобы каждый раз заниматься долгими вычислениями. Даже вычисляя среднюю скорость 5 нерадивых работников стало бы головной болью. Помните наш первый пример про скорость, с которой вы едете на работу и домой? Чтобы найти среднюю скорость вашего передвижения в тот день, мы просто используем формулу.

При этом нам даже не нужно знать, где находится дом или офис! Теперь вместо X и Y у нас не кирпичи, а количество километров за единицу времени. Вне зависимости от расстояния результат один и тот же: допустим, некое количество километров R мы проходим на скорости X, а другое количество километров R — на скорости Y. Средняя скорость при этом будет вычисляться так же, как вычисляется средняя скорость прохождения 1 км на скорости X и одного километра на скорости Y:

Ключевая идея: Среднее гармоническое используется тогда, когда один и тот же объём работы выполняется на разных скоростях.

Ещё более ключевая идея: Помните, что среднее значение — это один элемент, способный передать суть целой группы элементов. В нашем примере с работой и офисой в среднем туда-обратно мы едем на скорости 40 км/ч (вместо 30 км/ч туда и 60 км/ч обратно)

Важно помнить, что средней скоростью мы заменяем каждую «стадию»

Ещё несколько примеров из жизни среднего гармонического:

• Передача данных: Мы передаём данные между клиентом и сервером. Клиент посылает данные за плату 10 Гб/доллар, а сервер получает их за плату 20 Гб/доллар. Каково среднее количество Гб, которые можно передать и получить за один доллар? Мы усредняем значения для клиента и для сервера: 2 / (1/10 + 1/20) = 13,3 Гб/доллар для каждой стороны. Поскольку данные и передаются, и получаются (каждая сторона выполняет свою половину работу), мы делим это значение на 2 и получаем следующее значение: 6,65 Гб за доллар.
• Продуктивность машины: У нас есть производственная установка для подготовки и полировки деталей. За час установка может подготовить 25 деталей; либо за тот же час она может отполировать 10 деталей. Какова средняя производительность установки? Усредняем значения для каждой стадии: 2 / (1/25 + 1/10) = 14,28 деталей/час. Снова делим это значение на два, поскольку нас интересует средняя производительность установки, если она занимается сразу двумя фазами: получаем 7,14 деталей/час.

Занимательная математика. Среднее значение

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

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

Давайте рассмотрим пример.

Пример 1. Даны числа: 6, 7, 11. Нужно найти их среднее значение.

Решение.

Для начала найдем сумму всех данных чисел.

6 + 7 + 11 = 24

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

24 : 3 = 8

Следовательно, среднее значение чисел 6, 7 и 11 — это 8. Почему именно 8? Да потому, что сумма 6, 7 и 11 будет такая же, как трех восьмерок. Это отлично видно на иллюстрации.

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

Рассмотрим еще один пример, чтобы закрепить полученные знания.

Пример 2. Даны числа: 3, 7, 5, 13, 20, 23, 39, 23, 40, 23, 14, 12, 56, 23, 29. Нужно найти их среднее арифметическое значение.

Решение.

Находим сумму.

3 + 7 + 5 + 13 + 20 + 23 + 39 + 23 + 40 + 23 + 14 + 12 + 56 + 23 + 29 = 330

Делим на количество слагаемых (в этом случае — 15).

330 : 15 = 22

Следовательно, среднее значение данного ряда чисел равно 22.

Теперь рассмотрим отрицательные числа. Вспомним, как их суммировать. Например, у вас есть два числа 1 и -4. Найдем их сумму.

1 + (-4) = 1 – 4 = -3

Зная это, рассмотрим еще один пример.

Пример 3. Найти среднее значение ряда чисел: 3, -7, 5, 13, -2.

Решение.

Находим сумму чисел.

3 + (-7) + 5 + 13 + (-2) = 12

Так как слагаемых 5, разделим получившуюся сумму на 5.

12 : 5 = 2,4

Следовательно, среднее арифметическое значение чисел 3, -7, 5, 13, -2 равно 2,4.

Для того чтобы посчитать среднее значение ряда чисел, необходимо использовать функцию AVERAGE. Синтаксис для этой функции:
= Average (argument1, argument2, … argument255)
где argument1, argument2, … argument255 — это либо числа, либо ссылки на ячейки (под ячейками подразумеваются диапазоны и массивы).

Чтобы было более понятно, опробуем полученные знания.

1. Введите числа 11, 12, 13, 14, 15, 16 в ячейки С1 – С6.
2. Выделите ячейку С7, нажав на нее. В этой ячейке у нас будет отображаться среднее значение.
3. Щелкните на вкладке «Формулы».
4. Выберите More Functions > Statistical для того, чтобы открыть выпадающий список.
5. Выберите AVERAGE. После этого должно открыться диалоговое окно.
6. Выделите и перетащите туда ячейки С1–С6, чтобы задать диапазон в диалоговом окне.
7. Подтвердите свои действия клавишей «ОК».
8. Если вы все сделали правильно, в ячейке С7 у вас должен появиться ответ – 13,7. При нажатии на ячейку C7 функция (= Average (C1: C6)) будет отображаться в строке формул.

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

fb.ru

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

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

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

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

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

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

Вывод:

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

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

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

• SQL и теория вероятностей (YouTube)
• Анализ нормальности распределения данных (YouTube)
• Меры центральной тенденции

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

Digital-аналитик и иногда спортсмен.

• Учимся применять оконные функции — 29.09.2020
• Автоматизация отчетности при помощи SQL и Power BI — 05.04.2020
• Зачем аналитику нужно программирование на SQL? — 22.10.2019

Как найти среднее значение в Excel?

Ситуация такая. Имеется следующая таблица:
В столбиках, закрашенных красным цветом содержатся численные значения оценок по предметам. В столбце «Средний балл» требуется подсчитать их среднее значение.
Проблема вот в чем: всего предметов 60-70 и часть из них на другом листе.
Я смотрела в другом документе уже подсчитано среднее, а в ячейке стоит формула типа
=’имя листа’!|Е12
но это делал какой-то программист, которого уволили.
Подскажите, пожалуйста, кто разбирается в этом.

Гектор

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

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

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

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

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

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

Давайте найдем среднее арифметическое, прибавив время, за все пять дней. Итак,

15 + 20 + 25 + 25 + 30 = 115

Теперь разделим эту сумму на количество дней

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

Задания ЕГЭ, на тему «Среднее арифметическое»

Задание 1:

Среднее арифметическое 7 натуральных чисел равно 12. К ним добавили восьмое число такое, что среднее арифметическое этих восьми чисел равно 14. Найдите восьмое число.

Решение:

Согласно оределению среднего арифметического для 7 чисел имеем:

a_{ср.арифм 7} =  
a₁+ a₂+ …+ a₇
7

  =  
S₇
7

 
  =   12

А для 8 чисел получется, что

a_{ср.арифм 8} =  
a₁+ a₂+ …+ a₇+ a₈
8

  =  
S₇+ a₈
8

 
  =   14

откуда

S₇ = 12 × 7 = 84;

  S₇+ a₈ = 14 × 8 = 112;

 a₈ = 112 — S₇ = 112 — 84 = 28;

Ответ: a₈ = 28

Задание 2:

На доске написано более 40, но менее 50 целых чисел. Среднее арифметическое этих чисел равно −4, среднее арифметическое всех положительных из них равно 5, а среднее арифметическое всех отрицательных из них равно −5.
а). Сколько чисел написано на доске?

б). Каких чисел написано больше: положительных или отрицательных?

в). Какое наибольшее количество положительных чисел может быть среди них?

Решение:

Пусть всего  n чисел, 40 < n < 50.Пусть  k — количество положительных чисел, рассматриваемого множества;m — количество отрицательных, и p — число нулей.
Тогда 

 k + m + p = n

по определению среднего арифметического сумма множества чисел равна призведению среднего арафметического и их количества и по условию задачи имеем: 

 5·k + -5·m + 0·p = -4·n   (2.1)

 5·(k — m) = -4·n      (2.2)

(включаем логику )

а). Очевидно, что левая часть полученного равенства 2.2 делится на 5, поэтому nтоже должно делиться на 5. По условию 40 n , отсюда

n = 45.

Таким образом, написано 45 целых чисел.

б). Подставим в равенство 2.2 полученное для n значение, тогда

 5·k + -5·m = -180   или   m — k = 36; m = 36 + k,   (2.3)

поскольку m ≥ 0 и k ≥ 0, то m > k, то есть отрицательных чисел больше, чем положительных.

в). Для определения наибольшего возможного количества целых положительных чисел удовлетворяющих условиям задачи, возпользуемся выражениями 2.1 и 2.3 с подставленным значением n. С учетом того, что p ≥ 0получаем:

k + m ≤ 45;
m = 36 + k

или подставляя в первое значение m:

2·k ≤ 45-36, k ≤ 4,5

Таким образом положительных чисел может быть не более 4.

Ответ: а) 45; б) отрицательных; в) 4.

P.S. На этой странице используется Бета версия программы расчета среднего арифметического, об обнаруженных недочетах, а так же возможных пожеланиях просьба сообщить на форум сайта (окно для входа на форум находится в нижней части страницы).

1. Другие кому-то, возможно, более привычные определения:Среднее арифметическое нескольких чисел равно сумме этих чисел, 
делённой на количество слагаемых в этой сумме (Математика, 5 класс).Средним арифметическим ряда чисел называется частное от деления суммы этих чисел на число слагаемых (Алгебра, Макарычев, 7 класс).

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

σ 2 =  
(a₁ — a_cp)2 + (a₂ — a_cp)2 + …+ (a_n — a_cp)2
n
 

3. Среднеквадратическое отклонение σ вычисляется как корень квадратный от дисперсий и возвращает нас в область сопоставимых со средним арифметическим величин:

σ = 

√

(a₁ — a_cp)2 + (a₂ — a_cp)2 + …+ (a_n — a_cp)2
n
 

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

V =  

σ

a_ср
 
  × 100%

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

R = a_max — a_min

6. Среднее линейное отклонение определяется как средняя арифметическая величина абсолютных значений отклонений каждого из ряда чисел от их среднего арифметического:

δ =  
|a₁ — a_cp| + |a₂ — a_cp| + …+ |a_n — a_cp|
n
 

Определение среднего значения по условию

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

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

1. Встаем в результирующую ячейку и жмем кнопку “Вставить функцию” (fx) слева от строки формул.
2. В Мастере функций выбираем категорию “Статистические”, кликаем по оператору “СРЗНАЧЕСЛИ” и жмем ОК.
3. Откроются аргументы функции, после заполнения которых кликаем OK:
   • в значении аргумента “Диапазон” указываем (вручную или выделив с помощью левой кнопки мыши в самой таблице) требуемую область ячеек;
   • в значении аргумента “Условие”, соответственно, задаем наше условие попадания ячеек из отмеченного диапазона в общий расчет. В нашем случае, это выражение “>0”. Вместо конкретного числа, в случае необходимости, в условии можно указать адрес ячейки, содержащей числовое значение.
   • поле аргумента “Диапазон_усреднения” можно оставить пустим, так как его обязательное заполнение требуется только при работе с текстовыми данными.
4. Среднее значение с учетом заданного нами условия отбора ячеек отобразилось в выдранной ячейке.