Часть 1. Циклические ссылки

Лабораторная работа №9

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

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

Рассмотрим задачу нахождения корня уравнения методом Ньютона с использованием циклических ссылок. Возьмем для примера квадратное уравнение: х²-5х+6=0, графическое представление которого приведено на рис. 8. Найти корень уравнения можно, используя всего одну ячейку Excel.

Рис. 8. График функции

Для включения режима циклических вычислений нажмите кнопку Office и меню Параметры Excel / вкладка Формулы - включаем флажок Включить итеративные вычисления, при необходимости изменяем число повторений цикла в поле Предельное число итераций и точность вычислений в поле Относительная погрешность (по умолчанию их значения равны 100 и 0,0001 соответственно). Кроме этих установок выбираем вариант ведения вычислений: автоматически или вручную. При автоматическом вычислении Excel выдает сразу конечный результат, при вычислениях, производимых вручную, можно наблюдать результат каждой итерации.

В OpenOffice Calc: меню Сервис / Параметры

Далее, выберем произвольную ячейку, присвоим ей имя, скажем - Х, и введем в нее рекуррентную формулу, задающую вычисления по методу Ньютона:

,

где F и F1 задают соответственно выражения для вычисления значений функции и ее первой производной. Для нашего квадратного уравнения после ввода формулы в ячейке появится значение 2, соответствующее одному из корней уравнения (рис. 8). В нашем случае начальное приближение не задавалось, итерационный вычислительный процесс начинался со значения, по умолчанию хранимого в ячейке Х и равного нулю. А как получить второй корень? Обычно это можно сделать изменением начального приближения. Решать проблему задания начальных установок в каждом случае можно по-разному. Мы продемонстрируем один прием, основанный на использовании функции ЕСЛИ. С целью повышения наглядности вычислений ячейкам были присвоены содержательные имена (рис. 9).

· В ячейку Хнач (A2) заносим начальное приближение: 5.

· В ячейку Хтекущ (B2) записываем формулу (формула ссылается сама на себя):
=ЕСЛИ(Хтекущ=0;Хнач; Хтекущ-(Хтекущ^2-5*Хтекущ+6)/(2*Хтекущ-5)).

Или нажимаем кнопку вставки функции , выбираем функцию ЕСЛИ () и вводим формулу по частям (в OpenOffice Calc условная функция называется IF ()):

· В этой формуле: если ячейка Хтекущпустая (Хтекущ=0) то в нее помещается Хнач; после этого вычисляется итерационная формула Хтекущ- F(Хтекущ)/F1(Хтекущ)– то есть вычитается отношение исходной функции ( Xтекущ^2-5*Xтекущ+6 ) к ее первой производной (2*Хтекущ-5).

· В ячейку C2 помещаем формулу, задающую вычисление значения функции в точке Хтекущ, что позволит следить за процессом решения (=Xтекущ^2-5*Xтекущ+6).

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

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

Рис. 9. Определение начальных установок

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