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

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

Тема: Реализация численного метода средствами 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. Используя метод наименьших квадратов функцию Результаты коэффициентов линейной аппроксимации - №1 - открытая онлайн библиотека , заданную таблично, аппроксимировать

а) многочленом первой степени Результаты коэффициентов линейной аппроксимации - №2 - открытая онлайн библиотека ;

б) многочленом второй степени Результаты коэффициентов линейной аппроксимации - №3 - открытая онлайн библиотека ;

в) экспоненциальной зависимостью Результаты коэффициентов линейной аппроксимации - №4 - открытая онлайн библиотека .

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

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

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

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

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

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

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

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

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

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

Таблица 1

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

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

Результаты коэффициентов линейной аппроксимации - №6 - открытая онлайн библиотека , 1.1

где а1, а2, …, аm – параметры функции.

Пусть все измерения значений функции y1, y2, …, yn выполнены с одинаковой точностью. Согласно методу наименьших квадратов наилучшими коэффициентами а1, а2, …, аm считаются те, для которых

Результаты коэффициентов линейной аппроксимации - №7 - открытая онлайн библиотека , (1.2)

будет минимальной. Таким образом, параметры а1, а2, …, аm определяются из условия, чтобы сумма квадратов отклонений измеренных значений yi от Результаты коэффициентов линейной аппроксимации - №8 - открытая онлайн библиотека принимала наименьшее значение.

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

Результаты коэффициентов линейной аппроксимации - №9 - открытая онлайн библиотека , (1.3)

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

Система (1.3) упрощается, если функция Результаты коэффициентов линейной аппроксимации - №10 - открытая онлайн библиотека является линейной относительно параметров а1, а2, …, аm.

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

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

Результаты коэффициентов линейной аппроксимации - №11 - открытая онлайн библиотека , (1.1.1)

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

Результаты коэффициентов линейной аппроксимации - №12 - открытая онлайн библиотека (1.1.2)

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

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

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

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

y = axb, (1.2.1)

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

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

Результаты коэффициентов линейной аппроксимации - №14 - открытая онлайн библиотека, (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 обычно называют теоретической зависимостью.

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

Результаты коэффициентов линейной аппроксимации - №16 - открытая онлайн библиотека , (1.2.7)

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

Задача

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

– Многочлен первой степени Результаты коэффициентов линейной аппроксимации - №17 - открытая онлайн библиотека ,

– Показательную функцию Результаты коэффициентов линейной аппроксимации - №18 - открытая онлайн библиотека ,

– Степенную функцию Результаты коэффициентов линейной аппроксимации - №19 - открытая онлайн библиотека ,

– Многочлен второй степени Результаты коэффициентов линейной аппроксимации - №20 - открытая онлайн библиотека .

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

Результаты коэффициентов линейной аппроксимации - №21 - открытая онлайн библиотека , (2.1)

где Результаты коэффициентов линейной аппроксимации - №22 - открытая онлайн библиотека -заданные значения функции,

Результаты коэффициентов линейной аппроксимации - №23 - открытая онлайн библиотека -теоретические значения функции,

Результаты коэффициентов линейной аппроксимации - №24 - открытая онлайн библиотека -среднее арифметическое значение, i = 1, 2, …,n.

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

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

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

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

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.

Результаты коэффициентов линейной аппроксимации - №25 - открытая онлайн библиотека

Рисунок 1

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

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

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

– в ячейки 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 - открытая онлайн библиотека линейной функцией Результаты коэффициентов линейной аппроксимации - №27 - открытая онлайн библиотека .

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

Результаты коэффициентов линейной аппроксимации - №28 - открытая онлайн библиотека , (3.1)

решив которую по теореме Крамера, получим Результаты коэффициентов линейной аппроксимации - №29 - открытая онлайн библиотека .

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

Таким образом, линейная аппроксимация имеет вид Результаты коэффициентов линейной аппроксимации - №30 - открытая онлайн библиотека .

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

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

Результаты коэффициентов линейной аппроксимации - №31 - открытая онлайн библиотека

Рисунок 2

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

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

Далее аппроксимируем функцию Результаты коэффициентов линейной аппроксимации - №26 - открытая онлайн библиотека квадратичной функцией Результаты коэффициентов линейной аппроксимации - №33 - открытая онлайн библиотека . Для определения коэффициентов Результаты коэффициентов линейной аппроксимации - №34 - открытая онлайн библиотека , Результаты коэффициентов линейной аппроксимации - №35 - открытая онлайн библиотека и Результаты коэффициентов линейной аппроксимации - №36 - открытая онлайн библиотека воспользуемся системой

Результаты коэффициентов линейной аппроксимации - №37 - открытая онлайн библиотека

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

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

Результаты коэффициентов линейной аппроксимации - №38 - открытая онлайн библиотека

Далее аппроксимируем функцию Результаты коэффициентов линейной аппроксимации - №26 - открытая онлайн библиотека квадратичной функцией Результаты коэффициентов линейной аппроксимации - №33 - открытая онлайн библиотека . Для определения коэффициентов Результаты коэффициентов линейной аппроксимации - №34 - открытая онлайн библиотека , Результаты коэффициентов линейной аппроксимации - №35 - открытая онлайн библиотека и Результаты коэффициентов линейной аппроксимации - №36 - открытая онлайн библиотека воспользуемся системой

Результаты коэффициентов линейной аппроксимации - №37 - открытая онлайн библиотека

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

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

Результаты коэффициентов линейной аппроксимации - №45 - открытая онлайн библиотека

решив которую, получим Результаты коэффициентов линейной аппроксимации - №46 - открытая онлайн библиотека , Результаты коэффициентов линейной аппроксимации - №47 - открытая онлайн библиотека и Результаты коэффициентов линейной аппроксимации - №48 - открытая онлайн библиотека .

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

Результаты коэффициентов линейной аппроксимации - №49 - открытая онлайн библиотека

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