Логарифмическое приближение
В логарифмической модели уравнение линии тренда имеет следующий вид:
у = c*Ln(x) + b,
где Ln натуральный логарифм по основанию е (приблизительно 2,718). Так как логарифм определен только для положительных х, то значения независимой переменной должны быть положительными. Если же среди значений х имеются нулевые или отрицательные, то на вкладке тип окна Линии тренда пиктограмма Логарифмическая будет выделена серым. (Чтобы обойти это ограничение, можно добавить какое-либо число ко всем х.) Результаты добавления логарифмической линии тренда на графике стоимости объекта недвижимости приведены на рисунке.
Для получения оценок регрессии при помощи инструмента анализа необходимо добавить к исходным данным столбец со значениями Ln(Площадь) в столбец В.
Входным интервалом Y будет область С1:С16.
Входным интервалом Х – область В1:В16.
На рисунке представлены результаты инструмента регрессии.
По сравнению с линейной моделью, данная логарифмическая модель имеет меньшую стандартную ошибку и большее R2. В соответствии с этим логарифмическая модель несколько лучше линейной.
2.3. Показательное приближение.
В показательной модели линия тренда имеет следующее уравнение:
у = с*хb .
Excel выполняет логарифмическое преобразование исходных данных Х и Y для определения подобранных значений, поэтому как зависимые, так и независимые переменные должны быть положительными. (Чтобы обойти это ограничение, можно добавить какое-либо число ко всем значениям Х и Y.)
При добавлении показательной линии тренда Excel не пытается найти значений b и с, минимизирующих сумму квадратов отклонений фактических у и предсказанных. Вместо этого берется логарифм от обеих частей формулы:
Ln(y) = Ln(c) + b*Ln(x),
а затем применяется обычная линейная регрессия для зависимой переменной Ln(y) и независимой Ln(x). То есть Excel определяет смещение и наклон, минимизирующие сумму квадратов отклонений между фактическими значениями и предсказанными.
Ln(y) = Смещение + Наклон * Ln(х).
Таким образом, значение Смещения соответствует Ln(c), а с в формуле равно Exp(смещение). Значение наклона соответствует b в формуле.
При построении регрессии входной интервал Y: значения Ln(Цена),
Входной интервал Х: значения Ln(Площадь).
На рисунке представлены результаты регрессии.
Значения R-квадрат и Стандартной ошибке нельзя прямо сравнивать со значениями, полученными в линейной модели, так как здесь R-квадрат является долей дисперсии Ln(у), выраженной через Ln(х) в линейной модели, а стандартная ошибка выражается в тех же единицах измерения, что и Ln(у).
Чтобы определить параметр с формулы показательной модели, надо вычислить Ехр(G18).