Интерпретация результатов
Полученное уравнение с квадратичной функцией регрессии, имеет вид:
.
В линейной модели (см. лабораторную работу №6) мы получили стандартную ошибку и нормированный коэффициент детерминации равными $3238 и 0,6377 соответственно. По сравнению с линейной моделью данная квадратичная модель имеет немного большую стандартную ошибку ($3266) и меньшее значение нормированного коэффициента детерминации (0,6315). Исходя из этого, можно сказать, что квадратичная модель не является лучше линейной.
Задание 2. |
В квадратичной модели выполните прогнозирование средней цены для объекта с жилой площадью в 1000 квадратных метров. |
-
На Листе2 выделите ячейку A20 и введите в нее значение площади, равное 1000.
-
В ячейку B20 введите формулу = A20^2.
-
В ячейку С20 введите формулу для предсказанной цены = F12 + F13* A20 + F14*B20.
-
Сравните полученную цену с предсказанной ценой в линейной модели.
Логарифмическое приближение
В логарифмической модели уравнение регрессии имеет следующий вид.
.
В качестве независимой переменной в уравнении используется .
Замечание. Так как при построении линии тренда Excel проводит логарифмирование, то значения переменной X должны быть положительными. Если же среди значений переменной X имеются нулевые или отрицательные значения, то в диалоговом окне Формат линии тренда на вкладке Параметры линии тренда пиктограмма Логарифмическая будет выделена серым цветом.
Задание 3. |
Построить график логарифмической функции регрессии, отражающей зависимость между стоимостью и площадью жилого объекта. |
-
С Листа1 скопируйте данные вместе с диаграммой рассеяния на Лист3.
-
Щелкните правой кнопкой мыши на линии тренда и в контекстном меню выберите команду Удалить.
-
На диаграмме рассеяния щелкните правой кнопкой мыши на любой точке данных и в контекстном меню выберите команду Добавить линию тренда… Появится диалоговое окно Формат линии тренда.
-
В диалоговом окне на вкладке Параметры линии тренда установите тип Логарифмическая.
-
В области Название аппроксимирующей (сглаженной) кривой выберите опцию автоматическое:.
-
Установите флажки показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R^2). Щелкните на кнопке Закрыть.
-
Выделите текст с уравнением регрессии и значением R2 и перетащите на свободное место диаграммы.
Далее с помощью инструмента анализа Регрессия получим более полные оценки регрессии логарифмической модели.
-
Вставьте в книгу Лист4 и скопируйте в него данные с Листа1 (диапазон A1:B16) в такой же диапазон.
-
Выделите столбец B и на вкладке Главная в группе Ячейки выберите команду Вставить→Вставить столбцы на лист.
-
В ячейку B1 введите метку Ln(Площадь). Увеличьте ширину столбца B, дважды щелкнув на правой границе его заголовка.
-
Выделите ячейку B2 и введите в нее формулу =LN(A2). В остальные ячейки столбца B скопируйте формулу, выделив ячейку B2 и дважды щелкнув по маркеру заполнения.
-
На вкладке Данные выберите команду Анализ данныхРегрессия. В диалоговом окне Регрессия установите следующие параметры.
-
Входной интервал Y: укажите диапазон значений зависимой переменной (C1:C16), включая метку в первой строке.
-
Входной интервал X: укажите диапазон значений независимой переменной (B1:B16), включая метку в первой строке.
-
Метки: включите эту опцию, так как во Входные интервалы X и Y были включены подписи.
-
Параметры вывода: включите Выходной интервал, щелкните в текстовой строке и введите в нее ссылку на ячейку E1, указывающую левый верхний угол области вывода результатов. Щелкните на кнопке ОК.
-
Выделите диапазон столбцов E:M и увеличьте ширину столбцов, дважды щелкнув по правой границе в строке заголовков столбцов.
-
Удалите часть результатов, относящихся к дисперсионному анализу. Для этого выделите диапазон E10:M14 и выберите из контекстного меню команду Удалить… В диалоговом окне установите опцию ячейки, со сдвигом вверх. Щелкните на кнопке ОК.