9.5. Поиск решения

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

В основе надстройки также лежат итерационные методы, но ее возможности гораздо больше, нежели у команды Подбор параметра. Надстройка Поиск решения позволяет

использовать одновременно большое количество изменяемых ячеек;

задавать ограничения для изменяемых ячеек;

получать не заранее известный результат в какой-либо ячейке, а найти оптимальное (минимальное или максимальное), т. е. наилучшее из возможных, решение;

для сложных задач генерировать множество различных решений и сохранять эти решения (с этим вопросом можно ознакомиться по справке Excel).

Целевая ячейка, в которой формируется нужный результат, единственная.

Рассмотрим некоторые примеры.

Решим систему линейных алгебраических уравнений (СЛАУ) 2-го порядка:

Для этого выполните следующие действия:

1) в первых двух строчках электронной таблицы запишите формулировку задачи;

2) увеличьте ширину четвертой строки;

3) с помощью редактора формул наберите систему уравнений;

4) в ячейки А6 и А7 введите произвольные начальные значения х и у. В данном примере выбраны нулевые значения;

5) в ячейку В6 введите формулу «=2*А6 + 3*А7», в В7 – «= А6 + 2*А7». Экран в режиме отображения формул приобретет вид, изображенный на рис. 17;

6) выполните команду Сервис, Поиск решения. Появится диалоговое окно этой команды (рис. 18). В поле Установить целевую ячейку введите $B$6, в поле Равной значению – число 8, в поле Изменяя ячейки – $A$6:$A$7;

7) используйте второе уравнение системы в качестве ограничения. Для этого нажмите кнопку Добавить диалогового окнаПоиск решения. Появится диалоговое окноДобавление ограничения(рис. 19). Заполните его поля;

8) нажмите клавишу ОК. Вернется диалоговое окно Поиск решения, в поле Ограничения которого будет запись «$B$7=5». Нажмите кнопку Выполнить. В появившемся диалоговом окне Результаты поиска решения выберите Сохранить найденное решение и нажмите ОК. Результат решения приведен на рис. 20.

Рис. 17. Решение СЛАУ (режим отображения формул)

В качестве другого примера рассмотрим задачу: найти минимальное значение функции z = – 3x– 4y, если ограничения на значенияхиy заданы системой:

Эта задача относится к задачам линейного программирования. Она легко может быть решена в Excel. ЭТ (электронная таблица) с исходными данными для решения этой задачи (режим отображения формул) показана на рис. 21. Начальные значения х и у (ячейки А4 и А5) выбраны произвольно. Как надо заполнить поля диалогового окна Поиск решения, показано на рис. 22. Результат вычислений: ЭТ в режиме отображения значений приведена на рис. 23.

З а м е ч а н и е. Решение задачи линейного программирования будет найдено быстрее, если в диалоговом окне Поиск решения нажать кнопку Параметры и установить флажок Линейная модель.

Рис. 20. Решение СЛАУ