Главная | Обратная связь | Поможем написать вашу работу!
МегаЛекции

Регрессионный анализ трехмерной модели.




 

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

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

 

 

Регрессионная статистика          
Множественный R 0,762322          
R-квадрат 0,581135          
Нормированный R-квадрат 0,563682          
Стандартная ошибка 50,23613          
Наблюдения            
Дисперсионный анализ        
  df SS MS F Значимость F  
Регрессия   168064,8 84032,39 33,2977 8,51E-10  
Остаток   121136,1 2523,668      
Итого   289200,9        
  Коэффициенты Стандартная ошибка t-статистика P-Значение Нижние 95% Верхние 95%
Y-пересечение 225,7848 27,41026 8,237239 9,67E-11 170,6728 280,8968
X8 23,38168 10,96783 2,131842 0,038166 1,329382 45,43398
X4 -503,93 69,72031 -7,22788 3,29E-09 -644,112 -363,748

Рис.8. Регрессия Y2 на X4,X8.

На рис.8 приведены результаты применения инструмента Регрессия к статистическим данным по признакам X4–X8–Y2.

Оценка линейной функции регрессии y2 на x4,x8 имеет вид:

Значение F–критерия Fрасч =33,2977, что значительно больше Fкр = 3,18 Это означает, что оценка достаточно хорошо согласуется с данными наблюдений. Это подтверждается и достаточно высоким значением коэффициента детерминации R2 = 0,5811351. Расчетные значения t –статистики для свободного члена и коэффициента при x4 больше tкр = 2,009, что подтверждает их значимость. Для коэффициента при x8 tрасч близко к критическому значению, что ставит под сомнение его значимость.

 

  A B C D E F H I
  X4 X8 Y2 P(x) ε ε2 P2 (x) ε22
  0,42 0,66 13,6 =A$56+B$56*A2+C$56* B2 =C2-D2 =E2^2 =A$59+B$59*A2+C$59*B2+D$59*A2^2+E$59*B2^2+F$59*A2*B2 =(C2-H2)^2
  0,51 1,23   =A$56+B$56*A3+C$56* B3 =C3-D3 =E3^2 =A$59+B$59*A3+C$59*B3+D$59*A3^2+E$59*B3^2+F$59*A3*B3 =(C3-H3)^2
  0,38 1,04 18,1 =A$56+B$56*A4+C$56* B4 =C4-D4 =E4^2 =A$59+B$59*A4+C$59*B4+D$59*A4^2+E$59*B4^2+F$59*A4*B4 =(C4-H4)^2
  0,51 0,24 21,9 =A$56+B$56*A5+C$56* B5 =C5-D5 =E5^2 =A$59+B$59*A5+C$59*B5+D$59*A5^2+E$59*B5^2+F$59*A5*B5 =(C5-H5)^2
  0,43 2,13 26,8 =A$56+B$56*A6+C$56* B6 =C6-D6 =E6^2 =A$59+B$59*A6+C$59*B6+D$59*A6^2+E$59*B6^2+F$59*A6*B6 =(C6-H6)^2
  0,43 0,84 30,1 =A$56+B$56*A7+C$56* B7 =C7-D7 =E7^2 =A$59+B$59*A7+C$59*B7+D$59*A7^2+E$59*B7^2+F$59*A7*B7 =(C7-H7)^2
  0,34 0,68 32,3 =A$56+B$56*A8+C$56* B8 =C8-D8 =E8^2 =A$59+B$59*A8+C$59*B8+D$59*A8^2+E$59*B8^2+F$59*A8*B8 =(C8-H8)^2
  0,18 1,06 34,2 =A$56+B$56*A9+C$56* B9 =C9-D9 =E9^2 =A$59+B$59*A9+C$59*B9+D$59*A9^2+E$59*B9^2+F$59*A9*B9 =(C9-H9)^2

Рис.9. Размещение информации для МНК.

 

В случае нелинейной регрессии необходимо выполнять действия, предусмотренные методом наименьших квадратов(МНК), используя вычислительные возможности Excel. Расположение исходных данных и формул в таблице Excel приведено на рис.9.

Все формулы вводятся только в верхнюю строку, а затем копируются по всему столбцу. На рис.9 приведены расчеты поиска оценок линейной P(x) и квадратичной P2 (x) функции регрессии. Параметры функции регрессии βj расположены в ячейках A56 ÷ C56 для линейной зависимости и в ячейках A59 ÷ F59 для квадратичной зависимости (см. рис.10). Ячейки F53 и I53 содержат значения функций Q – суммы квадратов отклонений.

 

 

  A B C D E F H I
  0,02 1,14 264,8 =A$56+ B$56*A50+ C$56*B50 =C50-D50 =E50^2 =A$59+B$59*A50+ C$59*B50+D$59*A50^2+E$59*B50^2+F$59*A50*B50 =(C50-H50)^2
  0,16 4,44 267,3 =A$56+ B$56*A51+ C$56*B51 =C51-D51 =E51^2 =A$59+B$59*A51+ C$59*B51+D$59*A51^2+E$59*B51^2+F$59*A51*B51 =(C51-H51)^2
  0,01 1,27 355,6 =A$56+ B$56*A52+ C$56*B52 =C52-D52 =E52^2 =A$59+B$59*A52+ C$59*B52+D$59*A52^2+E$59*B52^2+F$59*A52*B52 =(C52-H52)^2
          Q = =СУММ(F2: F52) Q2 = =СУММ(I2: I52)
          σ = =КОРЕНЬ(F53/51) σ2 = =КОРЕНЬ(I53/51)
  β0 β1 β2          
  225,78481426 -503, 23,381653963          
                 
  β0 β1 β2 β3 β4 β5    
  247,96413983 -930, 73,537978008 1009,39006400157 -4,446 -140,188    

Рис.10. Размещение информации для Поиска решения.

 

Значения βj находятся с помощью надстройки Excel Поиск решения по такому алгоритму:

– установить курсор на ячейке, содержащей значение функции Q (Q2);

Сервис – Поиск решения;

– в появившемся диалоговом окне Поиск решения (рис.11) проверить, стоит ли в поле Установить целевую ячейку адрес функции Q (Q2), и если нет, то ввести его;

– в поле Равной щелкнуть пункт минимальному значению;

– в поле Изменяя ячейки ввести диапазон ячеек, которые отведены для значений искомых параметров ;

– щелкнуть по кнопке Выполнить;

– если решение найдено, сообщение об этом появится в диалоговом окне, где нужно щелкнуть по пункту Сохранить найденное решение. Значения найдены и находятся в отведенных для них ячейках (рис.10).

Значение суммы квадратов отклонений найденной оценки функции регрессии от наблюденных значений результирующего признака, т.е. функции Q для линейной регрессии и функции Q2 для квадратичной регрессии, находятся в ячейках F53 и I53, линейная величина отклонений – в ячейке F54 и в ячейке I54.

 

 

Рис.11. Ввод информации для Поиска решения.

 

Таким образом, коэффициенты линейной функции регрессии P(x) следует считывать из ячеек A56,B56 и С56; коэффициенты нелинейной функции регрессии P2(x) – из ячеек A59 F59. Для рассматриваемого примера линейная функция регрессии совпадает с полученной с помощью инструмента Регрессия, а квадратичная

P2(x) = 247,9641 – 930,3571x4 + 73,538x8 + 1009,39x42 – 4,44689x82 – 140,1884x4x8

Проверка значимости полученной квадратичной оценки уравнения регрессии выполним так. Определим коэффициент корреляции значений эмпирической функции регрессии и выборочного среднего RyP2(x). Как видно из рис.12, коэффициент корреляции достаточно большой (0,80921). Выполним еще одну проверку значимости P2(x) с помощью коэффициента детерминации, для чего необходимо вычислить значения Sост, Sфакт.

Размещение нужных формул приведено на рис.12, а промежуточные результаты и значения коэффициента детерминации ниже. Поскольку коэффициент детерминации для случая квадратичной регрессии значительно превосходит коэффициент детерминации для случая линейной регрессии и имеет достаточно большое значение (0,472867), делаем вывод, что квадратичная регрессия достаточно хорошо согласуется со статистическими данными.

Выполним оценку значимости полученного приближения функции в целом с помощью критерия Фишера. Для этого найдем значения критерия Фишера по выборке для рассматриваемых двух видов зависимости (см. рис.12 и 13).

 

 

  R S
  RyP(x) RyP2(x)
  =КОРРЕЛ(C2:C52;D2:D52) =КОРРЕЛ(C2:C52;H2:H52)
 
Sост
Sост
     
  =F53/48 =I53/45
 
Sфакт
Sфакт
     
  =L53/48 =N53/45
  R2 R22
  =1-R5/ (R9 + R5) =1-S5/ (S9 + S5)
  Fрасч F2расч
  =R11*(51-2-1)/(1-R11)/2 =S11*(51-2-1)/(1-S11)/2
     
  Fкрит = 3,205

 

Рис.12.Расчетные формулы

Как видно, расчетное значение F-критерия для квадратичной зависимости значительно превосходит значение Fкрит ,что подтверждает ее значимость. Для линейной зависимости превышение Fрасч не столь велико, что делает снова-таки предпочтительнее квадратичную оценку регрессии y2 на x4 и x8 .

 

  K L M N O Q R S
1

 

  ^2   ^2     RyP(x) RyP2(x)
  66,0145 4357,91 52,4372 2749,66     0,762322 0,80921
  98,0407 9611,98 63,6085 4046,04      
Sост

 

 
Sост

 

  36,9723 1366,95 39,0068 1521,53        
  121,189 14686,7 59,1584 3499,72     2523,668 2218,362
  36,6828 1345,63 52,8333 2791,36      
Sфакт

 

 
Sфакт

 

  66,8451 4468,27 52,8975 2798,14        
  25,2325 636,678 31,6051 998,881        
  -64,2814 4132,09 -63,8871 4081,57     3501,349 4208,353
  3,56772 12,7286 14,147 200,138     R2 R22
  43,0760 1855,54 43,5092 1893,05     0,581135 0,654822
  -12,1715 148,144 4,46566 19,9421     Fрасч F2расч
  37,1816 1382,47 39,3711 1550,09     33,29771 45,5293
  68,8203 4736,24 53,556 2868,24        
  37,88307 1435,127 39,90716 1592,582     Fкрит = 3,205

Рис.13.Проверка значимости.

 

Таким образом, выборочное уравнение регрессии имеет вид:

 

 

V. Содержание и объем курсовой работы

Цель задания – выполнить статистический анализ результатов измерений и определить на основании исследования математической модели оценки законов и параметров распределения, корреляционные связи и статистические зависимости

1. Выполнить многомерный экономико-статистический анализ показателей (в соответствии с вариантом):

– проверить данные на засорение;

– проверить закон распределения всех параметров;

– оценить тесноту связей параметров;

– выполнить регрессионный анализ двумерных и трехмерной моделей.

2. Составить математическую модель задачи на основании корреляционного и регрессионного анализа статистических данных.

3. Работу выполнить средствами Excel и его надстроек Пакет анализа и Поиск решения.

4. Отчет по курсовой работе создать в среде Word с необходимыми по тексту результатами в виде таблиц и диаграмм Excel.

5. Отчет должен содержать:

– титульный лист;

– задание в соответствии с вариантом;

– названия и краткое описание, а также результаты каждого этапа выполнения задания;

– приложение в виде рабочей книги Excel, содержащей все выполненные расчеты (на дискете);

Варианты заданий приведены в приложении на странице 48.

 

 

VI. Литература

1. Магнус Я.Р., Катышев П.К., Пересецкий А.А. Эконометрика. Начальный курс. – М.: Дело, 1997. – 248 с.

2. Дубров А.М., Мхитарян В.С., Трошин Л.И. Многомерные статистические методы: Учебник. – М.: Финансы и статистика, 2000. – 352 с.

3. Колемаев В.А. и др. Теория вероятностей и математическая статистика: Учеб. пособие для экон. спец. вузов / В.А.Колемаев, О.В.Староверов, В.Б.Турундаевский; Под ред. В.А.Колемаева. – М.: Высш. шк.,!991. – 400 с.

4. Сивец С.А. Статистические методы в оценке недвижимости и бизнесе. Учебно-практическое пособие по статистике для оценщиков. – Запорожье, 2001. – 320 с.

5. Тюрин Ю.Н., Макаров А.А. Статистический анализ данных на компьютере / Под ред. Фигурнова В.Э. – М.: ИНФРА, 1998. – 528 с.

6. Савицкая Г.В. Анализ хозяйственной деятельности предприятия. – Минск: ООО «Новое знание», 2000. – 668 с.

7. Ларсен, Рональд У. Инженерные расчеты в Excel.: Пер. с англ. – М.: Издательский дом «Вильямс», 2004. – 544 с.

8. Гурман В.Е. Теория вероятностей и математическая статистика. Учеб. Пособие для втузов. М., «Высш. школа», 1977. – 479 с.

9. Математическая статистика: Учебник / Иванова В.М., Калинина В.Н., Нешумова Л.А. и др. – М.: Высш. школа, 1981. –371 с.

 

Приложение

Статистические данные.

 

№ п/п
  9,26 204,2 13,26 0,23 0,78 0,40 1,37 1,23 0,23
  9,38 209,6 10,16 0,24 0,75 0,26 1,49 1,04 0,39
  12,1 222,6 13,72 0,19 0,68 0,40 1,44 1,80 0,43
  10,8 236,7 12,85 0,17 0,70 0,50 1,42 0,43 0,18
  9,35 62,0 10,63 0,23 0,62 0,40 1,35 0,88 0,15
  9,87 53,1 9,12 0,43 0,76 0,19 1,39 0,57 0,34
  8,17 172,1 25,83 0,31 0,73 0,25 1,16 1,72 0,38
  9,12 56,5 23,39 0,26 0,71 0,44 1,27 1,70 0,09
  5,88 52,6 14,68 0,49 0,69 0,17 1,16 0,84 0,14
  6,30 46,6 10,05 0,36 0,73 0,39 1,25 0,60 0,21
  6,22 53,2 13,99 0,37 0,68 0,33 1,13 0,82 0,42
  5,49 30,1 9,68 0,43 0,74 0,25 1,10 0,84 0,05
  6,50 146,4 10,03 0,35 0,66 0,32 1,15 0,67 0,29
  6,61 18,1 9,13 0,38 0,72 0,02 1,23 1,04 0,48
  4,32 13,6 5,37 0,42 0,68 0,06 1,39 0,66 0,41
  7,37 89,8 9,86 0,30 0,77 0,15 1,38 0,86 0,62
  7,02 62,5 12,62 0,32 0,78 0,08 1,35 0,79 0,56
  8,25 46,3 5,02 0,25 0,78 0,20 1,42 0,34 1,76
  8,15 103,5 21,18 0,31 0,81 0,20 1,37 1,60 1,31
  8,72 73,3 25,17 0,26 0,79 0,30 1,41 1,46 0,45
  6,64 76,6 19,40 0,37 0,77 0,24 1,35 1,27 0,50
  8,10 73,01 21,0 0,29 0,78 0,10 1,48 1,58 0,77
  5,52 32,3 6,57 0,34 0,72 0,11 1,24 0,68 1,20
  9,37 199,6 14,19 0,23 0,79 0,47 1,40 0,86 0,21
  13,1 598,1 15,81 0,17 0,77 0,53 1,45 1,98 0,25
  6,67 71,2 5,23 0,29 0,80 0,34 1,40 0,33 0,15
  5,68 90,8 7,99 0,41 0,71 0,20 1,28 0,45 0,66
  5,22 82,1 17,50 0,41 0,79 0,24 1,33 0,74 0,74
  10,0 76,2 17,16 0,22 0,76 0,54 1,22 0,03 0,32
  8,16 119,5 14,54 0,29 0,78 0,40 1,28 0,99 0,89
  3,78 21,9 6,24 0,51 0,62 0,20 1,47 0,24 0,23
  6,48 48,4 12,08 0,36 0,75 0,64 1,27 0,57 0,32
  10,4 173,5 9,49 0,23 0,71 0,42 1,51 1,22 0,54
  7,65 74,1 9,28 0,26 0,74 0,27 1,46 0,68 0,75
  8,77 68,6 11,42 0,27 0,65 0,37 1,27 1,00 0,16
  7,00 60,8 10,31 0,29 0,66 0,38 1,43 0,81 0,24
  11,0 355,6 8,65 0,01 0,84 0,35 1,50 1,27 0,59
  9,02 264,8 10,94 0,02 0,74 0,42 1,35 1,14 0,56
  13,2 526,6 9,87 0,18 0,75 0,32 1,41 1,89 0,63
  9,27 118,6 6,14 0,25 0,75 0,33 1,47 0,67 1,10
  6,70 37,1 12,93 0,31 0,79 0,29 1,35 0,96 0,39
  6,69 57,7 9,78 0,38 0,72 0,30 1,40 0,67 0,73
  9,42 51,6 13,72 0,24 0,70 0,56 1,20 0,98 0,28
  7,24 64,7 17,29 0,31 0,66 0,42 1,15 1,16 0,10
  5,39 48,3 7,11 0,42 0,69 0,26 1,09 0,54 0,68
  5,61 15,0 22,49 0,51 0,71 0,16 1,26 1,23 0,87
  5,59 87,5 12,14 0,31 0,73 0,45 1,36 0,78 0,49
  6,57 108,4 15,25 0,37 0,65 0,31 1,15 1,16 0,16
  6,54 267,3 31,34 0,16 0,82 0,08 1,87 4,44 0,85
  4,23 34,2 11,56 0,18 0,80 0,68 1,17 1,06 0,13
  5,22 26,8 30,14 0,43 0,83 0,03 1,61 2,13 0,49
  18,0 43,6 19,71 0,40 0,70 0,02 1,34 1,21 0,09
  11,0 72,0 23,56 0,31 0,74 0,22 1,22 2,20 0,79

 

Продолжение таблицы

 

№ п/п
  1,45   167,69   6,40 166,32 10,08 17,72
  1,30   186,10   7,80 92,88 14,76 18,39
  1,37   220,45   9,76 158,04 6,48 26,46
  1,65   169,30   7,90 93,96 21,96 22,37
  1,91   39,53   5,35 173,88 11,88 28,13
  1,68   40,41   9,90 162,30 12,60 17,55
  1,94   102,96   4,50 88,56 11,52 21,92
  1,89   37,02   4,88 101,16 8,28 19,52
  1,94   45,74   3,46 166,32 11,52 23,99
  2,06   40,07   3,60 140,76 32,40 21,76
  1,96   45,44   3,56 128,52 11,52 25,68
  1,02   41,08   5,65 177,84 17,28 18,13
  1,85   136,14   4,28 114,48 16,20 25,74
  0,88   42,39   8,85 93,24 13,32 21,21
  0,62   37,39   8,52 126,72 17,28 22,97
  1,09   101,78   7,19 91,80 9,72 16,38
  1,60   47,55   4,82 69,12 16,20 13,21
  1,53   32,61   5,46 66,24 24,84 14,48
  1,40   103,25   6,20 67,68 14,76 13,38
  2,22   38,95   4,25 50,40 7,56 13,69
  1,32   81,32   5,38 70,56 8,64 16,66
  1,48   67,26   5,88 72,00 8,64 15,06
  0,68   59,92   9,27 97,20 9,00 20,09
  2,30   107,34   4,36 80,28 14,76 15,98
  1,37   512,60   10,3 51,48 10,08 18,27
  1,51   53,81   4,69 105,12 14,76 14,42
  1,43   80,83   4,16 128,52 10,44 22,76
  1,82   59,42   3,13 94,68 14,76 15,41
  2,62   36,96   4,02 85,32 20,52 19,35
  1,75   91,43   5,23 76,32 14,40 16,83
  1,54   17,16   2,74 153,00 24,84 30,53
  2,25   27,29   3,10 107,64 11,16 17,98
  1,07   184,33   10,4 90,72 6,48 22,09
  1,44   58,42   5,65 82,44 9,72 18,29
  1,40   59,40   6,67 79,92 3,24 26,05
  1,31   49,63   5,91 120,96 6,48 26,20
  1,12   391,27   12,0 84,60 5,40 17,26
  1,16   258,62   8,30 85,32 6,12 18,83
  0,88   75,66   1,63 101,52 8,64 19,70
  1,07   123,68   8,94 107,64 11,88 16,87
  1,24   37,21   5,82 85,32 7,92 14,63
  1,49   53,37   4,80 131,76 10,08 22,17
  2,03   32,87   5,01 116,64 18,72 22,62
  1,84   45,63   4,12 138,24 13,68 26,44
  1,22   48,41   5,10 156,96 16,56 22,26
  1,72   13,58   3,49 137,52 14,76 19,13
  1,75   63,99   4,19 135,72 7,92 18,28
  1,46   104,55   5,01 155,52 18,36 28,23
  1,60   222,11   11,4 48,60 8,28 12,39
  1,47   25,76   7,67 42,84 14,04 11,64
  1,38   29,52   4,66 142,20 16,92 8,62
  1,41   41,99   4,30 145,80 11,16 20,10
  1,39   78,11   6,62 120,52 14,76 19,41
№ вар. Результативный признак Факторные признаки, X   № вар. Результативный признак Факторные признаки, X  
  Y1 Y1 Y1 Y1 Y1 Y1 Y3 Y3 Y3 Y2 Y1 Y1 Y1 Y1 Y1 Y1 Y1 Y1   6,8,11,12,17 8,11,12,13,17 7,9,12,13,17 8,9,13,14,17 5,6,7,9,17 5,7,10,14,17 8,10,15,16,17 5,6,7,11,12 8,9,10,11,17 4,5,6,8,9 6,8,11,13,17 6,8,13,14,17 8,11,13,14,17 6,8,12,13,17 7,11,12,13,17 8,11,12,13,17 5,7,9,11,17 5,6,12,13,17     Y1 Y1 Y1 Y1 Y3 Y3 Y3 Y3 Y3 Y2 Y2 Y2 Y2 Y3 Y3 Y3 Y3     5,6,10,14,17 5,6,10,15,17 5,6,7,11,12 8,9,10,11,17 5,6,7,11,12 8,9,10,12,17 4,5,6,8,9 4,5,6,7,9 7,9,12,13,17 4,5,6,8,9 4,5,6,7,9 4,5,8,9,17 4,5,7,9,17 8,11,12,13,17 6,8,12,13,17 6,8,12,13,17 8,11,13,14,17

Варианты заданий к работе

«Статистический анализ»

Примечание. В таблице указаны индексы факторных признаков X. Так, для варианта 1 следует выполнить статистический анализ всех 53 значений показателей Y1, X6, X8, X 11, X12, X17.

 

Поделиться:





Воспользуйтесь поиском по сайту:



©2015 - 2024 megalektsii.ru Все авторские права принадлежат авторам лекционных материалов. Обратная связь с нами...