Застосування стандартних статистичних функцій 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^a¹+x2^a²). Потім розрахувати по моделі всі значення Y по кожному Х. После цього можна скористатися Пошуком рішення. Як початкові дані встановити:

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

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

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

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

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

№ п/п	Х	Y
1.	28873,34
2.	45223,07
3.	44234,12
4.	18356,98	16321,8
5.
6.	37113,1
7.		28345,8
8.	32567,45
9.	25332,3	21675,6
10.	15887,45
11.		11563,4
12.	8134,87	9543,56
13.	4876,45	5395,7
14.	7154,35	8584,45
15.	7332,87	6498,3
16.	7432,23	8845,67

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

№ п/п	Х	Y
1.	4876,45	5395,7
2.	7154,35	8584,45
3.	7332,87	6498,3
4.	7432,23	8845,67
5.	8134,87	9543,56
6.		11563,4
7.	15887,45
8.	18356,98	16321,8
9.
10.	25332,3	21675,6
11.		28345,8
12.	28873,34
13.	32567,45
14.	37113,1
15.	44234,12
16.	45223,07

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

Лінійна

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

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

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

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

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

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

Синтаксис (лінійна):

ЛИНЕЙН (известные_значения_y;известные_значения_x;конст;статистика)

Известные_значения_y - це безліч значень у, які вже відомі для співвідношення у = mx + b.

Известные_значения_x - це необов'язкова безліч значень x, які вже відомі для співвідношення у = mx + b.