Задание 16.
Дана таблица
экспериментальных данных:
|
x |
0,5 |
1 |
1,5 |
2 |
2,5 |
3 |
3,5 |
4 |
4,5 |
5 |
|
y |
1 |
2 |
4 |
7 |
11 |
16 |
22 |
29 |
37 |
46 |
Определить
вид и параметры зависимости y
(x).
Руководство к
выполнению Задания 16.
а) Построим график
зависимости У от Х по заданным точкам.
Рисунок 96
б) Докажем
аналитически, что данная зависимость
не линейная. Для этого вычислим
,
если они не имеют близкие между собой
значения, проверим значения
.
Если значения
близки между собой значения, то данная
зависимость квадратичная, т.е.
.
Расчетная таблица
выглядит следующим образом:
|
n |
x |
y |
p1 |
p2 |
|
1 |
0,5 |
1 |
2 |
2 |
|
2 |
1 |
2 |
4 |
2 |
|
3 |
1,5 |
4 |
6 |
2 |
|
4 |
2 |
7 |
8 |
2 |
|
5 |
2,5 |
11 |
10 |
2 |
|
6 |
3 |
16 |
12 |
2 |
|
7 |
3,5 |
22 |
14 |
2 |
|
8 |
4 |
29 |
16 |
2 |
|
9 |
4,5 |
37 |
18 |
|
|
10 |
5 |
46 |
Рисунок 97
Перейдем к линейной
зависимости
.
Для этого вычтем из уравнения, записанного
для i –
й пары точек,
уравнение, записанное для первой пары
точек
.
После алгебраических преобразований
получим
,
где i
не равно 1.
Обозначим
и
.
В таблицах EXCEL
формулы вычисления t
и Z
показаны на Рисунке 97, а результат
вычисления на Рисунке 98:
Рисунок 98
|
n |
x |
y |
p1 |
p2 |
||
|
1 |
0,5 |
1 |
2 |
2 |
t |
Z |
|
2 |
1 |
2 |
4 |
2 |
1,5 |
2 |
|
3 |
1,5 |
4 |
6 |
2 |
2 |
3 |
|
4 |
2 |
7 |
8 |
2 |
2,5 |
4 |
|
5 |
2,5 |
11 |
10 |
2 |
3 |
5 |
|
6 |
3 |
16 |
12 |
2 |
3,5 |
6 |
|
7 |
3,5 |
22 |
14 |
2 |
4 |
7 |
|
8 |
4 |
29 |
16 |
2 |
4,5 |
8 |
|
9 |
4,5 |
37 |
18 |
5 |
9 |
|
|
10 |
5 |
46 |
5,5 |
10 |
Рисунок 99
Функция ЛИНЕЙН
для t
и z
согласно
Рисунку 100 имеет параметры:
Рисунок 100
Согласно Рисунку
101 значения коэффициентов функции
равны a=2,
b=-1.
Рисунок 101
Значение с
вычисляется из уравнения для первой
точки
согласно
Рисунку 101.
Рисунок 102
Формула для проверки
полученных значений a,
b,
c
показана на
Рисунке 103.
Рисунок 103
Результат вычислений
и график полученной функции
показаны на Рисунках 103 и 104.
Рисунок 104
Рисунок 105
Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]
- #
- #
- #
- #
- #
- #
- #
- #
- #
- #
- #
Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью квадратичной функции y=ax 2 +bx+с .
Метод наименьших квадратов (англ. Ordinary Least Squares , OLS ) является одним из базовых методов регрессионного анализа в части оценки неизвестных параметров регрессионных моделей по выборочным данным. Основная статья про МНК — МНК: Метод Наименьших Квадратов в MS EXCEL .
Для построения графика квадратичной зависимости y=ax 2 +bx+с вспомним исходный критерий МНК, который необходимо минимизировать:
Теперь ŷ i = a*x i 2 +b*x i +с и мы имеем зависимость от 3-х параметров полинома второй степени: a , b и с .
Вышеуказанное выражение примет минимальное значение при таких параметрах a , b и с , при которых соответствующие 3 частные производные функции F равны нулю, т.е.:
В результате вычислений и преобразований получим систему из 3-х линейных уравнений:
Сначала вычислим выражения со знаком суммирования. Для этого возьмем исходные данные таблицы и произведем с ними арифметические действия (см. файл примера ).
Затем получившуюся систему линейных уравнений нужно решить относительно параметров a , b и с. Для этого можно использовать, например метод обратной матрицы или функцию ЛИНЕЙН() (эти подходы реализованы в файле примера ).
В результате вычислений будут найдены три параметра квадратичного полинома a , b и с, а также построена соответствующая парабола.
Примечание : Три параметра можно также найти с помощью замены переменных – это сделано в более общем случае для полинома .
Регрессия — это статистический метод, который мы можем использовать для объяснения взаимосвязи между одной или несколькими переменными-предикторами и переменной-откликом. Наиболее распространенным типом регрессии является линейная регрессия , которую мы используем, когда связь между переменной-предиктором и переменной-откликом является линейной .
То есть, когда предикторная переменная увеличивается, переменная отклика также имеет тенденцию к увеличению. Например, мы можем использовать модель линейной регрессии для описания взаимосвязи между количеством часов обучения (переменная-предиктор) и оценкой, которую студент получает на экзамене (переменная-ответ).
Однако иногда связь между переменной-предиктором и переменной-ответом нелинейна.Одним из распространенных типов нелинейных отношений является квадратичная зависимость , которая может выглядеть как U или перевернутая U на графике.
То есть, когда переменная-предиктор увеличивается, переменная-отклик также имеет тенденцию к увеличению, но после определенного момента переменная-отклик начинает уменьшаться, поскольку переменная-предиктор продолжает расти.
Например, мы можем использовать модель квадратичной регрессии, чтобы описать взаимосвязь между количеством часов, потраченных на работу, и уровнями счастья человека. Возможно, чем больше человек работает, тем более удовлетворенным он себя чувствует, но как только он достигает определенного порога, большая работа на самом деле приводит к стрессу и уменьшению счастья. В этом случае модель квадратичной регрессии будет соответствовать данным лучше, чем модель линейной регрессии.
Давайте рассмотрим пример выполнения квадратичной регрессии в Excel.
Квадратичная регрессия в Excel
Предположим, у нас есть данные о количестве отработанных часов в неделю и сообщаемом уровне счастья (по шкале от 0 до 100) для 16 разных людей:
Во-первых, давайте создадим диаграмму рассеяния, чтобы увидеть, является ли линейная регрессия подходящей моделью для соответствия данным.
Выделите ячейки A2:B17.Затем щелкните вкладку «ВСТАВИТЬ» на верхней ленте, затем нажмите « Разброс » в области « Диаграммы ». Это создаст диаграмму рассеяния данных:
Легко заметить, что зависимость между количеством отработанных часов и заявленным счастьем не является линейной. На самом деле он имеет U-образную форму, что делает его идеальным кандидатом для квадратичной регрессии .
Прежде чем мы подгоним модель квадратичной регрессии к данным, нам нужно создать новый столбец для квадратов значений нашей переменной-предиктора.
Сначала выделите все значения в столбце B и перетащите их в столбец C.
Затем введите формулу =A2^2 в ячейку B2. Это дает значение 36.Затем щелкните в правом нижнем углу ячейки B2 и перетащите формулу вниз, чтобы заполнить оставшиеся ячейки в столбце B.
Далее мы подгоним модель квадратичной регрессии.
Нажмите «ДАННЫЕ» на верхней ленте, затем нажмите « Анализ данных» справа. Если вы не видите эту опцию, то вам сначала нужно установить бесплатный Analysis ToolPak .
После того, как вы нажмете « Анализ данных» , появится всплывающее окно. Нажмите «Регрессия», а затем нажмите «ОК» .
Затем заполните следующие значения в появившемся окне Регрессия.Затем нажмите ОК .
Будут отображены следующие результаты:
Вот как интерпретировать различные числа из вывода:
Квадрат R: также известный как коэффициент детерминации, это доля дисперсии переменной отклика, которая может быть объяснена предикторными переменными. В этом примере R-квадрат равен 0,9092 , что указывает на то, что 90,92% дисперсии зарегистрированных уровней счастья можно объяснить количеством отработанных часов и количеством отработанных часов^2.
Стандартная ошибка: Стандартная ошибка регрессии — это среднее расстояние, на которое наблюдаемые значения отклоняются от линии регрессии. В этом примере наблюдаемые значения отклоняются от линии регрессии в среднем на 9,519 единиц .
F-статистика : F-статистика рассчитывается как регрессия MS/остаточная MS. Эта статистика показывает, обеспечивает ли регрессионная модель лучшее соответствие данным, чем модель, которая не содержит независимых переменных. По сути, он проверяет, полезна ли регрессионная модель в целом. Как правило, если ни одна из переменных-предикторов в модели не является статистически значимой, общая F-статистика также не является статистически значимой. В этом примере статистика F равна 65,09 , а соответствующее значение p <0,0001. Поскольку это p-значение меньше 0,05, регрессионная модель в целом является значимой.
Коэффициенты регрессии. Коэффициенты регрессии в последней таблице дают нам числа, необходимые для написания оценочного уравнения регрессии:
у шляпа = б 0 + б 1 х 1 + б 2 х 1 2
В этом примере расчетное уравнение регрессии имеет вид:
сообщаемый уровень счастья = -30,252 + 7,173 (отработанные часы) -0,106 (отработанные часы) 2
Мы можем использовать это уравнение для расчета ожидаемого уровня счастья человека на основе количества отработанных часов. Например, ожидаемый уровень счастья человека, который работает 30 часов в неделю, составляет:
сообщаемый уровень счастья = -30,252 + 7,173(30) -0,106(30) 2 = 88,649 .
Дополнительные ресурсы
Как добавить квадратную линию тренда в Excel
Как читать и интерпретировать таблицу регрессии
Что такое хорошее значение R-квадрата?
Понимание стандартной ошибки регрессии
Простое руководство по пониманию F-теста общей значимости в регрессии
Содержание
- В чем именно заключается МНК (метод наименьших квадратов)
- Как вывести формулы для вычисления коэффициентов
- Как изобразить МНК на графике функций
- Доказательство метода МНК
Начнем статью сразу с примера. У нас есть некие экспериментальные данные о значениях двух переменных – x и y . Занесем их в таблицу.
| i = 1 | i = 2 | i = 3 | i = 4 | i = 5 | |
| x i | 1 | 2 | 4 | 5 | |
| y i | 2 , 1 | 2 , 4 | 2 , 6 | 2 , 8 | 3 , 0 |
После выравнивания получим функцию следующего вида: g ( x ) = x + 1 3 + 1 .
Мы можем аппроксимировать эти данные с помощью линейной зависимости y = a x + b , вычислив соответствующие параметры. Для этого нам нужно будет применить так называемый метод наименьших квадратов. Также потребуется сделать чертеж, чтобы проверить, какая линия будет лучше выравнивать экспериментальные данные.
В чем именно заключается МНК (метод наименьших квадратов)
Главное, что нам нужно сделать, – это найти такие коэффициенты линейной зависимости, при которых значение функции двух переменных F ( a , b ) = ∑ i = 1 n ( y i — ( a x i + b ) ) 2 будет наименьшим. Иначе говоря, при определенных значениях a и b сумма квадратов отклонений представленных данных от получившейся прямой будет иметь минимальное значение. В этом и состоит смысл метода наименьших квадратов. Все, что нам надо сделать для решения примера – это найти экстремум функции двух переменных.
Как вывести формулы для вычисления коэффициентов
Для того чтобы вывести формулы для вычисления коэффициентов, нужно составить и решить систему уравнений с двумя переменными. Для этого мы вычисляем частные производные выражения F ( a , b ) = ∑ i = 1 n ( y i — ( a x i + b ) ) 2 по a и b и приравниваем их к 0 .
δ F ( a , b ) δ a = 0 δ F ( a , b ) δ b = 0 ⇔ — 2 ∑ i = 1 n ( y i — ( a x i + b ) ) x i = 0 — 2 ∑ i = 1 n ( y i — ( a x i + b ) ) = 0 ⇔ a ∑ i = 1 n x i 2 + b ∑ i = 1 n x i = ∑ i = 1 n x i y i a ∑ i = 1 n x i + ∑ i = 1 n b = ∑ i = 1 n y i ⇔ a ∑ i = 1 n x i 2 + b ∑ i = 1 n x i = ∑ i = 1 n x i y i a ∑ i = 1 n x i + n b = ∑ i = 1 n y i
Для решения системы уравнений можно использовать любые методы, например, подстановку или метод Крамера. В результате у нас должны получиться формулы, с помощью которых вычисляются коэффициенты по методу наименьших квадратов.
n ∑ i = 1 n x i y i — ∑ i = 1 n x i ∑ i = 1 n y i n ∑ i = 1 n — ∑ i = 1 n x i 2 b = ∑ i = 1 n y i — a ∑ i = 1 n x i n
Мы вычислили значения переменных, при который функция
F ( a , b ) = ∑ i = 1 n ( y i — ( a x i + b ) ) 2 примет минимальное значение. В третьем пункте мы докажем, почему оно является именно таким.
Это и есть применение метода наименьших квадратов на практике. Его формула, которая применяется для поиска параметра a , включает в себя ∑ i = 1 n x i , ∑ i = 1 n y i , ∑ i = 1 n x i y i , ∑ i = 1 n x i 2 , а также параметр
n – им обозначено количество экспериментальных данных. Советуем вам вычислять каждую сумму отдельно. Значение коэффициента b вычисляется сразу после a .
Обратимся вновь к исходному примеру.
Здесь у нас n равен пяти. Чтобы было удобнее вычислять нужные суммы, входящие в формулы коэффициентов, заполним таблицу.
| i = 1 | i = 2 | i = 3 | i = 4 | i = 5 | ∑ i = 1 5 | |
| x i | 1 | 2 | 4 | 5 | 12 | |
| y i | 2 , 1 | 2 , 4 | 2 , 6 | 2 , 8 | 3 | 12 , 9 |
| x i y i | 2 , 4 | 5 , 2 | 11 , 2 | 15 | 33 , 8 | |
| x i 2 | 1 | 4 | 16 | 25 | 46 |
Решение
Четвертая строка включает в себя данные, полученные при умножении значений из второй строки на значения третьей для каждого отдельного i . Пятая строка содержит данные из второй, возведенные в квадрат. В последнем столбце приводятся суммы значений отдельных строчек.
Воспользуемся методом наименьших квадратов, чтобы вычислить нужные нам коэффициенты a и b . Для этого подставим нужные значения из последнего столбца и подсчитаем суммы:
n ∑ i = 1 n x i y i — ∑ i = 1 n x i ∑ i = 1 n y i n ∑ i = 1 n — ∑ i = 1 n x i 2 b = ∑ i = 1 n y i — a ∑ i = 1 n x i n ⇒ a = 5 · 33 , 8 — 12 · 12 , 9 5 · 46 — 12 2 b = 12 , 9 — a · 12 5 ⇒ a ≈ 0 , 165 b ≈ 2 , 184
У нас получилось, что нужная аппроксимирующая прямая будет выглядеть как y = 0 , 165 x + 2 , 184 . Теперь нам надо определить, какая линия будет лучше аппроксимировать данные – g ( x ) = x + 1 3 + 1 или 0 , 165 x + 2 , 184 . Произведем оценку с помощью метода наименьших квадратов.
Чтобы вычислить погрешность, нам надо найти суммы квадратов отклонений данных от прямых σ 1 = ∑ i = 1 n ( y i — ( a x i + b i ) ) 2 и σ 2 = ∑ i = 1 n ( y i — g ( x i ) ) 2 , минимальное значение будет соответствовать более подходящей линии.
σ 1 = ∑ i = 1 n ( y i — ( a x i + b i ) ) 2 = = ∑ i = 1 5 ( y i — ( 0 , 165 x i + 2 , 184 ) ) 2 ≈ 0 , 019 σ 2 = ∑ i = 1 n ( y i — g ( x i ) ) 2 = = ∑ i = 1 5 ( y i — ( x i + 1 3 + 1 ) ) 2 ≈ 0 , 096
Ответ: поскольку σ 1 σ 2 , то прямой, наилучшим образом аппроксимирующей исходные данные, будет
y = 0 , 165 x + 2 , 184 .
Как изобразить МНК на графике функций
Метод наименьших квадратов наглядно показан на графической иллюстрации. С помощью красной линии отмечена прямая g ( x ) = x + 1 3 + 1 , синей – y = 0 , 165 x + 2 , 184 . Исходные данные обозначены розовыми точками.
Поясним, для чего именно нужны приближения подобного вида.
Они могут быть использованы в задачах, требующих сглаживания данных, а также в тех, где данные надо интерполировать или экстраполировать. Например, в задаче, разобранной выше, можно было бы найти значение наблюдаемой величины y при x = 3 или при x = 6 . Таким примерам мы посвятили отдельную статью.
Доказательство метода МНК
Чтобы функция приняла минимальное значение при вычисленных a и b , нужно, чтобы в данной точке матрица квадратичной формы дифференциала функции вида F ( a , b ) = ∑ i = 1 n ( y i — ( a x i + b ) ) 2 была положительно определенной. Покажем, как это должно выглядеть.
У нас есть дифференциал второго порядка следующего вида:
d 2 F ( a ; b ) = δ 2 F ( a ; b ) δ a 2 d 2 a + 2 δ 2 F ( a ; b ) δ a δ b d a d b + δ 2 F ( a ; b ) δ b 2 d 2 b
Решение
δ 2 F ( a ; b ) δ a 2 = δ δ F ( a ; b ) δ a δ a = = δ — 2 ∑ i = 1 n ( y i — ( a x i + b ) ) x i δ a = 2 ∑ i = 1 n ( x i ) 2 δ 2 F ( a ; b ) δ a δ b = δ δ F ( a ; b ) δ a δ b = = δ — 2 ∑ i = 1 n ( y i — ( a x i + b ) ) x i δ b = 2 ∑ i = 1 n x i δ 2 F ( a ; b ) δ b 2 = δ δ F ( a ; b ) δ b δ b = δ — 2 ∑ i = 1 n ( y i — ( a x i + b ) ) δ b = 2 ∑ i = 1 n ( 1 ) = 2 n
Иначе говоря, можно записать так: d 2 F ( a ; b ) = 2 ∑ i = 1 n ( x i ) 2 d 2 a + 2 · 2 ∑ x i i = 1 n d a d b + ( 2 n ) d 2 b .
Мы получили матрицу квадратичной формы вида M = 2 ∑ i = 1 n ( x i ) 2 2 ∑ i = 1 n x i 2 ∑ i = 1 n x i 2 n .
В этом случае значения отдельных элементов не будут меняться в зависимости от a и b . Является ли эта матрица положительно определенной? Чтобы ответить на этот вопрос, проверим, являются ли ее угловые миноры положительными.
Вычисляем угловой минор первого порядка: 2 ∑ i = 1 n ( x i ) 2 > 0 . Поскольку точки x i не совпадают, то неравенство является строгим. Будем иметь это в виду при дальнейших расчетах.
Вычисляем угловой минор второго порядка:
d e t ( M ) = 2 ∑ i = 1 n ( x i ) 2 2 ∑ i = 1 n x i 2 ∑ i = 1 n x i 2 n = 4 n ∑ i = 1 n ( x i ) 2 — ∑ i = 1 n x i 2
После этого переходим к доказательству неравенства n ∑ i = 1 n ( x i ) 2 — ∑ i = 1 n x i 2 > 0 с помощью математической индукции.
- Проверим, будет ли данное неравенство справедливым при произвольном n . Возьмем 2 и подсчитаем:
2 ∑ i = 1 2 ( x i ) 2 — ∑ i = 1 2 x i 2 = 2 x 1 2 + x 2 2 — x 1 + x 2 2 = = x 1 2 — 2 x 1 x 2 + x 2 2 = x 1 + x 2 2 > 0
У нас получилось верное равенство (если значения x 1 и x 2 не будут совпадать).
- Сделаем предположение, что данное неравенство будет верным для n , т.е. n ∑ i = 1 n ( x i ) 2 — ∑ i = 1 n x i 2 > 0 – справедливо.
- Теперь докажем справедливость при n + 1 , т.е. что ( n + 1 ) ∑ i = 1 n + 1 ( x i ) 2 — ∑ i = 1 n + 1 x i 2 > 0 , если верно n ∑ i = 1 n ( x i ) 2 — ∑ i = 1 n x i 2 > 0 .
( n + 1 ) ∑ i = 1 n + 1 ( x i ) 2 — ∑ i = 1 n + 1 x i 2 = = ( n + 1 ) ∑ i = 1 n ( x i ) 2 + x n + 1 2 — ∑ i = 1 n x i + x n + 1 2 = = n ∑ i = 1 n ( x i ) 2 + n · x n + 1 2 + ∑ i = 1 n ( x i ) 2 + x n + 1 2 — — ∑ i = 1 n x i 2 + 2 x n + 1 ∑ i = 1 n x i + x n + 1 2 = = ∑ i = 1 n ( x i ) 2 — ∑ i = 1 n x i 2 + n · x n + 1 2 — x n + 1 ∑ i = 1 n x i + ∑ i = 1 n ( x i ) 2 = = ∑ i = 1 n ( x i ) 2 — ∑ i = 1 n x i 2 + x n + 1 2 — 2 x n + 1 x 1 + x 1 2 + + x n + 1 2 — 2 x n + 1 x 2 + x 2 2 + . . . + x n + 1 2 — 2 x n + 1 x 1 + x n 2 = = n ∑ i = 1 n ( x i ) 2 — ∑ i = 1 n x i 2 + + ( x n + 1 — x 1 ) 2 + ( x n + 1 — x 2 ) 2 + . . . + ( x n — 1 — x n ) 2 > 0
Выражение, заключенное в фигурные скобки, будет больше 0 (исходя из того, что мы предполагали в пункте 2 ), и остальные слагаемые будут больше 0 , поскольку все они являются квадратами чисел. Мы доказали неравенство.
Ответ: найденные a и b будут соответствовать наименьшему значению функции F ( a , b ) = ∑ i = 1 n ( y i — ( a x i + b ) ) 2 , значит, они являются искомыми параметрами метода наименьших квадратов (МНК).
3. Аппроксимация функций с помощью метода
Метод наименьших квадратов применяется при обработке результатов эксперимента для аппроксимации (приближения) экспериментальных данных аналитической формулой. Конкретный вид формулы выбирается, как правило, из физических соображений. Такими формулами могут быть:
,
Сущность метода наименьших квадратов состоит в следующем. Пусть результаты измерений представлены таблицей:
Будем считать, что вид аппроксимирующей (приближающей) зависимости выбран, и её можно записать в виде
где f — известная функция, a , a 1 , …, am — неизвестные постоянные параметры, значения которых надо найти. В методе наименьших квадратов приближение функции (3.1) к экспериментальной зависимости считается наилучшим, если выполняется условие
то есть сумм a квадратов отклонений искомой аналитической функции от экспериментальной зависимости должна быть минимальна .
Заметим, что функция Q называется невязкой.
Так как невязка
то она имеет минимум. Необходимым условием минимума функции нескольких переменных является равенство нулю всех частных производных этой функции по параметрам. Таким образом, отыскание наилучших значений параметров аппроксимирующей функции (3.1), то есть таких их значений, при которых Q = Q ( a , a 1 , …, am ) минимальна, сводится к решению системы уравнений:
Методу наименьших квадратов можно дать следующее геометрическое истолкование: среди бесконечного семейства линий данного вида отыскивается одна линия, для которой сумма квадратов разностей ординат экспериментальных точек и соответствующих им ординат точек, найденных по уравнению этой линии, будет наименьшей.
Нахождение параметров линейной функции
Пусть экспериментальные данные надо представить линейной функцией:
Требуется подобрать такие значения a и b , для которых функция
будет минимальной. Необходимые условия минимума функции (3.4) сводятся к системе уравнений:
После преобразований получаем систему двух линейных уравнений с двумя неизвестными:
решая которую , находим искомые значения параметров a и b .
Нахождение параметров квадратичной функции
Если аппроксимирующей функцией является квадратичная зависимость
то её параметры a , b , c находят из условия минимума функции:
Условия минимума функции (3.6) сводятся к системе уравнений:
После преобразований получаем систему трёх линейных уравнений с тремя неизвестными:
при решении которой находим искомые значения параметров a , b и c .
Пример . Пусть в результате эксперимента получена следующая таблица значений x и y :
Требуется аппроксимировать экспериментальные данные линейной и квадратичной функциями.
Решение. Отыскание параметров аппроксимирующих функций сводится к решению систем линейных уравнений (3.5) и (3.7). Для решения задачи воспользуемся процессором электронных таблиц Excel .
1. Сначала сцепим листы 1 и 2. Занесём экспериментальные значения xi и yi в столбцы А и В, начиная со второй строки (в первой строке поместим заголовки столбцов). Затем для этих столбцов вычислим суммы и поместим их в десятой строке.
В столбцах C – G разместим соответственно вычисление и суммирование
2. Расцепим листы. Дальнейшие вычисления проведём аналогичным образом для линейной зависимости на Листе 1 и для квадратичной зависимости на Листе 2.
3. Под полученной таблицей сформируем матрицу коэффициентов и вектор-столбец свободных членов. Решим систему линейных уравнений по следующему алгоритму:
Для вычисления обратной матрицы и перемножения матриц воспользуемся Мастером функций и функциями МОБР и МУМНОЖ.
4. В блоке ячеек H2: H 9 на основе полученных коэффициентов вычислим значения аппроксимирующего полинома yi выч ., в блоке I 2: I 9 – отклонения D yi = yi эксп . — yi выч ., в столбце J – невязку:
Полученные таблицы и построенные с помощью Мастера диаграмм графики приведёны на рисунках 6, 7, 8.
Рис. 6. Таблица вычисления коэффициентов линейной функции,
аппроксимирующей экспериментальные данные.
Рис. 7. Таблица вычисления коэффициентов квадратичной функции,
аппроксимирующей экспериментальные данные.
Рис. 8. Графическое представление результатов аппроксимации
экспериментальных данных линейной и квадратичной функциями.
Ответ. Аппроксимировали экспериментальные данные линейной зависимостью y = 0,07881 x + 0,442262 c невязкой Q = 0,165167 и квадратичной зависимостью y = 3,115476 x 2 – 5,2175 x + 2,529631 c невязкой Q = 0,002103 .
Задания. Аппроксимировать функцию, заданную таблично, линейной и квадратичной функциями.
Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью квадратичной функции y=ax 2 +bx+с .
Метод наименьших квадратов (англ. Ordinary Least Squares, OLS) является одним из базовых методов регрессионного анализа в части оценки неизвестных параметров регрессионных моделей по выборочным данным. Основная статья про МНК — МНК: Метод Наименьших Квадратов в MS EXCEL.
Для построения графика квадратичной зависимости y=ax 2 +bx+с вспомним исходный критерий МНК, который необходимо минимизировать:
Вышеуказанное выражение примет минимальное значение при таких параметрах a, b и с, при которых соответствующие частные производные функции F равны нулю, т.е.:
В результате вычислений и преобразований получим систему из 3-х линейных уравнений:
Все выражения со знаком суммирования вычисляются из таблицы с исходными данными (см. файл примера ).
Получившуюся систему линейных уравнений можно решить, например методом обратной матрицы или с помощью функции ЛИНЕЙН() (эти подходы реализованы в файле примера ).
В результате вычислений будут найдены три параметра квадратичного полинома и построена соответствующая парабола.
Примечание: Три параметра можно также найти с помощью замены переменных – это сделано в более общем случае для полинома.