Реализация задания на компьютере с помощью ППП Excel

В качестве заданий по этой теме используется статистический материал, представленный в теме «Парная регрессия». Предлагается каждому студенту попробовать описать функционально связь между экономическими показателями не только линейной функцией (что было сделано в первой части задания), но и рядом нелинейных моделей, а именно:

1. Полиномом второго порядка

Реализация задания на компьютере с помощью ППП Excel - student2.ru ;

2. Степенной функцией

Реализация задания на компьютере с помощью ППП Excel - student2.ru ;

3. Показательной функцией

Реализация задания на компьютере с помощью ППП Excel - student2.ru ;

4. Равносторонней гиперболой

Реализация задания на компьютере с помощью ППП Excel - student2.ru .

По результатам оценивания моделей выбирается наилучшая модель с точки зрения достоверности статистическим данным.

Для оценивания показательной модели можно использовать стандартную функцию ЛГРФПРИБЛ (смотри приложение «Стандартные функции»). Эта функция, как и функция ЛИНЕЙН возвращает статистику по регрессии. Порядок регрессионной статистики в выходном массиве такой же, как у ЛИНЕЙН (рис. 3.1)

Реализация задания на компьютере с помощью ППП Excel - student2.ru

Рис.3.1.

Для оценивания остальных моделей необходимо использовать функцию ЛИНЕЙН.

Пример решения задания на компьютере представлен ниже.

Имеется информация по однотипным предприятиям торговли о сроках эксплуатации типового оборудования и затратах на его ремонт:

№ предприятия
Срок эксплуатации (лет)
Затраты (тыс. руб.) 1,5 2,0 1,4 2,3 2,7 4,0 2,3 2,5 6,6 1,7

В целях нормирования расходования средств необходимо подобрать наиболее адекватную статистическим данным экономико-математическую модель из следующих функций:

1) Линейной;

2) Полином 2-го порядка;

3) Степенной;

4) Показательной;

5) Равносторонней гиперболы;

Результаты решения этой задачи с использованием ППП Ехсеl представлены на рисунках 3.2 - 3.6.

Реализация задания на компьютере с помощью ППП Excel - student2.ru


1. Y = b0 + b1*X      
         
х у умод е Tb1 Tb0 Rкв
1,5 0,867391 0,6326087 5,3969895 -1,9019578 0,78452627
1,478261 0,5217391      
1,4 1,478261 -0,078261 F Ср.ош А,% Прогн,V,%
2,3 2,08913 0,2108696 29,127496 21,5868577 28,4323048
2,7 3,31087 -0,61087  
4,532609 -0,532609  
2,3 3,31087 -1,01087  
2,5 2,7 -0,2  
6,6 5,143478 1,4565217  
1,7 2,08913 -0,38913  
         
Статист. Лин.      
0,61087 -1,57609      
0,113187 0,828666      
0,784526 0,767672      
29,1275      
17,16543 4,714565 Реализация задания на компьютере с помощью ППП Excel - student2.ru    
Al = 0,1
Fкр Ткр
3,45791307 2,3060056

Рис. 3.2


2. Y = b0 + b1*X + b2*X^2 X1=x
X2=x^2
 
           
X1 X2 Y Yмод е  
1,5 1,8141436 -0,3141436 Tb2 Tb1 Tb0 Rкв  
1,6652062 0,3347938 3,564425 -2,39118 2,593683 0,923455  
1,4 1,6652062 -0,2652062  
2,3 1,7677157 0,5322843 F Ср.ош А,% Прогн,V,%
2,7 2,7270754 -0,0270754 42,225118 14,40654772 18,1162156  
4,6922228 -0,6922228  
2,3 2,7270754 -0,4270754  
2,5 2,1216721 0,3783279  
6,6 6,0519669 0,5480331  
1,7 1,7677157 -0,0677157  
           
Статист. Лин.
Реализация задания на компьютере с помощью ППП Excel - student2.ru

 
0,125723 -1,28045 4,924362  
0,035272 0,535488 1,898598  
0,923456 0,489138 #Н/Д  
42,22512 #Н/Д  
20,20521 1,674791 Реализация задания на компьютере с помощью ППП Excel - student2.ru  
                             

Реализация задания на компьютере с помощью ППП Excel - student2.ru

Рис. 3.3


3. Y = b0*x^b1 Y = lny B0 = lnb0 X = lnx      
               
х у Y X Yмод умод Е е
1,5 0,405465 1,3862944 0,2115272 1,235564 0,193938 0,2644364
0,693147 1,6094379 0,5028681 1,653457 0,190279 0,3465433
1,4 0,336472 1,6094379 0,5028681 1,653457 -0,1664 -0,2534567
2,3 0,832909 1,7917595 0,7409109 2,097846 0,091998 0,2021544
2,7 0,993252 2,0794415 1,1165146 3,054191 -0,12326 -0,3541906
1,386294 2,3025851 1,4078555 4,087181 -0,02156 -0,0871809
2,3 0,832909 2,0794415 1,1165146 3,054191 -0,28361 -0,7541906
2,5 0,916291 1,9459101 0,9421732 2,565551 -0,02588 -0,0655509
6,6 1,88707 2,3978953 1,5322944 4,628785 0,354775 1,971215
1,7 0,530628 1,7917595 0,7409109 2,097846 -0,21028 -0,3978456
            СумЕкв Сумeкв
Статист. Лин.         0,376814 5,0452469
1,305621 -1,59845   b0 b1 Tb1 Tb0 Rкв F
0,224728 0,432331   0,2022102 1,3056208 5,809771 -3,6972812 0,769412 26,69404
0,808399 0,217029        
33,75344   5,0452469 Реализация задания на компьютере с помощью ППП Excel - student2.ru Ср.ош А,% Прогн,V,% Sкв
1,589847 0,376814       16,58298291 29,4125349 0,6306559
                     

>

Реализация задания на компьютере с помощью ППП Excel - student2.ru

Рис. 3.4


4. Y=b0*b1^x c использованием стандартной функции ЛГРФПРИБЛ  
х у lny lnумод умод еln е  
1,5 0,405465 0,3013904 1,351737 0,104075 0,148263  
0,693147 0,4947415 1,6400743 0,198406 0,359926  
1,4 0,336472 0,4947415 1,6400743 -0,15827 -0,24007  
2,3 0,832909 0,6880926 1,9899164 0,144816 0,310084  
2,7 0,993252 1,0747949 2,9293919 -0,08154 -0,22939  
1,386294 1,4614971 4,3124107 -0,0752 -0,31241  
2,3 0,832909 1,0747949 2,9293919 -0,24189 -0,62939  
2,5 0,916291 0,8814438 2,414383 0,034847 0,085617  
6,6 1,88707 1,6548482 5,2322856 0,232221 1,367714  
1,7 0,530628 0,6880926 1,9899164 -0,15746 -0,28992  
Статист. Нелин.      
СумЕlnкв
СумЕкв
1,213309 0,246967      
0,246967
2,813696
0,025906 0,189661          
0,874423 0,175701    
 
Sкв(ln) Sкв
0,0308709 0,35171198
S(ln) S
0,1757011 0,5930531
Sb1(ln) Sb1
0,0259057 0,08744091
Sb0(ln) Sb0
0,1896609 0,40982091
Tb1(ln) Tb1
7,4636488 13,8757561
Tb0(ln) Tb0
-2,4887256 1,52199351
Rкв(ln) Rкв
0,8744232 0,8714033
R(ln) R
0,935106 0,93348985
Rxy(ln) Rxy
0,935106 0,88573488
F(ln) F
55,706053 54,2099939

 
55,70605        
1,719694 0,246967   2,813696 Реализация задания на компьютере с помощью ППП Excel - student2.ru  
                             

   
Ср.ош А,% Прогн,V,%
14,338321 21,96493
Реализация задания на компьютере с помощью ППП Excel - student2.ru

Рис. 3.5


5. Y = b0 + b1*1/x   X = 1/x  
         
х у X Yмод е
1,5 0,25 0,5035001 0,9964999
0,2 1,6807425 0,3192575
1,4 0,2 1,6807425 -0,2807425
2,3 0,166667 2,4655708 -0,1655708
2,7 0,125 3,4466061 -0,7466061
0,1 4,0352273 -0,0352273
2,3 0,125 3,4466061 -1,1466061
2,5 0,142857 3,0261624 -0,5261624
6,6 0,090909 4,2492714 2,3507286
1,7 0,166667 2,4655708 -0,7655708
Статист. Лин.  
-23,5448

Tb1 Tb0 Rкв F Ср.ош А,% Прогн,V,%
-3,239832 5,3710378 0,567486 10,49651 28,973034 40,282365
6,389712

 
7,267305 1,189661  
0,567486 1,087624  
10,49651  
12,41659 9,463405  

Реализация задания на компьютере с помощью ППП Excel - student2.ru

Реализация задания на компьютере с помощью ППП Excel - student2.ru

Рис. 3.6

Поясним некоторые обозначения, используемые на рисунках:

- параметры с индексами «мод» (Yмод,…) означают модельные (оценки) значения этих параметров;

- «ln» - логарифмы соответствующих переменных;

- «Статист.лин.» - результаты использования функции ЛИНЕЙН;

- «Статист.нелин.» - результаты использования функции ЛГРФПРИБЛ;

- «Ср.Ош.А» - относительная ошибка аппроксимации в процентах, вычисляемая по формуле: Реализация задания на компьютере с помощью ППП Excel - student2.ru ;

- «Прогн.V» - относительная ошибка прогноза в процентах, реализуемая соотношением: Реализация задания на компьютере с помощью ППП Excel - student2.ru , где S – стандартная ошибка регрессии; Реализация задания на компьютере с помощью ППП Excel - student2.ru - среднее значение Y.

Параметры A и V используются для выбора «наилучшей» модели аппроксимации в случае примерного равенства основных критериев качества уравнений регрессии.

Результатом выполнения данного задания является выбор наиболее адекватной статистическим данным математической модели. В выводах должны быть приведены статистические критерии, обосновывающие данный выбор.

Вопросы для подготовки к защите индивидуального задания

1. Приведите примеры нелинейных моделей, используемых в эконометрике.

2. Какие из известных вам типов нелинейных моделей поддаются непосредственной линеаризации?

3. Как линеаризуются модели гиперболического вида?

4. Как линеаризуются модели экспоненциального вида?

5. Как линеаризуются модели степенного вида?

6. Как линеаризуются модели логарифмического вида?

7. Каковы признаки качественной регрессионной модели?

8. Назовите основные виды ошибок спецификации.

9. Как можно обнаружить ошибки спецификации?

10. Можно ли обнаружить ошибки спецификации с помощью исследования остаточного члена?

11. В чем суть теста Рамсея?

Глава IV

Наши рекомендации