Данные выборки соответствуют Вашему варианту (Контрольная работа №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; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!