Оценка параметров регрессионной модели методом наименьших квадратов в Excel Текст научной статьи по специальности «Математика»

Economics

УДК 330.43

T.C. Каграманян, Л.О. Бабешко

Оценка параметров регрессионной модели методом наименьших квадратов в Excel

Аннотация

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

I Ключевые слова: статистика, диапазон, метод наименьших квадратов, регрессионный анализ, линейный метод наименьших квадратов, константа.

T.S. Kagramanyan, L.O. Babeshko

Estimation parameter regression model by least squares in Excel

Abstract

The article deals with the problems associated with the use of regression. Much attention is given to the difficulties associated with the regression analysis. Report assessment of the conditions of applicability of the method of least squares; wrong choice of alternative methods in violation of the conditions of applicability of the method of least squares; the use of regression analysis without in-depth knowledge about the subject.

| Keywords: statistics, Ordinary Least Squares, regression analysis, linear least squares, range, constant.

Метод наименьших квадратов (МНК, англ. Ordinary Least Squares, OLS) - один из методов оценки параметров регрессионных моделей. Достоинством метода являются - статистические свойства МНК-

оценок (при выполнении предпосылок Гаусса-Маркова - несмещенность и эффективность), простота математических выводов и практической реализации.

МНК позволяет решить задачу «наилучшего» приближения выборочных данных х(,у(, ? = 1,...,П линейной функцией:

/ (X) = а + ЬХ (1)

- для парной регрессии.

Смысл «наилучшего» приближения определяется выбором критерия. В методе наименьших квадратов - это сумма квадратов отклонений (остатков)

П 2 ^ \2 , л2

/ л\ " О " /V О П ^

F(а,Ь) = I е2 = I & - )2 = £ & - (а + ЬХ{ ))2 t=1 1=1 г=1

и оценки параметров (2 и Ь должны быть подобраны таким образом, чтобы функция F (а ,Ь) была

минимальна:

n

F(a,b)=Ze2 ^ min. (2)

v ' t=\

Экономика

Для решения задачи (2) (задача на безусловный экстремум) составляются необходимые условия экстремума (First Order Condition)'.

dF

да

dF

2Z(y

t

a

— / / —

bXt ):

0

db

= - 21 Xt (y - a - bXt ) =

0

которые, можно также записать следующим образом:

z(y, - â - bXt)= 0

zXt Y - â - bXt )= 0

(3)

или в компактной форме:

£ е = о £ хе = о •

Система (3) называется системой нормальных уравнений. В (3) столько уравнений, сколько параметров требуется оценить по выборочным данным. Из решения системы нормальных уравнений находятся МНК-оценки параметров:

Z Y

an

b z Xt = 0

ZXtYt - azxt - bz x2 = 0

1 -1 - — a =1Z Yt - b1Z Xt = Y - bX, n n

где X и Y - средние значения по выборке:

X

1 n

- z xt nt=1

Y

1 n

1 z yt . nt=1

Подстановка, полученного для а выражения, во второе уравнение системы нормальных уравнений

£xtyt -1 (еyt)(£x,) + ъ1(£x,)(£x,)-ьеx2 = 0,

n

приводит к следующей оценке параметра b

è = nZXYz&XkZLl-Z xt • y,

n

rt VZ-i t/VZ-i tZ x; - ( Z x, )2

Z

t_st X *

где X, = X, — X , у, = У, — У - значения переменных центрированные по средним выборочным;

Таким образом, МНК - оценки параметров парной регрессионной модели выражаются через выборочные данные следующим образом:

b =

z х,у,

z x;

2

â = 1 zYt -1zXtb = Y - bX n n

(4)

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

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

Наиболее просто реализуются вычисления коэффициентов линейной регрессионной модели (1).

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

ОТРЕЗОК(диапозон_Y;диапазон_X) НАКЛОН(диапазон_Y;диапазон_X) КОРРЕЛ(диапазон_Y;диапазон_X) Первая функция вычисляет свободный член уравнения регрессии (a в выражении (1), вторая -наклон прямой (b в выражении (1). Третья функция позволяет вычислить коэффициент корреляции.

<

>

Economics

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

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

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

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

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

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

Константа. Необязательный аргумент. Логическое значение, которое указывает, требуется ли, чтобы константа а была равна 0. Если аргумент

константа имеет значение ИСТИНА или опущен, то свободный член вычисляется обычным образом.

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

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

„

b a

Sb Sa

R2 S = b

F V2

RSS ESS

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

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

2. Если статистика=ИСТИНА, то 5 строк и к столбцов (число столбцов равно числу оцениваемых параметров, для парной регрессии - 2).

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

Таблица 1

Величина Описание

b,a МНК-оценки параметров.

и т. д. Стандартные значения ошибок для коэффициентов Ь; а; ...

R2 Коэффициент детерминации. Он характеризует тесноту связи между результативным показателем и набором факторных показателей. Принимает только положительные значения в пределах от 0 до 1. Чем ближе значение коэффициента к 1, тем больше теснота связи. И, наоборот, чем ближе к 0, тем зависимость меньше.

G Оценка ско возмущения.

F ^-статистика или ^-наблюдаемое значение. ^-статистика используется для определения того, является ли случайной наблюдаемая взаимосвязь между зависимой и независимой переменными.

v Степени свободы. Степени свободы полезны для нахождения ^-критических значений в статистической таблице. Для определения уровня надежности модели необходимо сравнить значения в таблице с ^-статистикой, возвращаемой функцией ЛИНЕЙН.

RSS Регрессионная сумма квадратов.

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

Экономика

Практическая часть

Пример. По территориям региона приводятся данные за 20ХХ г.

Таблица 2

Номер региона Среднедневная заработная плата, руб., у Среднедушевой прожиточный минимум в день одного трудоспособного, руб., X

1 133 78

2 148 82

3 134 87

4 154 79

5 162 89

6 195 106

7 139 67

8 158 88

9 152 73

10 162 87

11 159 76

12 173 115

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

ъ 0,920431 76,97649 а

0,279716 24,21156

Я2 0,519877 12,54959

10,82801 10

яда 1705,328 1574,922 ЕББ

Можем записать стандартную форму записи оцененной модели:

У = 76.98 + 0.92 х X + е (24.21) (0.28) (12.55)

Таким образом, в нашем случае коэффициент С увеличением среднедневной зарплаты детерминации равен 0,52, следовательно, оцененная среднедушевой прожиточный минимум увеличивается модель среднего качества. на 0,92 процентных пунктов.

Литература

1. Бабешко Л.О. Основы эконометрического моделирования. - М.: КомКнига, 2010. - 432 с.

2. Бородич С.А. Эконометрика. - Минск: Новое знание, 2001. - 408 с.

3. Орлов А.И. Эконометрика. - М.: Экзамен, 2002.

4. Кремер Н.Ш. Эконометрика: Учебник для вузов / Н.Ш. Кремер, Б.А. Путко; под ред. проф. Н.Ш. Кремера. -М.: ЮНИТА-ДАНА, 2005. - 311 с.