Рис П2.20 Результат выполнения итераций
У описанного метода есть еще одно очень серьезное ограничение: так как в качестве начального приближения в нем всегда используется ноль, он непригоден для нахождения нескольких корней уравнения. Для решения этой проблемы программа Excel предоставляет другой метод – надстройку Поиск решения.
Замечание Если были выполнены все описываемые действия в программе Excel, то лучше будет вернуть измененные настройки в исходное состояние. Выберите команду Сервис Параметры..., в диалоговом окне Параметры перейдите на вкладку Вычисления и снимите флажок итерации.
Введение в надстройку Поиск решения
Внадстройке Поиск решения используется код нелинейной оптимизации Generalized Reduced Gradient (GRG2), разработанный сотрудником университета города Остин (штат Техас) Леоном Ласдоном и сотрудником Кливлендского государственного университета Алланом Вареном. Эта надстройка представляет собой универсальный, снабженный многими приложениями инструмент для решения задач.
Вкачестве альтернативы итерациям в ячейке в программе Excel используется надстройка под названием Поиск решения. Эта надстройка не устанавливается по умолчанию. Даже если она установлена, по умолчанию эта надстройка не активируется. Таким образом, перед использованием надстройки Поиск решения ее, скорее всего, придется установить (с помощью установочного ком- пакт-диска) и активировать (это можно сделать во время работы программы
Excel).
Если в меню Сервис отображается команда Поиск решения..., это означает, что нужная надстройка установлена, активирована и готова к применению.
Впротивном случае эта надстройка либо не установлена, либо не активирована.
Втакой ситуации вначале необходимо ее активировать, так как это можно сделать непосредственно из Excel.
Активирование надстройки Поиск решения
Выберите в меню Сервис команду Надстройки... (рис. П2.21), а затем в окне со списком Надстройки (рис. П2.22) установите флажок Поиск решения. Чтобы активировать эту надстройку, щелкните на кнопке ОК. Если надстройка Поиск решения не отображается в окне Надстройки, значит она не установлена.
78
Рис. П2.21 Выполнение команды
Сервис Надстройки...
Рис. П2.22 Список доступных надстроек
Установка надстройки Поиск решения
Пользуясь установочным компакт-диском, запустите программу установки Excel (или Microsoft Office) и выберите команду Добавить или Удалить компоненты. В диалоговом окне Microsoft Office 2000: обновление компонентов раскройте пункт Microsoft Excel для Windows (для этого нужно щелкнуть на маленьком квадрате со знаком +, расположенном справа), затем раскройте пункт Надстройки и в раскрывающемся меню Поиск решения выберите команду Запускать с моего компьютера (или другую, в зависимости от конфигурации аппаратного обеспечения).
79
Применение надстройки Поиск решения
После установки и активации надстройки Поиск решения можно приступать к ее использованию. Для этого нужно, чтобы уравнение (П2.1)было записано в разновидности формы 1: в правой части уравнения не обязательно должен стоять ноль, там может быть любая константа.
Перед запуском надстройки Поиск решения введем в одну из ячеек электронной таблицы начальное приближение, а во вторую – рассматриваемое уравнение (фактически, его левую часть) (рис. П2.23).
Вячейку В4 таблицы на рис. П2.23 введено начальное приближение (3,6),
ав ячейку В5 – формула = В4 ^ 3 − 12* B4 + 4 . Программа Excel вычисляет значение формулы, используя при этом начальное приближение, но не выполняет никаких итераций, пока не будет применена надстройка Поиск решения.
Рис. П2.23 Подготовка к применению надстройки Поиск реше-
ния
Запустите надстройку Поиск решения, выбрав команду Сервис Поиск решения.... При этом на экране появится диалоговое окно Поиск решения (рис.
П2.24).
Рис. П2.24 Диалоговое окно
Поиск решения
Выбрав указанные на рис. П2.24 параметры, была создана замкнутая цепь ссылок. В поле Установить целевую ячейку в качестве целевой ячейки вы-
брана ячейка В5, содержащая формулу. Если адрес ячейки в это поле вводится не вручную, а с помощью кнопки, расположенной справа и позволяющей указать нужную ячейку (или диапазон ячеек) прямо в электронной таблице с помощью мыши, то в этот адрес автоматически добавляются знаки доллара, пре-
80
вращающие данный адрес в абсолютный. Это не представляет особой помехи, однако при решении простых задач излишне.
Параметры заданы таким образом, что надстройка Поиск решений будет продолжать поиск решения, пока значение формулы в целевой ячейке не будет равно нулю (так как в правой части уравнения стоит ноль). Для этого в окне Поиск решения переключатель Равной: был установлен в положение значению:, а в соответствующем поле введено значение 0.
В поле Изменяя ячейки: вводится адрес ячейки, в которой находится предполагаемое значение. В процессе решения задачи значение, хранящееся в этой ячейке, будет меняться.
Замечание Надстройку Поиск решения можно использовать для решения уравнений, а также для поиска значений локальных и глобальных максимумов и минимумов.
Завершив ввод необходимых параметров в диалоговом окне Поиск решения, щелкните на кнопке Выполнить. После выполнения итераций программа Excel отобразит диалоговое окно Результаты поиска решения, позволяющее задавать способ обработки результатов (рис. П2.25).
Рис. П2.25 Диалоговое ок-
но Результа-
ты поиска решения
В ячейке ВЗ находится последнее предполагаемое значение (рис. П2.26), и теперь есть возможность оставить его там или возвратить таблицу в исходное состояние. Кроме того, программа Excel подготовила краткие отчеты, которые можно разместить на отдельных листах. В этих отчетах описывается характер полученного решения, его устойчивость и пределы (для задач с ограничениями). Чтобы вернуться в электронную таблицу и ознакомиться с результатами, щелкните на кнопке ОК (рис. П2.26).
Рис П2.26 Корень уравнения, найденный с использованием над-
стройки Поиск решения
С помощью надстройки Поиск решения найден корень, равный 3,28357 . Если это значение подставить в формулу, получим величину, близкую к нулю
( −1 10−7 ), но не равную ему. Чтобы найти другие корни, следует изменить предполагаемое значение и повторно запустить надстройку Поиск решения.
81