Цель работы. Используя режим Регрессия, вычислить вектор коэффициентов уравнения регрессии (4.1)

Табличный процессор MS Excel содержит модуль Анализ данных. Этот модуль позволяет выполнить статистический анализ выборочных данных (построение гистограмм, вычисление числовых характеристик и т.д.). Режим работы Регрессия этого модуля осуществляется вычисление коэффициентов линейной множественной регрессии с k переменными, построение доверительных интервалов и проверку значимости уравнения регрессии.

После вызова режима Регрессия на экране появляется диалоговое окно (рис 4.1), в котором задаются следующие параметры:

Рисунок 4.1

1. Входной интервал Y – вводится диапазон ячеек, содержащих значения (ячейки должны составлять один столбец).

2. Входной интервал X – вводится диапазон ячеек содержание значения независимых переменных. Значения каждой переменой представляются одним столбцом. Количество переменных- не более 16.

3. Метки – включается, если первая строка во входном диапазоне содержит заголовок. В этом случае автоматически будут созданы стандартные названия.

4. Уровень надежности – при включении этого параметра задается надежность при построении доверительных интервалов.

5. Константа-ноль – при включении этого параметра коэффициент

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

7. Новый рабочий лист – при включении этого параметра открывается новый лист, в который, начиная с ячейки A1, вставляются результаты работы режима Регрессия.

8. Новая рабочая книга – при включении этого параметра открывается новая книга, на первом листе которой, начиная с ячейки A1, вставляются результаты работы режима Регрессия.

9. Остатки – при включении вычисляется столбец, содержащий невязки .

10. Стандартизированные остатки – при включении вычисляется столбец, содержащий стандартизированные остатки.

11. График остатков – при включении выводятся точечные графики невязки в зависимости от значений переменных Количество графиков равно число k переменных .

12. График подбора – при включении выводятся точечные графики предсказанных по построенной регрессии значений от значений переменных Количество графиков равно числу k переменных .

Решение. Первоначально введем в столбец C десять значений первой переменной , в столбец D – десять значений второй переменной , а в столбец F – десять значений переменной Y.

После этого вызовем режим Регрессия и в диалогом окне зададим необходимые параметры (рис. 4.1). Результаты работы приведены на рис.4.2 -4.3. Заметим, что из-за большой «ширины» таблиц, в которых выводятся результаты работы режима Регрессия, часть результатов помещена в другие ячейки.

Дадим краткую интерпретацию показателем, значения которых вычисляются в режиме Регрессия. Первоначально рассмотрим показатели, объединённые названием Регрессионная статистика (рис. 4.2).

Рис.4.2

Множественный R – корень квадратный из коэффициента детерминации.

R-квадрат – коэффициент детерминации .

Нормированный R-квадрат – приведенный коэффициент детерминации.

Стандартная ошибка – оценка S для среднеквадратичного отклонения .

Наблюдения – число наблюдений n.

Перейдем к показателям, объединенными названием Дисперсионный анализ (рис. 4.2).

Столбец df – число степеней свободы. Для строки Регрессия показатель числу равен числу независимых переменных для строки Остаток он равен для строки Итого .

Столбец SS – сумма квадратов отклонений. Для строки Регрессия показатель равен величине (см. формулы (2.10)); Для строки Остаток – равен величине (см. формулы (2.10)); для строки Итого .

Столбец MS – дисперсии, вычисленные по формуле , т.е. дисперсия на одну степень свободы.

Столбец F – значение , равное F-критерию Фишера, вычисленному по формуле

Столбец значимости F – значение уровня значимости, соответствующее вычисленной величине F-критерия и равное вероятности ), где – случайная величина, подчиняющая распределению Фишера с степенями свободы. Если вероятность меньше уровня значимости (обычно , то построенная регрессия является значимой.

Перейдем к следующей группе показателей, объединенных в табл. 4.2.

Таблица 4.2

	Коэффициенты	Стандартная ошибка	t-статистика
Y-пересечение	-3,539	1,907	-1,8564
Переменная	0,854	0,221	3,8726
Переменная	0,367	0,243	1,5108
	P-значение	Нижние 95 %	Верхние 95%
	0,1058	-8,0477	0,9690
	0,0061	0,3325	1,3753
	-0,1746	-0,2074	0,9415

Столбец Коэффициенты – вычисленные значения коэффициентов расположенных сверху вниз.

Столбец Стандартная ошибка – значения , вычисленные по формуле

Столбец t-статистика – значение статистик

Столбец P-значение – содержит вероятности случайных событий

где – случайная величина, подчиняющаяся распределению Стьюдента с степенями свободы.

Если эта вероятность меньше уровня значимости , то принимается гипотеза о значимости соответствующего коэффициента регрессии.

Из табл. 4.2 видно, что значимым коэффициентом является только коэффициент при 0,854, так как P – значенияпри равно 0,0061, что меньше .

Столбцы Нижние 95% и Верхние 95% - соответственно нижние и верхние интервалы для оцениваемых коэффициентов .

Перейдем к следующей группе показателей, объединенных в табл.4.3.

Столбец Наблюдение – содержит номера наблюдений.

Столбец Предсказание У – значения , вычисленные по построенному уравнению регрессии.

Столбец Остатки – значение невязок .

Таблица 4.3

В заключении рассмотрения результатов режима работы Регрессия приведем график невязок – остатков при заданных значениях только второй переменной (рис. 4.3). Наличие чередующихся положительных и отрицательных значений невязок является косвенным признаком отсутствия систематической ошибки (неучтенной независимой переменной) в построенном уравнении регрессии.

Рисунок 4.3.

БИБЛИОГРАФИЧЕСКИЙ СПИСОК

1. Брандт З. Анализ данных. Статистические и вычислительные методы для научных работников и инженеров : учеб. пособие / З. Брандт ; пер. с англ. О. И. Волкова. ‒ М. : Мир, 2003. ‒ 686 с.

2. Вадзинский Р. Статистические вычисления в среде Excel. Библиотека пользователя Р. Вадзинский. ‒ СПб. : Питер, 2008. ‒ 608 с.

3. Горелова Г. В. Теория вероятностей и математическая статистика в примерах и задачах с применением Excel : учеб. пособие / Г. В. Горелова, И. А. Кацко. – 3-е изд., доп. и перераб. – Ростов н/Д : Феникс, 2005. – 480 с.

4. Колемаев В. А. Теория вероятностей и математическая статистика : учебник / В. А. Колемаев, В. Н. Калинина. ‒ М. : ИНФРА-М, 2000. ‒ 336 с.

5. Лемешко Б. Ю. Некоторые приложения, рекомендуемые к использованию в задачах прикладной математической статистики [Электронный ресурс] : учеб. пособие / Б. Ю. Лемешко. ‒ Режим доступа: http://ami.nstu.ru/~headrd/applied/index.html.

6. Минько А. А. Статистический анализ в MS Excel / А. А. Минько. – М. : Издательский дом «Вильямс», 2004. – 448 с.

7. Розанов Ю. А. Теория вероятностей, случайные процессы и математическая статистика : учебник / Ю. А. Розанов. ‒ 2-е изд., доп. ‒ М. : Наука, 1989. ‒ 312 с.

8. Себер Дж. Линейный регрессионный анализ / Дж. Себер ; пер. с англ.

В. П. Носко. ‒ М. : Мир, 1980. ‒ 456 с.

9. Тюрин Ю.Н. Анализ данных на компьютере /Ю.Н. Тюрин, А.А. Макаров под ред. В. Э. Фигурнова. – 3-е изд., перераб. и доп. –М. : ИНФРА М,2003. – 544 с.

10. Ходасевич Г. Б. Обработка экспериментальных данных на ЭВМ. Ч. 1. Обработка одномерных данных [Электронный ресурс] : учеб. пособие / Г. Б. Ходасевич. ‒ Режим доступа: http://www.dvo.sut.ru/libr/opds/il30 hodo_part1/4.htm.

Приложение 1