Функции для определения адекватности построенных моделей

Оценить адекватность построенной модели на основании коэффициента корреляции, критерия Фишера и критерия Стьюдента можно с помощью функций:

• КОРРЕЛ(массив1; массив2) – рассчитывает коэффициент корреляции R, который должен находиться в пределах от -1 до +1. Положительное значение свидетельствует о прямой связи, отрицательное – об обратной. Чем ближе его абсолютное значение к 1, тем теснее связь. Связь считается достаточно сильной, если коэффициент корреляции по абсолютной величине превышает 0.7; и слабой, если меньше 0.4. Адекватность модели можно оценить помощью коэффициента детерминированности R², который равен квадрату коэффициента корреляции и показывает, насколько точно уравнение, полученное с помощью регрессионного анализа, объясняет взаимосвязи между переменными.
• FРАСПРОБР(вероятность; степени_свободы1; степени_свободы2) -эта функция вычисляет критерий Фишера: F-статистику, используемую для определения того, является ли наблюдаемая взаимосвязь между зависимосй и независимой переменными случайной или нет;

- вероятность – это вероятность, связанная с F-распределением;

- степени_свободы1 – это числитель степеней свободы;

- степени_свободы2 – это знаменатель степеней свободы.

• СТЬЮДРАСПОБР(вероятность; степени_свободы) – это функция вычисляет t-статистику Стьюдента, позволяющую оценить значимость коэффициентов уравнения регрессии;

- вероятность – вероятность, соответствующая двустороннему распределению Стьюдента;

- степени_свободы – число степеней свободы, характеризующее распределение.

ПРАКТИЧЕСКАЯ ЧАСТЬ

Зависимость между факторами X₁, X₂,X₃ и признаком Y характеризуется данными:

Задание. По выборочным данным исследовать влияние факторов X₁, X₂, X₃ на результативный признак Y. Для этого:

1. Построив корреляционное поле, сделать предположение о наличии и типе связи между исследуемыми факторами
2. Для каждого фактора построить линейную и экспоненциальную регрессионные модели вида Y=f(X)
3. Оценить:

• Адекватность уравнения регрессии по значению коэффициента детерминированности R²
• Значимость коэффициентов уравнения регрессии по t-критерию Стьюдента при заданном уровне доверительности p=0.05;
• Степень случайности связи между каждым фактором X и признаком Y (критерий Фишера)
• На основании выполненной оценки по каждому фактору их двух уравнений регрессии выбрать наилучшее

1. Повторить расчеты с использованием надстройки «Пакет анализа»

Решение.

1. Построение корреляционного поля.

Для этого разместим таблицу с исходными данными в ячейках A3:D15 рабочего листа Excel, как показано в Приложении 1.1.

Используя возможности мастера диаграмм MS Excel, построим корреляционное поле, т.е. представим графически связь между результирующим признаком и каждым их факторов (см. Приложение 1.1-1.2). Из графиков видно, что между результирующим признаком Y и каждым из факторов X существует прямо пропорциональная зависимость, приближающаяся к линейной. Далее исследуем тесноту характер связи между факторами

2. Построение матрицы коэффициентов парной корреляции

Используя надстройку «Пакет анализа» (Сервис->Анализ данных ->Корреляция), посторим матрицу коэффициентов парной корреляции (ячейки Q6:U10). Окно инструмента «Корреляция» представлено на рис.1. Матрица полученных коэффициентов парной корреляции представлена на рис.2.

Рис.1. Окно «Корреляция»	Рис.2. Матрица коэффициентов парной корреляции

Из матрицы видно, что все рассматриваемые факторы X1-X3 имеют тесную связь с результативным признаком Y. Кроме того, все факторы X между собой мультиколлинеарны. Поэтому построение многофакторной модели вида Y=f(X1,X2,X3) невозможно.

3. Построение однофакторной регрессионной модели вида Y=f(X1).

Для построения модели линейного вида Y=m×X+b воспользуемся функцией ЛИНЕЙН из категории статистических функций. В ячейки N20:O24 введем как формулу массива функцию ЛИНЕЙН в формате ЛИНЕЙН(A4:a14;B4:B14;1;1) (см. Приложение 1.1). При вводе следует одновременно нажать клавиши <CTRL>, <SHIFT>, <ENTER> В результате получим массив значений, верхняя строка которого представляет собой коэффициенты уравнения регрессии m и b.

Таким образом, уравнение регрессии, устанавливающее зависимость межу X1 и Y имеет вид:

Y(X1) = 2.5017x1 - 0.9085

• Коэффициент детерминированности R² =0.846 (ячейка N22), т.е. почти 85% вариации признака Y определяется изменением фактора X1.
• Значимость коэффициентов уравнения регрессии определяется по t-критерию Стьюдента. Расчетное значение критерия Стьюдента t_p=7.049 (ячейка O27, формула N20/N21), что больше табличного t_Т=2.26 (функция =СТЬЮДРАСПОБР(0,05;О23)), т.е. коэффициент при переменной X1 значим.
• Расчетное значение критерия Фишера Fp=49,68 (ячейка N23) больше табличного F_Т=5,117 (ячейка О12, формула -FРАСПОБР(0,05;1;О23)=5,117). Т.е. связь между факторами не случайна и в целом уравнение регрессии адекватно.

Для построения экспоненциальной модели вида Y= b m×^X используют функцию ЛГРФПРИБЛ (см. Приложение 1.1) и в ячейках K20:L24 по описанной выше методике рассчитывают параметры экспоненциальной регрессионной модели. В результате уравнение регрессии имеет вид:

Y(X1)э=8.013*1.11^x1

В этой модели коэффициент детерминированности R² =0.735 (ячейка K22), т.е. приблизительно 74% вариации результативного признака Y определяется изменением фактора X1.

Критерий Фишера Fp=25.01 ячейка K23) больше табличного Fт=5.12. Т.е. связь между факторами не случайна и в целом уравнение регрессии адекватно.

Аналогичным образом рассчитаем и оценим адекватность уравнения регрессии вида Y=f(X2) (ячейки N38:O42 и K38:L42) и вида Y=f(X3) (ячейки N55:O59 и K55:L59).

• Для расчета прогнозных значений Y в случае линейной однофакторной модели можно использовать функцию ПРЕДСКАЗ (если значение X одно) или ТЕНДЕНЦИЯ (для массива исходных значений X).
• В случае экспоненциальной зависимости для расчета новых значений используют функцию РОСТ.

4. Построение линейной однофакторной регрессионной модели Y=f(X1) средствами надстройки «Пакет анализа»

Используя надстройку «Пакет анализа» (Сервис-Анализ данных-Регрессия), рассчитаем линейную регрессионную модель вида Y=f(X1).

Рис.3. Окно «Регрессия»

Результаты регрессионного анализа (ячейки Q16:Y33) представлены в виде трех таблиц.

Первая таблица – «Регрессионная статистика» (ячейки Q18:R23) позволяет оценить тесноту связи между факторами и уровень стандартной ошибки.

Вторая таблица – «Дисперсионный анализ» на основании критерия Фишера, остаточной и регрессионной суммы квадратов позволяет оценить адекватность уравнения регрессии в целом.

В третьей таблице представлены значения коэффициентов уравнения регрессии (ячейки R32:R33), критерий Стьюдента (ячейки T32:T33) и уровень значимости p (ячейки U32:U33)

Результаты расчета, проведенного с помощью надстройки «Пакет анализа», полностью совпадают с результатами для функции ЛИНЕЙН.

Аналогичный регрессионный анализ можно провести для линейных моделей вида Y=f(X2) и Y=f(X3).

Вывод: все построенный модели отвечают условиям адекватности. Наиболее высокие статистические характеристики имеет модель Y=f(X3) вида

Y=6.907 * 1.0545^X, в которой

коэффициент детерминированности R² =0.983;

F=532.37 (F_p=532.37>F_T=5.117)

Критерий Стьюдента 14.95 (t_p=14.95>t_T=2.26) – коэффициенты уравнения регрессии значимы.

Полное решение задачи приведено в приложениях ниже