Численные методы

Размещено на http://www.allbest.ru/

Контрольные задания

«Численные методы»

ассистент кафедры высшей математики Касаткина Е.А.

Введение

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

Существует специальное направление в математике, которое занимается разработкой методов реализации на компьютерах перечисленных задач. Такие методы получили название численных методов. Как правило, решение задач с использованием численных методов проводится либо на базе алгоритмических языков программирования (Паскаль, Бейсик и др.), либо на основе специализированных пакетов прикладных программ (например, Matchad). Появление такого мощного средства инженерных и научных вычислений как табличный процессор EXCEL, позволяет реализовать численные методы с его помощью.

В процессе выполнения контрольной работы студенты познакомятся с численными методами и их реализацией в EXCEL на примерах решения уравнений и систем уравнений, интерполяции и интегрирования функций.

Контрольная работа состоит из 10 заданий. Номер варианта контрольной работы определяется последней цифрой номера зачетной книжки или студенческого билета.

1. Интерполяция функций с равноотстоящими узлами

Научиться пользоваться программой EXCEL для получения аналитической зависимости по экспериментальным данным.

2. Основные теоретические положения

2.1 Приближение функций одной переменной

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

Пример 1.

Закон движения некоторого объекта S = f(t) представлен в табл.1 (t - время, S -путь).

Таблица 1

t	0	1	2	5	4	5	6
S	0	2	10	30	46	130	222

Требуется найти пройденный объектом путь к моменту t = 3,5.

Для вычисления S=f(3,5) необходимо на основе табл.1 получить математическое описание функциональной зависимости S=f(t).

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

В тех случаях, когда значения функции в узлах определены с некоторой погрешностью или количество узловых точек велико, требование точного совпадения в узлах излишне. Аппроксимирующая зависимость должна быть близка к исходной функции лишь в смысле некоторого критерия. В этом случае задача о приближении ( задача аппроксимации) ставится следующим образом. Требуется данную достаточно сложную функцию f(x) заменить (аппроксимировать) полиномом так, чтобы отклонение функции от полинома на заданном множестве Х={х} было минимальными качестве критерия рассогласования в задачах аппроксимации наиболее распространен критерий «наименьших квадратов» R:

где yi - наблюдаемое значение аппроксимирующей зависимости (вычисленное или полученное из опыта) в i-й точке;

f(xi) - рассчитанное значение аппроксимирующей функции в i-й точке.

2.2 Постановка задачи интерполяции

Задача интерполирования может быть сформулирована следующим образом.

Пусть на отрезке [а;b] заданы n+1 точка x0,x1,…,xn, которые называются узлами интерполяции, и значения некоторой интерполируемой функции f(х) в этих точках, т. е.

y0=f(x0); y1=f(x1); … ; yn=f(xn).

Требуется построить интерполирующую зависимость F(x), которая в узлах интерполяции принимает те же значения, что и интерполируемая функция f(х), т.е.

F(x0)=f(x0)=y0,

……………………

F(xn)=f(xn)=yn.

Графически задача интерполирования заключается в том, чтобы построить такую интерполирующую функцию, которая бы проходила через все узлы интерполяции.

Чаще всего в качестве интерполирующей функции F(x) используются многочлены Рn(х). Задача состоит в том, чтобы подобрать многочлен Рn(х), обеспечивающий требуемую точность интерполяции , т.е. удовлетворяющий условию

. (1)

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

2.3 Конечные разности

Пусть для значений х0, х0+h, x0+2h,…,x0+nh, где h - шаг интерполяции, известны значения функции y0, y1,…,yn.

Определение: Конечной разностью первого порядка называется разность

(2)

Аналогично определяются конечные разности второго и более высокого порядка

(3)

Конечные разности при вычислении удобно записать в таблицу, форма которой представлена в табл. 2.

Таблица 2

i	xi	yi
0	x0	y0
1	x1	y1
2	х2	y2
J	х3	yз
4	x4	y4

Отметим, что число (порядок) конечных разностей всегда на единицу меньше числа узлов.

Пример 2.

Вычислим таблицу конечных разностей для экспериментальных данных, приведенных в табл.3.

Таблица 3

xi	0	1	2	3
yi	5	5	9	25

Решение.

Сведем значения конечных разностей в табл.4.

Таблица 4

i	xi	yi
0	0	5	0	4	8
1	1	5	4	12
2	2	9	16
3	3	25

2.4 Интерполяционный полином Ньютона

Интерполяционный многочлен Ньютона для равноотстоящих узлов записывается в виде

(4)

(5)

Можно показать, что оценка погрешности Rn(x) при замене f(x) полиномом Рn(х) имеет вид:

(6)

Пример 3.

Построим интерполяционный полином Ньютона для экспериментальных данных, приведенных в табл.3 (конечные разности в табл.4).

Ясно, что здесь шаг интерполяции h=l. Степень полинома определяется числом (порядком) конечных разностей, т.е.



Итак, интерполяционный полином для табл.3 имеет вид:

Пример 4.

Найти значение у =f(x) для х=1,5.

Решение.

т.е. y(1,5)=6.

3. Порядок выполнения работы

Задание 1. Средствами таблицы Excel провести интерполяцию полиномом Ньютона данных табл.3 и вычислить значения: у(1,5);y(0,8); y(2,7).

Задание 2. Средствами таблицы Excel провести интерполяцию полиномом Ньютона данных табл. 1 и вычислить значения: y(4,3); y(5,6).

Задание 3. Средствами таблицы Excel провести интерполяцию полиномом Ньютона данных по индивидуальному заданию.

3.1 Выполнение задания 1

3.1.1 Подготовка исходных данных электронной таблицы в Excel (табл.5.)

1) Введем текстовые и числовые константы ( ячейки А1:К4 ). Свободные ячейки в табл.5 оставлены для того, чтобы было удобно преобразовать ее для выполнения задания 2.

2) Введем номера по порядку в ячейки А5:А8.

3) Введем исходные данные в ячейки В5:С8.

3.1.2 Ввод формул

1) Ввод формул для вычисления конечных разностей первого порядка.

а) в ячейку D5 введем формулу для вычисления , которая примет вид: =С6-С5;

б) копируем эту формулу в ячейки D6:D7. В результате в ячейке D6 получаем формулу =С7-С6 (т.е. ), авячейке D7 получаем формулу = С8-С7 (т.е. ). Отображение табл.5 в режиме показа формул представлено в табл.6.

2) Ввод формул для вычисления конечных разностей второго порядка.

а) копируем формулу из ячейки D5 в ячейку Е5. В ячейке Е5 будет формула =D6-D5 (т.е. );

б) копируем формулу из ячейки Е5 в ячейку Е6 (см. табл.6).

3) Ввод формул для вычисления конечных разностей третьего порядка. Копируем формулу из ячейки Е5 в F5.

Обратите внимание ! Для вычисления всех конечных разностей необходимо ввести только одну формулу (в ячейке D5), все остальные получены копированием!!! Это справедливо для любого объема исходных данных, т.е. для любого порядка конечных разностей.

4) Ввод формул для вычисления промежуточных коэффициентов.

В выражении (5), описывающем вычисление полинома Ньютона, можно выделить общую закономерность вычисления коэффициентов. Каждый следующий Ki+1 получается из предыдущего Ki-го умножением его на множитель (х-хi) и делением на i·h ( вспомним, что и т.д.).

4.1 Ввод формулы для вычисления первого промежуточного коэффициента.

а) Пропустим столбцы GчI для удобства преобразования табл.5 и выполнения задания 2;

б) В ячейку J5 введем формулу для вычисления первого промежуточного коэффициента , для чего запишем: =($К$2-В5)/(А5+1)/$F$2. В ячейке К2 находится текущее значение X. При копировании адрес этой ячейки изменять нельзя, используем абсолютный адрес (значок доллара). Так как 1! =1, для задания этой константы используем порядковый номер исходных данных, увеличенный на единицу

(А5+1)=0+1=1=1!

Таблица 5

Интерполяция
Шаг интерполяции h=	1	Текущее значение x	1,5
№ п/п	Исходные данные	КОНЕЧНЫЕ РАЗНОСТИ	Промежуточные коэффициенты	Коэффициенты полинома Ньютона
	x	y	Первого порядка	Второго порядка	Третьего порядка
0	0	5	в	4	8				1,5	0
1	1	5	4	12					0,375	1,5
2	2	9	16						-0,0625	-0,5
3	3	25
СУММА КОЭФФИЦИЕНТОВ ПОЛИНОМА	1
		ЗНАЧЕНИЕ ПОЛИНОМА	6

Таблица 6

Интерполяция
Шаг интерполяции h=	1	Текущее значение х	1,5
№ п/п	Исходные данные	КОНЕЧНЫЕ РАЗНОСТИ	Промежуточные коэффициенты	Коэффициенты полинома Ньютона
	x	у	Первого порядки	Второго порядка	Третьего порядка
0	0	5	=С6-С5	=D6-D5	=E6-E5	=($K$2-В5)/(А5+1)/$F$2	=J5*D$5
1	1	5	=C7-C6	=D7-D6		=J5*($K$2-В6)/(А6+1)/$F$2	=J6*E$5
2	2	9	=C8-C7			=J6*($K$2-В7)/(А7+1)/$F$2	=J7*F$5
3	3	25
СУММА КОЭФФИЦИЕНТОВ ПОЛИНОМА	=СУММ(К5:К7)
		ЗНАЧЕНИЕ ПОЛИНОМА	=KI3+C5

В ячейке F2 находится шаг интерполяции. Адрес этой ячейки также абсолютный.

4.2 Ввод формулы для второго промежуточного коэффициента. В ячейку J6 вводим формулу для вычисления следующего промежуточного коэффициента.

,

где а - коэффициент в ячейке J5,

;

b - коэффициент, на который нужно домножать J5,

.

а) Вводим в J6 формулу: =J5·($K$2-B6)/(A6+1)/$F$2.

4.3 Ввод остальных промежуточных коэффициентов.

а) Копируем формулу из J6 во все остальные ячейки (для задания 1 это только одна ячейка;

б) если в таблице исходных данных, например, 10 пар чисел, то после ввода данных в J5 и J6 копируем их в 7 нижестоящих ячеек столбца J. Сравните формулы столбца J с формулами табл.6.

5) Ввод формул для вычисления полинома Ньютона.

а) ввод формул для вычисления первого члена полинома Ньютона, который равен

т.е. содержимое ячейки J5 нужно умножить на ячейку D5, где хранятся конечные разности первого порядка. Вводим в ячейку К5 формулу =J5+D$5. Знак $ перед номером строки необходим, так как в полиноме Ньютона находятся только конечные разности с индексом ноль (), т.е. все конечные разности берутся только из строки с номером 5.

б) ввод формул для вычисления остальных членов полинома Ньютона. Копируем формулу из К5 в К6:К7. При этом в К6 будет формула: =J6*D$5 (должно быть =J6*E$5), а в ячейке К7 формула: =J7*F$5. Корректируем формулы в К6:К7 (см.табл.6).

6) Ввод формул для вычисления суммы членов полинома Ньютона.

а) в ячейки B13:J13 вводим комментарий «Сумма коэффициентов полинома»;

б) в ячейку К13 вводим формулу: =СУММ(К5:К7). Теперь в К13 будет сумма всех членов полинома Ньютона, кроме у0;

в) в ячейки В14:J14 вводим комментарий «Значение полинома»;

г) в ячейку К14 вводим формулу: =К13+С5. В ячейке К14 появится число 6, которое и есть значение полинома, вычисленное в точке х=1,5.

3.1.3.Вычисление значений полинома при х=0,8 и х=2,7.

а) в ячейку К2 вводим 0,8. В ячейке К14 находим результат.

б) в ячейку К2 вводим 2,7. В ячейке К14 получаем результат.

3.2 Выполнение задания 2

3.2.1 Создание копии табл.5

* Ставим указатель мыши на имя Лист 1 , щелчок правой кнопкой мыши.

* Выбрать в появившемся контекстном меню команду Перед лист2 и Создать копию - появится Лист1(2).

3.2.2 Изменение исходных данных

В ячейках А5:С11 вводим исходные данные согласно табл.1 (табл.7 -показ ЭТ в режиме вычислений, табл.8 - в режиме формул).

3.2.3 Коррекция формул для вычисления конечных разностей

Вводим комментарий в ячейки G4:I4 (табл.7).

Из ячейки D7 копируем формулу в ячейки D8:D10.

Из ячейки Е6 копируем формулу в ячейки Е7:Е9.

Из ячейки F5 копируем формулу в F6:F8.

Из ячейки F5 копируем формулу в ячейки G5:I5.

Из ячейки G5 копируем формулу в ячейки G6:G7.

Из ячейки Н5 копируем формулу в Н6.

Коррекция формул для вычисления промежуточных коэффициентов Из ячейки J7 копируем формулу в ячейки J8:J10.

Коррекция формул для вычисления коэффициентов Ньютона

Из ячейки К7 копируем формулу в ячейки К8:К10.

В ячейке К8 исправляем D$5 на G$5.

В ячейке К9 исправляем D$5 на Н$5.

В ячейке К10 исправляем DS5 на I$5.

Корректировка итоговых формул

В ячейке К13 исправляем формулу на =СУММ(К5:К10).

Вычисление значения полинома.

Записываем результат из ячейки К14 для х=4,3.

Вводим в F2 число 5,6, и получаем результат в ячейке К14.

Таблица 7

Интерполяция
Шаг интерполяции h=	1	Текущее значение x	2,7
№ п/п	Исходные данные	КОНЕЧНЫЕ РАЗНОСТИ	Промежуточные коэффициенты	Коэффициенты полинома Ньютона
	x	y	Первого порядка	Второго порядка	Третьего порядка	Четвертого порядка	Пятого порядка	Шестого порядка
0	0	5	0	4	8	3	-13	30	2,7	0
1	1	5	4	12	11	-10	17		2,295	9,18
2	2	9	16	23	1	7			0,5355	4,284
3	3	25	39	24	8				-0,0402	-0,3213
4	4	64	63	32					0,0104	0,0835
5	5	127	95						-0,004	-0,032
6	6	222
СУММА КОЭФФИЦИЕНТОВ ПОЛИНОМА	13,194
		ЗНАЧЕНИЕ ПОЛИНОМА	18,194

Таблица 8

Интерполяция
Шаг интерполяции h=	1	Текущее значение x	2,7
№ п/п	Исходные данные	КОНЕЧНЫЕ РАЗНОСТИ	Промежуточные коэффициенты	Коэффициенты полинома Ньютона
	x	y	Первого порядка	Второго порядка	Третьего порядка	Четвертого порядка	Пятого порядка	Шестого порядка
0	0	5	=C6-C5	=D6-D5	=E6-E5	=F6-F5	=G6-G5	=H6-H5	=($K$2-В5)/(А5+1)/$F$2	=J5*D$5
1	1	5	=C7-C6	=D7-D6	=E7-E6	=F7-F6	=G7-G6		=J5*($K$2-В6)/(А6+1)/$F$2	=J6*E$5
2	2	9	=C8-C7	=D8-D7	=E8-E7	=F8-F7			=J6*($K$2-В7)/(А7+1)/$F$2	=J7*F$5
3	3	25	=C9-C8	=D9-D8	=E9-E8				=J7*($K$2-В8)/(А8+1)/$F$2	=J8*G$5
4	4	64	=C10-C9	=D10-D9					=J8*($K$2-В9)/(А9+1)/$F$2	=J9*H$5
5	5	127	=C11-C10						=J9*($K$2-В10)/(А10+1)/$F$2	=J10*I$5
6	6	222
СУММА КОЭФФИЦИЕНТОВ ПОЛИНОМА	=СУММ(К5:К7)
		ЗНАЧЕНИЕ ПОЛИНОМА	=KI3+C5

3.3 Выполнение задания 3

3.3.1 Ввод исходных данных для индивидуального задания

Необходимо осуществить интерполяцию с помощью полинома Ньютона исходных данных из табл.9.

Номер варианта выбирается по последней цифре номера зачетной книжки.

	Порядковый номер исходных данных
№	1	2	j	4	5	6	7	8	9	10
1-й вариант
X	1.415	1,420	1,425	1,430	1,435	1,440	1.445	1,450	1,455	1,460
У	0,88	0.889	0,890	0,891	0,892	0,893	0,894	0,895	0,896	0.897
Значения	х, =1,4161	л: 2 =1,462	х 3 = 1,413	х 4 = 1,470
2-й вариант
X	0,101	0,106	0.111	0,116	0,121	0.126	0,131	0,136	0,141	0.146
У	1.261	1.276	1,291	1,306	1,321	1,336	1,352	1.367	1.383	1,399
Значения	xi = 1,418	х 2 =1,4633	х3 = 1,4124	х4 = 1.4655
3-й вариант
X	0.15	0.20	0,25	0.30	0,35	0.40	0,45	0,50	0.55	060
У	0.86	0,819	0.779	0,741	0.705	0.670	0,638	0,606	0.577	0,549
Значения	х, = 1,4161	х 2 =1,462	х3= 1,413	х4 = 1,470
4-й вариант
X	0,18	0,185	0,190	0,195	0,200	0.205	0,210	0.215	0.220	0,225
У	5,615	5.467	5,352	5,193	5.066	4.946	4,832	4,722	4.618	4,519
Значения	Xi =0,182	х 2 =0,225	х3 = 0,175	х4 = 0.238
5-й вариант
X	3,5	3.55	3,60	3,65	3.70	3,75	3,80	3.85	3.90	3,95
У	33.11	34,65	36,60	38,47	40.44	42,52	44,70	46,99	49,40	51,93
Значения	х, =3,522	х2 = 4,176	х з = 3,475	х 4 = 4,25
6-й вариант
X	0,115	0,120	0,125	0.130	0,135	0.140	0,145	0,150	0.165	0,170
У	8.68	8,29	7,96	7,65	7,36	7.10	6,85	6,62	6.40	6,20
Значения	х, =0,122	х 2 =0,174	х з = 0,114	х4 = 0,185
7-й вариант
X	1,340	1,345	1.350	1.360	1.365	1,370	1.375	1.380	1,385	1,390
У	4,26	4,35	4.46	4.56	4.67	4,79	4,91	5,01	5.18	- .
Значения	х, = 1,362	х 2 =1,392	х з = 1,336	х4= 1,40
8-й вариант
X	0,15	0,16	0,17	0.18	0,19	0,20	0,21	0,22	0,23	0,24
У	4,48	4.95	5,47	5,99	6.05	6,68	6.909	7,38	8,166	9.025
Значения	х-, =0,153	х г =0,257	х3 = 0,140'	х 4 = 0,266
9-й вариант
X	0,45	0,46	0.47	0.48	0,49	0,50	0,51	0,52	0,53	0,54
У	20,19	19.61	18.94	18.17	17.30	16,31	15.19	13,94	12,55	10,99
Значения	х, =0,455	х 2 =0,551	S	х з = 0,44	х4 = 0.567
10-й вариант
X	0.01	0,06	0,11	0,16	0,21	0,26,.	0,31	0.36	0,41	0,46
У	0.99	0,95	0.91	0.88	0,84	0,81	0,78	0,74	0.71	0,68
Значения	х, =0,022	х 2 =0,525	х з = 0,008	х 4 = 0,610

3.3.2 Корректировка областей ЭТ

а) Для вычисления еще трех конечных разностей перенести информацию из столбцов J и К на три столбца правее:

выделить ячейки J5:К10;

щелкнуть по пиктограмме «Вырезать»;

поставить указатель мыши на ячейку М5;

щелкнуть по кнопке «Вставить».

б) Аналогичные действия выполнить для смещения формул из В13:К14 в В15:К16.

в) Осуществить ввод исходных данных в ячейки А5:С14 согласно данным табл.9.

г) Провести коррекцию формул, аналогично п.3.2.

д) Проделать вычисление для заданных значений x из табл.9.

Отчет по проделанной работе.

Представить результаты вычислений по заданиям 1, 2, 3.

II. Анализ и прогнозирование в Excel

1. Цель работы

Изучение режимов экстраполяции данных в Excel.

2. Основные теоретические положения

Табличный процессор Excel предоставляет две возможности для аппроксимации:

с использованием функций аппроксимации кривой;

с использованием аппарата графического анализа. Рассмотрим оба варианта.

2.1 Экстраполяция (прогнозирование) с помощью функции аппроксимации кривой

Пусть в узлах x0,x1,…,xn произведены измерения функции f(x0); f(x1); … ; f(xn). Необходимо осуществить прогнозирование (экстраполяцию), т.е. вычислить значения f(xn+1); f(xn+2); … .

В категории Статистические функции Excel имеются две, используемые для экстраполяции: ТЕНДЕНЦИЯ и ПРЕДСКАЗ. Обе осуществляют линейную аппроксимацию кривой для данных массивов X и У методом наименьших квадратов.

Функция ТЕНДЕНЦИЯ имеет структуру:

ТЕНДЕНЦИЯ (Умассив, Хмассив, Хсписок),

где: Хсписок - это значения х, для которых нужно осуществить прогнозирование; Хмассив и Умассив - исходные данные.

Пример 1.

Пусть в результате наблюдений за спросом на продукцию предприятия получена табл.10, в которой X - порядковый номер месяца.

Таблица 10

X	1	2	3	4
У	10	12	15	17

Необходимо осуществить прогноз спроса на май, июнь и июль месяц (т.е. на месяцы, соответствующие значениям X = {5;6;7}).

Разместим исходные данные в ячейках Al:F2 (фрагмент ЭТ представлен в табл. 11).

Таблица 11

	A	B	C	D	E	F
1	Исходные данные	X	1	2	3	4
2		У	10	12	15	17
3
4	Прогноз	X	5	6	7
5		У

Список значений X для прогнозирования разместим в ячейках С4:Е4. После обращения к функции ТЕНДЕНЦИЯ результат прогнозирования будет размещен в ячейках С5:Е5.

Функция ПРЕДСКАЗ (X; Умассив; Хмассив) после аппроксимации возвращает только одно прогнозируемое значение У (для одного из заданных значений аргумента X).

2.2 Графический аппарат прогнозирования в Excel

Excel имеет специальный аппарат для графического анализа данных, в том числе возможность построения Линий тренда, которые могут использоваться для анализа и прогнозирования. Тренд - это графическая аппроксимация экспериментальных данных. Для изучения Линий тренда построим небольшую табл. 12 - динамика спроса на продукцию.

Таблица 12

Месяц	Спрос(шт.)
Январь	90
Февраль	88
Март	85
Апрель	70
Май	69
Июнь	65
Июль

В табл.12 приведены результаты изучения спроса за первые шесть месяцев года. Ставится задача спрогнозировать спрос на следующий месяц.

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

В Excel возможность прогнозировать дает Мастер диаграмм. При выборе типа График, Линейная диаграмма (рис.1), можно построить график для табл.12. После щелчка по правой кнопке мыши по линии графика в появившемся контекстном меню имеется возможность воспользоваться командой Линия тренда. Эта команда вызывает одноименное окно (рис.2), в котором можно выбрать степень экстраполирующего полинома, а в следующем окне (рис.3) указать период прогнозирования.

3. Порядок выполнения работы

Задание 1. Осуществить прогнозирование для примера 1 с помощью статистических функций.

Задание 2. Осуществить прогнозирование для табл.12 с использованием графического средства в Excel.

3.1 Выполнение задания 1

3.1.1 Работа с функцией ТЕНДЕНЦИЯ

а) создать табл. 11 для примера 1;

б) чтобы поместить результат в список итоговых ячеек С5:Е5, а не в одну ячейку, выделить эти ячейки;

в) щелкнуть по пиктограмме Мастера функций;

г) в первом окне выбрать категорию Статистические, функцию ТЕНДЕНЦИЯ, щелкнуть по ОК;

д) в окно Изв.знач.У ввести адрес блока ячеек C2:F2;

е) в окно Изв.знач.Х ввести адрес блока ячеек С1:F1;

ж) в окне Нов. знач.Х укажем адрес блока ячеек С4:Е4;

з) для того, чтобы результат записался сразу в три выделенные ячейки, надо одновременно нажать клавиши CTRL, SHIFT и ENTER.

В ячейках С5:Е5 - прогноз на три месяца;

и) изменить в ячейках C2:F2 массив значений У на числа 2,4,6,8 и наблюдать изменение прогноза.

3.1.2 Работа с функцией ПРЕДСКАЗ

а) используем имеющуюся табл. 11. Прогноз будем осуществлять для первого значения список X (в ячейке С4);

б) для размещения результата активизируем ячейку С6;

в) с помощью Мастера функций вызываем функцию ПРЕДСКАЗ (категория Статистические);

г) в окне X укажем адрес ячейки С6;

д) в окне Изв.знач. У укажем адрес блока ячеек C2:F2;

е) в окне Изв. знач. X укажем С1:F1;

ж) щелкнуть по ОК: ( в С6 - прогноз);

з) изменить данные массива У (ввести в ячейки C2:F2 значения 3,6,9,12) и посмотреть, как изменится предсказанное значение в ячейке Сб.

3.2 Выполнение задания 2

Создать электронную таблицу согласно табл. 12.

Выделить всю заполненную данными область таблицы.

Щелкнуть по пиктограмме Мастер диаграмм.

Выбрать тип Диаграмма-график, формат 2 - линейная диаграмма (рис. 1).

Выполнить с помощью Мастера диаграмм все шаги построения графика.

Поставить указатель мыши на линию графика и щелкнуть правой клавишей. Появится контекстное меню.

В контекстном меню выбрать пункт Линия тренда. Появится одноименное диалоговое окно (рис.1).

На вкладке Тип этого окна выбрать Вид тренда: полиномиальный, четвертой степени.

На вкладке Параметры окна Линия тренда установить следующие параметры: Прогноз на один период; Показать уравнение на диаграмме (рис.3). На диаграмме будет показана линия тренда и прогноз на спрос на следующий период.



Размещено на http://www.allbest.ru/

Размещено на http://www.allbest.ru/



Размещено на http://www.allbest.ru/

Отчет по работе.

Записать результаты выполнения задания 1 и 2.

ІІІ. Приближенное решение уравнений. Отделение корней

интерполяция ньютон прогнозирование уравнение

1. Цель работы

Ознакомится с численными методами решения конечных уравнений и реализацией их в Excel.

2. Основные теоретические положения

2.1 Постановка задачи

В общем случае уравнение с одним неизвестным имеет вид

f(x)=0, (7)

где f(х) - заданная функция, определенная на отрезке [а,b]. Всякое число о (действительное или мнимое) на отрезке [а,b], обращающее уравнение в тождество:

f(о)?0 (8)

называется корнем уравнения или его решением.

Решение задачи приближенного определения корней уравнения состоит из двух этапов: 1) отделение корней, т.е. нахождение подинтервалов [б,в] на отрезке [а,b], которые содержат только один корень уравнения; 2) уточнение корней, т.е. непосредственное вычисление значений корней на найденных подинтервалах [б,в] с заданной точностью е.

2.2 Отделение корней

Графический способ отделения корней заключается в построении графика функции f(x) на отрезке [а,b]. Точка пересечения графика функции с осью абсцисс дает приближенное значение корня уравнения. Найденные таким образом приближенные значения корней позволяют выделить отрезки [б,в], на которых при необходимости можно выполнить уточнение корней (рис.4).



Размещено на http://www.allbest.ru/

При отделении действительных корней расчетным путем для непрерывных функций f(x) можно руководствоваться следующими соображениями:

если на концах отрезка [a,b] функция имеет разные знаки (f(a)·f(b)<0), то между точками а и b на оси абсцисс имеется нечетное число корней;

если же f(a)·f(b)>0, то между а и b имеется четное число корней или их совсем нет;

если f(a)·f(b)<0 и либо первая производная f '(х), либо вторая производная f ''(х) не меняют знака на этом отрезке, то уравнение имеет единственный корень на отрезке [а, b].

Отделим все действительные корни уравнения

x5-4х-2=0 (9)

Находим значения функции f(x) на концах отрезка: f(-2)=-26; f(2)=22. Так как функция имеет разные знаки, то на отрезке [-2,2] имеется нечетное число корней. Первая и вторая производные от функции f(x):

f '(х)=5х4-4 и f "(x)=20x3 (10)

меняют знак на отрезке [-2,2] (рис.5), поэтому корней будет несколько и отрезок надо разделить пополам.



Рассмотрим два отрезка [-2,0] и [0,2] отдельно; так как f(0)=-2, то f(2)·f(0)>0, a f(0)·f(2)<0. На первом отрезке [-2,0] либо нет корней, либо их четное число, а на втором отрезке [0,2] - корней нечетное число.

На втором отрезке первая производная меняет знак, вторая - не меняет, поэтому на этом отрезке есть единственный корень. Таким образом, отделен один корень уравнения - на отрезке [0,2].

Первый отрезок делим пополам, получаем два подинтервала: [-2,-1] и [-1,0]. Так как f(-l)=l, значения функций на концах каждого из отрезков различны:f(-2)·f(-l)<0 и f(-l)·f(0)<0; эти отрезки должны содержать нечетное число корней. На отрезке [-2,-1] первая производная не меняет знака, а на отрезке [-1,0] вторая производная не меняет своего знака. Отсюда можно заключить, что на интервалах [-2,-1] и [-1,0] имеется по одному корню.

Таким образом, на отрезке [-2,2] для уравнения х5-4х-2=0 отделено три действительных корня на отрезках [-2,-1], [-1,0] и [0,2].

2.3 Уточнение корней

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

Если при последовательных итерациях (к=1,2,...) получаемые величины х(к) все ближе приближаются к истинному значению корня то итерационный процесс будет сходящимся, в противном случае - расходящимся. При этом различают монотонную и колебательную сходимость (расходимость) в зависимости от того, с одной или с разных сторон осуществляется приближение (удаление) к (от) искомому решению.

Для реализации итерационного процесса должны быть заданы начальное приближение х(0) и точность s, с которой требуется найти решение уравнения.

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

Условие окончания итерационного процесса (нахождения значения корня с точностью е) имеет вид

, к=0,1,2,3,... . (11)

В Excel для организации итерационного процесса существует специальный режим ИТЕРАЦИИ ( команды Сервис - Параметры - Вычисления).

3. Порядок выполнения работы

Задание 1. Создать электронную таблицу для отделения корней.

Задание 2. Отделить все действительные корни для уравнения (9).

Задание 3. Отделить все действительные корни для уравнения из индивидуального задания.

3.1 Создание электронной таблицы для отделения корней

Как следует из п.2.2, отделение корней происходит в несколько этапов. На каждом этапе необходимо:

а) определить значения функции на концах интервала [а,b];

б) если эти значения разные по знаку (т.е. f(a)·f(b)<0), то корень существует. Проследим за изменением знаков на этом интервале у первой f '(х) и второй f "(х) производных ;

в) если f '(х) либо f "(х) не меняет знака, то корень на интервале [а,b] единственный.

Следовательно, создаваемая ЭТ должна обеспечить выполнение действий а) ч в) на одном этапе отделения корней, а изменение границы интервала приведет к повторению этих действий для нового интервала.

Создадим ЭТ для отделения корней - табл. 13, (режим отображения формул - табл.14).

3.1.1 Ввод текстовых и числовых констант

Введем комментарии и данные в ячейки А1:С6.

Используем обозначения: fp - первая производная функции f(x), fv - вторая производная f(x).

3.1.2 Ввод формул для вычисления значений функции

а) введем в ячейку А7 комментарий «Середина интервала»;

б) введем формулу в ячейку С7 для вычисления

х = (а+b)/2 - середины интервала [а,b]: =(С5+С6)/2;

в) введем формулу для вычисления значений f(x) в нижней границе интервала, т.е. f(a):

в ячейку А8 введем комментарий « f нижнее»;

в ячейку В8 введем формулу: =С5^5-4*С5-2;

г) введем формулу для вычисления значений

:

в ячейку А9 введем комментарий «f среднее»;

в ячейку В9 - формулу: =С7^5-4*С7-2 ;

д) введем формулу для вычисления значения функции f(x) в верхней границе интервала:

в ячейку А10 - комментарий «f верхнее»;

в ячейку В10 - формулу = С6^5-4*С6-2.

Ввод формул для вычисления значений первой производной. Заполним ячейки А11:В13 аналогично пп. в) ч д) раздела 3.1.2. и табл.14.

Ввод формул для вычисления значений второй производной.

Заполним ячейки А14:В16 аналогично пп. в) ч д) раздела 3.1.2. и табл.14.

Ввод формул для одной итерации отделения корней

а) в ячейку А17 введем формулу для проверки знаков функции f(x) на концах интервала: =ЕСЛИ(В8*В10<0; 1; 2). В результате выполнения этой команды в ячейке А17 будет записано число 1, если знаки f(x) на концах интервала разные (т.е. число корней нечетное), и число 2, если знаки значений f(x) на концах интервала одинаковые (т.е. число корней четное или их нет совсем).

б) в ячейку А18 введем формулу: =ЕСЛИ(А17=1;ЕСЛИ(В11*В13<0;0;ЕСЛИ(В12*В13>=0;EСЛИ(В11*В12>=0;1;0);0))). ЭТ проверит знаки первой производной на концах и в середине интервала. Если знаки одинаковые, в ячейку А18 будет записана 1, если разные - запишется 0;

в) в ячейку А19 запишем аналогичную формулу для проверки знаков на концах и в середине интервала для второй производной:

=ЕСЛИ(А17=1;ЕСЛИ(В14*В16<0;0;ЕСЛИ(В14*В15>=0;ЕСЛИ(В15*В16>=0;1; 0);0)));

г) в ячейку С20 введем формулу для суммирования содержимого ячеек А18иА19: =СУММ(А18:А19);

д) если хотя бы в одной из ячеек (А18 или А19) будет 1, то в С20 будет число, большее 0. Это значит, что либо первая, либо вторая производная не меняли своего знака на интервале, т.е. корень единственный. Введем в А21 формулу для установления этого факта:

=ЕСЛИ(С20>0; «На интервале "С5"-"С6" корень единственный»;0). Если вышеописанное условие выполняется, в ячейке А21 напечатается сообщение: «На интервале [-2,2] корень единственный»;

Таблица 13

Отделение корней
функция	f(x) = х^5 - 4*х - 2
Первая производная	fp(x) = 5*х^4-4
Вторая производная	fv(x) = 20*х^3
Нижняя граница интервала	0
Верхняя граница интервала	2
Средняя граница интервала	1,5
f нижнее	-2
f среднее	-5,00
f верхнее	22
fp нижнее	-4
fp среднее	1
fp верхнее	76
fv нижнее	0
fv среднее	20
fv верхнее	160
1
0
1
на интервале корень единственный
0
ПОВЛЕДОВАТЕЛЬНОСТЬ ДЕЙСТВИЙ
ПРИ ДЕЛЕНИИ ИНТЕРВАЛА ПОПОЛАМ
1.Вычислите и запишите границы двух полученных интервалов
2. Введите в ячейки С5 и С6 границы первого интервала
После пересчета Excel таблицы запишите результаты
3. Введите ячейки С5 и С6 границы второго интервала
Результаты запишите

Таблица 14

Отделение корней
функция	f(x)=х^5-4*х-2
Первая производная	fp(x)=5*х^4-4
Вторая производная	fv(x)=20*х^3
Нижняя граница интервала	0
Верхняя граница интервала	2
Средняя граница интервала	(С5+С6)/2
f нижнее	=С5^5-4*С5-2
fсреднее	=С7^5-4*С7-2
fверхнее	=С6^5-4*С6-2
fp нижнее	=5*С5^4-4
fp среднее	=5*С7^4-4
fp верхнее	=5*С6^4-4
fv нижнее	=20*С5^4
fv среднее	=20*С7^3
fv верхнее	=20*С6^3
=ЕСЛИ(В8*В10<0;1;2)
=ЕСЛИ(А17=1;ЕСЛИ(В11*В13<0;0;ЕСЛИ(В12*В13>=0;ЕСЛИ(В13*В14>=0; 1 ;0)
=ЕСЛИ(А17=1;ЕСЛИ(В14*В16<0;0;ЕСЛИ(В14*В15>=0;ЕСЛИ(В15*В16>=0; 1 ;0
		=СУММА(А18;А19)
=ЕСЛИ(С20>0), «на интервале корень единственный»,0)
=ЕСЛИ(С20=0); «следует разделить интервал»
ПОВЛЕДОВАТЕЛЬНОСТЬ ДЕЙСТВИИ
ПРИ ДЕЛЕНИИ ИНТЕРВАЛА ПОПОЛАМ
1 .Вычислите и запишите границы двух полученных интервалов
2. Введите в ячейки С5 и С6 границы первого интервала
После пересчета Excel таблицы запишите результаты
3. Введите ячейки С5 и С6 границы второго интервала
Результаты запишите

е) в ячейку А22 введем сообщение для того случая, если на интервале корень не единственный:

=ЕСЛИ(С20=0; «Следует разделить интервал пополам»; 0);

ж) в строки 23-29 запишем подсказку для действий при делении интервала пополам: Последовательность действий при делении интервала пополам:

Записать границы двух полученных интервалов.

Ввести в ячейки С5 и С6 границы первого интервала. Записать полученный результат.

Ввести в ячейки С5 и С6 границы второго интервала. Результат записать.

3.2 Отделение действительных корней уравнения (9)

Согласно комментариям в строках 23-29 табл.13, проведем отделение трех действительных корней уравнения (9) на интервалах [-2;-1], [-1;0] и [0;2].

3.3 Отделение корней для уравнения из индивидуального задания

По последней цифре номера зачетной книжки из табл.15 выбрать индивидуальное задание, для выполнения которого ввести в ячейки В8чВ16 табл.13 формулы, получаемые из своего уравнения, а в ячейки С5:С6 - интервал из задания и отделить корни.

Таблица 15.

Номер варианта	Уравнение	Интервал
0	2x3-5x2+4х-9=0	[0;4]
1	Зх3-10х2+2х-7=0	[0;4]
2	Зх3-7х2+2х-5=0	[-1;3]
3	2x3-5х2+5х-12=0	[0;4]
4	5х3-Зх2+4х=0	[0;4]
5	2х3-5х2+5х-12=0	[2;6]
6	2х3-5х2+4х-11=0	[2;6]
7	2x3-7х2+Зх-10=0	[0;4]
8	Зх3-105х2+2х-7=0	[2;6]
9	3x3-2х2+5х-3=0	[-2;2]

Отчет о работе.

Результаты выполнения заданий 1-3 .

IV. Уточнение корней уравнения с использованием режима «Поиск решений»

1. Цель работы

Изучение возможностей Excel в режиме Поиск решений для уточнения корней.

2. Основные теоретические положения

Основы приближенного решения уравнений изложены в [3], стр.4-8. Методика отделения корней с помощью Excel рассмотрена в работе 3.

3. Порядок выполнения работы

Задание 1. Уточнить с погрешностью Е=0,001 все корни уравнения х5-4х-2=0 (отделение корней выполнено в работе 3, задание 2).

Задание 2. Уточнить с погрешностью Е=0,01 все корни уравнения из индивидуального задания. (Отделение корней выполнено в процессе работы 3, задание 3).

3.1 Выполнение задания 1

В работе 3 для уравнения х5-4х-2=0 были отделены три корня на интервалах: [-2;-1], [-1;0] и [0;2].

	А	В
1	Уточнение корней
2	Режим «Поиск решения»
3	Начальное приближение корня	-2
4	Уравнение	=ВЗ^5-4*ВЗ-2
5	Нижняя граница интервала	-2
6	Верхняя граница интервала	-1

Для уточнения корней на первом интервале [-2;-1] составим ЭТ (табл.16).

Запускаем режим «Поиск решения»:

а) выполняем команды Сервис-Поиск Решения;

б) в окне Поиск решения (рис.6) указываем - Установить целевую ячейку В4;

в) выбираем опцию Равной значению и вводим 0;

г) в окне «Изменяя ячейки» вводим $В$3;

д) для ввода ограничений надо щелкнуть по кнопке Добавить;

е) в окне Добавление ограничений (рис.7) введем ограничения : ВЗ>=В5 (щелкнуть по Добавить);

ВЗ<=В6 (щелкнуть по (ОК);

ж) нажать кнопку Выполнить.

Режим показа решения отобразит найденные корни.

Для уточнения корня на интервале [-1:0] вводим:

в ячейку ВЗ значение -1;

в ячейку В5 значение -1;

в ячейку В6 значение 0.

Далее повторить действия п.3.1.2а (установки п.п. 3.1.26 ч 3.1.2ж сохраняются).

Для уточнения корня на интервале [ 0;2 ] вводим:

в ячейку ВЗ значение 0;

в ячейку В5 значение 0;

в ячейку В6 значение 2. Далее повторить действия п.3.1.2а.

3.2 Уточнение корня для уравнения из индивидуального задания

Для корней, отделенных при выполнении индивидуального задания в работе 3, произвести уточнение корней аналогично п.3.1.

Отчет по работе

Оформить результаты выполнения заданий 1-2.



V. Уточнение корней уравнения с использованием режима «Итерации»

1. Цель работы

Изучение возможностей Excel в режиме Итерации для уточнения корней.

2. Основные теоретические положения. 2.1. Метод простой итерации

Уравнение f(x)=0 должно быть преобразовано к виду

х=ц(х), (12)

что является частным случаем уравнения (7).

Поэтому корень уравнения (12) при графической интерпретации соответствует точке пересечения прямой х с кривой ц(х) (см.рис.8).

Сущность метода заключается в следующем.

Задается начальное приближение х(0)(один из концов интервала [а;b] либо точки внутри него) и подставляется в правую часть уравнения (12). В результате получается приближение корня на первой итерации:

х(1)=ц(х(0)). (13)

На второй итерации полученное значение х(1) опять подставляется в правую часть (12), и вычисляется второе приближение

х(2)=ц(х(1)). (14)

Эти соотношения будут справедливы и для всех последущих итераций,

включая и произвольную (к+1)-ю:

х(к+1)=ц(х(к)), к=0,1,2,... (15)

Итерационный процесс завершается, когда будет выполнено условие (11).

Графическая интерпретация метода представлена на рис.8 (сходимость) и на рис.9, (расходимость).

Рис. 9(а) Рис. 9(б)

На рис.9(а) изображен случай монотонной расходимости, на рис.9(б) - колебательной расходимости.

Сходимость или расходимость метода зависит от того, каким образом располагается кривая ц(х) относительно прямой х, т.е. как преобразуется уравнение вида (7) к виду (12).

Например, из уравнения lgx-х2+3=0 можно получить следующие формы уравнения (12):

а) 

б) 

в) 

Предпочтение следует отдавать тому виду функции ц(х), при реализации которой обеспечивается сходимость, по возможности монотонная.

При выборе вида функции ц(х) следует руководствоваться достаточным условием сходимости, которое справедливо для непрерывных функций ц(х), имеющих непрерывную производную. Это условие формулируется следующим образом: итерационный процесс обязательно сходится, если для всех значений х отрезка [а,b] выполняется неравенство

|ц '(х)|<1, х[а,b]. (16)

В соответствии с этим неравенством тангенс угла наклона кривой ц(х) в точках х(к) (рис.8а) не должен превышать величины , равной единице. При этом принято различать три вида сходимости:

|ц'(х)| < 0,1 - хорошая сходимость;

0,1<|ф'(х)|<0,5 - удовлетворительная сходимость;

0,5<|ц'(х)|<1 - плохая сходимость.

Пример 1.

Методом простых итераций решить уравнение lgx-x2+3=0 с точностью е =0,001, принимая в качестве начального приближения х(0)=2,0.

Из рассмотренных выше способов выражения функции ц(х) выберем следующий:

, тогда

Определим значения ц'(х) в различных точках х:

ц'(2,0)=0,06; ц'(1,8)=0,067; ц'(1,6)=0,076.

Здесь ц'(х)<0,1, поэтому есть основание ожидать хорошую сходимость; итерационная формула будет иметь вид

х(k+1)= , к=0,1,...

На первой итерации имеем:

х(0)=2,0; x(1)=; х(1)= 1,817; |х(1)-х(0)|=0,183; 0,183>0,001.

На второй итерации получаем:

x(2)=; |х(2)-х(1)|=|1,8052-1,817|=0,0118; 0,0118>0,001.

На третьей итерации вычисляется следующее значение:

х(3)=; |х(3)-х(2)|=|1,8046-1,8052|=0,0006; 0,006<0,001.

Так как в соответствии с (11) точность достигнута, то корнем уравнения будет являться значение о?1,8046.

Пример 2

Уточнить корни уравнения х5-4х-2=0.

Для этого преобразуем уравнение х5-4х-2=0 к итерационной форме. Очевидно, что возможны по крайней мере два варианта.

Первый вариант.

х=1/4х5-1/2, (17)

т.е. ц(х)=1/4х5-1/2, тогда

ц'(х)=5/4х4 (18)

Для производной функции (18) условие сходимости |ц'(х)|<1 на интервале [-2;2] выполняться не будет. Значит, от этого варианта следует отказаться.

Второй вариант.

х=, (19)

т.е. ц(х)=1/4х5-1/2, тогда ц'(х)=1/5(4х-2)^(1/5-1)·4,

или ц'(х)=4/5(4х-2)-4/5=. (20)

Очевидно, что для производной функции (20) условие сходимости (16) выполняется. Итак, принимаем для данного уравнения итерационную форму (19).

3. Порядок выполнения работы

Задание 1. Методом простых итераций уточнить с точностью е=0,001 корень уравнения х=cos х на интервале [0,1], используя режим Итерации - вручную.

Задание 2. Уточнить корень уравнения из задания 1, используя режим Итерации - автоматически.

Задание 3. Методом простых итераций уточнить корни уравнения (19) на интервалах: [-2,-1]; [-1,0]; [0,2].

3.1 Выполнение задания 1

Решим данное уравнение с использованием итерационного аппарата электронной таблицы Excel. Формулы записываются в электронной таблице в том же виде, что и на бумаге, но для этого можно использовать не всю рабочую страницу, а всего лишь несколько ячеек. Для реализации метода следует отключить автоматический пересчет листа и ввести режим Итерации-вручную формулы с рекурсией (циклическими ссылками).

Как правило, ячейки листа пересчитываются в нормальном порядке. Это означает, что аргументы ячейки ( т.е. те ячейки, от которых зависит ее значение) вычисляются до того, как пересчитывается значение самой ячейки. Это правило применяется ко всем ячейкам листа, пока все они не будут пересчитаны. При изменении значения ячейки пересчитаваются все ячейки, значения которых зависят от нее прямо или косвенно. Рекурсия, или циклическая ссылка, имеет место в том случае, когда значение ячейки зависит от нее самой, как прямо, так и косвенно через значения других ячеек. В нормальном порядке такое значение вычислить нельзя, поскольку нельзя определить аргументы ячейки, в число которых входит она сама - получается, что для определения значения ячейки необходимо сразу знать его заранее!

В итерационном режиме вначале пересчитываются только те ячейки листа, которые не содержат циклических ссылок. Затем ячейки, содержащие циклические ссылки, итерируются один или несколько раз, в зависимости от значения, установленного на вкладке Вычисления диалогового окна Параметры (Сервис - Параметры ). Ячейки, содержащие циклические ссылки, вычисляются с текущими значениями аргументов без их предварительного пересчета. Пересчет прекращается после выполнения указанного в окне Вычисления количества итераций. Чтобы заново пересчитать все ячейки, щелкните по кнопке Вычислить. Этот способ используется для последовательного вычисления значения по заданной формуле и подстановки его в ту же формулу.

В нашем примере установим количество итераций, равное 1, чтобы наблюдать за изменением значений при пересчете листа. На практике для более быстрого нахождения решения лучше выбрать большее количество итераций.

Для выполнения задания 1:

1. Откройте новый лист и назовите его Итерации.

2. Выберите команду Сервис - Параметры. Откройте вкладку Вычисления, включите режим Вручную, сделайте значение поля Предельное число итераций - равным 1 и уберите отметку с переключателя Пересчет перед сохранением. Щелкните по кнопке ОК.

Увеличьте ширину столбца А (в соответствии с табл. 17).

Введите в ячейку А1 текст x=cos(x); последовательные приближения.

Теперь необходимо создать таблицу с начальным значением и флагом инициализации (рис.10). Флаг инициализации переводит лист в заданное начальное состояние (т.е. позволяет пользователю провести выполнение первой итерации).

В ячейки АЗ:В4 ввести приведенные ниже значения (см.табл. 17).

Ячейка Значение Ячейка Значение

A3 Нач.значение ВЗ О

А4 Нач. флаг В4 Истина.

В ячейке В6 будет выполняться проверка, равно ли значению ИСТИНА значение ячейки НАЧ Флаг. Если это так, то х будет установлено равным начальному значению, в противном случае - равным значению ячейки В7, т.е. cos х. В ячейке В7 вычисляется косинус значения, которое находится в ячейке В6, и тем самым организуется циклическая ссылка. Ввести указанные ниже значения в ячейки А6:В7. Ячейка Значение Ячейка Значение

А6 х В6 =Если(В4;ВЗ;В7)

А4 Cos(x) В7 =Cos(B6)

Табл. 17 в режиме показа формул представлена в табл. 18.

Размещено на http://www.allbest.ru/



Рис. 10а

7. Далее следует вычислить погрешность - разницу между х и Cos(x), которая будет служить критерием сходимости процесса последовательных приближений. Для этого:

а) в ячейку А9 надо ввести текст Погрешность;

б) в ячейку В9 ввести =В7-В6.

8. Теперь надо организовать вторую циклическую ссылку - для подсчета количества итераций:

а) в ячейку А11 ввести Итераций.

б) в ячейку B11 ввести =ЕСЛИ(В4;0;В12+1).

в) в ячейку В12 ввести =В11.

Для выполнения расчета нажать клавишу ВЫЧИСЛИТЬ (или F9) для решения задачи.

Изменить значение начального флага (содержимое ячейки В4) на ЛОЖЬ, вновь нажать ВЫЧИСЛИТЬ (F9). При каждом нажатии этой клавиши выполняется одна итерация и вычисляется следующее приближенное значение х.

11.Нажимать клавишу (F9) ВЫЧИСЛИТЬ до тех пор, пока значение х не достигнет необходимой точности. Точность полученного приближенного значения х проверяется путем его сравнения со значением f(x); разность между ними отображается в ячейке В9 (т.е. итерации следует повторять до тех пор, пока содержимое ячейки В9 не станет равным (или меньше 0,001). К этому моменту рабочий лист должен выглядеть так, как указано в табл.19. Значение х, которое является корнем уравнения, указано в ячейке В6.

Если бы итерационый процесс для данной формы уравнения оказался расходящимся, то уравнение следовало бы переписать в эквивалентной форме (для арккосинуса) и попробовать решить заново.

3.2 Выполнение задания 2

Отслеживание значений х и прекращение вычислений после достижения нужной точности может выполняться в Excel автоматически. Для этого изменим наш пример так, чтобы вместо непосредственного наблюдения за изменением приближенных значений х воспользоваться критерием остановки итераций. Для этого сначала надо очистить ячейки А9:В12, так как их значения на каждой итерации изменяются, что не позволит корректно сработать критерию остановки вычислений. Затем выберите количество итераций и критерий остановки на вкладке Вычисления диалогового окна Сервис - Параметры.

	А	В
1	x=cos(x); последовательность приближения
2
3	Нач. значение	0
4	Нач. флаг	ИСТИНА
5
6	X	0
7	cos(x)	1
8
9	погрешность	1
10
11	Итераций	0
12		0
Таблица 18
	А	В
1	x=cos(x); последовательность приближения
2
5	Нач. значение	0
4	Нач. флаг	ИСТИНА
5
6	X	=ЕСЛИ(В4;ВЗ;В7)
7	cos(x)	=COS(B6)
8
9	погрешность	=В7-86
10
11	Итераций	=ЕСЛИ(В4;0;В12+1)
12		В11
Таблица19
	А	В
1	x=cos(x); последовательность приближения
2
j	Нач. значение	0
4	Нач. флаг	ЛОЖЬ
5
6	X	0,739
7	cos(x)	0,739
8
9	погрешность	0,000
10
11	Итераций	22
12		22

Скопируйте лист Итерации и назовите его Итерации 1.

Удалите содержимое ячеек А9:В12 (выделить и нажать Delete).

Выберите команду Сервис - Параметры, откройте вкладку Вычисления и установите значение поля Предельное число итераций, равным 100, а поля Относительная погрешность, равным 1.0Е-3. Включите режим Автоматически в группе Вычисления и щелкните по кнопке ОК (рис. 11).

Инициализируйте вычисления путем ввода значений ИСТИНА в ячейку В4 и пересчета листа.

Запустите процесс последовательных приближений путем ввода в ячейку В4 - ЛОЖЬ. Лист будет пересчитываться до тех пор, пока максимальное относительное изменение значения любой ячейки не станет меньше 1.0* 10-3, как показано в табл.20.

3.3 Выполнение задания 3

Уточнить корни уравнения( 19) аналогично п.3.1.4 и п.3.2. 4. Отчет о работе. Представить результаты выполнения заданий 1-3.

VI. Решение систем уравнений в Excel

1. Цель работы

Научиться решать в Excel системы конечных уравнений с использованием обратных матриц.

2. Основные теоретические положения

Пусть необходимо решить систему уравнений:

2.1 Метод обратной матрицы

С использованием понятия матрицы и матричных операций система уравнений может быть записана в матричном виде:

. (22)

Здесь [А] - матрица коэффициентов системы вида:

где , - вектор неизвестных и вектор свободных членов соответственно. Например, система

в матричном виде записывается аналогично (22) следующим образом:



Решение системы методом обратной матрицы может быть получено в результате умножения правой части системы уравнения (22) на матрицу, обоатную к матоипе коэффициентов системы:

Учитывая, что произведение обратной матрицы на прямую дает единичную матрицу, получаем

Таким образом, решение системы сводится к нахождению обратной матрицы [А-1] и затем вычислению произведения этой матрицы на вектор .

Этот метод удобно применять в тех случаях, когда несколько раз решается система с разными правыми частями. В этом случае достаточно один раз вычислить обратную матрицу [А-1] и затем умножать ее на разные векторы .

Пример.

Методом обратной матрицы решить систему

Решение ищется в виде . Вычислим обратную матрицу [А-1] классическим методом согласно алгоритму:



АТ - транспонированная матрица;

3. Порядок выполнения работы

Задание 2. Решить систему уравнений согласно индивидуального задания.

3.1 Выполнение задания 1

Система уравнений из задания 1 имеет решение x1=1, x2=2, x3=3. В матричной форме уравнения записываются следующим образом:

Для решения системы в Excel нужно:

Создать новый лист и присвоить ему имя «Система».

В ячейке А1 ввести Решение систем уравнений; обращение матрицы

3. В ячейку В3 ввести текст Ах=b . Теперь ввести матрицу коэффициентов А и вектор правой части b, для этого:

а) В ячейку А5 ввести «Исходная матрица (А)».

б) В ячейки А6:С8 ввести элементы матрицы А:

Ячейка Значение Ячейка Значение Ячейка Значение

в) В ячейку Е5 ввести «Правая часть (b)».

г) В ячейки Е6:Е8 ввести компоненты вектора правой части: Ячейка Значение Ячейка Значение Ячейка Значение

Е6 0 Е7 1 Е8 -2

Таблица 21

	А	В	С	D	Е	F	G	Н
1	Решение систем уравнений. Обращение матрицы
2
3		Ах=b
4
5	Исходная матрица А		Правая часть (b)
6	-8	1	2		0
7	5	7	-3		10
8	2	1	-2		-2
9
10	Обратная матрица (1/А)		Вектор решения х=(1/А)/b
11	-0,149	0,054	-0,230		1
12	0,054	0,162	-0,189		2
13	-0,122	0,135	-0,824		3

Далее необходимо обратить матрицу А и умножить вектор на матрицу, обратную к А. Применяемая для обращения матрицы функция МОБР возвращает массив значений, который вставляется сразу в целый столбец ячеек.

4. Для вычисления обратной матрицы:

а) В ячейку А10 ввести текст «Обратная матрица (1/А)».

б) Выделить ячейки Al1 :С13, куда будет помещена обратная матрица.

в) Щелкнуть по пиктограмме Мастер функций f

г) В первом окне Мастера функций выбрать категорию Математические, функцию МОБР.

д) Во втором окне Мастера функций ввести адрес массива исходной матрицы А6:С8. Нажать одновременно клавиши Ctrl+Shift+Enter для вставки этой формулы во все выбранные ячейки А11:С13.

5. Для умножения обратной матрицы на столбец свободных членов:

а) В ячейку Е10 ввести «Вектор решения х=(1/А)b»;

б) выделить ячейки Е11:Е13;

в) щелкнуть по пиктограмме Мастер функций;

г) выбрать категорию Математические, функцию МУМНОЖ;

д) ввести формулу =МУМНОЖ(А11:С13;Е6:Е8);

е) затем нажать «Ctrl+Shift+Enter» для вставки формулы во все выделенные ячейки.

Рабочий лист к этому моменту должен выглядеть так, как показано в табл.21 (режим показа формул - табл.22). В ячейках Е11чЕ13 должны стоят значения компонентов вектора решения х1, х2,х3 (в данном примере это числа (1; 2; 3)).

3.2 Выполнение задания 2

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

Таблица 22

	А	В	С	D	Е
1	Решение систем уравнений. Обращение матрицы
3		Ах=b
4
5	Исходная матрица А		Правая часть (b)
6	-8	1	2		0
7	5	7	-3		10
8	2	1	-2		-2
9
10	Обратная матрица (1/А)		Вектор решения х=(1/А)/b
11	{=МОБР(А6:С8)}	{=МОБР(А6:С8)}	{=МОБР(А6:С8)}		{=МУМНОЖ(А11:С13;Е6:Е8)}
12	{=МОБР(А6:С8)}	{=МОБР(А6:С8)}	{=МОБР(А6:С8)}		{=МУМНОЖ(А11:С13;Е6:Е8)}
13	{=МОБР(А6:С8)}	{=МОБР(А6:С8)}	{=МОБР(А6:С8)}		{=МУМНОЖ(А11:С13;Е6:Е8)}

Таблица 23

Отчет по работе.

Результаты выполнения заданий 1-2.

VII. Приближенное решение систем уравнений

1. Цель работы

Изучение возможностей Excel, режима Итерации для решения систем уравнений.

2. Основные теоретические положения

Пусть необходимо решить систему уравнений:

Рассмотрим решение системы (24) методом простых итераций.

2.1 Метод простых итераций

Представить систему из трех уравнений вида (24) в итерационной форме можно путем записи каждого его уравнения в виде решения относительно одного из неизвестных, например

(25)

или, в матричном виде.

(26)

Элементы матрицы [С] и вектора вычисляют по формулам

cij=-аij/аii, di = bi/aii, i,j=1,2,3.

* При использовании итерационного метода решения необходимо оценить сходимость метода для данной системы, которая зависит только от матрицы коэффициентов [С]. Процесс сходится в том случае, если норма матрицы [С] меньше единицы, т.е.

(27)

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