4.1. Поиск решений (Solver)
Мощным
средством анализа данных Excel
является надстройка Solver
(Поиск решения).
С ее помощью можно определить, при каких
значениях у
казанных
влияющих ячеек формула в целевой ячейке
принимает нужное значение (минимальное,
максимальное или равное какой-либо
величине). Для процедуры поиска решения
можно задать ограничения, причем не
обязательно, чтобы при этом использовались
те же влияющие ячейки. Для расчета
заданного значения применяются различные
математические методы поиска. Вы можете
установить режим, в котором полученные
значения переменных автоматически
заносятся в таблицу. Кроме того, результаты
работы программы могут быть оформлены
в виде отчета.
Программа Поиск решений
(в оригинале Excel Solver) – дополнительная
надстройка табличного процессора MS
Excel, которая предназначена для решения
определенных систем уравнений, линейных
та нелинейных задач оптимизации,
используется с 1991 года.
Размер задачи,
которую можно решить с помощью базовой
версии этой программы, ограничивается
такими предельными показателями:
количество неизвестных (decision variable) – 200;
количество формульных ограничений (explicit constraint) на неизвестные – 100;
количество предельных условий (simple constraint) на неизвестные – 400.
Разработчик
программы Solver компания Frontline System уже
давно специализируется на разработке
мощных и удобных способов оптимизации,
встроенных в среду популярных табличных
процессоров разнообразных фирм-производителей
(MS Excel Solver, Adobe Quattro Pro, Lotus 1-2-3).
Высокая
эффективность их применения объясняется
интеграциею п
рограммы
оптимизации и табличного бизнес-документа.
Благодаря мировой популярности табличного
процессора MS Excel встроенная в его
среду программа Solver есть наиболее
распространенным инструментом для
поиска оптимальных решений в сфере
современного бизнеса.
По умолчанию
в Excel надстройка Поиск решения отключена.
Чтобы активизировать ее в Excel
2007, щелкните
значок Кнопка
Microsoft Office
, щелкните Параметры
Excel, а
затем выберите категорию Надстройки.
В поле Управление
выберите значение Надстройки
Excel и
нажмите кнопку Перейти.
В поле Доступные
надстройки
установите флажок рядом с пунктом Поиск
решения
и нажмите кнопку ОК.
В Excel 2003 и ниже выберите команду Сервис/Надстройки, в появившемся диалоговом окне Надстройки установите флажок Поиск решения и щелкните на кнопке ОК. Если вслед за этим на экране появится диалоговое окно с предложением подтвердить ваши намерения, щелкните на кнопке Да.
4.1.1. Процедура поиска решения
1. Создайте таблицу с формулами, которые устанавливают связи между ячейками.
2. Выделите целевую ячейку, которая должна принять необходимое значение, и выберите команду:
В Excel 2007 Данные/Анализ/Поиск решения;
В Excel 2003 и ниже Tools > Solver (Сервис > Поиск решения). Поле Set Target Cell (Установить целевую ячейку) открывшегося диалогового окна надстройки Solver (Поиск решения) будет содержать адрес целевой ячейки.
3. Установите переключатели Equal To (Равной), задающие значение целевой ячейки, — Мах (максимальному значению), Min (минимальному значению) или Value of (значению). В последнем случае введите значение в поле справа.
4. Укажите в поле By Changing Cells (Изменяя ячейки), в каких ячейках программа должна изменять значения в поисках оптимального результата. 5. Создайте ограничения в списке Subject to the Constraints (Ограничения). Для этого щелкните на кнопке Add (Добавить) и в диалоговом окне Add Constraint (Добавление ограничения) определите ограничение.
6. Щелкните кнопку Options (Параметры), и в появившемся окне установите переключатель Неотрицательные значения (если переменные должны быть позитивными числами), Линейная модель (если задача, которую вы решаете, относится к линейным моделям)
7. Щелкнув на кнопке Solver (Выполнить), запустите процесс поиска решения.
8. Когда появится
диалоговое окно Solver Results (Результаты
поиска решения), выберите переключатель
Keep Solve Solution (Сохранить найденное решение)
или Restore Original Values (Восстановить исходные
значения).
9. Щелкните на кнопке ОК.