Выполнение задания в ППП MS Excel.

Ход работы:

Определите фактор, оказывающий влияние (x) и результативный признак (y). Для построения уравнения регрессии воспользуемся Пакетом анализа: Сервис – Анализ данных – Регрессия. Если строки Анализ данных в Сервисе нет, то надо предварительно выполнить следующие действия: Сервис – Надстройки – Пакет анализа ( отметить флажком).

В окне Регрессия:

Входной интервал Х – это столбец данных, определенных вами как фактор (причина); Входной интервал Y – это столбец данных, определенных вами как результат. Выходной интервал – несколько чистых ячеек на том же листе, где находятся исходные данные, или на отдельном листе. В результате получаем таблицу расчетов (см. пример в таблице 2).

Таблица 2. «Вывод итогов»

Регрессионная статистика
Множественный R	0,502621
R-квадрат	0,252628
Нормированный R-квадрат	0,159206
Стандартная ошибка	25,16016
Наблюдения

 

 

Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия		1711,83	1711,83	2,704169	0,138707
Остаток		5064,27	633,0337
Итого		6776,1

 

 

	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение
Y-пересечение	18,37277	19,30663	0,95163	0,369146
Переменная x1	0,533713	0,324557	1,644436	0,138707

 

1.Из таблицы берем значения коэффициентов регрессии и получаем уравнение: у = 18,37277 + 0,533713х₁ (коэффициенты при y – пересечении и переменной x₁).

2.Проверка на значимость заключается в проверке гипотезы Н0:а₀=0,т.е. проверке гипотезы о статистической незначимости проверяемого параметра а₀, другими словами, проверки вывода о том, что проверяемый параметр сформировался под влиянием случайных причин, а не в результате достаточно сильного влияния, т.е. закономерности. В таблице уровень значимости (Р-значение t-статистики при Y-пересечении) равен 0,369146, что значительно превышает уровень 0,05 или 5%. Делаем вывод о том, что гипотезу о незначимости коэффициента регрессии а₀ принимаем. Аналогично проверяем гипотезу о незначимости параметра b₁ (при переменной x₁) и гипотезу о незначимости всей регрессионной модели (по уровню значимости F).

Проверку значимости коэффициентов уравнения можно сделать по значению t-статистики, которое сравнивается с пороговым значением, зафиксированным в таблице t-статистики. Для коэффициента а₀значение t-статистики равно 0,951653, для коэффициента b₁ значение t - статистики равно 1,644436. Сравниваем каждое из этих значений с пороговым значением. Если пороговое значение t-статистики меньше, чем в данной модели, проверяемый коэффициент уравнения значим и наоборот. Пороговое значение найдете в таблице значений t-статистики. Пороговое значение находим в таблице в зависимости от выбранного уровня значимости и числа степеней свободы, рассчитанного по формуле:

k = n – 3

k- число степеней свободы:

n- число наблюдений.

Выбранный уровень значимости указывает вероятность ошибки, т.е. при уровне значимости 0,05 проверяемый коэффициент уравнения регрессии считается значимым с вероятностью (1 – 0,05) или 95%; при уровне значимости 0,01 проверяемый коэффициент уравнения регрессии считается значимым с вероятностью (1 – 0,01) или 99%.

Возможен и другой способ проверки. В таблице 2, кроме значений t-статистики, дан уровень значимости t-статистики (графа «Р – Значение»). Если уровень значимости, приведенный в таблице, не превышает 0,05 (5%), делаем вывод, то проверяемый коэффициент значим и наоборот. Кроме того, уровень значимости может быть указан более точно, чем больше или меньше 0,05. Так, «Р – Значение» равное 0,369146 говорит о том, уровень ошибки при проверке гипотезы о незначимости коэффициента а₀ уравнения регрессии равен 0,369146 (36,92%), а для коэффициента b₁ – 0,138707 (13,87%). Поскольку оба значения ошибки гораздо больше 5%, гипотеза о незначимости коэффициентов уравнения, следовательно и самого уравнения регрессии, принимается. Коэффициент a₀ может быть принят лишь с вероятностью (1–0,369146) или 63%, коэффициент b₁ – с вероятностью (1-0,138707) или 88%. 3.Оценку тесноты связи делаем по R – квадрат.

R – квадрат измеряет тесноту связи через отношение дисперсий результативного признака. Чем ближе R –квадрат к 0, тем слабее связь между x и y, чем ближе R –квадрат с 1, тем сильнее связь.

4.Проверка на значимость всей регрессионной модели, т.е. уравнения регрессии и коэффициента тесноты связи, проводим по F-критерию. Берем F-критерию из таблицы 2 и сравниваем с пороговым значением из таблицы “Значения F-критерия». Если пороговое значение F-критерия меньше, чем в данном примере (2,704169), проверяемая регрессионная модель значима и наоборот. Пороговое значение определяется в зависимости от выбранного уровня значимости и числа степеней свободы (k₁ и k₂), рассчитанных по формулам:

k₁ = n– 3

k₂ = n-m

где k₁ и k₂- число степеней свободы;

n - число наблюдений;

m – число параметров уравнения регрессии, для парной регрессии равно 2.

Второй вариант проверки – это определение вероятности принятия гипотезы о незначимости регрессионной модели по уровню “Значимость F ”, равное в этом примере 0,138707 или 13,87%. Данное значение, говорит о том, что модель незначима.

Определите факторы, оказывающие влияние и обозначьте их (x₁,x₂,…..x_k). Определите результативный признак (y). Для построения уравнения регрессии воспользуемся Пакетом анализа ППП Ехсеl: Сервис – Анализ данных – Регрессия. Если строки Анализ данных в Сервисе нет, то надо предварительно выполнить следующие действия: Сервис – Надстройки – Пакет анализа (поставить метку ).

В окне Регрессия:

Входной интервал Х – это столбцы данных, определенных вами как факторы (выделяются единым массивом); Входной интервал Y – это столбец данных, определенных вами как результат. Выходной интервал – несколько чистых ячеек на том же листе, где находятся исходные данные, или на отдельном листе. В результате получаем таблицу расчетов (см. пример в таблице 3).

 

Таблица 3. «Вывод итогов »

Регрессионная статистика
Множественный R	0,502621
R-квадрат	0,252628
Нормированный R-квадрат	0,159206
Стандартная ошибка	25,16016
Наблюдения

 

Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия		1711,83	1711,83	0,455188	0,769029
Остаток		5064,27	633,0337
Итого		6776,1

 

	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение
Y-пересечение	22,80765	19,30663	0,351022	0,739878
Переменная x1	0,469454	0,324557	0,812186	0,453601
Переменная x2	0,119464	36,14581	0,226852	0,829522
Переменная x3	19,47716	0,023568	2,692154	0,017188
Переменная x4	10,17820	0,014578	2,811450	0,005123

1.Из таблицы берем значения коэффициентов регрессии и получаем уравнение:

y=22,80765+0,469454x₁+0,119464x₂+19,47716x₃-10,1782x₄ (коэффициенты при y – пересечении и переменных x₁, x₂, x₃, x₄.

2.Проверка на значимость заключается в проверке гипотезы о статистической незначимости проверяемых параметров, или проверки вывода о том, что проверяемый параметр сформировался под влиянием случайных причин, а не в результате достаточно сильного влияния, т.е. закономерности. При этом уровень значимости представляет собой вероятность ошибки отклонения правильной гипотезы. Договоримся, что допустимый уровень значимости при выполнении лабораторной работы примем равным 0,05 или 5%.

Проверку значимости коэффициентов уравнения можно сделать по значению t-статистики, которое сравнивается с табличным значением. Для коэффициента a₀ значение t-статистики равно 0,351022; для коэффициента b₁ значение t-статистики равно 0,812186; для b₂ – 0,226852 и т.д. Сравниваем каждое из этих значений с пороговым. Если пороговое значение t-статистики меньше, чем в данной модели, проверяемый коэффициент уравнения значим и наоборот. Пороговое значение найдете в таблице t-статистики. Пороговое значение определяется в зависимости от выбранного уровня значимости и числа степеней свободы, рассчитанного по формуле:

k = n – m -1

k - число степеней свободы:

n - число наблюдений.

m - число факторов.

Выбранный уровень значимости указывает вероятность ошибки, т.е. при уровне значимости 0,05 проверяемый коэффициент уравнения регрессии считается значимым с вероятностью (1 – 0,05) или 95%; при уровне значимости 0,01 проверяемый коэффициент уравнения регрессии считается значимым с вероятностью (1 – 0,01) или 99%.

Возможен и другой способ проверки. В таблице 1, кроме значений t-статистики, дан уровень значимости t-статистики (графа «Р – Значение»). Если уровень значимости, приведенный в таблице 2, не превышает 0,05 (5%), делаем вывод, что проверяемый коэффициент значим и наоборот. Кроме того, уровень значимости может быть указан более точно, чем просто больше или меньше 0,05. Так, «Р – Значение» равное 0,739878 говорит о том, коэффициент a₀ уравнения регрессии можно считать значимым с вероятностью 0,739878 (73,99%), а коэффициент b₁ – 0,453601 (45,36%), коэффициент b₂ – 0,829522 (82,95%) и т.д. Поскольку все значения вероятности гораздо меньше 95%, гипотеза о незначимости коэффициентов уравнения принимается, а уравнение регрессии признается незначимым. Коэффициент a₀ может быть принят лишь с вероятностью (1–0,739878) или 26%, коэффициент b₁ – с вероятностью (1-0,453601) или 55% и т.д. Следовательно, перечисленные коэффициенты незначимы.

3.Оценку тесноты связи делаем по R – квадрат.

R – квадратизмеряет тесноту связи через отношение дисперсий результативного признака. Чем ближе R –квадратк 0, тем слабее связь между Х и Y, чем ближе R –квадрат с 1, тем сильнее связь. Нормированный R -квадрат дает скорректированную оценку коэффициента детерминации, т.е. долю вариации результата за счет включенных в уравнение регресии факторов в общей вариации результата с учетом степеней свободы общей и остаточной дисперсии. R – квадратпредставляет собой нескорректированную оценку тесноты связи (без учета числа степеней свободы). Различия между скорректированной и нескорректированной оценкой становятся важными, если мы должны сравнивать между собой модели с различным числом факторов. Сопоставлять уравнения регрессии с различным числом факторных признаков можно только по скорректированным значениям (Нормированный R - квадрат).

 

4.Проверка на значимость всей регрессионной модели, т.е. уравнения регрессии и коэффициента тесноты связи, проводим по F-критерию. Берем F-критерию из таблицы 2 и сравниваем с пороговым значением из таблицы “Значения F-критерия» Приложения 2. Если пороговое значение F-критерия меньше, чем в данном примере (0,4552188), проверяемая регрессионная модель значима и наоборот. Пороговое значение определяется в зависимости от выбранного уровня значимости и числа степеней свободы (k₁ и k₂), рассчитанных по формулам:

k₁ = n – 3

k₂ = n-m

где k₁ и k₂- число степеней свободы;

n - число наблюдений;

m - число параметров уравнения регрессии, для парной регрессии равно 2.

Второй вариант проверки – это определение вероятности принятия гипотезы о незначимости регрессионной модели по значению ”Значимость F ”, равное в этом примере 0,769029 или 76,9%. Данное значение ошибки при принятии гипотезы означает, что F-критерий незначим.

5.Для того, что улучшить создаваемую модель, надо сравнить между собой несколько вариантов регрессионной модели с различным числом факторов. Вспомним, что предпочтительнее та модель, которая при том же значении коэффициента детерминации имеет меньшее количество факторов, включенных в нее. Например, сравним 2-факторную модель с факторами х₁ и х₂ (таблица 4) с 3-факторной моделью, включающей факторы х₁, х₂, х₃ (таблица 5) и с 4-факторной моделью, содержащей факторы х₁, х₂, х₃, х₄ (таблица 6).

Таблица 4. Вывод итогов

Регрессионная статистика
Множественный R	0,990069
R-квадрат	0,980237
Нормированный R-квадрат	0,974591
Стандартная ошибка	0,482617
Наблюдения

 

 

Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия		1711,83	1711,83	173,6	0,00000109
Остаток		5064,27	633,0337
Итого		6776,1

 

 

	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение
Y-пересечение	23,76087	1,46212	16,25098	8,13Е-07
Переменная x1	-1,43478	0,311799	-4,60163	0,002479
Переменная x2	-0,86957	0,450043	-1,93218	0,094618

 

Таблица 5.Вывод итогов

Регрессионная статистика
Множественный R	0,994937
R-квадрат	0,989899
Нормированный R-квадрат	0,984848
Стандартная ошибка	0,372678
Наблюдения

 

Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия		1711,83	1711,83		2,25Е-06
Остаток		5064,27	633,0337
Итого		6776,1

 

	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение
Y-пересечение	16,5	3,234325	5,101528	0,002218
Переменная x1	-0,83333	0,347853	-2,39565	0,053612
Переменная x2	-0,83333	0,347853	-2,39565	0,053612
Переменная x3	0,83333	0,347853	2,39548	0,053612

 

Таблица 6. Вывод итогов

Регрессионная статистика
Множественный R	0,998085
R-квадрат	0,996173
Нормированный R-квадрат	0,993112
Стандартная ошибка	0,251271
Наблюдения

 

 

Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия		1711,83	1711,83	325,4193	3,16Е-06
Остаток		5064,27	633,0337
Итого		6776,1

 

	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение
Y-пересечение	10,38235	3,052896	3,400821	0,019234
Переменная x1	-0,49216	0,263065	-1,87085	0,120281
Переменная x2	-0,65686	0,242496	-2,70876	0,042339
Переменная x3	0,75098	0,236290	3,17821	0,024588
Переменная x4	0,517647	0,180784	2,863347	0,035268

 

Сравним модели но нормированным R-квадрат. Самое большое значение у 3-факторной модели. Видно, что по мере увеличения факторов в уравнение регрессии увеличивается значение нормированного R-квадрат. Такое возможно только в том случае, когда в уравнение добавляются значимые факторы, оказывающие значительное влияние на результат. Именно поэтому их включение в регрессионную модель в той последовательности, в которой они включены в модель, целесообразно.

Улучшить регрессионную модель можно, оценив целесообразность включения каждого фактора отдельно, по коэффициентам парной корреляции: Сервис – Анализ данных – Корреляция. Входной интервал: выделяем весь массив данных; Группирование - по столбцам (ставим метку); Выходной интервал – выделяем несколько свободных ячеек. Пример расчетов представлен в таблице 7.

 

Таблица 7. Пример корреляционной матрицы

Y	x1	x2	x3	x4
x1	0,98473
x2	0,9594	0,8094
x3	0,969223	-0,9525	0,6921
x4	0,971908	-0,9513	0,71997	0,7199

 

В таблице 7 перечислены коэффициенты, показывающие тесноту связи между признаками попарно. Сравним их друг с другом по абсолютной величине. Особое внимание обратим на межфакторные связи, т.е. на связи между различными x_k. Если межфакторная связь сильнее, чем связь фактора с результативным признаком, такой фактор следует исключить из уравнения регрессии. Наличие сильной межфакторной связи свидетельствует о сильной коллинеарности (взаимосвязи) факторов. Для качественной регрессионной модели недопустим уровень коллинеарности, превышающий 0,8.

В рассматриваемом примере самой сильной является связь y с x₁ (0,98473), затем по мере убывания y с x₄ (0,97908), y с x₃ (0,969223), y с x₂ (0,9594), x₁ с x₃ (0,9525), x₁ с _Х4 (0,9513), x₁ с x₂ (0,8094), x₂ с x₄ (0,71997), x₃ с x₄ (0,7199), x₂ с x₃ (0,6921). Поскольку межфакторные связи слабее связи факторного и результативного признаков, следует оставить в уравнении все факторные признаки, но требованию неколлинеарности факторов уравнения регрессии отвечает лишь связь x₂ с x₄; x₃ с x₄ и x₂ с x₃. Однако для окончательного вывода о целесообразности присутствия в уравнении каждого из факторных признаков, следует проверить уровни значимости коэффициентов уравнения по таблицам 3,4 и 5.

Поскольку 4-х факторная модель имеет самый высокий уровень коэффициента детерминации, начнем с нее. По Р-Значениям t-критерия видим, что коэффициент b₁ имеет Р-Значениям t-критерия больше 5% (t-критерий равен 0,120281), следовательно x₁ надо исключить из уравнения. (Это объясняется тем, что несмотря на сильную связь y и x₁ мы исключаем x₁ из уравнения регрессии из-за наличия сильной коллинеарности x₁ с x₂, x₃, x₄).

Аналогичную проверку сделаем для других значений х_k и для других моделей. По Р-Значениям t-критерия видно, что в 3-х факторной модели все факторы находятся в одинаковом положении, т.к. уровень Р-Значениям t-критерия почти равен 5%. Для 2-х факторной модели удаленным из уравнения должен быть фактор x₂ (Р-Значениям t-критерия для b₂ = 0,094618, что больше 0,05). С учетом уровня Р-Значениям всей модели (Р-Значения F-критерия) можно сделать вывод о высокой надежности всех трех моделей (0,00000109; 0,00000225; 0,00000316 соответственно).

Задание к лабораторной работе №3.

1.Построить уравнение парной линейной регрессии.

2.Измерить тесноту связи.

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

4.Построить уравнение множественной регрессии.

5.Измерить тесноту связи.

6.Проверить на значимость коэффициенты множественной регрессии и регрессионную модель.

7.Улучшить регрессионную модель путем удаления из модели факторов, не имеющих значимого влияния на результат.

8.Построить уравнение парной регрессии нелинейной формы.