Файл: Курсовая работа по дисциплине Введение в информационные технологии.docx

сводиться к решению системы (3).

В случае линейной формулы зависимости система (3) примет вид:

(4)

В случаи квадратичной зависимости система (3) примет вид:

(5)

Экспоненциальная зависимость:

(6)

Где и - неопределённые коэффициенты.

Линеаризация достигается путём логарифмирования равенства (6), после чего получим соотношение.

(7)

Коэффициент корреляции вычисляется по формуле:

(8)

Где , , и - среднее арифметическое значения соответственно по x и y.

Коэффициент детерминированности (детерминации) определяться по формуле:

(9)

(10)

(11)

; (12)

, (13)

Где m- число параметров при переменных x.

Для линейной и экспоненциальной аппроксимации m=1, для квадратичной аппроксимации m=2.

Критерий Фишера определяться соотношение:

(14)

(15)

Для линейной и экспоненциальной функции формула имеет вид:

. (16)

Для параболы формула F- критерия будет:

---

(17)

Стандартная ошибка коэффициента регрессии определяется по формуле:

. (18)

Стандартная ошибка параметра :

. (19)

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

(20)

(21)

(22)

где |A| - определитель матрицы .

---

2)Расчёт аппроксимации и построение графиков MS Excel

2.1)Расчет аппроксимации

Сначала проведем расчеты, используя средства табличного процессора MS Excel. Данные представлены на Рисунке 1 и Рисунке 2.


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



Рис.2. Вычисление вспомогательных сумм и средних значений
Пояснения к расчетам:
Шаг 1. В ячейки A3:A16 заносим значения y(i).
Шаг 2. В ячейки B3:B16 заносим значения x(i).
Шаг 3. В ячейку C3 вводим формулу =B3^2.
Шаг 4. В ячейки C4:C16 эта формула копируется.
Шаг 5. В ячейку D3 вводим формулу =B3*A3.
Шаг 6. В ячейки D4:D16 эта формула копируется.
Шаг 7. В ячейку E3 вводим формулу =B3^3.
Шаг 8. В ячейки E4:E16 эта формула копируется.
Шаг 9. В ячейку F3 вводим формулу =B3^4.
Шаг 10. В ячейки F4:F16 эта формула копируется.
Шаг 11. В ячейку G3 вводим формулу =B3^2*A3.
Шаг 12. В ячейки G4:G16 эта формула копируется.
Шаг 13. В ячейку H3 вводим формулу =LN(A3)
Шаг 14. В ячейки H4:H16 эта формула копируется.
Шаг 15. В ячейку I3 вводим формулу =B3*LN(A3).
Шаг 16. В ячейки I4:I16 эта формула копируется.
Последующие шаги делаем с помощью автосуммирования ∑ .
Шаг 17. В ячейку A17 вводим формулу =СУММ(A3:A16).
Шаг 18. В ячейку B17 вводим формулу =СУММ(B3:B16).
Шаг 19. В ячейку C17 вводим формулу =СУММ(C3:C16).
Шаг 20. В ячейку D17 вводим формулу =СУММ(D3:D16).
Шаг 21. В ячейку E17 вводим формулу =СУММ(E3:E16).
Шаг 22. В ячейку F17 вводим формулу =СУММ(F1:F16).
Шаг 23. В ячейку G17 вводим формулу =СУММ(G3:G16).
Шаг 24. В ячейку H17 вводим формулу =СУММ(H3:H16).
Шаг 25. В ячейку I17 вводим формулу =СУММ(I3:I16).
Далее вычисляем средние значения:
Шаг 26. В ячейку B18 вводим формулу =СЧЁТ(B3:B16).
Шаг 27. В ячейку A19 вводим формулу =A17/$B$18.
Шаг 28. В ячейку B19 вводим формулу =B17/$B$18.
Шаг 29. В ячейку H18 вводим формулу =H17/$B$18.
Продолжаем заполнение таблицы (рис, 3).
Шаг 30. В ячейку J3 вводим формулу =(B3-$B$19)*(A3-$A$19).
Шаг 31. В ячейки J4:J16 эта формула копируется.
Шаг 32. В ячейку K3 вводим формулу =(B3-$B$19)^2.
Шаг 33. В ячейки K4:K16 эта формула копируется.
Шаг 34. В ячейку L3 вводим формулу =(A3-$A$19)^2.
Шаг 35. В ячейки L4:L16 эта формула копируется.
Последующие шаги делаем с помощью автосуммирования ∑ .
Шаг 36. В ячейку J17 вводим формулу =СУММ(J3:J16).
Шаг 37. В ячейку K17 вводим формулу =СУММ(K3:K16).
Шаг 38. В ячейку L17 вводим формулу =СУММ(L3:L16).


На рисунке 3 представлены расчеты для

---

линейной аппроксимации



Рис.3. Фрагмент рабочего листа MS Excel для линейной аппроксимации

Шаг 39. В ячейку A22 вводим формулу =$B$18.
Шаг 40. В ячейку B22 вводим формулу =B17.
Шаг 41. В ячейку A23 вводим формулу =B17.
Шаг 42. В ячейку B23 вводим формулу =C17.
Шаг 43. В ячейку C22 вводим формулу =A17.
Шаг 44. В ячейку C23 вводим формулу =D17.
Шаг 45. Выделяем ячейки A26:B27 и вводим формулу
{=МОБР(A22:B23)}.
Шаг 46. Выделяем ячейки E26:E27 и вводим формулу
{=МУМНОЖ(A26:B27;C22:C23)}.
Шаг 47. В ячейку E24 вводим формулу =J17/(K17*L17)^(1/2).
Шаг 48. В ячейку M3 вводим формулу =(A3-($E$26+$E$27*B3))^2.

19
Шаг 49. В ячейки M4:M16 эта формула копируется.
Шаг 50. В ячейку M17 вводим формулу =СУММ(M3:M16).
Шаг 51. В ячейку E25 вводим формулу =1-M17/L17.
Шаг 52. В ячейку B29 вводим число 5,12
11 =df и 1222 =−= ndf в табл. 46 из приложения 1).
Шаг 53. В ячейку B30 вводим формулу =E25*(B18-2)/(1-E25).
Шаг 54. В ячейку B31 вводим формулу =M17/(B18-2).
Шаг 55. В ячейку B32 вводим формулу
=((B31*C17)/(B18*K17))^(1/2).
Шаг 56. В ячейку B33 вводим формулу =(B31/K17)^(1/2).
Шаг 57. В ячейку C30 вводим формулу
=ЕСЛИ(B30>B29;"Уравнение значимо";"Уравнение не значимо").
Шаг 58. В ячейку D31 вводим число 1,8125
Шаг 59. В ячейку D32 вводим формулу =ABS(E26)/B32.
Шаг 60. В ячейку D33 вводим формулу =ABS(E27)/B33.
Шаг 61. В ячейку F32 вводим формулу
=ЕСЛИ(D32>$D$31;"значим";"не значим").
Шаг 62. В ячейку F33 вводим формулу
=ЕСЛИ(D33>$D$31;"значим";"не значим").

При этом согласно критерию Фишера – Снедекора, уравнение линейной регрессии значимо и коэффициент этого уравнения согласно критерия Стьюдента тоже значим.

Расчёты для квадратичной аппроксимации представлены на рисунке 4.



Рис.4. Фрагмент рабочего листа MS Excel для квадратичной аппроксимации

Шаг 63. В ячейку A35 вводим формулу =$B$18.
Шаг 64. Шаг 86. В ячейку A36 вводим формулу =B17.
Шаг 65. Шаг 88. В ячейку A37 вводим формулу =C17.
Шаг 66. В ячейку B35 вводим формулу =B17.
Шаг 67 В ячейку B36 вводим формулу =C17.
Шаг 68 В ячейку B37 вводим формулу =E17.
Шаг 69. В ячейку C35 вводим формулу =C17.
Шаг 70. В ячейку C36 вводим формулу =E17.
Шаг 71. В ячейку C37 вводим формулу =F17.
Шаг 72. В ячейку D35 вводим формулу =A17.
Шаг 73. В ячейку D36 вводим формулу =D17.
Шаг 74. В ячейку D37 вводим формулу =G17.
Шаг 75. Выделяем ячейки A40:C42 и вводим формулу

---

{=МОБР(A35:C37)}.
Шаг 76. Выделяем ячейки F40:F42 и вводим формулу
{ =МУМНОЖ(A40:C42;D35:D37)}.
Шаг 77. В ячейку N3 вводим формулу
=(A3-($F$40+$F$41*B3+$F$42*B3^2))^2.
Шаг 78. В ячейки N4:N16 эта формула копируется.

21
Шаг 79. В ячейку N17 вводим формулу =СУММ(N3:N16).
Шаг 80. В ячейку F38 вводим формулу =1-N17/L17.
Шаг 81. В ячейку B44 вводим число 5,32
Шаг 82. В ячейку B45 вводим формулу =F38*(B18-3)/(2*(1-F38)).
Шаг 83. В ячейку B46 вводим формулу =N17/(B18-3)
Шаг 84. В ячейку B47 вводим формулу
=((N17/($B$18-3))*A40)^(1/2).
Шаг 85. В ячейку B48 вводим формулу
=((N17/($B$18-3))*B41)^(1/2).
Шаг 86. В ячейку B49 вводим формулу
=((N17/($B$18-3))*C42)^(1/2).
Шаг 87. В ячейку C45 вводим формулу
=ЕСЛИ(B45>B44;"Уравнение значимо";"Уравнение не значимо").
Шаг 88. В ячейку C45 вводим формулу
Шаг 89. В ячейку D46 вводим число 1,8331
Шаг 90. В ячейку D47 вводим формулу =ABS(F40)/B47.
Шаг 91. В ячейку D48 вводим формулу =ABS(F41)/B48.
Шаг 92. В ячейку D49 вводим формулу =ABS(F42)/B49.
Шаг 93. В ячейку F47 вводим формулу
=ЕСЛИ(D47>$D$46;"значим";"не значим").
Шаг 94. В ячейку F48 вводим формулу
=ЕСЛИ(D48>$D$46;"значим";"не значим").
Шаг 95. В ячейку F49 вводим формулу
=ЕСЛИ(D49>$D$46;"значим";"не значим").

Согласно критерию Фишера – Снедекора, уравнение квадратичной регрессии (24) значимо. Два коэффициента этого уравнения и согласно критерию Стьюдента не значимы, а третий коэффициент , - значим.

Расчёты для экспоненциальной аппроксимации представлены на рисунке 5.



Рис.5. Фрагмент рабочего листа MS Excel для экспоненциальной аппроксимации

Шаг 92. В ячейку А51 вводим формулу =$B$18.

Шаг 93. В ячейку В51 водим формулу =В17.

Шаг 94. В ячейку А52 вводим формулу =В17.

Шаг 95. В ячейку В52 вводим формулу =С17.

Шаг 96 В ячейку С51 вводим формулу =Н17.

Шаг 97. В ячейку С51 вводим формулу =I17.

Шаг 98. Выделяем ячейку А55:В56 и вводим формулу

{=МОБР(А51:В52)}.

Шаг 99. Выделяем ячейку Е54:Е55 и вводим формулу

{=МУМНОЖ(А55:В56;С51:С52)}.

Шаг 100. В ячейку Е56 вводим формулу =EXP(Е54).

Шаг 101. В ячейку Е53 вводим формулу =1-P17/O17.

Шаг 102. В ячейку В58 вводим формулу =В29.

Шаг 103. В ячейку В59 вводим формулу =E53*(B18-2)/(1-E56).

Шаг 104. В ячейку В60 вводим формулу =Р17/(В18-2)).

Шаг 105. В ячейку В61 вводим формулу =(P17/(($B$18-2)*K17))^(1/2)

Шаг 106. В ячейку В62 вводим формулу

=((P17*C17)/(($B$18-2)*$B$18*K17))^(1/2).

Шаг 107. В ячейку С59 вводим формулу =ЕСЛИ(В59<В59;”значим”;”не значим”).

---