Выполнение задания в ППП 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х1 (коэффициенты при y – пересечении и переменной x1).
2.Проверка на значимость заключается в проверке гипотезы Н0:а0=0,т.е. проверке гипотезы о статистической незначимости проверяемого параметра а0, другими словами, проверки вывода о том, что проверяемый параметр сформировался под влиянием случайных причин, а не в результате достаточно сильного влияния, т.е. закономерности. В таблице уровень значимости (Р-значение t-статистики при Y-пересечении) равен 0,369146, что значительно превышает уровень 0,05 или 5%. Делаем вывод о том, что гипотезу о незначимости коэффициента регрессии а0 принимаем. Аналогично проверяем гипотезу о незначимости параметра b1 (при переменной x1) и гипотезу о незначимости всей регрессионной модели (по уровню значимости F).
Проверку значимости коэффициентов уравнения можно сделать по значению t-статистики, которое сравнивается с пороговым значением, зафиксированным в таблице t-статистики. Для коэффициента а0значение t-статистики равно 0,951653, для коэффициента b1 значение 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 уравнения регрессии равен 0,369146 (36,92%), а для коэффициента b1 – 0,138707 (13,87%). Поскольку оба значения ошибки гораздо больше 5%, гипотеза о незначимости коэффициентов уравнения, следовательно и самого уравнения регрессии, принимается. Коэффициент a0 может быть принят лишь с вероятностью (1–0,369146) или 63%, коэффициент b1 – с вероятностью (1-0,138707) или 88%. 3.Оценку тесноты связи делаем по R – квадрат.
R – квадратизмеряет тесноту связи через отношение дисперсий результативного признака. Чем ближе R –квадратк 0, тем слабее связь между x и y, чем ближе R –квадрат с 1, тем сильнее связь.
4.Проверка на значимость всей регрессионной модели, т.е. уравнения регрессии и коэффициента тесноты связи, проводим по F-критерию. Берем F-критериюиз таблицы 2 и сравниваем с пороговым значением из таблицы “Значения F-критерия». Если пороговое значение F-критерия меньше, чем в данном примере (2,704169), проверяемая регрессионная модель значима и наоборот. Пороговое значение определяется в зависимости от выбранного уровня значимости и числа степеней свободы (k1 и k2), рассчитанных по формулам:
k1 = n– 3
k2 = n-m
где k1 и k2- число степеней свободы;
n - число наблюдений;
m – число параметров уравнения регрессии, для парной регрессии равно 2.
Второй вариант проверки – это определение вероятности принятия гипотезы о незначимости регрессионной модели по уровню “Значимость F”, равное в этом примере 0,138707 или 13,87%. Данное значение, говорит о том, что модель незначима.
Определите факторы, оказывающие влияние и обозначьте их (x1,x2,…..xk). Определите результативный признак (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,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 |
| Наблюдения |
| Дисперсионный анализ | |||||
| 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 перечислены коэффициенты, показывающие тесноту связи между признаками попарно. Сравним их друг с другом по абсолютной величине. Особое внимание обратим на межфакторные связи, т.е. на связи между различными 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 соответственно).
Задание к лабораторной работе №3.
1.Построить уравнение парной линейной регрессии.
2.Измерить тесноту связи.
3.Проверить на значимость коэффициенты регрессии и регрессионную модель.
4.Построить уравнение множественной регрессии.
5.Измерить тесноту связи.
6.Проверить на значимость коэффициенты множественной регрессии и регрессионную модель.
7.Улучшить регрессионную модель путем удаления из модели факторов, не имеющих значимого влияния на результат.
8.Построить уравнение парной регрессии нелинейной формы.