Поиск решения EXCEL (1.2). Распределение ресурсов (ограничение по количеству оборудования)

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

Задача оптимального распределения ресурсов (распределительная задача) заключается в отыскании наилучшего распределения ресурсов, при котором либо максимизируется результат, либо минимизируются затраты. Решим задачу, в которой минимизируются затраты, на основе примера с сайта ]]> www.solver.com ]]> .

Вводная статья про Поиск решения в MS EXCEL 2010 находится здесь .

Задача

Предприятие выпускает продукт (только один вид изделия и ничего более) и ему необходимо выполнить заказ клиента. На предприятии 3 типа оборудования. Все типы оборудования выпускают один и тот же продукт. Производительность каждого типа оборудования разная. Каждый тип оборудования имеет постоянную и переменную часть расходов. Переменная часть расходов пропорциональна количеству произведенных изделий. Имеется ограниченное количество единиц оборудования каждого типа (но общее количество оборудования избыточно для выполнения заказа). Требуется минимизировать расходы на оборудование при условии выполнения заказа.

Создание модели

На рисунке ниже приведена модель, созданная для решения задачи (см. файл примера ).

Предприятие несет расходы в зависимости от типа оборудования: использование оборудования типа Alpha-3000 самое дорогое в эксплуатации, но оно и самое производительное. Оборудование типа Alpha-1000 самое дешевое в эксплуатации, но оно и менее производительное. Задача Поиска решения выбрать наиболее дешевое оборудование, так чтобы заказ был выполнен (мощностей Alpha-1000 не хватит для выполнения заказа). Казалось бы, решение очевидно (взять по максимуму дешевое оборудование, остальную производительность обеспечить более дорогим). Однако, если учесть, что из-за низкой производительности дешевых машин приходится их брать больше, неся существенные постоянные расходы, то решение уже не кажется очевидным.

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

Ограничения (выделено синим) . Количество задействованных машин должно быть целым числом. Количество задействованных машин каждого типа должно быть не больше, чем имеется в наличии. Всего должно быть выпущено продукции не меньше чем величина заказа. Также необходимо ограничить производительность задействованного оборудования. Производительность задается не для каждой единицы, а для типа в целом. Максимальная производительность задействованного оборудования рассчитывается формулой массива = Машин_Задействовано* Макс_производительность Макс_производительность – это именованный диапазон . Ограничения выделены синим цветом.

Целевая функция (выделено красным) . Целевая функция задается формулой = СУММПРОИЗВ(Продукции_выпущено_По_типу; Расходы_переменные)+ СУММПРОИЗВ(Машин_Задействовано; Расходы_постоянные) Это просто суммарные операционные расходы (переменная и постоянные части). Результат вычисления этой формулы должен быть минимизирован (выделено красным).

Убедитесь, что метод решения соответствует линейной задаче. Теперь в диалоговом окне можно нажать кнопку Найти решение .

Результаты расчетов

Поиск решения найдет оптимальный набор единиц оборудования по типам и их производительность, при котором операционные расходы будут минимальные, а заказ выполнен. Обратите внимание, что значение переменных (количество продукции, выпущенное на каждом типе оборудования) – целые числа, хотя в ограничениях этого прописано не было. Изменив значения максимальной производительности с 40 на, например, 40,3 (ячейка D 7 ) и пересчитав еще раз, получим нецелые значения выпущенной продукции. Т.е. Поиск решения сам «догадался», что нам требуются целые значения переменной, т.к. в качестве ограничения были указаны значения без дробной части. Двигаемся дальше. В условии задачи предполагается, что выпуск продукции осуществляется лишь в течение одного периода. В статье Поиск решения MS EXCEL (1.3). Распределение ресурсов (ограничение по количеству оборудования, несколько периодов) решим задачу определения наилучшего распределения ресурсов в случае нескольких периодов.