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

Ход работы:

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

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

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

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

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. «Вывод итогов»

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. Вывод итогов

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

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

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

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

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

В таблице 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.Построить уравнение парной регрессии нелинейной формы.

• ⇐ Назад
• 1
• 23