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

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

Если в ячейку 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 запустит вычисления с новым начальным приближением.

Часть 2. Подбор параметра

Когда желаемый результат вычислений по формуле известен, но неизвестны значения, необходимые для получения этого результата, можно воспользоваться средством Подбор параметра, выбрав вкладку Данные/Анализ что-если/Подбор параметра.

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

Возьмем в качестве примера то же квадратное уравнение х²-5х+6=0. Для нахождения корней уравнения выполним следующие действия:

· В ячейку B2 (рис. 10) введем формулу для вычисления значения функции,

Рис. 10. Окно диалога Подбор параметра

· стоящей в уравнении слева от знака равенства. В качестве аргумента используем ссылку на ячейку B1, т.е. =B1^2-5*B1+6. В B1 введем значение 0.

· В окне диалога Подбор параметра (рис. 10) в поле Установить в ячейке введем ссылку на ячейку с формулой, в поле Значение - ожидаемый результат, в поле Изменяя значения ячейки - ссылку на ячейку, в которой будет храниться значение подбираемого параметра (содержимое этой ячейки не может быть формулой).

· После нажатия на кнопку Ok Excel выведет окно диалога Результат подбора параметра. Если подобранное значение необходимо сохранить, то нажмите на Оk, и результат будет сохранен в ячейке, заданной ранее в поле Изменяя значения ячейки. Для восстановления значения, которое было в ячейке B2 до использования команды Подбор параметра, нажмите кнопку Отмена.

При подборе параметра Excel использует итерационный (циклический) процесс. Количество итераций и точность устанавливаются в меню Сервис/Параметры/вкладка Вычисления. Если Excel выполняет сложную задачу подбора параметра, можно нажать кнопку Пауза в окне диалога Результат подбора параметра и прервать вычисление, а затем нажать кнопку Шаг, чтобы выполнить очередную итерацию и просмотреть результат. При решении задачи в пошаговом режиме появляется кнопка Продолжить - для возврата в обычный режим подбора параметра.

Вернемся к примеру. Опять возникает вопрос: как получить второй корень? Как и в предыдущем случае необходимо задать начальное приближение. Для того чтобы найти второй корень, достаточно в качестве начального приближения (рис. 11) в ячейку B1поместить константу 5 и после этого запустить процесс Подбор параметра.

Рис. 11. Поиск второго корня