Среднее арифметическое. Определение среднего значения, вариации и формы распределения
Допустим, вам нужно найти среднее количество дней для выполнения задач разными сотрудниками. Кроме того, вы хотите вычислить среднюю температуру на определенный день в течение 10-годичного периода времени. Вычисление среднего значения для группы чисел можно выполнить несколькими способами.
Функция СРЗНАЧ вычисляет среднее значение, то есть центр набора чисел в статистическом распределении. Существует три наиболее распространенных способа определения среднего значения:
Среднее значение Это среднее арифметическое, которое вычисляется путем добавления группы чисел и деления их на количество этих чисел. Например, средним значением для чисел 2, 3, 3, 5, 7 и 10 будет 5, которое является результатом деления их суммы, равной 30, на их количество, равное 6.
Медиана Средний номер группы чисел. Половина чисел содержит значения, превышающие медиану, а половина чисел содержат значения меньше медианы. Например, медианой для чисел 2, 3, 3, 5, 7 и 10 будет 4.
Мода Наиболее часто встречающееся число в группе чисел. Например, модой для чисел 2, 3, 3, 5, 7 и 10 будет 3.
При симметричном распределении множества чисел все три значения центральной тенденции будут совпадать. В отклоненном распределении группы чисел они могут быть разными.
Вычисление среднего значения в смежных строках или столбцах
Выполните указанные ниже действия.
Вычисление среднего значения за пределами непрерывной строки или столбца
Для выполнения этой задачи используется функция СРЗНАЧ . Скопируйте таблицу, расположенную ниже, на пустой лист.
Расчет среднего взвешенного значения
Для выполнения этой задачи используйте функции СУММПРОИЗВ и Sum . в ВСИС примере рассчитываются средние цены, оплаченные за единицу в трех покупках, где каждая из них предназначена для разных единиц товара на разных единицах.
Скопируйте таблицу, расположенную ниже, на пустой лист.
Для того чтобы найти среднее значение в Excel (при том неважно числовое, текстовое, процентное или другое значение) существует много функций. И каждая из них обладает своими особенностями и преимуществами. Ведь в данной задаче могут быть поставлены определенные условия.
Например, средние значения ряда чисел в Excel считают с помощью статистических функций. Можно также вручную ввести собственную формулу. Рассмотрим различные варианты.
Как найти среднее арифметическое чисел?
Чтобы найти среднее арифметическое, необходимо сложить все числа в наборе и разделить сумму на количество. Например, оценки школьника по информатике: 3, 4, 3, 5, 5. Что выходит за четверть: 4. Мы нашли среднее арифметическое по формуле: =(3+4+3+5+5)/5.
Как это быстро сделать с помощью функций Excel? Возьмем для примера ряд случайных чисел в строке:
Или: сделаем активной ячейку и просто вручную впишем формулу: =СРЗНАЧ(A1:A8).
Теперь посмотрим, что еще умеет функция СРЗНАЧ.
Найдем среднее арифметическое двух первых и трех последних чисел. Формула: =СРЗНАЧ(A1:B1;F1:H1). Результат:
Среднее значение по условию
Условием для нахождения среднего арифметического может быть числовой критерий или текстовый. Будем использовать функцию: =СРЗНАЧЕСЛИ().
Найти среднее арифметическое чисел, которые больше или равны 10.
Функция: =СРЗНАЧЕСЛИ(A1:A8;">=10")
Результат использования функции СРЗНАЧЕСЛИ по условию ">=10":
Третий аргумент – «Диапазон усреднения» - опущен. Во-первых, он не обязателен. Во-вторых, анализируемый программой диапазон содержит ТОЛЬКО числовые значения. В ячейках, указанных в первом аргументе, и будет производиться поиск по прописанному во втором аргументе условию.
Внимание! Критерий поиска можно указать в ячейке. А в формуле сделать на нее ссылку.
Найдем среднее значение чисел по текстовому критерию. Например, средние продажи товара «столы».
Функция будет выглядеть так: =СРЗНАЧЕСЛИ($A$2:$A$12;A7;$B$2:$B$12). Диапазон – столбец с наименованиями товаров. Критерий поиска – ссылка на ячейку со словом «столы» (можно вместо ссылки A7 вставить само слово "столы"). Диапазон усреднения – те ячейки, из которых будут браться данные для расчета среднего значения.
В результате вычисления функции получаем следующее значение:
Внимание! Для текстового критерия (условия) диапазон усреднения указывать обязательно.
Как посчитать средневзвешенную цену в Excel?
Как мы узнали средневзвешенную цену?
Формула: =СУММПРОИЗВ(C2:C12;B2:B12)/СУММ(C2:C12).
С помощью формулы СУММПРОИЗВ мы узнаем общую выручку после реализации всего количества товара. А функция СУММ - сумирует количесвто товара. Поделив общую выручку от реализации товара на общее количество единиц товара, мы нашли средневзвешенную цену. Этот показатель учитывает «вес» каждой цены. Ее долю в общей массе значений.
Среднее квадратическое отклонение: формула в Excel
Различают среднеквадратическое отклонение по генеральной совокупности и по выборке. В первом случае это корень из генеральной дисперсии. Во втором – из выборочной дисперсии.
Для расчета этого статистического показателя составляется формула дисперсии. Из нее извлекается корень. Но в Excel существует готовая функция для нахождения среднеквадратического отклонения.
Среднеквадратическое отклонение имеет привязку к масштабу исходных данных. Для образного представления о вариации анализируемого диапазона этого недостаточно. Чтобы получить относительный уровень разброса данных, рассчитывается коэффициент вариации:
среднеквадратическое отклонение / среднее арифметическое значение
Формула в Excel выглядит следующим образом:
СТАНДОТКЛОНП (диапазон значений) / СРЗНАЧ (диапазон значений).
Коэффициент вариации считается в процентах. Поэтому в ячейке устанавливаем процентный формат.
Средняя зарплата… Средняя продолжительность жизни… Практически каждый день мы с вами слышим эти словосочетания, используемые для описания множества одним единственным числом. Но как ни странно, «среднее значение» — достаточно коварное понятие, часто вводящее в заблуждение обычного, неискушенного в математической статистике, человека.
В чем проблема?
Под средним значением чаще всего подразумевается среднее арифметическое, которое очень сильно варьируется под воздействием единичных фактов или событий. И вы не получите реального представления о том, как именно распределены значения, которые вы изучаете.
Давайте обратимся к классическому примеру со средней зарплатой.
В какой-то абстрактной компании работает десять сотрудников. Девять из них получают зарплату около 50 000 рублей, а один 1 500 000 рублей (по странному совпадению он же является генеральным директором этой компании).
Средним значением в данном случае будет 195 150 рублей, что согласитесь, неправильно.
Какие способы вычисления среднего бывают?
Первым способом является вычисление уже упомянутого среднего арифметического , являющегося суммой всех значений, деленной на их количество.
- x – среднее арифметическое;
- x n – конкретное значение;
- n – количество значений .
- Хорошо работает при нормальном распределении значений в выборке;
- Легко вычислить;
- Интуитивно понятно.
- Не дает реального представления о распределении значений;
- Неустойчивая величина легко поддающаяся выбросам (как в случае с генеральным директором).
Вторым способом является вычисление моды , то есть наиболее часто встречающегося значения.
- M 0 – мода;
- x 0 – нижняя граница интервала, который содержит моду;
- n – величина интервала;
- f m – частота (сколько раз в ряду встречается то или иное значение);
- f m-1 – частота интервала предшествующего модальному;
- f m+1 – частота интервала следующего за модальным.
- Прекрасно подходит для получения представления об общественном мнении;
- Хорошо подходит для нечисловых данных (цвета сезона, хиты продаж, рейтинги);
- Проста для понимания.
- Моды может просто не быть (нет повторов);
- Мод может быть несколько (многомодальное распределение).
Третий способ — это вычисление медианы , то есть значения, которое делит упорядоченную выборку на две половины и находится между ними. А если такого значения нет, то за медиану принимается среднее арифметическое между границами половин выборки.
- M e – медиана;
- x 0 – нижняя граница интервала, который содержит медиану;
- h – величина интервала;
- f i – частота (сколько раз в ряду встречается то или иное значение);
- S m-1 – сумма частот интервалов предшествующих медианному;
- f m – число значений в медианном интервале (его частота).
- Дает самую реалистичную и репрезентативную оценку;
- Устойчива к выбросам.
- Сложнее вычислить, так как перед вычислением выборку нужно упорядочить.
Мы рассмотрели основные методы нахождения среднего значения, называющиеся мерами центральной тенденции (на самом деле их больше, но это наиболее популярные).
А теперь давайте вернемся к нашему примеру и посчитаем все три варианта среднего при помощи специальных функций Excel:
- СРЗНАЧ(число1;[число2];…) — функция для определения среднего арифметического;
- МОДА.ОДН(число1;[число2];...) — функция моды (в более старых версиях Excel использовалась МОДА(число1;[число2];...));
- МЕДИАНА(число1;[число2];...) — функция для поиска медианы.
И вот какие значения у нас получились:
В данном случае мода и медиана гораздо лучше характеризуют среднюю зарплату в компании.
Но что делать, когда в выборке не 10 значений, как в примере, а миллионы? В Excel это не посчитать, а вот в базе данных где хранятся ваши данные, без проблем.
Вычисляем среднее арифметическое на SQL
Тут все достаточно просто, так как в SQL предусмотрена специальная агрегатная функция AVG .
И чтобы ее использовать достаточно написать вот такой запрос:
Вычисляем моду на 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 .
Какой способ все-таки использовать?
Из сказанного выше следует, что медиана лучший способ для вычисления среднего значения.
Но это не всегда так. Если вы работаете со средним, то остерегайтесь многомодального распределения:
На графике представлено бимодальное распределение с двумя пиками. Такая ситуация может возникнуть, например, при голосовании на выборах.
В данном случае среднее арифметическое и медиана — это значения, находящиеся где-то посередине и они ничего не скажут о том, что происходит на самом деле и лучше сразу признать, что вы имеете дело с бимодальным распределением, сообщив о двух модах.
А еще лучше разделить выборку на две группы и собрать статистические данные для каждой.
Вывод:
При выборе метода нахождения среднего нужно учитывать наличие выбросов, а также нормальность распределения значений в выборке.
Окончательный выбор меры центральной тенденции всегда лежит на аналитике.
Запомните!
Чтобы найти среднее арифметическое , нужно сложить все числа и поделить их сумму на их количество.
Найти среднее арифметическое 2, 3 и 4 .
Обозначим среднее арифметическое буквой «m ». По определению выше найдем сумму всех чисел.
Разделим полученную сумму на количество взятых чисел. У нас по условию три числа.
В итоге мы получаем формулу среднего арифметического :
Для чего нужно среднее арифметическое?
Кроме того, что его постоянно предлагают найти на уроках, нахождение среднего арифметического весьма полезно и в жизни.
Например, вы решили продавать футбольные мячи. Но так как вы новичок в этом деле, совершенно непонятно по какой цене вам продавать мячи.
Тогда вы решаете узнать, по какой цене в вашем районе уже продают футбольные мячи конкуренты. Узнаем цены в магазинах и составим таблицу.
Цены на мячи в магазинах оказались совсем разные. Какую цену для продажи футбольного мяча нам лучше выбрать?
Если выбрать самую низкую (290 руб.), то мы будем продавать товар себе в убыток. Если выбрать самую высокую (360 руб.), то покупатели не будут приобретать футбольные мячи у нас.
Нам нужна средняя цена. Здесь на помощь приходит среднее арифметическое .
Вычислим среднее арифметическое цен на футбольные мячи:
Средняя цена =
=
290 + 360 + 310
3
= 320
руб.960
3
Таким образом, мы получили среднюю цену (320 руб.), по которой мы можем продавать футбольный мяч не слишком дёшево и не слишком дорого.
Средняя скорость движения
Со средним арифметическим тесно связано понятие средней скорости движения .
Наблюдая за движением транспорта в городе, можно заметить, что машины, то разгоняются и едут с большой скоростью, то замедляются и едут с маленькой скоростью.
Таких участков на пути следования автотранспорта бывает много. Поэтому для удобства расчётов, используют понятие средней скорости движения.
Запомните!
Средняя скорость движения — это весь пройденный путь разделить на всё время движения.
Рассмотрим задачу на среднюю скорость.
Задача № 1503 из учебника «Виленкин 5 класс»
Автомобиль двигался 3,2 ч по шоссе со скоростью 90 км/ч, затем 1,5 ч по грунтовой дороге со скоростью 45 км/ч, наконец 0,3 ч по просёлочной дороге со скоростью 30 км/ч. Найдите среднюю скорость движения автомобиля на всём пути.
Для расчёта средней скорости движения нужно знать весь путь, пройденный автомобилем, и всё время, которое автомобиль двигался.
S 1 = V 1 t 1S 1 = 90 · 3,2 = 288 (км)
— шоссе.S 2 = V 2 t 2
S 2 = 45 · 1,5 = 67,5 (км) — грунтовая дорога.
S 3 = V 3 t 3
S 3 = 30 · 0,3 = 9 (км) — просёлочная дорога.
S = S 1 + S 2 + S 3
S = 288 + 67,5 + 9 = 364,5 (км) — весь путь, пройденный автомобилем.
T = t 1 + t 2 + t 3
T = 3,2 + 1,5 + 0,3 = 5 (ч) — всё время.
V ср = S: t
V ср = 364,5: 5 = 72,9 (км/ч) — средняя скорость движения автомобиля.
Ответ: V ср = 72,9 (км/ч) — средняя скорость движения автомобиля.