Экономико-математическая модель

Расчет  параметров линейной регрессии с  использованием инструмента Регрессия надстройки Пакет анализа:

Для проведения регрессионного анализа выберем пункт меню Сервис/Анализ данных/Регрессия. Откроется следующее диалоговое окно:

 

После заполнения полей  ввода нажимаем кнопку OK и получаем следующие результаты:

Регрессионная статистика
Множественный R	0,879
R-квадрат	0,773
Нормированный R-квадрат	0,688
Стандартная ошибка	0,248
Наблюдения	12

 

 

Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия	3	1,681	0,560	9,093	0,006
Остаток	8	0,493	0,062
Итого	11	2,174

 

	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение	Нижние 95%	Верхние 95%	Нижние 95,0%	Верхние 95,0%
Y-пересечение	0,999	2,550	0,392	0,705	-4,882	6,880	-4,882	6,880
X1	-20,248	22,254	-0,910	0,389	-71,567	31,070	-71,567	31,070
X2	13,718	22,928	0,598	0,566	-39,154	66,591	-39,154	66,591
X3	0,071	0,072	0,980	0,356	-0,096	0,237	-0,096	0,237

 

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

Расчет  параметров экспоненциальной регрессии с использованием функции ЛГРФПРИБЛ:

Для экспоненциальной аппроксимации  в Excel существует функция ЛГРФПРИБЛ(изв. зн. Y, изв. зн. X, константа, статистика) она возвращает массив значений описывающих кривую вида:

изв. зн. Y – это известные значения функции

изв. зн. X – это известные значения аргументов

константа – определяет чему должно равняться b, если константа имеет значение ЛОЖЬ то b полагается равным 1, иначе b вычисляется обычным образом.

статистика – если значение равно ИСТИНА то будет представлена дополнительная регрессионная статистика, если ЛОЖЬ то нет.

Экспоненциальная  зависимость
1,016	32,182	0,011	2,051
0,016	5,161	5,009	0,574
0,781	0,056	#Н/Д	#Н/Д
9,522	8,000	#Н/Д	#Н/Д
0,089	0,025	#Н/Д	#Н/Д

Полученные числа имеют следующий смысл:

mn	mn-1	…	b
Sen	Sen-1	…	Seb
R2	Sey
F	Df
Ssreg	Ssresid

 

S_e – стандартная ошибка для коэффициента m

S_eb – стандартная ошибка для свободного члена b

R² – коэффициент детерминированности, который показывает как близко уравнение описывает исходные данные. Чем ближе он к 1, тем больше сходится теоретическая зависимость и экспериментальные данные.

S_ey – стандартная ошибка для y

F – критерий Фишера определяет случайная или нет взаимосвязь между зависимой и независимой переменными

Df – степень свободы системы

Ssreg – регрессионная сумма квадратов

Ssresid – остаточная сумма квадратов

 

Аналогичным образом  построим экспоненциальную регрессионную  зависимость при аргументе Константа равном 0:

Экспоненциальная  модель
1,037	0,080	0,000	1,000
0,001	1,979	0,723	#Н/Д
0,999	0,058	#Н/Д	#Н/Д
2485,379	9,000	#Н/Д	#Н/Д
24,746	0,030	#Н/Д	#Н/Д

 

Определение модели наиболее точно  описывающей фактические данные.

Зависимость	Вид уравнения	R2
Линейная		0,773
Линейная		0,781
Экспоненциальная		0,998
Экспоненциальная		0,999

 

Моделью наиболее точно  описывающей фактические данные  является линейная модель вида, так как для нее коэффициент детерминированности R² имеет наибольшее значение.

Таким образом, в данном разделе был проведен корреляционный анализ средствами MS Excel, в результате чего был сделан вывод о связи между исследуемыми факторами.

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