Тема: Решение задач математической статистики в Excel.

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

2. Учебные цели:

- получение навыков работы по созданию и редактированию таблиц;

- освоение приемов работы с мастером функций.

В результате освоения темы студент должен уметь:

- создавать и редактировать таблицы;

- использовать мастер функций для решения математических и статистических задач;

Для формирования умений студент должен знать:

- математические методы обработки результатов наблюдений (корреляционный и регрессионный анализ, дисперсионный анализ);

- приемы работы в электронной таблице Excel.

3. Материалы для самоподготовки к освоению данной темы.

Вопросы для самоподготовки:

1) Определение коэффициента корреляции.

2) Метод наименьших квадратов.

3) Составление выборочного уравнения линейной регрессии с помощью метода наименьших квадратов.

4) Возможности мастера функций пакета Excel и приемы работы с электронной таблицей.

4. Вид занятия: практическое занятие.

5. Продолжительность занятия: 3 академических часа.

6. Оснащение: компьютер с установленным пакетом MS Office.

7. Содержание занятия.

7.1. Контроль исходного уровня знаний.

Вопросы:

1. Функциональные возможности пакета Excel.

2. Ввод данных в пакете Excel.

3. Типы данных в электронной таблице.

4. Ввод формул в Excel.

5. Понятие коэффициента корреляции.

6. Составление выборочного уравнения линейной регрессии.

7. Формулы фактической и остаточной дисперсий.

8. Критерий Фишера-Снедекора.

7.2. Решение задач математической статистики в Excel.

Выборочный метод.

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

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

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

Пусть из генеральной совокупности извлечена выборка, причем x₁ наблюдалось n₁ раз, x₂ – n₂ раз, x_{k –} n_k раз и Sn_i=n – объем выборки. Наблюдаемые значения x_i называют вариантами, а последовательность вариант, записанных в возрастающем порядке, - вариационным рядом. Числа наблюдений называют частотами, а их отношения к объему выборки n_i/n – относительными частотами.

Статистическим распределением выборки называют перечень вариант и соответствующих им частот или относительных частот.

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

Эмпирической (выборочной) функцией распределения называют функцию F^*(x), определяющую для каждого значения x относительную частоту события X<x.

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

Полигоном частот называют ломаную, отрезки которой соединяют точки (x₁;n₁), (x₂;n₂),…,(x_k;n_k).Для построения полигона частот на оси абсцисс откладывают варианты x_i, а на оси ординат – соответствующие им частоты n_i. Точки (x_i;n_i) соединяют отрезками прямых и получают полигон частот. Если откладывать на оси ординат относительные частоты, то получим полигон относительных частот.

Гистограммой частот называют ступенчатую фигуру, состоящую из прямоугольников, основаниями которых служат частичные интервалы длиною Dx, а высоты равны отношению n_i/Dx (плотность частоты). Если высоты равны отношению относительной частоты на длину частичного интервала Dx, то гистограмму называют гистограммой относительных частот.

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

В Excel для построения эмпирических функций распределения используется специальная функция ЧАСТОТА и процедура пакета анализа Гистограмма.

Функция ЧАСТОТА вычисляет частоты появления случайной величины в интервалах значений и выводит их как массив цифр.

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

Пример. Построить эмпирическое распределение следующей выборки: 24, 27, 23, 22, 25, 24, 27, 21, 20, 29, 23, 21, 26, 25,23, 22, 25, 26,23, 29.

Решение.

1. В ячейку А1 введите слово Наблюдения, а в диапазон А2:В11 – значения выборки.

2. Выберите ширину интервала 1. Тогда при крайних значениях 20 и 29 получится 9 интервалов. В ячейки С1 введите букву Х. В диапазон С2:С10 введите граничные значения интервалов (20, 21, 22, 23, 24, 25, 26, 27, 29).

3. Введите заголовки: в ячейку D1 – Абсолютные частоты, в ячейку E1 – Относительные частоты, в ячейку F1 – Накопленные частоты.

4. Заполните столбец абсолютных частот. Выделите ячейки D2:D10. Вызовите Мастер функций (кнопка f_x), категорию Статистические и функцию ЧАСТОТА, нажмите кнопку ОК. Появится диалоговое окно ЧАСТОТА.

Указателем мыши введите диапазон данных (А2:В11) в рабочее поле Массив данных. В рабочее поле Двоичный массив введите диапазон интервалов (С2:С10).

После этого нажмите комбинацию клавиш Ctrl+Shift+Enter. В столбце D2:D10 появится массив абсолютных частот.

5. Найдите объем выборки в ячейке D11. Для этого нажмите кнопку Автосумма, затем выберите диапазон суммирования ( D2:D10 ) и нажмите клавишу Enter.

6. Заполните столбец относительных частот. Введите формулу в ячейку E2 для вычисления относительной частоты: =D2/D$11. Нажмите клавишу Enter. Протягиванием скопируйте формулу в диапазон E3:E10. Получим относительные частоты.

7. Заполните столбец накопленных частот. Относительную частоту, указанную в ячейке E2, наберите в ячейке F2. Введите формулу в ячейку F3 := F2+E3. Нажмите клавишу Enter. Протягиванием скопируйте формулу в диапазон: F4:F10. Получим накопленные частоты.

8. Результаты вычислений относительных частот и накопленных частот приводятся на рисунке.

	A	B	C	D	E	F
	Наблюдения	Х	Абсолютные частоты	Относительные частоты	Накопленные частоты
					0,05	0,05
					0,1	0,15
					0,1	0,25
					0,2	0,45
					0,1	0,55
					0,15	0,7
					0,1	0,8
					0,1	0,9
					0,1

9. Построим диаграмму относительных и накопленных частот. Щелкните указателем мыши по кнопке Мастер диаграмм. Выберите вкладку Нестандартные и тип диаграммы График/гистограмма 2. Затем нажмите кнопку Далее и укажите диапазон данных: E2:F10. Проверьте положение переключателя Ряды в: столбцах. Выберите вкладку Ряд и введите в рабочее поле Подписи оси X диапазон значений X C2:C10. Нажав кнопку Далее, введите названия осей X и Y: в рабочее поле Ось X - X; Ось Y - Относительная частота; Вторая ось Y - Накопленная частота. Нажмите кнопку Готово. Диаграмма будет иметь следующий вид:

Корреляционный анализ.

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

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

Выборочный коэффициент линейной корреляции между двумя случайными величинами X и Y рассчитывается по формуле

где x, y – значения признаков X и Y; n_xy – частота пары значений (x,y); n – объем выборки; s_x, s_y – выборочные средние квадратические отклонения; - выборочные средние.

, , , , ,

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

t_эксп.= .

По таблице критических значений распределения Стьюдента при заданном уровне значимости a и числе степеней свободы f=n-2 находят критическое значение t_кр..

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

В пакете Excel для вычисления коэффициента линейной корреляции используется специальная функция КОРРЕЛ. Параметрами функции являются КОРРЕЛ (массив 1; массив 2), где массив 1 – это диапазон ячеек первой случайной величины; массив 2 – это второй интервал ячеек со значениями второй случайной величины.

Пример. Изучали зависимость между систолическим давлением (мм рт. ст.) у мужчин в начальной стадии шока и возрастом X (годы).

Результаты наблюдений приведены в таблице.

X

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

Решение. Создайте рабочую таблицу в Excel. Введите в ячейку А1 слово “Возраст”. Затем в ячейки А1:А12 – соответствующие значения возраста. В ячейку В1 введите “Систолическое давление”. В ячейки В1:В12 – значения систолического давления. Затем вычисляется значение коэффициента корреляции между выборками. Установите курсор в свободную ячейку (А13). Нажмите кнопку Вставка функции (f_x) на панели инструментов. Выберите в диалоговом окне Мастер функций статистические функции, затем выберите функцию КОРРЕЛ.

Появится диалоговое окно КОРРЕЛ. Указателем мыши введите диапазон данных “Возраст” в поле массив 1 (А2:А12). Диапазон данных “Систолическое давление” введите в поле массив 2 (В2:В12).

В ячейке А13 после нажатия кнопки ОК появится значение коэффициента корреляции – 0,61. Если проверить значимость коэффициента корреляции между переменными X и при уровне значимости a=0,05 (при n=20 t_кр.=2,1), то можно сделать вывод, что имеется заметная линейная корреляционная связь между и X.

Регрессионный анализ.

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

Приведем уравнения регрессии Y на X и X на Y:

M(Y)_x=f(x), M(X)_y=j(y),

M(Y)_x – условное математическое ожидание величины Y, соответствующее значению x;

M(X)_y – условное математическое ожидание величины X, соответствующее значению y.

В результате n независимых опытов получены n пар чисел (x₁, y₁), (x₂, y₂), … , (x_n, y_n).

Найдем по данным наблюдений выборочное уравнение прямой линии регрессии.

Выборочное уравнение линейной регрессии Y на X будем искать в виде

(*)

Угловой коэффициент прямой линии регрессии Y на X называют выборочным коэффициентом регрессии Y на X.

Подберем параметры r_yx и b так, чтобы сумма квадратов отклонений ординат всех эмпирических точек от ординат соответствующих точек прямой (*) должна быть минимальной (в этом состоит сущность метода наименьших квадратов).

В результате применения метода наименьших квадратов получим следующие формулы для вычисления r_yx и b:

;

,

где , ,

, ,

,

.

Линейный регрессионный анализ заключается в подборе графика и его уравнения для набора наблюдений.

Для получения коэффициентов уравнения регрессии используется процедура Регрессия из пакета анализа. Кроме того, могут быть использованы функция ЛИНЕЙН для получения параметров регрессионного уравнения и функция ТЕНДЕНЦИЯ для получения предсказанных значений Y в требуемых точках.

Для реализации процедуры Регрессия необходимо:

- выполнить команду Сервис, Анализ данных;

- в появившемся диалоговом окне Анализ данных в списке Инструменты анализа выбрать строку Регрессия;

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

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

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

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

- нажать кнопку ОК.

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

Приводимое значение R – квадрат (коэффициент детерминации) в регрессионной статистике определяет, с какой степенью точности полученное регрессионное уравнение аппроксимирует исходные данные. Если R – квадрат > 0,95, говорят о высокой точности аппроксимации. Если R – квадрат лежит в диапазоне от 0,8 до 0,95, говорят об удовлетворительной аппроксимации. Если R – квадрат < 0,6, то точность аппроксимации недостаточна и модель требует улучшения.

В таблице Дисперсионный анализ оценивается общее качество полученной модели: ее достоверность по уровню значимости критерия Фишера – p, который должен быть меньше, чем 0,05. Значение p определяем в строке Регрессия, в столбце Значимость F.

Значения коэффициентов модели определяются из таблицы в столбце коэффициенты – в строке Y – пересечение приводится свободный член; в строках соответствующих переменных приводятся значения коэффициентов при этих переменных. В столбце p – значение приводится достоверность отличия соответствующих коэффициентов от нуля. В случаях, когда p > 0,5, коэффициент может считаться нулевым. Это означает, что соответствующая независимая переменная практически не влияет на зависимую переменную и коэффициент может быть убран из уравнения.

Пример. Изучали зависимость между объемом Y (мкм³) и диаметром X (мкм) сухого эритроцита у млекопитающих. Результаты наблюдений приведены в таблице:

X	Y
7,6
8,9
5,5
9,2
3,5
4,8
7,3
7,4
6,8

Необходимо на основании этих данных построить регрессионное уравнение.

Решение.

1. В пункте меню Сервис выберите строку Анализ данных

и далее укажите курсором мыши на строку Регрессия.

2. В появившемся диалоговом окне задайте Входной интервал Y.

3. Укажите Входной интервал X.

4. Установите флажок в поле График подбора.

5. Укажите выходной диапазон. Для этого поставьте переключатель в положение Выходной интервал, затем наведите указатель мыши на правое поле ввода Выходной интервал и, щелкнув левой кнопкой мыши, указатель мыши наведите на левую верхнюю ячейку выходного диапазона (C1). Щелкните левой кнопкой мыши. Нажмите кнопку ОК.

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

ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R	0,94943
R-квадрат	0,901418
Нормированный R-квадрат	0,887335
Стандартная ошибка	9,446213
Наблюдения
Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия		5711,383	5711,383	64,00676	9,11E-05
Остаток		624,6166	89,23094
Итого
	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение	Нижние 95%	Верхние 95%	Нижние 95,0%	Верхние 95,0%
Y-пересечение	-32,4752	12,50339	-2,59731	0,035564	-62,041	-2,90936	-62,041	-2,90936
Переменная X 1	14,28322	1,785308	8,000422	9,11E-05	10,06164	18,5048	10,06164	18,5048
ВЫВОД ОСТАТКА
Наблюдение	Предсказанное Y	Остатки
	76,07731	10,92269
	94,6455	-13,6455
	46,08255	3,917447
	98,93047	13,06953
	17,51611	0,483886
	36,0843	0,915701
	71,79235	-0,79235
	73,22067	-4,22067
	64,65074	-10,6507

В таблице Дисперсионный анализ оценивается общее качество полученной модели: ее достоверность по уровню значимости критерия Фишера (строка Регрессия, столбец Значимость F, в примере 0,0000911, то есть p=0,0000911 и модель значима).

Приводимое значение R – квадрат (коэффициент детерминации) в регрессионной статистике определяет степень точности описания моделью процесса. В примере R – квадрат=0,9015. Так как R – квадрат < 0,95, не можем говорить о высокой точности аппроксимации.

Определим значения коэффициентов модели. На пересечении строки Y – пересечение и столбца Коэффициент приводится свободный член. В строке Переменная X1 приводится коэффициент при X1.

Поэтому выражение для определения объема сухого эритроцита у млекопитающих от диаметра будет иметь вид:

Однофакторный дисперсионный анализ.

Для сравнения нескольких средних пользуются дисперсионным анализом. На практике дисперсионный анализ применяют, чтобы установить, оказывает ли существенное влияние некоторый качественный фактор А, который имеет m уровней А_1, А₂ … А _m на изучаемую величину Х. Например, если требуется выяснить, какая доза рентгеновского излучения наиболее эффективно влияет на темп размножения бактерий, то фактор А – рентгеновское излучение, а его уровни – дозы излучений.

Основная идея дисперсионного анализа состоит в сравнении факторной дисперсии и остаточной дисперсии. В математической статистике доказывается, что факторная дисперсия характеризует влияние фактора А на величину Х, а остаточная – влияние случайных причин.

Рассмотрим случай, когда число испытаний на различных уровнях различно. Пусть произведено q₁ испытаний на уровне А₁, q₂ испытаний на уровне А₂, …, q _m испытаний – на уровне А_m.

Общую сумму квадратов отклонений наблюдаемых зачений от общей средней х находят по формуле :

S_общ = [P₁ + P₂ + …+ P_m] – (R₁ +R₂ +… + R_m)²/n,

где

P₁ = - сумма квадратов наблюдавшихся значений признака на уровне А₁;

P₂ = - сумма квадратов наблюдавшихся значений признака на уровне А₂;

_{. . .}

P_m = - сумма квадратов наблюдавшихся значений признака на уровне А _m.

R₁ = , R₂ = … R_m = - суммы наблюдавшихся значений признака соответственно на уровнях А_1, А_{2, …} А_m .

n= q_{1 +} q_{2 +… +}q_m - общее число испытаний ( объем выборки).

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

S_{факт =} [ ( R₁²/q₁) + (R₂²/q₂ ) +… + (R_m²/q_m )] – [ (R₁ + R₂ + …+ R_m)² /n]

Остаточную сумму квадратов отклонений наблюдаемых значений группы от своей групповой средней, которая характеризует рассеяние "внутри групп", находят по формуле :

S _ост = S _общ -S _факт

Факторную дисперсию находят по формуле :

S² _факт = S _факт / (m-1)

Остаточную дисперсию находят по формуле:

S² _ост = S _ост /(n-m)

Сравниваем факторную и остаточную дисперсии.

Если факторная дисперсия окажется меньше остаточной, то фактор не оказывает существенное влияние на величину Х.

Если факторная дисперсия больше остаточной, то применяем критерий Фишера - Снедекора, для чего найдем наблюдаемое значение критерия

F _набл = S² _факт / S² _ост

По таблице “Критические точки распределения F Фишера - Снедекора” находим критическую точку F_кр ( ά; m-1; n-m), ά – уровень значимости . Если F _набл > F_кр, то гипотезу о равенстве групповых средних отвергаем, значит фактор А оказывает существенное влияние на величину Х.

Для проведения в MS Excel дисперсионного анализа необходимо:

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

- выбрать команду Сервис, затем Анализ данных в списке Инструменты анализа выбрать процедуру Однофакторный дисперсионный анализ;

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

- в разделе Группировка переключатель установить в положение по столбцам;

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

Пример.

Определить достоверность влияния фермента (фактора А) на выход продукта биохимического синтеза при уровне значимости a£0,05.

№ испытания	Уровни фактора А А1 А2 А3 А4

Результаты анализа.

В результате будет получена таблица

Однофакторный дисперсионный анализ
ИТОГИ
Группы	Счет	Сумма	Среднее	Дисперсия
Столбец 1
Столбец 2			67,66667	4,333333
Столбец 3			71,33333	20,33333
Столбец 4			68,33333	2,333333
Дисперсионный анализ
Источник вариации	SS	df	MS	F	P-Значение	F критическое
Между группами	30,91667		10,30556	1,212418	0,366066	4,06618
Внутри групп			8,5
Итого	98,91667

В таблице Дисперсионный анализ на пересечении строки Между группами и столбца MS находится значение факторной дисперсии 10,30556. На пересечении строки Внутри групп и столбца MS находится значение остаточной дисперсии 8,5. Наблюдаемое значение критерия Фишера – Снедекора равно 1,212418. F критическое 4,06618. F_набл.<F_кр., следовательно фактор А не оказывает существенное влияние на величину X.

Контрольная работа

№	Часть 1	Часть 2
	1.1		1,2,3
	2.2		4,5,6
	3.3		7,8,9
	4.2		10,11,12
	5.3		13,14,15
	6.2		16,17,18
	7.1		19,20,21
	8.1		3,5,22
	9.2		7,9,11
	10.1		13,14,17
	11.1		19,20,22
	12.2,12.3		2,4,6
	13.3		8,10,12
	14.4		14,16,18
	15.4		2,20,22
	16.2		3,6,9
	17.1		12,15,18
	18.2		4,8,12
	19.1,19.2		7,16,20
	20.2		1,4,7
	21.2		10,13,16
	22.1		5,19,22
	2.2		2,5,8
	4.1		11,14,17
	6.3		11,19,20
	7.3		1,6,11
	10.2		7,16,21
	12.1		2,7,12
	14.3		1,17,22
	16.3		3,8,13
	18.1		1,11,18
	20.1		4,9,14
	22.2		9,19,21
	1.2		5,10,15
	3.2		6,11,17
	5.3		1,8,15
	7.2		2,9,16
	9.4		3,11,19

Таблица вариантов Номер варианта выбирается по последним двум цифрам шифра.

<

	11.3		4,12,20
	13.3		5,12,19
	15.4		1,10,20
	17.1		13,21,9
	20.1		6,17,2
	21.1		11,13,10
	1.2,1.3		15,7,9
	2.3,2.4		2,14,21
	3.1		4,7,16
	4.2		1,15,21
	5.1		3,13,22
	6.2		7,17,19
	7.3		6,15,20
	8.1		9,12,18
	9.1		5,12,17
	10.3		11,15,19
	11.2		2,15,18
	12.4		17,21,4
	13.1		3,7,18
	14.2		5,11,16
	15.3		4,14,22
	16.1		10,15,21
	17.3		8,19,22
	18.1		4,8,15
	19.1,19.2		3,14,18
	20.1		6,12,17
	21.3		5,10,18
	22.3		4,17,21
	1.1		2,5,9
	14.5		4,8,18
	7.2		10,12,19
	10.4		7,15,17
	13.2		1,11,18