Лабораторная работа по управлению проектами (Вариант 10)

Обозначим через х1 х2, х3 искомую производственную программу — объемы выпуска (в штуках) шкафов, тумбочек и столов. Тогда математическая модель задачи оптимизации примет вид Z = 3х1 + 4 х2 + 2х3 => max (1) при ограничениях х1 + 2х2 + х3 18, 2 х1 + х2 + х3 16, х1 + х2 8 (2) х2 + х3 6 х1 0, х2 0, х3 0. Для решения создадим на рабочем листе Excel две таблицы (рис.1). Рисунок 1. Рабочий лист Excel Выделим листе на рабочем Excel ячейки В4, С4, D4 (массив B4:D4) для переменных решения х1, х2, х3. В ячейку Е4 запишем формулу для вычисления целевой функции. Так как целевая функция равна сумме попарных произведений коэффициентов целевой функции с1, с2, с3 и соответствующих переменных решения х1, х2, х3, то формулу в ячейке Е4 удобно записать с помощью стандартной функции Excel СУММПРОИЗВ (Вставка Функция > Математические.>СУММПРОИЗВ) (рис. 1). Соответствующая формула в ячейке Е4, вычисляющая значения целевой функции для производственной программы х1, х2, х3, запишется следующим образом: =CУMMПРОИЗВ(B12:D12;B9:D9). В ячейках B12:D12 расположены (и далее будут подбираться «Поиском решения») значения х1, х2, х3, а в ячейках B9:D9 записаны значения коэффициентов целевой функции. На этой стадии переменным х1, х2, х3 можно не придавать никакого начального числового значения — оставить их пустыми или положить равными нулю. Для записи ограничений в табличной модели предусмотрены ячейки А3:E7 (рис. 1). В ячейках B4:D7 записаны известные из условий задачи удельные расходы ресурсов, необходимых для производства каждого изделия. В колонку «Запас ресурсов» помещены значения запасов ресурсов — правые части ограничений. Для удобства анализа полученных решений можно также добавить колонку «Запас-Расход», в которой будут вычисляться излишки каждого ресурса, остающиеся после выполнения найденной производственной программы. Формула для нахождения остатка проста: это разность между имевшимся запасом и фактически потребленным количеством ресурса. Колонка «Расход ресурса» предусмотрена для записи формул, вычисляющих левые части ограничений системы (2). Как видно из структуры ограничений (2), их левые части представляют собой сумму попарных произведений переменных х1, х2, х3 на удельные расходы ресурсов. Следовательно, для записи формулы здесь также целесообразно применить стандартную функцию Excel СУММПРОИЗВ. Первое ограничение системы (2) имеет вид х1 + 2х2 + х3 18, Левая часть ограничения — это сумма попарных произведений чисел {1, 2, 1} на х1; х2, х3, т.е. удельных расходов ресурса ДСП на объемы производства каждого изделия. Так как удельные расходы для ДСП {1, 2, 1} расположены в ячейках B4:D4, а производственная программа х1; х2, х3, (объемы выпуска изделий) расположена в ячейках B12:D12, то для вычисления левой части ограничения в ячейку Е8 записываем формулу =CУMMПРОИЗВ($B$4:$D$4;B12:D12). После того как вся необходимая информация размещена на рабочем листе, можно переходить к решению оптимизационной задачи с помощью надстройки «Поиск решения» (Сервис > Поиск решения…). В открывшемся диалоговом окне вводим адрес целевой ячейки — B13 (поле «Установить целевую ячейку:») — и адреса массива ячеек B12:D12, в которых содержатся переменные решения — х1; х2, х3, (поле «Изменяя ячейки:»). Устанавливаем флажок «Равной: максимальному значению». Устанавливаем параметры поиска решения, необходимые для решения задачи линейного программирования: с помощью кнопки «Параметры» переходим к окну «Параметры поиска решения». ваем флажки «Линейная модель», «Неотрицательные значения», «Автоматическое масштабирование». После установки параметров нажатием кнопки «ОК» возвращаемся в основное диалоговое окно. Для ввода ограничений используем кнопку «Добавить» и переходим в окно «Добавление ограничения». Для рассматриваемой модели это можно сделать двумя способами: вводя поочередно каждое ограничение системы или введя сразу массивы левых и правых частей ограничений («Поиск решения» это допускает). В первом случае поле «Ограничения:» будет содержать 4 строки (рис. 5.8), во втором случае — одну строку. После заполнения всех полей надстройка «Поиск решения» готова к решению задачи оптимизации и выводу результатов на рабочий лист Excel. Для этого необходимо нажать кнопку «Выполнить». Прежде чем вывести результаты оптимизации на рабочий лист, «Поиск решения» выводит на экран диалоговое окно «Результаты поиска решения» В зависимости от того, имеет задача решение или нет, в окне могут выводиться различные сообщения. Если в окне «Результаты поиска решения» появляется сообщение «Решение найдено. Все ограничения и условия оптимальности выполнены», то задача успешно решена. После нажатия кнопки «ОК» на рабочий лист Excel выводятся результаты оптимизации Заключение Для рассматриваемой задачи получена следующая оптимальная производственная программа: • шкафы необходимо выпускать в количестве 5 шт.; • тумбочки — в количестве 3 шт.; • столы — в количестве 3 шт. Соответствующие оптимальные значения переменных х1; х2, х3, найденные «Поиском решения» и обращающие целевую функцию в максимум, расположены в ячейках B12:D12. При такой производственной программе будет достигнута максимальная прибыль Zmax = 33. Максимальное значение целевой функции, соответствующее оптимальному решению, вычислено «Поиском решения» в ячейке B13. Расходы ресурсов, необходимые для выполнения оптимальной производственной программы, помещены в ячейки B16:B19. Откуда следует: • ДСП необходимо 14 единиц, в то время как запас составлял 18 единиц. Следовательно, запас данного ресурса избыточен. Излишек ресурса равен 4 единицам — ячейка Н8. • Фанера, стекло и крепеж при оптимальной производственной программе расходуются полностью — ячейки Н9:Н1