2. Розрахунок багатофакторних моделей з використанням Excel

Багатофакторний аналіз полягає в знаходженні залежності одного чинника (результуючого) від декількох інших. Загальний вид багатофакторної функції є:

Y=F(x1, X2, ., Xn),

де F – може бути будь-якої форми (лінійна, логарифмічна, параболічна, гіперболічна, експоненціальна і ін.).

F може бути будь-якої форми:

• лінійна;

• параболічна;

• гіперболічна;

• логарифмическая4

• експоненціальна;

• ін.

При розрахунку моделей необхідно підібрати відповідні коефіцієнти так, щоб при підстановці реальних значень змінних Х в рівняння, розрахункові значення Y були приблизно рівні реальним (або якомога ближче до ним).

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

Для побудови регресійних моделей з сучасних програмних продуктів використовується Microsoft Excel. У набір стандартних статистичних функцій якого входить розрахунок 2-х регресійних моделей - лінійної і логарифмічної, причому окрім розрахунку основних коефіцієнтів надаються додаткові статистичні дані (такі як відхилення коефіцієнтів моделей, коефіцієнт Фішера для розрахункової моделі і ін.).

Застосування стандартних статистичних функцій Microsoft Excel

У набір стандартних статистичних функцій входить розрахунок 2-х регресійних моделей – лінійної і логарифмічної. Зовнішній вигляд цих моделей такої:

Y=a0+x1a1+x2a2+.+xnan – лінійна модель.

Y=a0*x^a1*x^a2*.*x^an - логарифмічна модель.

Для проведення розрахунків по лінійній моделі необхідно:

1. Створити таблицю по фактичних значеннях чинників.

2. У вибрану, на свій розсуд комірку, ввести формулу ЛИНЕЙН|(Y;X;L)

де:

Y – відомі значення результуючого чинника;

X – відомі значення впливаючих чинників;

F – 1, якщо розраховується модель з вільним коефіцієнтом а0, і 0 в іншому випадку;

L – 1, якщо необхідна додаткова статистична інформація (для довідки за додатковою статистичною інформацією див. довідку Excel по функції ЛИНЕЙН|).

3. Після отримання результату необхідно виділити блок, першим коміркою в якому є той комірка, де написана формула, і який відповідає за розмірами блоку повертаних функцією значень (див. довідку Excel по функції ЛИНЕЙН|), і натиснути комбінацію клавіш [Ctrl-Shift-Enter]. Виділений блок заповниться розрахованими значеннями коефіцієнтів моделі, і додатковою статистичною інформацією (якщо L=1).

Для побудови логарифмічної моделі слід поступати аналогічним чином, тільки в цьому випадку використовується функція ЛГРФПРИБЛ (див. довідку Excel).

Застосування пункту меню Пошук рішення

Для розрахунку моделей довільного вигляду при рішенні задачі регресійного аналізу засобами Excel, можна скористатися вбудованим інструментом Пошук рішення (пункт меню Сервіс).

В цьому випадку необхідно наперед визначитися з видом моделі (наприклад y=x1^a1+x2^a2). Потім розрахувати по моделі всі значення Y по кожному Х. После цього можна скористатися Пошуком рішення. Як початкові дані встановити:

• Цільова комірка – посилання на перший комірку, що містить розрахункове значениеY1.

• Змінні комірки – відведені для коефіцієнтів а0, а1, а2.

• Обмеження – визначити, що всі розрахункові значення Y повинні бути рівні фактичним.

• Якщо необхідно вказати додаткові параметри для пошуку рішення, можна скористатися кнопкою Параметри.

У таблиці приведені запропоновані дані для знаходження залежності:

№ п/п	Х	Y
	28873,34	28242
	45223,07
	44234,12
	18356,98	16321,8
	24650	22462
	37113,1
	27100	28345,8
	32567,45
	25332,3	21675,6
	15887,45	12546
	13687	11563,4
	8134,87	9543,56
	4876,45	5395,7
	7154,35	8584,45
	7332,87	6498,3
	7432,23	8845,67

Відсортуємо відомі значення Х за збільшенням.

№ п/п	Х	Y
	4876,45	5395,7
	7154,35	8584,45
	7332,87	6498,3
	7432,23	8845,67
	8134,87	9543,56
	13687	11563,4
	15887,45	12546
	18356,98	16321,8
	24650	22462
	25332,3	21675,6
	27100	28345,8
	28873,34	28242
	32567,45
	37113,1
	44234,12
	45223,07

Застосування стандартних статистичних функцій Excel.

Лінійна

Лінійна - розраховує статистику для ряду із застосуванням методу найменших квадратів, щоб обчислити пряму лінію, яка найкращим чином апроксимує наявні дані. Функція повертає масив, який описує одержану пряму. Оскільки повертається масив значень, функція повинна задаватися у вигляді формули масиву.

Зовнішній вигляд лінійної моделі наступний:

у = mx + b або у = m1x1 + m2x2 + ... + b (у разі декількох інтервалів значень x)

де залежне значення у є функцією незалежного значення x. Значення m - це коефіцієнти, відповідні кожній незалежній змінній x, а b - це постійна.

Точність апроксимації за допомогою прямої, обчисленою функцією ЛИНЕЙН залежить від ступеня розкиду даних. Чим ближче дані до прямої, тим більше точної є модель, використовувана функцією ЛИНЕЙН. Функція використовує метод найменших квадратів для визначення якнайкращої апроксимації даних.

Для проведення розрахунків по лінійній моделі необхідно вказати параметри згідно синтаксису функції ЛИНЕЙН().