Выполнение работы в MS Excel

Порядок выполнения работы рассмотрим на примере построения линейной регрессионной зависимости расходов на жилье (Y, млрд. дол.) от располагаемого личного дохода (X, млрд. дол.) и индекса реальных цен (Р) относительно 1972 г. по данным США за 1959-1978 г. Эти данные приведены на рис 14.

Ввод данных. В ячейках A1-А21 расположим имя фактора Х (располагаемый личный доход) и его значения, в ячейках B1-В21 имя фактора Р (индекса реальных цен) и его значения, в ячейках C1-С21 имя зависимой переменной Y (расходы на жилье) и его значения.

Рис.14. Множественная регрессия

Построение корреляционной матрицы. Следуя работе №1, построим корреляционную матрицу для величин X, P, Y. Расположим ее в ячейках G5-I7, см. рис.14. Парные коэффициенты корреляции Выполнение работы в MS Excel - student2.ru , Выполнение работы в MS Excel - student2.ru , Выполнение работы в MS Excel - student2.ru говорят о тесной парной линейной корреляционной зависимости рассматриваемых величин.

Оценка уравнения множественной линейной регрессии Выполнение работы в MS Excel - student2.ru . Откроем вкладку «Данные», в группе «Анализ» выберем надстройку «Анализ данных». В открывшемся окне «Инструменты анализа» выберем функцию «Регрессия». В появившемся окне "Регрессия" укажем входные данные для оценки параметров уравнения регрессии, выводимые результаты и их расположение. Заполнение окна "Регрессия" для рассматриваемого примера приведено на рис. 15. В части "Входные данные" в поле ввода "Входной интервал Y" указываем диапазон ячеек, содержащий значения зависимой переменной, в нашем примере это С1:С21. В поле ввода "Входной интервал X" – диапазон ячеек, содержащий значения независимых переменных, в примере это A1:В21. Значения объясняющих переменных должны располагаться в последовательных столбцах. В поле "Метки" устанавливаем флажок Выполнение работы в MS Excel - student2.ru , он указывает на то, что первые строки диапазонов данных содержат имена этих данных (заголовки). В "Константа-ноль" флажок не устанавливаем. При установке флажка Выполнение работы в MS Excel - student2.ru в левом поле "Уровень надежности", наряду с используемым по умолчанию стандартным уровнем надежности 95% ( Выполнение работы в MS Excel - student2.ru ), можно задать и другое его значение, в этом случае будут выведены интервальные оценки параметров регрессии для двух уровней надежности.

В части "Параметры вывода" выбираем "Выходной интервал" – для помещения результатов на текущем рабочем листе, положение результатов на листе указываем заданием верхней левой ячейки, начиная с которой располагаются результаты, в нашем примере выбрана ячейка А25. Далее, выставляя флажки, указываем какую дополнительную информацию, предлагаемую функцией "Регрессия", мы хотим иметь в результатах:

· "Остатки" – для выдачи прогнозов Выполнение работы в MS Excel - student2.ru и остатков регрессии Выполнение работы в MS Excel - student2.ru ;

· "График остатков" – для вывода точечной диаграммы остатков Выполнение работы в MS Excel - student2.ru ;

· "График подбора" – для вывода наложенных на диаграмму рассеяния точек линии регрессии. По ОКполучаем результаты регрессии, которые включают в себя таблицу регрессионной статистики, таблицу дисперсионного анализа, таблицу коэффициентов регрессии, таблицу остатков и графики остатков и подбора. Результаты регрессии приведены на рис.16-17. Пояснения к выводимым результатам см. в работе № 3.

Выполнение работы в MS Excel - student2.ru

Рис. 15. Заполнение окна «Регрессия»

Выполнение работы в MS Excel - student2.ru

Рис. 16. Итоги выполнения «Регрессии»

Из таблицы коэффициентов регрессии имеем следующие МНК-оценки параметров уравнения регрессии Выполнение работы в MS Excel - student2.ru , Выполнение работы в MS Excel - student2.ru , Выполнение работы в MS Excel - student2.ru . Их стандартные ошибки равны Выполнение работы в MS Excel - student2.ru . 95%-е доверительные интервалы коэффициентов регрессии: Выполнение работы в MS Excel - student2.ru ; Выполнение работы в MS Excel - student2.ru ; Выполнение работы в MS Excel - student2.ru . В таблице «ВЫВОД ИТОГОВ» величина «Стандартная ошибка» является оценкой стандартного отклонения Выполнение работы в MS Excel - student2.ru зависимой переменной (ошибки регрессии), т.е. Выполнение работы в MS Excel - student2.ru .

Построенное уравнение регрессии: Выполнение работы в MS Excel - student2.ru .

Выполнение работы в MS Excel - student2.ru

Рис. 17. Графики остатков и подбора множественной регрессии

Графики остатков и подбора в множественной регрессии в MS Excel выдаются отдельно по каждому фактору. Приведенные на рис. 17 графики остатков регрессии имеют колебательный характер, а графики подбора говорят о хорошем качестве подгонки построенной модели к наблюдаемым данным.

Верификация модели. Значения t-статистик для коэффициентов уравнения регрессии соответственно Выполнение работы в MS Excel - student2.ru превышают по модулю критическое значение Выполнение работы в MS Excel - student2.ru . Для вычисления Выполнение работы в MS Excel - student2.ru при n=20, р=3 и Выполнение работы в MS Excel - student2.ru выделим ячейку G45, в вкладке «Формулы» выберем «Другие функции», в группе «Статистические» выберем функцию «СТЬЮДЕНТ.ОБР.2Х». В окне этой функции в поле «Вероятность» введем значение Выполнение работы в MS Excel - student2.ru , равное 0,05, в поле «Степени свободы» зададим число степеней свободы n-р, равное 17. По «ОК» в ячейке G45получим значение Выполнение работы в MS Excel - student2.ru , в рассматриваемом примере оно равно 2,1098 (см. рис. 16). Следовательно, при уровне значимости Выполнение работы в MS Excel - student2.ru коэффициенты Выполнение работы в MS Excel - student2.ru уравнения регрессии значимо отличаются от нуля. О значимом влиянии располагаемого личного дохода X и индекса реальных цен Р на расходы на жилье Y говорят также р-значения, которые меньше заданного уровня значимости Выполнение работы в MS Excel - student2.ru , а также доверительные интервалы для коэффициентов уравнения регрессии, которые не содержат нуля.

Большие значения скорректированного коэффициента детерминации Выполнение работы в MS Excel - student2.ru и F-статистики, Выполнение работы в MS Excel - student2.ru , говорят о статистической значимости построенного уравнения регрессии и хорошем качестве подгонки модели к выборочным данным. 98,99% вариации зависимой переменной объясняется вариацией объясняющих переменных.

Построение точечного и интервального прогнозы среднего зависимой переменной. Построение прогноза среднего зависимой переменной для значений факторов равных их выборочным средним, т.е. для Выполнение работы в MS Excel - student2.ru . Для нахождения выборочных средних факторов и зависимой переменной последовательно выделяя, например, ячейки А23, В23, С23 и вводя соответственно в строке формул =СРЗНАЧ(А2:А21),=СРЗНАЧ(В2:В21),=СРЗНАЧ(С2:С21), получим значения выборочных средних Выполнение работы в MS Excel - student2.ru , Выполнение работы в MS Excel - student2.ru , Выполнение работы в MS Excel - student2.ru . Для вычисления прогноза среднего Выполнение работы в MS Excel - student2.ru при заданных значениях факторов выделим, например, ячейку Н102 и, учитывая расположение значений факторов и коэффициентов уравнения регрессии, в строке формул введем =В41+В42*А23+В43*В23. По ОК в Н102получим искомое значение Выполнение работы в MS Excel - student2.ru , совпадающее с выборочным средним Выполнение работы в MS Excel - student2.ru , см. рис. 18.

Построение интервальной оценки, надежности Выполнение работы в MS Excel - student2.ru , среднего зависимой величины. Доверительный интервал надежности Выполнение работы в MS Excel - student2.ru для среднего Выполнение работы в MS Excel - student2.ru зависимой величины при заданном векторе значений факторов Выполнение работы в MS Excel - student2.ru определяется неравенством

Выполнение работы в MS Excel - student2.ru

где Выполнение работы в MS Excel - student2.ru – квантиль уровня Выполнение работы в MS Excel - student2.ru распределения Стьюдента с числом степеней свободы Выполнение работы в MS Excel - student2.ru . Оценка дисперсии Выполнение работы в MS Excel - student2.ru для заданного вектора значений факторов Выполнение работы в MS Excel - student2.ru определяется как Выполнение работы в MS Excel - student2.ru . Предварительно вычислим оценку дисперсии Выполнение работы в MS Excel - student2.ru (см. рис. 18).

Для этого в ячейках А100-С119 создадим матрицу X, первый столбец которой состоит из единиц, второй – из значений фактора Х, третий – из значений фактора Р. В ячейках В123-U125разместим транспонированную матрицу Выполнение работы в MS Excel - student2.ru . В ячейках F98-H98 расположим вектор ( Выполнение работы в MS Excel - student2.ru )=(1; 713,035; 100,405) значений факторов для которых вычисляется интервальный прогноз. В ячейках K98-K100 расположим транспонированный вектор Выполнение работы в MS Excel - student2.ru . Выделим ячейку Н105 и, учитывая расположение величины s в ячейке В31, в строке формул введем

=B31^2*(1+МУМНОЖ(МУМНОЖ(F98:H98;МОБР(МУМНОЖ(B123:U125;A100:C119)));K98:K100)).

Выполнение работы в MS Excel - student2.ru

Рис. 18 Построение точечного и интервального прогноза среднего Выполнение работы в MS Excel - student2.ru

По Enter в этой ячейке получим искомое значение оценки дисперсии среднего, равное 7,734. Выделим под нижнюю границу доверительного интервала ячейку К108и в строке формул введем

=H102-СТЬЮДЕНТ.ОБР(0,975;17)*КОРЕНЬ(H105)

По Enter в ячейке К108 получим значение нижней границы доверительного интервала, равное 93, 0426. Аналогично, выделив ячейку N108 и введя в строке формул

=H102+СТЬЮДЕНТ.ОБР(0,975;17)*КОРЕНЬ(H105),

Получим в ней значение верхней границы доверительного интервала, равное 104,777.

Таким образом, доверительный интервал надежности Выполнение работы в MS Excel - student2.ru среднего зависимой величины для значений факторов Выполнение работы в MS Excel - student2.ru и p Выполнение работы в MS Excel - student2.ru задается неравенством

Выполнение работы в MS Excel - student2.ru .

Общее заключение об оцененной модели и ее интерпретация.

Построенная модель Выполнение работы в MS Excel - student2.ru хорошо согласуется с имеющейся выборкой. Об этом свидетельствует высокое значение нормированного коэффициента детерминации Выполнение работы в MS Excel - student2.ru , т.е. 98,99% вариации Y относительной ее средней объясняется изменениями X и P. Большое значение Выполнение работы в MS Excel - student2.ru -статистики, Выполнение работы в MS Excel - student2.ru , и ее уровень значимости, равный Выполнение работы в MS Excel - student2.ru , свидетельствует о наличии значимой линейной корреляционной зависимости Y от X и P. Оценка Выполнение работы в MS Excel - student2.ru среднеквадратического отклонения Выполнение работы в MS Excel - student2.ru ошибок регрессии Выполнение работы в MS Excel - student2.ru мала по сравнению с Выполнение работы в MS Excel - student2.ru , что также свидетельствует о малом разбросе выборочных данных относительно линии регрессии. Значения t-статистик коэффициентов Выполнение работы в MS Excel - student2.ru уравнения регрессии и их p-значения, равные соответственно 0,0197, Выполнение работы в MS Excel - student2.ru и 0,00091 говорят об их значимом отличии от нуля. Следовательно, располагаемый личный доход и индекс цен значимо влияют на расходы на жилье и построенная регрессионная модель статистически значима.

Интерпретация построенной модели Выполнение работы в MS Excel - student2.ru . Коэффициент при индексе цен p имеет отрицательный знак, что согласуется с теоретическим положением о снижении спроса на жилье с ростом цены. Коэффициент при величине располагаемых доходов x положительный, что согласуется с положением о росте спроса с ростом доходов. Значения коэффициентов при x и p говорит о возрастании расходов на жилье в среднем на 0,13388 млрд. дол при росте располагаемых доходов на 1млрд. дол, расходы на жилье сокращаются на 1,31194 млрд. дол при росте индекса цен на 1%.

Выборочный коэффициент корреляции располагаемого личного дохода Х и индекса цен Р близок по модулю к единице, Выполнение работы в MS Excel - student2.ru , что говорит о сильной коррелированности рассматриваемых факторов. Что говорит о необходимости проведения дополнительных исследований на мультиколлинеарность. Кроме того, графики остатков имеют определенную закономерность, некоторую колебательность, что может говорить о наличии автокорреляции остатков. Поэтому необходимы дополнительные исследования на автокорреляцию, что также связано с тем, что выборочные данные представляют временные ряды.

Контрольные вопросы.

1. В чем заключается спецификация модели множественной регрессии?

2. Как находятся оценки параметров линейной множественной регрессии?

3. Может ли быть линейная множественная регрессия быть нелинейной по объясняющим переменным?

4. Сформулируйте критерии значимости параметров множественной регрессии.

5. Приведите предпосылки линейной множественной регрессии.

6. Сформулируйте Теорему Гаусса-Маркова.

7. С помощью каких критериев проверяется значимость линейного уравнения множественной регрессии?

8. В чем отличие ошибок регрессии от остатков регрессии?

9. Что характеризует скорректированный коэффициент детерминации?

10. Как определяется средняя ошибка аппроксимации, что она характеризует?

11. Как интерпретируются коэффициенты линейной множественной регрессии?

12. Что характеризует частный коэффициент эластичности для линейной множественной регрессии?

13. В чем заключается прогноз значений зависимой переменной? Как определяется дисперсия прогноза?

14. Как строится интервальный прогноз среднего зависимой переменной?

15. С увеличением надежности интервального прогноза он увеличивается или уменьшается?

Наши рекомендации