Вычисление коэффициентов линейной множественной регрессии и проверка значимости в режиме Регрессия

Цель работы. Используя пространственную выборку таблицы 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. График невязок как функция переменной