Санкт-Петербургский  государственный технологический  институт

(технический  университет)

 
 
 

Кафедра: математического  моделирования и оптимизации  химико-технологических процессов

 
 

Факультет: физико-математическое отделение

 

      Курс: I

                                   Группа: 271

 
 

Учебная дисциплина: информатика

 
 
 
 
 
 

     Курсовая  работа

 
 

Тема: обработка экспериментальных данных средствами Excel, MathCad и Visual Basic

 
 
 
 

Студент: _________________________________________________ Пумпурс Ю.Р.

 
 
 

Руководитель:______________________________________________Гайков А.В.

 
 

Оценка за курсовую работу:__________________________________ Личная подпись

           руководителя

 
 
 
 
 
 
 
 
 
 
 
 

 

Оглавление

 

     Введение

   Сейчас, в XXI веке, ни одна область науки и жизни не обходится без ЭВМ.  Знания человека расширяются и, следовательно, усложняются. В настоящее время создано много программ, способных облегчить решение многих проблем и задач во многих областях познаний. Я, как будущий специалист в области химии, считаю необходимым умение создавать программные продукты в различных приложениях.

   Цель  моей работы: обработать экспериментальные  данные в приложениях Excel, MathCad, Visual Basic

   Мои задачи: описать метод выполнения расчётов, выполнить расчёты в  Excel, MathCad, Visual Basic, построить графики, сравнить результаты, полученные в трёх приложениях, выяснить, какое из трёх приложений наиболее удобно для решения данной задачи.

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
---

Санкт-Петербургский  государственный технологический  институт

 (технический университет)

 

Факультет: физико-математическое отделение

Кафедра: математического  моделирования и оптимизации  химико-технологических процессов

Учебная дисциплина: «Информатика»

Курс: I

ЗАДАНИЕ НА КУРСОВУЮ РАБОТУ № 13

 

Тема: Обработка экспериментальных данных средствами Excel, MathCAD и Visual Basic

 

Постановка  задачи

Источник  радиоактивного излучения помещен  в жидкость. Датчики расположены  на расстоянии ( ) 20, 50 и 100 см от источника. Измерения интенсивности излучения

( , мРн) проводились через 1, 5 и 10 суток ( ) после установки источника. Результаты измерений ( ) приведены в таблицы:

 

 Переменная ( ) зависит от двух независимых переменных и ,

 

                        (1)

 

Требуется определить вид зависимости и найти неизвестные параметры, входящие в функцию.

Примечание

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

 

                                              (2)

 

Прологарифмируем (2) и получим 

 

 

Введем  обозначения:

 

    

 

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

 

 

Продифференцируем по , и и приравняем все частные производные нулю, получим систему уравнений

 
 

 

Решением  этой системы будут искомые значения , и , через которые можно найти , и .

Задание

 

1. Разработать  программный продукт на VB

     a. для определения , и

     b. для вычисления для заданных значений и

     c. для вычисления относительной погрешности

     d. для определения наибольшей и наименьшей погрешности

 

2. Для  решения системы уравнений использовать  метод Крамера, вычисление оформить  в виде процедуры Sub..End Sub.

 

3. Выполнить  вычисления в среде Excel. Построить графики.

4. Выполнить  работу в среде MathCAD.

Провести  сравнение полученных результатов.

 

 

 

Описание  метода

     Метод наименьших квадратов

     МНК – один из методов аппроксимации  функции y=y(x), заданной таблицей из n значений. По этому методу строится функциональная зависимость y=f(x, a1,…,a_m), где а1,…,а_{m –} неизвестные параметры, подбираемые так, чтобы величина выражения

                   n

     s = Σ p_i (f(x_i, a₁,…..,a_m) – yi)²

             i=1

была  бы минимальной (pi – положительные числа, называемые весами, они выбираются произвольно, исходя из физических или математических соображений, либо полагаются все равными единице). Параметры ai,…am должны удовлетворять системе уравнений

∂x

∂y = 0, i=1….m (нормальная система). Если нормальная система имеет единственное решение, то оно и является искомым. Обычно в качестве f(x, a1,…,a_m ) выбирают многочлены (алгебраические или обобщённые). При выборе алгебраического многочлена в случае m=n приходим к интерполяционному многочлену, в случае m>n задача неопределённа. М.Н.К. применяется при m<n. В частном случае, когда строится линейная зависимость вида f(x) = ax+b, коэффициенты a и b находятся из системы линейных уравнений¹:

                                 n               n            n

                            a Σ x_i²+b Σ x_i = Σ x_iy_i

                                i=1             i=1         i=1

 

                                n                   n

                            a Σ x_i+bn = Σ y_i                              

                                i=1              i=1

 

Метод Гаусса:

   Определитель  квадратной матрицы A – число D, равное сумме n! членов (-1)ⁿ a₁₁, a₂₂,a₃₃…a_nn, каждый из которых соответствует одному из n! различных упорядоченных множеств j₁,j₂,….j_n , полученных r-парными перестановкамиэлементов из множеств 1,2,…n.

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

    

                          a_ij^(k)=a_ij^(k-1) – a_ij^(k-1)*(a_kj^(k-1)/a_kk^(k-1)),

   где k=1,2,…,(n-1) и  a_kk^(k-1) ≠0. Преобразование массива A (n,n) производится в направлении расположения столбцов слева направо. Определитель вычисляется как произведение всех диагональных элементов преобразованной матрицы.

   Необходимое условие для реализации простого метода Гаусса заключается в неравенстве  элемента a_kk^(k-1)) нулю на всех этапах преобразования.²

 

   Регрессионный анализ  (приближение функций по методу наименьших квадратов)

   Линейный парный регрессионный анализ в определении параметров эмпирической линейной зависимости y=b₁(x)+b_0, описывающей связь между некоторым числом N парных значений x_i и y_i, обеспечивая при этом наименьшую среднеквадратичную погрешность. Графически эту задачу можно представить следующим образом: в облаке точек x_i y_i  плоскости xy требуется провести прямую так, чтобы величина всех отклонений отвечала условию:

                    n

   U = Σ[y_i – y(x_i)]² = min,

          I=1

   где y(x_i) – зависимость. Для этого нужно приравнять нулю частные производные:

   ∂U         n

   ∂b₀ = Σ [y_i+(b_o+b₁x_i]

                i=1

   ∂U          n

   ∂b1 = Σ [y_i+(b_o+b₁x_i ) x_i ], что даёт для определения неизвестных

                      i=1

   коэффициентов система линейных уравнений:

                    N                 N

   b₀N+b₁ Σx_i = Σy_i

              i=1           i=1

         N                  N             N

   b₀ Σx_i+b₁ Σx_i²= Σx_iy_i

         i=1                i=1           i=1

Решение этой системы:

 

               N                       N                 N

   b₁ = Σx_i+b₁Σy_i-NΣx_iy_i

           i=1             i=1           i=1____

            N                  N

           (Σx_i)²-NΣx_i²

             i=1               i=1

               N                  N

    bo= 1(Σy_i – b_iΣx_i)

           N i=1               i=1³

 

    Метод Крамера:

   Применяется для нахождения корней системы n уравнений с n неизвестными. По методу Крамера находится определитель квадратной матрицы ∆, а затем каждый столбец по очереди заменяется на столбец свободных членов, и вычисляются определители ∆1, ∆2, ………∆n. Получив все определители, применяем формулу Крамера для каждого из ∆1, ∆2, ………∆n для нахождения корней системы:

            ∆1          ∆1           ∆n

   X₁=  ∆  ,   x₂= ∆  ,  x_n =   ∆.

   Метод основан на неравенстве определителей  нулю на каждом этапе.

VISUAL BASIC

 

 

Программа

Dim Y() As Single, X1() As Single, X2() As Single, YP() As Single, n As Integer

Private Sub Command1_Click()

Dim F As String, i As Integer

With CD1

.Action = 1

F = .FileName

End With

Open F For Input As #1

Input #1, n

ReDim X1(1 To n) As Single, X2(1 To n) As Single, Y(1 To n) As Single, YP(1 To n) As Single

With Grid1

.Rows = n + 1

For i = 1 To n

Input #1, X1(i), X2(i), Y(i)

.Row = i

.Col = 0

.Text = CStr(X1(i))

.Col = 1

.Text = CStr(X2(i))

.Col = 2

.Text = CStr(Y(i))

Next i

End With

Close #1

End Sub

 

Private Sub Command2_Click()

End

End Sub

Private Sub Command3_Click()

Const k = 3

Dim a0 As Single, a1 As Single, a2 As Single, i As Integer, j As Single

Dim matr(1 To 3, 1 To 3) As Single, stolb(1 To 3) As Single, PRO(1 To 3) As Single

Dim GLOPR As Single, KOEF(1 To 3) As Single, dup() As Single

ReDim dup(1 To n) As Single

For i = 1 To n

matr(1, 2) = matr(1, 2) + X1(i)

matr(1, 3) = matr(1, 3) + X2(i)

matr(2, 2) = matr(2, 2) + X1(i) * X1(i)

matr(3, 2) = matr(3, 2) + X1(i) * X2(i)

matr(3, 3) = matr(3, 3) + X2(i) * X2(i)

stolb(1) = stolb(1) + Log(Y(i))

stolb(2) = stolb(2) + X1(i) * Log(Y(i))

stolb(3) = stolb(3) + X2(i) * Log(Y(i))

Next i

matr(1, 1) = n: matr(2, 1) = matr(1, 2)

matr(3, 1) = matr(1, 3): matr(2, 3) = matr(3, 2)

GLOPR = Opred(k, matr())

For j = 1 To k

For i = 1 To k

PRO(i) = matr(i, j)

matr(i, j) = stolb(i)

Next i

KOEF(j) = Opred(k, matr()) / GLOPR

For i = 1 To k

matr(i, j) = PRO(i)

Next i

Next j

a0 = Exp(KOEF(1))

a1 = Exp(KOEF(2))

a2 = Exp(KOEF(3))

Text1.Text = CStr(a0)

Text2.Text = CStr(a1)

Text3.Text = CStr(a2)

With Grid1

For i = 1 To n

.Row = i

YP(i) = a0 * a1 ^ X1(i) * a2 ^ X2(i)

.Col = 3

.Text = CStr(YP(i))

dup(i) = Abs(YP(i) - Y(i)) / Abs(Y(i))

.Col = 4

.Text = CStr(dup(i))

Next i

End With

Text4.Text = CStr(Min(n, dup()))

Text5.Text = CStr(Max(n, dup()))

End Sub

Private Function Opred(no1 As Integer, xo1() As Single) As Single

Dim io As Integer, jo As Integer, do1 As Single

Dim eo As Single, ko As Integer, bo1 As Integer, co As Integer

Dim ao As Single, so As Single, go As Single, zo As Integer

ReDim xo(1 To no1, 1 To no1) As Single

zo = 1

do1 = 1

For io = 1 To no1

For jo = 1 To no1

xo(io, jo) = xo1(io, jo)

Next jo

Next io

For ko = 1 To no1 - 1

eo = 0

For io = ko To no1

For jo = ko To no1

If Abs(eo) >= Abs(xo(io, jo)) Then GoTo m90

eo = xo(io, jo): bo1 = io: co = jo

m90:

Next jo

Next io

If ko = bo1 Then GoTo m120

For jo = ko To no1

so = xo(ko, jo)

xo(ko, jo) = xo(bo1, jo)

xo(bo1, jo) = so

Next jo

zo = -zo

m120:

If ko = co Then GoTo m150

For io = ko To no1

so = xo(io, ko)

xo(io, ko) = xo(io, co)

xo(io, co) = so

Next io

zo = -zo

m150:

For io = ko + 1 To no1

go = xo(io, ko) / xo(ko, ko)

For jo = ko To no1

xo(io, jo) = xo(io, jo) - go * xo(ko, jo)

Next jo

Next io

Next ko

For io = 1 To no1

do1 = do1 * xo(io, io)

Next io

do1 = do1 * zo

Opred = do1

End Function

Private Function Min(n As Integer, z() As Single) As Single

Dim i As Integer

Min = z(1)

For i = 2 To n

If Min > z(i) Then Min = z(i)

Next i

End Function

Private Function Max(n As Integer, z() As Single) As Single

Dim i As Integer

Max = z(1)

For i = 2 To n

If Max < z(i) Then Max = z(i)

Next i

End Function

Private Sub Form_Load()

With Grid1

.Row = 0

.Col = 0

.Text = "X1"

.Col = 1

.Text = "X2"

.Col = 2

.Text = "Y"

.Col = 3

.Text = "YP"

.Col = 4

.Text = "DeltaY"

End With

End Sub

 

EXCEL

 

 
 
 
 
 
 
 
 

 

 

 

 

 

 

 
 
 
 
 
 
 

 

MATHCAD

 
 

 

 

Выводы:

 

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

-  а₀, а₁, а₂;

- y_расч для заданных x₁и х_2;

- относительной  погрешности;

- минимальной  и максимальной погрешностей.

С моей точки зрения более рациональной и наименее трудоёмкой является разработка программного продукта в среде MathCad. Полученные данные представлены наиболее наглядно, и, следовательно, удобны для изучения.

 

Список литературы:

 

1.   Воднев В.Т. Математический словарь высшей школы\ М. 1988

2. Дьяконов  В.П. Справочник по алгоритмам  и программам на языке бейсик  для персональных ЭВМ\М. 1989

3.   Ракитин В.И., В.Е. Первушин. Практическое руководство по методам вычислений с приложением программ для персональных компьютеров\М. 1998