Линейное и нелинейное оценивание параметров регрессии в Microsoft Excel

Размещено на http://www.allbest.ru/

Линейное и нелинейное оценивание параметров регрессии в Microsoft Excel

В.Б. Яковлев

В статье рассматривается сравнительная оценка линейного и нелинейного оценивания параметров регрессии в Microsoft Excel. Для нелинейного оценивания предлагается применение нелинейного метода обобщенного понижающего градиента, реализованного в надстройке Поиск решения.

Ключевые слова: регрессионный анализ; оценивание параметров регрессии; метод обобщенного понижающего градиента.

V.B. Yakovlev

Linear and Nonlinear Estimation of Regression Parameters in Microsoft Excel

The article deals with the comparative evaluation of linear and nonlinear estimation of regression parameters in Microsoft Excel. For nonlinear estimation, it is proposed to apply the nonlinear method of the generalized gradient, reduction implemented in Solver add-in.

Keywords: regression analysis; estimation of regression parameters; generalized gradient reduction method.

При оценивании параметров линейной регрессии применяют различные методы (см., например, [1-5]). Из них наиболее часто используемым является метод наименьших квадратов, с помощью которого находят значения параметров уравнения регрессии, минимизируя суммы квадратов отклонений фактических данных от расчетных y_x :

Для данной функции найдем частные производные по каждому из параметров а и b и приравниваем их к нулю:

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

Нелинейные регрессии бывают трех типов.

Линеаризацию переменных можно произвести только для первых двух типов нелинейных уравнений регрессии. Первые приводят к линейному виду простой заменой переменных, вторые -- с помощью логарифмирования (см. табл. 1). Внутренне нелинейные к линейному виду не приводятся.

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

Таблица 1 Линеаризация нелинейных регрессий

Это будет наглядно видно из дальнейшего анализа. Поэтому для таких регрессий предпочтительнее использовать нелинейные методы наименьших квадратов. В них, как и в классическом методе наименьших квадратов, находят значения параметров уравнения регрессии, при которых сумма квадратов отклонений S фактических данных у от расчетных y_x является минимальной.

Для решения этой задачи применяют два основных метода:

1) прямую минимизацию функции S методами нелинейной оптимизации, которые позволяют находить экстремумы выпуклых линий (сюда можно отнести различные методы наискорейшего спуска (градиентные методы), например метод обобщенного понижающего градиента, используемый в табличном процессоре Microsoft Excel, и др.);

2) решение системы нелинейных уравнений, полученной из необходимого условия экстремума функции -- равенства нулю частных производных по каждому из параметров (эта система решается итерационными методами, например методом Гаусса-Ньютона, который используется в статистическом пакете Statistica, и др.).

Существуют также методы оценивания параметров нелинейной регрессии, которые сочетают в себе два вышеизложенных метода. Сюда можно отнести метод Левенберга-Марквардта, являющийся сочетанием направления Ньютона-Г аусса и метода наискорейшего спуска. Данный метод используется во многих статистических пакетах (Statistica, IBM SPSS Statistics и др.).

Рассмотрим методику линейного и нелинейного оценивания параметров регрессии для зависимости численности обучающихся от количества организаций высшего образования по Центральному федеральному округу (табл. 2).

Таблица 2 Количество государственных организаций высшего образования и численность обучающихся в них в Центральном федеральном округе в 2016 г.

Линейное оценивание параметров регрессии

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

На основе данных диаграммы имеется возможность получать пять типов линейно оцененных регрессий или линий тренда, таких как линейная, полиномиальная различных степеней, логарифмическая, степенная и экспоненциальная.

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

Осуществим линейное оценивание параметров указанных выше регрессий с помощью точечной диаграммы, на которую добавим соответствующие линии трендов. Для этого подготовим данные в таблице MS Excel в соответствии с рисунком 1.

Рис. 1. Данные в MS Excel

Построим точечную диаграмму данной зависимости. Для этого выделим ячейки d:D18 и выполним команду Вставка, Рекомендуемые диаграммы. В открывшемся диалоговом окне Вставка диаграммы установим параметры в соответствии с рисунком 2.

Рис. 2. Диалоговое окно Вставка диаграммы

Точечная диаграмма выводится в следующем отформатированном виде (рис. 3). Вначале проведем оценивание параметров линейного уравнения регрессии. Для этого установим курсор на любую точку диаграммы и щелкнем правой кнопкой мыши. В появившемся контекстном меню нажмем на кнопку Добавить линию тренда.

* Численность обучающихся, тыс. чел. (у)

Рис. 3. Точечная диаграмма

В диалоговом окне Формат линии тренда установим параметры в соответствии с рисунком 4.

Рис. 4. Формат линии тренда

Диаграмма выводится в следующем отформатированном виде (рис. 5).

Рис. 5. Диаграмма

Проведем выравнивание по остальным уравнениям тренда аналогично выравниванию по линейному уравнению тренда (рис. 6).

Рис. 6. Выравнивание по остальным уравнениям тренда

Более высокий индекс детерминации R² получается у полиномиальной (0,786) и линейной (0,741) регрессий. Поэтому можно сделать вывод, что данные регрессии в лучшей степени отражают зависимость численности обучающихся от количества организаций высшего образования.

Нелинейное оценивание параметров регрессии

Осуществим расчет параметров линейной, полиномиальной, логарифмической, степенной и экспоненциальной регрессий в Microsoft Excel с помощью нелинейного оценивания. Для этого используем надстройку Поиск решения, в которой реализован поиск решения нелинейных задач методом обобщенного понижающего градиента (ОПГ).

Подготовим данные в MS Excel в соответствии с рисунком 7.

Рис. 7. Подготовка данных в MS Excel

Рассчитаем параметры уравнения линейной регрессии y_x = a + bx. Для этого вначале введем в ячейки Е2 и F2 соответственно формулы =$C$21+$C$22*C2 и =D2-E2 и скопируем их в ячейки B3:F18.

Затем найдем оптимальные значения параметров уравнения регрессии а и b, минимизируя сумму квадратов остатков (отклонений фактических уровней ряда от предсказанных значений ряда). Для этого введем в ячейку С24 функцию =СУММПРОИЗВ(Е2:Р18;Р2:Р18) и выполним команду Данные, Поиск решения. В диалоговом окне Параметры поиска решения установим параметры в соответствии с рисунком 8.

Рис. 8. Параметры поиска решения

В результате будут получены оптимальные значения параметров уравнения регрессии а и b (см. рис. 9).

Рис. 9. Оптимальные значения параметров уравнения регрессии а и b

Для этого введем:

• в ячейку С26 функцию =ДИСП.Г(В2:Б18) для расчета общей дисперсии;

• в ячейку С27 формулу =C24/A18 для расчета остаточной дисперсии;

• в ячейку С28 формулу =1-C27/C26 для расчета индекса детерминации. Результаты выводятся в следующем виде (рис. 10).

Рис. 10. Параметры уравнения регрессии

Как видим, получены те же результаты, что и при линейной оценке параметров линейного уравнения регрессии (см. рис. 5).

Аналогично проведем нелинейное оценивание параметров полиномиальной, логарифмической, степенной и экспоненциальной регрессий. Для этого для каждого уравнения регрессии создадим копии листа с расчетными данными по линейной регрессии и на скопированных листах проведем соответствующие расчеты: на каждом листе введем в ячейку Е2 соответствующие формулы и скопируем их в ячейки Е3:Е15:

• =$C$21+$C$22*C2+$C$23*C2^A2 (полиномиальная регрессия);

• =$C$21+$C$22*LN(C2) (логарифмическая регрессия);

• =$C$21*C2^A$C$22 (степенная регрессия);

• =$C$21*EXP($C$22*C2) (экспоненциальная регрессия).

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

Таблица 3 Линейное и нелинейное оценивание параметров регрессии

Нелинейное оценивание степенной и экспоненциальной регрессий показывает лучшие результаты по сравнению с линейным оцениванием (см. рис. 6), поскольку получен более высокий индекс детерминации, характеризующий достоверность аппроксимации. Это следует из того, что оценки параметров для линеаризуемых уравнений, как правило, оказываются несколько смещенными, то есть заниженными.

Нанесем на диаграммы результаты линейного и нелинейного оценивания степенной (рис. 11) и экспоненциальной регрессий (рис. 12). На рисунках наглядно видны различия в их оценке, особенно степенной регрессии. нелинейный регрессия excel

Проверка итогов нелинейного оценивания регрессии в статистическом пакете Statistica 10 показала аналогичные результаты (см. рис. 13, 14).

Таким образом, нелинейное оценивание регрессий в Microsoft Excel, нелинейных по оцениваемым параметрам, приводит к лучшим результатам по сравнению с линейным оцениванием. Поэтому его рекомендуется применять на практике для такого рода регрессий, причем как внутренне линейных, так и внутренне нелинейных.

Рис. 11. Результаты линейного и нелинейного оценивания степенной регрессии

Рис. 12. Результаты линейного и нелинейного оценивания экспоненциальной регрессии

Рис. 13. Проверка итогов нелинейного оценивания регрессии в статистическом пакете Statistica 10

Рис. 14. Проверка итогов нелинейного оценивания регрессии в статистическом пакете Statistica 10

Литература

1. Воскобойников Ю.Е. Построение регрессионных эконометрических моделей (с примерами в Excel): учебное пособие. Новосибирск: НГАСУ (Сибстрин), 2014. 224 с.

2. Конрад Карлберг. Регрессионный анализ в Microsoft Excel. М.: Диалектика, 2017. 400 с.

3. ЯковлевВ.Б. Статистика. Расчеты в Microsoft Excel: учебное пособие. М.: Юрайт, 2017. 353 с.

4. Яковлев В.Б. Эконометрика в Excel и Statistica: учебное пособие. Ч. 1. Регрессионный анализ. М.: Эдитус, 2018. 168 с.

5. Яковлев В.Б., Яковлева О.А. Практикум по общей теории статистики: учебное пособие. М.: ИНФРА-М, 2016. 382 с.

Literatura

1. Voskobojnikov Уп.Е. Postroenie regressionny'x e'konometricheskix modelej (s primerami v Excel): uchebnoe posobie. Novosibirsk: NGASU (Sibstrin), 2014. 224 s.

2. Konrad Karlberg. Regressionny'j analiz v Microsoft Excel. M.: Dialektika, 2017. 400 s.

3. Yakovlev V.B. Statistika. Raschety' v Microsoft Excel: uchebnoe posobie. M.: Yurajt, 2017. 353 s.

4. Yakovlev V.B. E'konometrika v Excel i Statistica: uchebnoe posobie. Ch. 1. Regressionny'j analiz. M.: E'ditus, 2018. 168 s.

5. Yakovlev V.B., Yakovleva O.A. Praktikum po obshhej teorii statistiki: uchebnoe posobie. M.: INFRA-M, 2016. 382 s.

Размещено на Allbest.ru