Практична робота 3 використання функції "линейн"
Інший метод одержання рівняння регресії складається у використанні убудованої статистичної функції Excel ЛИНЕЙН.
Вона розраховує статистику для ряду із застосуванням методу найменших квадратів, щоб обчислити пряму лінію, що щонайкраще апроксимує наявні дані. Функція расчитывает масив, що описує отриману пряму. Рівняння для прямої лінії має вигляд:
y = mx + b або y = m1x1 + m2x2 + ... + b (у випадку декількох інтервалів значень x)
де залежне значення y є функцією незалежного значення x. Значення m - це коефіцієнти, що відповідають кожної незалежної змінної x, а b - це постійна. Функція ЛИНЕЙН расчитывает масив {mn;mn-1;...;m1;b}, а також може расчитывать додаткову регресійну статистику (табл.). Вона показує наскільки корисно отримане рівняння для пророкування майбутніх значень.
Додаткова регресійна статистика:
se1,se2,...,sen - стандартні значення помилок для коефіцієнтів m1,m2,...,mn.
Seb - стандартне значення помилки для постійної b (seb = #Н/Д, якщо константа має значення НЕПРАВДА).
R2 - коефіцієнт детермінації. Рівняються фактичні значення y і значення, одержувані з рівняння прямої; за результатами порівняння обчислюється коефіцієнт детермінації, нормований від 0 до 1. Якщо він дорівнює 1, то має місце повна кореляція з моделлю, тобто немає розходження між фактичним й оцінним значеннями y. У протилежному випадку, якщо коефіцієнт детермінації дорівнює 0, то рівняння регресії невдало для пророкування значень y.
Sey - стандартна помилка для оцінки y.
F - F-статистика. Використається для визначення того, чи є спостережуваний взаємозв'язок між залежною й незалежної змінними випадковою чи ні.
Df - ступеня волі. Корисні для знаходження F-критичних значень у статистичній таблиці. Для визначення рівня надійності моделі потрібно зрівняти значення в таблиці з F-статистикою, расчитываемой функцією ЛИНЕЙН.
Ssreg - регресійна сума квадратів.
Ssresіd - залишкова сума квадратів. Для кожної крапки обчислюється квадрат різниці між прогнозованим значенням y і фактичним значенням y. Сума цих квадратів разностей і називається залишковою сумою квадратів.
Mіcrosoft Excel підраховує суму квадратів різностей між фактичними значеннями y і середнім значенням y, що називається загальною сумою квадратів (регресійна сума квадратів + залишкова сума квадратів). Чим менше залишкова сума квадратів у порівнянні із загальною сумою квадратів, тим більше значення коефіцієнта детермінованості r2, що показує, наскільки гарне рівняння, отримане за допомогою регресійного аналізу, пояснює взаємозв'язку між змінними.
У наведеній нижче таблиці показано, у якому порядку розраховується додаткова регресійна статистика.
Таблиця 8
Порядок розміщення додаткової регресійної статистики функції ЛИНЕЙН
mn |
Mn-1 |
… |
m1 |
B |
sen |
Sen-1 |
… |
se1 |
Seb |
r2 |
Sey |
|
|
|
F |
Df |
|
|
|
ssreg |
Ssresid |
|
|
|
Послідовність дій:
1. Відкрити робочу книгу Аналіз.х1з за допомогою команди меню файл - Відкрити.
2. Установити курсор на аркуш Регресія. Як вихідні значення використаємо дані таблиці 6.
3. Виділити на аркуші блок вільних осередків, куди буде спрямований результат обчислень функції. Число стовпців блоку дорівнює числу оцінюваних факторів плюс один, для розміщення значення константи. У нашому випадку число стовпців буде дорівнює 3, тому що ми маємо два оцінюваних факторів (витрати на рекламу й ціна за 1 виріб). Число рядків повинне бути дорівнює 5 (див. табл.6) У цьому випадку блок осередків Е2:G7.
4. Клацнути на кнопці Майстер функцій.
- Вибрати в списку функцій категорії Статистичні функцію ЛИНЕЙН.
-У діалоговому вікні, що з'явилося, указати вихідні параметри для розрахунку:
Рис.6 Діалогове вікно функції ЛИНЕЙН
- Відомі значення y - це безліч значень y, які вже відомі для співвідношення y = mx + b. У нашому випадку це значення Стовпця обсяг продажів З2:З19;
- Відомі значення x - це необов'язкова безліч значень x, які вже відомі для співвідношення y = mx + b. Відповідні значення стовпців Витрати на рекламу й Ціна за 1 продукції А2:В19.
Відомі значення y й x можуть бути масивами будь-якої форми за умови, що вони мають однакову розмірність. Якщо відомі значення x опущені, то передбачається, що це масив {1;2;3;...} такого ж розміру як і відомі значення y.
- Константа це логічне значення, що вказує, потрібно чи, щоб константа b була дорівнює 0. Якщо константа має значення 1 або опущене, то b обчислюється звичайним образом, якщо константа має значення 0, то b покладається рівним 0 і значення m підбираються так, щоб виконувалося співвідношення y = mx. Уведіть значення 1.
- Статистика - це логічне значення, що вказує, потрібно чи повернути додаткову статистику по регресії. Якщо статистика має значення 1, то функція ЛИНЕЙН розраховує додаткову регресійну статистику, так що масив буде мати вигляд: {mn;mn-1;...;m1;b:sen;sen-1;...;se1;seb:r2;sey:F;df:ssreg;ssresіd} (табл.8), інакше функція розраховує тільки коефіцієнти m і постійну b. Уведіть значення 1.
5. Для того щоб у блоці розрахункових даних було видно не тільки значення першого осередку уведення завершите одночасним натисканням комбінації клавіш Ctrl+Shіft+Enter.
6. З отримані результати з результатами попередньої лабораторної роботи (Табл.7).
Таблиця 9
Результати розрахунку додаткової статистики функції ЛИНЕЙН
-358,1412987 |
0,3828415 |
36779,49 |
129,6571733 |
0,09343971 |
13165,54 |
0,806540874 |
14348,6222 |
#Н/Д |
31,26787915 |
15 |
#Н/Д |
12875046967 |
3088244387 |
#Н/Д |
У регресивному аналізі обчислюють експонентну криву, що апроксимує дані відповідно до рівняння виду
y = b·mх
або y = (b· (m1x1) · (m2x2) ·...) (при наявності декількох значень x),
В Excel для цих цілей існує статистична функція ЛГРФПРБЛ. Так само, як функція ЛИНЕЙН, функція ЛГРФПРИБЛ розраховує масив, що описує залежність між значеннями, але ЛИНЕЙН підганяє пряму лінію до наявним даних, а ЛГРФПРИБЛ підганяє експонентну криву. Так само є можливість одержання даних додаткової статистики. Порядок роботи аналогічний описаному вище в даній роботі.
Функції апроксимації ЛИНЕЙН і ЛГРФПРИБЛ можуть обчислити пряму або експонентну криву, що щонайкраще описує Ваші дані. Однак, Вам самим має бути вирішувати, який із двох результатів найбільшою мірою підходить до Вашим даним.
ПРАКТИЧНА РОБОТА 4
ВИКОРИСТАННЯ РЕЗУЛЬТАТІВ РЕГРЕСІЇ В ПРОГНОЗУВАННІ
Існує два методи знаходження розрахункових значень показника. Перший полягає в підстановці конкретних значень факторів у рівняння регресії, отримане за допомогою функцій ЛИНЕЙН або ЛГРФПРИБЛ. Значення y, передвіщені за допомогою рівняння регресії, можливо, не будуть правильними, якщо вони розташовуються поза інтервалом значень y, які використалися для визначення рівняння.
Інший метод знаходження розрахункових значень показника складається у використанні убудованих статистичних функцій ТЕНДЕНЦИЯ й РОСТ.
Послідовність дій:
1. Відкрити робочу книгу Аналіз.х1s за допомогою команди меню файл ? Відкрити.
2. Установити курсор на аркуш Регресія. Як вихідні значення використаємо дані таблиці 6.
3. З таблицю значеннями, на основі яких одержимо розрахункове значення. Для цього в осередок А21 уведи 155212 а в осередок В21 - 77. Курсор установите в осередок С21 куди буде поміщений результат розрахунку.
4. Клацнути на кнопці Майстер функцій.
- Вибрати в списку функцій категорії Статистичні функцію ТЕНДЕНЦИЯ.
- У діалоговому вікні, що з'явилося,
указати вихідні параметри для розрахунку:
Рис.7 Діалогове вікно функції ТЕНДЕНЦИЯ
- Відомі значення y - це значення Стовпця обсяг продажів, блок осередків С2:С19
- Відомі значення x - відповідні значення стовпців Витрати на рекламу й Ціна за 1 продукції, блок осередків А2:В19
- Нові значення x - блок осередків А21:В21.
- Константа - уведіть значення 1.
- Нажати кнопку ОК. Розраховане значення дорівнює 68624.
Тепер змінюючи в осередках А21:В21 вихідні значення факторів ви можете одержати будь-яке розрахункове значення показника.
5. Для одержання не одного, а ряду розрахункових значень одночасно необхідно на аркуші ввести всі вихідні значення факторів.
- Наприклад: в осередки А22:А24 уведіть 77742,104981, 170116, а в осередки В22:В24 - 9,104,71.
- Потім виділити блок осередків, куди будуть розміщені розрахункові значення показника. У цьому випадку осередку З22:З24.
- Викликати діалогове вікно функції ТЕНДЕНЦИЯ за допомогою майстра функцій, заповнити вихідні значення як було зазначено вище, але як Нові значення х виділити блок осередків А22:В24. Уведення завершити одночасним натисканням комбінації клавіш Ctrl+Shіft+Enter.
Можна аналогічно обчислити функцію РОСТ для експонентної кривої. Ці функції, якщо не задавати аргумент Нові значення x, розраховує масив обчислених значень y для фактичних значень x відповідно до прямій або кривої. Тепер можна зрівняти обчислені значення з фактичними значеннями. Можна також побудувати діаграми для візуального порівняння.
Вправа 1
Побудувати рівняння регресії для розрахунку витрат на ремонт устаткування залежно від строку експлуатації й випуску продукції. Визначити значимість коефіцієнтів цього рівняння й письмово виконати статистичний аналіз на основі отриманих результатів. Вихідні дані для аналізу представлені нижче..
Строк експлуатації |
Випуск продукції, |
Витрати на ремонт, |
обладнання, років |
т. грн/рік |
т. грн/рік |
1,3 |
1200 |
0,12 |
2,1 |
2100 |
1,1 |
4,1 |
5000 |
2,3 |
1 |
4500 |
0 |
0 |
5000 |
0 |
1,75 |
6000 |
1,1 |
2,3 |
3200 |
1,8 |
12,1 |
1000 |
7,8 |
10 |
6700 |
7,1 |
1 |
1200 |
0,67 |
8,6 |
4300 |
6,75 |
3,4 |
2670 |
1,75 |
Вправа 2.
За допомогою тренда визначите рівняння лінійної регресії залежності вартості будинку від терміну служби, а так само використайте множинний регресійний аналіз для оцінки ціни офісного будинку в заданому районі на основі наступних змінних.
Загальна площа (кв.м) |
Кількість офісів |
Кількість входів |
Строк експлуатації |
Вартість (у.е.) |
2310 |
2 |
2 |
20 |
142600 |
2333 |
2 |
2 |
12 |
144000 |
2356 |
3 |
1,5 |
33 |
151000 |
2379 |
3 |
2 |
43 |
150000 |
2402 |
2 |
3 |
53 |
139000 |
2425 |
4 |
2 |
23 |
169000 |
2448 |
2 |
1,5 |
99 |
126000 |
2471 |
2 |
2 |
34 |
142900 |
2494 |
3 |
3 |
23 |
163000 |
2517 |
4 |
4 |
55 |
169000 |
2540 |
2 |
3 |
22 |
149000 |
Вправа 3.
Використовуючи данні таблиці скласти регресійну модель завдання залежності врожайності кукурудзи на зерно від факторів добрива й число комбайнів.
Урожайність |
Добрива |
Число комбайнів на 100 га |
|
мінеральні |
органічні |
||
28 |
1,5 |
25 |
10 |
32 |
1,8 |
30 |
12 |
34 |
2,2 |
30 |
16 |
29 |
1,7 |
25 |
14 |
27 |
1,5 |
20 |
11 |
24 |
1,3 |
12 |
9 |
28 |
1,7 |
25 |
11 |
33 |
2,1 |
30 |
15 |
26 |
1,4 |
20 |
8 |
22 |
0,9 |
10 |
6 |
26 |
1,1 |
20 |
10 |
28 |
1,8 |
30 |
13 |
25 |
1,5 |
15 |
7 |
29 |
1,9 |
30 |
11 |
40 |
2,5 |
40 |
18 |
Вправа 4.
Використовуючи данні таблиці скласти регресійну модель завдання залежності середньодобових приростів ваги свиней від факторів. Зробити аналіз.
Середньодобові прирости ваги |
Корму на 1ед.гол.ц.к.ед. |
Протеїнув 1к.ед. г |
220 |
8 |
90 |
250 |
10,5 |
95 |
270 |
11 |
100 |
300 |
12 |
105 |
350 |
12,5 |
107 |
240 |
7 |
92 |
260 |
7,5 |
95 |
280 |
11,5 |
97 |
320 |
12 |
104 |
290 |
12 |
98 |
210 |
7,5 |
92 |
380 |
14 |
107 |
400 |
15,5 |
110 |
310 |
11,5 |
108 |
230 |
8,5 |
95 |
Вправа 5.
Використовуючи данні таблиці скласти регресійну модель завдання залежності собівартості 1ц приросту ваги великої рогатої худоби. Зробити аналіз.
Собівартість1 ц.,грн. |
Середньодобові прирости ваги, г |
Витрати чел.година на1 ц |
Витрати к.ед. на 1ц |
220 |
300 |
50 |
12 |
195 |
350 |
45 |
11 |
185 |
400 |
40 |
10 |
180 |
420 |
38 |
9,5 |
170 |
500 |
35 |
9 |
190 |
380 |
39 |
11 |
200 |
320 |
50 |
12 |
170 |
450 |
34 |
8,5 |
165 |
550 |
25 |
8 |
140 |
700 |
20 |
7 |
150 |
650 |
22 |
7 |
180 |
480 |
35 |
8 |
170 |
520 |
34 |
7,5 |
160 |
590 |
23 |
8 |
190 |
470 |
40 |
11 |
Вправа 6.
Використовуючи данні таблиці скласти регресійну модель завдання залежності виходу товарної продукції на 1 га сільськогосподарських угідь від забезпеченості ресурсами.
Товарна продукція грн. |
Основні фонди грн. |
Виробничі витрати грн. |
Середньорічна чисельність робітників на 100 га |
408 |
668 |
440 |
13,7 |
283 |
505 |
350 |
8,9 |
289 |
559 |
326 |
8,7 |
351 |
731 |
414 |
9,7 |
417 |
802 |
513 |
1,7 |
316 |
599 |
382 |
9,3 |
275 |
600 |
376 |
8,0 |
324 |
623 |
390 |
8,6 |
350 |
723 |
417 |
9,6 |
340 |
616 |
360 |
8,5 |
454 |
756 |
481 |
11,8 |
357 |
689 |
453 |
10,3 |
319 |
723 |
400 |
8,9 |
279 |
516 |
355 |
9,4 |