Выполнение задания 2 в ППП MS Excel 2007.
Определите факторы, оказывающие влияние и обозначьте их (x1,x2,…..xk). Определите результативный признак (y). Для построения уравнения регрессии воспользуемся Пакетом анализа ППП Ехсеl: Данные – Анализ данных – Регрессия.
В окне Регрессия:
Входной интервал Х – это столбцы данных, определенных вами как факторы (выделяются единым массивом); Входной интервал Y – это столбец данных, определенных вами как результат. Выходной интервал – несколько чистых ячеек на том же листе, где находятся исходные данные, или на отдельном листе. В результате получаем таблицу расчетов (см. пример в таблице 3).
Таблица 3. «Вывод итогов»
| Регрессионная статистика | |
| Множественный R | 0,502621 |
| R-квадрат | 0,252628 |
| Нормированный R-квадрат | 0,159206 |
| Стандартная ошибка | 25,16016 |
| Наблюдения | 50 |
| Дисперсионный анализ | |||||
| df | SS | MS | F | Значимость F | |
| Регрессия | 1 | 1711,83 | 1711,83 | 0,455188 | 0,769029 |
| Остаток | 48 | 5064,27 | 633,0337 | ||
| Итого | 49 | 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,469454x1+0,119464x2+19,47716x3-10,1782x4 (коэффициенты при y – пересечении и переменных x1, x2, x3, x4.
|
|
|
2. Проверка на значимость заключается в проверке гипотезы о статистической незначимости проверяемых параметров, или проверки вывода о том, что проверяемый параметр сформировался под влиянием случайных причин, а не в результате достаточно сильного влияния, т.е. закономерности. При этом уровень значимости представляет собой вероятность ошибки отклонения правильной гипотезы. Договоримся, что допустимый уровень значимости при выполнении лабораторной работы примем равным 0,05 или 5%.
Проверку значимости коэффициентов уравнения можно сделать по значению t-статистики, которое сравнивается с табличным значением. Для коэффициента a0 значение t-статистики равно 0,351022; для коэффициента b1 значение t-статистики равно 0,812186; для b2 – 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 говорит о том, коэффициент a0 уравнения регрессии можно считать значимым с вероятностью 0,739878 (73,99%), а коэффициент b1 – 0,453601 (45,36%), коэффициент b2 – 0,829522 (82,95%) и т.д. Поскольку все значения вероятности гораздо меньше 95%, гипотеза о незначимости коэффициентов уравнения принимается, а уравнение регрессии признается незначимым. Коэффициент a0 может быть принят лишь с вероятностью (1–0,739878) или 26%, коэффициент b1 – с вероятностью (1-0,453601) или 55% и т.д. Следовательно, перечисленные коэффициенты незначимы.
|
|
|
3. Оценку тесноты связи делаем по R – квадрат.
R – квадрат измеряет тесноту связи через отношение дисперсий результативного признака. Чем ближе R –квадрат к 0, тем слабее связь между Х и Y, чем ближе R –квадрат с 1, тем сильнее связь. Нормированный R -квадрат дает скорректированную оценку коэффициента детерминации, т.е. долю вариации результата за счет включенных в уравнение регресии факторов в общей вариации результата с учетом степеней свободы общей и остаточной дисперсии. R – квадрат представляет собой нескорректированную оценку тесноты связи (без учета числа степеней свободы). Различия между скорректированной и нескорректированной оценкой становятся важными, если мы должны сравнивать между собой модели с различным числом факторов. Сопоставлять уравнения регрессии с различным числом факторных признаков можно только по скорректированным значениям (Нормированный R - квадрат).
4. Проверка на значимость всей регрессионной модели, т.е. уравнения регрессии и коэффициента тесноты связи, проводим по F -критерию. Берем F -критерию из таблицы 2 и сравниваем с пороговым значением из таблицы «Значения F -критерия» Приложения 2. Если пороговое значение F -критерия меньше, чем в данном примере (0,4552188), проверяемая регрессионная модель значима и наоборот. Пороговое значение определяется в зависимости от выбранного уровня значимости и числа степеней свободы (k1 и k2), рассчитанных по формулам:
|
|
|
k1 = n – 3
k2 = n-m
где k1 и k2- число степеней свободы;
n - число наблюдений;
m - число параметров уравнения регрессии, для парной регрессии равно 2.
Второй вариант проверки – это определение вероятности принятия гипотезы о незначимости регрессионной модели по значению «Значимость F», равное в этом примере 0,769029 или 76,9%. Данное значение ошибки при принятии гипотезы означает, что F-критерий незначим.
5. Для того, что улучшить создаваемую модель, надо сравнить между собой несколько вариантов регрессионной модели с различным числом факторов. Вспомним, что предпочтительнее та модель, которая при том же значении коэффициента детерминации имеет меньшее количество факторов, включенных в нее. Например, сравним 2-факторную модель с факторами х1 и х2 (таблица 4) с 3-факторной моделью, включающей факторы х1, х2, х3 (таблица 5) и с 4-факторной моделью, содержащей факторы х1, х2, х3, х4 (таблица 6).
Таблица 4. Вывод итогов
| Регрессионная статистика | |
| Множественный R | 0,990069 |
| R-квадрат | 0,980237 |
| Нормированный R-квадрат | 0,974591 |
| Стандартная ошибка | 0,482617 |
| Наблюдения | 50 |
| Дисперсионный анализ | |||||
| df | SS | MS | F | Значимость F | |
| Регрессия | 2 | 1711,83 | 1711,83 | 173,6 | 0,00000109 |
| Остаток | 47 | 5064,27 | 633,0337 | ||
| Итого | 49 | 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 |
| Наблюдения | 50 |
| Дисперсионный анализ | |||||
| df | SS | MS | F | Значимость F | |
| Регрессия | 3 | 1711,83 | 1711,83 | 196 | 2,25Е-06 |
| Остаток | 46 | 5064,27 | 633,0337 | ||
| Итого | 49 | 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 |
| Наблюдения | 50 |
|
Дисперсионный анализ | |||||
| df | SS | MS | F | Значимость F | |
| Регрессия | 4 | 1711,83 | 1711,83 | 325,4193 | 3,16Е-06 |
| Остаток | 45 | 5064,27 | 633,0337 | ||
| Итого | 49 | 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 -квадрат. Такое возможно только в том случае, когда в уравнение добавляются значимые факторы, оказывающие значительное влияние на результат. Именно поэтому их включение в регрессионную модель в той последовательности, в которой они включены в модель, целесообразно.
Улучшить регрессионную модель можно, оценив целесообразность включения каждого фактора отдельно, по коэффициентам парной корреляции: Данные – Анализ данных – Корреляция. Входной интервал: выделяем весь массив данных; Группирование - по столбцам (ставим метку); Выходной интервал – выделяем несколько свободных ячеек. Также возможен другой способ – при помощи функции 
– Статистические – КОРРЕЛ, т.е. вводим я ячейку:
=КОРРЕЛ(массив1;массив2)
где
массив1 – данные по одному признаку,
массив2 – данные по второму признаку.
Пример расчетов представлен в таблице 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 перечислены коэффициенты, показывающие тесноту связи между признаками попарно. Сравним их друг с другом по абсолютной величине. Особое внимание обратим на межфакторные связи, т.е. на связи между различными xk. Если межфакторная связь сильнее, чем связь фактора с результативным признаком, такой фактор следует исключить из уравнения регрессии. Наличие сильной межфакторной связи свидетельствует о сильной коллинеарности (взаимосвязи) факторов. Для качественной регрессионной модели недопустим уровень коллинеарности, превышающий 0,8.
В рассматриваемом примере самой сильной является связь y с x1 (0,98473), затем по мере убывания y с x4 (0,97908), y с x3 (0,969223), y с x2 (0,9594), x1 с x3 (0,9525), x1 с Х4 (0,9513), x1 с x2 (0,8094), x2 с x4 (0,71997), x3 с x4 (0,7199), x2 с x3 (0,6921). Поскольку межфакторные связи слабее связи факторного и результативного признаков, следует оставить в уравнении все факторные признаки, но требованию неколлинеарности факторов уравнения регрессии отвечает лишь связь x2 с x4; x3 с x4 и x2 с x3. Однако для окончательного вывода о целесообразности присутствия в уравнении каждого из факторных признаков, следует проверить уровни значимости коэффициентов уравнения по таблицам 3,4 и 5.
Поскольку 4-х факторная модель имеет самый высокий уровень коэффициента детерминации, начнем с нее. По Р-Значениям t -критерия видим, что коэффициент b1 имеет Р-Значениям t -критерия больше 5% (t-критерий равен 0,120281), следовательно x1 надо исключить из уравнения. (Это объясняется тем, что несмотря на сильную связь y и x1 мы исключаем x1 из уравнения регрессии из-за наличия сильной коллинеарности x1 с x2, x3, x4).
Аналогичную проверку сделаем для других значений хk и для других моделей. По Р-Значениям t -критерия видно, что в 3-х факторной модели все факторы находятся в одинаковом положении, т.к. уровень Р-Значениям t -критерия почти равен 5%. Для 2-х факторной модели удаленным из уравнения должен быть фактор x2 (Р-Значениям t -критерия для b2 = 0,094618, что больше 0,05). С учетом уровня Р-Значениям всей модели (Р-Значения F -критерия) можно сделать вывод о высокой надежности всех трех моделей (0,00000109; 0,00000225; 0,00000316 соответственно).
Дата добавления: 2021-03-18; просмотров: 60; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!