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

Шаг 5. Размещение результатов регрессионного анализа

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

Шаг 6. Отображение или отмена отображения результатов в графическом виде

Для визуального контроля точности описания данных линией регрессии всегда полезно иметь перед глазами график остатков и диаграмму с изображением аппроксимирующей кривой. Чтобы отобразить указанные графики, установите в диалоговом окне Регрессия флажки График остатков и График подбора (рис. 5.22).

Шаг 7. Выполнение регрессии

Чтобы выполнить регрессию, щелкните на кнопке ОК. Результаты будут представлены в виде таблиц и графиков (если графики запрошены). Часть вывода показана на рис. 5.23.

Из листа итогов видно, что линия, наилучшим образом описывающая данные, характеризуется угловым коэффициентом b₁= 2,775 К/мин (вывод о размерности этого коэффициента можно сделать исходя из размерностей данных) и точкой пересечения с осью ординат b_о= 296,1 К. Величина R² равна 0,9864. Эти результаты совпадают с теми, которые были получены другими методами.

Графики позволяют визуально убедиться, насколько хорошо аппроксимирующая линия описывает данные. Программа Excel создает и размещает на странице с итогами график подбора (рис. 5.24).

Он почти такой же, как и график, который создается с помощью линии тренда. Плохая аппроксимация еще лучше видна на графике остатков (рис. 5.25).

Остаток (residual) — это разность между значениями у, соответствующими данным, и значениями у, через которые проходит линия регрессии при каждом значении х. График остатков (residual plot) позволяет подчеркнуть плохие совпадения и облегчает их выявление. В нашем случае маркеры данных на графике остатков образуют букву "U". Это свидетельствует о том, что выбранная для описания данных модель (прямая линия) не учитывает определенной закономерности. Напрашивается вывод, что следует поискать более подходящую модель регрессии.

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

5.2. Выполнение полиномиальной регрессии с помощью пакета анализа Регрессия

Наилучшая аппроксимация зависимости температуры от времени была получена с помощью полинома второго порядка:

y_p = b₀ + b₁x + b₂x²(5.3)

или, применяя обозначения температуры Т и времени t,

T_p=b₀+b₁t+b₂t²(5.4)

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

Пакет регрессионного анализа в Excel позволяет использовать только один столбец значений у (в котором содержатся значения зависимой переменной), но при этом можно указывать несколько столбцов со значениями х (содержащих значения независимой переменной). Это могут быть полностью независимые друг от друга переменные (например, энтальпия (зависимая переменная) как функция температуры (одна из независимых переменных) и давления (вторая независимая переменная)). Кроме того, в различные столбцы можно ввести одну и ту же переменную в разных формах (например, х и х² или z и ln(z)).

Таким образом, используя в качестве модели регрессии полином второго порядка, следует ввести два столбца с независимыми переменными, содержащих значения t и t². В таблице, приведенной на рис. 5.26, значения зависимой переменной Т перенесены в столбец А, чтобы значения tut²можно было поместить в соседние столбцы В и С.

Процедура регрессии этих данных почти совпадает с той, которая была использована в предыдущем примере, за исключением шага 4, где программе Excel нужно указать столбцы со значениями х. В таком случае следует выделить оба столбца В и С (рис. 5.27).

В остальном процесс регрессионного анализа остается неизменным. Результаты регрессии выводятся на лист с итогами (как и в предыдущем случае, на рис. 5.28 приводится лишь часть результатов).