Выполнение линейной регрессии с помощью функций Excel и с помощью линии тренда. Модели линейной регрессии с двумя коэффициентами, страница 2

В диалоговом окне Пиния тренда перейдите на вкладку Тип и выберите команду Линейная (рис. 7).

Чтобы закрыть диалоговое окно Линия тренда и добавить к диаграмме линию тренда, нажмите кнопку ОК (рис. 8).

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

Перейдите на вкладку Параметры и установите флажки показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R^{^}2), чтобы включить эти элементы в диаграмму (рис.9).

После нажатия кнопки OK Excel отобразит на диаграмме уравнение регрессии X величину R²(рис. 10).

Как видно из диаграммы, уравнение регрессии имеет вид

у=2,7758х+296,11                                                       (2)

с достоверностью аппроксимации 0,9864. Напомним, что те же результаты были получены с помощью функций НАКПОН(), ОТРЕЗОК() и КВПИРСОН(). Это свидетельствует о том, что прямая, лучше всего описывающая данные, характеризуется угловым коэффициентом b₁ = 2,7758 К/мин (единицы измерения получены с учетом размерностей данных) и точкой пересечения с осью ординат b_Q= 296,1 К. Кроме того, получается, что прямая описывает данные с достоверностью аппроксимации 0,9864. Поскольку идеальной аппроксимации соответствует величина R² = I, можно сделать вывод, что прямая линия не описывает зависимости идеально. Присутствие линии тренда на диаграмме позволяет визуально оценить качество описания. Из предыдущего примера видно, что линия тренда не слишком хорошо ложится на данные. Нужно попытаться подобрать другую кривую регрессии, обладающую некоторой кривизной.

3. Другие модели линейной регрессии с двумя коэффициентами.

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

Все виды регрессии можно выполнить в Excel, построив соответствующие линии тренда. Чтобы выбрать нужный тип регрессии, следует выделить соответствующую команду на вкладке Тип диалогового окна Формат линии тренда (или диалогового окна Линия тренда).

Исходя из характера изгиба естественно предположить экспоненциальную зависимость. Построим аппроксимирующую кривую, для чего дважды щелкнем на существующей линии тренда, чтобы вызвать диалоговое окно Формат линии тренда (рис. 10).

На панели Тип выберите команду Экспоненциальная. Нажмите кнопку ОК, после чего Excel выполнит регрессию с помощью аппроксимирующей экспоненты (рис. 11).

Примечание. Для рассматриваемого набора данных типы Логарифмическая и Степенная недоступны, поскольку в них содержится значение х=0 (т.е. нулевой момент времени). В обоих случаях при выполнении репрессии вычисляется натуральней логарифм х. Поскольку логарифм нуля неопределен, эти уравнения непригодны для нашего набора данных.

Результат аппроксимации данных экспонентой выглядит ненамного лучше предыдущей аппроксимации, и соответствующая ей величина R² почти такая же, какая была получена при использовании уравнения прямой в качестве модели регрессии. На вкладке Тип диалогового окна Формат линии тренда (или диалогового окна Пиния тренда) есть еще один вариант выбора — Полиномиальная.

4. Полиномиальная регрессия

Полиномиальная регрессия тоже линейная, поскольку полиномиальные коэффициенты (b_i) входят в уравнение регрессии линейно. Она отличается от других моделей регрессии тем, что включает в себя больше двух коэффициентов. Формально регрессия с помощью уравнения прямой также является полиномиальной регрессией, однако этот термин обычно применяется для полиномов второй степени или выше. В программе Excel можно построить линии тренда, степень которых находится в пределах от 2 до 6.