ЛАБОРАТОРНА РОБОТА №7

 

Тема: Побудова багатофакторної нелінійної моделі.

Мета заняття: На підставі статистичних даних показника Y факторів X₁, X₂ знайти оцінки параметрів моделі, якщо припустити, що вона має наступну структуру: Y=ln(a₀+ a₁/ X₁+ a₂*X₂).

ü Оцінити адекватність прийнятої математичної моделі статистичним даним з надійністю Р=0,95.

ü Якщо прийнята математична модель адекватна знайти:

- оцінку прогнозу та з надійністю Р=0,95 його довірчий інтервал;

- оцінки часткових коефіцієнтів еластичності для прогнозу.

 

Хід роботи

Висувається гіпотеза, що між факторами X₁, X₂ і показником Y існує стохастична залежність Y=ln(a₀+ a₁/ x₁+ a₂x₂).           

1.    Завантажити програму EXCEL.

2.    Сформувати таблицю вихідних даних, заповнивши діапазон комірок А4:C16 (рис.1).

 

 

 

 

 

 

 

 

 

 

3.    Для приведення регресії до лінійного виду зробимо заміну величин Y₁=exp(Y), Z₁=1/X_1. Для того, щоб таблицю можна було використати, необхідно ввести Z₂= Х_2.

4.    У комірці G20 за допомогою вбудованої функції СЧЕТЗ знайдемо об’єм вибірки n.

5.    Розрахуємо суму величин , _, , _{, , ,} використовуючи вбудовану функцію СУММ або Автосумму, результати розмістити у комірки А18,В18,С18,D18,Е18 й F18 відповідно (рис.1).

6.    Складемо систему нормальних рівнянь, використовуючи вбудовані функції СУММ і СУММПРОИЗВ ( для стислості запису ці вбудовані функції позначимо відповідно через С и СП).

7.    Симплекс-таблиця для рішення системи нормальних рівнянь записується в наступному виді:

8.    Для розв’язання системи нормальних рівнянь використаємо звичайні жорданові виключення, вибираючи за розв'язувальні елементи діагональні елементи.

9.    Процедура обчислення одного кроку ЗЖВ:

1)   елементи, що не належать розв'язувальному рядку та розв'язувальному стовпцю, обчислюються за формулою:, так для комірки С28:=C23-C$22*$B23/$B$22. Скопіювати формулу у решту комірок таблиці D28,E28,C29:Е29;

2)   елементи розв'язувального стовпця ділять на розв'язувальний елемент, так для комірки В28: = B23/B$22. Скопіювати формулу у комірку розв'язувального стовпця В29;

3)   елементи розв'язувального рядка змінюють знак на протилежний і ділять на розв'язувальний елемент, так для комірки С27: =-C22/$B22. Скопіювати формулу у комірки розв'язного рядка D27, Е27;

4)   замість розв'язувального елемента береться його обернене значення В27:=1/B22.

10.    Таким же чином  виконуються інші кроки ЗЖВ. Після трьох кроків ЗЖВ у блоці В37:D39 буде знаходитися обернена матриця блоку В22:D24, а у блоці Е37:Е39 вектор оцінених параметрів (рис.2).

 

 

 

 

 

 

 

 

11.    Якщо допустити, що після двох кроків ЗЖВ значення наступного розв'язувального елемента близьке нулю, тоді можна вважати, що існує залежність між факторами, тобто мультиколінеарність. У цьому випадку одну із змінних слід виключити із розв’язку.

12.    Запишемо отримане рівняння =ln(a₀+ a₁/ x₁+ a₂ x₂) у блоці А40:J40.

13.    Знайдемо розрахункові значення для наведеної моделі, розмістивши значення у блоці G4:G17. Спочатку перше значення G4:=$E$37+$E$38*D4+$E$39*E4. Отриману формулу скопіюємо у інші комірки блоку (G5:G17). У комірці G18 розрахуємо суму комірок блоку G4:G16 (рис.1).

14.    Розрахункове значення фактора блоку Н4:Н17 знайдемо, пролагорифмуючи значення блоку G4:G17. У комірці Н18 розрахуємо суму комірок блоку Н4:Н16 (рис.1).

15.    Для визначення адекватності прийнятої моделі експериментальним даним у комірці G21 обчислимо розрахункове значення критерію Фишера, для цього введемо у комірку G21 формулу G21:=СТАНДОТКЛОН(H4:H16)^2*12*5/I18, де у комірці I18 попередньо розраховано суму квадратів різниці Y й Y_розр (рис.1)_, а у комірці G22 – табличне (F_(0,05;2;10)=4,103) (рис.2).

16.    Порівняємо отримані результати та зробимо висновок про адекватність прийнятої моделі експериментальним даним.

17.    Знайдемо оцінку прогнозного значення показника у комірці Н17 і внесемо його в таблицю прогнозних значень А46, ввівши дані прогнозу X_1, X₂ й Y у комірки А17, В17 і С17 відповідно (X_1п=1,5; X_2п=8; Y_п=1) (рис.1,3).

 

 

 

 

 

 

18.    Обчислимо значення частинних коефіцієнтів еластичності за формулами:

,  

Значення частинних коефіцієнтів еластичності обчислимо у комірках Е46, F46  таблиці прогнозних значень (блок А45: F46) (рис.3).

Знайдемо довірчий інтервал оцінки прогнозу. Для цього спочатку знайдемо довірчий інтервал для приведеної до лінійного виду регресії, а потім перетворимо межі довірчого інтервалу, використовуючи зворотні перетворення.

19.    Для розрахунку дисперсії використаємо формулу :

Матриця знайдена у блоці В37:D39, а значення координат вектора – у рядку С17:Е17. Використовуючи вбудовану функцію СУММПРОИЗВ, знайдемо добуток вектора-рядка та матриці (СУММПРОИЗВ вектор-рядок, рядок матриці). Результат добутку запишемо у блоці С41:Е41.

Результат матричного добутку   знайдемо у комірці С43, ввівши формулу С43:= СУММПРОИЗВ(C41:E41;C17:E17).

20.    Обчислимо значення  у комірці В46, ввівши формулу В46: =E43*КОРЕНЬ(K18*(1+C43)/(G20-4)), попередньо розрахувавши у комірці К18 суму квадратів різниці Y₁ й Y_1розр  (мал.3), а у комірці Е43 значення коефіцієнта t_ak, використовуючи вбудовану функцію СТЬЮДРАСПОБР (t(_0,05;₁₀₎=2,23).

21.    Знайдемо межі довірчого інтервалу у комірках С46 й D46 за формулами С46: =LN(G17-B46) і D46: =LN(G17+B46) (рис.3).

22.    Підвести підсумки лабораторної роботи і зробити висновки.

23.    Зберегти книгу у своїй робочій папці під ім'ям Лаб.7.

 

Висновки:

1.    Оскільки Fроз>Fтаб, то з надійністю Р=0,95 математичну модель  можна вважати адекватною експериментальним даним і на підставі прийнятої моделі можна проводити економічний аналіз.

2.    З надійністю р=0,95 можна вважати, що вплив факторів Х₁ і Х₂ на показник Y значний.

3.    Середнє значення прогнозу дорівнює 2,54  з надійністю р=0,95 буде знаходитися у проміжку (2,46; 2,61).

4.    Для прогнозу зміна фактора Х₁ на 1% при незмінних інших факторах викличе зміну показника у зворотному напрямку з на 0,064%, а зміна фактора Х₂ на 1% при незмінних інших факторах викличе зміну показника на 0,265% у тому ж напрямку.

 

 

 

Завдання до лабораторної роботи №7