|
|
|
|
|
|
|
|
|
|
0,77
|
0,56
|
2,76
|
7,06
|
5,54
|
28,76
|
8,12
|
65,87
|
11.89
|
130,75
|
1,45
|
2,08
|
3,45
|
14,98
|
5,81
|
30,76
|
77,85
|
12,56
|
149,56
|
1,76
|
3,04
|
3,89
|
15,98
|
6,98
|
45,76
|
9,45
|
86,09
|
13,43
|
172,45
|
2,23
|
2,76
|
4,87
|
23,22
|
7,34
|
50,87
|
10,87
|
101,65
|
13,55
|
175,51
|
2,65
|
3,65
|
5,04
|
26,12
|
7.86
|
60,45
|
11,23
|
124,37
|
14,76
|
200,54
|
Требуется выяснить - какая из функций - линейная, квадратичная или
экспоненциальная наилучшим образом аппроксимирует функцию заданную таблицей 1.
3.1 Аппроксимация функции y = f(х) многочленом первой степени
Поскольку
в задании каждая пара значений (,) встречается один раз, то корреляционная таблица
примет вид единичной матрицы. Значит условные средние совпадают со значениями . Отсюда следует, что корреляционное отношение равно 1 и, следовательно, между и существует
функциональная зависимость.
Для
проведения расчетов используем средства табличного процессора Microsoft Excel.
Таблица
2
Поясним,
как таблица 2 составляется.
Шаг
1. В ячейки B4:B28 заносим значения .
Шаг
2. В ячейки C1:C28 заносим значения .
Шаг
3. В ячейку D4 вводим формулу =B4^2.
Шаг
4. В ячейки D5:D28 эта формула копируется.
Шаг
5. В ячейку E4 вводим формулу =B4*C4.
Шаг
6. В ячейки E5:E28 эта формула копируется.
Шаг
7. В ячейку F4 вводим формулу =B4^3.
Шаг
8. В ячейки F5:F28 эта формула копируется.
Шаг
9. В ячейку G4 вводим формулу =B4^4.
Шаг
10. В ячейки G5:G28 эта формула копируется.
Шаг
11. В ячейку H4 вводим формулу =B4^2*C4.
Шаг
12. В ячейки H5:H28 эта формула копируется.
Шаг
13. В ячейку I4 вводим формулу =LN(C4).
Шаг
14. В ячейки I5:I28 эта формула копируется.
Шаг
15. В ячейку J4 вводим формулу =B4*LN(C4).
Шаг
16. В ячейки J5:K28 эта формула копируется.
Последующие
шаги делаем с помощью автосуммирования .
Шаг
17. В ячейку B29 вводим формулу =СУММ(B4:B28).
Шаг
18. В ячейку C29 вводим формулу =СУММ(C4:C28).
Шаг
19. В ячейку D29 вводим формулу =СУММ(D4:D28).
Шаг
20. В ячейку E29 вводим формулу =СУММ(E4:E28).
Шаг
21. В ячейку F29 вводим формулу =СУММ(F4:F28).
Шаг
22. В ячейку G29 вводим формулу =СУММ(G4:G28).
Шаг
23. В ячейку H29 вводим формулу =СУММ(H4:H28).
Шаг
24. В ячейку J29 вводим формулу =СУММ(J4:J28).
Шаг
25. В ячейку K29 вводим формулу =СУММ(K4:K28).
Аппроксимируем
функцию линейной функцией . Для
определения коэффициентов и воспользуемся
системой
(3.1.1)
Используя
итоговые суммы таблицы 2.1,расположенные в ячейках B29, C29, D29 и
E29, запишем систему 2.1 в виде
(3.1.2)
Результаты решения системы (3.1.2) представлены в таблице 3.
Таблица 3
Таким
образом, линейная аппроксимация имеет вид
=
-35,1556+14,0781x (3.1.3)
В
таблице 3 в ячейках B37:C38 введена формула {=МОБР(B33:C34)}.
В
ячейках F37:F38 введена формула {=МУМНОЖ(B37:C38;D33:D34)}.
.2
Аппроксимация многочленом второй степени
Далее
аппроксимируем функцию квадратичной функцией .
Для определения коэффициентов , и воспользуемся
системой
(3.2.1)
Используя
итоговые суммы таблицы 3, расположенные в ячейках B29, C29, D29, E29, F29, G29 и
H29, запишем систему (2.1.4) в виде
(3.2.2)
решив
которую, получим = -0,61511, =
0,55425и = -0,90104.
Результаты
решения системы (3.2.2) представлены в таблице 4.
Таблица
4
Таким
образом, квадратичная аппроксимация имеет вид
y=-0,61511+0,55425x-0,90104x2
(3.2.3)
В
таблице 2.3 в ячейках С47:E49 введена формула {=МОБР (C41:E44)}.
В
ячейках G47:G49 введена формула {=МУМНОЖ (C47:E49,F41:F44)}.
.3
Аппроксимация экспоненциальной зависимостью
Теперь
аппроксимируем функцию экспоненциальной функцией . Для определения коэффициентов и прологарифмируем
значения и используя итоговые суммы таблицы 2, расположенные в
ячейках B29, D29, I29 и J29, получим систему
(3.3.1)
где
=ln().
Результаты
решения системы (2.3.3) представлены в таблице 5.
Таблица
5
Таким
образом, система (2.3.3) имеет следующие решения: = 0,88433, =0,35140.
После потенцирования получим =
2,42137.
Таким
образом, экспоненциальная аппроксимация имеет вид
(3.3.2)
В
таблице 5 в ячейках B57:B58 введена формула {=МОБР (B53:C54)}.
В
ячейках Е56:Е57 введена формула {=МУМНОЖ (B57:C58,D53:D54)}.
В
ячейке Е58 введена формула =EXP(E56).
Вычислим
среднее арифметическое и по
формулам
;
Результаты
расчета и представлены
в таблице 6.
Таблица
6
В
ячейке В30 введена формула =B29/25.
В
ячейке В31 введена формула =C29/25.
.4
Расчет коэффициентов детерминированности и корреляции
Для
того чтобы рассчитать коэффициент корреляции и коэффициент детерминированности
воспользуемся таблицей 7, которая является продолжением таблицы 2.
Таблица
7
В
таблице 7 ячейки B4:B29 и C4:C29 уже заполнены (см. табл. 2).
В
ячейку K4 введена формула =(B4-$B$30)*(C4-$B$31).
В
ячейки K5:K28 эта формула копируется.
В
ячейку L4 введена формула =(B4-$B$30)^2.
В
ячейки L5:L28 эта формула копируется.
В
ячейку M4 введена формула =(C4-$B$31)^2.
В
ячейки M5:M28 эта формула копируется.
В
ячейку N4 введена формула =($E$37+$E$38*B4-C4)^2.
В
ячейки N5:N28 эта формула копируется.
В
ячейку O4 введена формула =($ =($F$47+$F$48*B4+$F$49*B4^2-C4)^2.
В
ячейки O5:O28 эта формула копируется.
В
ячейку P4 введена формула =($E$58*EXP($E$57*B4)-C4)^2.
В
ячейки P5:P28 эта формула копируется.
Последующие
шаги выполнены с помощью автосуммирования Σ.
В
ячейку K29 введена формула =СУММ(K4:K28).
В
ячейку L29 введена формула =СУММ(L4:L28).
В
ячейку M29 введена формула =СУММ(M4:M28).
В
ячейку N29 введена формула =СУММ(N4:N28).
В
ячейку O29 введена формула =СУММ(O4:O28).
В
ячейку P29 введена формула =СУММ(P4:P28).
Для
расчета коэффициента корреляции для линейной аппроксимации воспользуемся
формулой:
(2.3.1)
Для
расчета коэффициента детерминированности воспользуемся формулой:
(2.3.3)
Результаты
расчетов представлены в таблице 8.
Таблица
8
В
таблице 8 в ячейке C71 введена формула =K29/(L29*M29)^0,5.
В
ячейке F72 введена формула = =1-N29/M29.
В
ячейке F73 введена формула =1-O29/M29.
В
ячейке F74 введена формула = =1-P29/M29.
Анализ
результатов расчетов показывает, что квадратичная аппроксимация наилучшим
образом описывает экспериментальные данные.
4. Построение графиков функций и использование функции ЛИНЕЙН
Исследование характера зависимости проведём в три этапа:
1. Построение графика зависимости.
2. Построение
линии тренда (в данном случае это прямая ).
. Получение
числовых характеристик коэффициентов этого уравнения.
.1 Построение графика зависимости
) Выделим интервал B4:C28 (см. табл.2).
) Нажимаем «Вставка», выбираем «Точечную диаграмму».
) Среди точечных диаграмм выбираем диаграмму с маркерами.
) Выбираем «Макет 1».
) На появившейся диаграмме подписываем название «Линейная
аппроксимация».
.2 Построение линии тренда
1) Дважды щелкнем по диаграмме. Диаграмма активизируется.
2) После нажатия правой кнопки мыши на график, выберем из
открывшегося меню команду «Добавить линию тренда»
) Появиться диалоговое окно «Линия тренда» - выберем на вкладке
«Тип» - «линейный тип» и перейдем к вкладке «Параметры». На вкладке «Параметры»
потребуем показывать уравнение тренда на диаграмме и показывать значение R².
Нажмем кнопку «ОК».
) На диаграмме появится линия тренда с соответствующим уравнением.
При желании текстовое поле с уравнением можно перенести в более удобное место и
отредактировать.
Рис.1
Для построения квадратичной аппроксимации на третьем шаге в диалоговом
окне «Линия тренда» выберем на вкладке «Тип» полиномиальный тип степень 2.
Результат представлен на рис.2.
Рис. 2
Для построения экспоненциальной аппроксимации на третьем шаге в
диалоговом окне «Линия тренда» выберем на вкладке «Тип» экспоненциальный тип.
Результат представлен на рис.3.
Рис. 3
Примечание: построения диаграмм велись в Microsoft Excel 2007.
.3 Получение числовых характеристик зависимости
1) Создаем табличную формулу (5 строк и 2 столбца).
2) Выделяем область C80:D84.
) Вызываем «мастер функций».
) Выбираем функцию ЛИНЕЙН.
) Определяем аргументы функции - в графе «изв_знач_у» указываем
В1:В25; в графе «изв_знач_х» указываем А1:А25; графу «константа» оставляем
пустой; в графе «стат» набираем «истина».
) Нажимаем кнопку «закончить» и устанавливаем курсор в строку
формул.
) Устанавливаем курсор в строку формул и нажимаем комбинацию
клавиш Ctrl+Shift+Enter.
В результате получаем таблицу 9.
Таблица 9
|
C
|
D
|
80
|
14,07823657
|
-35,1563923
|
81
|
0,711474905
|
5,841406895
|
82
|
0,94451696
|
14,99754038
|
83
|
391,5410895
|
23
|
84
|
88067,85625
|
5173,303002
|
Заключение
Сравнение результатов, полученных в среде Excel в матричной форме, с результатами работы функции
ЛИНЕЙН показывает, что они полностью совпадают с вычислениями, проведенными
выше. Отсюда следует, что вычисления проведены правильно.
Линейная аппроксимация имеет вид:=-35,1556+14,0781x
Квадратичная аппроксимация имеет вид:
y=-0,61511+0,55425x-0,90104x2
Экспоненциальная аппроксимация имеет вид:
y=2,42137 е0,35140x
Анализ результатов расчетов показывает, что квадратичная аппроксимация
наилучшим образом описывает экспериментальные данные, т.к. коэффициент
корреляции равен 0,97186; Коэффициенты детерминированности линейной
аппроксимации - 0,94452; квадратичной аппроксимации - 0,99772; экспоненциальной
аппроксимация - 0,87120.
Список литературы
1.
Б.П. Демидович,
И.А. Марон. Основы вычислительной математики. М.: Государственное издательство
физико-математической литературы, 1963.
2.
Вычислительная
техника и программирование. Под ред. А.В. Петрова. М.: Высшая школа, 1991.
3.
Гончаров A., Excel 97 в примерах. - СПб: Питер, 1997.
4.
Левин А.,
Самоучитель работы на компьютере. - М.: Международное агентство А.Д.Т., 1996.
5.
Информатика:
Методические указания к курсовой работе. Санкт-Петербургский горный институт.
Сост. Д.Е. Гусев, Г.Н. Журов. СПб, 1999