Файл: Информационные процессы в переработке нефти и газа.doc

Рисунок 31 - Фрагмент листа MathCAD с исходными данными

Линейная регрессия

Линейная регрессия в системе MathCAD выполняется по векторам аргумента Х и отсчетов Y функциями: intercept(X,Y) – вычисляет параметр a1 , смещение линии регрессии по вертикали; slope(X,Y) – вычисляет параметр a2 , угловой коэффициент линии регрессии. Полученные значения коэффициентов используем в уравнении регрессии

Функция corr(Y,y(x)) - вычисляет коэффициент корреляции Пирсона. Чем он ближе к 1, тем точнее обрабатываемые данные соответствуют линейной зависимости.

Вычислив параметры линейной регрессии, строим графики исходной функции y и функции линейной регрессии f(x)

Рисунок 32 - Фрагмент листа MathCAD с найденными коэффициентами для системы уравнений и графиком зависимости линии тренда для линейной аппроксимации

Полиномиальная регрессия

Одномерная полиномиальная регрессия с произвольной степенью n полинома и с произвольными координатами отсчетов в MathCAD выполняется функцией regress(X,Y,n), которая вычисляет вектор S, в составе которого находятся коэффициенты ai полинома n-й степени. Значения коэффициентов ai могут быть извлечены из вектора S функцией submatrix(S, 3, length(S)-1, 0, 0). Полученные значения коэффициентов используем в уравнении регрессии/ Вычислив параметры квадратичной регрессии, строим графики исходной функции y и функции квадратичной регрессии f(x)

Рисунок 33 - Фрагмент листа MathCAD с найденными коэффициентами для системы уравнений и графиком зависимости линии тренда для квадратичной аппроксимации

Рисунок 34 - Фрагмент листа MathCAD с найденными коэффициентами для системы уравнений и графиком зависимости линии тренда для экспоненциальной аппроксимации

Проведенные расчеты показывают, что результаты, полученные с помощью среды MathCAD, полностью совпадают со значениями, рассчитанными в MS Excel.

Аппроксимация с помощью VBA

Код программы:

Public Sub MHK()

Dim x(1 To 26) As Single, y(1 To 26) As Single, yt(1 To 26), yt1(1 To 26), yt2(1 To 26) As Single

Dim Sx1, Sx2, Sx3, Sx4 As Single

Dim Sy, Sxy, Sx2y As Single

Dim x1, x2, x3, x4 As Single

Dim y1, y2, sxr, yxr, lny1, slny, sxlny, sxsrysr As Single

Dim n As Integer

Dim i As Integer

Dim a1 As Single, a2 As Single

Dim coef_cor As Single

Dim coef_det As Single

Dim coef_det2 As Single

Dim coef_det3 As Single

n = 25

' вводим исходные данные в вектора x и y

For i = 1 To n

x(i) = Range("B" & 2 + i)

y(i) = Range("a" & 2 + i)

xsr = xsr + x(i)

ysr = xsr + y(i)

Next i

xsr = xsr / 26

ysr = ysr / 26
' определяем коэф СЛАУ

Sx1 = 0

Sx2 = 0

Sx3 = 0

Sx4 = 0

Sy1 = 0

Sxy = 0

Sx2y = 0

For i = 1 To n

x1 = x(i)

y1 = y(i)

lny1 = Log(y1)

x2 = x1 * x1

x3 = x2 * x1

x4 = x3 * x1

Sx1 = Sx1 + x1

Sx2 = Sx2 + x2

Sx3 = Sx3 + x3

Sx4 = Sx4 + x4

Sy1 = Sy1 + y1

Sxy = Sxy + x1 * y1

Sx2y = Sx2y + x2 * y1

slny = slny + lny1

sxlny = sxlny + x1 * lny1

sxsrysr = sxsrysr + ((x1 - xsr) * (y1 - ysr))

Next i

'решаем СЛАУ методом Крамера

Call kram2(26, Sx1, Sx1, Sx2, Sy1, Sxy, a1, a2)

MsgBox "Линейная аппроксимация:" & "a1= " & a1 & "a2= " & a2

'вычисляем среднее значение для x и y

xsr = Sx1 / n

ysr = Sy1 / n

'вычисляем коэффициент корреляции

Sxy_mean = 0

Sx2_mean = 0

Sy2_mean = 0

For i = 1 To n

Sxy_mean = Sxy_mean + (x(i) - xsr) * (y(i) - ysr)

Sx2_mean = Sx2_mean + (x(i) - xsr) ^ 2

Sy2_mean = Sy2_mean + (y(i) - ysr) ^ 2

Next i

coef_cor = Sxy_mean / Sqr(Sx2_mean) / Sqr(Sy2_mean)

MsgBox "коэффициент корреляции =" & coef_cor

'вычисляем вектор теоретических значений yt

For i = 1 To n

yt(i) = line(x(i), a1, a2)

Next i

coef_det = r2(n, ysr, y, yt)

MsgBox "коэффициент детерминированности линейный=" & coef_det

Call kram3(26, Sx1, Sx2, Sx1, Sx2, Sx3, Sx2, Sx3, Sx4, Sy1, Sxy, Sx2y, a1, a2, a3)

MsgBox "Квадратичная аппроксимация:" & "a1= " & a1 & "a2= " & a2 & "a3= " & a3

For i = 1 To n

yt1(i) = kvad(x(i), a1, a2, a3)

Next i

coef_det2 = r2(n, ysr, y, yt1)

MsgBox "коэффициент детерминированности кавдратичный=" & coef_det2

Call kram4(26, Sx1, Sx1, Sx2, slny, sxlny, a1, a2)

MsgBox "Экспоненциальная аппроксимация:" & "a1= " & a1 & "a2= " & a2

For i = 1 To n

yt2(i) = expon(x(i), a1, a2)

Next i

coef_det3 = r2(n, ysr, y, yt2)

MsgBox "коэффициент детерминированности экспоненциальный=" & coef_det3
End Sub

Public Sub kram2(a11, a12, a21, a22, b1, b2, x1, x2)

Dim d, d1, d2 As Single

d = a11 * a22 - a21 * a12

d1 = b1 * a22 - b2 * a12

d2 = a11 * b2 - a21 * b1

x1 = d1 / d

x2 = d2 / d
End Sub

Public Sub kram3(a11, a12, a13, a21, a22, a23, a31, a32, a33, b1, b2, b3, x1, x2, x3)

Dim d, d1, d2, d3 As Single

d = a11 * a22 * a33 + a12 * a23 * a31 + a13 * a21 * a32 - a13 * a22 * a31 - a12 * a21 * a33 - a11 * a23 * a32

d1 = b1 * a22 * a33 + b2 * a23 * a31 + b3 * a21 * a32 - b3 * a22 * a31 - b2 * a21 * a33 - b1 * a23 * a32

d2 = a11 * b2 * a33 + a12 * b3 * a31 + a13 * b1 * a32 - a13 * b2 * a31 - a12 * b1 * a33 - a11 * b3 * a32

d3 = a11 * a22 * b3 + a12 * a23 * b1 + a13 * a21 * b2 - a13 * a22 * b1 - a12 * a21 * b3 - a11 * a23 * b2

x1 = d1 / d

x2 = d2 / d

x3 = d3 / d

End Sub

Public Sub kram4(a11, a12, a21, a22, b1, b2, x1, x2)

Dim d, d1, d2 As Single

d = a11 * a22 - a21 * a12

d1 = b1 * a22 - b2 * a12

d2 = a11 * b2 - a21 * b1

x1 = exp(d1 / d)

x2 = d2 / d
End Sub

Public Function line(x, b, a) As Single

line = a * x + b

End Function

Public Function kvad(x, b, a1, a2) As Single

kvad = a2 * x * x + a1 * x + b

End Function

Public Function expon(x, a1, a2) As Single

expon = a1 * e ^ (a2 * x)

End Function

Public Function r2(n, ysr, y, yt) As Single

sost = 0

sfact = 0

For i = 1 To n

sost = sost + (y(i) - yt(i)) ^ 2

sfact = sfact + (yt(i) - ysr) ^ 2

Next i

r2 = 1 - sost / (sost + sfact)

End Function

Результат работы программы представлен на рисунках

Рисунок 35 – Результат работы программы (Линейная аппроксимация)

Рисунок 36 – Результат работы программы (Квадратичная аппроксимация)

Рисунок 37 – Результат работы программы (Экспоненциальная аппроксимация)

1 2 3 4 5 6 7 8 9 10

Задача 3

Вариант 6

Y	X
0,45	2850
0,52	2860
0,42	2870
0,53	2910
0,39	2930
0,78	2930
0,57	2950
0,69	2980
0,78	3010
0,81	3030
0,83	3030
0,82	3040
0,89	3040
1,01	3040
0,89	3050
0,85	3050
0,92	3050
0,9	3050
1,02	3050
1,05	3060
0,98	3070
1,1	3070

Таблицы, выполненные средствами Microsoft Excel.

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

Рисунок 39 - Вычисления вспомогательных сумм (продолжения).
На рис. 40 представлены расчёты для линейной аппроксимации.

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

Таким образом уравнение линейной регрессии имеет вид:

y = 0,0027x - 7,2135

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

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

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

y = 1E-05x² - 0,059x + 84,216

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

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

Таким образом, уравнение экспоненциальной регрессии имеет вид:

y = 6E-06e^0,0039x

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

Построение линии тренда

Методика проведения данных работ подробно изложена в работе [2]. Результаты представлены на рис.

Рисунок 43 - Исходные точки и линия тренда для линейной аппроксимации.

Рисунок 44 -. Исходные точки и линия тренда для квадратичной аппроксимации.

Рисунок 45 - Исходные точки и линия тренда для экспоненциальной аппроксимации.

Сравнивая данные результаты с результатами, полученными вручную ранее с использованием основных расчётных формул, видим, что они полностью совпадают. Это указывает на то, что вычисления верны.