Построить эмпирические формулы по исходным данным своего варианта тремя способами: используя стандартные средства Excel, проведя расчеты в табличном процессоре Excel, а также проведя вычисления по программе, написанной на языке программирования, изучавшимся в курсе «Информатика». Варианты заданий приведены в приложении.

Во всех вариантах требуется:

1. Построить в EXCEL график таблично заданной функции, сохраняя обозначения задания.

2. Вычислить коэффициент корреляции в EXCEL (либо по программе на языке программирования высокого уровня) для случая линейной зависимости между исходными параметрами.

3. В зависимости от вида графика и величины коэффициента корреляции выбрать несколько классов эмпирических функций из следующих возможных вариантов: линейная функция, степенная функция, экспоненциальная функция, квадратичная (полиномиальная) функция, логарифмическая функция. При изложении материала обязательно сохранять обозначения, данные в задании. Выбор функций согласовать с руководителем курсовой работы.

4. Определить конкретный вид выбранных эмпирических функций, решив системы линейных уравнений матричным методом в EXCEL и определив значения коэффициентов. Вычислить коэффициенты детерминированности для полученных функций. Построить графики теоретических функций, с наложением точек фактических данных.

5. Для выбранного класса функций построить в EXCEL отдельные графики линий тренда, с выводом уравнений и коэффициентов детерминированности.

6. Составить алгоритм вычислений эмпирических функций по методу наименьших квадратов в виде блок-схемы.

7. Выбрать один из численных методов решения систем линейных уравнений (метод простых итераций или метод Зейделя, или метод Гаусса с обратным ходом, или без обратного хода с выбором максимального элемента в столбце) и описать его.

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

9. Отладить программу и провести вычисления с выводом результатов в файл. Распечатать результаты вычислений в виде таблиц, снабдив их необходимыми пояснениями.

10. Распечатать результаты вычислений в EXCEL в виде таблиц, снабдив их необходимыми пояснениями.

11. Сравнить все три результата вычислений (в EXCEL, на языке программирования и полученные при построении линии тренда), сделать выводы. Определить, какая из полученных эмпирических формул наилучшим образом аппроксимирует заданную функцию

Таблица 1

Введение

Перфоратор ПП-54 (рис 1) широко используется в горной промышленности, применяется для бурения нисходящих шпуров при проходке обводненных шахтных стволов. Бурение ведется с применением пневматических поддержек.

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

Рис. 1. Фронтальный вид пневматического перфоратора ПП-54

Таблица 2

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

Если между величинами F и V существует некоторая функциональная зависимость (в частности, в данной курсовой работе), но ее аналитический вид неизвестен, то возникает практическая задача - найти эмпирическую формулу

V ^Т =F (F,a₁,a₂,..a_m), (1)

где a₁,a₂,..a_m - коэффициенты.

Вид функции и значения коэффициентов a₁,..a_m подбираются таким образом, чтобы значения V^Т_i, вычисление по эмпирической формуле при различных значениях F_i, возможно мало отличаться бы от опытных значений V_i. Нахождение аналитической зависимости между эмпирическими величинами называется аппроксимацией функции заданной таблично (от латинского "approximare" -"приближаться"). В научных исследованиях аппроксимация применяется для описания, анализа, обобщения и дальнейшего использования эмпирических результатов. Чаще всего, чем проще уравнение используется для аппроксимации, тем более приблизительно получаемое описание зависимости. Главное при вычислении аппроксимации не стремление с максимальной точностью передать случайные отклонения величин в конкретных рядах эмпирических данных, а гораздо важнее уловить основную тенденцию зависимости (или ее "тренд").

При аппроксимации указывают класс функции, из которой выбирается теоретическая функция (например: линейная или кубическая и т.п.) и далее определяются наилучшие значения коэффициентов методом наименьших квадратов.

Согласно методу наименьших квадратов наилучшими коэффициентами а₁,а₂,….а_m считаются те, для которых сумма квадратов отклонений найденной теоретической функции от заданных эмпирических значений будет минимальной. Следовательно, задача состоит в определении коэффициентов а₁,а₂,….а_m, таким образом, чтобы сумма квадратов отклонений была наименьшей.

(2)

Для того чтобы найти набор коэффициентов а₁,а₂,….а_m, при которых достигается минимум функции S, определяемой формулой (2), используем необходимое условие экстремума функции нескольких переменных - равенство нулю частных производных.

Таким образом, нахождение коэффициентов сводится к решению системы (3).

(3)

Конкретный вид системы (3) зависит от того, из какого класса эмпирических формул мы ищем зависимость (1).

По эмпирическим данным мы с помощью мастера диаграмм строим график исходных данных (рис 2), данные берем из таблицы 1.

Рис. 2

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

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

(4)

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

(5)

где а₁, а₂, а₃, а₄ - неизвестные, а суммы: ; и т.д. дают конкретные значения коэффициентов и свободных членов в системах линейных уравнений (4) и(5).

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

1. Расчеты, проведенные с помощью Microsoft Excel

Составляем матрицу А. Коэффициенты a₁, a₂ и a₃ вычисляем по формуле =[А^-1]*. Три составляющие вектора будут искомыми коэффициентами a₁, a₂ и a₃. Для нахождения чисел, составляющих матрицу А (рис 3.), выполняются расчеты приведенные в приложении на рис 1.

1. В ячейки A2:A26 и B2:B26 заносим значения F_i и V_i соответственно

2. В ячейку С2 вводим формулу =A2^2.(копируем ее в остальные клетки)

3. В ячейку D2 вводим формулу = A2^3. (копируем ее в остальные клетки)

4. В ячейку E2 вводим формулу = A2^4. (копируем ее в остальные клетки)

5. В ячейки A28:D28 вводим формулу =СУММ(A_X:A_Y). Суммируем значения вышестоящих величин.

6. Вычисляем так же по формуле =A2*B2.(копируем ее в нижние клетки и суммируем их (=СУММ(F2:F26)). Вычисляем по формуле =(C2*B2), копируем ее в нижние клетки и суммируем их =СУММ(G2:G26).

Определение коэффициентов квадратичного уравнения сводится к решению матрицы (4). Исходными данными для которой будут служить числа найденные выше (В нашем случае матрица решается матричным методом). Поясним более подробно ход решения.

1. В ячейки I3:K5 вводятся коэффициенты стоящие при а₁, а₂,….а_n. (рис 3)

2. В ячейках I10:K12 находим матрицу обратную матрице в ячейках I3:K5 (=МОБР(I3:K5) (рис 3)

3. В ячейках F13:F14 находим по формуле (=МУМНОЖ(L9:O12;Q3:Q6)) коэффициенты квадратичной аппроксимации.(рис 3)

Наряду с вычислениями в EXCEL, часто используются вычисления на языке программирования высокого уровня. Что связано с удобством в применении полученной программы. Так как полученную программу можно использовать, при аналогичных расчетах, подставляя в нее только исходные данные. Так же программа в QBasic позволяет получить математически точные графики зависимостей.

Программа написанная в QBasic использует подпрограмму решения СЛАУ n-ого порядка. Подпрограмма находит вектор неизвестных, которые являются коэффициентами аппроксимирующей функции. Входными данными для подпрограммы являются: размерность матрицы, матрица коэффициентов и матрица свободных членов. Выводными данными будут коэффициенты аппроксимирующей функции.

Для удобства вычислений тексты программ по расчету для квадратичной и кубической функции написаны в разных файлах. Алгоритм программы написанной в QBasic представлен в блок-схеме 1. Тексты основных программ и подпрограммы представлены в приложении.

Блок-схема (алгоритм решения в QBasic):

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

a1=-25.26

a2=447.53

a3=-164.51

Kdet=0.9789

Для аппроксимации кубической функции:

a1= 85.44

a2=122.34

a3= 124.78

a4=-79.57

Kdet= 0.9979

Полученные в QBasic результаты вычислений совпали с результатами вычислений в Excel, что говорит о правильно составленной программе. Так же получили математически точные графики зависимости скорости бурения по граниту V перфоратором ПП-54 от усилия подачи.

Графики зависимостей:

Рис. 9. График квадратичной зависимости, вычисленный в QBasic

Рис. 10. График кубической зависимости, вычисленный в QBasic

Вывод

перфоратор бурение аппроксимация excel

В курсовой работе мы установили теоретическую зависимость скорости бурения V[мм/мин] по граниту перфоратором ПП-54 от усилия подачи F[кН]. Рассмотрели два варианта теоретической зависимости: квадратичная и кубическая. Для каждой зависимости вычислили коэффициенты функции и детерминированности с помощью табличного процессора Microsoft Excel. Так же построили линии тренда и графики теоретических и фактических данных. C малыми отклонениями сошлась только кубическая функция.

Коэффициенты детерминированности:

R_кв = 0,9789 - коэффициент детерминированности квадратичной аппроксимации;

R_куб = 0,9979 - коэффициент детерминированности кубической аппроксимации;

Сравнив линии тренда, графики теоретических и фактических данных и значения коэффициентов детерминированности, вычисленных в EXCEL, видно, что кубическая зависимость близка к фактически данным и для практических целей можно использовать кубическую аппроксимацию.

Библиографический список

DECLARE SUB GAUS (aa!(), b!(), e!(), r!)

CLS

n = 25

DIM x(n), y(n)

DATA 0.51,0.56,0.62,0.68,0.74,0.79,0.85,0.91,0.97,1.03,1.09,1.15,1.21, 1.27

DATA 1.33,1.39,1.45,1.49,1.54,1.59,1.65,1.72,1.79,1.86,1.91

FOR i = 1 TO n: READ x(i)

NEXT i

DATA 170,179,191,201,212,221,231,239,247,256,265,271,275,279,281

DATA 284,285,284,281,275,267,256,246,233,223

FOR i = 1 TO n: READ y(i):

NEXT i

FOR i = 1 TO n

sx = sx + x(i): sy = sy + y(i)

sx2 = sx2 + x(i) ^ 2: sxy = sxy + x(i) * y(i)

sx3 = sx3 + x(i) ^ 3: sx4 = sx4 + x(i) ^ 4: sx2y = sx2y + (x(i) ^ 2) * y(i)

NEXT i

xcp = sx / n: ycp = sy / n

PRINT "xcp="; xcp: PRINT "ycp="; ycp

FOR i = 1 TO n

s1 = s1 + (x(i) - xcp) * (y(i) - ycp)

s2 = s2 + (x(i) - xcp) ^ 2: s3 = s3 + (y(i) - ycp) ^ 2

NEXT i

r = s1 / (s2 ^ (1 / 2) * s3 ^ (1 / 2))

PRINT "Sx="; sx: PRINT "Sy="; sy

PRINT "Сумма (Xi-Xср)(Yi-Yср)="; s1

PRINT "Сумма (Xi-Xср)^2="; s2

PRINT "Сумма (Yi - Yср)^2 = "; s3

PRINT "r="; r

SLEEP: CLS

COLOR 10: PRINT "Квадратичная Аппроксимация": COLOR 7

r = 3

DIM aa(r, r), e(r), b(r)

aa(1, 1) = n: aa(1, 2) = sx: aa(1, 3) = sx2: b(1) = sy

aa(2, 1) = sx: aa(2, 2) = sx2: aa(2, 3) = sx3: b(2) = sxy

aa(3, 1) = sx2: aa(3, 2) = sx3: aa(3, 3) = sx4: b(3) = sx2y

CALL GAUS(aa(), b(), e(), r)

PRINT

PRINT USING "a1=###.##"; e(1): PRINT USING "a2=###.##"; e(2)

PRINT USING "a3=####.##"; e(3)

FOR i = 1 TO n

yte = (e(1) + e(2) * x(i) + e(3) * x(i) ^ 2)

yteor = yteor + yte

Spol = Spol + (y(i) - ycp) ^ 2

Soct = Soct + (yte - y(i)) ^ 2

Kdet1 = 1 - Soct / Spol

NEXT i

PRINT

PRINT USING "Kdet=#.####"; Kdet1

SLEEP: CLS

' ГРАФИК КВАДРАТИЧНОЙ ЗАВИСИМОСТИ

SCREEN 12

PAINT (1, 1)

'оси

LINE (40, 440)-(440, 440), 0

LINE (40, 40)-(40, 440), 0

'черточки на осях и подписи данных

FOR i = 1 TO 7

LINE (35, 40 + i * 50)-(45, 40 + i * 50), 0

LOCATE 28 - (i * 3) -.098 * i, 7: PRINT i * 50

NEXT i

FOR i =.5 TO 2 STEP.5

LINE (40 + i * 200, 435)-(40 + i * 200, 445), 0

LOCATE 27, 6 + (i * 24): PRINT i

NEXT i

LOCATE 3, 3: PRINT "V(мм/мин)": LOCATE 28, 58: PRINT "F(kH)"

'график эмпирических данных

FOR i = 1 TO 25

x(0) =.51

y(0) = 170

LINE (40 + 200 * x(i - 1), 440 - y(i - 1))-(40 + 200 * x(i), 440 - y(i)), 1

NEXT i

'график теоретической зависимости

FOR i =.51 TO 1.91 STEP.005

y = (e(1) + e(2) * i + e(3) * i ^ 2)

PSET (40 + i * 200, 440 - y), 13

NEXT i

'легенда

LOCATE 23, 20: PRINT "Эмпирические данные"

LINE (120, 360)-(145, 359), 1, BF

LOCATE 24, 20: PRINT "Теоретическая зависимость"

LINE (120, 376)-(145, 375), 13, BF

Кубическая аппроксимация

DECLARE SUB GAUS (aa!(), b!(), e!(), r!)

CLS

n = 25

DIM x(n), y(n)

DATA 0.51,0.56,0.62,0.68,0.74,0.79,0.85,0.91,0.97,1.03,1.09,1.15,1.21, 1.27

DATA 1.33,1.39,1.45,1.49,1.54,1.59,1.65,1.72,1.79,1.86,1.91

FOR i = 1 TO n: READ x(i)

NEXT i

DATA 170,179,191,201,212,221,231,239,247,256,265,271,275,279,281

DATA 284,285,284,281,275,267,256,246,233,223

FOR i = 1 TO n: READ y(i):

NEXT i

FOR i = 1 TO n

sx = sx + x(i)

sx2 = sx2 + x(i) ^ 2

sx3 = sx3 + x(i) ^ 3

sx4 = sx4 + x(i) ^ 4

sx5 = sx5 + x(i) ^ 5

sx6 = sx6 + x(i) ^ 6

sy = sy + y(i)

sxy = sxy + x(i) * y(i)

sx2y = sx2y + (x(i) ^ 2) * y(i)

sx3y = sx3y + (x(i) ^ 3) * y(i)

NEXT i

xcp = sx / n: ycp = sy / n

COLOR 10: PRINT "Кубическая аппроксимация": COLOR 7

r = 4

DIM aa(r, r), e(r), b(r)

aa(1, 1) = n: aa(1, 2) = sx: aa(1, 3) = sx2: aa(1, 4) = sx3: b(1) = sy

aa(2, 1) = sx: aa(2, 2) = sx2: aa(2, 3) = sx3: aa(2, 4) = sx4: b(2) = sxy

aa(3, 1) = sx2: aa(3, 2) = sx3: aa(3, 3) = sx4: aa(3, 4) = sx5: b(3) = sx2y

aa(4, 1) = sx3: aa(4, 2) = sx4: aa(4, 3) = sx5: aa(4, 4) = sx6: b(4) = sx3y

CALL GAUS(aa(), b(), e(), r)

PRINT USING "a1=###.##"; e(1): PRINT USING "a2=###.##"; e(2)

PRINT USING "a3=####.##"; e(3): PRINT USING "a4=###.##"; e(4)

FOR i = 1 TO n

yte = (e(1) + e(2) * x(i) + e(3) * x(i) ^ 2 + e(4) * x(i) ^ 3)

yteor = yteor + yte

Spol = Spol + (y(i) - ycp) ^ 2

Soct = Soct + (yte - y(i)) ^ 2

Kdet1 = 1 - Soct / Spol

NEXT i

PRINT

PRINT USING "Kdet=##.####"; Kdet1

SLEEP: CLS

' ГРАФИК КУБИЧЕСКОЙ ЗАВИСИМОСТИ

SCREEN 12

PAINT (1, 1)

'оси

LINE (40, 440)-(440, 440), 0

LINE (40, 40)-(40, 440), 0

'черточки на осях и подписи данных

FOR i = 1 TO 7

LINE (35, 40 + i * 50)-(45, 40 + i * 50), 0

LOCATE 28 - (i * 3) -.098 * i, 7: PRINT i * 50

NEXT i

FOR i =.5 TO 2 STEP.5

LINE (40 + i * 200, 435)-(40 + i * 200, 445), 0

LOCATE 27, 6 + (i * 24): PRINT i

NEXT i

LOCATE 3, 3: PRINT "V(мм/мин)": LOCATE 28, 58: PRINT "F(kH)"

'график эмпирических данных

FOR i = 1 TO 25

x(0) =.51

y(0) = 170

LINE (40 + 200 * x(i - 1), 440 - y(i - 1))-(40 + 200 * x(i), 440 - y(i)), 1

NEXT i

'график теоретической зависимости

FOR i =.51 TO 1.91 STEP.005

y = (e(1) + e(2) * i + e(3) * i ^ 2 + e(4) * i ^ 3)

PSET (40 + i * 200, 440 - y), 13

NEXT i

'легенда

LOCATE 23, 20: PRINT "Эмпирические данные"

LINE (120, 360)-(145, 359), 1, BF

LOCATE 24, 20: PRINT "Теоретическая зависимость"

LINE (120, 376)-(145, 375), 13, BF

Подпрограмма

SUB gaus (aa(), b(), e(), r)

l = r + 1

DIM a(r, l)

FOR i = 1 TO r

a(i, l) = b(i)

FOR j = 1 TO r

a(i, j) = aa(i, j): NEXT j, i

PRINT "CИCTEMA УPABHEHИЙ"

FOR i = 1 TO r

FOR j = 1 TO l

PRINT a(i, j); : NEXT j

PRINT : NEXT i

FOR i = 1 TO r

D = a(i, i): t = i

FOR j = i TO r

IF ABS(a(j, i)) > ABS(D) THEN D = a(j, i): t = j

NEXT j

IF i <> t THEN

FOR j = i TO l

D = a(i, j): a(i, j) = a(t, j): a(t, j) = D: NEXT j

END IF

FOR j = l TO i STEP -1

a(i, j) = a(i, j) / a(i, i)

NEXT j

FOR k = i + 1 TO r

FOR j = l TO i STEP -1

a(k, j) = a(k, j) - a(i, j) * a(k, i)

NEXT j, k, i

e(r) = a(r, l)

FOR i = r - 1 TO 1 STEP -1

D = 0: FOR j = r TO i + 1 STEP -1

D = D + a(i, j) * e(j): NEXT j

e(i) = a(i, l) - D

NEXT i

PRINT " VEKTOR X: ";

FOR i = 1 TO r

PRINT USING "####.##"; e(i);

NEXT i

END SUB

Размещено на Allbest.ru