2.3.2. Логарифмическое приближение

В логарифмической модели уравнение линии тренда имеет следующий вид , где - натуральный логарифм. Так как логарифм определен только для положительных , то значения независимой переменной должны быть положительными. Если же среди значений имеются нулевые или отрицательные, то на вкладке Тип окна Линии тренда пиктограмма Логарифмическая будет выделена серым. Чтобы обойти это ограничение, можно добавить какое-либо число ко всем . Результаты добавления логарифмической линии тренда на график стоимости объектов недвижимости приведены на рис. 2.19.

Рис. 2.19. Логарифмическая линия тренда

Повторяя последовательность шагов, описанную в разделе 2.3.1, получим более полные оценки регрессии в логарифмической модели с помощью инструмента анализа Регрессия. На рис. 2.20 представлены результаты инструмента регрессии после удаления части, относящейся к анализу дисперсии. По сравнению с линейной моделью, рассмотренной в разделе 2.2., данная логарифмическая модель имеет меньшую стандартную ошибку и большее . В соответствии с этим логарифмическая модель несколько лучше линейной.

Рис. 2.20. Результаты применения логарифмической регрессии

Для осуществления прогноза средней стоимости в логарифмической модели введите значение Площади в ячейку (например, А17) и формулу для вычисления логарифма площади в ячейку В17. Затем постройте формулу для предсказанной цены и поместите =F12+F13*B17 в ячейку С17.

2.3.3. Показательное приближение

В показательной модели линия тренда имеет следующее уравнение . MS Excel выполняет логарифмическое преобразование исходных данных и для определения подобранных значений, поэтому как зависимые, так и независимые переменные должны быть положительными. Если же среди значений или имеются нулевые или отрицательные, то на вкладке Тип окна Линии тренда пиктограмма Показательная будет выделена серым. Чтобы обойти это ограничение, можно добавить какое-либо число ко всем и . Результаты добавления показательной линии тренда на график стоимости объектов недвижимости приведены на рис. 2.21.

Рис. 2.21. Линия тренда в показательной модели

При добавлении показательной линии тренда MS Excel не пытается найти значения и , минимизирующих сумму квадратов отклонений фактических и предсказанных. Вместо этого берется логарифм от обеих частей формулы, что может быть записано так , а затем применяется обычная линейная регрессия для зависимой переменной и независимой . То есть MS Excel определяет смещение и наклон, минимизирующие сумму квадратов отклонений между фактическими и предсказанными по формуле .

Таким образом, значение Смещения соответствует , а в формуле равно Exp(смещение). Значение Наклона соответствует в формуле.

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

1. Введите данные в столбцы А и В, как показано на рис. 2.22.

2. Введите метку Ln(Площадь) в ячейку С1. Выделите ячейку С2 и введите формулу =Ln(А2).

3. Введите метку Ln(Цена) в ячейку D1. Выделите ячейку D2 и введите формулу =Ln(B2).

4. Скопируйте формулу в остальные ячейки, выделив C2 и D2 и дважды щелкнув по маркеру заполнения в правом нижнем углу ячейки D2. Значения логарифма появятся в столбцах С и D.

5. В меню Сервис выберите Анализ данных. В диалоговом окне Анализа данных выберите пункт Регрессия и нажмите ОК. Появится диалоговое окно Регрессии.

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

7. Входной интервал : укажите на листе или введите ссылки на область со значениями независимой переменной (С1:С16), включая метку в первой строке.

8. Метки: отметьте этот пункт, так как метки включены во входные интервалы для и .

9. Не выбирайте опции Константа – ноль или Уровень надежности.

10. Параметры вывода: щелкните по кнопке Выходной интервал, выделите строку справа и укажите или введите ссылку на левый верхний угол области шириной в 16 столбцов, куда будут помещены результаты (F1). Можно включить опции для получения Остатков. Затем нажмите ОК.

На рис.2.22 представлены результаты регрессии после удаления части, относящейся к анализу дисперсии. Значения - квадрат и Стандартной ошибки нельзя прямо сравнивать со значениями, полученными в линейной модели, так как здесь - квадрат является долей дисперсии , выраженной через в линейной модели, а Стандартная ошибка выражается в тех же единицах измерения, что и .

Рис. 2.22. Результаты применения показательной регрессии

Чтобы определить параметр формулы показательной модели, выделите ячейку G14 и введите формулу =EXP(G12). Для осуществления прогноза средней стоимости в показательной модели введите значение Площади в ячейку (например, А17). Затем постройте формулу для предсказанной цены (поместите =G14*A17^G13 в ячейку В17).