Результаты коэффициентов линейной аппроксимации.

ПОЯСНИТЕЛЬНАЯ ЗАПИСКА

Тема: Реализация численного метода средствами Microsoft Excel и с помощью средств пакета MathCAD

Автор: студент гр. МГП-12 __________________ /Румянцева Н.А./

^{(шифр группы) (подпись) (Ф.И.О.)}

ОЦЕНКА: _____________

Дата: _____________

ПРОВЕРИЛ:

Руководитель работы _доцент__ ____________ /Алейников А.А./

^{(должность) (подпись) (Ф.И.О.)}

Санкт-Петербург

Министерство образования и науки Российской Федерации
Национальный минерально-сырьевой университет "Горный"
		УТВЕРЖДАЮ Заведующий кафедрой   /_____________/ доц. Маховиков А.Б./   "___"__________2013 г.

Кафедра: ________Информатики и компьютерных технологий_______________

КУРСОВАЯ РАБОТА

по дисциплине _______________ИНФОРМАТИКА __________________________

^{(наименование учебной дисциплины согласно учебному плану)}

ЗАДАНИЕ

студенту группы МГП-12 Румянцева Н.А.

^{(шифр группы) (Ф.И.О.)}

1. Тема работы: _Реализация численного метода средствами Microsoft Excel и с помощью средств пакета MathCAD

2. Исходные данные к работе: _Вариант № 17__________________________________

_________

3. Содержание пояснительной записки: _Пояснительная записка включает в себя задание на выполнение работы, расчетные формулы, расчет с помощью таблиц (Microsoft Excel), результаты расчета, расчет с использованием пакета MathCAD, результаты расчета, графики, заключение, библиографический список_____________

4. Перечень графического материала: _Представление результатов в виде экранных форм____________________________________________________________________

5. Срок сдачи законченной работы: ___01.05.2013г.____________________________

Руководитель работы: ________ ______________ /_________/

^{(должность) (подпись) (Ф.И.О.)}

Дата выдачи задания: __15.02.2013 г.______________

Аннотация

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

Страниц 24, таблиц 3, рисунков 14, приложений 0.

Abstract

The explanatory note represents the report on term paper performance. In it questions on a finding of empirical formulas by a method of the least squares (МНК) by means of possibilities of package Microsoft Excel are considered, and also the decision of the given problem in Turbo Pascal 7.0 is considered. In work the equations of various kinds by means of approximation linear, square-law and экспоненциальной dependences are received. Upon termination of work the conclusion is drawn, the problem is solved by what method is better.

Pages 24, tables 3, figures 14, appendixes 0.

Оглавление

Аннотация. 2

Введение. 4

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

Общие сведения. 6

Линейная зависимость. 7

Нелинейная зависимость. 7

Задача. 9

Исходные данные. 10

Расчет аппроксимаций в табличном процессоре Excel 11

Построение графиков. 17

Функция ЛИНЕЙН.. 18

Вывод. 18

Выполнение аппроксимации в программе MathCAD.. 19

Введение. 19

Линейная аппроксимация в программе MathCAD.. 21

Экспоненциальная аппроксимация в программе MathCAD.. 22

Полиномальная (квадратичная аппроксимация в программе MathCAD.. 23

Вывод. 23

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

Введение

Аппроксимация (от латинского "approximare" -"приближаться") – научный метод, суть которого состоит в замене одних, известных значений, другими, приближёнными и более простыми. Эти простые значения должны удовлетворять некой зависимости, нахождение которой, в целом, и есть конечная цель этого метода.

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

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

2. Несовершенством человеческих знаний о природе – отнюдь не все современные научные концепции позволяют точно рассчитать какие-либо значения для реальных случаев – многие из них направлены на случаи идеальные.

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

При выборе аппроксимации следует исходить из конкретной задачи исследования. Обычно, чем более простое уравнение используется для аппроксимации, тем более приблизительно получаемое описание зависимости. Поэтому важно считывать, насколько существенны и чем обусловлены отклонения конкретных значений от получаемого тренда. При описании зависимости эмпирически определенных значений можно добиться и гораздо большей точности, используя какое-либо более сложное, многопараметрическое уравнение. Однако нет никакого смысла стремиться с максимальной точностью передать случайные отклонения величин в конкретных рядах эмпирических данных. Гораздо важнее уловить общую закономерность, которая в данном случае наиболее логично и с приемлемой точностью выражается именно двухпараметрическим уравнением степенной функции. Таким образом, выбирая метод аппроксимации, исследователь всегда идет на компромисс: решает, в какой степени в данном случае целесообразно и уместно «пожертвовать» деталями и, соответственно, насколько обобщенно следует выразить зависимость сопоставляемых переменных. Наряду с выявлением закономерностей, замаскированных случайными отклонениями эмпирических данных от общей закономерности, аппроксимация позволяет также решать много других важных задач: формализовать найденную зависимость; найти неизвестные значения зависимой переменной путем интерполяции или, если это допустимо, экстраполяции.

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

1. Используя метод наименьших квадратов функцию , заданную таблично, аппроксимировать

а) многочленом первой степени ;

б) многочленом второй степени ;

в) экспоненциальной зависимостью .

2. Для каждой зависимости вычислить коэффициент детерминированности.

3. Вычислить коэффициент корреляции (только в случае а).

4. Для каждой зависимости построить линию тренда.

5. Используя функцию ЛИНЕЙН вычислить числовые характеристики зависимости y от x.

6. Сравнить свои вычисления с результатами, полученными при помощи функции ЛИНЕЙН.

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

8. Выполнить обработку заданных экспериментальных данных с использованием встроенных функций интерполяции (аппроксимации) и регрессии пакета MathCAD и сравнить результаты с результатами, полученными в Microsoft Excel.

Общие сведения

При экспериментальном изучении функциональной зависимости y = f(x) производят измерения величины y при различных значениях величины x. Результаты представляют в виде таблицы 1 или графически.

X	x1	x2	×××	xn
Y	x1	Y2	×××	yn

Таблица 1

Задача заключается в аналитическом представлении искомой функциональной зависимости, т.е. в подборе формулы, описывающей результаты эксперимента. Эмпирическую формулу обычно выбирают из достаточно узкого класса функций, рассматривая, например, множество функций линейных, степенных, показательных и т.п. При этом руководствуются какими либо теоретическими соображениями или соображениями простоты представления эмпирического материала. Найденная эмпирическая формула должна быть такой, чтобы вычисленные по ней значения функций при X=x_i возможно мало отличалось бы от опытных данных y_i (i = 1, 2, …,n).

Обозначим выбранную функциональную зависимость

,

1.1

где а₁, а₂, …, а_m – параметры функции.

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

,

(1.2)

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

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

,

(1.3)

Решив систему (1.3), получим значения искомых параметров а1, а2, …, аm.

Система (1.3) упрощается, если функция является линейной относительно параметров а1, а2, …, аm.

Линейная зависимость

Рассмотрим линейную зависимость между X и Y. Это значит, что для переменных X и Y соответствующие значения x_i и y_i (i =1, 2, …, n) таковы, что точки М(x, y) располагаются на прямой линии. В этом случае эмпирическая функция имеет вид

,

(1.1.1)

где а1, а2 –неизвестные параметры, а система (1.3) примет вид

(1.1.2)

Эта линейная зависимость может быть решена известным методом, например, методом Гаусса или по формулам Крамера.

Нелинейная зависимость

Пусть для переменных X и Y соответствующие значения xi и yi (i = 1, 2, …, n) таковы, что точки М(xi , yi) не располагаются на прямой линии. Тогда во многих случаях можно ввести новые переменные такие, чтобы преобразование точки лежали на прямой.

В частности, путем логарифмирования можно свести к линейной степенную зависимость

y = axb,

(1.2.1)

где a, b –постоянные причем x > 0 и y > 0.

Логарифмируя равенство (1.2.1), получим

,

(1.2.2)

полагая Y = lnx, v = lny, u = lna,

получим зависимость

v = bY+u,

(1.2.3)

и применив формулы (1.1.2), найдем значения параметров b и u, а затем значение параметра а.

Показательную зависимость

y = aebx,

(1.2.4)

где а, b - постоянные, а>0, также можно привести к линейной путем логарифмирования равенства (1.2.4).

В этом случае

v = lna+bx,

(1.2.5)

Полагая v = lny, c = lna, Y = x, получим линейную зависимость

Таблица №3.6

v=bY+c,

(1.2.6)

и применив формулы (1.1.2), найдем значения b, c, а затем вычислим значение а.

Найденную эмпирическую зависимость Y от X обычно называют теоретической зависимостью.

Подставив в соответствующую формулу значения x_i из таблицы 1, получают -теоретическое значение функции (i = 1, 2, …, n). Далее вычисляют сумму квадратов отклонений эмпирических значений y_i из таблицы 1 и теоретических значений

,

(1.2.7)

Чем меньше значение Q, тем лучше соответствует эмпирическая формула экспериментальным данным.

Задача

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

– Многочлен первой степени ,

– Показательную функцию ,

– Степенную функцию ,

– Многочлен второй степени .

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

,

(2.1)

где -заданные значения функции,

-теоретические значения функции,

-среднее арифметическое значение, i = 1, 2, …,n.

Если коэффициент детерминированности равен 1, то теоретические и эмпирические значения функции полностью совпадают. Если коэффициент

детерминированности равен 0, то теоретическая зависимость выбрана неудачно.

Исходные данные

Был проведён некоторый эксперимент. Его результаты записаны в виде таблицы, где x_i – величина, задаваемая исследователем (например – концентрация реагентов в химическом растворе), y_i – измеренная величина (в нашем примере это может быть скорость протекания реакции).

xi	yi	xi	yi	xi	yi	xi	yi	xi	yi
0.21	1.62	4.98	40.09	7.96	63.31	12.33	97.77	17.32	126.45
1.19	8.65	5.49	43.56	8.32	67.45	13.21	105.34	18.43	144.34
2.43	16.76	6.07	48.45	9.43	72.87	14.72	112.56	19.38	160.45
3.12	24.45	6.81	52.21	10.21	81.34	15.53	121.89	20.45	161.34
4.54	32.87	7.21	57.34	11.54	89.45	16.23	108.54	21.22	170.59

Таблица 2

Расчет аппроксимаций в табличном процессоре Excel

Для проведения расчетов целесообразно воспользоваться табличным процессором Microsoft Excel.

Рисунок 1

Для этого заносим:

– в ячейки А1:А25 значения x_i;

– в ячейки В1:B25 значения y_i;

– в ячейки C1 вводим формулу =А1^2 и копируем ее до ячейки C25;

– в ячейки D1 водим формулу =А1^B1 и копируем ее до ячейки D25;

– в ячейки E1 вводим формулу =А1^3 и копируем ее до ячейки E25;

– в ячейки F1 вводим формулу =А1^4 и копируем ее до ячейки F25;

– в ячейки G1 вводим формулу =C1*B1 и копируем ее до ячейки G25;

– в ячейки H1 вводим формулу =ln(B1) и копируем ее до ячейки H25;

– в ячейки I1 вводим формулу =A1*H1 и копируем ее до ячейки I25;

После этого считаем суммы получившихся значений (с помощью автосуммы å), для этого в ячейку А26 вводим формулу = СУММ(А1:А25), после чего копируем ее до ячейки I26.

Аппроксимируем функцию линейной функцией .

Для этого, используя итоговые суммы, расположенные в ячейках А26, В26, C26 и D26, запишем систему в виде:

,

(3.1)

решив которую по теореме Крамера, получим .

В ячейках A33:B34 записываем исходную матрицу. В ячейки A37:B38 записываем обратную матрицу от истинной матрицы соответственно. Для этого выделим ячейки А37:В38 и введем формулу = МОБР(А33:В34) и нажимаем комбинацию клавиш Ctrl+Shift+Enter. Для нахождения коэффициента а₁ и а₂ в ячейки Е37:Е38 вводим формулу =МУМНОЖ(А37:В38;С33:С34).

Таким образом, линейная аппроксимация имеет вид .

Решение системы проводили, пользуясь средствами Microsoft Excel.

Результаты коэффициентов линейной аппроксимации.

Рисунок 2

В ячейках A37:B38 записана формула {=МОБР(A33:B34)}.

В ячейках E37:E38 записана формула {=МУМНОЖ(A37:B38,C33:C34)}.

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

Используя итоговые суммы таблицы 2,

расположенные в ячейках A26, B26, C26, D26, E26, F26 и G26 запишем систему в виде

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

Используя итоговые суммы рисунка 1,

расположенные в ячейках A26, B26, C26, D26, E26, F26 и G26 запишем систему в виде

решив которую, получим , и .

Таким образом, квадратичная аппроксимация имеет вид:

Решение системы проводили, пользуясь средствами Microsoft Excel.