26
b1 = b1 - (f(b1) / (f(b1) - f(a1))) * (b1 - a1) a1 = a1 - f(a1) / f1(a1)
End If
Loop Until b1 - a1 < e
x = (a1 + b1) / 2
MsgBox x, , " Корень уравнения на отрезке [" & a & ", " & b & "]" Else
MsgBox "Метод не применим" End If
End Sub
1.2.9Уточнение корней уравнений в Excel с помощью циклической ссылки
При организации циклической ссылки точность вычислений зависит от числа выполненных итераций. Это число определяет пользователь, руководствуясь формулами оценки точности вычислений приближенного значения корня, в зависимости от применяемого метода, например, в методе половинного деления число итераций, определяемого формулой (6), при этом погрешность вычислений определяется по формуле (5).
Вычислительный процесс должен быть запущен после набора всех команд, реализующих алгоритм. В связи с этим предлагается следующая схема действий:
1.В меню Сервис выбрать команду Параметры. В появившемся окне Параметры выбрать вкладку Вычисления, на которой снять флажок Итерации; в поле Предельное число итераций ввести число, обеспечивающее точность вычислений.
2.Выполнить ввод команд, реализующих алгоритм.
3.Выполнить команду Сервис/Параметры. На вкладке Вычисления окна диалога Параметры установить флажок Итерации и нажать кнопку Ок. Выполняется
расчет.
Замечания:
1.Если Excel регистрирует циклическую ссылку, следует в появившемся окне нажать кнопку Отмена.
2.Если повторный расчет требует восстановления исходного состояния, то следует на вкладке Вычисления снять флажок Итерации, а затем выполнить двой-
ной щелчок мышкой по соответствующей ячейке. Ячейка открывается для корректировки, формулу не корректируем, а нажимаем на клавишу Enter.
3. Расчеты с использованием циклической ссылки могут быть выполнены пошагово:
1) В меню Сервис выбираем команду Параметры. В появившемся окне Параметры выбираем вкладку Вычисления, на которой устанавливаем флажок Итерации, в поле Предельное число итераций вводим число 1 и нажимаем кнопку Ok. Выполняется одна итерация.
2) При каждом нажатии клавиши F9 будет выполняться одна итерация. Рассмотрим решение примера средствами Excel.
27
1.2.9.1Метод половинного деления
Втаблице Excel выполним следующие действия.
1.В ячейку D4 вводим значение a = –1 – начало интервала изоляции нуля функции f(x). В ячейку E4 вводим значение b = 0 – конец интервала изоляции нуля функции f(x).
2.В ячейку G4 вводим константу ε = 10–7, определяющую точность вычисле-
ний.
3.В ячейку H4 вводим формулу (5), определяющую число итераций для достижения заданной точности:
=ОКРУГЛ((LN($E$4-$D$4)-LN($G$4))/LN(2);0).
На вкладке Вычисления в поле Предельное число итераций вводим полученное число.
4. В ячейку G9 вводим формулу (4), определяющую достигнутую точность вычислений:
=($E$4-$D$4)/2^($H$7+1).
6. В ячейку H7 вводим формулу, определяющую число выполненных итера-
ций
=$H$7+1.
7. Распределяем ячейки и вводим соответствующие формулы, реализующие метод половинного деления. Данные см. в таблице.
Имя ячейки |
Обозначе- |
Формула |
Комментарий |
|
ние |
|
|
E15 |
an |
=ЕСЛИ($H$7=1;D4;E21) |
Установить начало интер- |
|
|
|
вала изоляции корня функ- |
|
|
|
ции f(x) |
E16 |
bn |
=ЕСЛИ($H$7=1;E4;E22) |
Установить конец интерва- |
|
|
|
ла изоляции корня функ- |
|
|
|
ции f(x) |
E17 |
x |
=(E15+E16)/2 |
Вычислить срединную точ- |
|
|
|
ку интервала – приближен- |
|
|
|
ное значение нуля функции |
|
|
|
f(x) |
E18 |
f(x) |
= 5^E17-6*E17-3 |
Вычислить значение функ- |
|
|
|
ции f(x) в точке x. |
E19 |
sign f(x) |
=ЕСЛИ(E18>0;1;-1) |
Определить знак функции |
|
|
|
f(x) в точке x |
E20 |
sign f(a) |
=ЕСЛИ(5^E15-6*E15-3>0;1;-1) |
Определить знак функции |
|
|
|
f(x) в точке x = a |
E21 |
an+1 |
=ЕСЛИ(E20=E19;E17;E15) |
Вычислить новое значение |
|
|
|
начало интервала изоляции |
|
|
|
нуля функции f(x) |
E22 |
bn+1 |
=ЕСЛИ(E20=E19;E16;E17) |
Вычислить новое значение |
|
|
|
конца интервала изоляции |
|
|
|
нуля функции f(x) |
8. Выполняем расчет по алгоритму п. 1.2.9.
28
1.2.9.2 Метод итераций
Метод итераций реализуется следующим алгоритмом.
1. Выполните распределение ячеек и запись расчетных формул:
– |
диапазон A5:A15 – значения аргумента: –1 ≤ x ≤ 0; x = 0,1; |
–диапазон B5:B15 – значения производной f '(x). В ячейку B5 введите формулу =LN(5)*5^A5-6 и распространите ее на указанный диапазон;
–С5 – максимальное значение модуля производной max |f '(x)|. Введите формулу =ЕСЛИ(B5<0;ABS(МИН(B5:B15));МАКС(B5:B15));
–D5 – параметр λ = sgn f '(η) M2 . Введите формулу =ЗНАК(B5)*2/C5;
–E5 – предыдущее приближение x0. Введите формулу =ЕСЛИ(G5=0;A5;F5). Если число итераций G5=0, то значение для x0 устанавливается равным началу отрезка отделения корня, иначе – значению, вычисленному при предыдущей итерации;
–F5 – последующее приближение x1. Введите формулу =E5-$D$5*(5^E5- 6*E5-3);
–G5 – счетчик числа итераций. Введите формулу =G5+1;
–H5 – приближенное значение корня x. Введите формулу =(E5+F5)/2;
– I5 – значение функции в точке x. Введите формулу =5^H5-6*H5-3.
2.Выполните расчет.
3.Конец алгоритма.
1.2.9.3Метод Ньютона
Втаблице Excel выполним следующие действия.
1.Выполните распределение ячеек и запись расчетных формул:
− |
диапазон A5:A15 – значения аргумента: –1 ≤ x ≤ 0; x = 0,1; |
−диапазон B5:B15 – значения производной f '(x). В ячейку B5 введите формулу =LN(5)*5^A5-6 и распространите ее на указанный диапазон;
−диапазон C5:C15 – значения второй производной f ''(x). В ячейку C5 введите формулу =LN(5)*5^A5*LN(5) и распространите ее на указанный диапазон;
−D5 – максимальное значение модуля второй производной max |f ''(x)|. Введите формулу =ЕСЛИ(C5<0;ABS(МИН(C5:C15));МАКС(C5:C15));
−E5 – минимальное значение модуля производной min |f '(x)|. Введите формулу=ЕСЛИ(B5<0;ABS(МАКС(B5:B15));МИН(B5:B15));
−F5 – предыдущее приближение x0. Введите формулу
=ЕСЛИ(H5=0;ЕСЛИ(ЗНАК(B5)=ЗНАК(C5);A15;A5);G5). {Если число итераций
H5=0, то значение для x0 устанавливается равным началу отрезка отделения корня, если не совпадают знаки первой и второй производной функции и концу отрезка отделения корня, если эти знаки совпадают, иначе – значению, вычисленному при предыдущей итерации};
−G5 – последующее приближение x1. Введите формулу
=F5-(5^F5-6*F5-3)/(LN(5)*5^F5-6);
−H5 – счетчик числа выполненных итераций. Введите формулу =H5+1;
29
−I5 – значение функции в точке x. Введите формулу =5^G5-6*G5-3;
−J5 – значение d. Введите формулу =(G5-F5)^2;
−K5 – погрешность вычислений. Введите формулу =2*E5*0,0000001/D5.
2.Выполните расчет.
3.Конец алгоритма.
1.2.9.4Метод хорд
Втаблице Excel выполним следующие действия.
1.Выполните распределение ячеек и запись расчетных формул:
− |
диапазон A5:A15 – значения аргумента: –1 ≤ x ≤ 0; x = 0,1; |
−диапазон B5:B15 – значения производной f '(x). В ячейку B5 введите формулу =LN(5)*5^A5-6 и распространите ее на указанный диапазон;
−диапазон C5:C15 – значения второй производной f ''(x). В ячейку C5 введите формулу =LN(5)*5^A5*LN(5) и распространите ее на указанный диапазон;
−D5 – максимальное значение модуля второй производной max |f ''(x)|. Введите формулу =ЕСЛИ(C5<0;ABS(МИН(C5:C15));МАКС(C5:C15));
−E5 – минимальное значение модуля производной min |f '(x)|. Введите формулу=ЕСЛИ(B5<0;ABS(МАКС(B5:B15));МИН(B5:B15));
−F5 – определить вариант v расчета. Введите формулу =ЕСЛИ(ЗНАК(B5)=ЗНАК(C5);1;2). Если совпадают знаки первой и второй произ-
водной функции f(x), то принимаем v =1 – расчет приблизительного значения корня с недостатком, иначе v = 2 – расчет приблизительного значения корня с избытком.
−G5 –начало интервала изоляции корня, a. Введите формулу
=ЕСЛИ(J5=0;A5;ЕСЛИ(F5=1; I5;G5)).
Если число итераций J5=0, то значение для a устанавливается равным началу отрезка отделения корня, иначе, если вариант расчета v = 1 – значению x, в других случаях значение a не изменяется;
−H5 – конец интервала изоляции корня, b. Введите формулу
=ЕСЛИ(J5=0;A15;ЕСЛИ(F5=2; I5;H5)).
Если число итераций J5=0, то значение для a устанавливается равным началу отрезка отделения корня, иначе, если вариант расчета v = 2 – значению x, в других случаях значение b не изменяется;
−I5 – приближенное значение корня. Введите формулу
=G5-(5^G5-6*G5-3)*(H5-G5)/(5^H5-6*H5-3-(5^G5-6*G5-3));
−J5 – счетчик числа выполненных итераций. Введите формулу =J5+1;
−K5 – значение функции в точке x. Введите формулу =5^I5-6*I5-3;
−L5 – значение d. Введите формулу =ЕСЛИ(F5=1;I5-G5;H5-I5);
−M5 – погрешность вычислений. Введите формулу
=E5*0,0000001/ABS((D5-E5)).
2.Выполните расчет.
3.Конец алгоритма.