Реализация регрессионного анализа в программе MS Excel

Для проведения расчетов по линейному методу МНК можно использовать программу Microsoft Excel (входит в программный пакет Microsoft Office).

Наиболее просто реализуется вычисления коэффициентов линейной математической модели типа. Для этого можно использовать следующие встроенные функций MS Excel:

ОТРЕЗОК (диапозон_Y;диапазон_X)

НАКЛОН (диапазон_Y;диапазон_X)

КОРРЕЛ (диапазон_Y;диапазон_X)

Первая функция вычисляет свободный член уравнения регрессии (b₀ в y = b_o + b₁ · x), вторая – наклон прямой (b₁ в y = b_o + b₁ · x). Третья функция позволяет вычислить коэффициент корреляции  r.

Каждая из функций принимает два аргумента, разделяемых знаком точка с запятой “;”. Каждый из аргументов определяет диапазон ячеек, в котором находятся значения зависимой (диапазон_Y) и независимой (диапазон_Х) переменных. Диапазоны должны быть одинаковой формы (вектор-строка или вектор-столбец одинаковой длины).

В более общем виде линейный МНК может быть реализован с помощью встроенной функции ЛИНЕЙН, которая производит вычисления коэффициентов регрессии линейной математической модели с несколькими переменными  типа и дополнительно рассчитывает ряд статистических показателей. Вычисленные коэффициенты регрессии и статистики возвращаются в виде массива чисел. Поскольку возвращается массив значений, функция должна задаваться в виде формулы массива.

Запишем модель в следующем виде:

Функция ЛИНЕЙН может принимать от одного до четырех аргументов. Обязателен только первый аргумент, остальные – необязательные:

ЛИНЕЙН (диапазон_Y, [диапазон_X], [константа], [статистика])

Диапазон_Y —  Обязательный аргумент. Диапазон ячеек, содержащий множество значений зависимой переменной (y);

Диапазон_Х —  Диапазон ячеек, содержащий множество значений независимых переменных. Если переменных несколько, то они должны располагаться в смежных ячейках. Каждое диапазон значений независимой переменной должен иметь форму, аналогичную диапазону_Y.

Константа.  Необязательный аргумент. Логическое значение, которое указывает, требуется ли, чтобы константа b₀ была равна 0. Если аргумент константа имеет значение ИСТИНА или опущен, то свободный член b₀ вычисляется обычным образом.

Если аргумент константа имеет значение ЛОЖЬ, то значение b₀ полагается равным 0 и значения коэффициентов регрессии подбираются с этим условием.

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

Если аргумент статистика имеет значение ЛОЖЬ или опущен, функция ЛИНЕЙН возвращает только коэффициенты (то есть, вектор-строку). Размер диапазона ячеек, в которые будет записан результат выполнения функции ЛИНЕЙН следующий:

1.     Если статистика=ЛОЖЬ, то 1 строка и n столбцов (n-число определяемых параметров)

2.     Если статистика=ИСТИНА, то 5 строк и n столбцов.

Описание значений, вычисляемых функцией, приведены в таблице ниже.