Вычисление коэффициентов линейной множественной регрессии и проверка значимости в режиме Регрессия
Цель работы. Используя пространственную выборку таблицы 3.1 и используя режим Регрессия необходимо вычислить вектор коэффициентов уравнения регрессии
. (3.4)
Режим Регрессия модуля Анализ данных. Табличный процессор Excel содержит модуль Анализ данных. Этотмодуль позволяет выполнить статистический анализ выборочных данных (построение гистограмм, вычисление числовых характеристик и т.д.). Режим работы Регрессия этого модуля осуществляет вычисление коэффициентов линейной множественной регрессии с 
переменными, построение доверительные интервалы и проверку значимости уравнения регрессии.
Для вызова режима Регрессиямодуля Анализ данных необходимо:
· обратиться к пункту менюСервис;
· в появившемся меню выполнить команду Анализ данных;
· в списке режимов работы модуля Анализ данных выбрать режим Регрессия и щелкнуть на кнопке Ok.
После вызова режимаРегрессияна экране появляется диалоговое окно (см. рис. 3.2), в котором задаются следующие параметры:
1. Входной интервал Y – вводится диапазон адресов ячеек, содержащих значения 
(ячейки должны составлять один столбец).
Рис. 3.2. Диалоговое окно режима Регрессия
2. Входной интервал X – вводится диапазон адресов ячеек, содержащих значения независимых переменных. Значения каждой переменной представляются одним столбцом. Количество переменных не более 16 (т.е. 
).
3. Метки – включается если первая строка во входном диапазоне содержит заголовок. В этом случае автоматически будут созданы стандартные названия.
4. Уровень надежности – при включении этого параметра задается надежность 
при построении доверительных интервалов.
5. Константа-ноль – при включении этого параметра коэффициент 
.
6. Выходной интервал – при включении активизируется поле, в которое необходимо ввести адрес левой верхней ячейки выходного диапазона, который содержит ячейки с результатами вычислений режима Регрессия.
7. Новый рабочий лист – при включении этого параметра открывается новый лист, в который начиная с ячейки А1 вставляются результаты работы режима Регрессия.
8. Новая рабочая книга - при включении этого параметра открывается новая книга на первом листе которой начиная с ячейки А1 вставляются результаты работы режима Регрессия.
9. Остатки – привключении вычисляется столбец, содержащий невязки 
.
10. Стандартизованные остатки – при включении вычисляется столбец, содержащий стандартизованные остатки.
11. График остатков – при включении выводятся точечные графики невязки , в зависимости от значений переменных 
. Количество графиков равно числу переменных 
.
12. График подбора – при включении выводятся точечные графики предсказанных по построенной регрессии значений 
от значений переменных . Количество графиков равно числу переменных .
Решение. Первоначально введем в столбец С десять значений первой переменной, в столбец D - десять значений первой переменной (см. рис. 3.2), а в столбец F – десять значений зависимой переменной.
После этого вызовем режим Регрессия и в диалоговом окне зададим необходимые параметры (см. рис. 3.2). Результаты работы приводятся рис. 3.3 – 3.5. Заметим, из-за большой «ширины» таблиц, в которых выводятся результаты работы режима Регрессия, часть результатов помещены в другие ячейки.
Рис. 3.3. Результаты работы режима Регрессия
Дадим краткую интерпретацию показателям, значения которых вычисляются в режиме Регрессия. Первоначально рассмотрим показатели, объединенные названием Регрессионная статистика (см. рис. 3.3).
Множественный 
- корень квадратный из коэффициента детерминации.
квадрат – коэффициент детерминации 
.
Нормированный квадрат – приведенный коэффициент детерминации 
(см. формулу (2.1)).
Стандартная ошибка – оценка 
для среднеквадратического отклонения 
.
Наблюдения – число наблюдений 
.
Перейдем к показателям, объединенных названием Дисперсионный анализ (см. рис. 3.3).
Столбец 
- число степеней свободы. Для строки Регрессия показатель равен числу независимых переменных 
; для строки Остаток - равен 
; для строки Итого – равен 
.
Столбец SS – сумма квадратов отклонений. Для строки Регрессия показатель равен величине 
(см. формулы (1.16)), т.е.
;
для строки Остаток - равен величине 
(см. формулы (1.16)), т.е.
;
для строки Итого – равен 
.
Столбец 
дисперсии, вычисленные по формуле
,
т.е. дисперсия на одну степень свободы.
Столбец 
– значение 
, равное 
критерию Фишера, вычисленного по формуле:
.
Столбец значимость - значение уровня значимости, соответствующее вычисленной величине критерия и равное вероятности 
, где 
- случайная величина, подчиняющаяся распределению Фишера с 
степенями свободы. Эту вероятность можно также определить с помощью функции FРАСП( 
). Если вероятность меньше уровня значимости 
(обычно 
), то построенная регрессия является значимой..
Перейдем к следующей группе показателей, объединенных в таблице, показанной на рис. 3.4.
Рис. 3.4. Продолжение результатов работы режима Регрессия
Столбец Коэффициенты – вычисленные значения коэффициентов 
, расположенных сверху-вниз.
Столбец Стандартная ошибка – значения 
, вычисленные по формуле 
.
Столбец 
статистика – значения статистик 
.
Столбец Р – значение – содержит вероятности случайных событий 
, где 
случайная величина, подчиняющаяся распределению Стьюдента с 
степенями свободы.
Если эта вероятность меньше уровня значимости , то принимается гипотеза о значимости соответствующего коэффициента регрессии.
Из рис. 3.4 видно, что значимым коэффициентом является только коэффициент 
.
Столбцы Нижние 95% и Верхние 95% - соответственно нижние и верхние интервалы для оцениваемых коэффициентов 
.
Перейдем к следующей группе показателей, объединенных в таблице, показанной на рис. 3.5.
Рис. 3.5. Продолжение результатов работы режима Регрессия
Столбец Наблюдение – содержит номера наблюдений.
Столбец Предсказанное У – значения , вычисленные по построенному уравнению регрессии.
Столбец Остатки – значения невязок 
В заключении рассмотрения результатов работы режима Регрессия приведем график невязок (на рисунке 3.6 невязки названы остатками) при заданных значениях только второй переменной. Наличие чередующихся положительных и отрицательных значений невязок является косвенным признаком отсутствия систематической ошибки (неучтенной независимой переменной) в построенном уравнении регрессии.
Рис. 3.6. График невязок как функция переменной 