Тема_5_Inf-2_МНК
.pdf
5.Выполнив предварительное форматирование диаграммы, щелчком ЛКМ в поле диаграммы выделяем её и открываем меню
"Диаграмма". Выбираем команду "Добавить линию тренда...". В
открывающемся окне "Линия тренда" (рис. 12) на вкладке "Тип" представлены 6 полей, определяющих тип выбираемой аппроксимирующей функции - линии тренда.
Рис. 12. Диалоговое окно "Линия тренда" вкладка "Тип"
6.После выбора типа линии тренда переключаемся на вкладку
"Параметры" (рис. 13).
Рис. 13. Диалоговое окно "Линия тренда" вкладка "Параметры"
11
Обратим внимание на флажок "показывать уравнение на диаграмме". При включении этого флажка в поле диаграммы выводится текст, соответствующий виду функции линии тренда
(рис. 14).
Рис. 14. Окончательный вид таблицы и диаграммы, включающей линию линейного тренда, полученного для функции Y
Вприведённом примере коэффициенты функции линейного тренда совпадают с коэффициентами линейной аппроксимирующей функции, полученной нами ранее.
Водну диаграмму можно включить несколько различных вариантов линии тренда, последовательно вызывая диалоговое окно "Линия тренда" и выбирая в нём разные типы аппроксимирующих функций.
5.3.Вычисление коэффициентов квадратичной регрессии
Уравнение квадратичной регрессии записывается в виде |
|
F(x)=a*x2 + b*x + c. |
(15) |
В этом случае функцию для определения суммарного квадратичного отклонения функции (1) от табличной функции с результатами измерений записывается в виде:
Φ( a,b,c ) = ∑n [Y −F( xi ,a,b,c )]2 |
(16) |
||
|
i=1 |
|
|
Найдём частные производные функции (15) по параметрам: |
|
||
∂F = x 2 , |
∂F = x , |
∂F = 1 . |
(17) |
∂a |
∂b |
∂c |
|
После чего найдём частные производные функции (16) по параметрам a, b и с и приравняем их нулю:
12
|
|
|
|
|
|
|
|
|
∂Φ |
= 2 ∑n ( yi − ax i2 − bx − c )( −xi2 ) = 0 |
|
||||||||||
|
|
|
|
|
|
|
|
|
∂a |
i =1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
∂Φ |
= −2 ∑n |
( yi |
|
− ax i2 |
− bx − c )( −xi ) = 0 |
(18) |
||||||
|
|
|
|
|
|
|
|
|
∂b |
||||||||||||
|
|
|
|
|
|
|
|
|
i =1 |
|
|
|
|
|
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
∂Φ |
= −2 ∑n ( yi − ax i2 − bx − c )( −1 ) = 0 |
|
||||||||||
|
|
|
|
|
|
|
|
|
∂c |
i =1 |
|
|
|
|
|
|
|
|
|
|
|
Исходная система уравнений для нахождения значений коэффициентов |
|||||||||||||||||||||
а, b и c записывается в этом случае как |
+ c S x2 |
= S x2 y , |
|
||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
aS x4 |
+ bS x3 |
|
|||||||||
|
|
|
|
|
|
|
|
|
|
aS x3 |
+ bS x2 |
+ S x c |
= S x y , |
(19) |
|||||||
|
|
|
|
|
|
|
|
|
|
S x2 a + S x b + nc = S y . |
|
||||||||||
Здесь использованы следующие обозначения: |
|
|
|||||||||||||||||||
|
|
|
|
|
1 |
|
n |
|
|
|
|
1 |
|
n |
|
1 |
n |
|
|||
S x 4 = |
|
∑ xi4 ; S x 3 = |
|
∑ xi3 ; S x 2 = |
∑ xi2 ; |
|
|||||||||||||||
|
|
|
|
|
|
|
|
||||||||||||||
|
|
|
|
|
n i =1 |
|
|
|
|
n i =1 |
|
n i =1 |
|
||||||||
|
|
|
1 |
|
|
n |
|
|
1 |
|
n |
|
|
|
|
||||||
S x |
= |
|
|
|
∑ xi |
; S y = |
|
|
|
∑ yi ; |
|
|
|
(18) |
|||||||
|
|
|
|
|
|
|
|
|
|||||||||||||
|
|
|
n i =1 |
|
|
n i =1 |
|
|
|
|
|||||||||||
S |
|
|
= |
|
|
1 n |
2 |
|
|
|
|
1 n |
y . |
|
|
||||||
2 |
|
|
|
|
|
∑ xi y ; S xy = |
|
|
∑ xi |
|
|
||||||||||
|
|
|
|
|
|
n i =1 |
|
|
|
|
|
n i =1 |
|
|
|
|
|||||
|
x |
y |
|
|
|
|
|
|
|
|
|
|
|
|
|
||||||
Для решения этой системы уравнений расширяем табл. 2. Формируем в
таблице столбцы со значениями Хi2Yi, Xi3 и Xi4, а затем вычисляем их суммы и осредненные значения. Результаты этих вычислений приведены в табл. 4.
|
|
|
|
|
Таблица 4. |
|
|
|
|
|
|
|
|
|
|
№ |
X |
Y |
X i 2 |
X i 3 |
X i 4 |
X i *Y i |
X i 2 *Y i |
1 |
0,82 |
44,52 |
0,6724 |
0,551368 |
0,452122 |
36,5064 |
29,93525 |
2 |
-5,90 |
-46,34 |
34,81 |
-205,379 |
1211,736 |
273,406 |
-1613,095 |
3 |
-4,42 |
-18,37 |
19,5364 |
-86,350888 |
381,6709 |
81,1954 |
-358,8837 |
4 |
1,19 |
51,59 |
1,4161 |
1,685159 |
2,005339 |
61,3921 |
73,0566 |
5 |
-1,53 |
20,52 |
2,3409 |
-3,581577 |
5,479813 |
-31,3956 |
48,03527 |
6 |
0,95 |
15,32 |
0,9025 |
0,857375 |
0,814506 |
14,554 |
13,8263 |
7 |
-5,86 |
-42,99 |
34,3396 |
-201,230056 |
1179,208 |
251,9214 |
-1476,259 |
8 |
-3,72 |
-20,77 |
13,8384 |
-51,478848 |
191,5013 |
77,2644 |
-287,4236 |
9 |
-1,11 |
11,61 |
1,2321 |
-1,367631 |
1,51807 |
-12,8871 |
14,30468 |
10 |
-4,70 |
-25,35 |
22,09 |
-103,823 |
487,9681 |
119,145 |
-559,9815 |
Σ |
-24,28 |
-10,26 |
131,1784 |
-650,117098 |
3462,354 |
871,102 |
-4116,485 |
13
Записанные в табл. 4 значения используем для формирования матрицы А и вектора правых частей системы линейных алгебраических уравнений (19). Матрица и вектор правых частей этой системы приведены в табл. 5.
|
|
|
Таблица |
5. |
a |
b |
c |
f |
|
3462,35 |
-650,12 |
131,18 |
-4116,49 |
|
-650,12 |
131,18 |
-24,28 |
871,10 |
|
131,18 |
-24,28 |
10,00 |
-10,26 |
|
Решаем систему уравнений (19), используя стандартные функции Excel МОБР() и МУМНОЖ(). Полученные в решении значения коэффициентов a, b и с приведены в табл. 6.
Mобр |
|
|
a= |
0,0046 |
0,0210 |
-0,0090 |
|
0,0210 |
0,1101 |
-0,0077 |
b= |
-0,0090 |
-0,0077 |
0,1993 |
c= |
Таблица 6
Проверка
-0,4418 -4116,49
9,6875 871,102
28,2902 -10,26
Для проверки правильности полученного решения перемножаем матрицу, записанную в табл. 5, на вектор решения, приведенный в табл. 6. В результате этого перемножения получим приведенный в табл. 6 вектор значений, совпадающий со значениями вектора правых частей f таблицы 5. Числовые значения элементов этого столбца совпадают со значениями элементов вектора правых частей системы уравнений, записанной в табл. 5.
Используя полученное решение, вычислим значения аппроксимируемой
№ X |
Y |
F(x) |
(Y-F(x))^2 |
|
1 |
0,82 |
44,52 |
35,94 |
73,67 |
2 |
-5,90 |
-46,34 |
-44,24 |
4,39 |
3 |
-4,42 |
-18,37 |
-23,16 |
22,93 |
4 |
1,19 |
51,59 |
39,19 |
153,69 |
5 |
-1,53 |
20,52 |
12,43 |
65,38 |
6 |
0,95 |
15,32 |
37,09 |
474,14 |
7 |
-5,86 |
-42,99 |
-43,65 |
0,43 |
8 |
-3,72 |
-20,77 |
-13,86 |
47,74 |
9 |
-1,11 |
11,61 |
16,99 |
28,98 |
10 |
-4,70 |
-25,35 |
-27,00 |
2,72 |
функции в точках, совпадающих с узловыми точками исходной таблицы.
Таблица 8.
После чего определим среднее квадратическое отклонение σ.
Сумма квадратов отклонений: |
874,0738 |
Среднее квадратическое отклонение: |
2,956474 |
14
Сравнение значений средних квадратических отклонений показывает, что при использовании квадратичной формулы величина среднего квадратичного отклонения уменьшается.
Для построения графиков исходной и аппроксимирующих функций выполним сортировку таблицы 8 по возрастанию значений переменной X. Результат сортировки показан в табл. 9.
Таблица 9.
№ |
X |
Y |
F(x) |
(Y-F(x))^2 |
2 |
-5,9 |
-46,34 |
-44,24 |
4,39 |
7 |
-5,86 |
-42,99 |
-43,65 |
0,43 |
10 |
-4,7 |
-25,35 |
-27,00 |
2,72 |
3 |
-4,42 |
-18,37 |
-23,16 |
22,93 |
8 |
-3,72 |
-20,77 |
-13,86 |
47,74 |
5 |
-1,53 |
20,52 |
12,43 |
65,38 |
9 |
-1,11 |
11,61 |
16,99 |
28,98 |
1 |
0,82 |
44,52 |
35,94 |
73,67 |
6 |
0,95 |
15,32 |
37,09 |
474,14 |
4 |
1,19 |
51,59 |
39,19 |
153,69 |
Для построения графиков объединим таблицу, приведенную на рис. 3 и столбец F(x) из табл.9.
|
|
|
Таблица 10. |
|
|
|
|
|
|
X |
F |
Fлин.регр. |
Fкв.регр. |
|
-5,9 |
-46,34 |
-41,70 |
-44,24 |
|
-5,86 |
-42,99 |
-41,23 |
-43,65 |
|
-4,7 |
-25,35 |
-27,64 |
-27,00 |
|
-4,42 |
-18,37 |
-24,36 |
-23,16 |
|
-3,72 |
-20,77 |
-16,16 |
-13,86 |
|
-1,53 |
20,52 |
9,49 |
12,43 |
|
-1,11 |
11,61 |
14,42 |
16,99 |
|
0,82 |
44,52 |
37,03 |
35,94 |
|
0,95 |
15,32 |
38,55 |
37,09 |
|
1,19 |
51,59 |
41,36 |
39,19 |
|
Используя точечную диаграмму можно построить графики трех функций Y(x), Fлин(х) и Fкв(х). Эти графики совпадают с линиями линейного и квадратичного тренда, которые показаны на рис. 6, приводимом ниже.
15
Y |
|
|
Квадратичная аппроксимация и линии трендов |
|
||||
60 |
|
|
|
|
|
|
|
|
|
Y |
|
|
Fкв |
|
|
|
|
40 |
Полиномиальный (Y) |
Линейный (Y) |
|
|
|
|
||
|
|
|
|
|
|
|
|
|
|
y = 11,716x + 27,42 |
|
|
|
|
|
|
|
20 |
|
|
|
|
|
|
|
|
|
y = -0,4418x2 + 9,6875x + 28,29 |
|
|
|
|
|
||
0 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
X |
-20 |
|
|
|
|
|
|
|
|
-40 |
|
|
|
|
|
|
|
|
-60 |
|
|
|
|
|
|
|
|
-6 |
-5 |
-4 |
-3 |
-2 |
-1 |
0 |
1 |
2 |
Рис.6. Точечная диаграмма, отображающая график исходной функции и линии тренда |
||||||||
Литература
1.Мудров А.Е. Численные методы для ПЭВМ на языках Бейсик, Фортран и Паскаль. – Томск: МП “РАСКО”, 1991. – 272 с.: ил.
2.Калиткин Н. Н. Численные методы. – М.: Наука, 1978. – 512 с.
3.Любимов Е.Б. и др. Решение систем линейных алгебраических уравнений средствами программы Microsoft Excel. Методические указания. СПбГАСУ, - СПб., 2005. - 16 с.
4.Заварыкин В.М. и др. Численные методы. - М.: Просвещение, 1990. -176 c.
5.Гарнаев А.Ю. Excel, VBA, Internet в экономике и финансах. - СПб.: БХВ-Петербург, 2001. - 816 с.
16