Основы информатики и вычислительной техники. Шилов О.И. - 29 стр.

UptoLike

Составители: 

Статистические расчёты в Microsoft Excel
Практические задания и рекомендации по информатике
29
Лабораторная работа 8. Статистические расчёты в Microsoft Excel
Цель работы
Научиться выполнять простейшие статистические расчёты средствами Microsoft Excel.
Задание
1) Рассчитать среднее значение, дисперсию, среднее квадратическое отклонение, моду, ме-
диану, ошибку среднего и доверительный интервал для данной выборки случайных вели-
чин.
2)
Определить коэффициент парной корреляции двух рядов данных и построить графики
линейной регрессии.
Решение
1) Сначала придётся вспомнить теорию математической статистики. Пусть дана случайная
величина X (генеральная совокупность). Для изучения характеристик этой совокупности взя-
та выборка объёмом n. Получены значения x
i
(i=1, 2, …, n).
Средняя выборочная (она жеоценка генеральной средней) есть среднее арифметическое
отобранных значений случайной величины:
=
=
n
i
i
x
n
x
1
1
В Microsoft Excel для расчёта среднего арифметического используется встроенная функ-
ция
СРЗНАЧ(). В качестве аргументов этой и следующих функций могут быть как отдель-
ные значения, разделённые точкой с запятой (например,
СРЗНАЧ(A1;B2;C3;C4)), так и диа-
пазоны значений (например,
СРЗНАЧ(A2:A10)) или же их сочетание -
СРЗНАЧ(A2:A10;B2:B10;С14).
Дисперсия случайной величины (выборочная дисперсия) вычисляется по формуле:
2
1
2
1
)( xx
n
xD
n
i
i
=
=
Для расчёта выборочной дисперсии Excel имеет встроенную функцию
ДИСПР(). Приме-
чание
: в справке по этой функции указано, что она вычисляет дисперсию для генеральной
совокупности. В действительности эта функция рассматривает свои аргументы как всю гене-
ральную совокупность, следовательно, вычисляет то, что мы называем выборочной диспер-
сией.
Выборочное среднее квадратическое отклонение есть квадратный корень из дисперсии:
)(xD=
σ
. Для расчёта его можно использовать формулу =КОРЕНЬ(значение диспер-
сии)
, а можно воспользоваться встроенной функцией СТАНДОТКЛОНП().
Мода (Mo) – наиболее часто встречающееся или повторяющееся значение и медиана (Me)
- число, которое является серединой множества значений выборки, вычисляется соответст-
венно встроенными функциями
МОДА() и МЕДИАНА().
Уточнённая (исправленная) дисперсия, называемая также оценкой генеральной диспер-
сии, вычисляется по формуле:
)(
1
2
xD
n
n
S
x
=
В Excel для расчёта оценки генеральной дисперсии используется функция ДИСП().
Оценка среднего квадратического отклонения генеральной совокупности (или уточнённое
среднее квадратическое отклонение) является квадратным корнем из исправленной диспер-
                           Статистические расчёты в Microsoft Excel                   29



   Лабораторная работа №8.              Статистические расчёты в Microsoft Excel

Цель работы
  Научиться выполнять простейшие статистические расчёты средствами Microsoft Excel.

Задание
1) Рассчитать среднее значение, дисперсию, среднее квадратическое отклонение, моду, ме-
   диану, ошибку среднего и доверительный интервал для данной выборки случайных вели-
   чин.
2) Определить коэффициент парной корреляции двух рядов данных и построить графики
   линейной регрессии.

Решение
1) Сначала придётся вспомнить теорию математической статистики. Пусть дана случайная
величина X (генеральная совокупность). Для изучения характеристик этой совокупности взя-
та выборка объёмом n. Получены значения xi (i=1, 2, …, n).
   Средняя выборочная (она же – оценка генеральной средней) есть среднее арифметическое
отобранных значений случайной величины:
                                              1 n
                                         x = ⋅ ∑ xi
                                              n i =1
   В Microsoft Excel для расчёта среднего арифметического используется встроенная функ-
ция СРЗНАЧ(). В качестве аргументов этой и следующих функций могут быть как отдель-
ные значения, разделённые точкой с запятой (например, СРЗНАЧ(A1;B2;C3;C4)), так и диа-
пазоны значений (например, СРЗНАЧ(A2:A10)) или же их сочетание -
СРЗНАЧ(A2:A10;B2:B10;С14).
   Дисперсия случайной величины (выборочная дисперсия) вычисляется по формуле:
                                             1 n
                                    D( x) = ⋅ ∑ xi2 − x 2
                                             n i =1
   Для расчёта выборочной дисперсии Excel имеет встроенную функцию ДИСПР(). Приме-
чание: в справке по этой функции указано, что она вычисляет дисперсию для генеральной
совокупности. В действительности эта функция рассматривает свои аргументы как всю гене-
ральную совокупность, следовательно, вычисляет то, что мы называем выборочной диспер-
сией.
   Выборочное среднее квадратическое отклонение есть квадратный корень из дисперсии:
σ = D(x) . Для расчёта его можно использовать формулу =КОРЕНЬ(значение диспер-
сии), а можно воспользоваться встроенной функцией СТАНДОТКЛОНП().
   Мода (Mo) – наиболее часто встречающееся или повторяющееся значение и медиана (Me)
- число, которое является серединой множества значений выборки, вычисляется соответст-
венно встроенными функциями МОДА() и МЕДИАНА().
   Уточнённая (исправленная) дисперсия, называемая также оценкой генеральной диспер-
сии, вычисляется по формуле:
                                               n
                                      S x2 =        D( x)
                                             n −1
   В Excel для расчёта оценки генеральной дисперсии используется функция ДИСП().
   Оценка среднего квадратического отклонения генеральной совокупности (или уточнённое
среднее квадратическое отклонение) является квадратным корнем из исправленной диспер-



Практические задания и рекомендации по информатике