Решение задач оптимизации в Excel

      6. Решение задач оптимизации в Excel 

      Рассмотрим  задачу нахождения точек, в которых  достигаются максимальные и минимальные  значения функций нескольких  переменных, определенных на множествах с линейными  и нелинейными ограничениями.

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

      В качестве примера приведем  задачу об использовании сырья. 

      Задача  об использовании  сырья 

      Необходимо  определить оптимальный план выпуска  продукции в условиях дефицита продукции.

      Предположим, что предприятие выпускает два  вида продукции. Цена единицы 1 вида продукции  равна 35000, 2 вида — 58000.

      Для изготовления продукции используются три вида сырья, запасы которого оцениваются в 47, 59,4 и 7.8  условных единиц. На каждый вид сырья есть коэффициент его расхода на единицу продукции. Соответствующие коэффициенты приведена в таблице 1..

      Таблица.1

        Коэффициенты расхода сырья на единицу продукции

      Продукция Запасы сырья

      вид 1 вид 2 (усл. ед.)

      1,2 1,9 47

      2,3 1,8 59.4

      0,1 0,7 7.8

      Обозначим количество произведенной продукции 1 вида через c1, 2 вида — c2. Целевая функция есть выражение следующего вида:  

            J(c1,c2) = 35000*c1 + 58000*c2

      Это есть цена произведенной продукции. Наше решение должно обеспечить максимальное значение этой функции.

      Табл. 2 налагает на величины c1 и c2 ограничения следующего вида:

                                          Табл.2. 

      1,2*c1 + 1,9*c2 <= 47

      2,3*c1 + 1,8*c2 <= 59,4

      0,1*c1 + 0,7*c2 <= 7.8

      c1 >= 0

      c2 >= 0

      Задача  поставлена и приступаем к ее решению. Для решения выполните следующие  действия:

      Введите в ячейку A1 формулу для целевой функции: =35000*c1+58000*c2.

      Введите в ячейку A3 формулу для ограничения: =1,2*c1+1,9*c2.

      Введите в ячейку A4 формулу для ограничения: =2,3*c1+1,8*c2.

      Введите в ячейку A5 формулу для ограничения: =0,1*c1+0,7*c2.

      Введите в ячейку A6 формулу для ограничения: =c1.

      Введите в ячейку A7 формулу для ограничения: =c2.

      Введите в ячейки C1:C2 начальные значения переменных. В нашем случае положим эти значения нулевыми.

      Выполните команду Сервис |   Поиск решения.   Появится окно диалога “Поиск решения”.

      В поле ввода Установить целевую ячейку введите ссылку на ячейку A1.

      В поле ввода Изменяя ячейки укажите ссылки на ячейки C1:C2.

      Начинаем  вводить информацию в поле ввода  Ограничения. Нажмите кнопку Добавить. Появится окно диалога “Добавить ограничения”. В поле ввода Ссылка на ячейку введите ссылку на ячейку A3. В поле ввода Ограничение введите <= и число 47

      Воспользуйтесь кнопкой Добавить для ввода остальных ограничений.. Для изменения ограничения установите на него курсор и нажмите кнопку Изменить.

      Нажмите кнопку Выполнить. После окончания расчета Excel откроет окно диалога “Результаты поиска решения”

Выберите в окне “Тип отчета” Результаты и нажмите кнопку OK. Перед тем листом, где записана постановка задачи, будет вставлен лист “Отчет по результатам 1”, а на экране вы увидите ответ на поставленную задачу . В ячейках c1 и c1 отображаются значения переменных, на которых достигается максимальное значение целевой функции.