Подобрать вид функциональной зависимости можно исходя из теоретических соображений или анализируя расположение точек на координатной плоскости. Определение параметров при известном виде зависимости осуществляют методом наименьших квадратов, согласно которому наилучшими коэффициентами считаются те, для которых сумма квадратов отклонений найденной эмпирической функции от заданных значений функции
(1)
будет минимальной.
Для того, чтобы найти коэффициенты доставляющих минимум функции S, определяемой формулой (1), используем необходимое условие экстремума функции нескольких переменных – равенство нулю частных производных первого порядка [3]. В результате получим систему для определения коэффициентов :
(2)
Конкретный вид системы (2) зависит от вида аппроксимирующей функции. В случае линейной зависимости система (2) примет вид:
В случае квадратичной зависимости будем иметь:
В некоторых случаях, например, экспоненциальной зависимости задачу сначала нужно линериазовать т.е. свести к линейной, путем логарифмирования [2].
Пусть функция задана таблицей 1. Требуется выяснить какая из функций линейная, квадратичная или экспоненциальная наилучшим образом аппроксимирует функцию
Таблица 1
Экспериментальные данные
12,85 |
154,77 |
9,65 |
81,43 |
7,74 |
55,86 |
5,02 |
24,98 |
1,86 |
3,91 |
12,32 |
145,59 |
9,63 |
80,97 |
7,32 |
47,63 |
4,65 |
22,87 |
1,76 |
3,22 |
11,43 |
108,37 |
9,22 |
79,04 |
7,08 |
48,03 |
4,53 |
20,32 |
1,11 |
1,22 |
10,59 |
100,76 |
8,44 |
61,76 |
6,87 |
36,85 |
3,24 |
9,06 |
0,99 |
1,10 |
10,21 |
98,32 |
8,07 |
60,54 |
5,23 |
25,65 |
2,55 |
6,23 |
0,72 |
0,53 |
Для проведения расчетов в табличном процессоре Microsoft Excel данные целесообразно расположить в виде таблицы 2 [1].
Таблица 2
Экспериментальные данные для проведения расчетов в табличном процессоре Microsoft Excel
A |
C |
D |
E |
F |
G |
H |
I |
||
1 |
x |
y |
x2 |
xy |
x3 |
x4 |
x2y |
lny |
xlny |
2 |
12,85 |
154,77 |
165,12 |
1988,79 |
2121,82 |
27265,4 |
25556,01 |
5,04 |
64,79 |
3 |
12,32 |
145,59 |
151,78 |
1793,66 |
1869,96 |
23037,9 |
22098,00 |
4,98 |
61,36 |
4 |
11,43 |
108,37 |
130,64 |
1238,66 |
1493,27 |
17068,0 |
14157,99 |
4,69 |
53,56 |
5 |
10,59 |
100,75 |
112,15 |
1066,94 |
1187,65 |
12577,2 |
11298,92 |
4,61 |
48,85 |
6 |
10,21 |
98,32 |
104,24 |
1003,84 |
106433 |
10866,8 |
10249,28 |
4,59 |
46,85 |
7 |
9,55 |
81,43 |
91,20 |
777,656 |
898,63 |
8317,90 |
7426,62 |
4,40 |
42,02 |
8 |
9,53 |
80,97 |
90,82 |
771,644 |
893,06 |
8248,44 |
7353,77 |
4,39 |
41,88 |
9 |
9,22 |
79,04 |
85,01 |
728,748 |
783,78 |
7226,43 |
6719,06 |
4,37 |
40,29 |
10 |
8,44 |
51,75 |
71,23 |
436,77 |
601,22 |
5074,23 |
3686,34 |
3,95 |
33,31 |
11 |
8,07 |
50,54 |
65,12 |
407,857 |
525,56 |
4241,25 |
3291,41 |
3,92 |
31,66 |
12 |
7,74 |
55,85 |
59,91 |
432,279 |
463,68 |
3588,92 |
3345,84 |
4,02 |
31,14 |
13 |
7,32 |
47,53 |
53,58 |
347,919 |
392,22 |
2871,07 |
2546,77 |
3,86 |
28,27 |
14 |
7,08 |
48,03 |
50,13 |
340,052 |
354,89 |
2512,66 |
2407,57 |
3,87 |
27,41 |
15 |
5,87 |
35,85 |
34,46 |
210,439 |
324,24 |
1187,28 |
1235,28 |
3,58 |
21,01 |
16 |
5,23 |
25,55 |
27,35 |
133,626 |
143,06 |
748,18 |
698,87 |
3,24 |
16,95 |
17 |
5,02 |
24,98 |
25,20 |
125,399 |
126,51 |
635,06 |
629,51 |
3,22 |
16,15 |
18 |
4,55 |
22,87 |
20,70 |
104,058 |
100,54 |
428,59 |
473,47 |
3,13 |
14,24 |
19 |
4,53 |
20,32 |
20,52 |
92,0496 |
92,96 |
421,11 |
416,98 |
3,01 |
13,64 |
20 |
3,24 |
9,05 |
10,50 |
29,322 |
34,01 |
110,20 |
95,00 |
2,20 |
7,14 |
21 |
2,55 |
5,23 |
6,50 |
13,3365 |
16,58 |
42,28 |
34,01 |
1,65 |
4,22 |
22 |
1,85 |
3,91 |
3,42 |
7,2335 |
6,43 |
11,71 |
13,38 |
1,36 |
2,52 |
23 |
1,75 |
3,22 |
3,06 |
5,635 |
5,45 |
9,38 |
9,86 |
1,17 |
2,05 |
24 |
1,11 |
1,22 |
1,23 |
1,3542 |
1,37 |
1,52 |
1,50 |
0,20 |
0,22 |
25 |
0,99 |
1,10 |
0,98 |
1,089 |
0,97 |
0,96 |
1,08 |
0,10 |
0,09 |
26 |
0,72 |
0,53 |
0,52 |
0,3816 |
0,37 |
0,27 |
0,27 |
-0,63 |
-0,46 |
27 |
163,08 |
1279,01 |
1402,97 |
12289,3 |
13502,5 |
12289,3 |
125964,2 |
79,49 |
657,57 |
Аппроксимируем линейной функцией Для определения коэффициентов и воспользуемся системой (3) и итоговыми суммами таблицы 2, расположенными в ячейках A27, B27, C27 и D27:
Решив систему средствами Microsoft Excel, получим и Таким образом, линейная аппроксимация имеет вид y= -24,74+11,63x.
Аппроксимируем квадратичной функцией Для определения коэффициентов воспользуемся системой (4). Используя итоговые суммы таблицы 2, расположенные в ячейках A27, B27, C27, D27, E27, F27, G27 запишем систему в виде:
В результате решения средствами Microsoft Exсel получим Квадра-тичная аппроксимация имеет вид ..
Аппроксимируем функцию экспонен-циальной зависимостью . Для определения коэффициентов и прологарифмируем значения и, используя итоговые суммы таблицы 2, расположенные в ячейках A27, C27, H27 и I27 получим систему:
где
Решив систему, средствами Microsoft Excel, найдем После потенцирования получим Экспоненциальная аппроксимация имеет вид
Построим в Excel графики полученных зависимостей и линии их трендов с использованием функции ЛИНЕЙН [1].
Рис.1 График линейной аппроксимации
Рис. 2 График квадратичной аппроксимации
Рис. 3 График экспоненциальной аппроксимации
Анализ результатов расчетов и построенные графики показывают, что квадратичная аппроксимация наилучшим образом описывает экспериментальные данные.
Библиографический список
Апроксимация в Excel [Электронный ресурс]. – Режим доступа: http://tgspa.ru/info/education/faculties/ffi/ito/programm/ aproksimazia/ 1.3.html (дата обращения 28.09.2015)
Беришвили, О. Н. Линейная алгебра и аналитическая геометрия: учеб. пособие / О.Н. Беришвили. – Самара: РИЦ СГСХА, 2012. – 301 с.
Беришвили, О. Н. Методы оптимальных решений: учеб. пособие / О. Н. Беришвили, С. В. Плотникова. – Самара: РИЦ СГСХА, 2013. – 180 с.