Интерпретация результатов

Полученное уравнение с логарифмической функцией регрессии, имеет вид:

.

По сравнению с линейной моделью данная логарифмическая модель имеет меньшую стандартную ошибку ($3108<$3238) и большее значение нормированного коэффициента детерминации (0,6662>0,6377). Следовательно, логарифмическая модель является несколько лучше линейной.

Задание 4.

В логарифмической модели выполните прогнозирование средней цены для объекта с жилой площадью в 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 равно нулю или отрицательно, то в диалоговом окне Формат линии тренда на вкладке Параметры линии тренда пиктограмма Степенная будет выделена серым цветом.

Задание 5.

Построить график степенной функции регрессии, отражающей зависимость между стоимостью и площадью жилого объекта.

1. Добавьте Лист5. С Листа1 скопируйте данные вместе с диаграммой рассеяния на Лист5.

2. Щелкните правой кнопкой мыши на линии тренда и в контекстном меню выберите команду Удалить.

3. На диаграмме рассеяния щелкните правой кнопкой мыши на любой точке данных и в контекстном меню выберите команду Добавить линию тренда… Появится диалоговое окно Формат линии тренда.

4. В диалоговом окне на вкладке Параметры линии тренда установите тип Степенная.

5. В области Название аппроксимирующей (сглаженной) кривой выберите опцию автоматическое:.

6. Установите флажки показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R^2). Щелкните на кнопке Закрыть.

7. Выделите текст с уравнением регрессии и значением R² и перетащите на свободное место диаграммы.

Далее с помощью инструмента анализа Регрессия получим более полные оценки регрессии степенной модели.

8. Вставьте в книгу Лист6 и скопируйте в него данные с Листа1 (диапазон A1:B16) в такой же диапазон.

9. В ячейку C1 введите метку Ln(Площадь), а в ячейку D1 введите метку Ln(Цена). Измените ширину столбцов C и D, дважды щелкнув на правой границе в строке заголовков столбцов.

10. Выделите ячейку C2 и введите в нее формулу =LN(A2).

11. Выделите ячейку D2 и введите в нее формулу =LN(B2).

12. Скопируйте формулы в остальные ячейки. Для этого выделите ячейки C2 и D2 и дважды щелкните по маркеру заполнения ячейки D2.

13. На вкладке Данные выберите команду Анализ данныхРегрессия. В диалоговом окне Регрессия установите следующие параметры.

• Входной интервал Y: укажите диапазон значений зависимой переменной (D1:D16), включая метку в первой строке.

• Входной интервал X: укажите диапазон значений независимой переменной (C1:C16), включая метку в первой строке.

• Метки: включите эту опцию, так как во Входные интервалы X и Y были включены подписи.

• Параметры вывода: включите Выходной интервал, щелкните в текстовой строке и введите ссылку на ячейку F1, указывающую левый верхний угол области вывода результатов. Щелкните на кнопке ОК.

14. Выделите диапазон столбцов F:N и увеличьте ширину столбцов, дважды щелкнув по правой границе в строке заголовков столбцов.

15. Удалите часть результатов, относящихся к дисперсионному анализу. Для этого выделите диапазон F10:N14 и выберите из контекстного меню команду Удалить… В диалоговом окне установите опцию ячейки, со сдвигом вверх. Щелкните на кнопке ОК.