Надстройка «Solver» (Поиск решения)
Программа Поиск решений (в оригинале Excel Solver) – дополнительная надстройка табличного процессора MS Excel, которая предназначена для решения определенных систем уравнений, линейных и нелинейных задач оптимизации.
Размер задачи, которую можно решить с помощью базовой версии этой программы, ограничивается такими предельными показателями:
количество неизвестных (decision variable) – 200;
количество формульных ограничений (explicit constraint) на неизвестные – 100;
количество предельных условий (simple constraint) на неизвестные – 400.
По умолчанию в Excel надстройка Поиск решения отключена. Чтобы активизировать ее в Excel 2007, щелкните значок Кнопка Microsoft Office , щелкните Параметры Excel, а затем выберите категорию Надстройки. В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти. В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК.
Рис.1.1. Диалоговое окно «Надстройки» MS Excel
В Excel 2003 и ниже выберите команду Сервис/Надстройки, в появившемся диалоговом окне Надстройки установите флажок Поиск решения и щелкните на кнопке ОК. Если вслед за этим на экране появится диалоговое окно с предложением подтвердить ваши намерения, щелкните на кнопке ОК (рис. 1.1). (Возможно, вам понадобится установочный компакт-диск MS Office).
Процедуру поиска решения рассмотрим на примере фабрики по изготовлению и продаже двух видов краски (таб. 1.1):
Создайте таблицу с формулами, которые устанавливают связи между ячейками (рис. 1.2).
Рис. 1.2. Табличная модель
В таблице строка «Оптимальный объем производства» – это оптимальный план данной задачи, а строка «Доход» – целевая функция.
Столбцы «Использовано» и «Остаток» служат для проверки ограничений запаса сырья (F4:F5 G4:G5, где G4:G5 – суммs расходов сырья), а так же для дальнейшего анализа и выводов по данной задаче.
В ячейки F4 и F5 запишите формулы определения количества использованного сырья – «=СУММПРОИЗВ(C8:D8;C4:D4)» и «=СУММПРОИЗВ(C8:D8;C5:D5)» соответственно.
В ячейки G4 и G5 запишите формулы определения разности между количеством запаса сырья и количеством использованного сырья – «=E4-F4» и «=E5-F5».
В целевую ячейку определения максимального дохода запишите следующую формулу – «=СУММПРОИЗВ(C8:D8;C7:D7)».
Выделите целевую ячейку, которая должна принять необходимое значение, и выберите команду Данные/Анализ/Поиск решения (в Excel 2003 Сервис > Поиск решения). Поле Установить целевую ячейку открывшегося диалогового окна надстройки Solver (Поиск решения) будет содержать адрес целевой ячейки (рис. 1.3).
Установите переключатели Равной, задающие значение целевой ячейки, – максимальному значению, минимальному значению или значению или введите значение в поле справа.
Укажите в поле Изменяя ячейки, в каких ячейках программа должна изменять значения в поисках оптимального результата.
Создайте ограничения в списке Ограничения. Для этого щелкните на кнопке Добавить и в диалоговом окне Добавление ограничения определите ограничение.
Если, согласно условиям задачи, оптимальный план должен содержать целый значения, то обязательно это укажите в ограничениях.
Рис. 1.3. Окно «Поиск решения»
Щелкните на кнопке Параметры, и в появившемся окне установите переключатель Неотрицательные значения (если переменные должны быть положительными числами), Линейная модель (если задача, которую вы решаете, относится к линейным моделям) (рис. 1.4).
Рис. 1.4. Окно «Параметры поиска решения»
Щелкнув на кнопке Выполнить, запустите процесс поиска решения.
Когда появится диалоговое окно Результаты поиска решения, выберите все три типа отчетов, переключатель Сохранить найденное решение или Восстановить исходные значения и щелкните по кнопке ОК (рис. 1.5).
Рис. 1.5. Окно «Результаты поиска решения»
В итоге Вы добьетесь следующих результатов (рис. 1.6).
Рис. 1.6. Табличная модель с результатами решения