Excel для математиков

Ограничения целочисленности, как я уже говорил, накладываются на переменные довольно просто. Для этого, задавая ограничения в левой его части, нужно указать диапазон регулируемых ячеек, а отношение, связывающее левую и правую часть ограничения, задать как int. Как видите, в правой части появляется слово integer - признак целочисленности переменных. Вот как изменялись установки в окне Параметры:

Рис. 2.26. Изменение установок окна Параметры

В этом окне включен флажок линейности модели, положительности переменных, автоматический выбор масштаба, а также уменьшено до 1% значение параметра Tolerance. Решатель находит наилучшее решение - 37 малых барабанов и 6 больших. Результаты можно увидеть на рисунке:

увеличить изображение
Рис. 2.27. Успешное решение и уведомление о "неуспехе"

Однако заметьте, Решатель, получив правильное решение, вместе с тем уведомляет о неуспехе. Причина этого, если вспомнить то, что я говорил о настройках параметров, понятна. Найденное решение отличается от оптимального, нецелочисленного решения более чем на 1%, - отсюда и неуспех. Какие уроки можно извлечь из этой истории? Оптимизационные задачи сложны, сложен и сам инструментарий, так что не следует доверять первому полученному решению. Всегда следует пытаться решить эту же задачу в другой постановке. Правильная постановка задачи и умелое управление возможностями Решателя являются залогом успеха. Еще один вывод состоит в том, что только весьма квалифицированный пользователь способен применять Решатель в своих задачах. Чаще всего, программисту приходится делать надстройку, облегчающую пользователю решение той или иной оптимизационной задачи. Я посвятил отдельную главу созданию подобного инструмента, а сейчас дам краткий обзор методам программной работы с Решателем.

Решатель и программирование

Правильная постановка оптимизационной задачи, грамотное использование Решателя требует от пользователя определенной математической культуры. В ряде случаев эту часть работы следует взять на себя программисту и организовать программный вызов Решателя, оставив за пользователем задание исходных данных. Все, что можно сделать вручную, можно сделать и на VBA и даже несколько больше, используя стандартный набор функций VBA для работы с Решателем. Кратко охарактеризую четыре основные функции:

• SolverOk(SetCell, MaxMinVal, ValueOf, ByChange) - позволяет поставить задачу оптимизации. Она выполняет работу, эквивалентную установке параметров в диалоговом окне при вызове Решателя. Параметр SetCell задает ячейку, содержащую формулу с целевой функцией. Три возможных значения параметра MaxMinVal указывают, что с ней делать (максимизировать, минимизировать или пытаться зафиксировать ее значение, которое при этом задается параметром ValueOf ). Последний по счету параметр ByChange задает диапазон регулируемых ячеек - переменных оптимизационной задачи.
• SolverAdd(CellRef, Relation, FormulaText) - позволяет добавлять ограничения в модель. Первый параметр CellRef задает левую часть ограничения в виде ссылки на ячейку или их диапазон. Пять возможных значений параметра Relation (1…5) определяют отношение, связывающее левую и правую часть ограничения (<=, =, >=, int, bin). Два последних отношения накладывают на переменные ограничения целочисленности и двоичности. Двоичные переменные могут принимать только два значения 0 и 1. Параметр FormulaText задает правую часть ограничений, он не должен задаваться, если отношение задано как int или bin.
• SolverOptions(MaxTime, Iterations, Precision, AssumeLinear, StepThru, Estimates, Derivates, Search, IntTolerance, Scaling, Convergence, AssumeNonNeg) - используя эту функцию, можно установить параметры Решателя, которые вручную устанавливаются в окне Options. Мы не будем их описывать, так как большинство из них мы фактически рассмотрели. Но на одном из них остановимся. Булев параметр StepThru имеет значение True, если включается пошаговое исполнение с прерыванием после выполнения каждой итерации. При программировании можно передать Решателю макрос, который будет выполняться при каждом таком прерывании.
• SolverSolve(UserFinish, ShowRef) - запускает Решатель. Ее действие эквивалентно выбору кнопки Solve. Возможный булев параметр UserFinish равен True, если результаты не выводятся на дисплей. Если параметр опущен или равен False, результаты появятся в диалоговом окне Решателя, предназначенном для их вывода. Параметр ShowRef представляет строку, задающую имя макроса, который должен выполняться в паузах между итерациями. Он задается, только если параметр StepThru равен True.