Пример выполнения лабораторной работы.

Лабораторная работа №1.

Простейшая обработка данных. Линейная регрессия.

Коэффициент корреляции. Его значимость

Цель: научиться находить коэффициент корреляции и определятьего начимость; находить коэффициенты регрессии и строить уравнениерегрессии.

Основные сведения

Парная регрессия – это уравнение связи двух переменных у и х:

y=f(х),

где у – зависимая переменная (результат, отклик);

х – независимая, объясняющая переменная (фактор).

Различают линейные и нелинейные регрессии.

Линейная регрессия: .

Построение уравнения регрессии сводится к оценке ее параметров.Для оценки параметров регрессий, линейных по параметрам, используютметод наименьших квадратов (МНК). МНК позволяет получить такиеоценки параметров, при которых сумма квадратов отклоненийфактических значений результативного признака у от теоретических у_xминимальна.

Для линейных и нелинейных уравнений, приводимых к линейным, решается следующая система относительно а и в:

Можно воспользоваться готовыми формулами, которые вытекают из этой системы:

.

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

Тесноту связи изучаемых явлений оценивает линейный коэффициентпарной корреляции  r_xyдля линейной регрессии

Теснота линейной связи между переменными может быть оценена на основании шкалы Чеддока:

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

Оценку статистической значимости коэффициента корреляциипроводят с помощью t-критерия Стьюдента. Выдвигают гипотезу Н₀ остатистически незначимом отличии коэффициента от нуля. Оценказначимости коэффициента корреляции с помощью t-критерия Стьюдентапроводится путем сопоставления его значения с величиной случайнойошибки:

t_r=r/m_r.

Стандартная (случайная) ошибка коэффициента корреляцииопределяется по формуле:

.

Сравнивая фактическое и табличное (критическое) значенияt-статистики –t_табл. и  t_факт.– принимает или отвергаем гипотезу Н₀.

Если t_табл. < t_факт., то гипотеза Н₀ отклоняется, коэффициенткорреляции не случайно отличается от. Если t_табл. > t_факт, то гипотеза Н₀ не отклоняется и признается случайная природа формирования коэффициента корреляции.

Порядок выполнения работы.

По заданной выборке исследовать зависимость результата у от фактора х. Для этого

1. Создать таблицу данных.

2. Найти средние значения , выборочные дисперсии  исправленные средние квадратические отклонения .

3. Найти коэффициент корреляции и проверить его значимость.

4. Найти коэффициенты линейного уравнения регрессии.

5. Построить график прямой регрессии.

Пример выполнения лабораторной работы.

В табл. 1.1 приведены данные об объеме производства у (тыс.ед.) в зависимости от численности занятых х (тыс.чел.) некоторой фирмы.

Таблица 1.1.

Исходные данные

1. В диапазоне В3:C11 подготовим исходные данные.

2. Вводим следующие формулы:

Получим следующие результаты (см. рис. 1.1).

Рис. 1.1. Результаты простейшей обработки данных

3. Для определения коэффициента корреляции воспользуемся формулой . Для этого в ячейку Е16вводим формулу

=(D12-B12*C12)/КОРЕНЬ(A17*B17)

Из расчетов следует, что коэффициент корреляции r=0,97. Этосвидетельствует о том, что связь между объемом выпуска продукции ичисленностью занятых весьма высокая и положительная.

4. Для проверки значимости коэффициента корреляции введемвспомогательные данные:

Ячейки

К169 число предприятий;

К170,05 уровень значимости.

5. Далее вводим следующие формулы:

Таким образом, получим данные, представленные на рис 1.2.

Рис. 1.2. Анализ значимости коэффициента корреляции

6. Для определения коэффициентов уравнения линейной регрессии на

основе формул

следует в ячейки I3, I4ввести соответственно следующие формулы:

=(D12-B12*C12)/A17;

=C12-I3*B12.

Уравнение регрессии у=7,9+1,47х.

Значение коэффициента b=1,47 говорит о том, что при увеличениичисленности занятых на 1 тыс.чел. объем продукции увеличится на1,74 тыс.ед.

Результаты расчетов приведены на рис.1.3.

Рис. 1.3. Результаты расчетов

7. Дляпостроения графика выделим диапазон В3:С11. ВызовемМастер диаграмм. Чтобы ось отражала фактические данные,выберем тип диаграммы Точечная.После чего нажмем кнопкуГотово. На построенной диаграмме выделим график функции,щелкнув по нему левой кнопкой мыши. Выделение обозначаетсясветлыми маркерами на функции. Нажав правую кнопку мыши,выведем контекстно-зависимое меню, в котором выберем опциюДобавить линию тренда. В окне Линия трендапо вкладке Типвыберем тип функции Линейная, а во вкладке Параметры–установим флажок показывать уравнение на диаграмме. Врезультате на диаграмме появиться вид теоретической кривой –тренда и ее уравнение (рис.1.4).

Рис. 1.4. Графики фактических данных и построенной регрессии

8. Вычисление параметров регрессии с помощью статистическихфункций Excel:

КОРРЕЛ(массив1;массив2) вычисляет коэффициент корреляциимежду двумя переменными; значения первой из них приведены вдиапазоне массив1, значения второй – в диапазоне массив2;

НАКЛОН(известные_значения_y;известные_значения_x)служитдля определения коэффициента b;

ОТРЕЗОК(известные_значения_y;известные_значения_x)служит для определения коэффициента a.

Вводим формулы:

Встроенная статистическая функция ЛИНЕЙНопределяетпараметры линейной регрессии. Порядок вычислений следующий:

1) выделите область пустых ячеек 5х2 (5 строк, 2 столбца) с цельювывода результатов регрессионной статистики (А27:В3);

2) в главном меню выберите Вставка/Функция;

3) в строке Категория(рис.1.5) выберите Статистические, в окнеФункция– ЛИНЕЙН. Щелкните ОК.

Рис. 1.5. Диалоговое окно «Мастер функций»

4) Заполните аргументы функции (рис.1.6.):

Известные_значения_у – диапазон, содержащий данныерезультативного признака;

Известные_значения_х – диапазон, содержащий данные факторовнезависимого признака;

Константа – логическое значение, которое указывает на наличиеили на отсутствие свободного члена в уравнении; если Константа = 1, тосвободный член рассчитывается обычным образом, если Константа = 0,то свободный член равен 0.

Статистика – логическое значение, которое указывает выводитьдополнительную информацию по регрессионному анализу или нет. ЕслиСтатистика = 1, то дополнительная информация выводится, еслиСтатистика = 0, то выводится только оценки параметров уравнения.Далее ОК.

Рис.1.6. Диалоговое окно ввода аргументов функции ЛИНЕЙН

5) В левой верхней ячейке выделенной области появится первыйэлемент итоговой таблицы. Чтобы раскрыть всю таблицу, нажмите наклавишу F2,а затем – на комбинацию клавиш CTRL+SHIFT+ENTER.Дополнительная регрессионная статистика будет выводиться в порядке,указанном в следующей схеме:

Значение коэффициента	Значение коэффициента
Среднеквадратическое отклонение	Среднеквадратическое отклонение
Коэффициент детерминации	Среднеквадратическое отклонение y
F - статистика	Число степеней свободы
Регрессионная сумма квадратов	Остаточная сумма квадратов

Результаты регрессионного анализа представлены на рис.1.7.

Рис. 1.7.Результаты регрессионного анализа