Вывод уравнения регрессии.

Лабораторная работа 8

Построение уравнения регрессии

ЗАДАНИЕ 1

С помощью MS Excel провести автоматический анализ тренда на основе диаграммы экспериментальных данных Х и У (в конце лабораторной работы).

В MS Excel предлагается выбрать тренд из пяти типов аппроксимирующих линий.

Порядок выполнения задания:

В MS Excel открыть новую книгу и на первом листе ввести данные (они в конце лабораторной работы) для X и Y (рис. 1.).

Построить диаграмму данных в виде точечного графика.

Активизировать диаграмму и выполнить команду Диаграмма | Добавить линию тренда … | окно Линия тренда | вкладка Параметры (флаг − показать уравнение на диаграмме; флаг − поместить на диаграмму величину достоверности аппроксимации ( R ^2)).

Построить точечные графики для пяти видов зависимостей. Анализируя  изменение коэффициента детерминации (R²)  подобрать ту линию регрессии, при которой R² будет максимальным. Обратить внимание на вид уравнения регрессии.

Рис. 1.

ЗАДАНИЕ 2

С помощью MS Excel провести регрессионный анализ данных своего варианта. Для чего:

1. провести расчет простого уравнения линейной регрессии;

2. проверить адекватность уравнения регрессии (модели) исходным данным;

3. проверить достоверность коэффициентов модели;

4. провести анализ остатков;

5. применить разработанную модель для прогнозирования.

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

Примерный вид модели изображен на рис. 2, 3, 4.

Рис. 2.

Рис. 3.

Рис. 4.

Формулы, используемые для построения линейной регрессионной модели

Вывод уравнения регрессии.

Х − независимая переменная,

Y − зависимая переменная,

k − количество определяемых коэффициентов уравнения,

n − =СЧЕТ(Х) − количество элементов в выборке,

МХ − =СРЗНАЧ(Х) − среднее арифметическое переменной Х,

М Y − =СРЗНАЧ(Y) − среднее арифметическое переменной Y,

а − =ОТРЕЗОК(Y;X) − коэффициент а,

b − =НАКЛОН(Y;X) − коэффициент b,

Y ^ = a + b * X − уравнение регрессии,

SS 1 − =СУММ((Y^ − MY)²) − общая сумма квадратов регрессии,

SS 2 − =СУММ((Y − Y^)²) − сумма квадратов остатков регрессии,

R ² = SS1 / (SS1 + SS2) − коэффициент детерминации,

Y − Y^ − остатки.