Формирование нелинейных однофакторных регрессионных моделей на компьютере с помощью ППП Excel

Для вычисления параметров экспоненциальной регрессии (4.8) на компьютере (в Excel) используется встроенная статистическая функция ЛГРФПРИБЛ. Порядок вычисления аналогичен применению функции ЛИНЕЙН.

Для вычисления параметров степенной регрессии после преобразования исходных данных в соответствие с (4.11), можно воспользоваться функцией ЛИНЕЙН.

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

4.6. Практический блок

Пример

Задача 1. По некоторым территориям районов края известны значения среднего суточного душевого дохода в у.е. (фактор X) и процент от общего дохода, расходуемого на покупку продовольственных товаров (фактор Y) (табл. 4.1).

Требуется для характеристики зависимости У от X рассчитать параметры линейной, степенной, показательной функции и выбрать оптимальную модель (провести оценку моделей через среднюю ошибку аппроксимации (А) и F-критерий Фишера.

Таблица 4.1

Район	у	х
Пожарский (1)	68,8	45,1	61,277	7,5231	11,4989	56,5970
Кавалеровский (2)	61,2	59,0	56,4689	4,7311	2,00817	22,3833
Дальнегорский (3)	59,9	57,2	57,0915	2,8085	0,63123	7,88767
Хасанский (4)	56,7	61,8	55,5004	1,1996	5,69109	1,43904
Лесозаводский (5)	55,0	58,8	56,5381	1,5381	1,81683	2,36575
Хорольский (6)	54,3	47,2	60,5505	6,2505	7,09956	39,0687
Анучинский (7)	49,3	55,2	57,7833	8,4833	0,01055	71,9664
итого	405,2			32,534	28,7563	201,708
среднее	57,886			4,6477

РЕШЕНИЕ.

1а. Для расчета параметров а и b линейной регрессии у = аx + b решаем систему нормальных уравнений относительно а и b (или используем EXCEL).

Получаем уравнение регрессии: у = 76,88 – 0,35 x.

С увеличением среднедневной заработной платы на 1 руб. доля расходов на покупку продовольственных товаров снижается в среднем на 0,35 %-ных пункта.

Рассчитаем линейный коэффициент парной корреляции: r = -0,35326.

Связь умеренная, обратная.

Определим коэффициент детерминации:

R² = 0,1248.

Вариация результата на 12,5% объясняется вариацией фактора х. Подставляя в уравнение регрессии фактические значения х, определим теоретические (расчетные) значения (см. табл. 4.1).

Найдем величину средней ошибки аппроксимации А:

(4,647744/57,88571)´100%=0,080292.

В среднем расчетные значения отклоняются от фактических на 8,03%.

Рассчитаем F-критерий:

Fтабл = 6,6 > Fфакт, при γ = 0,05.

Полученное значение указывает на необходимость принять гипотезу Н₀ о случайной природе выявленной зависимости и статистической незначимости параметров уравнения и показателя тесноты связи.

1б. Построению степенной модели у = bx^а предшествует процедура линеаризации переменных. Линеаризация производится путем логарифмирования обеих частей уравнения:

lg y = lg b + a lg х, или Y = С + а Х,

где Y = lg(y), X = lg(x), C = lg(b).

Для расчетов используем формулы для линейной регрессии (или используем EXCEL).

Получим уравнение: у = 190,03х^-0,2984. R² =0,1157.

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

1в. Построению уравнения показательной кривой у = bа^х предшествует процедура линеаризации переменных при логарифмировании обеих частей уравнения:

lg y = lg b + х lg а, или Y = С + х lg а, и опять же можно использовать формулы для линейной регрессии(или EXCEL).

Получим уравнение: у = 77,24е^{-0,0053 х} . R² =0,1026.

Показательная функция еще хуже, чем степенная, описывает изучаемую зависимость.

1г. Уравнение равносторонней гиперболы у = а/x + b линеаризуется при замене: x = 1/ z.

Тогда у = аz + b. Для расчетов используем формулы для линейной регрессии (или используем EXCEL).

Получено уравнение: у = 38,435 + 1054.7/ x. R² =0.1539.

По уравнению равносторонней гиперболы получена наибольшая оценка тесноты связи (по сравнению с линейной, степенной и показательной регрессиями). A остается на допустимом уровне: 8,1%.

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

Контрольные вопросы

1. Какие методы применяются для выбора вида модели регрессии?

2. Какие функции чаще всего используются для построения уравнения парной регрессии?

3. Какой вид имеет система нормальных уравнений метода наименьших квадратов в случае гиперболической, показательной регрессии?

4. В каких случаях осуществляется построение нелинейных спецификаций уравнения регрессии с последующей их линеаризацией?

5. Приведите примеры нелинейных моделей регрессии и их линеаризацию.

6. Какие проблемы спецификации ошибок возникают при линеаризации уравнения регрессии?