Экспоненциальная регрессия

Пример 3. а) Функция ЛГРФПРИБЛ.

Условие примера 2.

Поскольку функция в табл. 2 носит явно нелинейный характер, целесообразно искать её приближение в виде не прямой линии, как в примере 2, а в виде нелинейной кривой. Из всех видов нелинейности (гипербола, парабола и др.) Excel реализует только экспоненциальное приближение вида у = b∙ m^x c помощью функции ЛГРФПРИБЛ, которая рассчитывает для этого уравнения значения b и m.

Выделим для результата блок ячеек F8: G12, введём в строку формул

Функцию =ЛГРФПРИБЛ(E2: E7; D2: D7; 1; 1), нажмём клавиши Ctrl+Shift+ Enter, в выделенных ячейках появится результат:

1, 56628015	1, 196513
0, 02038299	0, 07938
0, 99181334	0, 085268
484, 599687
3, 52335921	0, 029083

 

Таким образом, коэффициент m = 1, 556, а b = 1, 197, т.е. уравнение приближающей кривой имеет вид:

 

у = 1, 197∙ (1, 556 ^х) (13)

со стандартными ошибками для m, b и y равными 0, 02, 0, 07 и 0, 08 соответ-ственно. Коэффициент детерминированности r² = 0, 992, т.е.

полученное уравнение даёт совпадение с табличными данными с вероятностью 99, 2%.

Поскольку интерполяция табл. 2 экспоненциальной кривой даёт более точное приближение (99, 2%) и с меньшими стандартными ошибками для m, b и y, в качестве приближающего уравнения принимаем уравнение (13).

При х = 8 получим у = 1, 197 ∙ 34, 363 = 41, 131 град.

б) Функция РОСТ вычисляет прогнозируемое по экспоненциальному приближению значения у для новых значений х, имеет формат:

=РОСТ (изв_знач_ у; изв_знач_ х; нов_знач_ х; константа).

Выделим блок ячеек F14: F17, введём формулу

=РОСТ(E2: E7; D2: D7; G2: G5; ИСТИНА), в выделенных ячейках появится массив чисел {27, 6696434; 43, 3384133; 67, 8800967; 106, 319248}, т.е. при х=8 значение функции у = 43, 34 град. Это значение немного отличается от вычисленного в п. а), поскольку функция РОСТ использует для расчетов линию экспоненциального тренда.

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

 

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

y -оценочная цена здания под офис;

x₁ -общая площадь в квадратных метрах;

x₂ -количество офисов;

x₃ -количество входов;

x₄ -время эксплуатации здания в годах.

Агент наугад выбирает 11 зданий из имеющихся 1500 и получает сле­дующие данные:

	A	B	C	D	E
	х1 -площадь, м2	х2 -офисы	х3 - входы	х4- срок, лет	Цена, у.е.
			1.5
			1.5

 

" Пол-входа" означает вход только для доставки корреспонденции.

В этом примере предполагается, что существует линейная зависимость между каждой независимой переменной (x₁, x₂, x₃, x₄) и зависимой переменной (y), т.е. ценой здания под офис в данном рай­оне.

§ выделим блок ячеек А14: Е18 (в соответствии с табл. 1),

§ введем формулу =ЛИНЕЙН (E2: E12; A2: D12; ИСТИНА; ИСТИНА),

§ нажмём клавиши Ctrl+Shift+ Enter,

§ в выделенных ячейках появится результат:

 

	A	B	C	D	E
	-234.237	2553.210	12529.7682	27.6413	52317.830
	13.2680	530.66915	400.066838	5.42937	12237.361
	0.99674	970.57846	#H/Д	#H/Д	#H/Д
	459.753		#H/Д	#H/Д	#H/Д
		5652135.3	#H/Д	#H/Д	#H/Д

 

Уравнение множественной регрессии y=m_l ∙ x_l+m₂ ∙ x₂+m₃ ∙ x₃+m₄ ∙ x₄+b теперь может быть получено из строки 14:

y=27, 64 ∙ x₁+12530 ∙ x₂+2553 ∙ x₃-234, 24 ∙ x₄+52318 (14)

Теперь агент может определить оценочную стоимость здания под офис

в том же районе, которое имеет площадь 2500 кв. м, три офиса, два входа, зда­нию 25 лет, используя следующее уравнение:

у=27, 64 ∙ 2500+12530 ∙ 3+2553 ∙ 2-234, 24 ∙ 25+52318=158261 у.е.

Это значение может быть вычислено с помощью функции ТЕНДЕНЦИЯ:

=ТЕНДЕНЦИЯ (Е2: Е12; A2: D12; {2500; 3; 2; 25}).

Если вместо функции ЛИНЕЙН провести интерполирование таблицы с помощью функции

=ЛГРФПРИБЛ(E2: E12; A2: D12; ИСТИНА; ИСТИНА), то

для получения уравнения множественной экспоненциальной регрессии будет выведен результат:

 

0, 99835752	1, 0173792	1, 0830186	1, 0001704	81510, 335
0, 00014837	0, 0065041	0, 0048724	6, 033E-05	0, 1365601
0, 99158875	0, 0105158	#Н/Д	#Н/Д	#Н/Д
176, 832548		#Н/Д	#Н/Д	#Н/Д
0, 07821851	0, 0006635	#Н/Д	#Н/Д	#Н/Д
#Н/Д	#Н/Д	#Н/Д	#Н/Д	#Н/Д

 

Коэффициент детерминированности здесь составляет 0, 992 (99, 2%), т.е. меньше, чем при линейной интерполяции, поэтому в качестве основного следует оставить уравнение множественной регрессии (14).

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

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

1 Сущность регрессионного анализа, его использование для прогнозирования функций.

2 Как получить уравнение одномерной линейной регрессии, каков синтаксис функций линейного приближения?

3 Как получить уравнение многомерной линейной регрессии, каков синтаксис функции?

4 Как получить уравнение одномерной экспоненциальной регрессии, каков синтаксис функции экспоненциального приближения?

5 Как получить уравнение многомерной экспоненциальной регрессии, каков синтаксис функции экспоненциального приближения?

6 Что выполняют функции ЛИНЕЙН, ТЕНДЕНЦИЯ, РОСТ, ЛГРФПРИБЛ, ПРЕДСКАЗ?

7 Каковы правила ввода и использования табличных формул?

8 Как на гистограмме исходных данных добавить линию тренда?

9 Как с помощью линии тренда отобразить прогнозируемые величины?