4. Решение задач линейного программирования с помощью ms Excel

Задача распределения ресурсов.

Рассмотрим постановку задачи на следующем примере.

Положим, цех предприятия производит два вида продукции (Продукт 1 и Продукт 2). Следует рассчитать оптимальные недельные объемы производства этих продуктов с точки зрения максимальной прибыли. Прибыль (Целевая функция – F) от первого продукта составляет 5 единиц, от второго – 5,5.

На производстве действуют ограничения по сырью, трудовым ресурсам и транспортным расходам:

Для Продукта 1 требуется 3 единицы сырья, для Продукта 2 – 6. Всего цех располагает 18 единицами сырья.
Для изготовления Продукта 1 требуется 6 рабочих, для Продукта 2 – 4. В цехе 24 рабочих.
Транспортные расходы на перевозку Продукта 1 составляют 2 единицы, а Продукта 2 – 1 единицу. Эти затраты не могут быть менее 2 единиц (цена аренды одного автомобиля минимальной грузоподъемности в течение дня). Полагаем, что вся дневная продукция цеха может быть вывезена на одном грузовике.

Кроме того, очевидно, что ни одна из переменных (число единиц продукции) не может быть

менее нуля.

Отсюда запишем соотношения, из которых можно вычислить оптимальные объемы

производства Продукта 1 и Продукта 2 (виды продукции обозначены как Х₁ и Х₂).

Пример построения таблицы применительно к рассмотренной задаче приведен на рис. 20. На рис. 21 показаны формулы, необходимые для выполнения решения.

Рис. 20 Рис. 21

Для оптимизации в Excel используется инструмент Поиск решения, вызываемый через меню Сервис, представленный на рис. 22.

С начала задается ячейка, содержащая оптимизируемое значение (здесь Е6), затем указывается его желаемое значение (у нас максимальное). Можно задать не только максимальное/минимальное значение, но и любую произвольную величину, введя ее в специальное поле (Равной значению:). Ограничения устанавливаются с помощью кнопки Добавить, которая вызывает окна их ввода (рис. 23).

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

Замечание. Не забудьте в Параметрах поиска решения (Рис. 24) указать Линейная модель и Неотрицательные значения.

Если вычисления оказались успешными, Excel предъявит окно итогов (Рис. 25). Их можно сохранить или отказаться. Сохраним их. Кроме того, можно получить один из трех видов отчетов (Результаты, Устойчивость, Пределы), позволяющие лучше осознать полученные результаты, в том числе, оценить их достоверность.