Полиномиальное приближение

Рассмотрим квадратичную модель, в которой функция регрессии представляет собой полином второй степени. Уравнение регрессии квадратичной модели имеет следующий вид.

В качестве независимых переменных в уравнении используются переменные x и x ².

Построить график квадратичной функции регрессии, отражающей зависимость между стоимостью и площадью жилого объекта. 1. Откройте программу Excel. 2. Щелкните на кнопке Сохранить на панели инструментов Стандартная. 3. В появившемся диалоговом окне откройте папку Статистика и 4. задайте имя файлу Нелинейная регрессия.xls. 5. Откройте файл Двумерные данные.xls. 6. Выделите Лист1 и выполните команду Правка→Переместить/скопировать лист… 7. В диалоговом окне из списка в книгу: выберите файл Нелинейная регрессия.xls, 8. в списке перед листом: выберите Лист1, 9. установите флажок Создавать копию и 10. щелкните на кнопке ОК. 11. В файле Нелинейная регрессия.xls удалите Лист1, 12. а имя Лист1(2) замените на имя Лист1. 13. Закройте файл Двумерные данные.xls. 14. На диаграмме рассеяния щелкните правой кнопкой мыши на любой точке данных (Ряд1) и в контекстном меню выберите команду Добавить линию тренда… 15. Появится диалоговое окно (см. рисунок ниже). 16. В диалоговом окне на вкладке Тип щелкните по пиктограмме Полиномиальная. Параметр Степень: должен соответствовать числу 2. 17. Откройте вкладку Параметры и 18. в области Название аппроксимирующей (сглаженной) кривой выберите опцию автоматическое:. 19. Убедитесь, что опция пересечение кривой с осью Y в точке: не отмечена. 20. Включите опции показывать уравнение на диаграмме и 21. поместить на диаграмму величину достоверности аппроксимации (R^2). 22. Щелкните на кнопке ОК. 23. Выделите текст с уравнением регрессии и значением R ², щелкните у его границы и расположите под заголовком диаграммы. Результат приближения квадратичной функцией немного лучше, чем при линейном приближении, т.к. коэффициент детерминации R ², равный 68%, получился больше 66%. Для более точного анализа квадратичной модели получим дополнительные характеристики регрессии, используя инструмент анализа Регрессия. 24. Скопируйте данные с Листа1 (диапазон A1:B16) на Лист2 в такой же диапазон. 25. Выделите столбец B и 26. из контекстного меню выберите команду Добавить ячейки. 27. В ячейку B1 введите метку Площадь^2. 28. Увеличьте ширину столбца B, дважды щелкнув на правой границе его заголовка. 29. Выделите ячейку B2 и введите в нее формулу =A2^2. 30. Скопируйте формулу в остальные ячейки столбца B, выделив ячейку B2 и 31. дважды щелкнув по маркеру заполнения. 32. Выберите команду: Сервис®Анализ данных®Регрессия. 33. В диалоговом окне Регрессия установите параметры как указано ниже на рисунке. 34. Щелкните на кнопке ОК. 35. Выделите диапазон столбцов E:M и увеличьте ширину столбцов, 36. дважды щелкнув по правой границе в строке заголовков столбцов. 37. Удалите часть результатов, относящихся к дисперсионному анализу. 38. Для этого выделите диапазон E10:M14 и 39. выберите из контекстного меню команду Удалить… 40. В диалоговом окне установите опцию ячейки, со сдвигом вверх. 41. Щелкните на кнопке ОК. Интерпретация результатов Полученное уравнение с квадратичной функцией регрессии, имеет вид: . В линейной модели (см. лабораторную работу №6) мы получили стандартную ошибку и нормированный коэффициент детерминации равными $3238 и 0,6377 соответственно. По сравнению с линейной моделью данная квадратичная модель имеет немного большую стандартную ошибку ($3266) и меньшее значение нормированного коэффициента детерминации (0,6315). Исходя из этого, можно сказать, что квадратичная модель не является лучше линейной. В квадратичной модели выполните прогнозирование средней цены для объекта с жилой площадью в 1000 квадратных метров. 1. На Листе2 выделите ячейку A20 и введите в нее значение площади, равное 1000. 2. В ячейку B20 введите формулу = A20^2. 3. В ячейку С20 введите формулу для предсказанной цены = F12 + F13* A20 + F14*B20. 4. Сравните полученную цену с предсказанной ценой в линейной модели. Логарифмическое приближение В логарифмической модели уравнение регрессии имеет следующий вид. . В качестве независимой переменной в уравнении используется . Так как при построении линии тренда Excel проводит логарифмирование, то значения переменной X должны быть положительными. Если же среди значений переменной X имеются нулевые или отрицательные значения, то в диалоговом окне Линия тренда на вкладке Тип пиктограмма Логарифмическая будет выделена серым цветом. Построить график логарифмической функции регрессии, отражающей зависимость между стоимостью и площадью жилого объекта. 1. С Листа1 скопируйте данные вместе с диаграммой на Лист3. 2. Щелкните правой кнопкой мыши на линии тренда и в контекстном меню выберите команду Очистить. 3. На диаграмме рассеяния щелкните правой кнопкой мыши на любой точке данных (Ряд1) и в контекстном меню выберите команду Добавить линию тренда… 4. В диалоговом окне на вкладке Тип щелкните по пиктограмме Логарифмическая. 5. Откройте вкладку Параметры и в области Название аппроксимирующей (сглаженной) кривой выберите опцию автоматическое:. Включите опции показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R^2). Щелкните на кнопке ОК. 6. Текст с уравнением регрессии и значением R ² расположите под заголовком диаграммы. Далее с помощью инструмента анализа Регрессия получим более полные оценки регрессии логарифмической модели. 7. Вставьте в книгу Лист4 и скопируйте в него данные с Листа1 (диапазон A1:B16) в такой же диапазон. 8. Выделите столбец B и из контекстного меню выберите команду Добавить ячейки. 9. В ячейку B1 введите метку Ln(Площадь). Увеличьте ширину столбца B, дважды щелкнув на правой границе его заголовка. 10. Выделите ячейку B2 и введите в нее формулу =LN(A2). В остальные ячейки столбца B скопируйте формулу, выделив ячейку B2 и дважды щелкнув по маркеру заполнения. 11. Выберите команду: Сервис®Анализ данных®Регрессия. В диалоговом окне Регрессия установите следующие параметры. Ü Входной интервал Y: укажите диапазон значений зависимой переменной (C1:C16), включая метку в первой строке. Ü Входной интервал X: укажите диапазон значений независимой переменной (B1:B16), включая метку в первой строке. Ü Метки: включите эту опцию, так как во Входные интервалы X и Y были включены подписи. Ü Параметры вывода: включите Выходной интервал, выберите текстовую строку и введите в нее ссылку на ячейку E1, указывающую левый верхний угол области вывода результатов. Щелкните на кнопке ОК. 12. Выделите диапазон столбцов E:M и увеличьте ширину столбцов, дважды щелкнув по правой границе в строке заголовков столбцов. 13. Удалите часть результатов, относящихся к дисперсионному анализу. Для этого выделите диапазон E10:M14 и выберите из контекстного меню команду Удалить… В диалоговом окне установите опцию ячейки, со сдвигом вверх. Щелкните на кнопке ОК. Интерпретация результатов Полученное уравнение с логарифмической функцией регрессии, имеет вид: . По сравнению с линейной моделью данная логарифмическая модель имеет меньшую стандартную ошибку ($3108<3238) и большее значение нормированного коэффициента детерминации (0,6662>0,6377). Следовательно, логарифмическая модель является несколько лучше линейной. В логарифмической модели выполните прогнозирование средней цены для объекта с жилой площадью в 1000 квадратных метров. 1. На Листе4 выделите ячейку A20 и введите в нее значение площади, равное 1000. 2. В ячейку B20 введите формулу = LN(A20). 3. В ячейку С20 введите формулу для предсказанной цены = F12 + F13*B20. 4. Сравните полученную цену с предсказанной ценой в линейной модели. Степенное приближение В степенной модели уравнение регрессии имеет следующий вид. . При построении линии тренда Excel сначала преобразует степенную модель в линейную. Для этого проводится логарифмирование обеих частей уравнения: . Затем применяется обычная линейная регрессия для зависимой переменной и независимой переменной . В результате Excel определит коэффициент регрессии, соответствующий коэффициенту b степенной модели, и постоянный член . Чтобы получить уравнение регрессии степенной модели, необходимо определить коэффициент a. Для этого выполняется обратное преобразование, т.е. коэффициент a вычисляется по формуле: . Поскольку Excel выполняет логарифмическое преобразование исходных данных X и Y, то, как зависимая переменная Y, так и независимая переменная X должны быть положительными. Если какое-либо из значений X или Y равно нулю или отрицательно, то в диалоговом окне Линия тренда на вкладке Тип пиктограмма Степенная будет выделена серым цветом. Построить график степенной функции регрессии, отражающей зависимость между стоимостью и площадью жилого объекта. 1. Добавьте Лист5. С Листа1 скопируйте данные вместе с диаграммой на Лист5. 2. Щелкните правой кнопкой мыши на линии тренда и в контекстном меню выберите команду Очистить. 3. На диаграмме рассеяния щелкните правой кнопкой мыши на любой точке данных (Ряд1) и в контекстном меню выберите команду Добавить линию тренда… 4. В диалоговом окне на вкладке Тип щелкните по пиктограмме Степенная. 5. Откройте вкладку Параметры и в области Название аппроксимирующей (сглаженной) кривой выберите опцию автоматическое:. Включите опции показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R^2). Щелкните на кнопке ОК. 6. Текст с уравнением регрессии и значением R ² расположите под заголовком диаграммы. Далее с помощью инструмента анализа Регрессия получим более полные оценки регрессии степенной модели. 7. Вставьте в книгу Лист6 и скопируйте в него данные с Листа1 (диапазон A1:B16) в такой же диапазон. 8. В ячейку C1 введите метку Ln(Площадь), а в ячейку D1 введите метку Ln(Цена). Измените ширину столбцов C и D, дважды щелкнув на правой границе в строке заголовков столбцов. 9. Выделите ячейку C2 и введите в нее формулу =LN(A2). 10. Выделите ячейку D2 и введите в нее формулу =LN(B2). 11. Скопируйте формулы в остальные ячейки. Для этого выделите ячейки C2 и D2 и дважды щелкнув по маркеру заполнения ячейки D2. 12. Выберите команду: Сервис®Анализ данных®Регрессия. В диалоговом окне Регрессия установите следующие параметры. Ü Входной интервал Y: укажите диапазон значений зависимой переменной (D1:D16), включая метку в первой строке. Ü Входной интервал X: укажите диапазон значений независимой переменной (C1:C16), включая метку в первой строке. Ü Метки: включите эту опцию, так как во Входные интервалы X и Y были включены подписи. Ü Параметры вывода: включите Выходной интервал, выберите текстовую строку и введите в нее ссылку на ячейку F1, указывающую левый верхний угол области вывода результатов. Щелкните на кнопке ОК. 13. Выделите диапазон столбцов F:N и увеличьте ширину столбцов, дважды щелкнув по правой границе в строке заголовков столбцов. 14. Удалите часть результатов, относящихся к дисперсионному анализу. Для этого выделите диапазон F10:N14 и выберите из контекстного меню команду Удалить… В диалоговом окне установите опцию ячейки, со сдвигом вверх. Щелкните на кнопке ОК. Интерпретация результатов Результаты, полученные с помощью инструмента Регрессия относятся к линейной модели преобразованного уравнения регрессии, в котором зависимой переменной является , а независимой – . Эти результаты нельзя сравнивать с линейной моделью, рассмотренной в лабораторной работе №6, так как стандартная ошибка в этом случае определяется в единицах измерения , а значение нормированного коэффициента детерминации является долей изменений , выраженной через . Для получения уравнения степенной модели необходимо вычислить коэффициент a, выполнив обратное преобразование. На основе результатов, полученных с помощью инструмента анализа Регрессия, вычислите коэффициент a для степенной модели. 1. На Листе6 выделите ячейку G14 и введите формулу =EXP(G12) для вычисления коэффициента a. Укажите, какой вид имеет уравнение регрессии полученной степенной модели. В степенной модели выполните прогнозирование средней цены для объекта с жилой площадью в 1000 квадратных метров. 1. На Листе6 выделите ячейку A20 и введите в нее значение площади, равное 1000. 2. В ячейку B20 введите формулу для предсказанной цены = G14*A20^G13. 3. Сравните полученную цену с предсказанной ценой в линейной модели   Экспоненциальное приближение В экспоненциальной модели уравнение регрессии имеет следующий вид. . При построении линии тренда Excel сначала преобразует экспоненциальную модель в линейную. Для этого проводится логарифмирование обеих частей уравнения: . Затем применяется обычная линейная регрессия для зависимой переменной и независимой переменной х. В результате Excel определит коэффициент регрессии, соответствующий коэффициенту b экспоненциальной модели, и постоянный член . Чтобы получить уравнение регрессии экспоненциальной модели, необходимо определить коэффициент a. Для этого выполняется обратное преобразование, т.е. коэффициент a вычисляется по формуле: . Поскольку Excel выполняет логарифмическое преобразование исходных данных Y, то значения зависимой переменная Y должны быть положительными. Если какое-либо из значений Y равно нулю или отрицательно, то в диалоговом окне Линия тренда на вкладке Тип пиктограмма Экспоненциальная будет выделена серым цветом. Построить график экспоненциальной функции регрессии, отражающей рост продаж компьютеров за период 1987 ¸ 1994 г.г     В таблице 1 представлен временной ряд, определяющий ежегодные продажи компьютеров за период 1987 ¸ 1994 г.г. Таблица 1. Данные продаж за год

Год	Продажи

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

22. Какие типы взаимосвязей существуют между переменными X и Y? Как можно определить взаимосвязь по диаграмме рассеяния?

23. Как определяется форма нелинейной взаимосвязи с помощью графика?

24. Какие характеристики используются при сравнении нелинейной регрессионной модели с линейной регрессией?

25. Как по найденной регрессионной модели осуществляется прогнозирование переменной Y?

26. Какой вид имеет квадратичная модель регрессии? Какие переменные в уравнении используются в качестве независимых?

27. Какой вид имеет логарифмическая модель регрессии? Какая переменная в уравнении регрессии является независимой? Какое ограничение имеют значения переменной X в логарифмической модели?

28. Какой вид имеет степенная модель регрессии? С какой целью в Excel проводится логарифмическое преобразование уравнения регрессии? Что такое обратное преобразование?

29. Какой вид имеет экспоненциальная модель регрессии? Как определяются коэффициенты a и b уравнения регрессии?