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

Шаг 108. В ячейку D60 вводим формулу =D31.Шаг 109. В ячейку D61 вводим формулу =ABS(E55)/B61.Шаг 110. В ячейку D62 вводим формулу =ABS(E56)/B62.Шаг 111. В ячейку F61 вводим формулу =ЕСЛИ(D58<$D$60;”значим”;”не значим”).Шаг 112. В ячейку F62 вводим формулу=ЕСЛИ(D62<$D$60;”значим”;”не значим”).Согласно критерию Фишера – Снедекора, уравнение экспоненциальной регрессии значимо. Первый коэффициент этого уравнения, согласно критерию Стьюдента, значим, второй - нет.Таким образом уравнения аппроксимаций имеют вид:Для линейной: y = 0,0061x - 0,0996Для квадратичной: y = 8E-05x² - 0,0021x + 0,0489Для экспоненциальной: y = 0,0192e^0,0373x

---

2.2)Представление результатов в виде графиков. Построение линии тренда.

Результаты построения линии тренда представлены на рисунках 5, 6, 7.Рис. 5. Исходные точки и линия тренда для линейной аппроксимации.Рис. 6. Исходные точки и линия тренда для квадратичной аппроксимации.Рис. 7. Исходные точки и линия тренда для экспоненциальной аппроксимации.Сравнивая данные результаты с результатами, полученными ранее вручную с использованием основных расчётных формул, видим, что они полностью совпадают. Это указывает на то, что вычисления верны.

2.3)Получение числовых характеристик с использованием функции ЛИНЕЙН и ЛГРФПРИБЛ

Результаты расчётов числовых характеристик представлены на рисунках 8,9,10.Рис. 8. – Фрагмент рабочего листа MS Excel использования функции ЛИНЕЙН для линейной аппроксимацииЗдесь в интервале ячеек G20:Н24 введена формула =ЛИНЕЙН(А4:А13;В4:В13;;ИСТИНА). В ячейках E26 и E27 расположены соответственно значения коэффициентов и В ячейках G21:Н21 расположены соответственно значения стандартных ошибок коэффициентов и Рис.9. Фрагмент рабочего листа MS Excel использования функции ЛИНЕЙН для квадратичной аппроксимацииЗдесь в интервале ячеек G37:I41 введена формула =ЛИНЕЙН(A3:A13;B3:C13;;ИСТИНА).В ячейках G35, H35 и I35 расположены соответственно значения коэффициентов

---

и

В ячейках F40, F41 и F42 расположены соответственно значения стандартных ошибок коэффициента и



Рис.10. Фрагмент рабочего стола MS Excel использование функции ЛГРФПРИБЛ для экспоненциальной аппроксимации
Здесь в интервал ячеек G52:H56 введена формула

=ЛГРФПРИБЛ(A3:A13;B3:B13;;ИСТИНА).

В ячейке E56 расположено значения коэффициента

Сравнивая результаты, полученные с помощью функции ЛИНЕЙН и ЛГРФПРИБЛ, с результатами полученными вручную с использованием основных расчётов формул, можно видеть , что они полностью совпадают. Это указывает на то, что вычисления верны.

Лучше всего результаты испытаний, аппроксимирует квадратичная функция y = 8E-05x² - 0,0021x + 0,0489 т.к коэффициент детерминированности квадратичной аппроксимации выше. Полученное соотношение является простым и пригодным для анализа и прогноза, Значения числовых характеристик вычислялись разными способами и все результаты совпали, что свидетельствует об их правильности.

2.4)Вычисление прогнозного значения

Прогноз значение давления взрыва при заданной концентрации пыли вычисляем с помощью функции наиболее хорошо аппроксимирующей табличные данные. Результаты вычислений прогнозного значения представлены на рисунке 11.Рис.11. Фрагмент рабочего листа MS Excel вычисления прогнозного значенияЗдесь в ячейку Н29 введена формула =МАКС(B3:B16).В ячейку Н30 введена формула =МИН(B3:B16).В ячейку Н31 введена формула =H29-H30.В ячейку Н32 введена формула =B20+0,1*H31.В ячейку Н31 введена формула =E26+E27*H32.

---

3)Расчёт аппроксимаций по программе, разработанной в среде VBA

Программа, разработанная в среде VBA рассчитывает аппроксимацию по алгоритму, блок-схема которого представлена на рисунке 12.Рис. 12. Укрупненная блок-схема алгоритма расчета аппроксимацииИсходный код программы выглядит следующим образом:Option Explicit Dim Inp_Data As String Dim FileName As StringPublic UR As Range, CL As Range Dim R1 As Byte, C1 As Byte, R As ByteDim i As Integer, j As Integer, k As Integer Public n As Integer Dim m_1() As Single, m_2() As SingleDim x() As Single, y() As Single Dim lny() As SingleConst n1 = 2 Dim Ftab(1 To n1) As SingleDim Stab(1 To n1) As SinglePublic alfa As Single Dim stl() As Single, ssrl() As Single, stsqr() As Single, ssrsqr() As _Single, YL() As Single, Ysqr() As Single, Yexp() As Single, _lnYexp() As SinglePublic Sx As Single, Sy As Single, Sxy As Single, Sx2 As Single, _Sx3 As Single, Sx4 As Single, Sx2y As Single, Sx2sr As Single, _Sy2sr As Single, Sxysr As Single, Slny As Single, Sxlny As SinglePublic a1L As Single, a2L As Single, koef_cor As Single, MD As SinglePublic Sa1L As Single, Sa2L As Single, Xsr As Single, _Ysr As Single, lnYsr As SinglePublic SostL As Single, SregrL As Single, SpolnL As Single, _R_det_L As SinglePublic Fline As Single, Sline As Single, ta1L As Single, ta2L As SinglePublic a1sqr As Single, a2sqr As Single, a3sqr As SinglePublic Sa1sqr As Single, Sa2sqr As Single, Sa3sqr As SinglePublic Sostsqr As Single, Sregrsqr As Single, Spolnsqr As Single, _R_det_sqr As Single, DSost As SinglePublic Fsqr As Single, Ssqr As Single, ta1sqr As Single, _ta2sqr As Single, ta3sqr As SinglePublic cexp As Single, a1exp As Single, a2exp As SinglePublic Sa1exp As Single, Sa2exp As SinglePublic Sostexp As Single, Sregrexp As Single, Spolnexp As Single, _R_det_exp As SinglePublic Fexp As Single, ta1exp As Single, ta2exp As SinglePublic gr As IntegerPublic Min As Single, Max As Single, Xpr As Single, Ypr As SinglePublic f_st As StringPublic c As IntegerPublic st1 As StringPublic cell_1 As IntegerPublic cell_2 As IntegerPublic pr1 As IntegerPublic Sub ReadList()Sheets("VBA").ActivateSet UR = ActiveSheet.UsedRangeR1 = UR.Rows(1).Row If R1 = 0 Then Exit Sub C1 = UR.Columns(1).Column n = 0Cells(R1, C1).ActivateWhile ActiveCell <> ""If IsNumeric(ActiveCell.Value) Thenn = n + 1ReDim Preserve m_1(1 To 2, 1 To n)m_1(1, n) = ActiveCell.Valuem_1(2, n) = ActiveCell.Offset(0, 1).ValueEnd IfActiveCell.Offset(1, 0).ActivateWendIf n > 0 ThenReDim m_2(1 To n, 1 To 2)For j = 1 To nm_2(j, 1) = m_1(1, j)m_2(j, 2) = m_1(2, j)Next jElseReDim m_2(1 To 1, 1 To 2)End IfWith UserForm1.ListBox1.ColumnCount = 2.List = m_2End WithEnd SubPublic Sub Read_XY(x, y) ReDim x(1 To n)ReDim y(1 To n)For j = 1 To nx(j) = m_2(j, 1)y(j) = m_2(j, 2)Next jEnd SubPublic Sub Read_tabl(alfa) Dim d_f1 As Integer, d_f2 As Integerd_f1 = 1d_f2 = n - 1On Error GoTo old_funcFtab(1) = WorksheetFunction.F_Inv_RT(alfa, d_f1, d_f2 - 1)Ftab(2) = WorksheetFunction.F_Inv_RT(alfa, d_f1, d_f2 - 2)

---

Stab(1) = WorksheetFunction.T_Inv_2T(alfa, d_f2)Stab(2) = WorksheetFunction.T_Inv_2T(alfa, d_f2 - 1)Exit Subold_func:Ftab(1) = WorksheetFunction.FInv(alfa, d_f1, d_f2 - 1)Ftab(2) = WorksheetFunction.FInv(alfa, d_f1, d_f2 - 2)Stab(1) = WorksheetFunction.TInv(alfa, d_f2)Stab(2) = WorksheetFunction.TInv(alfa, d_f2 - 1)End SubPublic Sub L_Kram(a11 As Integer, a12 As Single, a21 As Single, _a22 As Single, b1 As Single, b2 As Single, a1 As Single, a2 As Single)Dim d As Single, d1 As Single, d2 As Singled = a11 * a22 - a21 * a12d1 = b1 * a22 - b2 * a12d2 = a11 * b2 - a21 * b1a1 = d1 / da2 = d2 / dEnd SubPublic Sub Sqr_Kram(a11 As Integer, a12 As Single, a13 As Single, _a21 As Single, a22 As Single, a23 As Single, a31 As Single, _a32 As Single, a33 As Single, b1 As Single, b2 As Single, _b3 As Single, a1 As Single, a2 As Single, a3 As Single, MD As Single)Dim d As Single, d1 As Single, d2 As Single, d3 As Singled = a11 * a22 * a33 + a12 * a23 * a31 + a21 * a32 * a13 - a13 * _a22 * a31 - a21 * a12 * a33 - a11 * a23 * a32d1 = b1 * a22 * a33 + a12 * a23 * b3 + b2 * a32 * a13 - a13 * _a22 * b3 - b2 * a12 * a33 - b1 * a23 * a32d2 = a11 * b2 * a33 + b1 * a23 * a31 + a21 * b3 * a13 - a13 * _b2 * a31 - a21 * b1 * a33 - a11 * a23 * b3d3 = a11 * a22 * b3 + a12 * b2 * a31 + a21 * a32 * b1 - b1 * _a22 * a31 - a21 * a12 * b3 - a11 * b2 * a32a1 = d1 / da2 = d2 / da3 = d3 / dMD = dEnd SubPublic Sub Koef_det(ns As Integer, a() As Single, b() As Single, _c As Single, s1 As Single, s2 As Single, s3 As Single, R As Single)Dim i As IntegerFor i = 1 To nss1 = s1 + (b(i) - a(i)) ^ 2s2 = s2 + (b(i) - c) ^ 2Next is3 = s1 + s2R = 1 - s1 / s3End SubPublic Sub ZnachLine(n As Integer, DS As Single, R2 As Single, _a As Single, asr As Single, a1 As Single, a2 As Single, s1 As Single, _s2 As Single, f As Single, t1 As Single, t2 As Single)s1 = Sqr(DS * a / (n * (n - 2) * asr))s2 = Sqr(DS / ((n - 2) * asr))f = R2 * (n - 2) / (1 - R2)t1 = Abs(a1) / s1t2 = Abs(a2) / s2End SubPublic Sub MinMax(a() As Single, n As Integer, Min As Single, _Max As Single)Dim i As IntegerMin = a(1)Max = a(1)For i = 2 To nIf a(i) > Max Then Max = a(i)If a(i) < Min Then Min = a(i)Next iEnd SubPublic Sub Print_rez(gr As Integer, sa1 As String, sa2 As String, _sa3 As String, sk As String, sR As String, sm1 As String, _sm2 As String, sm3 As String, sf As String, st1 As String, _st2 As String, st3 As String, a1 As Single, a2 As Single, a3 As Single, _k As Single, R As Single, m1 As Single, m2 As Single, m3 As Single, _f As Single, t1 As Single, t2 As Single, t3 As Single)Dim Fd As Single, Sd As Single, st As String, str1 As Stringf_st = "0.0000" ' ôîðìàò âûâîäà ÷èñåëIf gr = 2 Then Fd = Ftab(2) Else Fd = Ftab(1)If gr = 2 Then Sd = Stab(2) Else Sd = Stab(1)Select Case grCase 1st = "ëèíåéíîé àïïpîêñèìàöèè"With UserForm1.tb_k1l = Format(a1, f_st).tb_k2l = Format(a2, f_st).tb_kc = Format(k, "0.000000").tb_kdl = Format(R, "0.000000").tb_k1l_s = Format(m1, "0.000000").tb_k2l_s = Format(m2, "0.000000").tb_k1l_tr = Format(t1, f_st).tb_k2l_tr = Format(t2, f_st).tb_kl_tt = Format(Sd, f_st).tb_l_ft = Format(Fd, f_st).tb_l_fr = Format(f, f_st)If t1 > Sd Then.tb_k1l_z = "çíà÷èì"Else.tb_k1l_z = "íå çíà÷èì"End IfIf t2 > Sd Then.tb_k2l_z = "çíà÷èì"Else.tb_k2l_z = "íå çíà÷èì"End IfIf f > Fd Then .tb_l_z = "çíà÷èìî" Else .tb_l_z = "íå çíà÷èìî"End WithCase 2st = "êâàäpàòè÷íîé àïïpîêñèìàöèè"With UserForm1.tb_k1q = Format(a1, f_st).tb_k2q = Format(a2, f_st).tb_k3q = Format(a3, f_st).tb_kdq = Format(R, "0.000000").tb_k1q_s = Format(m1, "0.000000")

---