Линейный многомерный регрессионный анализ

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

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

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

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

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

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

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

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

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

Для многомерного регрессионного анализа выполним следующие операции:

Таблица 6

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

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

§ введем формулу

=ЛИНЕЙН (E2:E12;A2:D12;ИСТИНА;ИСТИНА),

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

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

Продолжение табл. 6

	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;ИСТИНА;ИСТИНА)

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

Таблица 7

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 Как с помощью линии тренда отобразить прогнозируемые величины?