Метод наименьших квадратов и его реализация в MicrosoftExcel

МИНИСТЕРСТВО ОБРАЗОВАНИЯ РЕСПУБЛИКИ БЕЛАРУСЬ

Учреждение образования

«Брестский государственный университет имени А. С. Пушкина»

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

Кафедра преподавания математики и информатики

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

Метод наименьших квадратов и его реализация в

Microsoft Excel

 

 

 

 

 

 

 

 

 

 

 

 

Брест 2013

 

 

Оглавление

Введение

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

2. Расчетные формулы

2.1 Построение эмпирических формул  методом наименьших квадратов

2.2 Линеаризация экспоненциальной  зависимости

3. Расчет коэффициентов аппроксимации  в Microsoft Excel

4. Построение графиков в Excel и использование функции ЛИНЕЙН

5.Заключение

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1.Введение

     Метод наименьших  квадратов — один из методов  теории ошибок для оценки неизвестных  величин по результатам измерений, содержащим случайные ошибки.

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

     Когда искомая величина  может быть измерена непосредственно, как, например, длина  прямой или  угол, то, для увеличения точности, измерение производится много  раз, и за окончательный результат  берут арифметическое среднее  из всех отдельных измерений. Это правило арифметической середины  основывается на соображениях  теории вероятности; легко показать, что сумма квадратов уклонений  отдельных измерений от арифметической  середины будет меньше, чем сумма  квадратов уклонений отдельных  измерений от какой бы то  ни было другой величины. Само  правило арифметической середины  представляет, следовательно, простейший  случай метода наименьших квадратов.

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

     До  начала XIX в. учёные  не имели опредёленных правил для решения системы уравнений, в которой число неизвестных менее числа уравнений; до этого времени употреблялись частные приёмы, зависевшие от вида уравнений и от остроумия вычислителей, и потому разные вычислители, исходя из тех же данных наблюдений, приходили к различным выводам. Лежандру (1805—06) и Гауссу (1794—95) принадлежит первое применение к решению указанной системы уравнений теории вероятности, исходя из начал, аналогичных с началом арифметической середины, уже издавна и, так сказать, бессознательно применяемых к выводам результатов в простейшем случае многократных измерений. Как и в случае арифметической середины, вновь изобретённый способ не даёт, конечно, истинных значений искомых, но даёт зато вероятнейшие значения. Этот способ распространён и усовершенствован дальнейшими изысканиями Лапласа, Энке, Бесселя, Ганзена и др. и получил название метода наименьших квадратов, потому что после подстановки в начальные уравнения неизвестных величин, выведенных этим способом, в правых частях уравнений получаются если и не нули, то небольшие величины, сумма квадратов которых оказывается меньшей, чем сумма квадратов подобных же остатков, после подстановки каких бы то ни было других значений неизвестных. Помимо этого, решение уравнений по способу наименьших квадратов даёт возможность выводить вероятные ошибки неизвестных, то есть даёт величины, по которым судят о степени точности выводов.

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

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

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

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

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

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

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

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

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

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

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

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

 

 

 

 

 

 

 

 

 

 

2. Расчетные  формулы

2.1 Построение  эмпирических формул методом  наименьших квадратов

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

x			¼		¼
y			¼		¼

При аналитическом исследовании взаимосвязи между двумя величинами x и y производят ряд наблюдений и в результате получается таблица значений:

 

 

Эта таблица обычно получается как итог каких-либо экспериментов, в которых (независимая величина) задается экспериментатором, а получается в результате опыта. Поэтому эти значения будем называть эмпирическими или опытными значениями.

Между величинами x и y существует функциональная зависимость, но ее аналитический вид обычно неизвестен, поэтому возникает практически важная задача - найти эмпирическую формулу

(2.1.1)

 

(где  - параметры), значения которой при возможно мало отличались бы от опытных значений .

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

Если в эмпирическую формулу (2.1.1) подставить исходные , то получим теоретические значения , где .

Разности называются отклонениями и представляют собой расстояния по вертикали от точек до графика эмпирической функции.

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

(2.1.2)

 

будет минимальной.

Поясним геометрический смысл метода наименьших квадратов.

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

Построение эмпирической формулы состоит из двух этапов: выяснение общего вида этой формулы и определение ее наилучших параметров.

Если неизвестен характер зависимости между данными величинами x и y , то вид эмпирической зависимости является произвольным. Предпочтение отдается простым формулам, обладающим хорошей точностью. Удачный выбор эмпирической формулы в значительной мере зависит от знаний исследователя в предметной области, используя которые он может указать класс функций из теоретических соображений. Большое значение имеет изображение полученных данных в декартовых или в специальных системах координат (полулогарифмической, логарифмической и т.д.). По положению точек можно примерно угадать общий вид зависимости путем установления сходства между построенным графиком и образцами известных кривых.

Определение наилучших коэффициентов входящих в эмпирическую формулу производят хорошо известными аналитическими методами.

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

информационный аппроксимация корреляция линеаризация

   (2.1.3)

 

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

Эта система упрощается, если эмпирическая формула (2.1.1) линейна относительно параметров , тогда система (2.1.3) - будет линейной.

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

(2.1.4)

Эта линейная система может быть решена любым известным методом (методом Гаусса, простых итераций, формулами Крамера). В случае квадратичной зависимости система (2.1.3) примет вид: (2.1.5)

 

2.2 Линеаризация  экспоненциальной зависимости

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

(2.2.1)

где и неопределенные коэффициенты.

Линеаризация достигается путем логарифмирования равенства (2.2.1), после чего получаем соотношение

(2.2.2)

Обозначим и соответственно через и , тогда зависимость (2.2.1) может быть записана в виде , что позволяет применить формулы (2.1.4) с заменой на и на .

 

3. Расчет коэффициентов  аппроксимации в Microsoft Excel

Функция y=f(x) задана таблицей 1

Таблица 1 Исходные данные.

12.85	154.77	9.65	81.43	7.74	55.86	5.02	24.98	1.86	3.91
12.32	145.59	9.63	80.97	7.32	47.63	4.65	22.87	1.76	3.22
11.43	108.37	9.22	79.04	7.08	48.03	4.53	20.32	1.11	1.22
10.59	100.76	8.44	61.76	6.87	36.85	3.24	9.06	0.99	1.10
10.21	98.32	8.07	60.54	5.23	25.65	2.55	6.23	0.72	0.53

Требуется выяснить - какая из функций - линейная, квадратичная или экспоненциальная наилучшим образом аппроксимирует функцию заданную таблицей 1. Решение. Поскольку в данном примере каждая пара значений встречается один раз, то между и существует функциональная зависимость. Для проведения расчетов данные целесообразно расположить в виде таблицы 2, используя средства табличного процессора Microsoft Excel.

Таблица 2 Расчет сумм.

Поясним как таблица 2 составляется.

Шаг 1. В ячейки A2:A26 заносим значения .

Шаг 2. В ячейки B2:B26 заносим значения .

Шаг 3. В ячейку C2 вводим формулу =A2^2.

Шаг 4. В ячейки C3:C26 эта формула копируется.

Шаг 5. В ячейку D2 вводим формулу =A2*B2.

Шаг 6. В ячейки D3:D26 эта формула копируется.

Шаг 7. В ячейку F2 вводим формулу =A2^4.

Шаг 8. В ячейки F3:F26 эта формула копируется.

Шаг 9. В ячейку G2 вводим формулу =A2^2*B2.

Шаг 10. В ячейки G3:G26 эта формула копируется.

Шаг 11. В ячейку H2 вводим формулу =LN(B2).

Шаг 12. В ячейки H3:H26 эта формула копируется.

Шаг 13. В ячейку I2 вводим формулу =A2*LN(B2).

Шаг 14. В ячейки I3:I26 эта формула копируется.

Последующие шаги делаем с помощью автосуммирования .

Шаг 15. В ячейку A27 вводим формулу =СУММ(A2:A26).

Шаг 16. В ячейку B27 вводим формулу =СУММ(B2:B26).

Шаг 17. В ячейку C27 вводим формулу =СУММ(C2:C26).

Шаг 18. В ячейку D27 вводим формулу =СУММ(D2:D26).

Шаг 19. В ячейку E27 вводим формулу =СУММ(E2:E26).

Шаг 20. В ячейку F27 вводим формулу =СУММ(F2:F26).

Шаг 21. В ячейку G27 вводим формулу =СУММ(G2:G26).

Шаг 22. В ячейку H27 вводим формулу =СУММ(H2:H26).

Шаг 23. В ячейку I27 вводим формулу =СУММ(I2:I26).

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

Используя итоговые суммы таблицы 2, расположенные в ячейках A27, B27, C27 и D27, запишем систему в виде

 

 

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

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

 

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

 

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

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