CC BY
0
Ганиева З. С. ассистент
Самаркандский институт экономики и сервиса
Садиева С. С. студент
Самаркандский институт экономики и сервиса
Равшанова Ш.Р. студент
Самаркандский институт экономики и сервиса
ОЦЕНКА ПАРАМЕТРОВ РЕГРЕССИОННОЙ МОДЕЛИ МЕТОДОМ НАИМЕНЬШИХ КВАДРАТОВ В EXCEL
Аннотация: рассмотрена задача с применением регрессии. Особое внимание обращается на упрощение способа вычислений, связанного с регрессионным анализом: ошибочная оценка условий применимости метода наименьших квадратов; неправильный выбор альтернативных методов при нарушении условий применимости метода наименьших квадратов.
Ключевые слова: метод наименьших квадратов, регрессионная модель, программу Microsoft Excel, функции MS Excel.
Ganieva Z.S. assistant
Samarkand Institute of Economics and Service
Sadieva S.S. student
Samarkand Institute of Economics and Service
Ravshanova Sh.R.
student
Samarkand Institute of Economics and Service
ESTIMATING PARAMETERS OF A REGRESSION MODEL USING THE LESS SQUARE METHOD IN EXCEL
Annotation. a problem using regression is considered. Particular attention is paid to simplifying the method of calculations associated with regression analysis: erroneous assessment of the conditions for the applicability of the least squares method; incorrect choice of alternative methods when the conditions of applicability of the least squares method are violated.
Key words: least squares method, regression model, Microsoft Excel program, MS Excel functions.
Метод наименьших квадратов (МНК, англ. Ordinary Least Squares, OLS) - один из методов оценки параметров регрессионных моделей. Достоинством метода являются - статистические свойства МНК-оценок (при выполнении предпосылок ГауссаМаркова - несмещенность и эффективность), простота математических выводов и практической реализации.
МНК позволяет решить задачу «наилучшего» приближения выборочных данных Xt, Yt,t = 1,..., п, линейной функцией:
f(X) = a + bX(1) - для парной регрессии. Смысл «наилучшего» приближения определяется выбором критерия. В методе наименьших квадратов - это сумма квадратов отклонений (остатков):
п п п
F(a, b) =£ = £(Yt — Ytf = £(^ — (а + bXt)f
t=i t=i t=i
2
где et2 квадраты отклонений величин. Оценки параметров а.иЪ должны быть подобраны таким образом, чтобы функция F(cL, Ъ) была минимальной:
F(cL, Ъ) = Yt=1 et2 ^ шт(2) Для решения последней задачи, которая является задачей на безусловный экстремум, составляются необходимые условия экстремума (First Order Condition)
f п '
д^ = £(Ъ-(а + Щ)2 =-2£(ъ-(а + Щ) = 0
t=i а
п '
dF>=£( Yt-( S + bXt))2 = -2Xt £ (Yt — (a + bXt)) = 0
t=i S
Производя некоторые преобразования систему уравнений можно записать в виде:
£ (Yt — (LL + bXt)) = 0 ( £(Yt — a — bXt) = 0
- \ H xr - (3) Xt £ (Yt — (cL + bXt)) = 0 lXt£(Yt — a — bXt) = 0
£et = 0
или {
Xt £ t = 0
Система (3) называется системой нормальных уравнений. В (3) столько уравнений, сколько параметров требуется оценить по выборочным данным. Из решения системы нормальных уравнений находятся МНК-оценки параметров:
{^XtYt-a^Xt-b^Xt2 = 0
где V и Y - средние значения по выборке:
п п
^ = 1Yxt,Y = 1VYt
n n
t=i t=i
Подставив для a а выражения, во второе уравнение системы нормальных уравнений
1 Y<—1 (I (I y0+s 1 (I (I Y<H1 *<2=0
приходим к следующей оценке параметра b
g= n£XtYt =Extyt
где xt = — V, yt = Yt — Y - значения переменных центрированные по средним выборочным; Таким образом, МНК -оценки параметров парной регрессионной модели выражаются через выборочные данные следующим образом:
г £*tyt
1 F^
â=nlYt—®1IVt = Y—^
Реализация регрессионного анализа в программе MS Excel.
Для проведения расчетов по линейному методу МНК можно использовать программу Microsoft Excel (входит в программный пакет Microsoft Office и является мощным табличным редактором, дающий высокие результаты вычислений и предоставляющий доступный сервис пользователю). Наиболее просто реализуются вычисления коэффициентов линейной регрессионной модели (1). Для этого можно использовать следующие встроенные функций MS Excel:
0TPE30K(INTERCEPT) (диапазон_Y; диапазон_Х) - определяет точку пересечения линейного тренда с осью ординат;
НАКЛОН (SLOPE)(диапазон_Y; диапазон_Х) -определяет коэффициент наклона линейного тренда;
КОРРЕЛ(диапазон_Y; диапазон_Х) -вычисляет коэффициент корреляции
Каждая из функций принимает два аргумента, разделяемых знаком точка с запятой «;». Каждый из аргументов определяет диапазон ячеек, в котором находятся значения зависимой (диапазон_Y) и независимой
(диапазон_Х) переменных. Диапазоны должны быть одинаковой формы (вектор-строка или вектор-столбец одинаковой длины). В более общем виде линейный МНК может быть реализован с помощью встроенной функции ЛИНЕИН, которая производит вычисления коэффициентов линейной регрессии и дополнительно рассчитывает ряд статистических показателей. Вычисленные коэффициенты регрессии и статистики возвращаются в виде массива чисел. Поскольку возвращается массив значений, функция должна задаваться в виде формулы массива. Функция ЛИНЕИН может принимать от одного до четырех аргументов. Обязателен только первый аргумент, остальные - необязательные:
ЛИНЕИН (диапазонY, [диапазонХ], [константа], [статистика])
-ДиапазонY - обязательный аргумент.
Диапазон ячеек, содержащий множество значений зависимой переменной (у);
-ДиапазонХ - диапазон ячеек, содержащий множество значений независимых переменных. Если переменных несколько, то они должны располагаться в смежных ячейках. Каждый диапазон значений независимой переменной должен иметь форму, аналогичную диапазонуY.
-Константа. Необязательный аргумент. Логическое значение, которое указывает, требуется ли, чтобы константа а была равна 0. Если аргумент константа имеет значение ИСТИНА или опущен, то свободный член вычисляется обычным образом. Если аргумент константа имеет значение ЛОЖЬ, то значение а полагается равным 0 и значения коэффициентов регрессии подбираются с этим условием.
-Статистика. Необязательный аргумент. Логическое значение, которое указывает, требуется ли возвратить дополнительную регрессионную статистику. Если аргумент статистика имеет значение ИСТИНА, функция ЛИНЕИН возвращает дополнительную регрессионную статистику. Возвращаемый массив чисел будет иметь следующий вид:
ъ а
Ч
ю.
р у2
КББ БББ
Если аргумент статистика имеет значение ЛОЖЬ или опущен, функция ЛИНЕИН возвращает только коэффициенты (то есть, вектор-строку). Размер диапазона ячеек, в которые будет записан результат выполнения функции ЛИНЕИН следующий: 1. Если статистика=ЛОЖЬ, то 1 строка и п столбцов (п-число определяемых параметров). 2. Если статистика=ИСТИНА, то 5 строк и к столбцов (число столбцов равно числу оцениваемых параметров, для парной регрессии - 2).
Описание значений, вычисляемых функцией, приведены в таблице
ниже.
Величина Описание
Ь,а МНК-оценки параметров.
'V ^и т. д. Стандартные значения ошибок для коэффициентов Ь; а,...
&? Коэффициент детерминации. Он характеризует тесноту связи между результативным показателем и набором факторных показателей. Принимает только положительные значения в пределах от 0 до 1. Чем ближе значение коэффициента к 1, тем больше теснота связи. И, наоборот, чем ближе к 0, тем зависимость меньше.
Оценка возмущения.
F 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
h â
0, 279716 24, 21156
Sb Sà
R2 0, 519877 12, 54959
F 10, 82801 10 v2
RSS 1705, 328 1574, 922 ESS
Тогда уравнение модели будет записано в виде: Y = 76,98+ 0,92Xt + et
С увеличением среднедневной зарплаты среднедушевой прожиточный минимум увеличивается на 0, 92 процентных пунктов.
Использованные источники:
1. Бабешко Л.О. Основы эконометрического моделирования. - М.: КомКнига, 2010. - 432 с.
2. Бородич С.А. Эконометрика. - Минск: Новое знание, 2001. - 408 с.
3. Орлов А.И. Эконометрика. - М.: Экзамен, 2002.
4. Кремер Н.Ш. Эконометрика: Учебник для вузов / Н.Ш. Кремер, Б.А. Путко; под ред. проф. Н.Ш. Кремера. - М.: ЮНИТА-ДАНА, 2005. - 311 с.
5. Гарнаев А.Ю., Использование MS EXCEL и VBA в экономике и финансах. - СПб.: БХВ - Петербург, 1999. - 332с.
