2.2 Решение задачи об оптимальном плане выпуска продукции с помощью Excel
1. Организуйте данные на листе MS Excel так, как это показано на рисунке 1.
a) В ячейку G9 введена
целевая функция
,
представляющая собой прибыль от продажиx1десятков свитеров иx2десятков кофточек.
b) В ячейки G6,G7,G8 — формулы, отражающие расход сырья при изготовленииx1десятков свитеров иx2десятков кофточек.
Рисунок 1 – Организация данных на листе MS Excel для примера «Оптимальный план производства трикотажной фабрики»
2. Выберите вкладку «Данные» «Поиск решения». Появится окно, озаглавленное«Поиск решения» (рисунок 2).
а) В поле окна «Установить целевую ячейку»отметьте ячейкуG9 (щелкните сначала по полю окна, а затем по ячейкеG9);
b) Установите переключатель на отметке «Равной максимальному значению»;
c) В поле окна «Изменяя ячейки»отметьте ячейки В12:С12.
Рисунок 2 – Общий вид и работа с окном «Поиск решения»
Добавьте
ограничения, щелкая по кнопке «Добавить».
В появившемся окне, озаглавленном«Добавление ограничения»(рисунок
3), щелкните по полю «Ссылка на ячейку»,
а затем отметьте ячейки В12:С12, выберите
знак ограничения, щелкните по правому
полю «Ограничение» и введите в него
значение 0. Таким образом, вы ввели
ограничение
.
Вновь щелкните по кнопке«Добавить».
.
Рисунок 3 – Общий вид и работа с окном «Добавление ограничения»
e) В появившемся окне «Добавление
ограничения»щелкните в поле«Ссылка
на ячейку», а затем отметьте ячейкуG6, выберите знак ограничения
(≤), щелкните по правому полю«Ограничение»и отметьте в нем ячейкуD6,
содержащую ограничение на ресурс«ДСП».
Таким образом, вы ввели ограничение
;
f) Продолжайте процесс, пока не введете остальные два ограничения.
3. Щелкните по кнопке «Параметры». Появится окно«Параметры поиска решения»(рисунок 4), в котором можно (но не нужно) менять многочисленные параметры оптимизации. Вас интересует только, установлен ли флажок«Линейная модель». Если нет, установите его, щелкните по кнопкеOkи вернитесь к окну«Поиск решения».
Рисунок 4 – «Параметры поиска решения» в MSExcel
Установка параметров оптимизации в окне «Поиск решения» должна выглядеть так, как показано на рисунке 5.
Рисунок 5 – Ввод данных для примера «Оптимальный план выпуска продукции мебельного цеха» в окно «Поиск решения»
Замечание
а) Первое ограничение (
)
можно не добавлять в список ограничений
в окне«Поиск решения», вместо этого
можно поставить флажок«Неотрицательные
значения»в окне«Параметры поиска
решения»;
b) Если ограничения имеют один и тот же знак и расположены на листеMSExcelв смежных ячейках (и если запасы ресурсов также расположены в смежных ячейках), то в окне«Добавление ограничения»в полях«Ссылка на ячейку» и«Ограничение» можно указывать не отдельные ячейки, а диапазоны ячеек (рисунок 6).
Рисунок 6 – Пример ввода ограничений в окно «Добавление ограничения»
4. Щелкните по кнопке «Выполнить». Оптимизационная программа MS Excel выполнит поиск решения, после чего появится окно«Результаты поиска решения»(рисунок 7). Прочтите сообщение программы в этом окне. Если вы все сделали правильно, программа сообщит:«Решение найдено. Все ограничения и условия оптимальности выполнены».
Рисунок 7 – Вид окна «Результаты поиска решения»
Вид листа MS Excel, соответствующий оптимальному решению, показан на рисунке 8.
Рисунок 8 – Вид листа MS Excel, соответствующий оптимальному решению
В этом случае убедитесь, что переключатель в окне «Результаты поиска решения»находится в положении«Сохранить найденное решение», щелкните по кнопкеOkи прочтите ответ в ячейках В12:С12. В ячейкахG6:G8 содержатся значения ресурсов, которые необходимы для полученного оптимального плана. В случае, если вы неверно задали знак ограничений, ввели неверные формулы для целевой функции или для ограничений и оптимизационная программа не может найти решения, в окне появятся сообщения«Значения целевой ячейки не сходятся» или«Поиск не может найти решения», или«Условия линейной модели не выполняются». В этом случае следует переставить переключатель в окне«Результаты поиска решения» в положение«Восстановить исходные данные», щелкнуть по кнопкеOkи проверить организацию данных на листе Excel и в установках окна«Поиск решения».