Данные выборки соответствуют Вашему варианту (Контрольная работа №2).
Практикум №10
Статистическая обработка информации в Microsoft Excel: выборочный коэффициент корреляции, выборочное уравнение прямой линии регрессии
Задание 1
Оценить с помощью выборочного коэффициента корреляции тесноту линейной связи между общим весом плода некоторого растения (г) и весом его семян (г) на основе данных выборки, представленных в таблице. Проверить значимость выборочного коэффициента корреляции при уровне значимости 0,05. Найти выборочное уравнение прямой линии регрессии на и построить линию регрессии.
40 | 41 | 45 | 50 | 52 | 55 | 59 | 62 | 68 | 70 | 77 | 79 | 80 | 83 | 86 | 90 | 92 | 95 | 98 | 100 | |
9 | 12 | 9 | 15 | 14 | 14 | 13 | 18 | 17 | 17 | 16 | 19 | 22 | 21 | 22 | 22 | 24 | 25 | 23 | 26 |
1. Создать таблицу, содержащую данные выборки (рис. 1). Устанавливать такую же небольшую ширину столбцов, как на рис. 1, не обязательно.
Рис. 1
2. Найти выборочный коэффициент корреляции. Для этого:
- установить курсор в ячейку А7;
- выбрать функцию КОРРЕЛ из категории Статистические;
- в окне Аргументы функции в поле ввода Массив 1 ввести диапазон B3:U3, содержащий данные о весе плода (значения случайной величины ), а в поле ввода Массив 2 ввести диапазон B4:U4, содержащий данные о весе семян (значения случайной величины );
- в результате в ячейке А7 появится значение выборочного коэффициента корреляции (рис. 2);
|
|
Важное замечание! Найденный коэффициент корреляции близок к 1, значит зависимость между и достаточно близка к линейной.
Рис. 2
Важное замечание! Нам известно, что выборочное уравнение прямой линии регрессии на имеет вид:
.
Раскрыв скобки в правой части уравнения, получим: .
Тогда, очевидно, уравнение можно записать в более компактном виде:
,
где параметры и определяются следующим образом:
, (*).
Таким образом, для составления выборочного уравнения прямой линии регрессии на нужно найти значения параметров и по формулам (*). Однако в Excel имеются стандартные функции, позволяющие найти значения параметров и , не вычисляя предварительно выборочные средние ( и ) и выборочные средние квадратические отклонения ( и ).
3. Найти параметры и выборочного уравнения прямой линии регрессии . Для этого:
- установить курсор в ячейку А11;
- выбрать функцию ОТРЕЗОК из категории Статистические;
- в окне Аргументы функции в поле ввода Известные_значения_y ввести диапазон B4:U4, содержащий значения случайной величины , а в поле ввода Известные_значения_x ввести диапазон B3:U3, содержащий значения случайной величины ;
|
|
- в результате в ячейке А11 появится значение параметра линейной функции регрессии (рис. 3);
- используя функцию НАКЛОН из категории Статистические, найти значение параметра линейной функции регрессии и поместить его в ячейку А13 (рис. 3).
Рис. 3
4. Создать таблицу значений случайных величин , и линейной функции регрессии (рис. 4). Для этого:
- ввести в ячейку В16 формулу =В3;
- копировать введенную формулу с помощью маркера автозаполнения вправо во все незаполненные ячейки первой строки таблицы;
- в результате в первой строке появятся значения случайной величины из исходной таблицы (рис. 4);
- аналогично, используя формулы, заполнить вторую строку таблицы значениями случайной величины (рис. 4);
- используя формулы (содержащие абсолютные ссылки на ячейки, в которых находятся значения параметров и ) и маркер автозаполнения, вычислить значения линейной функции регрессии в третьей строке таблицы (рис. 4).
Рис. 4
5. Построить в одной системе координат линию регрессии и точки , координаты которых соответствуют наблюдавшимся значениям случайных величин и . Для этого:
- с помощью мыши выделить данные, по которым будет строиться изображение (прямоугольный диапазон B16:U18, содержащий значения случайных величин , и линейной функции регрессии);
|
|
- на вкладке Вставка в группе Диаграммы выбрать тип диаграммы Точечнаяи вид диаграммы, на котором изображены точки, соединенные отрезками;
- в результате будут построены две линии в одной системе координат – красная, соответствующая графику линейной функции регрессии, и синяя, соответствующая точкам с координатами (рис. 5);
- выделить красную линию и, используя кнопку Изменить тип диаграммы на вкладке Конструктор, выбрать тип диаграммы Точечная с прямыми отрезками;
- выделить синюю линию и, используя кнопку Изменить тип диаграммы на вкладке Конструктор, выбрать тип диаграммы Точечная с маркерами;
- используя вкладку Макет, удалить легенду, добавить названия осей и название диаграммы;
- в результате изображение должно иметь вид, показанный на рис. 6.
Важное замечание! Видно, что точки расположены в достаточно узкой полосе вдоль линии регрессии. Значит, полученное уравнение прямой линии регрессии хорошо описывает зависимость случайной величины от случайной величины . Вообще, чем ближе коэффициент корреляции к 1, тем лучше уравнение прямой линии регрессии описывает зависимость между случайными величинами.
|
|
Рис. 5
Рис. 6
6. Проверить значимость найденного выборочного коэффициента корреляции при . Для этого:
- ввести в ячейку Е39 значение уровня значимости (рис. 7);
- используя функцию СЧЁТ, найти объем выборки, поместить его в ячейку Е40;
- ввести в ячейку Е41 формулу =E40-2 (для нахождения числа степеней свободы ) (рис. 7);
- ввести в ячейку Е42 формулу =(A7*КОРЕНЬ(E40-2))/КОРЕНЬ(1-A7^2) (для вычисления наблюдаемого значения критерия ) (рис. 7);
- установить курсор в ячейку Е43;
- выбрать функцию СТЬЮДРАСПОБР из категории Статистические;
- в окне Аргументы функции в поле ввода Вероятность ввести адрес ячейки Е39, содержащей уровень значимости, а в поле ввода Степени_свободы ввести адрес ячейки Е41, содержащей число степеней свободы;
- в результате ячейке Е43окажется критическая точка (рис. 7);
- с помощью функции ЕСЛИ заполнить ячейку Е44: если , то в ячейке должен появиться текст «Коэффициент корреляции значимо отличается от нуля», в противном случае – текст «Коэффициент корреляции незначим» (рис. 7); для нахождения модуля числа в Excel используется функция ABS.
Рис. 7
7. Переименовать текущий рабочий лист, дав ему имя Задание 1.
Задание 2
Для выяснения зависимости скорости письма под диктовку (знаков в минуту) от скорости чтения (слов в минуту) были обследованы школьники, обучающиеся в третьем классе. Результаты проведенного исследования представлены в таблице.
50 | 54 | 56 | 57 | 61 | 61 | 64 | 67 | 69 | 70 | 71 | 73 | 74 | 75 | |
30 | 36 | 32 | 28 | 35 | 30 | 40 | 45 | 35 | 31 | 54 | 30 | 49 | 60 | |
75 | 76 | 78 | 79 | 80 | 80 | 80 | 85 | 89 | 92 | 97 | 100 | 105 | 110 | |
43 | 38 | 37 | 53 | 41 | 45 | 38 | 42 | 47 | 63 | 51 | 50 | 66 | 53 |
Найти среднюю скорость чтения и среднюю скорость письма. Найти выборочный коэффициент корреляции и оценить тесноту линейной связи между и . Проверить значимость выборочного коэффициента корреляции при уровне значимости 0,02. Найти выборочное уравнение прямой линии регрессии на и построить линию регрессии.
Все расчеты должны быть выполнены на отдельном листе в соответствии с рассмотренным в предыдущем задании алгоритмом. Данные выборки, записанные в условии задания в четырех строчках, необходимо представить на рабочем листе в виде таблицы, состоящей из двух строк.
Задание 3
По данным выборки, представленным в корреляционной таблице, найти выборочный коэффициент корреляции. Считая, что выборка извлечена из нормально распределенной двумерной генеральной совокупности, проверить гипотезу о значимости выборочного коэффициента корреляции при . Найти выборочное уравнение прямой линии регрессии на и построить линию регрессии.
Данные выборки соответствуют Вашему варианту (Контрольная работа №2).
Все расчеты должны быть выполнены на отдельном листе.
Данные выборки, представленные в корреляционной таблице, предварительно необходимо представить в виде таблицы, состоящей из двух строк. В первой строке таблицы указываются значения случайной величины , а во второй строке – значения случайной величины .
Например, пусть дана корреляционная таблица:
20 | 25 | 30 | 35 | 40 | ||
16 | 4 | 6 | 10 | |||
26 | 8 | 10 | 18 | |||
36 | 32 | 3 | 9 | 44 | ||
46 | 4 | 12 | 6 | 22 | ||
56 | 1 | 5 | 6 | |||
4 | 14 | 46 | 16 | 20 |
Ее следует преобразовать в таблицу вида:
20 | 20 | 20 | 20 | 25 | 25 | 25 | 25 | 25 | 25 | … | 40 | 40 | 40 | 40 | 40 | |
16 | 16 | 16 | 16 | 16 | 16 | 16 | 16 | 16 | 16 | … | 56 | 56 | 56 | 56 | 56 |
Так как пара вариант имеет частоту 4, то последней таблице эта пара записана четыре раза подряд. Пара вариант имеет частоту 6, поэтому она указана шесть раз подряд и т.д. В результате число столбцов последней таблицы будет равно объему выборки.
ВАРИАНТЫ
2.1. | ||||||
3 | 5 | 7 | 9 | 11 | ||
10 | 4 | 4 | 1 | |||
30 | 2 | 11 | ||||
50 | 4 | 14 | 5 | |||
70 | 1 | 8 | ||||
90 | 2 | 4 |
2.2. | ||||||
2 | 5 | 8 | 11 | 14 | ||
20 | 5 | 2 | ||||
24 | 2 | 10 | 4 | |||
28 | 8 | 15 | ||||
32 | 1 | 3 |
2.3. | |||||||
6 | 10 | 14 | 18 | 22 | 26 | ||
2 | 10 | 2 | 1 | ||||
3 | 8 | 16 | |||||
4 | 3 | 7 | 15 | 2 | |||
5 | 9 | 13 | |||||
6 | 1 | 5 | 8 |
2.4. | |||||||
5 | 10 | 15 | 20 | 25 | 30 | ||
100 | 5 | 10 | 4 | ||||
130 | 9 | 16 | 2 | ||||
160 | 4 | 17 | 8 | 3 | |||
190 | 5 | 7 |
2.5. | ||||||
10 | 16 | 22 | 28 | 34 | ||
4 | 2 | 4 | ||||
6 | 2 | 6 | 4 | |||
8 | 2 | 17 | 11 | 1 | ||
10 | 9 | 7 | 3 | |||
12 | 7 | 3 | 2 |
2.6. | ||||||
8 | 15 | 22 | 29 | 36 | ||
10 | 6 | 4 | 2 | |||
15 | 7 | 8 | 5 | |||
20 | 3 | 15 | 10 | |||
25 | 1 | 9 |
2.7. | |||||||
6 | 14 | 22 | 30 | 38 | 46 | ||
125 | 2 | 2 | 10 | ||||
150 | 6 | 9 | 5 | 1 | |||
175 | 19 | 10 | 6 | ||||
200 | 7 | 9 | 12 | ||||
225 | 9 | 10 | 3 |
2.8. | |||||||
11 | 20 | 29 | 38 | 47 | 56 | ||
2 | 8 | 10 | 3 | ||||
5 | 7 | 12 | 14 | 1 | |||
8 | 18 | 8 | 11 | 1 | |||
11 | 1 | 4 | 3 | 9 |
2.9. | ||||||
10 | 20 | 30 | 40 | 50 | ||
30 | 6 | 5 | 1 | |||
45 | 3 | 9 | 2 | 3 | ||
60 | 2 | 16 | 7 | |||
75 | 6 | 4 | 4 | |||
90 | 5 | 7 |
2.10. | ||||||
12 | 14 | 16 | 18 | 20 | ||
50 | 1 | 9 | ||||
100 | 4 | 13 | 11 | |||
150 | 7 | 8 | 5 | |||
200 | 6 | 4 | 2 |
2.11. | |||||||
7 | 10 | 13 | 16 | 19 | 22 | ||
1 | 9 | 10 | 1 | ||||
1,5 | 7 | 9 | 12 | ||||
2 | 18 | 10 | 6 | ||||
2,5 | 9 | 9 | 5 | 1 | |||
3 | 2 | 2 | 10 |
2.12. | |||||||
3 | 7 | 11 | 15 | 19 | 23 | ||
4 | 4 | 3 | 9 | ||||
8 | 19 | 8 | 11 | ||||
12 | 7 | 12 | 14 | 1 | |||
16 | 8 | 12 | 2 |
Дата добавления: 2018-06-27; просмотров: 208; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!