Левые части неравенств ограничений задаем формулами в ячейках:
F8 (трудовые ресурсы) формула:
=B8*B3+C8*C3+D8*D3+E8*E3,
F9 (сырьё) формула:
=B9*B3+C9*C3+D9*D3+E9*E3,
F10 (финансы) формула:
=B10*B3+C10*C3+D10*D3+E10*E3.
Лимиты на ресурсы помещаем в ячейки H8, H9 и H10.
FПосле того как в соответствующие ячейки таблицы записаны формулы целевой функции и функций ограничений, выполним команду СЕРВИСðПОИСК РЕШЕНИЯ и в открывшемся диалоговом окне выполним необходимые установки:
В поле «Установить целевую ячейку» введем адрес ячейки, содержимое которой должно быть оптимизировано. В нашем случае это ячейка «$Н$3», содержащая формулу вычисления дохода предприятия
В группе опций «Равной» установим необходимое значение целевой функции (ячейки $Н$3). В нашем примере выбираем опцию -«Максимальному значению»
В поле « Изменяя область ячеек» необходимо задать адреса ячеек, которые могут изменяться в процессе оптимизации решения. В нашем случае, это ячейки, содержащие информацию о плановом количестве выпуска товаров - ячейки $B$3 : $Е$3. (При нажатии кнопки «Предположить» Excel выделяет область ячеек, на которые прямо или косвенно ссылается целевая ячейка)
FДля того, чтобы ввести в диалоговое окно ограничения:
установите курсор мыши в поле «Ограничения»;
щелкните на кнопке «Добавить», после чего на экране появится диалоговое окно, в которое необходимо ввести все установки ограничений;
В поле «Ссылка на ячейку» необходимо ввести адрес ячейки, содержащее формулу ограничения;
в окно «Ограничение» вводится адрес ячейки, содержащей значение ограничения (либо численное значение ограничения)
содержимое полей «Ссылка на ячейку» и «Ограничение» соединяется с помощью операторов сравнения, выбираемых из раскрывающегося списка
В нашей задаче формулы ограничений на количество используемых ресурсов были записаны в ячейках F8:F10, а сами значения ограничений в ячейках Н8:Н10
Введем первое ограничение, указав в поле «Ссылка на ячейку» адрес ячейки, содержащей формулу ограничений по трудовым ресурсам (F6) (Обратите внимание на то , что после того как в поле “Ссылка на ячейку» Вы указали адрес ячейки, содержащей формулу ограничений, Excel автоматически преобразует эту ссылку в абсолютную.)
в поле «Ограничение» введем адрес ячейки, содержащей значение ограничения по трудовым ресурсам (Н8) (либо само числовое значение ограничения)
выберем оператор сравнения £ ( количество использованных трудовых ресурсов должно быть меньше (или равно) имеющихся в наличии)
FДля ввода нового ограничения, щелкните на кнопке «Добавить».
Повторив описанные выше действия, введите ограничения по сырью и финансам;
щелкните на кнопке «Добавить» и введите ограничения для области решения задачи. В нашем случае, таким ограничением будет то, что количество выпускаемой продукции не может быть величиной отрицательной. Т.е., необходимо ввести ограничения В3 ³ 0; C3 ³ 0; D3 ³ 0; E3 ³ 0.
Завершив ввод ограничений, щелкните на кнопке «ОК»
FВо
вновь открывшемся диалоговом окне
«Поиск
решения»:
Щелкните на кнопке «Параметры» и сделайте необходимые установки ( пояснения к этому окну см. в приведенной ниже таблице) и щелкните на кнопке «ОК»
В диалоговом окне «Поиск решения», щелкните на кнопке «Выполнить».
FВ результате выполненных действий Excel выведет на экран сообщение
Рис.2 Сообщение Excel о результатах поиска решения
щелкните на кнопке «ОК» (о создании отчетов и их анализе будет рассказано ниже в разделе «Анализ оптимального решения») и результаты оптимального решения задачи будут внесены в созданную таблицу
Рис. 3 Фрагмент таблицы Excel с результатами решения задачи по разработке оптимального плана выпуска продуктов.
Как видно из приведенного на рисунке решения максимальная прибыль = 1320 руб. будет получена при выпуске двух видов продуктов: Прод1 и Прод3 в количествах 10 и 6, соответственно. При этом, все ограничения, поставленные нами при решении задачи - выполнены
Примечание:
1. При создании формул целевой функции и функций ограничений оказывается очень удобным использование функции Excel СУММПРОИЗВ(), позволяющую находить сумму произведений нескольких векторов. Так например вместо записи целевой функции в виде =B4*B3+C4*C3+D4*D3+E4*E3
можно было записать =СУММПРОИЗВ(B3:E3;B4:E4)
2. При вводе ограничений мы вводили каждое из них отдельно. В большинстве случаев оказывается возможным ввод ограничения виде массива. Так, например, вместо раздельного ввода ограничений по каждому виду ресурса, можно ввести ограничение в виде массива $F$8:$F$10 £ $H$8:$H$10
