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

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

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

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

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

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

 

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

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

Функция: РОСТ - возвращает значения результативного признака в соответствии с показательным (y=а0*(а1^x ^1)*(a2^x ^2)*…*(an^x ^n)) уравнением регрессии.
Аргумент	Описание аргумента
Известные значения y	Совокупность значений, описывающих результативный признак
Известные значения x	Совокупность значений, описывающих все факторные признаки
Новые значения x	Совокупность значений факторных признаков, для которых нужно рассчитать значения результативного признака
Константа	Логическое значение, которое указывает, требуется ли, чтобы коэффициент a0 была равна 1. Если аргумент константа имеет значение ИСТИНА, то a0 вычисляется обычным образом. В противном случае полагается равным 0.

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

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

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

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

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