Використання функцій ЛИНЕЙН, ЛГРФПРИБЛ MS EXCEL для аналізу складної сукупності даних та прогнозування

Для проведення прогнозування за допомогою статистичних \ функцій Microsoft Excel застосовують регресивний аналіз. Це вид І статистичного аналізу, що дозволяє оцінити міру залежності між І змінними, пропонуючи механізм обчислення передбачуваного значення змінної з декількох уже обчислених значень. Іншими словами, на основі статистичної вибірки відомих значень функції F(x) та аргументів х, можна спрогнозувати поведінку функції шляхом підстановки нових значень аргументів.

• 3 використанням множинної лінійної регресії — =ЛИНЕИН(відоміY;відомiX) — прогнозує значення коефіцієнтів а, (і = п, п-\, ..., 2,1) та Ь. Отримані при прогнозуванні значення коефіцієнтів підставляються у рівняння множинної лінійної регресії і отримується значення Y.

• 3 використанням множинної нелінійної регресії — =ЛГФПРИБЛ(відоміY; відоміX) — прогнозує значення коефіцієнтів а, (і = п, п- 1, ..., 2,1) та b. Отримані при прогнозуванні значення коефіцієнтів підставляються у рівняння множинної нелінійної регресії і отримується значення Y.

Приклад:

Фірма бажає придбати будівлю під офіс і має оцінити вартість нерухомості, запропонованої на ринку.

Розв 'язання:

Можна використати множинний регресійний аналіз для прогнозування вартості будівлі. Передбачається, що використовується функція У = F(xi), де Y— вартість будівлі, хі — площа, х2 — кількість поверхів, хЗ — кількість входів, х4 — строк експлуатації будівлі.

Для прогнозування вартості триповерхової будівлі, що експлуатується ЗО років, з площею у 2000 м" та 2 входами, використовують Прайс-лист для нерухомості подібного типу у вигляді таблиці:

Для оцінки вартості використовується функція =ЛИНЕЙН (ві-доміУ;відомь¥), або функція

=ЛГФПРИБЛ(в ідом і Y; відомії).

Кожна з функцій не визначає Y у відповідному рівнянні множинної регресії, а визначає значення коефіцієнтів а, та Ь, починаючи з старшого коефіцієнта ап. Обчислені коефіцієнти використовуються у формулі, яка реалізує рівняння множинної регресії (лінійної, якщо використовувалась функція ЛИНЕЙ, і нелінійної, якщо використовувалась функція ЛГФПРИБЛ).

Відомими Ye значення, розташовані у комірках ЕЗ:Е13, відомими Хе значення, розташовані у комірках A2:D13.

Для визначення коефіцієнтів треба створити заголовки у комірках А15:Е15 і занести вибрану статистичну функцію у комірки А16:Е16 як формулу масиву:

у комірку Е20 заноситься формула яка реалізує рівняння множинної лінійної регресії =D16*A20+C16*B20+B16*C20+A16*D20+E16, і отримується результат.