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

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

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

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

Выделим для результата блок ячеек F8:G12, введём в строку формул Функцию =ЛГРФПРИБЛ(Е2:Е7;D2:D7;1;1), нажмем клавиши Сtrl+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 и равными 0,02, 0,07 и 0,08 соответственно. Коэффициент детерминированности r2 =0,992, т.е. полученное уравнение даёт совпадение с табличными данными с вероятностью 99,2%.

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

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

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

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

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

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

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

Множественная линейная регрессия.

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

у – оценочная цена здания под офис;

х1 – общая площадь в квадратных метрах;

х2 – количество офисов;

х3 – количество входов;

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

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

	А	В	С	D	Е
	х1-площадь, м2	х2-офисы	х3-входы	х4-срок, лет	Цена, у.е.
			1,5
			1,5

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

В этом примере предполагается, что существует линейная зависимость между каждой независимой переменной (х1,х2,х3,х4) и зависимой переменной (у), т.е. ценой зданий под офис в данном районе.

• выделим блок ячеек А14:Е18 (в соответствии с табл. 1),
• введём формулу =ЛИНЕЙН(Е2:Е12;А2:D12;ИСТИНА;ИСТИНА), -
• нажмём клавиши Ctrl+Shift+Enter,
• в выделенных ячейках появится результат:

	А	В	С	D	E
	-234,237	2553,210	12529,7682	27,6413	52317,83
	13,2680	530,6691	400,066838	5,42937	12237,36
	0,99674	970,5784	#Н/Д	#Н/Д	#Н/Д
	459,753		#Н/Д	#Н/Д	#Н/Д
			#Н/Д	#Н/Д	#Н/Д

Уравнение множественной регрессии у=m1*x2+m2*x2+m3*x3+m4*x4+b теперь может быть получено из строки 14:

у=27,64*х1+12,530*х2+2553*х3-234,24*х4+52318 (14)

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

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

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