Книга: Решение уравнений средствами Excel
2.1. Циклические ссылки
2.2. Подбор параметра
2.3. Поиск решения
2.1. Циклические ссылки
Если в ячейку Excel введена формула, содержащая ссылку на эту же самую ячейку (может быть и не напрямую, а опосредованно - через цепочку других ссылок), то говорят, что имеет место циклическая ссылка (цикл). На практике к циклическим ссылкам прибегают, когда речь идет о реализации итерационного процесса, вычислениях по рекуррентным соотношениям. В обычном режиме Excel обнаруживает цикл и выдает сообщение о возникшей ситуации, требуя ее устранения. Excel не может провести вычисления, так как циклические ссылки порождают бесконечное количество вычислений. Есть два выхода из этой ситуации: устранить циклические ссылки или допустить вычисления по формулам с циклическими ссылками (в последнем случае число повторений цикла должно быть конечным).
Рассмотрим задачу нахождения корня уравнения методом Ньютона с использованием циклических ссылок. Возьмем для примера квадратное уравнение: х2 - 5х + 6=0, графическое представление которого приведено на рис. 8. Найти корень этого (и любого другого) уравнения можно, используя всего одну ячейку Excel.
Для включения режима циклических вычислений в меню Сервис/Параметры/вкладка Вычисления включаем флажок Итерации, при необходимости изменяем число повторений цикла в поле Предельное число итераций и точность вычислений в поле Относительная погрешность (по умолчанию их значения равны 100 и 0,0001 соответственно). Кроме этих установок выбираем вариант ведения вычислений: автоматически или вручную. При автоматическом вычислении Excel выдает сразу конечный результат, при вычислениях, производимых вручную, можно наблюдать результат каждой итерации.
|
|
|
Рис. 8. График функции |
Выберем произвольную ячейку, присвоим ей новое имя, скажем, Х, и введем в нее рекуррентную формулу, задающую вычисления по методу Ньютона:
,
где F и F1 задают соответственно выражения для вычисления значений функции и ее производной. Для нашего квадратного уравнения после ввода формулы в ячейке появится значение 2, соответствующее одному из корней уравнения (рис. 8). В нашем случае начальное приближение не задавалось, итерационный вычислительный процесс начинался со значения, по умолчанию хранимого в ячейке Х и равного нулю. А как получить второй корень? Обычно это можно сделать изменением начального приближения. Решать проблему задания начальных установок в каждом случае можно по-разному. Мы продемонстрируем один прием, основанный на использовании функции ЕСЛИ. С целью повышения наглядности вычислений ячейкам были присвоены содержательные имена (рис. 9).
В ячейку Хнач (В4) заносим начальное приближение - 5.
· В ячейку Хтекущ (С4) записываем формулу: =ЕСЛИ(Хтекущ=0;Хнач; Хтекущ-(Хтекущ^2-5*Хтекущ+6)/(2*Хтекущ-5)).
· В ячейку D4 помещаем формулу, задающую вычисление значения функции в точке Хтекущ, что позволит следить за процессом решения.
· Заметьте, что на первом шаге вычислений в ячейку Хтекущ будет помещено начальное значение, а затем уже начнется счет по формуле на последующих шагах.
· Чтобы сменить начальное приближение, недостаточно изменить содержимое ячейки Хнач и запустить процесс вычислений. В этом случае вычисления будут продолжены, начиная с последнего вычисленного
|
|
|
Рис. 9. Определение начальных установок |
значения. Чтобы обнулить значение, хранящееся в ячейке Хтекущ, нужно заново записать туда формулу. Для этого достаточно для редактирования выбрать ячейку, содержащую формулу, дважды щелкнув мышью на ней (при этом содержимое ячейки отобразится в строке формул). Щелчок по кнопке (нажатие клавиши) Enter запустит вычисления с новым начальным приближением.
2.2. Подбор параметра
Когда желаемый результат вычислений по формуле известен, но неизвестны значения, необходимые для получения этого результата, можно воспользоваться средством Подбор параметра, выбрав команду Подбор параметра в меню Сервис. При подборе параметра Excel изменяет значение в одной конкретной ячейке до тех пор, пока вычисления по формуле, ссылающейся на эту ячейку, не дадут нужного результата.
Возьмем в качестве примера все то же квадратное уравнение х2 -5х+6=0. Для нахождения корней уравнения выполним следующие действия:
В ячейку С3 (рис. 10) введем формулу для вычисления значения функции, стоящей в уравнении слева от знака равенства. В качестве аргумента используем ссылку на ячейку С2, т.е. =С2^2-5*C2+6.
|
|
|
Рис. 10. Окно диалога Подбор параметра |
· В окне диалога Подбор параметра (рис. 10) в поле Установить в ячейке введем ссылку на ячейку с формулой, в поле Значение - ожидаемый результат, в поле Изменяя значения ячейки - ссылку на ячейку, в которой будет храниться значение подбираемого параметра (содержимое этой ячейки не может быть формулой).
· После нажатия на кнопку Ok Excel выведет окно диалога Результат подбора параметра. Если подобранное значение необходимо сохранить, то нажмите на Оk, и результат будет сохранен в ячейке, заданной ранее в поле Изменяя значения ячейки. Для восстановления значения, которое было в ячейке С2 до использования команды Подбор параметра, нажмите кнопку Отмена.
При подборе параметра Excel использует итерационный (циклический) процесс. Количество итераций и точность устанавливаются в меню Сервис/Параметры/вкладка Вычисления. Если Excel выполняет сложную задачу подбора параметра, можно нажать кнопку Пауза в окне диалога Результат подбора параметра и прервать вычисление, а затем нажать кнопку Шаг, чтобы выполнить очередную итерацию и просмотреть результат. При решении задачи в пошаговом режиме появляется кнопка Продолжить - для возврата в обычный режим подбора параметра.
Вернемся к примеру. Опять возникает вопрос: как получить второй корень? Как и в предыдущем случае необходимо задать начальное приближение. Это можно сделать следующим образом (рис. 11,а):
|
--> ЧИТАТЬ ПОЛНОСТЬЮ <-- К-во Просмотров: 316
Бесплатно скачать Книга: Решение уравнений средствами Excel
|