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

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

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

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

Задача

Компания владеет 4-мя карьерами, из которых она может добывать известняк различного качества. Особенно важны 2 характеристики: содержание в породе кальция и магния. Компания должна выполнить заказ на поставку 6000 тонн известняка (в течение 1 периода, года). Продукция должна содержать в среднем 0,9 единиц кальция на тонну и 2,3 единицы магния. Годовые затраты на эксплуатацию каждого карьера фиксированы и составляют значительную величину. Каждый карьер имеет свою производительность. Суммарная мощность карьеров избыточна для выполнения заказа. Чтобы минимизировать затраты необходимо один из карьеров закрыть (не эксплуатировать в этом году). Какой карьер выгоднее всего закрыть? (пример с сайта ]]> www.solver.com ]]> )

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

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

Переменные (выделено зеленым) . В качестве переменных модели следует взять бинарные значения (0 или 1), означающие 0 – карьер закрыт, 1 – открыт. Также необходимо варьировать количество известняка, выпускаемое каждым карьером, чтобы рассчитать содержание кальция и магния.

Ограничения (выделено синим) . Среднее содержание кальция и магния должно удовлетворять требования спецификации. Заказ должен быть выполнен. Объем выпущенной карьером продукции не должен превосходить его максимальной производительности.

Целевая функция (выделено красным) . Расходы на содержание карьеров должны быть минимальны.

Примечание : для удобства настройки Поиска решения используются именованные диапазоны .

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

Как показывают расчеты выгоднее закрыть 2-й карьер. В статье Поиск решения MS EXCEL (1.5). Распределение ресурсов (ограничение - требования к качеству продукции, несколько периодов) рассмотрена задача выполнения заказа в течение 5 лет.

Примечание . Данный тип задачи встречается, в частности, в нефтехимической отрасли или в сельском хозяйстве, когда требуется создать смесь, качество которой соответствует определенным требованиям (см. файл примера, лист Смеси ).