Возможности ТП MS Excel для построения и анализа регрессионных моделей

В ЭТ существует возможность моделирования задач экономики на базе следующих технологий:

· с использованием стандартных функций категории Статистические – линейные и экспоненциальные модели,

· графическим способом – линейные, экспоненциальные, степенные, логарифмические и т.д.

· с использованием специальных надстроек «Пакет анализа» и «Поиск решения».

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

 

Для расчета линейных моделей Excel располагает рядом функций, работа которых основана на методе наименьших квадратов: ЛИНЕЙН, НАКЛОН, ОТРЕЗОК, ПРЕДСКАЗ, ТЕНДЕНЦИЯ. Регрессионный анализ предполагает не только расчет параметров модели, но и оценку ее адекватности по некоторым статистическим характеристикам. Часть этих характеристик можно получить как результат работы некоторых стандартных функций, например, ЛИНЕЙН, КОРРЕЛ. Для проведения полного корреляционно-регрессионного анализа Excel предлагает специальную надстройку Пакет Анализа.

Рассмотрим поочередно перечисленные функции.

Функция ЛИНЕЙН -рассчитывает статистику ряда с применением метода наименьших квадратов для вычисления уравнения прямой линии, которое наилучшим образом описывает исходные данные. Результатом работы функции является массив, который описывает полученную теоретическую прямую.Эта функция является поистине самой универсальной для расчета параметров линейных моделей. Во-первых, она может использоваться как для расчета однофакторных, так и многофакторных моделей (что определяется размером массива независимых переменных х_i), во-вторых, при желании в качестве результата, кроме коэффициентов уравнения регрессии, можно получить и статистические характеристики, характеризующие построенную модель.

Формат

 

ЛИНЕЙН(известные значенияY, известные значения X, константа;

Статистика)

 

Известные значенияY – это известные значения Y, для которых параметры X по уравнению определены.

Известные значения X – это известные значения независимой переменной X. Массив известные значения X может быть многомерным в отличие от массива известные значения Y, который является одномерным. Массив X может быть опущен, тогда значения X устанавливаются автоматически как предварительный ряд чисел, начиная с 1. Но обязательно должно быть соответствие между размерностями массива X и Y, если массив X задан.

Константа – это логическое значение, которое указывает функции, каким образом должен быть определен коэффициент b. Если логическое значение ИСТИНА или оно опущено, то b определяется в обычном порядке. Если константа равна ЛОЖЬ, то коэффициенты подбираются таким образом, чтобы выполнялось равенство y=mx (b=0).

Статистика – логическое значение, которое может принимать значение ИСТИНА или ЛОЖЬ. Если статистика имеет значение ИСТИНА, то будет представлена дополнительная регрессионная статистика по регрессии, если ЛОЖЬ или опущено, то выходным массивом будет основная статистика, т.е. коэффициенты m₁, m₂, …, m_n и b.

В зависимости от количества независимых переменных уравнение получаемой теоретической прямой может иметь вид (10.1) для однофакторной модели или (10.2) для многофакторной модели.

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

 

Таблица 1. – Результаты, возвращаемые функцией ЛИНЕЙН

mn	mn-1	…	m2	m1	b
Sen	Sen-1	…	Se2	Se1	Seb
r2	Sey
F	df
SSreg	SSresid

Здесь m₁, m₂…m_n, b – коэффициенты уравнения регрессии.

Все остальное – дополнительная статистика по регрессии:

Se₁, Se₂, Se_n – стандартные ошибки для коэффициентов m₁, m₂…m_n;

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

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

Se_y– стандартная ошибка для Y;

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

Df – степень свободы системы (уравнение надежности);

SSreg- регрессионная сумма квадратов;

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

Для выполнения прогноза нового значения Y в полученное уравнение нужно в уравнение регрессии подставить новое значение х.

 

Функция ПРЕДСКАЗ - на основании линейного тренда вычисляет или предсказывает будущее значение зависимой переменной Y, соответствующее заданному X-значению, по существующим X- и Y-значениям.

Формат