АППРОКСИМАЦИЯ ЭМПИРИЧЕСКИХ ДАННЫХ С ИСПОЛЬЗОВАНИЕМ MICROSOFT EXCEL - Студенческий научный форум

VIII Международная студенческая научная конференция Студенческий научный форум - 2016

Личный портфель

АППРОКСИМАЦИЯ ЭМПИРИЧЕСКИХ ДАННЫХ С ИСПОЛЬЗОВАНИЕМ MICROSOFT EXCEL

Ибрашев Ю.С., Ненашев И.Е., Беришвили О.Н., Плотникова С.В.
 Комментарии
Текст работы размещён без изображений и формул.
Полная версия работы доступна во вкладке "Файлы работы" в формате PDF
В инженерной практике часто возникает необходимость описать в виде функциональной зависимости связь между величинами, заданными таблично, в виде набора точек с координатами которая позволила бы «сгладить» экспериментальные погрешности, получить промежуточ-ные и экстраполяционные значения функций, изначально не содержащиеся в исходной табличной информации. В связи с чем, определена цель исследования – установить вид эмпирической зависимости и определить значения неизвестных параметров наилучшим образом описывающих экспери-ментальные данные. В задачи исследования входит: используя метод наименьших квадратов провести аппроксимацию многочленами первой и второй степени и экспоненциальной зависимостью; построить линии тренда и провести сравнительный анализ полученных результатов.

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

(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 График экспоненциальной аппроксимации

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

Библиографический список

  1. Апроксимация в Excel [Электронный ресурс]. – Режим доступа: http://tgspa.ru/info/education/faculties/ffi/ito/programm/ aproksimazia/ 1.3.html (дата обращения 28.09.2015)

  2. Беришвили, О. Н. Линейная алгебра и аналитическая геометрия: учеб. пособие / О.Н. Беришвили. – Самара: РИЦ СГСХА, 2012. – 301 с.

  3. Беришвили, О. Н. Методы оптимальных решений: учеб. пособие / О. Н. Беришвили, С. В. Плотникова. – Самара: РИЦ СГСХА, 2013. – 180 с.

Просмотров работы: 1223