Пример 4. Предположим, что коммерческий агент рассматривает возможность закупки небольших зданий под офисы в традиционном деловом районе. Агент может использовать множественный регрессионный анализ для оценки цены здания под офис на основе следующих переменных:
y -оценочная цена здания под офис;
x1 -общая площадь в квадратных метрах;
x2 -количество офисов;
x3 -количество входов;
x4 -время эксплуатации здания в годах.
Агент наугад выбирает 11 зданий из имеющихся 1500 и получает следующие данные:
A | B | C | D | E | |
х1 -площадь, м2 | х2 -офисы | х3 - входы | х4- срок, лет | Цена, у.е. | |
1.5 | |||||
1.5 | |||||
"Пол-входа" означает вход только для доставки корреспонденции.
В этом примере предполагается, что существует линейная зависимость между каждой независимой переменной (x1,x2,x3,x4) и зависимой переменной (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=ml ∙ xl+m2 ∙ x2+m3 ∙ x3+m4 ∙ x4+b теперь может быть получено из строки 14:
y=27,64 ∙ x1+12530 ∙ x2+2553 ∙ x3-234,24 ∙ x4+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 Как с помощью линии тренда отобразить прогнозируемые величины?