Параболическая аппроксимация по методу наименьших квадратов. Общая методика решения. Вывод формул для нахождения коэффициентов
Аппроксима́ция , или приближе́ние - научный метод, состоящий в замене одних объектов другими, в том или ином смысле близкими к исходным, но более простыми.
Аппроксимация позволяет исследовать числовые характеристики и качественные свойства объекта, сводя задачу к изучению более простых или более удобных объектов (например, таких, характеристики которых легко вычисляются или свойства которых уже известны). В теории чисел изучаются диофантовы приближения, в частности, приближения иррациональных чисел рациональными. В геометрии рассматриваются аппроксимации кривых ломаными. Некоторые разделы математики в сущности целиком посвящены аппроксимации, например, теория приближения функций, численные методы анализа.
В переносном смысле употребляется в философии как метод приближения , указание на приблизительный, неокончательный характер. Например, в таком смысле термин «аппроксимация» активно употреблялся Сёреном Кьеркегором (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.
Аппроксимация (от латинского "approximate" -"приближаться")- приближенное выражение каких-либо математических объектов (например, чисел или функций) через другие более простые, более удобные в пользовании или просто более известные. В научных исследованиях аппроксимация применяется для описания, анализа, обобщения и дальнейшего использования эмпирических результатов.
Как известно, между величинами может существовать точная (функциональная) связь, когда одному значению аргумента соответствует одно определенное значение.
При выборе аппроксимации следует исходить из конкретной задачи исследования. Обычно, чем более простое уравнение используется для аппроксимации, тем более приблизительно получаемое описание зависимости. Поэтому важно считывать, насколько существенны и чем обусловлены отклонения конкретных значений от получаемого тренда. При описании зависимости эмпирически определенных значений можно добиться и гораздо большей точности, используя какое-либо более сложное, многопараметрическое уравнение. Однако нет никакого смысла стремиться с максимальной точностью передать случайные отклонения величин в конкретных рядах эмпирических данных. Выбирая метод аппроксимации, исследователь всегда идет на компромисс: решает, в какой степени в данном случае целесообразно и уместно «пожертвовать» деталями и, соответственно, насколько обобщенно следует выразить зависимость сопоставляемых переменных. Наряду с выявлением закономерностей замаскированных случайными отклонениями эмпирических данных от общей закономерности, аппроксимация позволяет также решать много других важных задач: формализовать найденную зависимость; найти неизвестные значения зависимой переменной путем интерполяции или, если это допустимо, экстраполяции.
Целью данной курсовой работы является изучение теоретических основ аппроксимации табулированной функции методом наименьших квадратов, и, применяя теоретические знания, нахождение аппроксимирующих полиномов. Нахождение аппроксимирующих полиномов в рамках данной курсовой работы следует путем написания программы на языке Pascal, реализующую разработанный алгоритм нахождения коэффициентов аппроксимирующего полинома, а также решить эту же задачу средствами MathCad.
В данной курсовой работе программа на языке Pascal разработана в оболочке PascalABC версия 1.0 beta. Решение задачи в среде MathCad производили в Mathcad версия 14.0.0.163.
Постановка задачи
В данной курсовой работе необходимо выполнить следующее:
1. Разработать алгоритм нахождения коэффициентов трёх аппроксимирующих полиномов (многочленов) вида
для табулированной функции y=f(x):для степени полиномов n=2, 4, 5.
2. Построить блок-схему алгоритма.
3. Создать программу на языке Pascal, реализующую разработанный алгоритм.
5. Построить графики 3-х полученных приближающих функций в одной системе координат. На графике должны содержаться и исходные точки (х i , y i ) .
6. Решить задачу средствами MathCAD.
Результаты решения задачи с помощью созданной программы на языке Pascal и в среде MathCAD нужно представить в виде построенных с помощью найденных коэффициентов трёх полиномов; таблицы, содержащей полученные с помощью найденных полиномов значения функции в точках хi и среднеквадратичных отклонений.
Построение эмпирических формул методом наименьших квадратов
Очень часто, особенно при анализе эмпирических данных возникает необходимость найти в явном виде функциональную зависимость между величинами x и y , которые получены в результате измерений.
При аналитическом исследовании взаимосвязи между двумя величинами x и y производят ряд наблюдений и в результате получается таблица значений:
| x | ¼ | ¼ | ||||
| y | ¼ | ¼ |
Эта таблица обычно получается как итог каких-либо экспериментов, в которых
Пример.
Экспериментальные данные о значениях переменных х и у приведены в таблице.
В
результате их выравнивания получена
функция
Используя метод наименьших квадратов , аппроксимировать эти данные линейной зависимостью y=ax+b (найти параметры а и b ). Выяснить, какая из двух линий лучше (в смысле метода наименьших квадратов) выравнивает экспериментальные данные. Сделать чертеж.
Суть метода наименьших квадратов (мнк).
Задача
заключается в нахождении коэффициентов
линейной зависимости, при которых
функция двух переменных а
и b
принимает
наименьшее значение. То есть, при данныха
и b
сумма квадратов отклонений экспериментальных
данных от найденной прямой будет
наименьшей. В этом вся суть метода
наименьших квадратов.
Таким образом, решение примера сводится к нахождению экстремума функции двух переменных.
Вывод формул для нахождения коэффициентов.
Составляется
и решается система из двух уравнений с
двумя неизвестными. Находим частные
производные функции
по
переменныма
и b
,
приравниваем эти производные к нулю.
Решаем
полученную систему уравнений любым
методом (например методом
подстановки
или методом
Крамера
)
и получаем формулы для нахождения
коэффициентов по методу наименьших
квадратов (МНК).
При
данных а
и
b
функция
принимает
наименьшее значение. Доказательство
этого факта приведенониже
по тексту в конце страницы
.
Вот и весь метод наименьших квадратов. Формула для нахождения параметра a содержит суммы ,,,и параметрn - количество экспериментальных данных. Значения этих сумм рекомендуем вычислять отдельно. Коэффициент b находится после вычисления a .
Пришло время вспомнить про исходый пример.
Решение.
В
нашем примере n=5
. Заполняем
таблицу для удобства вычисления сумм,
которые входят в формулы искомых
коэффициентов.
Значения в четвертой строке таблицы получены умножением значений 2-ой строки на значения 3-ей строки для каждого номера i .
Значения в пятой строке таблицы получены возведением в квадрат значений 2-ой строки для каждого номера i .
Значения последнего столбца таблицы – это суммы значений по строкам.
Используем
формулы метода наименьших квадратов
для нахождения коэффициентов а
и b
.
Подставляем в них соответствующие
значения из последнего столбца таблицы:
Следовательно, y = 0.165x+2.184 - искомая аппроксимирующая прямая.
Осталось
выяснить какая из линий y
= 0.165x+2.184
или
лучше
аппроксимирует исходные данные, то есть
произвести оценку методом наименьших
квадратов.
Оценка погрешности метода наименьших квадратов.
Для
этого требуется вычислить суммы квадратов
отклонений исходных данных от этих
линий
и
,
меньшее значение соответствует линии,
которая лучше в смысле метода наименьших
квадратов аппроксимирует исходные
данные.
Так как , то прямаяy = 0.165x+2.184 лучше приближает исходные данные.
Графическая иллюстрация метода наименьших квадратов (мнк).
На
графиках все прекрасно видно. Красная
линия – это найденная прямая y
= 0.165x+2.184
,
синяя линия – это
,
розовые точки – это исходные данные.
На практике при моделировании различных процессов - в частности, экономических, физических, технических, социальных - широко используются те или иные способы вычисления приближенных значений функций по известным их значениям в некоторых фиксированных точках.
Такого рода задачи приближения функций часто возникают:
при построении приближенных формул для вычисления значений характерных величин исследуемого процесса по табличным данным, полученным в результате эксперимента;
при численном интегрировании, дифференцировании, решении дифференциальных уравнений и т. д.;
при необходимости вычисления значений функций в промежуточных точках рассматриваемого интервала;
при определении значений характерных величин процесса за пределами рассматриваемого интервала, в частности при прогнозировании.
Если для моделирования некоторого процесса, заданного таблицей, построить функцию, приближенно описывающую данный процесс на основе метода наименьших квадратов, она будет называться аппроксимирующей функцией (регрессией), а сама задача построения аппроксимирующих функций - задачей аппроксимации.
В данной статье рассмотрены возможности пакета MS Excel для решения такого рода задач, кроме того, приведены методы и приемы построения (создания) регрессий для таблично заданных функций (что является основой регрессионного анализа).
В Excel для построения регрессий имеются две возможности.
Добавление выбранных регрессий (линий тренда - trendlines) в диаграмму, построенную на основе таблицы данных для исследуемой характеристики процесса (доступно лишь при наличии построенной диаграммы);
Использование встроенных статистических функций рабочего листа Excel, позволяющих получать регрессии (линии тренда) непосредственно на основе таблицы исходных данных.
Добавление линий тренда в диаграмму
Для таблицы данных, описывающих некоторый процесс и представленных диаграммой, в Excel имеется эффективный инструмент регрессионного анализа, позволяющий:
строить на основе метода наименьших квадратов и добавлять в диаграмму пять типов регрессий, которые с той или иной степенью точности моделируют исследуемый процесс;
добавлять к диаграмме уравнение построенной регрессии;
определять степень соответствия выбранной регрессии отображаемым на диаграмме данным.
На основе данных диаграммы Excel позволяет получать линейный, полиномиальный, логарифмический, степенной, экспоненциальный типы регрессий, которые задаются уравнением:
y = y(x)
где x - независимая переменная, которая часто принимает значения последовательности натурального ряда чисел (1; 2; 3; …) и производит, например, отсчет времени протекания исследуемого процесса (характеристики).
1 . Линейная регрессия хороша при моделировании характеристик, значения которых увеличиваются или убывают с постоянной скоростью. Это наиболее простая в построении модель исследуемого процесса. Она строится в соответствии с уравнением:
y = mx + b
где m - тангенс угла наклона линейной регрессии к оси абсцисс; b - координата точки пересечения линейной регрессии с осью ординат.
2 . Полиномиальная линия тренда полезна для описания характеристик, имеющих несколько ярко выраженных экстремумов (максимумов и минимумов). Выбор степени полинома определяется количеством экстремумов исследуемой характеристики. Так, полином второй степени может хорошо описать процесс, имеющий только один максимум или минимум; полином третьей степени - не более двух экстремумов; полином четвертой степени - не более трех экстремумов и т. д.
В этом случае линия тренда строится в соответствии с уравнением:
y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6
где коэффициенты c0, c1, c2,... c6 - константы, значения которых определяются в ходе построения.
3 . Логарифмическая линия тренда с успехом применяется при моделировании характеристик, значения которых вначале быстро меняются, а затем постепенно стабилизируются.
y = c ln(x) + b
4 . Степенная линия тренда дает хорошие результаты, если значения исследуемой зависимости характеризуются постоянным изменением скорости роста. Примером такой зависимости может служить график равноускоренного движения автомобиля. Если среди данных встречаются нулевые или отрицательные значения, использовать степенную линию тренда нельзя.
Строится в соответствии с уравнением:
y = c xb
где коэффициенты b, с - константы.
5 . Экспоненциальную линию тренда следует использовать в том случае, если скорость изменения данных непрерывно возрастает. Для данных, содержащих нулевые или отрицательные значения, этот вид приближения также неприменим.
Строится в соответствии с уравнением:
y = c ebx
где коэффициенты b, с - константы.
При подборе линии тренда Excel автоматически рассчитывает значение величины R2, которая характеризует достоверность аппроксимации: чем ближе значение R2 к единице, тем надежнее линия тренда аппроксимирует исследуемый процесс. При необходимости значение R2 всегда можно отобразить на диаграмме.
Определяется по формуле:
Для добавления линии тренда к ряду данных следует:
активизировать построенную на основе ряда данных диаграмму, т. е. щелкнуть в пределах области диаграммы. В главном меню появится пункт Диаграмма;
после щелчка на этом пункте на экране появится меню, в котором следует выбрать команду Добавить линию тренда.
Эти же действия легко реализуются, если навести указатель мыши на график, соответствующий одному из рядов данных, и щелкнуть правой кнопкой мыши; в появившемся контекстном меню выбрать команду Добавить линию тренда. На экране появится диалоговое окно Линия тренда с раскрытой вкладкой Тип (рис. 1).
После этого необходимо:
Выбрать на вкладке Тип необходимый тип линии тренда (по умолчанию выбирается тип Линейный). Для типа Полиномиальная в поле Степень следует задать степень выбранного полинома.
1 . В поле Построен на ряде перечислены все ряды данных рассматриваемой диаграммы. Для добавления линии тренда к конкретному ряду данных следует в поле Построен на ряде выбрать его имя.
При необходимости, перейдя на вкладку Параметры (рис. 2), можно для линии тренда задать следующие параметры:
изменить название линии тренда в поле Название аппроксимирующей (сглаженной) кривой.
задать количество периодов (вперед или назад) для прогноза в поле Прогноз;
вывести в область диаграммы уравнение линии тренда, для чего следует включить флажок показать уравнение на диаграмме;
вывести в область диаграммы значение достоверности аппроксимации R2, для чего следует включить флажок поместить на диаграмму величину достоверности аппроксимации (R^2);
задать точку пересечения линии тренда с осью Y, для чего следует включить флажок пересечение кривой с осью Y в точке;
щелкнуть на кнопке OK, чтобы закрыть диалоговое окно.
Для того, чтобы начать редактирование уже построенной линии тренда, существует три способа:
воспользоваться командой Выделенная линия тренда из меню Формат, предварительно выбрав линию тренда;
выбрать команду Формат линии тренда из контекстного меню, которое вызывается щелчком правой кнопки мыши по линии тренда;
двойным щелчком по линии тренда.
На экране появится диалоговое окно Формат линии тренда (рис. 3), содержащее три вкладки: Вид, Тип, Параметры, причем содержимое последних двух полностью совпадает с аналогичными вкладками диалогового окна Линия тренда (рис.1-2). На вкладке Вид, можно задать тип линии, ее цвет и толщину.
Для удаления уже построенной линии тренда следует выбрать удаляемую линию тренда и нажать клавишу Delete.
Достоинствами рассмотренного инструмента регрессионного анализа являются:
относительная легкость построения на диаграммах линии тренда без создания для нее таблицы данных;
достаточно широкий перечень типов предложенных линий трендов, причем в этот перечень входят наиболее часто используемые типы регрессии;
возможность прогнозирования поведения исследуемого процесса на произвольное (в пределах здравого смысла) количество шагов вперед, а также назад;
возможность получения уравнения линии тренда в аналитическом виде;
возможность, при необходимости, получения оценки достоверности проведенной аппроксимации.
К недостаткам можно отнести следующие моменты:
построение линии тренда осуществляется лишь при наличии диаграммы, построенной на ряде данных;
процесс формирования рядов данных для исследуемой характеристики на основе полученных для нее уравнений линий тренда несколько загроможден: искомые уравнения регрессий обновляются при каждом изменении значений исходного ряда данных, но только в пределах области диаграммы, в то время как ряд данных, сформированный на основе старого уравнения линии тренда, остается без изменения;
в отчетах сводных диаграмм при изменении представления диаграммы или связанного отчета сводной таблицы имеющиеся линии тренда не сохраняются, то есть до проведения линий тренда или другого форматирования отчета сводных диаграмм следует убедиться, что макет отчета удовлетворяет необходимым требованиям.
Линиями тренда можно дополнить ряды данных, представленные на диаграммах типа график, гистограмма, плоские ненормированные диаграммы с областями, линейчатые, точечные, пузырьковые и биржевые.
Нельзя дополнить линиями тренда ряды данных на объемных, нормированных, лепестковых, круговых и кольцевых диаграммах.
Использование встроенных функций Excel
В Excel имеется также инструмент регрессионного анализа для построения линий тренда вне области диаграммы. Для этой цели можно использовать ряд статистических функций рабочего листа, однако все они позволяют строить лишь линейные или экспоненциальные регрессии.
В Excel имеется несколько функций для построения линейной регрессии, в частности:
НАКЛОН и ОТРЕЗОК.
ТЕНДЕНЦИЯ;
А также несколько функций для построения экспоненциальной линии тренда, в частности:
ЛГРФПРИБЛ.
Следует отметить, что приемы построения регрессий с помощью функций ТЕНДЕНЦИЯ и РОСТ практически совпадают. То же самое можно сказать и о паре функций ЛИНЕЙН и ЛГРФПРИБЛ. Для четырех этих функций при создании таблицы значений используются такие возможности Excel, как формулы массивов, что несколько загромождает процесс построения регрессий. Заметим также, что построение линейной регрессии, на наш взгляд, легче всего осуществить с помощью функций НАКЛОН и ОТРЕЗОК, где первая из них определяет угловой коэффициент линейной регрессии, а вторая - отрезок, отсекаемый регрессией на оси ординат.
Достоинствами инструмента встроенных функций для регрессионного анализа являются:
достаточно простой однотипный процесс формирования рядов данных исследуемой характеристики для всех встроенных статистических функций, задающих линии тренда;
стандартная методика построения линий тренда на основе сформированных рядов данных;
возможность прогнозирования поведения исследуемого процесса на необходимое количество шагов вперед или назад.
А к недостаткам относится то, что в Excel нет встроенных функций для создания других (кроме линейного и экспоненциального) типов линий тренда. Это обстоятельство часто не позволяет подобрать достаточно точную модель исследуемого процесса, а также получить близкие к реальности прогнозы. Кроме того, при использовании функций ТЕНДЕНЦИЯ и РОСТ не известны уравнения линий тренда.
Следует отметить, что авторы не ставили целью статьи изложение курса регрессионного анализа с той или иной степенью полноты. Основная ее задача - на конкретных примерах показать возможности пакета Excel при решении задач аппроксимации; продемонстрировать, какими эффективными инструментами для построения регрессий и прогнозирования обладает Excel; проиллюстрировать, как относительно легко такие задачи могут быть решены даже пользователем, не владеющим глубокими знаниями регрессионного анализа.
Примеры решения конкретных задач
Рассмотрим решение конкретных задач с помощью перечисленных инструментов пакета Excel.
Задача 1
С таблицей данных о прибыли автотранспортного предприятия за 1995-2002 гг. необходимо выполнить следующие действия.
Построить диаграмму.
В диаграмму добавить линейную и полиномиальную (квадратичную и кубическую) линии тренда.
Используя уравнения линий тренда, получить табличные данные по прибыли предприятия для каждой линии тренда за 1995-2004 г.г.
Составить прогноз по прибыли предприятия на 2003 и 2004 гг.
Решение задачи
В диапазон ячеек A4:C11 рабочего листа Excel вводим рабочую таблицу, представленную на рис. 4.
Выделив диапазон ячеек В4:С11, строим диаграмму.
Активизируем построенную диаграмму и по описанной выше методике после выбора типа линии тренда в диалоговом окне Линия тренда (см. рис. 1) поочередно добавляем в диаграмму линейную, квадратичную и кубическую линии тренда. В этом же диалоговом окне открываем вкладку Параметры (см. рис. 2), в поле Название аппроксимирующей (сглаженной) кривой вводим наименование добавляемого тренда, а в поле Прогноз вперед на: периодов задаем значение 2, так как планируется сделать прогноз по прибыли на два года вперед. Для вывода в области диаграммы уравнения регрессии и значения достоверности аппроксимации R2 включаем флажки показывать уравнение на экране и поместить на диаграмму величину достоверности аппроксимации (R^2). Для лучшего визуального восприятия изменяем тип, цвет и толщину построенных линий тренда, для чего воспользуемся вкладкой Вид диалогового окна Формат линии тренда (см. рис. 3). Полученная диаграмма с добавленными линиями тренда представлена на рис. 5.
Для получения табличных данных по прибыли предприятия для каждой линии тренда за 1995-2004 гг. воспользуемся уравнениями линий тренда, представленными на рис. 5. Для этого в ячейки диапазона D3:F3 вводим текстовую информацию о типе выбранной линии тренда: Линейный тренд, Квадратичный тренд, Кубический тренд. Далее вводим в ячейку D4 формулу линейной регрессии и, используя маркер заполнения, копируем эту формулу c относительными ссылками в диапазон ячеек D5:D13. Следует отметить, что каждой ячейке с формулой линейной регрессии из диапазона ячеек D4:D13 в качестве аргумента стоит соответствующая ячейка из диапазона A4:A13. Аналогично для квадратичной регрессии заполняется диапазон ячеек E4:E13, а для кубической регрессии - диапазон ячеек F4:F13. Таким образом, составлен прогноз по прибыли предприятия на 2003 и 2004 гг. с помощью трех трендов. Полученная таблица значений представлена на рис. 6.
Задача 2
Построить диаграмму.
В диаграмму добавить логарифмическую, степенную и экспоненциальную линии тренда.
Вывести уравнения полученных линий тренда, а также величины достоверности аппроксимации R2 для каждой из них.
Используя уравнения линий тренда, получить табличные данные о прибыли предприятия для каждой линии тренда за 1995-2002 гг.
Составить прогноз о прибыли предприятия на 2003 и 2004 гг., используя эти линии тренда.
Решение задачи
Следуя методике, приведенной при решении задачи 1, получаем диаграмму с добавленными в нее логарифмической, степенной и экспоненциальной линиями тренда (рис. 7). Далее, используя полученные уравнения линий тренда, заполняем таблицу значений по прибыли предприятия, включая прогнозируемые значения на 2003 и 2004 гг. (рис. 8).
На рис. 5 и рис. видно, что модели с логарифмическим трендом, соответствует наименьшее значение достоверности аппроксимации
R2 = 0,8659
Наибольшие же значения R2 соответствуют моделям с полиномиальным трендом: квадратичным (R2 = 0,9263) и кубическим (R2 = 0,933).
Задача 3
С таблицей данных о прибыли автотранспортного предприятия за 1995-2002 гг., приведенной в задаче 1, необходимо выполнить следующие действия.
Получить ряды данных для линейной и экспоненциальной линии тренда с использованием функций ТЕНДЕНЦИЯ и РОСТ.
Используя функции ТЕНДЕНЦИЯ и РОСТ, составить прогноз о прибыли предприятия на 2003 и 2004 гг.
Для исходных данных и полученных рядов данных построить диаграмму.
Решение задачи
Воспользуемся рабочей таблицей задачи 1 (см. рис. 4). Начнем с функции ТЕНДЕНЦИЯ:
выделяем диапазон ячеек D4:D11, который следует заполнить значениями функции ТЕНДЕНЦИЯ, соответствующими известным данным о прибыли предприятия;
вызываем команду Функция из меню Вставка. В появившемся диалоговом окне Мастер функций выделяем функцию ТЕНДЕНЦИЯ из категории Статистические, после чего щелкаем по кнопке ОК. Эту же операцию можно осуществить нажатием кнопки (Вставка функции) стандартной панели инструментов.
В появившемся диалоговом окне Аргументы функции вводим в поле Известные_значения_y диапазон ячеек C4:C11; в поле Известные_значения_х - диапазон ячеек B4:B11;
чтобы вводимая формула стала формулой массива, используем комбинацию клавиш + + .
Введенная нами формула в строке формул будет иметь вид: ={ТЕНДЕНЦИЯ(C4:C11;B4:B11)}.
В результате диапазон ячеек D4:D11 заполняется соответствующими значениями функции ТЕНДЕНЦИЯ (рис. 9).
Для составления прогноза о прибыли предприятия на 2003 и 2004 гг. необходимо:
выделить диапазон ячеек D12:D13, куда будут заноситься значения, прогнозируемые функцией ТЕНДЕНЦИЯ.
вызвать функцию ТЕНДЕНЦИЯ и в появившемся диалоговом окне Аргументы функции ввести в поле Известные_значения_y - диапазон ячеек C4:C11; в поле Известные_значения_х - диапазон ячеек B4:B11; а в поле Новые_значения_х - диапазон ячеек B12:B13.
превратить эту формулу в формулу массива, используя комбинацию клавиш Ctrl + Shift + Enter.
Введенная формула будет иметь вид: ={ТЕНДЕНЦИЯ(C4:C11;B4:B11;B12:B13)}, а диапазон ячеек D12:D13 заполнится прогнозируемыми значениями функции ТЕНДЕНЦИЯ (см. рис. 9).
Аналогично заполняется ряд данных с помощью функции РОСТ, которая используется при анализе нелинейных зависимостей и работает точно так же, как ее линейный аналог ТЕНДЕНЦИЯ.
На рис.10 представлена таблица в режиме показа формул.
Для исходных данных и полученных рядов данных построена диаграмма, изображенная на рис. 11.
Задача 4
С таблицей данных о поступлении в диспетчерскую службу автотранспортного предприятия заявок на услуги за период с 1 по 11 число текущего месяца необходимо выполнить следующие действия.
Получить ряды данных для линейной регрессии: используя функции НАКЛОН и ОТРЕЗОК; используя функцию ЛИНЕЙН.
Получить ряд данных для экспоненциальной регрессии с использованием функции ЛГРФПРИБЛ.
Используя вышеназванные функции, составить прогноз о поступлении заявок в диспетчерскую службу на период с 12 по 14 число текущего месяца.
Для исходных и полученных рядов данных построить диаграмму.
Решение задачи
Отметим, что, в отличие от функций ТЕНДЕНЦИЯ и РОСТ, ни одна из перечисленных выше функций (НАКЛОН, ОТРЕЗОК, ЛИНЕЙН, ЛГРФПРИБ) не является регрессией. Эти функции играют лишь вспомогательную роль, определяя необходимые параметры регрессии.
Для линейной и экспоненциальной регрессий, построенных с помощью функций НАКЛОН, ОТРЕЗОК, ЛИНЕЙН, ЛГРФПРИБ, внешний вид их уравнений всегда известен, в отличие от линейной и экспоненциальной регрессий, соответствующих функциям ТЕНДЕНЦИЯ и РОСТ.
1 . Построим линейную регрессию, имеющую уравнение:
y = mx+b
с помощью функций НАКЛОН и ОТРЕЗОК, причем угловой коэффициент регрессии m определяется функцией НАКЛОН, а свободный член b - функцией ОТРЕЗОК.
Для этого осуществляем следующие действия:
заносим исходную таблицу в диапазон ячеек A4:B14;
значение параметра m будет определяться в ячейке С19. Выбираем из категории Статистические функцию Наклон; заносим диапазон ячеек B4:B14 в поле известные_значения_y и диапазон ячеек А4:А14 в поле известные_значения_х. В ячейку С19 будет введена формула: =НАКЛОН(B4:B14;A4:A14);
по аналогичной методике определяется значение параметра b в ячейке D19. И ее содержимое будет иметь вид: =ОТРЕЗОК(B4:B14;A4:A14). Таким образом, необходимые для построения линейной регрессии значения параметров m и b будут сохраняться соответственно в ячейках C19, D19;
далее заносим в ячейку С4 формулу линейной регрессии в виде: =$C*A4+$D. В этой формуле ячейки С19 и D19 записаны с абсолютными ссылками (адрес ячейки не должен меняться при возможном копировании). Знак абсолютной ссылки $ можно набить либо с клавиатуры, либо с помощью клавиши F4, предварительно установив курсор на адресе ячейки. Воспользовавшись маркером заполнения, копируем эту формулу в диапазон ячеек С4:С17. Получаем искомый ряд данных (рис. 12). В связи с тем, что количество заявок - целое число, следует установить на вкладке Число окна Формат ячеек числовой формат с числом десятичных знаков 0.
2 . Теперь построим линейную регрессию, заданную уравнением:
y = mx+b
с помощью функции ЛИНЕЙН.
Для этого:
вводим в диапазон ячеек C20:D20 функцию ЛИНЕЙН как формулу массива: ={ЛИНЕЙН(B4:B14;A4:A14)}. В результате получаем в ячейке C20 значение параметра m, а в ячейке D20 - значение параметра b;
вводим в ячейку D4 формулу: =$C*A4+$D;
копируем эту формулу с помощью маркера заполнения в диапазон ячеек D4:D17 и получаем искомый ряд данных.
3 . Строим экспоненциальную регрессию, имеющую уравнение:
с помощью функции ЛГРФПРИБЛ оно выполняется аналогично:
в диапазон ячеек C21:D21 вводим функцию ЛГРФПРИБЛ как формулу массива: ={ ЛГРФПРИБЛ (B4:B14;A4:A14)}. При этом в ячейке C21 будет определено значение параметра m, а в ячейке D21 - значение параметра b;
в ячейку E4 вводится формула: =$D*$C^A4;
с помощью маркера заполнения эта формула копируется в диапазон ячеек E4:E17, где и расположится ряд данных для экспоненциальной регрессии (см. рис. 12).
На рис. 13 приведена таблица, где видны используемые нами функции с необходимыми диапазонами ячеек, а также формулы.
Величина R 2 называется коэффициентом детерминации .
Задачей построения регрессионной зависимости является нахождение вектора коэффициентов m модели (1) при котором коэффициент R принимает максимальное значение.
Для оценки значимости R применяется F-критерий Фишера, вычисляемый по формуле
где n - размер выборки (количество экспериментов);
k - число коэффициентов модели.
Если F превышает некоторое критическое значение для данных n и k и принятой доверительной вероятности, то величина R считается существенной. Таблицы критических значений F приводятся в справочниках по математической статистике.
Таким образом, значимость R определяется не только его величиной, но и соотношением между количеством экспериментов и количеством коэффициентов (параметров) модели. Действительно, корреляционное отношение для n=2 для простой линейной модели равно 1 (через 2 точки на плоскости можно всегда провести единственную прямую). Однако если экспериментальные данные являются случайными величинами, доверять такому значению R следует с большой осторожностью. Обычно для получения значимого R и достоверной регрессии стремятся к тому, чтобы количество экспериментов существенно превышало количество коэффициентов модели (n>k).
Для построения линейной регрессионной модели необходимо:
1) подготовить список из n строк и m столбцов, содержащий экспериментальные данные (столбец, содержащий выходную величину Y должен быть либо первым, либо последним в списке); для примера возьмем данные предыдущего задания, добавив столбец с названием "№ периода", пронумеруем номера периодов от 1 до 12. (это будут значения Х )
2) обратиться к меню Данные/Анализ данных/Регрессия
Если пункт "Анализ данных" в меню "Сервис" отсутствует, то следует обратиться к пункту "Надстройки" того же меню и установить флажок "Пакет анализа".
3) в диалоговом окне "Регрессия" задать:
· входной интервал Y;
· входной интервал X;
· выходной интервал - верхняя левая ячейка интервала, в который будут помещаться результаты вычислений (рекомендуется разместить на новом рабочем листе);
4) нажать "Ok" и проанализировать результаты.
КУРСОВАЯ РАБОТА
по дисциплине: Информатика
Тема: Аппроксимация функции методом наименьших квадратов
Введение
1.Постановка задачи
2.Расчётные формулы
Расчёт с помощью таблиц, выполненных средствами Microsoft Excel
Схема алгоритма
Расчет в программе MathCad
Результаты, полученные с помощью функции Линейн
Представление результатов в виде графиков
Введение
Целью курсовой работы является углубление знаний по информатике, развитие и закрепление навыков работы с табличным процессором Microsoft Excel и программным продуктом MathCAD и применение их для решения задач с помощью ЭВМ из предметной области, связанной с исследованиями.
Аппроксимация (от латинского "approximare" -"приближаться") - приближенное выражение каких-либо математических объектов (например, чисел или функций) через другие более простые, более удобные в пользовании или просто более известные. В научных исследованиях аппроксимация применяется для описания, анализа, обобщения и дальнейшего использования эмпирических результатов.
Как известно, между величинами может существовать точная (функциональная) связь, когда одному значению аргумента соответствует одно определенное значение, и менее точная (корреляционная) связь, когда одному конкретному значению аргумента соответствует приближенное значение или некоторое множество значений функции, в той или иной степени близких друг к другу. При ведении научных исследований, обработке результатов наблюдения или эксперимента обычно приходиться сталкиваться со вторым вариантом.
При изучении количественных зависимостей различных показателей, значения которых определяются эмпирически, как правило, имеется некоторая их вариабельность. Частично она задается неоднородностью самих изучаемых объектов неживой и, особенно, живой природы, частично - обуславливается погрешностью наблюдения и количественной обработке материалов. Последнюю составляющую не всегда удается исключить полностью, можно лишь минимизировать ее тщательным выбором адекватного метода исследования и аккуратностью работы. Поэтому при выполнении любой научно-исследовательской работы возникает проблема выявления подлинного характера зависимости изучаемых показателей, этой или иной степени замаскированных неучтенностью вариабельности: значений. Для этого и применяется аппроксимация - приближенное описание корреляционной зависимости переменных подходящим уравнением функциональной зависимости, передающим основную тенденцию зависимости (или ее "тренд").
При выборе аппроксимации следует исходить из конкретной задачи исследования. Обычно, чем более простое уравнение используется для аппроксимации, тем более приблизительно получаемое описание зависимости. Поэтому важно считывать, насколько существенны и чем обусловлены отклонения конкретных значений от получаемого тренда. При описании зависимости эмпирически определенных значений можно добиться и гораздо большей точности, используя какое-либо более сложное, много параметрическое уравнение. Однако нет никакого смысла стремиться с максимальной точностью передать случайные отклонения величин в конкретных рядах эмпирических данных. Гораздо важнее уловить общую закономерность, которая в данном случае наиболее логично и с приемлемой точностью выражается именно двухпараметрическим уравнением степенной функции. Таким образом, выбирая метод аппроксимации, исследователь всегда идет на компромисс: решает, в какой степени в данном случае целесообразно и уместно «пожертвовать» деталями и, соответственно, насколько обобщенно следует выразить зависимость сопоставляемых переменных. Наряду с выявлением закономерностей, замаскированных случайными отклонениями эмпирических данных от общей закономерности, аппроксимация позволяет также решать много других важных задач: формализовать найденную зависимость; найти неизвестные значения зависимой переменной путем интерполяции или, если это допустимо, экстраполяции.
В каждом задании формулируются условия задачи, исходные данные, форма выдачи результатов, указываются основные математические зависимости для решения задачи. В соответствии с методом решения задачи разрабатывается алгоритм решения, который представляется в графической форме.
1. Постановка задачи
1. Используя метод наименьших квадратов функцию, заданную таблично, аппроксимировать:
а) многочленом первой степени;
б) многочленом второй степени;
в) экспоненциальной зависимостью.
Для каждой зависимости вычислить коэффициент детерминированности.
Вычислить коэффициент корреляции (только в случае а).
Для каждой зависимости построить линию тренда.
Используя функцию ЛИНЕЙН вычислить числовые характеристики зависимости от.
Сравнить свои вычисления с результатами, полученными при помощи функции ЛИНЕЙН.
Сделать вывод, какая из полученных формул наилучшим образом аппроксимирует функцию.
Написать программу на одном из языков программирования и сравнить результаты счета с полученными выше.
Вариант 3. Функция задана табл. 1.
Таблица 1.
xyxyxyxyxy0.281.052.349.113.3329.434.2386.445.55187.540.872.872.6516.863.4137.454.8390.856.32200.451.656.432.7717.973.5542.444.9299.066.66212.971.998.962.8318.993.8556.945.14120.457.13275.742.088.083.0623.754.0175.085.23139.657.25321.43
2. Расчётные формулы
Часто при анализе эмпирических данных возникает необходимость найти функциональную зависимость между величинами x и y, которые получены в результате опыта или измерений.
Хi (независимая величина) задается экспериментатором, а yi , называемая эмпирическими или опытными значениями получается в результате опыта.
Аналитический вид функциональной зависимости, существующей между величинами x и y обычно неизвестен, поэтому возникает практически важная задача - найти эмпирическую формулу
(где - параметры), значения которой при возможно мало отличались бы от опытных значений.
Согласно методу наименьших квадратов наилучшими коэффициентами считаются те, для которых сумма квадратов отклонений найденной эмпирической функции от заданных значений функции будет минимальной.
Используя необходимое условие экстремума функции нескольких переменных - равенство нулю частных производных, находят набор коэффициентов, которые доставляют минимум функции, определяемой формулой (2) и получают нормальную систему для определения коэффициентов:
Таким образом, нахождение коэффициентов сводится к решению системы (3).
Вид системы (3) зависит от того, из какого класса эмпирических формул мы ищем зависимость (1). В случае линейной зависимости система (3) примет вид:
В случае квадратичной зависимости система (3) примет вид:
В ряде случаев в качестве эмпирической формулы берут функцию в которую неопределенные коэффициенты входят нелинейно. При этом иногда задачу удается линеаризовать т.е. свести к линейной. К числу таких зависимостей относится экспоненциальная зависимость
где a1и a2 неопределенные коэффициенты.
Линеаризация достигается путем логарифмирования равенства (6), после чего получаем соотношение
Обозначим и соответственно через и, тогда зависимость (6) может быть записана в виде, что позволяет применить формулы (4) с заменой a1 на и на.
График восстановленной функциональной зависимости y(x) по результатам измерений (xi, yi), i=1,2,…,n называется кривой регрессии. Для проверки согласия построенной кривой регрессии с результатами эксперимента обычно вводят следующие числовые характеристики: коэффициент корреляции (линейная зависимость), корреляционное отношение и коэффициент детерминированности.
Коэффициент корреляции является мерой линейной связи между зависимыми случайными величинами: он показывает, насколько хорошо в среднем может быть представлена одна из величин в виде линейной функции от другой.
Коэффициент корреляции вычисляется по формуле:
где - среднее арифметическое значение соответственно по x, y.
Коэффициент корреляции между случайными величинами по абсолютной величине не превосходит 1. Чем ближе к 1, тем теснее линейная связь между x и y.
В случае нелинейной корреляционной связи условные средние значения располагаются около кривой линии. В этом случае в качестве характеристики силы связи рекомендуется использовать корреляционное отношение, интерпретация которого не зависит от вида исследуемой зависимости.
Корреляционное отношение вычисляется по формуле:
где а числитель характеризует рассеяние условных средних около безусловного среднего.
Всегда. Равенство = соответствует случайным некоррелированным величинам; = тогда и только тогда, когда имеется точная функциональная связь между x и y. В случае линейной зависимости y от x корреляционное отношение совпадает с квадратом коэффициента корреляции. Величина используется в качестве индикатора отклонения регрессии от линейной.
Корреляционное отношение является мерой корреляционной связи y c x в какой угодно форме, но не может дать представления о степени приближенности эмпирических данных к специальной форме. Чтобы выяснить насколько точно построен5ная кривая отражает эмпирические данные вводится еще одна характеристика - коэффициент детерминированности.
где Sост = - остаточная сумма квадратов, характеризующая отклонение экспериментальных данных от теоретических.полн - полная сумма квадратов, где среднее значение yi.
Регрессионная сумма квадратов, характеризующая разброс данных.
Чем меньше остаточная сумма квадратов по сравнению с общей суммой квадратов, тем больше значение коэффициента детерминированности r2, который показывает, насколько хорошо уравнение, полученное с помощью регрессионного анализа, объясняет взаимосвязи между переменными. Если он равен 1, то имеет место полная корреляция с моделью, т.е. нет различия между фактическим и оценочным значениями y. В противоположном случае, если коэффициент детерминированности равен 0, то уравнение регрессии неудачно для предсказания значений y.
Коэффициент детерминированности всегда не превосходит корреляционное отношение. В случае когда выполняется равенство то можно считать, что построенная эмпирическая формула наиболее точно отражает эмпирические данные.
3. Расчёт с помощью таблиц, выполненных средствами Microsoft Excel
Для проведения расчётов данные целесообразно расположить в виде таблицы 2, используя средства табличного процессора Microsoft Excel.
Таблица 2
ABCDEFGHI10,281,050,07840,2940,0219520,0061470,082320,048790,01366120,872,870,75692,49690,6585030,5728982,1723031,0543120,91725131,656,432,722510,60954,4921257,41200617,505681,8609753,07060841,998,963,960117,83047,88059915,6823935,48252,192774,36361352,088,084,326416,80648,99891218,7177434,957312,0893924,34593562,349,115,475621,317412,812929,982249,882722,2093735,16993272,6516,867,022544,67918,6096349,31551118,39942,8249447,48610182,7717,977,672949,776921,2539358,87339137,8822,8887048,00170992,8318,998,008953,741722,6651964,14248152,0892,9439138,331272103,0623,759,363672,67528,6526287,677222,38553,1675839,692803113,3329,4311,088998,001936,92604122,9637326,34633,38201511,26211123,4137,4511,6281127,704539,65182135,2127435,47233,62300712,35445133,5542,4412,6025150,66244,73888158,823534,85013,74809113,30572143,8556,9414,8225219,21957,06663219,7065843,99324,04199815,56169154,0175,0816,0801301,070864,4812258,56961207,2944,31855417,3174164,2386,4417,8929365,641275,68697320,15591546,6624,45945118,86348174,8390,8523,3289438,8055112,6786544,23762119,4314,5092121,77948184,9299,0624,2064487,3752119,0955585,94982397,8864,59572622,61097195,14120,4526,4196619,113135,7967697,99533182,2414,79123524,62695205,23139,6527,3529730,3695143,0557748,18113819,8324,93913925,8317215,55187,5430,80251040,847170,9539948,7945776,7015,23399229,04866226,32200,4539,94241266,844252,4361595,3958006,4545,30056533,49957236,66212,9744,35561418,38295,40831967,4199446,4125,36115135,70527247,13275,7450,83691966,026362,46712584,3914017,775,61945840,06674257,25321,4352,56252330,368381,07812762,81616895,165,7727841,852652695,932089,99453,310511850,652417,56813982,9971327,3490,97713415,0797С У М М ЫПоясним, как таблица 2 составляется.
Шаг 1.В ячейки А1:A25 заносим значения xi. Шаг 2.В ячейки B1:B25 заносим значения уi. Шаг 3.В ячейку С1 вводим формулу=А1^2. Шаг 4.В ячейки С1:С25 эта формула копируется. Шаг 5.В ячейку D1 вводим формулу=А1*B1. Шаг 6.В ячейки D1:D25 эта формула копируется. Шаг 7.В ячейку F1 вводим формулу=А1^4. Шаг 8.В ячейки F1:F25 эта формула копируется. Шаг 9.В ячейку G1 вводим формулу=А1^2*B1. Шаг 10.В ячейки G1:G25 эта формула копируется. Шаг 11.В ячейку H1 вводим формулу = LN(B1). Шаг 12.В ячейки H1:H25 эта формула копируется. Шаг 13.В ячейку I1 вводим формулу=А1*LN(B1). Шаг 14.В ячейки I1:I25 эта формула копируется. Последующие шаги делаем с помощью автосуммирования S.
Шаг 15. В ячейку А26 вводим формулу = СУММ(А1:А25). Шаг 16. В ячейку В26 вводим формулу = СУММ(В1:В25). Шаг 17. В ячейку С26 вводим формулу = СУММ(С1:С25). Шаг 18. В ячейку D26 вводим формулу = СУММ(D1:D25). Шаг 19. В ячейку E26 вводим формулу = СУММ(E1:E25). Шаг 20. В ячейку F26 вводим формулу = СУММ(F1:F25). Шаг 21. В ячейку G26 вводим формулу = СУММ(G1:G25). Шаг 22. В ячейку H26 вводим формулу = СУММ(H1:H25). Шаг 23. В ячейку I26 вводим формулу = СУММ(I1:I25). Аппроксимируем функцию линейной функцией. Для определения коэффициентов и воспользуемся системой (4). Используя итоговые суммы таблицы 2, расположенные в ячейках A26, B26, C26 и D26, запишем систему (4) в виде решив которую, получим и. Систему решали методом Крамера. Суть которого состоит в следующем. Рассмотрим систему n алгебраических линейных уравнений с n неизвестными:
Определителем системы называется определитель матрицы системы:
Обозначим - определитель, который получится из определителя системы Δ заменой j-го столбца на столбец
Таким образом, линейная аппроксимация имеет вид
Решение системы (11) проводим, пользуясь средствами Microsoft Excel. Результаты представлены в таблице 3.
Таблица 3 ABCDE282595,932089,992995,93453,310511850,653031Обратная матрица320,212802-0,04503a1=-88,9208133-0,045030,011736a2=44,95997 В таблице 3 в ячейках A32:B33 записана формула {=МОБР(А28:В29)}. В ячейках Е32:Е33 записана формула {=МУМНОЖ(А32:В33),(C28:С29)}. Далее аппроксимируем функцию квадратичной функцией. Для определения коэффициентов a1, a2 и a3 воспользуемся системой (5). Используя итоговые суммы таблицы 2, расположенные в ячейках A26, B26, C26 , D26, E26, F26, G26 запишем систему (5) в виде
решив которую, получим a1=10,663624, и Таким образом, квадратичная аппроксимация имеет вид
Решение системы (16) проводим, пользуясь средствами Microsoft Excel. Результаты представлены в таблице 4. Таблица 4 ABCDEF362595,93453,31052089,993795,93453,31052417,56811850,65538453,31052417,56813982,9971327,3453940Обратная матрица410,632687-0,314390,033846a1=10,66362442-0,314390,184534-0,021712a2=-18,924512430,033846-0,021710,002728a3=8,0272305
В таблице 4 в ячейках А41:С43 записана формула {=МОБР(А36:С38)}. В ячейках F41:F43 записана формула {=МУМНОЖ(А41:C43),(D36:D38)}. Теперь аппроксимируем функцию экспоненциальной функцией. Для определения коэффициентов и прологарифмируем значения и, используя итоговые суммы таблицы 2, расположенные в ячейках A26, C26, H26 и I26, получим систему
Решив систему (18), получим и. После потенцирования получим. Таким образом, экспоненциальная аппроксимация имеет вид Решение системы (18) проводим, пользуясь средствами Microsoft Excel. Результаты представлены в таблице 5. Таблица 5 BCDEF462595,9390,977134795,93453,3105415,07974849Обратная матрицас=0,667679 500,212802-0,04503а2=0,774368 51-0,045030,011736а1=1,949707
В ячейках А50:В51 записана формула {=МОБР(А46:В47)}. В ячейке Е51 записана формула=EXP(E49). Вычислим среднее арифметическое и по формулам:
Результаты расчета и средствами Microsoft Excel представлены в таблице 6.
Таблица 6 BC54Xср=3,837255Yср=83,5996 В ячейке В54 записана формула=А26/25. В ячейке В55 записана формула=В26/25 Таблица 7 ABJKLMNO10,281,05293,645412,653676814,4365987,97624,444081,88177520,872,87239,54098,8042766517,2682774,7226,7334610,91071731,656,43168,78534,7838445955,147448,035726,395820,32073741,998,96137,87433,4121485571,0770,7358817,368220,02062652,088,08132,7033,0877525703,2112,138714,2039422,82478262,349,11111,52582,2416085548,70151,488211,4985887,99584272,6516,8679,233251,4094444454,174178,5730,000622,83382582,7717,9770,039911,1389164307,244311,46313,4777091,73059692,8318,9965,074791,0144524174,4373,4915,7914362,382273103,0623,7546,515110,604043581,975620,344117,375498,423061113,3329,4327,474820,2572522934,346983,819852,2462113,94466123,4137,4519,715110,18252129,786725,90914,090409102,2541133,5542,4411,821040,0824841694,113797,89844,861044143,3219143,8556,94-0,341240,000164710,7343741,750,023142342,3946154,0175,08-1,472190,0298672,58358265,3212126,0007996,9257164,2386,441,1157090,1542928,067872219,6288148,75781214,778174,8390,857,1981970,98565252,56831397,703245,695876,64891184,9299,0616,740521,172456239,0241103,718163,9776121,868195,14120,4548,00871,6972881357,952471,908425,17881258,6007205,23139,6578,0671,9398923141,64743,1629470,45155769,9408215,55187,54178,02912,93368410803,61725,38421200,5291951,06226,32200,45290,11626,16429613654,0227,28786126,28273577,409236,66212,97365,18687,968216736,76,038755767,788515795,87247,13275,74632,679910,8425336917,931944,47565,1469344766,92257,25321,43811,667611,647256563,37121,842677,966445516,82695,932089,93830,94585,207919964427404,823786,286115678,1С у м м ыОстаточные суммыXYлинейн.квадр.экспон.
Поясним как она составляется. Ячейки А1:А26 и В1:В26 уже заполнены. Шаг 1.В ячейку J1 вводим формулу = (А1-$B$54)*(B1-$B$55). Шаг 2.В ячейки J2:J25 эта формула копируется. Шаг 3.В ячейку K1 вводим формулу = (А1-$B$54)^2. Шаг 4.В ячейки k2:K25 эта формула копируется. Шаг 5.В ячейку L1 вводим формулу = (B1-$B$55)^2. Шаг 6.В ячейки L2:L25 эта формула копируется. Шаг 7.В ячейку M1 вводим формулу = ($E$32+$E$33*A1-B1)^2. Шаг 8.В ячейки M2:M25 эта формула копируется. Шаг 9.В ячейку N1 вводим формулу = ($F$41+$F$42*A1+$F$43*A1^2-B1)^2. Шаг 10.В ячейки N2:N25 эта формула копируется. Шаг 11.В ячейку O1 вводим формулу = ($E$51*EXP($E$50*A1)-B1)^2. Шаг 12.В ячейки O2:O25 эта формула копируется. Последующие шаги делаем с помощью авто суммирования S.
Шаг 13.В ячейку J26 вводим формулу = CУММ(J1:J25). Шаг 14.В ячейку K26 вводим формулу = CУММ(K1:K25). Шаг 15.В ячейку L26 вводим формулу = CУММ(L1:L25). Шаг 16.В ячейку M26 вводим формулу = CУММ(M1:M25). Шаг 17.В ячейку N26 вводим формулу = CУММ(N1:N25). Шаг 18.В ячейку O26 вводим формулу = CУММ(O1:O25). Теперь проведем расчеты коэффициента корреляции по формуле (8) (только для линейной аппроксимации) и коэффициента детерминированности по формуле (10). Результаты расчетов средствами Microsoft Excel представлены в таблице 8.
Таблица 8 AB57Коэффициент корреляции0,92883358Коэффициент детерминированности (линейная аппроксимация)0,8627325960Коэффициент детерминированности (квадратичная аппроксимация)0,9810356162Коэффициент детерминированности (экспоненциальная аппроксимация)0,42057863В ячейке E57 записана формула=J26/(K26*L26)^(1/2).
В ячейке E59 записана формула=1-M26/L26. В ячейке E61 записана формула=1-N26/L26. В ячейке E63 записана формула=1-O26/L26. Анализ результатов расчетов показывает, что квадратичная аппроксимация наилучшим образом описывает экспериментальные данные.
Схема алгоритма
Рис. 1. Схема алгоритма для программы расчёта. 5. Расчет в программе MathCad
Линейная регрессия ·line (x, y) - вектор из двух элементов (b, a) коэффициентов линейной регрессии b+ax;
·x - вектор действительных данных аргумента;
·y - вектор действительных данных значений того же размера.
Рисунок 2.
Полиномиальная регрессия означает приближение данных (х1, у1) полиномом k-й степени При k=i полином является прямой линией, при k=2 - параболой, при k=3 - кубической параболой и т.д. Как правило, на практике применяются k<5. ·regress (x,y,k) - вектор коэффициентов для построения полиномиальной регрессии данных;
·interp (s,x,y,t) - результат полиномиальной регрессии;
·s=regress(x,y,k);
·x - вектор действительных данных аргумента, элементы которого расположены в порядке возрастания;
·y - вектор действительных данных значений того же размера;
·k - степень полинома регрессии (целое положительное число);
·t - значение аргумента полинома регрессии.
Рисунок 3
Кроме рассмотренных, в Mathcad встроено еще несколько видов трехпараметрической регрессии, их реализация несколько отличается от приведенных выше вариантов регрессии тем, что для них, помимо массива данных, требуется задать некоторые начальные значения коэффициентов a, b, c. Используйте соответствующий вид регрессии, если хорошо представляете себе, какой зависимостью описывается ваш массив данных. Когда тип регрессии плохо отражает последовательность данных, то ее результат часто бывает неудовлетворительным и даже сильно различающимся в зависимости от выбора начальных значений. Каждая из функций выдает вектор уточненных параметров a, b, c.
Результаты, полученные с помощью функции ЛИНЕЙН
Рассмотрим назначение функции ЛИНЕЙН. Эта функция использует метод наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные. Функция возвращает массив, который описывает полученную прямую. Уравнение для прямой линии имеет следующий вид: M1x1 + m2x2 + ... + b или y = mx + b, алгоритм табличный microsoft программный Для получения результатов необходимо создать табличную формулу, которая будет занимать 5 строк и 2 столбца. Этот интервал может располагаться в произвольном месте на рабочем листе. В этот интервал требуется ввести функцию ЛИНЕЙН. В результате должны заполниться все ячейки интервала А65:В69 (как показано в таблице 9).
Таблица 9. АВ6544,95997-88,9208663,73946615,92346670,86273234,5183168144,55492369172239,227404,82 Поясним назначение некоторых величин, расположенных в таблице 9. Величины, расположенные в ячейках А65 и В65 характеризуют соответственно наклон и сдвиг.- коэффициент детерминированности.- F-наблюдаемое значение.- число степеней свободы.- регрессионная сумма квадратов.- остаточная сумма квадратов.
Представление результатов в виде графиков
Рис. 4. График линейной аппроксимации
Рис. 5. График квадратичной аппроксимации
Рис. 6. График экспоненциальной аппроксимации
Выводы
Сделаем выводы по результатам полученных данных. Анализ результатов расчетов показывает, что квадратичная аппроксимация наилучшим образом описывает экспериментальные данные, т.к. линия тренда для неё наиболее точно отражает поведение функции на данном участке. Сравнивая результаты, полученные при помощи функции ЛИНЕЙН, видим, что они полностью совпадают с вычислениями, проведенными выше. Это указывает на то, что вычисления верны. Результаты, полученные с помощью программы MathCad, полностью совпадают со значениями приведенными выше. Это говорит о верности вычислений.
Список используемой литературы
Постановка задачи аппроксимации по МНК. Условия наилучшего приближения.
Если набор экспериментальных данных получен со значительной погрешностью, то интерполяция не только не требуется, но и нежелательна! Здесь требуется построить кривую, которая воспроизводила бы график исходной экспериментальной закономерности, т.е. была бы максимально близка к экспериментальным точкам, но в то же время была бы нечувствительна к случайным отклонениям измеряемой величины.
Введем непрерывную функцию φ(x) для аппроксимации дискретной зависимости f(x i ) , i = 0…n . Будем считать, что φ(x) построена по условию наилучшего квадратичного приближения , если
. (1)
Весу ρ для i -й точки придают смысл точности измерения данного значения: чем больше ρ , тем ближе аппроксимирующая кривая «притягивается» к данной точке. В дальнейшем будем по умолчанию полагать ρ = 1 для всех точек.
Рассмотрим случай линейной аппроксимации :
φ(x) = c 0 φ 0 (x) + c 1 φ 1 (x) + … + c m φ m (x) , (2)
где φ 0 …φ m – произвольные базисные функции , c 0 …c m – неизвестные коэффициенты, m < n . Если число коэффициентов аппроксимации взять равным числу узлов, то среднеквадратичная аппроксимация совпадет с интерполяцией Лагранжа, при этом, если не учитывать вычислительную погрешность, Q = 0.
Если известна экспериментальная (исходная) погрешность данных ξ , то выбор числа коэффициентов, то есть величины m , определяется условием:
Иными словами, если , число коэффициентов аппроксимации недостаточно для правильного воспроизведения графика экспериментальной зависимости. Если , многие коэффициенты в (2) не будут иметь физического смысла.
Для решения задачи линейной аппроксимации в общем случае следует найти условия минимума суммы квадратов отклонений для (2). Задачу на поиск минимума можно свести к задаче поиска корня системы уравнений , k = 0…m . (4) .
Подстановка (2) в (1), а затем расчет (4) приведет в итоге к следующей системе линейных алгебраических уравнений:
Далее следует решить полученную СЛАУ относительно коэффициентов c 0 …c m . Для решения СЛАУ обычно составляется расширенная матрица коэффициентов, которую называют матрицей Грама , элементами которой являются скалярные произведения базисных функций и столбец свободных коэффициентов:
,
где 
, 
, j = 0…m, k
= 0…m
.
После того как с помощью, например, метода Гаусса найдены коэффициенты c 0 …c m , можно построить аппроксимирующую кривую или вычислить координаты заданной точки. Таким образом, задача аппроксимации решена.
Аппроксимация каноническим полиномом.
Выберем базисные функции в виде последовательности степеней аргумента x:
φ 0 (x) = x 0 = 1; φ 1 (x) = x 1 = x ; φ m (x) = x m , m < n .
Расширенная матрица Грама для степенного базиса будет выглядеть следующим образом:
Особенность вычислений такой матрицы (для уменьшения количества выполняемых действий) состоит в том, что необходимо сосчитать только элементы первой строки и двух последних столбцов: остальные элементы заполняются сдвигом предшествующей строки (за исключением двух последних столбцов) на одну позицию влево. В некоторых языках программирования, где отсутствует быстрая процедура возведения в степень, пригодится алгоритм расчета матрицы Грама, представленный далее.
Выбор базисных функций в виде степеней x не является оптимальным с точки зрения достижения наименьшей погрешности. Это является следствием неортогональности выбранных базисных функций. Свойство ортогональности заключается в том, что для каждого типа полинома существует отрезок [x 0 , x n ], на котором обращаются в нуль скалярные произведения полиномов разного порядка:
, j
≠ k, ρ
– некоторая весовая функция.
Если бы базисные функции были ортогональны, то все недиагональные элементы матрицы Грама были бы близки к нулю, что увеличило бы точность вычислений, в противном случае при определитель матрицы Грама очень быстро стремится к нулю, т.е. система становится плохо обусловленной.
Аппроксимация ортогональными классическими полиномами.
Представленные ниже полиномы, относящиеся ко многочленам Якоби , обладают свойством ортогональности в изложенном выше смысле. То есть, для достижения высокой точности вычислений рекомендуется выбирать базисные функции для аппроксимации в виде этих полиномов.
