Решение типовой задачи в MS Excel

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

Если в меню сервис еще нет команды Анализ данных, то необходимо сделать следующее. В главном меню последовательно выбираем Сервис→Надстройкии устанавливаем «флажок» в строке Пакет анализа(рис. 2.2):

Рис. 2.2

Далее следуем по следующему плану.

1. Если исходные данные уже внесены, то выбираем Сервис→Анализ данных→Регрессия.

2. Заполняем диалоговое окно ввода данных и параметров вывода (рис. 2.3):

Рис. 2.3

Здесь:

Входной интервал Y – диапазон, содержащий данные результативного признака;

Входной интервал X – диапазон, содержащий данные признака-фактора;

Метки – «флажок», который указывает, содержи ли первая строка названия столбцов;

Константа – ноль – «флажок», указывающий на наличие или отсутствие свободного члена в уравнении;

Выходной интервал – достаточно указать левую верхнюю ячейку будущего диапазона;

Новый рабочий лист – можно указать произвольное имя нового листа (или не указывать, тогда результаты выводятся на вновь созданный лист).

Получаем следующие результаты для рассмотренного выше примера:

Рис. 2.4

Откуда выписываем, округляя до 4 знаков после запятой и переходя

к нашим обозначениям:

Уравнение регрессии:

Коэффициент корреляции:

Коэффициент детерминации:

Фактическое значение F -критерия Фишера:

F =10,8280

Остаточная дисперсия на одну степень свободы:

Корень квадратный из остаточной дисперсии (стандартная ошибка):

Стандартные ошибки для параметров регрессии:

m_a = 24,2116, m_b = 0,2797.

Фактические значения t -критерия Стьюдента:

t_a = 3,1793, t_b = 3,2906.

Доверительные интервалы:

Как видим, найдены все рассмотренные выше параметры и характеристики уравнения регрессии, за исключением средней ошибки аппроксимации (значение t -критерия Стьюдента для коэффициента корреляции совпадает с t_b ). Результаты «ручного счета» от машинного отличаются незначительно (отличия связаны с ошибками округления).