Инструменты Excel для решения задачи парной линейной регрессии. Добавление линии тренда

Самый простой способ решения задачи парной линейной регрессии в Excel – добавление к точечной диаграмме линии  тренда.

Задача линейной регрессии — восстановить функциональную  зависимость  по результатам измерений , i =1, 2, …, n., аппроксимировать искомую зависимость функцией  f(x; a₀, a₁, …, a_k), которая линейно зависит от параметров a₀, a₁, …, a_k.

Линией  тренда называется график функции y = f(x; a₀, a₁, …, a_k).

Параметры a₀, a₁, …, a_k  выбираются таким образом, чтобы отклонение значений предложенной функции от результатов эксперимента было минимальным.

Если в качестве меры отклонения выбрана сумма квадратов отклонений, величина

,

то  метод построения оценок называют методом наименьших квадратов.

Для оценки качества аппроксимации чаще всего используют коэффициент детерминации R², о котором известно, что чем ближе его значение к единице, тем лучше выбранная модель.

Процедура Excel – добавление линии  тренда решает именно такую задачу.

Можно построить методом наименьших квадратов аппроксимацию

- линейной функцией ;

- логарифмической ;

- полиномом  не выше 6-й степени;

- степенной функцией

- экспонентой .

В результате можно получить на точечной диаграмме линию тренда (график аппроксимирующей функции), формулу — выражение для функции, значение коэффициента детерминации.

На приведенном ниже рисунке можно видеть решение задачи для различных типов аппроксимирующих функций.