Приклад використання множинної лінійної регресії

Припустимо, що забудовник оцінює вартість групи невеликих офісних будинків у традиційному діловому районі.

Забудовник може використовувати множинний регресійний аналіз для оцінки ціни офісного будинку в заданому районі на основі наступних змінних.

y - оцінна ціна будинку під офіс;

x₁ - загальна площа у квадратних метрах;

x₂ - кількість офісів;

x₃ - кількість входів (0,5 входу означає вхід тільки для доставки кореспонденції);

x₄ - час експлуатації будинку в літах.

У цьому прикладі передбачається, що існує лінійна залежність між кожної незалежної змінною (x₁, x₂, x₃ і x₄) і залежною змінної (y), тобто ціною будинку під офіс у даному районі. Вихідні дані показані на малюнку.

Налаштування для розв’язку поставленої задачі показані на рисунку вікна " Регресія ". Результати розрахунків розміщені на окремому аркуші в трьох таблицях

У підсумку ми одержали наступну математичну модель:

y = 52318 + 27,64*x1 + 12530*x2 + 2553*x3 - 234,24*x4.

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

y = 27,64*2500 + 12530*3 + 2553*2 - 234,24*25 + 52318 = 158 261 у.є.

У регресійному аналізі найбільш важливими результатами є:

• коефіцієнти при змінних та Y -Перетину, що є шуканими параметрами моделі;
• множинний R, що характеризує точність моделі для наявних вихідних даних;
• F -Критерій Фішера (у розглянутому прикладі він значно перевершує критичне значення, рівне 4,06);
• t -статистика – величини, що характеризують ступінь значимості окремих коефіцієнтів моделі.

На t -статистику варто зупинитися особливо. Дуже часто при побудові регресійної моделі невідомо, як впливає той або інший фактор x на y. Включення в модель факторів, які не впливають на вихідну величину, погіршує якість моделі. Обчислення t -статистики допомагає виявити такі фактори. Наближену оцінку можна зробити так: якщо при n >> k величина t -статистики за абсолютним значенням істотно більше трьох, що відповідає коефіцієнт варто вважати значимим, а фактор включити в модель, інакше виключити з моделі. Таким чином, можна запропонувати технологію побудови регресійної моделі, що складає із двох етапів:

1) обробити пакетом " Регресія " всі наявні дані, проаналізувати значення t -статистики;

2) видалити з таблиці вихідних дані стовпці з тими факторами, для яких коефіцієнти незначимі й обробити пакетом " Регресія " нову таблицю.

Для приклада розглянемо змінну x4. У довіднику по математичній статистиці t -критичне з (n - k -1)=6 степенями свободи і довірчою ймовірністю 0,95 дорівнює 1,94. Оскільки абсолютна величина t, рівна 17,7 більше, ніж 1,94, строк експлуатації - це важлива змінна для оцінки вартості будинку під офіс. Аналогічним очином можна протестувати всі інші змінні на статистичну значимість. Нижче приводяться спостережувані t -значення для кожної з незалежних змінних:

Загальна площа	5,1
Кількість офісів	31,3
Кількість входів	4,8
Строк експлуатації	17,7

Всі ці значення мають абсолютну величину більшу, ніж 1,94; отже, всі змінні, використані в рівнянні регресії, корисні для пророкування оцінної вартості будинку під офіс у даному районі.

Пошук розв’язку

Excel має кілька програм-надбудов, що виконують розв’язання різних задач. Однієї з надбудов є " Пошук розв’язку ", що дозволяє вирішувати оптимізаційні задачі в Excel. Найчастіше це задачі лінійного програмування (ЛП).

Загальне формулювання задачі ЛП: знайти невід’ємний розв’язок X системи лінійних рівнянь AX=B, при якому цільова функція f=CX приймає максимальне (мінімальне) значення, де A - матриця коефіцієнтів; B - об'єми ресурсів.

Економічний зміст системи AX=B полягає в завданні обмежень на ресурси, що витрачаються.

Економічний зміст цільової функції f=CX полягає в максимальному прибутку або мінімальній собівартості, що одержується від оптимального розв’язку X. Наприклад, якщо X - вектор об'ємів випуску продукції, а С - вектор прибутку, що одержується від одиниці кожного виду продукції, то f - сумарний прибуток від випуску всієї продукції.

Розглянемо роботу надбудови " Пошук розв’язку " на прикладі задачі про раціон годівлі тварин. Потрібно скласти такий раціон годівлі тварин трьома видами корму, при якому вони одержать необхідну кількість живильних речовин A і B і собівартість кормів буде мінімальна. Ціни кормів, необхідне кількість живильних речовин і їхній зміст у кожному кормі показані в таблиці.

Живильні речовини	Корм 1	Корм 2	Корм 3	Необхідна кількість (од. пит. речовини)
А (од./кг)
Б (од./кг)
Ціна корму (грн/кг)	2,20	1,95	2,87

Якщо позначити X=(x₁, x₂, x₃) — шукана кількість кормів, то задача ЛП формулюється так:

Знайти розв’язок X системи

при якому цільова функція

приймає мінімальне значення.

Математичне формулювання задачі необхідно оформити у вигляді таблиці, що показує основні залежності.

Середовища таблиці мають наступний зміст:

• діапазон A1:C2 - містить матрицю A;
• діапазон D1:D2 - містить вектор ресурсів В;
• діапазон A6:C6 - містить вектор цін С;
• діапазон A4:C4 - містить вектор розв’язків X, початкові значення якого задані нулю і який буде оптимізований програмою;
• діапазон E1:E2 - містить вирази, що обчислюють добуток AX;
• осередок E6 - містить вирази, що обчислює f=CX.

Виклик програми пошуку розв’язку виконується через меню "Сервіс\Пошук розв’язку...". У вікні, що відкрився, "Пошук розв’язку" необхідно встановити наступні параметри:

• " Встановити цільове середовище " — E6;
• Встановити перемикач " Рівної мінімальному значенню ";
• у поле " змінюючи середовище " указати діапазон A4:C4;
• в області " Обмеження " натиснути кнопку " Додати " і у вікні " Додавання обмежень " ввести обмеження: E1>=D1 і E2>=D2;

• Натиснути кнопку "Параметри..." і у вікні, що відкрилося, встановити прапорці "Лінійна модель", "Ненегативні значення" і вибрати перемикач "Оцінка" - "Лінійна".

Для запуску програми необхідно у вікні "Пошук розв’язку" натиснути кнопку "Виконати". Результати обчислень будуть записані в змінювані комірки таблиці. У підсумку таблиця повинна мати такий вигляд.

Таким чином, тварин варто годувати першим кормом у кількості 0,38 кг, третім — 3,85 кг і не використовувати другий корм взагалі. При такому раціоні витрати на годівлю однієї тварини складуть 11,88 грн.