Быстрое построение линейной регрессии в Excel: линия тренда

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

1) выделить столбцы с данными X и Y (они должны располагаться именно в таком порядке!);

2) вызвать Мастер диаграмм и выбрать в группе Тип – Точечная и сразу нажать Готово;

3) не сбрасывая выделения с диаграммы, выбрать появившейся пункт основного меню Диаграмма, в котором следует выбрать пункт Добавить линию тренда;

4) в появившемся диалоговом окне Линия тренда во вкладке Тип выбрать Линейная;

5) во вкладке Параметры можно активизировать переключатель Показывать уравнение на диаграмме, что позволит увидеть уравнение линейной регрессии (4.4), в котором будут вычислены коэффициенты (4.5).

6) В этой же вкладке можно активизировать переключатель Поместить на диаграмму величину достоверности аппроксимации (R^2). Эта величина есть квадрат коэффициента корреляции (4.3) и она показывает, насколько хорошо рассчитанное уравнение описывает экспериментальную зависимость. Если R ² близок к единице, то теоретическое уравнение регрессии хорошо описывает экспериментальную зависимость (теория хорошо согласуется с экспериментом), а если R ² близок к нулю, то данное уравнение не пригодно для описания экспериментальной зависимости (теория не согласуется с экспериментом).

В результате выполнения описанных действий получится диаграмма с графиком регрессии и ее уравнением.

§4.3. Основные виды нелинейной регрессии

Параболическая и полиномиальная регрессии.

Параболической зависимостью величины Y от величины Х называется зависимость, выраженная квадратичной функцией (параболой 2-ого порядка):

. (4.8)

Это уравнение называется уравнением параболической регрессии Y на Х. Параметры а, b, с называются коэффициентами параболической регрессии. Вычисление коэффициентов параболической регрессии всегда громоздко, поэтому для расчетов рекомендуется использовать компьютер.

Уравнение (4.8) параболической регрессии является частным случаем более общей регрессии, называемой полиномиальной. Полиномиальной зависимостью величины Y от величины Х называется зависимость, выраженная полиномом n -ого порядка:

, (4.9)

где числа а_i (i =0,1,…, n) называются коэффициентами полиномиальной регрессии.

Степенная регрессия.

Степенной зависимостью величины Y от величины Х называется зависимость вида:

. (4.10)

Это уравнение называется уравнением степенной регрессии Y на Х. Параметры а и b называются коэффициентами степенной регрессии.

Если прологарифмировать обе части уравнения степенной регрессии, то получится уравнение

ln =ln a + b· ln x. (4.11)

Это уравнение описывает прямую на плоскости с логарифмическими координатными осями ln x и ln . Поэтому критерием применимости степенной регрессии служит требование того, чтобы точки логарифмов эмпирических данных ln x_i и ln у_i находились ближе всего к прямой (4.11).

Показательная регрессия.

Показательной (или экспоненциальной) зависимостью величины Y от величины Х называется зависимость вида:

(или ). (4.12)

Это уравнение называется уравнением показательной (или экспоненциальной) регрессии Y на Х. Параметры а (или k) и b называются коэффициентами показательной (или экспоненциальной) регрессии.

Если прологарифмировать обе части уравнения степенной регрессии, то получится уравнение

ln = x· ln a +ln b (или ln = k·x +ln b). (4.13)

Это уравнение описывает линейную зависимость логарифма одной величины ln от другой величины x. Поэтому критерием применимости степенной регрессии служит требование того, чтобы точки эмпирических данных одной величины x_i и логарифмы другой величины ln у_i находились ближе всего к прямой (4.13).

Логарифмическая регрессия.

Логарифмической зависимостью величины Y от величины Х называется зависимость вида:

= a + b· ln x. (4.14)

Это уравнение называется уравнением логарифмической регрессии Y на Х. Параметры а и b называются коэффициентами логарифмической регрессии.

Гиперболическая регрессия.

Гиперболической зависимостью величины Y от величины Х называется зависимость вида:

. (4.15)

Это уравнение называется уравнением гиперболической регрессии Y на Х. Параметры а и b называются коэффициентами гиперболической регрессии и определяются методом наименьших квадратов. Применение этого метода приводит к формулам:

, , (4.16)

где:

, , (4.17)

.

В формулах (4.16-4.17) суммирование проводится по индексу i от единицы до количества наблюдений n.

К сожалению, в Excel нет функции, вычисляющих коэффициенты гиперболической регрессии. В тех случаях, когда заведомо не известно, что измеряемые величины связаны обратной пропорциональностью, рекомендуется вместо уравнения гиперболической регрессии искать уравнение степенной регрессии, так в Excel имеется процедура ее нахождения. Если же между измеряемыми величинами предполагается гиперболическая зависимость, то коэффициенты ее регрессии придется вычислять с помощью вспомогательных расчетных таблиц и операций суммирования по формулам (4.16-4.17).