Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
МІНІСТЕРСТВО ОСВІТИ І НАУКИ, МОЛОДІ І СПОРТУ УКРАЇНИ
ДЕРЖАВНИЙ ВИЩИЙ НАВЧАЛЬНИЙ ЗАКЛАД
«ДОНЕЦЬКИЙ НАЦІОНАЛЬНИЙ ТЕХНІЧНИЙ УНІВЕРСИТЕТ»
КАФЕДРА ОБЧИСЛЮВАЛЬНОЇ МАТЕМАТИКИ І ПРОГРАМУВАННЯ
МЕТОДИЧНІ ВКАЗІВКИ І ЗАВДАННЯ
до курсової роботи
з курсу «Інформатика та системологія»
для студентів напряму підготовки 6.040106 «Екологія, охорона навколишнього середовища та збалансоване природокористування»
Протокол засідання
кафедри обчислювальної
математики і програмування
№ 1 від 30 серпня 2013р.
ДОНЕЦК, 2013
УДК 004.382.7: 004.42
Методичні вказівки і завдання до курсової роботи з курсу «Інформатика та системологія»для студентів напряму підготовки 6.040106 «Екологія, охорона навколишнього середовища та збалансоване природокористування» (російською мовою). Чеснокова О. В., Тарабаєва І. В. Донецьк, ДонНТУ, 2013. 25с.
Приведені завдання до виконання курсової роботи. Дані теоретичні відомості і методичні рекомендації щодо виконання курсової роботи. Наведені докладні приклади виконання завдань курсової роботи.
Автори: Чеснокова О. В., ст. преп. каф. ОМіП,
Тарабаєва І.В., к.т.н, доц. каф. ОМіП
Рецензент: Алєксєєв Є.Р., к.т.н, проф. каф. ОМіП
Відп. за випуск: Павлиш В.М., д.т.н., проф., зав. каф. ОМіП
Целью курсовой работы является закрепление теоретических знаний и практических навыков, полученных при изучении курса «Информатика и системология» и применение их при решении реальной задачи.
Тема курсовой работы выдается студенту руководителем. Студент оформляет лист задания, содержащий тему курсовой работы, дату выдачи и срок сдачи, установленный руководителем. Основной формой выполнения курсовой работы является самостоятельная работа студента под руководством преподавателя. Курсовая работа должна быть выполнена в сроки, указанные в листе задания, и сдана на проверку руководителю. Оценка за выполнение курсовой работы выставляется комиссией, назначенной заведующим кафедрой. При неудовлетворительной оценке курсовая работа возвращается для исправления или дополнения либо студенту выдается новое задание.
Структура пояснительной записки к курсовой работе.
Титульный лист
Лист задания
Содержание
Введение
Заключение
Список литературы
Требования к оформлению пояснительной записки.
На рис. 1 и рис. 2 приведены образцы титульного листа и листа задания, каждый из которых должен быть оформлен на отдельном листе формата А4.
Министерство образования и науки, молодежи и спорта Украины
Донецкий национальный технический университет
Кафедра вычислительной
математики и программирования
ПОЯСНИТЕЛЬНАЯ ЗАПИСКА
к курсовой работе по курсу «Информатика и системология»
Выполнил студент ____________
(группа, факультет)
____________________________
(фамилия, инициалы)
Руководитель работы__________
____________________________
(фамилия, инициалы)
Донецк, 20__
Рис. 1. Образец оформления титульного лист
Министерство образования и науки, молодежи и спорта Украины
Донецкий национальный технический университет
Кафедра вычислительной
математики и программирования
ЗАДАНИЕ
на курсовую работу по курсу «Информатика и системология»
студенту _____________________________________________
(группа, факультет, фамилия, инициалы)
ТЕМА КУРСОВОЙ РАБОТЫ
_________________________________________________________________
(наименование темы)
Дата выдачи задания____________
Срок сдачи работы______________
Руководитель _____________ (_____________)
(подпись) (фамилия, инициалы)
Донецк, 20__
Рис. 2. Образец оформления листа задания
Во введении необходимо кратко охарактеризовать тему курсовой работы, описать основные определения и понятия, обосновать необходимость решения подобных задач при помощи ПЭВМ.
В разделе «Постановка задачи» нужно привести словесное описание задачи.
Раздел «Описание математической модели решения задачи» должен содержать математическое описание задачи.
В разделе «Описание вычислительного инструмента и обоснование его применения для решения поставленной задачи» необходимо кратко описать компьютерную систему, выбранную для выполнения работы, и подробно остановиться на тех средствах, которые будут применяться для решения поставленной задачи.
«Реализация поставленной задачи» – это распечатка фрагментов работы программного приложения с решением поставленной задачи. Раздел должен содержать все необходимые вычисления с подробными комментариями и графическими иллюстрациями.
В разделе «Анализ результатов» необходимо охарактеризовать результаты работы программы и проанализировать их.
«Заключение» содержит общие выводы по выполнению курсовой работы.
Все издания, используемые при выполнении курсовой работы и их авторы, перечисляются в «Списке используемой литературы», а в «Приложении» приводится распечатка результатов, полученных в результате работы программного приложения.
Пояснительная записка оформляется на листах формата А4 в текстовом редакторе. Текст пояснительной записки должен быть набран 14 шрифтом типа Times New Roman и иметь следующие параметры. Размеры полей: левое – 25мм., правое – 10мм., верхнее – 20мм., нижнее – 20мм., абзац – 15мм, межстрочный интервал – 1.5, выравнивание – По-ширине.
Каждый раздел пояснительной записки должен начинаться с новой страницы. Заголовки разделов нумеруются арабскими цифрами и выполняются 16 шрифтом типа Ariel. Заголовки отделяются от основного текста (рис. 3).
Нумерация таблиц (рис. 3), рисунков (рис. 4) и формул (рис. 5)сквозная.
Рис. 3. Пример оформления одного из разделов пояснительной записки
Рис. 4. Пример оформления рисунка в курсовой работе
Рис. 5. Пример записи и нумерации формул
Страницы нумеруются с титульного листа. На титульном листе, листе задания и содержании номера страниц не ставят, на последующих страницах номера указывают арабскими цифрами в правом верхнем углу (рис. 6).
Рис. 6. Пример нумерации страниц в курсовой работе
Пояснительная записка представляется к защите в сброшюрованном виде.
ЗАДАНИЯ К КУРСОВОЙ РАБОТЕ НА ТЕМУ
«ОБРАБОТКА РЕЗУЛЬТАТОВ ЭКСПЕРИМЕНТА»
1. Постановка задачи
Пусть в результате эксперимента были получены некоторые данные, представленные в виде таблицы.
|
xi |
x1 |
x2 |
x3 |
x4 |
x5 |
x6 |
x7 |
… |
xn |
|
yi |
y1 |
y2 |
y3 |
y4 |
y5 |
y6 |
y7 |
… |
yn |
Необходимо провести регрессионный анализ экспериментальных данных и построить аналитическую зависимость вида
Y = f(x, a0 a1, ..., ak)
Вариант №1. P(s)=As3+Bs2+Сs+D
S |
0,00 |
1,00 |
1,50 |
2,00 |
2,50 |
3,00 |
3,50 |
4,00 |
4,50 |
5,00 |
|
P |
10,00 |
50,10 |
39,58 |
15,40 |
23,68 |
33,60 |
57,78 |
100,90 |
149,50 |
256,00 |
Вариант № 2. G(s)=Aebs
|
s |
0,5 |
1,5 |
2 |
2,5 |
3 |
3,5 |
4 |
4,5 |
5 |
|
G |
3,99 |
5,65 |
6,41 |
7,71 |
11,215 |
17,611 |
27,83 |
38,19 |
39,3 |
Вариант № 3. K(s)=Asb
|
s |
0,5 |
1 |
1,5 |
2 |
2,5 |
3 |
3,5 |
4 |
|
K |
1,65 |
2,1 |
2 |
2,1 |
2,3 |
2,4 |
2,22 |
2,59 |
Вариант № 4. V(s)=AsbеCs
|
s |
0,2 |
0,7 |
1,2 |
1,7 |
2,2 |
2,7 |
3,2 |
|
V |
2,3198 |
2,9569 |
2,3999 |
6,4357 |
6,5781 |
6,9459 |
14,6621 |
Вариант № 5. W(s)=1/(As+B)
|
s |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
|
W |
0,529 |
0,298 |
0,267 |
0,171 |
0,156 |
0,124 |
0,1 |
0,078 |
0,075 |
Вариант № 6. Q(s)=As2+Bs+C
|
s |
1 |
1,25 |
1,5 |
1,75 |
2 |
2,25 |
2,5 |
2,75 |
3 |
|
Q |
5,21 |
4,196 |
3,759 |
3,672 |
4,592 |
4,621 |
5,758 |
7,173 |
9,269 |
Вариант № 7. Y=x/(Ax+B)
|
x |
3 |
3,1 |
3,2 |
3,3 |
3,4 |
3,5 |
3,6 |
3,7 |
3,8 |
3,9 |
|
Y |
0,61 |
0,6 |
0,592 |
0,58 |
0,585 |
0,583 |
0,582 |
0,57 |
0,572 |
0,571 |
Вариант № 8. V=1/(A+Be-U)
U |
2 |
2,5 |
3 |
3,5 |
4 |
4,5 |
5 |
5,5 |
6 |
|
V |
5,197 |
7,78 |
11,14 |
15,09 |
19,24 |
23,11 |
26,25 |
28,6 |
30,3 |
Вариант № 9. R=AtB-14,5
|
t |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
R |
2,11 |
5,2 |
5,15 |
19,27 |
18,2 |
30,37 |
32 |
31 |
30,22 |
31,2 |
Вариант № 10. Z=At4+Bt3+Ct2+Dt+K
|
t |
0,66 |
0,9 |
1,17 |
1,47 |
1,7 |
1,74 |
2,08 |
2,63 |
3,12 |
|
Z |
38,9 |
68,8 |
64,4 |
66,5 |
64,95 |
59,36 |
82,6 |
90,63 |
113,5 |
Вариант № 11. Y=Ax3+Cx+D
|
x |
1 |
1,5 |
2 |
2,5 |
3 |
3,5 |
4 |
4,5 |
5 |
|
Y |
6,5 |
20,38 |
46,4 |
88,63 |
151,1 |
237,9 |
535 |
500,3 |
684,5 |
Вариант № 12. R=Ch2+K
|
h |
1 |
1,5 |
2 |
2,5 |
3 |
3,5 |
4 |
4,5 |
|
R |
7,5 |
14,25 |
23,7 |
25,86 |
50,7 |
68,25 |
88,5 |
111,5 |
Вариант № 13. Z=At4+Ct2+K
|
t |
1 |
1,5 |
2 |
2,5 |
3 |
3,5 |
4 |
4,5 |
5 |
|
Z |
2,2 |
10,6 |
35,6 |
90 |
191,1 |
359,2 |
618,7 |
997,9 |
1598,5 |
Вариант № 14. Z=At4+Bt3+Dt+K
|
t |
1 |
1,5 |
2 |
2,5 |
3 |
3,5 |
4 |
4,5 |
5 |
|
Z |
2,21 |
9,83 |
30,5 |
74,5 |
155,2 |
288,86 |
494,5 |
794,69 |
1214,6 |
Вариант № 15. Z=At4+Bt3+Ct2+K
|
t |
1 |
1,5 |
2 |
2,5 |
3 |
3,5 |
4 |
4,5 |
5 |
|
Z |
5,25 |
13,4 |
31,29 |
64,64 |
121,23 |
209,94 |
341,23 |
527,14 |
751 |
Вариант № 16. Z=At4+Dt+K
|
t |
0,4 |
0,6 |
0,8 |
1 |
1,2 |
1,4 |
1,6 |
1,8 |
2 |
|
Z |
0,5 |
1,35 |
2,37 |
3,8 |
5,8 |
8,65 |
12,57 |
18,05 |
0,86 |
Вариант № 17. Y=Ax3+D
|
x |
0 |
0,2 |
0,4 |
0,6 |
0,8 |
1 |
1,2 |
1,4 |
1,6 |
1,8 |
|
Y |
3,6 |
3,59 |
3,65 |
3,96 |
4,12 |
4,86 |
5,67 |
6,85 |
8,42 |
10,47 |
Вариант № 18. V=A+BeU
U |
0 |
0,5 |
1 |
1,5 |
2 |
2,5 |
3 |
3,5 |
|
V |
3,597 |
4,597 |
5,5984 |
7,5987 |
11,269 |
17 |
26,5096 |
42,1599 |
Вариант № 19. G=D/L+K
L |
1 |
1,13 |
1,25 |
1,38 |
1,5 |
1,63 |
1,75 |
1,88 |
2 |
|
G |
3,8 |
3,2 |
2,6 |
2,2 |
1,8 |
1,6 |
1,4 |
1,3 |
1,1 |
Вариант № 20. Z=At5+Bt4+Ct3+Dt2+Kt+L
|
t |
0,66 |
0,9 |
1,17 |
1,47 |
1,7 |
1,74 |
2,08 |
2,63 |
3,12 |
|
Z |
38,9 |
68,8 |
64,4 |
66,5 |
64,95 |
59,36 |
82,6 |
90,63 |
113,5 |
Вариант № 21. R= At5+Bt4+Ct3+Dt2+L
|
t |
2 |
4 |
6 |
8 |
10 |
12 |
14 |
16 |
|
R |
0,035 |
0,09 |
0,147 |
0,1 |
0,24 |
0,28 |
0,31 |
0,34 |
Вариант № 22. G= At5+Bt4+Dt2+Kt+L
t |
0 |
0,5 |
1 |
1,5 |
2 |
2,5 |
3 |
3,5 |
4 |
|
G |
2 |
2,39 |
2,81 |
3,25 |
3,75 |
4,11 |
4,45 |
4,85 |
5,25 |
Вариант № 23. Y= At5+Ct3+Dt2+Kt+L
|
t |
1 |
1,5 |
2 |
2,5 |
3 |
3,5 |
4 |
4,5 |
5 |
|
Y |
14,5 |
25 |
26,9 |
83,75 |
89,9 |
219,1 |
326,1 |
464 |
637,5 |
Вариант № 24. Y= At5+Bt4+Ct3+Kt+L
|
x |
1 |
1,5 |
2 |
2,5 |
3 |
3,5 |
4 |
4,5 |
5 |
|
Y |
6,5 |
20,38 |
46,4 |
88,63 |
151,1 |
237,9 |
535 |
500,3 |
684,5 |
Вариант № 25. R= At5+Ct3+Kt
|
t |
1 |
1,5 |
2 |
2,5 |
3 |
3,5 |
4 |
4,5 |
5 |
|
R |
2,19 |
14,8 |
57,15 |
163,2 |
384,5 |
793,5 |
1486 |
2585,5 |
4242,22 |
Вариант № 26. R= At5+Dt2+Kt
|
t |
1 |
1,5 |
2 |
2,5 |
3 |
3,5 |
4 |
4,5 |
5 |
|
R |
2,19 |
14,8 |
57,15 |
163,2 |
384,5 |
793,5 |
1486 |
2585,5 |
4242,22 |
Вариант № 27. W= At5+Dt2+L
|
t |
1 |
1,5 |
2 |
2,5 |
3 |
3,5 |
4 |
4,5 |
5 |
|
W |
0,19 |
0,17 |
0,15 |
0,14 |
0,13 |
0,12 |
0,11 |
0,1 |
0,9 |
Вариант № 28. V= At5+L
|
t |
1 |
1,1 |
1,2 |
1,3 |
1,4 |
1,5 |
1,7 |
1,8 |
1,9 |
2 |
|
V |
20 |
33 |
52,5 |
83,5 |
130,2 |
202,5 |
310 |
475 |
1079 |
1614 |
Вариант № 29. R=Ch2+Dh+K
|
h |
2 |
4 |
6 |
8 |
10 |
12 |
14 |
16 |
|
R |
0,035 |
0,09 |
0,147 |
0,1 |
0,24 |
0,28 |
0,31 |
0,34 |
Вариант № 30. G=DL+K
L |
0 |
0,5 |
1 |
1,5 |
2 |
2,5 |
3 |
3,5 |
4 |
|
G |
2 |
2,39 |
2,81 |
3,25 |
3,75 |
4,11 |
4,45 |
4,85 |
5,25 |
Вариант № 31. Y=Ax3+Bx2+Cx+D
|
x |
1 |
1,5 |
2 |
2,5 |
3 |
3,5 |
4 |
4,5 |
5 |
|
Y |
14,5 |
25 |
26,9 |
83,75 |
89,9 |
219,1 |
326,1 |
464 |
637,5 |
Вариант № 32. R=AtB
|
t |
1 |
1,5 |
2 |
2,5 |
3 |
3,5 |
4 |
4,5 |
5 |
|
R |
2,19 |
14,8 |
57,15 |
163,2 |
384,5 |
793,5 |
1486 |
2585,5 |
4242,22 |
Вариант № 33. W(s)=1/(Bs+C)
|
s |
1 |
1,5 |
2 |
2,5 |
3 |
3,5 |
4 |
4,5 |
5 |
|
W |
0,19 |
0,17 |
0,15 |
0,14 |
0,13 |
0,12 |
0,11 |
0,1 |
0,9 |
Вариант № 34. V(s)=sbеCs
|
s |
1 |
1,1 |
1,2 |
1,3 |
1,4 |
1,5 |
1,7 |
1,8 |
1,9 |
2 |
|
V |
20 |
33 |
52,5 |
83,5 |
130,2 |
202,5 |
310 |
475 |
1079 |
1614 |
Вариант № 35. Y=x/(Ax+B)
|
x |
1 |
1,5 |
2 |
2,5 |
3 |
3,5 |
4 |
4,5 |
5 |
|
Y |
0,214 |
0,221 |
0,2237 |
0,2258 |
0,2262 |
0,2268 |
0,2275 |
0,2283 |
0,2288 |
Вариант № 36. V(s)=AsеCs
|
s |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
|
V |
43,75 |
32,25 |
17,83 |
8,76 |
4 |
1,77 |
0,76 |
0,32 |
Вариант № 37. V(s)=Asbеs
|
s |
1 |
1,5 |
2 |
2,5 |
3 |
3,5 |
4 |
4,5 |
|
V |
6,5 |
8,71 |
12,67 |
18,6 |
27,63 |
42,61 |
65,6 |
101,86 |
Вариант № 38. V(s)=AsB
|
s |
1 |
2,3 |
2,9 |
4,1 |
5,2 |
5,9 |
6,8 |
8,1 |
9,2 |
|
V |
2,4 |
29 |
58,5 |
165,5 |
337,5 |
493 |
754,5 |
11275 |
1868 |
Вариант № 39. K(s)=AeSb
|
s |
0 |
0,1 |
0,2 |
0,3 |
0,4 |
0,5 |
0,6 |
0,7 |
0,8 |
|
K |
2,2 |
3,73 |
6,25 |
10,49 |
17,61 |
29,65 |
49,84 |
140,9 |
237,1 |
Вариант № 40. K(h)=1/(A+Be-h)
h |
2 |
2,5 |
3 |
3,5 |
4 |
4,5 |
5 |
5,5 |
6 |
|
K |
5,197 |
7,78 |
11,14 |
15,09 |
19,24 |
23,11 |
26,25 |
28,6 |
30,3 |
2. Рекомендации к выполнению курсовой работы
3. Математическая модель поставленной задачи
Метод наименьших квадратов используется при обработке реальных количественных данных, полученных в результате всевозможных научных опытов, технических испытаний, астрономических, геодезических и т.п. наблюдений.
Пусть в результате эксперимента были получены некоторые данные, представленные в виде таблицы:
|
xi |
x1 |
x2 |
x3 |
x4 |
x5 |
x6 |
x7 |
… |
xn |
|
yi |
y1 |
y2 |
y3 |
y4 |
y5 |
y6 |
y7 |
… |
yn |
Необходимо построить аналитическую зависимость, наиболее близко описывающую результаты эксперимента.
Идея метода наименьших квадратов заключается в том, что функцию
Y = f(x, a0 a1, ..., ak)
необходимо подобрать таким образом, чтобы сумма квадратов отклонений измеренных значений yi от расчетных Yi была наименьшей (рис. 1):
(1)
Задача сводится к определению коэффициентов ai из условия (1). Для ее решения необходимо составить систему уравнений
Рис. 1. Геометрическая интерпретация метода наименьших квадратов
Если параметры ai входят в зависимость Y = f(x, a0 a1, ..., ak) линейно, то получим систему из k-линейных уравнений с k неизвестными:
(2)
Зная коэффициенты ai, являющиеся решением системы (2), можно составить искомую функцию Y = f(x, a0, a1, ..., ak).
3.1 Подбор параметров линейной функции
Пусть необходимо определить параметры функции:Y = a0 + a1x.
Составим многочлен (1) для заданной функции:
Сформируем систему линейных уравнений (2) решив которую, определим коэффициенты функции Y = a0 + a1x:
,
. (3)
Необходимо определить параметры функции: Y = a0 + a1 x + a2 x2.
Составим функцию (1):
.
После дифференцирования система уравнений (2) примет вид:
(4)
Решив систему (4), найдем значение параметров ao, a1, a2.
3.3. Кубическая функция
Необходимо определить параметры многочлена третьей степени:
Y = ao + a1 x + a2 x2 + a3 x3.
Составим функцию
Система уравнений для вычисления параметров ao, a1, a2, a3 примет вид:
(5)
Необходимо определить параметры многочлена k-й степени:
Y=a0 + a1x + a1x2 +... + ak xk.
Тогда система уравнений для определения параметров ak принимает вид:
(6)
3.5. Функции, приводимые к линейной
Для вычисления параметров функции Y = axb необходимо выполнить некоторые арифметические преобразования:
ln(y) = ln(axb) = ln (a) + b ln (x)
и сделать замену
Y = ln (y), X = ln (x), A = ln (a),
которая приведет заданную функцию к линейному виду Y = A + bX, где коэффициенты A и b вычисляются по формулам (3.3) и, соответственно, a = eA.
Аналогично можно подобрать параметры функции вида Y = aebx.
Прологарифмируем заданную функцию:
ln (y) = ln (a) + bx ln (e) ln (y) = ln (a) + bx.
Проведем замену: Y = ln (y), A = ln (a) и получим линейную зависимость Y = bx + A. По формулам (1.3) найдем A и b, а затем вычислим a = eA.
В табл. 1.20, приведены примеры еще нескольких функций, которые сводятся к линейной функции элементарными заменами.
Преобразование функций Y = f(x, a,b) к виду Y = Ax + b
|
Функция |
Замена |
Функция |
Замена |
Функция |
Замена |
Прологарифмируем выражение Y = axbecx:
ln (y)= ln (a) + b ln (x) + cx ln (e)
и сделаем замену Y = ln (y), A = ln (a):
Y = A + b ln (x) + cx.
Составим функцию по формуле (1):
.
После дифференцирования получим систему трех линейных алгебраических уравнений для определения коэффициентов A, b и c.
(7)
Значение коэффициента а вычислим по формуле a = еA.
Линия, описываемая уравнением вида y = a0 + a1x, называется линией регрессии y на x, параметры a0 и a1 называются коэффициентами регрессии и определяются формулами (3.3).
Чем меньше величина
тем более обоснованно предположение, что экспериментальные данные описываются линейной функцией. Существует показатель, характеризующий тесноту линейной связи между x и y, называемый коэффициентом корреляции и рассчитываемый по формуле:
(8)
Значение коэффициента корреляции удовлетворяет соотношению:
–1 r 1.
Чем меньше отличается абсолютная величина r от единицы, тем ближе к линии регрессии располагаются экспериментальные точки. Если коэффициент корреляции равен нулю, то это означает, что между x и y не существует линейной связи, но между ними может существовать зависимость, отличная от линейной.
Обычно коэффициент корреляции r применяется только в тех случаях, когда между данными существует прямолинейная связь. Если же связь криволинейная, то пользуются индексом корреляции, который рассчитывается по формуле:
(9)
где y – экспериментальные значения, Y – теоретические значения, My – среднее значение y. Индекс корреляции по своему абсолютному значению колеблется в пределах от 0 до 1. При функциональной зависимости индекс корреляции равен 1. При отсутствии связи R = 0. Если коэффициент корреляции r является мерой тесноты связи только для линейной формы связи, то индекс корреляции R – и для линейной, и для криволинейной. При прямолинейной связи коэффициент корреляции по своей абсолютной величине равен индексу корреляции:
4. Реализация метода наименьших квадратов в MS EXCEL
4.1. Использование встроенных функций
Вычисление коэффициентов регрессии осуществляется с помощью функции
ЛИНЕЙН(Значения_y; Значения_x; Конст; статистика),
где
Значения_y — массив значений y,
Значения_x— необязательный массив значений x, если массив х опущен, то предполагается, что это массив {1;2;3;...} такого же размера, как и Значения_y,
Конст— логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если Конст имеет значение ИСТИНА или опущено, то b вычисляется обычным образом. Если аргумент Конст имеет значение ЛОЖЬ, то b полагается равным 0 и значения a подбираются так, чтобы выполнялось соотношение y=ax.
Статистика— логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии. Если аргумент Статистика имеет значение ИСТИНА, то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику. Если аргумент Статистика имеет значение ЛОЖЬ или опущен, то функция ЛИНЕЙН возвращает только коэффициент a и постоянную b.
Необходимо помнить, что результатом функций ЛИНЕЙН() является множество значений – массив.
Для расчета коэффициента корреляции используется функция
КОРРЕЛ(Массив1;Массив2),
возвращающая значения коэффициента корреляции, где Массив1 — массив значений y, Массив2 — массив значений x. Массив1 и Массив2 должны быть одной размерности.
ПРИМЕР 1. Зависимость y(x) представлена в таблице. Построить линию регрессии и вычислить коэффициент корреляции.
|
y |
0 |
0.5 |
1 |
1.5 |
2 |
2.5 |
3 |
3.5 |
4 |
|
x |
1 |
2.39 |
2.81 |
3.25 |
3.75 |
4.11 |
4.45 |
4.85 |
5.25 |
Введем таблицу значений в лист MS Excel и построим точечный график. Рабочий лист примет вид изображенный на рис. 2.
Рис. 2
Для того чтобы рассчитать значения коэффициентов регрессии а и b выделим ячейки A7:B7, обратимся к мастеру функций и в категории Статистические выберем функцию ЛИНЕЙН. Заполним появившееся диалоговое окно так, как показано на рис. 3 и нажмем ОK.
Рис. 3
В результате вычисленное значение появится только в ячейке A6 (рис.4). Для того чтобы значение появилось и в ячейке B6 необходимо войти в режим редактирования (клавиша F2), а затем нажать комбинацию клавиш CTRL+SHIFT+ENTER.
Для расчета значения коэффициента корреляции в ячейку С6 была введена следующая формула:
С7=КОРРЕЛ(B3:J3;B2:J2).
Рис. 4
Зная коэффициенты регрессии а и b вычислим значения функции y=ax+b для заданных x. Для этого введем формулу
B5=$A$7*B2+$B$7
и скопируем ее в диапазон С5:J5 (рис. 5).
Рис. 5
Изобразим линию регрессии на диаграмме. Выделим экспериментальные точки на графике, щелкнем правой кнопкой мыши и выберем команду Исходные данные. В появившемся диалоговом окне (рис. 5) выберем вкладку Ряд и щелкнем по кнопке Добавить. Заполним поля ввода, так как показано на рис. 6 и нажмем кнопку ОК. К графику экспериментальных данных будет добавлена линия регрессии. По умолчанию ее график будет изображен в виде точек, не соединенных сглаживающими линиями.
Рис. 6
Чтобы изменить вид линии регрессии, выполним следующие действия. Щелкнем правой кнопкой мыши по точкам, изображающим график линии, выберем команду Тип диаграммы и установим вид точечной диаграммы, так как показано на рис. 7.
Рис. 7
Тип линии, ее цвет и толщину можно изменить следующим образом. Выделить линию на диаграмме, нажать правую кнопку мыши и в контекстном меню выбрать команду Формат рядов данных… Далее сделать установки, например, так как показано на рис. 8.
Рис. 8
В результате всех преобразований получим график экспериментальных данных и линию регрессии в одной графической области (рис. 9).
Рис. 9
4.2. Использование линии тренда.
Построение различных аппроксимирующих зависимостей в MS Excel реализовано в виде свойства диаграммы – линия тренда.
ПРИМЕР 2. В результате эксперимента была определена некоторая табличная зависимость.
|
0.15 |
0.16 |
0.17 |
0.18 |
0.19 |
0.20 |
|
4.4817 |
4.4930 |
5.4739 |
6.0496 |
6.6859 |
7.3891 |
Выбрать и построить аппроксимирующую зависимость. Построить графики табличной и подобранной аналитической зависимости.
Решение задачи можно разбить на следующие этапы: ввод исходных данных, построение точечного графика и добавление к этому графику линии тренда.
Рассмотрим этот процесс подробно. Введем исходные данные в рабочий лист и построим график экспериментальных данных. Далее выделим экспериментальные точки на графике, щелкнем правой кнопкой мыши и воспользуемся командой Добавить линию тренда (рис. 10).
Рис. 10
Появившееся диалоговое окно позволяет построить аппроксимирующую зависимость.
На первой вкладке (рис. 11) этого окна указывается вид аппроксимирующей зависимости.
На второй (рис. 12) определяются параметры построения:
Рис. 11
Рис. 12
Выберем в качестве аппроксимирующей зависимости полином второй степени (рис. 11) и выведем уравнение, описывающее этот полином на график (рис. 12). Полученная диаграмма представлена на рис. 13.
Рис. 13
Аналогично с помощью линии тренда можно подобрать параметры таких зависимостей как
4.3. Использование инструмента анализа вариантов: Поиск решения.
Значительный интерес представляет реализация в MS Excel подбора параметров функциональной зависимости методом наименьших квадратов с использованием инструмента анализа вариантов: Поиск решения. Эта методика позволяет подобрать параметры функции любого вида. Рассмотрим эту возможность на примере следующей задачи.
ПРИМЕР 3. В результате эксперимента получена зависимость z(t) представленная в таблице
|
0,66 |
0,9 |
1,17 |
1,47 |
1,7 |
1,74 |
2,08 |
2,63 |
3,12 |
|
38,9 |
68,8 |
64,4 |
66,5 |
64,95 |
59,36 |
82,6 |
90,63 |
113,5 |
Подобрать коэффициенты зависимости Z(t)=At4+Bt3+Ct2+Dt+K методом наименьших квадратов.
Эта задача эквивалентна задаче нахождения минимума функции пяти переменных
(10).
Рассмотрим процесс решения задачи оптимизации (рис. 14).
Рис. 14
Пусть значения А, В, С, D и К хранятся в ячейках A7:E7. Рассчитаем теоретические значения функции Z(t)=At4+Bt3+Ct2+Dt+K для заданных t (B2:J2). Для этого в ячейку B4 введем значение функции в первой точке (ячейка B2):
B4=$A$7*B2^4+$B$7*B2^3+$C$7*B2^2+$D$7*B2+$E$7.
Скопируем эту формулу в диапазон С4:J4 и получим ожидаемое значение функции в точках, абсциссы которых хранится в ячейках B2:J2.
В ячейку B5 введем формулу, вычисляющую квадрат разности между экспериментальными и расчетными точками:
B5=(B4-B3)^2,
и скопируем ее в диапазон С5:J5. В ячейке F7 будем хранить суммарную квадратичную ошибку (10). Для этого введем формулу:
F7 = СУММ(B5:J5).
Воспользуемся командой СервисПоиск решения и решим задачу оптимизации без ограничений. Заполним соответствующим образом поля ввода в диалоговом окне, показанном на рис. 14 и нажмем кнопку Выполнить. Если решение будет найдено, то появится окно, изображенное на рис. 15.
Результатом работы решающего блока будет вывод в ячейки A7:E7 значений параметров функции Z(t)=At4+Bt3+Ct2+Dt+K. В ячейках B4:J4 получим ожидаемые значение функции в исходных точках. В ячейке F7 будет храниться суммарная квадратичная ошибка.
Изобразить экспериментальные точки и подобранную линию в одной графической области можно, если выделить диапазон B2:J4, вызвать Мастер диаграмм, а затем отформатировать внешний вид полученных графиков.
Рис. 17 отображает рабочий лист MS Excel после проведенных вычислений.
Рис. 15
Рис. 16
Рис. 17
5. СПИСОК ЛИТЕРАТУРЫ
1. Алексеев Е.Р., Чеснокова О.В., Решение задач вычислительной математики в пакетах Mathcad12, MATLAB7, Maple9. – НТ Пресс, 2006.–596с. :ил. –(Самоучитель)
2. Алексеев Е.Р., Чеснокова О.В. Mathcad 12.–НТ Пресс, 2005.–345с.
3. Алексеев Е.Р., Чеснокова О.В., Е.А. Рудченко, Scilab, решение инженерных и математических задач. –М., БИНОМ, 2008.–260с.
4. Алексеев Е.Р. Универсальный самоучитель начинающего пользователя ПК. –М., НТ Пресс, 2007.–640с.
5. Алексеев Е.Р. (под общей редакцией О.В. Чесноковой) Подробное руководство для начинающих осваивать Интернет. –М., НТ Пресс, 2008.–448с.
6. Березин И.С., Жидков Н.П., Методы вычислений.–М.:Наука, 1966.–632с.
7. Гарнаев А.Ю., Использование MS EXCEL и VBA в экономике и финансах. – СПб.: БХВ - Петербург, 1999.–332с.
8. Демидович Б.П., Марон И А., Шувалова В.З., Численные методы анализа.–М.:Наука, 1967.–368с.
9. Корн Г., Корн Т., Справочник по математике для научных работников и инженеров.–М., 1970, 720с.
10. Алексеев Е.Р., Чеснокова О.В. Методические указания к выполнению лабораторных работ в MS EXCEL. Для студентов всех специальностей. Донецк, ДонНТУ, 2004. 112 с.