Технология решения задач линейного программирования с помощью «Поиска решений» в среде EXCEL

 Ø Сервис Ø Поиск решения… 
  Установить целевую ячейку  ö С17 
  Равной: ¤ минимальному значению  
  Изменяя ячейки ö С4:С10 

Далее приступаем к вводу ограничений (рис. 3.3) :

 

q Добавить  
  Ссылка на ячейку:= $C$4:$C$10  
  ò (вид ограничения) ö цел 
q Добавить 
  Ссылка на ячейку:= $C$4:$C$10 
  ò (вид ограничения) ö >= 
  Ограничение := 0

 
 q Добавить 
  Ссылка на ячейку:= $E$14:$K$14  
ò (вид ограничения) ö >= 
  Ограничение := $E$15:$K$15  
q OK

 q Выполнить

   После нажатия кнопки Выполнить надстройка "Поиск решения" приступает к итерациям, и затем, закончив вычисления, открывает диалоговое окно Результаты поиска решения (рис. 3.4), в котором выводится сообщение о том, найдено или нет решение поставленной задачи. Если найденное решение вас устраивает, вы можете сохранить его на рабочем листе:

 ¤ Сохранить найденное решение 
q OK

   Оптимальное решение — итоговое распределение выходных таково: 2 человек с выходными "понедельник, вторник"; 4 человек с выходными "вторник, среда"; 0 человека с выходными "среда, четверг"; 2 человек с выходными "четверг, пятница"; по 1  человек с выходными "пятница, суббота", 0 "воскресенье, понедельник"; и, что довольно естественно, никого с выходными "суббота, воскресенье".

   Можно также сохранить найденное решение  в качестве сценария с помощью кнопки Сохранить сценарий (обычно так поступают в том случае, когда требуется сохранить результаты нескольких различных решений, полученных при изменении некоторых ограничений).

      Специальные задачи линейного  программирования 

      Транспортные  задачи и задачи о  назначениях 

      1. Пример решения  транспортной задачи 

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

      Таблица 1.2

   Исходные  данные транспортной задачи

   Целевая функция и ограничения данной задачи имеют вид

      Экранные  формы, задание переменных, целевой функции, ограничений и граничных условий транспортной задачи (1.5) и ее решение представлены на рис.1.15, 1.16, 1.17 и в табл.1.3.

   Рис.1.15. Экранная форма транспортной задачи (1.5)

      (курсор  в целевой ячейке F15)

   Таблица 1.3

   Формулы экранной формы задачи (1.5)

 

 

   Рис.1.16. Ограничения и граничные условия  задачи (1.5) 

   Рис.1.17. Экранная форма после получения решения задачи (1.5)

      (курсор  в целевой ячейке F15) 

      Пример  решения задачи о  назначениях 

      Частным случаем задач с целочисленными переменными являются задачи, в результате решения которых искомые переменные могут принимать только одно из двух значений: 0 или 1. Такие переменные в честь предложившего их английского математика Джорджа Буля называют булевыми. На рис.1.18 представлена экранная форма с решением некоторой транспортной задачи с булевыми переменными. 

Рис.1.18. Решение транспортной задачи с булевыми переменными

      Помимо  задания требования целочисленности (см. подразд.1.3.2) при вводе условия  задач с булевыми переменными  необходимо:

• для наглядности восприятия ввести в экранную форму слово "булевы" в качестве характеристики переменных (см. рис.1.18);
• в окне "Поиск решения" добавить граничные условия, имеющие смысл ограничения значений переменных по их единичной верхней границе (рис.1.19).

 

Рис.1.19. Добавление условия единичной верхней  границы значений переменных транспортной задачи с булевыми переменными 

      Вид окна "Поиск решения" для задачи с булевыми переменными, представленной на рис.1.18, приведен на рис.1.20.

Рис.1.20. Окно "Поиск решения" для задачи с булевыми переменными, представленной на рис.1.18 

 

Задачи для самостоятельного решения (N– последняя цифра номера зачетки)

            (Математическая  модель и ответ  записываются в  тетради, а вычисления  проводятся в Excel (каждая задача решается на отдельном листе, который имеет название Задача 1, Задача 2, и т. д.)

Задача 1

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

   Определите  оптимальное количество подарочных наборов, обеспечивающее максимальный доход от продажи.

      Задача 2

     Нормы затрат на производство разных видов пиццы, объемы ресурсов и стоимость приведены в таблице

Определите  оптимальное количество пиццы, обеспечивающее максимальный доход от продаж.

 Задача 3

     Определите  структуру блюд на предприятии общественного  питания, обеспечивающую максимальный доход, на основе заданных нормативов затрат продуктов на первые и вторые блюда, представленных в следующей таблице

 Задача 4

      Составьте самый дешевый вариант 1 т кормовой смеси в соответствии с требованиями, представленными в таблице