В степенной модели уравнение регрессии имеет следующий вид.
.
При построении линии тренда Excel сначала преобразует степенную модель в линейную. Для этого проводится логарифмирование обеих частей уравнения:
.
Затем применяется обычная линейная регрессия для зависимой переменной и независимой переменной . В результате Excel определит коэффициент регрессии, соответствующий коэффициенту b степенной модели, и постоянный член . Чтобы получить уравнение регрессии степенной модели, необходимо определить коэффициент a. Для этого выполняется обратное преобразование, т.е. коэффициент a вычисляется по формуле: .
Поскольку Excel выполняет логарифмическое преобразование исходных данных X и Y, то, как зависимая переменная Y, так и независимая переменная X должны быть положительными. Если какое-либо из значений X или Y равно нулю или отрицательно, то в диалоговом окне Линия тренда на вкладке Тип пиктограмма Степенная будет выделена серым цветом.
Задание 5. | Построить график степенной функции регрессии, отражающей зависимость между стоимостью и площадью жилого объекта. |
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 и выберите из контекстного меню команду Удалить… В диалоговом окне установите опцию ячейки, со сдвигом вверх. Щелкните на кнопке ОК.