Автоматизация процесса регрессионного анализа с помощью функций MS EXCEL

К функциям, позволяющим автоматизировать процесс регрессионного анализа можно отнести[12]:

Функция:КОРРЕЛ - возвращает значение выборочного коэффициента корреляции
Аргумент	Описание аргумента
Массив 1	Совокупность значений, описывающих первую из анализируемых переменных
Массив 2	Совокупность значений, описывающих вторую из анализируемых переменных

Функция:ЛИНЕЙН - возвращает значения параметров линейного уравнения регрессии (y=a₀+a₁x₁+a₂x₂+…+a_nx_n) и набор дополнительных статистических показателей, позволяющих анализировать качество уравнения.
Аргумент	Описание аргумента
Известные значения y	Совокупность значений, описывающих результативный признак
Известные значения x	Совокупность значений, описывающих все факторные признаки
Константа	Логическое значение, которое указывает, требуется ли, чтобы коэффициент a₀ была равна 0. Если аргумент константа имеет значение ИСТИНА, то a₀ вычисляется обычным образом. В противном случае полагается равным 0.
Статистика	Логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии. Если аргумент имеет значение ИСТИНА, то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику, позволяющую анализировать качество полученной модели. В противном случае функция ЛИНЕЙН возвращает только коэффициенты уравнения регрессии.

Функция:ЛГРФПРИБЛ - возвращает значения параметров показательного уравнения регрессии (y = а₀(а₁^x¹)(a₂^x²)…(a_n^xⁿ)) и набор дополнительных статистических показателей, позволяющих анализировать качество уравнения.
Аргумент	Описание аргумента
Известные значения y	Совокупность значений, описывающих результативный признак
Известные значения x	Совокупность значений, описывающих все факторные признаки
Константа	Логическое значение, которое указывает, требуется ли, чтобы коэффициент a₀ была равна 0. Если аргумент константа имеет значение ИСТИНА, то a₀ вычисляется обычным образом. В противном случае полагается равным 1.
Статистика	Логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии. Если аргумент имеет значение ИСТИНА, то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику, позволяющую анализировать качество полученной модели. В противном случае функция ЛИНЕЙН возвращает только коэффициенты уравнения регрессии.

Функция:FРАСПОБР - возвращает критическое значение коэффициента Фишера (F-статистики).
Аргумент	Описание аргумента
Вероятность	Величина уровня значимости, при котором осуществляется исследование (наиболее часто встречающийся вариант – 0,05)
Степени свободы 1	Число степеней свободы v₁
Степени свободы 2	Число степеней свободы v₂

Функция:СТЬЮДРАСПОБР - возвращает критическое значение коэффициента Стьюдента (t-статистики).
Аргумент	Описание аргумента
Вероятность	Величина уровня значимости, при котором осуществляется исследование (наиболее часто встречающийся вариант – 0,05)
Степени свободы	Число степеней свободы v₂

Функция:ТЕНДЕНЦИЯ - возвращает значения результативного признака в соответствии с линейным (y=a₀+a₁x₁+a₂x₂+…+a_nx_n) уравнением регрессии.
Аргумент	Описание аргумента
Известные значения y	Совокупность значений, описывающих результативный признак
Известные значения x	Совокупность значений, описывающих все факторные признаки
Новые значения x	Совокупность значений факторных признаков, для которых нужно рассчитать значения результативного признака
Константа	Логическое значение, которое указывает, требуется ли, чтобы коэффициент a₀ была равна 0. Если аргумент константа имеет значение ИСТИНА, то a₀ вычисляется обычным образом. В противном случае полагается равным 0.

Функция:РОСТ - возвращает значения результативного признака в соответствии с показательным (y=а₀(а₁^x¹)(a₂^x²)…(a_n^xⁿ)) уравнением регрессии.
Аргумент	Описание аргумента
Известные значения y	Совокупность значений, описывающих результативный признак
Известные значения x	Совокупность значений, описывающих все факторные признаки
Новые значения x	Совокупность значений факторных признаков, для которых нужно рассчитать значения результативного признака
Константа	Логическое значение, которое указывает, требуется ли, чтобы коэффициент a₀ была равна 1. Если аргумент константа имеет значение ИСТИНА, то a₀ вычисляется обычным образом. В противном случае полагается равным 0.

Особо следует остановиться на функциях ЛИНЕЙН и ЛГРФПРИБЛ. Поскольку эти функции позволяют вывести не одно, а несколько различных значений, технология работы с ними несколько отличается от общепринятой:

1. Выделить диапазон ячеек следующего размера: число столбцов равно числу факторных признаков плюс 1, число строк равно 5;

2. В меню Вставка выбрать команду Функция. В открывшемся диалоговом окне выбрать категорию Статистическая после чего выбрать нужную функцию;

3. Указать все необходимые аргументы после чего завершить работу с помощью комбинации клавиш Control+Shift+Enter.

В итоге получится массив, имеющий следующую структуру:

a_i	a_i-1	…	a₂	a₁	a₀
se_i	se_i-1	…	se₂	se₁	se₀
R²	se_y
F	df
ss_reg	ss_ost

где:	a₀, a₁,…,a_n -	Значения соответствующих коэффициентов уравнения регрессии.
	Se₀,se₁,...,se_i -	Стандартные значения ошибок для коэффициентов.
	R² -	Коэффициент детерминации.
	se_y -	Стандартная ошибка для оценки y.
	F -	F-статистика, или F-расчетное значение.
	df -	Число степеней свободы v₂.
	ss_reg -	Регрессионная сумма квадратов.
	ss_ost -	Остаточная сумма квадратов.