Методом наименьших квадратов найти функцию. Метод наименьших квадратов в Excel

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

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

1. выбор формы связи (вида аналитического уравнения регрессии);
2. оценку параметров уравнения;
3. оценку качества аналитического уравнения регрессии.

Наиболее часто для описания статистической связи признаков используется линейная форма. Внимание к линейной связи объясняется четкой экономической интерпретацией ее параметров, ограниченной вариацией переменных и тем, что в большинстве случаев нелинейные формы связи для выполнения расчетов преобразуют (путем логарифмирования или замены переменных) в линейную форму.
В случае линейной парной связи уравнение регрессии примет вид: y i =a+b·x i +u i . Параметры данного уравнения а и b оцениваются по данным статистического наблюдения x и y . Результатом такой оценки является уравнение: , где , - оценки параметров a и b , - значение результативного признака (переменной), полученное по уравнению регрессии (расчетное значение).

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

Задача оценивания параметров линейного парного уравнения методом наименьших квадратов состоит в следующем: получить такие оценки параметров , , при которых сумма квадратов отклонений фактических значений результативного признака - y i от расчетных значений – минимальна.
Формально критерий МНК можно записать так: .

Классификация методов наименьших квадратов

1. Метод наименьших квадратов.
2. Метод максимального правдоподобия (для нормальной классической линейной модели регрессии постулируется нормальность регрессионных остатков).
3. Обобщенный метод наименьших квадратов ОМНК применяется в случае автокорреляции ошибок и в случае гетероскедастичности.
4. Метод взвешенных наименьших квадратов (частный случай ОМНК с гетероскедастичными остатками).

Проиллюстрируем суть классического метода наименьших квадратов графически . Для этого построим точечный график по данным наблюдений (x i , y i , i=1;n) в прямоугольной системе координат (такой точечный график называют корреляционным полем). Попытаемся подобрать прямую линию, которая ближе всего расположена к точкам корреляционного поля. Согласно методу наименьших квадратов линия выбирается так, чтобы сумма квадратов расстояний по вертикали между точками корреляционного поля и этой линией была бы минимальной.

Математическая запись данной задачи: .
Значения y i и x i =1...n нам известны, это данные наблюдений. В функции S они представляют собой константы. Переменными в данной функции являются искомые оценки параметров - , . Чтобы найти минимум функции 2-ух переменных необходимо вычислить частные производные данной функции по каждому из параметров и приравнять их нулю, т.е. .
В результате получим систему из 2-ух нормальных линейных уравнений:
Решая данную систему, найдем искомые оценки параметров:

Правильность расчета параметров уравнения регрессии может быть проверена сравнением сумм (возможно некоторое расхождение из-за округления расчетов).
Для расчета оценок параметров , можно построить таблицу 1.
Знак коэффициента регрессии b указывает направление связи (если b >0, связь прямая, если b <0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Формально значение параметра а – среднее значение y при х равном нулю. Если признак-фактор не имеет и не может иметь нулевого значения, то вышеуказанная трактовка параметра а не имеет смысла.

Оценка тесноты связи между признаками осуществляется с помощью коэффициента линейной парной корреляции - r x,y . Он может быть рассчитан по формуле: . Кроме того, коэффициент линейной парной корреляции может быть определен через коэффициент регрессии b: .
Область допустимых значений линейного коэффициента парной корреляции от –1 до +1. Знак коэффициента корреляции указывает направление связи. Если r x, y >0, то связь прямая; если r x, y <0, то связь обратная.
Если данный коэффициент по модулю близок к единице, то связь между признаками может быть интерпретирована как довольно тесная линейная. Если его модуль равен единице ê r x , y ê =1, то связь между признаками функциональная линейная. Если признаки х и y линейно независимы, то r x,y близок к 0.
Для расчета r x,y можно использовать также таблицу 1.

Для оценки качества полученного уравнения регрессии рассчитывают теоретический коэффициент детерминации – R 2 yx:

,
где d 2 – объясненная уравнением регрессии дисперсия y ;
e 2 - остаточная (необъясненная уравнением регрессии) дисперсия y ;
s 2 y - общая (полная) дисперсия y .
Коэффициент детерминации характеризует долю вариации (дисперсии) результативного признака y , объясняемую регрессией (а, следовательно, и фактором х), в общей вариации (дисперсии) y . Коэффициент детерминации R 2 yx принимает значения от 0 до 1. Соответственно величина 1-R 2 yx характеризует долю дисперсии y , вызванную влиянием прочих неучтенных в модели факторов и ошибками спецификации.
При парной линейной регрессии R 2 yx =r 2 yx .

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

Аппроксимация функции методом наименьших квадратов

Введение

эмпирический mathcad аппроксимация

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

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

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

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

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

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

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

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

xx 1 x 1 x i X n уy 1 y 1 y i Y n

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

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

y = f(x; a1, a2,…, am), (1)

(где a 1 , a 2 ,…, a m - параметры), значения которой при x = x, возможно мало отличались бы от опытных значений у, (i = 1,2,…, п) .

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

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

Y T i = f (x i ; a1, a2……a m ) , где i = 1,2,…, n .

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

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

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

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

Каждая пара чисел (x i , y i ) из исходной таблицы определяет точку M i на плоскости XOY. Используя формулу (1) при различных значениях коэффициентов a 1 , a 2 ,…, a m можно построить ряд кривых, которые являются графиками функции (1). Задача состоит в определении коэффициентов a 1 , a 2 ,…, a m таким образом, чтобы сумма квадратов расстояний по вертикали от точек M i (x i , y i ) до графика функции (1) была наименьшей (рис. 1).

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

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

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

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

В результате получим нормальную систему для определения коэффициентов a i (i = 1,2,…, m) :

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

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

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

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

1.2 Квадратичная зависимость

В случае квадратичной зависимости y = a 1 + a 2 x + a 3x2 система (3) примет вид:

1.3 Экспоненциальная зависимость

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

y = a 1 * e a2x (6)

где a1 иa2, неопределенные коффициенты.

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

ln y = ln a1 + a2x(7)

Обозначим ln у и ln a x соответственно через t и c , тогда зависимость (6) может быть записана в виде t = a 1 + a 2 х , что позволяет применить формулы (4) с заменой a 1 на c и у i на t i

1.4 Элементы теории корреляции

График восстановленной функциональной зависимости у(х) по результатам измерений (хi , у i ), i = 1,2, K , n называется кривой регрессии. Для проверки согласия построенной кривой регрессии с результатами эксперимента обычно вводят следующие числовые характеристики: коэффициент корреляции (линейная зависимость), корреляционное отношение и коэффициент детерминированности. При этом результаты обычно группируют и представляют в форме корреляционной таблицы. В каждой клетке этой таблицы приводятся численности n iJ - тех пар (х, у) , компоненты которых попадают в соответствующие интервалы группировки по каждой переменной. Предполагая длины интервалов группировки (по каждой переменной) равными между собой, выбирают центры хi (соответственно у i ) этих интервалов и числа n iJ - в качестве основы для расчетов.

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

Коэффициент корреляции вычисляется по формуле:

где, и - среднее арифметическое значение соответственно х и у .

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

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

Корреляционное отношение вычисляется по формуле:

где n i = , n f = , а числитель характеризует рассеяние условных средних у, около безусловного среднего y .

Всегда. Равенство = 0 соответствует некоррелированным случайным величинам; = 1 тогда и только тогда, когда имеется точная функциональная связь междуy и x. В случае линейной зависимости y от x корреляционное отношение совпадает с квадратом коэффициента корреляции. Величина - ? 2 используется в качестве индикатора отклонения регрессии от линейной.

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

Для его описания рассмотрим следующие величины. - полная сумма квадратов, где среднее значение.

Можно доказать следующее равенство

Первое слагаемое равно Sост = и называется остаточной суммой квадратов. Оно характеризует отклонение экспериментальных от теоритических.

Второе слагаемое равно Sрегр = 2 и называется регрессионной суммой квадратов и оно характеризует разброс данных.

Очевидно, что справедливо следующее равенство Sполн = Sост + Sрегр.

Коэффициент детерминированности определяется по формуле:

Чем меньше остаточная сумма квадратов по сравнению с общей суммой квадратов, тем больше значение коэффициента детерминированности r 2 , который показывает, насколько хорошо уравнение, полученное с помощью регрессионного анализа, объясняет взаимосвязи между переменными. Если он равен 1, то имеет место полная корреляция с моделью, т.е. нет различия между фактическим и оценочным значениями y. В противоположном случае, если коэффициент детерминированности равен 0, то уравнение регрессии неудачно для предсказания значений y

Коэффициент детерминированности всегда не превосходит корреляционное отношение. В случае когда выполняется равенство r 2 = то можно считать, что построенная эмпирическая формула наиболее точно отражает эмпирические данные.

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

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

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

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

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

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

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

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

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

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

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

Написать программу на одном из языков программирования и сравнить результаты счета с полученными выше.

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

Функция задана рисунком 1.

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

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

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

·в ячейки A6:A30 заносим значения xi.

·в ячейки B6:B30 заносим значения уi.

·в ячейку C6 вводим формулу =А6^2.

·в ячейки C7:C30 эта формула копируется.

·в ячейку D6 вводим формулу =А6*В6.

·в ячейки D7:D30 эта формула копируется.

·в ячейку F6 вводим формулу =А6^4.

·в ячейки F7:F30 эта формула копируется.

·в ячейку G6 вводим формулу =А6^2*В6.

·в ячейки G7:G30 эта формула копируется.

·в ячейку H6 вводим формулу =LN(B6).

·в ячейки H7:H30 эта формула копируется.

·в ячейку I6 вводим формулу =A6*LN(B6).

·в ячейки I7:I30 эта формула копируется. Последующие шаги делаем с помощью автосуммирования

·в ячейку А33 вводим формулу =СУММ (А6:А30).

·в ячейку B33 вводим формулу =СУММ (В6:В30).

·в ячейку C33 вводим формулу =СУММ (С6:С30).

·в ячейку D33 вводим формулу =СУММ (D6:D30).

·в ячейку E33 вводим формулу =СУММ (E6:E30).

·в ячейку F33 вводим формулу =СУММ (F6:F30).

·в ячейку G33 вводим формулу =СУММ (G6:G30).

·в ячейку H33 вводим формулу =СУММ (H6:H30).

·в ячейку I33 вводим формулу =СУММ (I6:I30).

Аппроксимируем функцию y = f (x) линейной функцией y = a 1 + a 2x. Для определения коэффициентов a1 и a2 воспользуемся системой (4). Используя итоговые суммы таблицы 2, расположенные в ячейках A33, B33, C33 и D33, запишем систему (4) в виде

решив которую, получим a1 = -24,7164 и a2 = 11,63183

Таким образом, линейная аппроксимация имеет вид y= -24,7164 + 11,63183х (12)

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

В таблице в ячейках A38:B39 записана формула {=МОБР (A35:B36)}. В ячейках E38:E39 записана формула {=МУМНОЖ (A38:B39, C35:C36)}.

Далее аппроксимируем функцию y = f (x) квадратичной функцией y = a 1 + a 2 x + a 3 x 2. Для определения коэффициентов a1, a2 и a3 воспользуемся системой (5). Используя итоговые суммы таблицы 2, расположенные в ячейках A33, B33, C33, D33, E33, F33 и G33 запишем систему (5) в виде:

Решив которую, получим a1 = 1,580946, a2 = -0,60819 и a3 = 0,954171 (14)

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

у = 1,580946 -0,60819х +0,954171 х 2

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

В таблице в ячейках A46:C48 записана формула {=МОБР (A41:C43)}. В ячейках F46:F48 записана формула {=МУМНОЖ (A41:C43, D46:D48)}.

Теперь аппроксимируем функцию y = f (х) экспоненциальной функцией y = a 1 e a2x . Для определения коэффициентов a 1 и a 2 прологарифмируем значения y i и используя итоговые суммы таблицы 2, расположенные в ячейках A26, C26, H26 и I26 получим систему:

где с = ln(a 1 ).

Решив систему (10) найдем с = 0,506435, a2 = 0.409819.

После потенцирования получим a1 = 1,659365.

Таким образом, экспоненциальная аппроксимация имеет вид y = 1,659365*e 0,4098194x

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

В таблице в ячейках A55:B56 записана формула {=МОБР (A51:B52)}. В ячейках E54:E56 записана формула {=МУМНОЖ (A51:B52, С51:С52)}. В ячейке E56 записана формула =EXP(E54).

Вычислим среднее арифметическое x и у по формулам:

Результаты расчета x и y средствами Microsoft Excel представлены на рисунке 6.

В ячейке B58 записана формула =A33/25. В ячейке B59 записана формула =B33/25.

Таблица 2

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

Ячейки A6:A33 и B6:B33 уже заполнены (см. рис. 2).

·в ячейку J6 вводим формулу =(A6-$B$58)*(B6-$B$59).

·в ячейки J7:J30 эта формула копируется.

·в ячейку K6 вводим формулу =(А6-$В$58)^2.

·в ячейки K7:K30 эта формула копируется.

·в ячейку L6 вводим формулу =(В1-$В$59)^2.

·в ячейки L7:L30 эта формула копируется.

·в ячейку M6 вводим формулу =($Е$38+$Е$39*А6-В6)^2.

·в ячейки M7:M30 эта формула копируется.

·в ячейку N6 вводим формулу =($F$46 +$F$47*A6 +$F$48*A6 Л6-В6)^2.

·в ячейки N7:N30 эта формула копируется.

·в ячейку O6 вводим формулу =($Е$56*ЕХР ($Е$55*А6) - В6)^2.

·в ячейки O7:O30 эта формула копируется.

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

·в ячейку J33 вводим формулу =CYMM (J6:J30).

·в ячейку K33 вводим формулу =СУММ (К6:К30).

·в ячейку L33 вводим формулу =CYMM (L6:L30).

·в ячейку M33 вводим формулу =СУММ (М6:М30).

·в ячейку N33 вводим формулу =СУММ (N6:N30).

·в ячейку O33 вводим формулу =СУММ (06:030).

Теперь проведем расчеты коэффициента корреляции по формуле (8) (только для линейной аппроксимации) и коэффициента детерминированности по формуле (10). Результаты расчетов средствами Microsoft Ехcеl представлены на рисунке 7.

В таблице 8 в ячейке B61 записана формула =J33/(K33*L33^(1/2). В ячейке B62 записана формула =1 - M33/L33. В ячейке B63 записана формула =1 - N33/L33. В ячейке B64 записана формула =1 - O33/L33.

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

4.1 Построение графиков в Excel

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

График линейной аппроксимации

График квадратичной аппроксимации

График экспоненциальной аппроксимации.

5. Аппроксимация функции с помощью MathCAD

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

.1 Линейная регрессия

Линейная регрессия в системе Mathcad выполняется по векторам аргумента Х и отсчетов Y функциями:

intercept (x, y) - вычисляет параметр а 1 , смещение линии регрессии по вертикали (см. рис.)

slope (x, y) - вычисляет параметр a 2 , угловой коэффициент линии регрессии (см. рис.)

y(x) = a1+a2*x

Функция corr (у, y(x)) вычисляет коэффициент корреляции Пирсона. Чем он ближе к 1, тем точнее обрабатываемые данные соответствуют линейной зависимости (см. рис.)

.2 Полиноминальная регрессия

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

regress (х, у, n) - вычисляет вектор S, в составе которого находятся коэффициенты ai полинома n -й степени;

Значения коэффициентов ai могут быть извлечены из вектора S функцией submatrix (S, 3, length(S) - 1, 0, 0).

Полученные значения коэффициентов используем в уравнении регрессии

y(x) = a1+a2*x+a3*x 2 (см. рис.)

.3 Нелинейная регрессия

Для простых типовых формул аппроксимации предусмотрен ряд функций нелинейной регрессии, в которых параметры функций подбираются программой Mathcad.

К их числу относится функция expfit (x, y, s), которая возвращает вектор, содержащий коэффициенты a1, a2 и a3 экспоненциальной функции

y(x) = a1 ^exp (a2 x) + a3. В вектор S вводятся начальные значения коэффициентов a1, a2 и a3 первого приближения.

Заключение

Анализ результатов расчетов показывает, что линейная аппроксимация наилучшим образом описывает экспериментальные данные.

Результаты полученные с помощью программы MathCAD полностью совпадают со значениями полученными с помощью Excel. Это говорит о верности вычислений.

Список используемой литературы

1. Информатика: Учебник / Под ред. проф. Н.В. Макаровой. М.: Финансы и статистика 2007
2. Информатика: Практикум по технологии работы на компьютере / Под. Ред. проф. Н.В. Макаровой. М Финансы и статистика, 2011.
3. Н.С. Пискунов. Дифференциальное и интегральное исчисление, 2010.
4. Информатика, Аппроксимация методом наименьших квадратов, методические указания, Санкт-Петербург, 2009.

Репетиторство

Нужна помощь по изучению какой-либы темы?

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

Аппроксима́ция , или приближе́ние - научный метод, состоящий в замене одних объектов другими, в том или ином смысле близкими к исходным, но более простыми.

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

В переносном смысле употребляется в философии как метод приближения , указание на приблизительный, неокончательный характер. Например, в таком смысле термин «аппроксимация» активно употреблялся Сёреном Кьеркегором (1813-1855) в «Заключительном ненаучном послесловии…»

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

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

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

Метод наименьших квадратов (МНК, англ. Ordinary Least Squares , OLS ) - математический метод, применяемый для решения различных задач, основанный на минимизации суммы квадратов некоторых функций от искомых переменных. Он может использоваться для «решения» переопределенных систем уравнений (когда количество уравнений превышает количество неизвестных), для поиска решения в случае обычных (не переопределенных) нелинейных систем уравнений, для аппроксимации точечных значений некоторой функцией. МНК является одним из базовых методов регрессионного анализа для оценки неизвестных параметров регрессионных моделей по выборочным данным.

Если некоторая физическая величина зависит от другой величины, то эту зависимость можно исследовать, измеряя y при различных значениях x . В результате измерений получается ряд значений:

x 1 , x 2 , ..., x i , ... , x n ;

y 1 , y 2 , ..., y i , ... , y n .

По данным такого эксперимента можно построить график зависимости y = ƒ(x). Полученная кривая дает возможность судить о виде функции ƒ(x). Однако постоянные коэффициенты, которые входят в эту функцию, остаются неизвестными. Определить их позволяет метод наименьших квадратов. Экспериментальные точки, как правило, не ложатся точно на кривую. Метод наименьших квадратов требует, чтобы сумма квадратов отклонений экспериментальных точек от кривой, т.е. 2 была наименьшей.

На практике этот метод наиболее часто (и наиболее просто) используется в случае линейной зависимости, т.е. когда

y = kx или y = a + bx.

Линейная зависимость очень широко распространена в физике. И даже когда зависимость нелинейная, обычно стараются строить график так, чтобы получить прямую линию. Например, если предполагают, что показатель преломления стекла n связан с длиной λ световой волны соотношением n = a + b/λ 2 , то на графике строят зависимость n от λ -2 .

Рассмотрим зависимость y = kx (прямая, проходящая через начало координат). Составим величину φ – сумму квадратов отклонений наших точек от прямой

.

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

или (19)

Вычисление показывает, что среднеквадратичная ошибка определения величины k равна при этом

, (20) где – n число измерений.

Рассмотрим теперь несколько более трудный случай, когда точки должны удовлетворить формуле y = a + bx (прямая, не проходящая через начало координат).

Задача состоит в том, чтобы по имеющемуся набору значений x i , y i найти наилучшие значения a и b.

Снова составим квадратичную форму φ , равную сумме квадратов отклонений точек x i , y i от прямой

и найдем значения a и b , при которых φ имеет минимум

;

.

Совместное решение этих уравнений дает

(21)

Среднеквадратичные ошибки определения a и b равны

(23)

. (24)

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

Пример 1. Исследовалось основное уравнение динамики вращательного движения ε = M/J (прямая, проходящая через начало координат). При различных значениях момента M измерялось угловое ускорение ε некоторого тела. Требуется определить момент инерции этого тела. Результаты измерений момента силы и углового ускорения занесены во второй и третий столбцы таблицы 5 .

Таблица 5

По формуле (19) определяем:

.

Для определения среднеквадратичной ошибки воспользуемся формулой (20)

0.005775 кг -1 · м -2 .

По формуле (18) имеем

S J = (2.996 · 0.005775)/0.3337 = 0.05185 кг · м 2 .

Задавшись надежностью P = 0.95 , по таблице коэффициентов Стьюдента для n = 5, находим t = 2.78 и определяем абсолютную ошибку ΔJ = 2.78 · 0.05185 = 0.1441 ≈ 0.2 кг · м 2 .

Результаты запишем в виде:

J = (3.0 ± 0.2) кг · м 2 ;

Пример 2. Вычислим температурный коэффициент сопротивления металла по методу наименьших квадратов. Сопротивление зависит от температуры по линейному закону

R t = R 0 (1 + α t°) = R 0 + R 0 α t°.

Свободный член определяет сопротивление R 0 при температуре 0° C , а угловой коэффициент – произведение температурного коэффициента α на сопротивление R 0 .

Результаты измерений и расчетов приведены в таблице (см. таблицу 6 ).

Таблица 6

							(r - bt - a) 2 ,10 -6

По формулам (21), (22) определяем

R 0 = ¯R- α R 0 ¯ t = 1.4005 - 0.002645 · 85.83333 = 1.1735 Ом .

Найдем ошибку в определении α. Так как , то по формуле (18) имеем:

.

Пользуясь формулами (23), (24) имеем

;

0.014126 Ом .

Задавшись надежностью P = 0.95, по таблице коэффициентов Стьюдента для n = 6, находим t = 2.57 и определяем абсолютную ошибку Δα = 2.57 · 0.000132 = 0.000338 град -1 .

α = (23 ± 4) · 10 -4 град -1 при P = 0.95.

Пример 3. Требуется определить радиус кривизны линзы по кольцам Ньютона. Измерялись радиусы колец Ньютона r m и определялись номера этих колец m. Радиусы колец Ньютона связаны с радиусом кривизны линзы R и номером кольца уравнением

r 2 m = mλR - 2d 0 R,

где d 0 – толщина зазора между линзой и плоскопараллельной пластинкой (или деформация линзы),

λ – длина волны падающего света.

λ = (600 ± 6) нм; r 2 m = y; m = x; λR = b; -2d 0 R = a,

тогда уравнение примет вид y = a + bx .

Результаты измерений и вычислений занесены в таблицу 7 .

Таблица 7

		y = r 2 , 10 -2 мм 2				y - bx - a, 10 -4	(y - bx - a) 2 , 10 -6

Рассчитываем:

1. a и b по формулам (21), (22).

a = ¯ r 2 - b¯m = (0.208548333 - 0.0594957 · 3.5) = 0.0003133 мм 2 .

2. Рассчитаем среднеквадратичные ошибки для величин b и a по формулам (23), (24)

3. При надежности P = 0.95 по таблице коэффициентов Стьюдента для n = 6 находим t = 2.57 и определям абсолютные ошибки

Δb = 2.57 · 0.000211179 = 6·10 -4 мм 2 ;

Δa = 2.57 · 0.000822424 = 3· 10 -3 мм 2 .

4. Записываем результаты

b = (595 ± 6)·10 -4 мм 2 при Р = 0.95;

a = (0.3 ± 3)·10 -3 мм 2 при Р = 0.95;

Из полученных результатов опыта следует, что в пределах ошибки этого опыта прямая r 2 m = ƒ(m) проходит через начало координат, т.к. если ошибка значения какого-либо параметра окажется сравнимой или превысит значение параметра, то это означает, что скорей всего, настоящее значение этого параметра равно нулю.

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

5. Подсчитаем радиус кривизны линзы:

R = b / λ = 594.5 / 6 = 99.1 мм .

6. Так как для длины волны дана систематическая ошибка, подсчитаем и для R систематическую ошибку по формуле (16), взяв в качестве систематической ошибки величины b ее случайную ошибку Δb.

Записываем окончательный результат R = (99 ± 2) мм ε ≈ 3% при P = 0.95.

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

Постановка задачи на конкретном примере

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

Итак, пусть X — торговая площадь продовольственного магазина, измеряемая в квадратных метрах, а Y — годовой товарооборот, определяемый в миллионах рублей.

Требуется сделать прогноз, какой товарооборот (Y) будет у магазина, если у него та или иная торговая площадь. Очевидно, что функция Y = f (X) возрастающая, так как гипермаркет продает больше товаров, чем ларек.

Несколько слов о корректности исходных данных, используемых для предсказания

Допустим, у нас есть таблица, построенная по данным для n магазинов.

Согласно математической статистике, результаты будут более-менее корректными, если исследуются данные по хотя бы 5-6 объектам. Кроме того, нельзя использовать «аномальные» результаты. В частности, элитный небольшой бутик может иметь товарооборот в разы больший, чем товарооборот больших торговых точек класса «масмаркет».

Суть метода

Данные таблицы можно изобразить на декартовой плоскости в виде точек M 1 (x 1 , y 1), … M n (x n , y n). Теперь решение задачи сведется к подбору аппроксимирующей функции y = f (x), имеющей график, проходящий как можно ближе к точкам M 1, M 2, .. M n .

Конечно, можно использовать многочлен высокой степени, но такой вариант не только труднореализуем, но и просто некорректен, так как не будет отражать основную тенденцию, которую и нужно обнаружить. Самым разумным решением является поиск прямой у = ax + b, которая лучше всего приближает экспериментальные данные, a точнее, коэффициентов - a и b.

Оценка точности

При любой аппроксимации особую важность приобретает оценка ее точности. Обозначим через e i разность (отклонение) между функциональными и экспериментальными значениями для точки x i , т. е. e i = y i - f (x i).

Очевидно, что для оценки точности аппроксимации можно использовать сумму отклонений, т. е. при выборе прямой для приближенного представления зависимости X от Y нужно отдавать предпочтение той, у которой наименьшее значение суммы e i во всех рассматриваемых точках. Однако, не все так просто, так как наряду с положительными отклонениями практически будут присутствовать и отрицательные.

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

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

В Excel, как известно, существует встроенная функция автосуммы, позволяющая вычислить значения всех значений, расположенных в выделенном диапазоне. Таким образом, ничто не помешает нам рассчитать значение выражения (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

В математической записи это имеет вид:

Так как изначально было принято решение об аппроксимировании с помощью прямой, то имеем:

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

Для этого требуется приравнять к нулю частные производные по новым переменным a и b, и решить примитивную систему, состоящую из двух уравнений с 2-мя неизвестными вида:

После нехитрых преобразований, включая деление на 2 и манипуляции с суммами, получим:

Решая ее, например, методом Крамера, получаем стационарную точку с некими коэффициентами a * и b * . Это и есть минимум, т. е. для предсказания, какой товарооборот будет у магазина при определенной площади, подойдет прямая y = a * x + b * , представляющая собой регрессионную модель для примера, о котором идет речь. Конечно, она не позволит найти точный результат, но поможет получить представление о том, окупится ли покупка в кредит магазина конкретной площади.

Как реализоавать метод наименьших квадратов в Excel

В "Эксель" имеется функция для расчета значения по МНК. Она имеет следующий вид: «ТЕНДЕНЦИЯ» (известн. значения Y; известн. значения X; новые значения X; конст.). Применим формулу расчета МНК в Excel к нашей таблице.

Для этого в ячейку, в которой должен быть отображен результат расчета по методу наименьших квадратов в Excel, введем знак «=» и выберем функцию «ТЕНДЕНЦИЯ». В раскрывшемся окне заполним соответствующие поля, выделяя:

• диапазон известных значений для Y (в данном случае данные для товарооборота);
• диапазон x 1 , …x n , т. е. величины торговых площадей;
• и известные, и неизвестные значения x, для которого нужно выяснить размер товарооборота (информацию об их расположении на рабочем листе см. далее).

Кроме того, в формуле присутствует логическая переменная «Конст». Если ввести в соответствующее ей поле 1, то это будет означать, что следует осуществить вычисления, считая, что b = 0.

Если нужно узнать прогноз для более чем одного значения x, то после ввода формулы следует нажать не на «Ввод», а нужно набрать на клавиатуре комбинацию «Shift» + «Control»+ «Enter» («Ввод»).

Некоторые особенности

Регрессионный анализ может быть доступен даже чайникам. Формула Excel для предсказания значения массива неизвестных переменных — «ТЕНДЕНЦИЯ» — может использоваться даже теми, кто никогда не слышал о методе наименьших квадратов. Достаточно просто знать некоторые особенности ее работы. В частности:

• Если расположить диапазон известных значений переменной y в одной строке или столбце, то каждая строка (столбец) с известными значениями x будет восприниматься программой в качестве отдельной переменной.
• Если в окне «ТЕНДЕНЦИЯ» не указан диапазон с известными x, то в случае использования функции в Excel программа будет рассматривать его как массив, состоящий из целых чисел, количество которых соответствует диапазону с заданными значениями переменной y.
• Чтобы получить на выходе массив «предсказанных» значений, выражение для вычисления тенденции нужно вводить как формулу массива.
• Если не указаны новые значения x, то функция «ТЕНДЕНЦИЯ» считает их равным известным. Если и они не заданы, то в качестве аргумента берется массив 1; 2; 3; 4;…, который соразмерен диапазону с уже заданными параметрами y.
• Диапазон, содержащий новые значения x должен состоять из такого же или большего количества строк или столбцов, как диапазон с заданными значениями y. Иными словами он должен быть соразмерным независимым переменным.
• В массиве с известными значениями x может содержаться несколько переменных. Однако если речь идет лишь об одной, то требуется, чтобы диапазоны с заданными значениями x и y были соразмерны. В случае нескольких переменных нужно, чтобы диапазон с заданными значениями y вмещался в одном столбце или в одной строке.

Функция «ПРЕДСКАЗ»

Реализуется с помощью нескольких функций. Одна из них называется «ПРЕДСКАЗ». Она аналогична «ТЕНДЕНЦИИ», т. е. выдает результат вычислений по методу наименьших квадратов. Однако только для одного X, для которого неизвестно значение Y.

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

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

Рассмотрим процедуру оценки параметров линейной эконометрической модели с помощью метода наименьших квадратов более подробно. Такая модель в общем виде может быть представлена уравнением (1.2):

y t = a 0 + a 1 х 1 t +...+ a n х nt + ε t .

Исходными данными при оценке параметров a 0 , a 1 ,..., a n является вектор значений зависимой переменной y = (y 1 , y 2 , ... , y T)" и матрица значений независимых переменных

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

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

Примеры решения задач методом наименьших квадратов

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

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

Таблица 2.1

Номер магазина	Годовой товарооборот, млн руб.	Торговая площадь, тыс. м 2

Решение методом наименьших квадратов. Обозначим — годовой товарооборот -го магазина, млн руб.; — торговая площадь -го магазина, тыс. м 2 .

Рис.2.1. Диаграмма рассеяния для примера 2.1

Для определения формы функциональной зависимости между переменными и построим диаграмму рассеяния (рис. 2.1).

На основании диаграммы рассеяния можно сделать вывод о позитивной зависимости годового товарооборота от торговой площади (т.е. у будет расти с ростом ). Наиболее подходящая форма функциональной связи — линейная .

Информация для проведения дальнейших расчетов представлена в табл. 2.2. С помощью метода наименьших квадратов оценим параметры линейной однофакторной эконометрической модели

Таблица 2.2

Таким образом,

Cледовательно, при увеличении торговой площади на 1 тыс. м 2 при прочих равных условиях среднегодовой товарооборот увеличивается на 67,8871 млн руб.

Пример 2.2. Руководство предприятия заметило, что годовой товарооборот зависит не только от торговой площади магазина (см. пример 2.1), но и от среднего числа посетителей. Соответствующая информация представлена в табл. 2.3.

Таблица 2.3

Решение. Обозначим — среднее число посетителей -го магазина в день, тыс. чел.

Для определения формы функциональной зависимости между переменными и построим диаграмму рассеяния (рис. 2.2).

На основании диаграммы рассеяния можно сделать вывод о позитивной зависимости годового товарооборота от среднего числа посетителей в день (т.е. у будет расти с ростом ). Форма функциональной зависимости — линейная.

Рис. 2.2. Диаграмма рассеяния для примера 2.2

Таблица 2.4

В целом необходимо определить параметры двухфакторной эконометрической модели

у t = a 0 + a 1 х 1 t + a 2 х 2 t + ε t

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

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

Таким образом,

Оценка коэффициента =61,6583 показывает, что при прочих равных условиях с увеличением торговой площади на 1 тыс. м 2 годовой товарооборот увеличится в среднем на 61,6583 млн руб.