Как найти выборочное среднее в excel

Обновлено: 05.07.2024

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

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

Среднее значение Это арифметическое и вычисляется путем с добавления группы чисел и деления на их количество. Например, средним значением для чисел 2, 3, 3, 5, 7 и 10 будет 5, которое является результатом деления их суммы, равной 30, на их количество, равное 6.

Медиана Среднее число числа. Половина чисел имеют значения больше медианой, а половина чисел имеют значения меньше медианой. Например, медианой для чисел 2, 3, 3, 5, 7 и 10 будет 4.

Мода Наиболее часто встречается число в группе чисел. Например, модой для чисел 2, 3, 3, 5, 7 и 10 будет 3.

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

Расчет среднего значения чисел в подрядной строке или столбце

Выполните действия, описанные ниже.

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

На вкладке "Главная" в группе "Редактирование" щелкните стрелку рядом с кнопкой " ", выберите "Среднее" и нажмите клавишу ВВОД.

Расчет среднего значения чисел, которые не являются частью строки или столбца

Для этого используйте функцию С AVERAGE. Скопируйте приведенную ниже таблицу на пустой лист.

Описание (результат)

Среднее значение всех чисел в списке выше (9,5).

Среднее значение 3-го и последнего числа в списке (7,5).

Среднее значение чисел в списке за исключением тех, которые содержат нулевые значения, например ячейка A6 (11,4).

Для этой задачи используются функции СУММПРОИВ ИСУММ. В этом примере вычисляется средняя цена за единицу для трех покупок, при которой каждая покупка приобретает различное количество единиц по разной цене.

В статье напомним некоторые понятия математической статистики: выборка, статистика, точечная оценка, выборочное распределение. Продемонстрируем в MS EXCEL сходимость некоторых распределений статистик к нормальному распределению, распределению ХИ-квадрат, распределению Стьюдента и F - распределению.

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

Сначала напомним основные понятия математической статистики, необходимые для оценки параметров.

О выборке

В математической статистике вероятностная модель явления (распределение) определена с точностью до неизвестных параметров. Например, предполагается известным, что случайная величина распределена по нормальному закону , но неизвестны его параметры ( среднее и дисперсия ). Отсутствие сведений о параметрах компенсируется тем, что нам позволено проводить «пробные» испытания ( выборки , samples) и на их основе восстанавливать недостающую информацию.

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

Число испытаний в выборке обозначим n. Каждое испытание состоит в том, что мы случайным образом выбираем один объект генеральной совокупности ( population ) и записываем его характеристику X. Полученный таким образом ряд чисел Х 1 . Х n будем называть случайной выборкой объема n, а числа X i - элементами выборки . Элементы выборки являются независимыми случайными величинами и, как все случайные величины, имеет функцию распределения (одинаковую для всех Х i ).

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

  • во-первых, из выборки можно оценить среднее и дисперсию исходного распределения (будем называть их показателями распределения).
  • во-вторых, можно оценить параметр(ы) распределения (см. ниже).

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

О статистиках и точечной оценке параметров распределения

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

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

Примечание : Для нормального распределения μ является как параметром распределения, так и его средним значением ( математическим ожиданием ), а также медианой и модой .

Процедура оценки параметров распределения с помощью статистик называется точечной оценкой ( point estimation ), а сама статистика называется точечной оценкой неизвестного параметра ( point estimator ) .

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

Выборочные распределения статистик

Т.к. статистики получены из случайной выборки , то они сами являются случайными величинами и, соответственно, имеют свое собственное распределение (в общем случае не обязательно совпадающее с исходным распределением, из которого взята выборка ). Это распределение называется выборочным распределением (sampling distribution).

Чтобы определить точность оценки необходимо исследовать ее выборочное распределение , особенно среднее и дисперсию этого распределения . Т.к. на основе выборки можно построить множество различных статистик , то необходимо сформулировать критерии, которые позволят выбрать «лучшие» статистики для оценки параметров распределения. Например, если среднее значение выборочного распределения статистики совпадает с оцениваемым параметром (для всевозможных значений параметра), то такая статистика называется несмещенной оценкой . Также очевидно, что среди двух несмещенных оценок лучше та, чья дисперсия соответствующего выборочного распределения меньше . Такая статистика называется несмещённой оценкой с минимальной дисперсией (MVUE, minimum variance unbiased estimator).

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

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

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

Выборочное распределение среднего


Пусть выборка извлекается из нормального распределения с параметрами N(μ;σ 2 ). Рассмотрим статистику Х ср ( среднее выборки ):

Из Центральной предельной теоремы известно, что выборочное распределение статистики Х ср ( выборочное распределение среднего ) при достаточно большом размере выборки n стремится к нормальному распределению с параметрами N(μ;σ 2 /n).

Проверим это утверждение в MS EXCEL (см. файл примера Лист Нормальное ). Для этого возьмем 60 значений выборочных средних (Хср), вычисленныхна основе 60 случайных выборок, взятых из нормального распределения с параметрами N(μ;σ 2 ). Размер выборки n взят равным 50.


С помощью Графика проверки на нормальность (Normal Probability Plot) покажем, что выборочное распределение среднего соответствует нормальному закону .

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

Использование выборочного распределения статистики Х ср позволяет при ИЗВЕСТНОЙ дисперсии исходного нормального распределения построить доверительный интервал для оценки математического ожидания этого распределения , а также провести проверку гипотез .

Выборочное распределение статистики


Пусть выборка извлекается из нормального распределения с параметрами N(μ;σ 2 ). Рассмотрим статистику , где s – стандартное отклонение выборки , n – размер выборки .

Известно, что выборочное распределение статистики при достаточно большом размере выборки стремится к распределению Стьюдента с n-1 степенью свободы.


Аналогично статистике Х ср , в файле примера на листе СТЬЮДЕНТ построен График вероятности для проверки этого утверждения.

Выборочное распределение статистики (n-1)s 2 /σ 2

Пусть выборка извлекается из нормального распределения с параметрами N(μ;σ 2 ). Рассмотрим статистику (n-1)s 2 /σ 2 , где s – стандартное отклонение выборки .

Известно, что Выборочное распределение статистики (n-1)s 2 /σ 2 при достаточно большом размере выборки стремится к распределению ХИ-квадрат с n-1 степенью свободы.


Аналогично рассмотренной статистике Х ср , в файле примера на листе ХИ2 построен График вероятности для проверки этого утверждения.

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

Выборочное распределение статистики

Пусть из двух нормальных распределений с параметрами N(μ 1 ;σ 1 2 ) и N(μ 2 ;σ 2 2 ) извлекается по одной выборке (в общем случае разного размера n 1 и n 2 ) .


Известно, что при достаточно большом размере выборок Выборочное распределение статистики стремится к F-распределению вероятности с n 1 -1 и n 2 -1 степенями свободы .

В файле примера на листе F-расп построен График вероятности для проверки этого утверждения.

Пусть имеется случайная выборка объема n, представленная вариационным рядом <(xj, nj)>, где xj — варианты, nj — частоты, j = 1, 2, …, m. Если мы имеем дело с интервальным вариационным рядом, то xj — середины интервалов.

Выборочное среднее значение определяется по формуле


(3.1)

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


(3.2)


Выборочное среднее является случайной величиной. Её математическое ожидание равно генеральной средней, т.е. выборочное среднее является несмещенной оценкой генеральной средней.


. (3.3)


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


. (3.4)

где N — объем генеральной совокупности.

Для вычисления выборочной средней для не сгруппированной выборки в программе Excel можно воспользоваться следующей функцией (которая вычисляет среднее арифметическое):

Число1, число2, . — это от 1 до 30 аргументов, для которых вычисляется среднее.

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

Если выборка представлена вариационным рядом, то для вычисления выборочного среднего можно воспользоваться функцией СУММПРОИЗВЕД(массив1;массив2;…), которая вычисляет сумму произведений соответствующих элементов массивов массив1, массив2 и т.д.

Пример 3.1. Найти выборочное среднее для выборки из 10 числовых значений, записанных в ячейках А2:А11 (см. рис. 3.1).


Решение. Введите в ячейку А12 формулу =СРЗНАЧ(А2:А11). Получим значение 1,9.

Пример 3.2. Найти выборочное среднее для выборки, представленной вариационным рядом из 10 числовых значений вариант, записанных в ячейках С2:С11, и 10 значений частот, записанных в ячейках D2:D11 .

Решение. Введите в ячейку C12 формулу

Получим значение 3,571429.

Выборочная дисперсия. Исправленная дисперсия

Выборочная дисперсия s 2 для сгруппированной в вариационный ряд выборки определяется по формуле


(3.5)


Исправленная дисперсия вычисляется по формуле


(3.6)

Если выборка не сгруппирована, то выборочная дисперсия s 2 определяется по формуле


, (3.7)


а исправленная дисперсия — по формуле


(3.8)

Исправленная дисперсия является несмещенной оценкой генеральной дисперсии, т.е. математическое ожидание исправленной дисперсии равно генеральной дисперсии.

В программе Excel для вычисления выборочной дисперсии для выборки, не сгруппированной в вариационный ряд, предназначена функция

Число1, число2. — от 1 до 30 числовых аргументов, соответствующих выборке (числа или диапазоны ячеек).

ДИСПР предполагает, что аргументы представляют всю генеральную совокупность. Если данные представляют только выборку из генеральной совокупности, то дисперсию следует вычислять, используя функцию ДИСП.

Формула для ДИСПР имеет вид (3.7).

Для вычисления исправленной дисперсии предназначена функция

Формула для ДИСП имеет вид (3.8).

Обратите внимание на имена этих функций, можно подумать, что ДИСПР() вычисляет исправленную дисперсию, а ДИСП() — выборочную, тогда как на самом деле функция ДИСП() вычисляет исправленную дисперсию, а ДИСПР() — выборочную.

Оценивает дисперсию по выборке.

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

Дополнительные сведения о новом варианте этой функции см. в статье Функция ДИСП.В.

Синтаксис

Аргументы функции ДИСП описаны ниже.

Число1 Обязательный. Первый числовой аргумент, соответствующий выборке из генеральной совокупности.

Число2. Необязательный. Числовые аргументы 2—255, соответствующие выборке из генеральной совокупности.

Замечания

В функции ДИСП предполагается, что аргументы являются только выборкой из генеральной совокупности. Если данные представляют всю генеральную совокупность, для вычисления дисперсии следует использовать функцию ДИСПР.

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

Учитываются логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов.

Если аргумент является массивом или ссылкой, то учитываются только числа. Пустые ячейки, логические значения, текст и значения ошибок в массиве или ссылке игнорируются.

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

Чтобы включить логические значения и текстовые представления чисел в ссылку как часть вычисления, используйте функцию ДИСПА.

Функция ДИСП вычисляется по следующей формуле:

где x — выборочное среднее СРЗНАЧ(число1,число2,…), а n — размер выборки.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Читайте также: