Рассмотрим квадратичную модель, в которой функция регрессии представляет собой полином второй степени. Уравнение регрессии квадратичной модели имеет следующий вид.
В качестве независимых переменных в уравнении используются переменные x и x 2.
Построить график квадратичной функции регрессии, отражающей зависимость между стоимостью и площадью жилого объекта.
| 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 2, щелкните у его границы и расположите под заголовком диаграммы.
Результат приближения квадратичной функцией немного лучше, чем при линейном приближении, т.к. коэффициент детерминации R 2, равный 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 2 расположите под заголовком диаграммы.
Далее с помощью инструмента анализа Регрессия получим более полные оценки регрессии логарифмической модели.
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 2 расположите под заголовком диаграммы.
Далее с помощью инструмента анализа Регрессия получим более полные оценки регрессии степенной модели.
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 уравнения регрессии?