Количество выпечки каждого вида j, которое мы можем производить, обозначим через xj. Тогда зная рецептуру (расход) продуктов на изготовления каждого вида выпечки и объем запасов по каждому виду из продуктов (сахар, яйца, мука, масло), который находится на складе, можем записать систему ограничений:
(1)
Еще добавим граничные условия, чтобы искомые объемы были представлены целыми положительными числами (нельзя произвести/сбыть дробное кол-во изделия):
(2)
В нашем случае целевая функция (критерий оптимизации) это максимизация прибыли получаемой от продажи выпечки 1 и выпечки 2:
(3)
Таким образом, совокупность системы ограничений (1), целевой функции (3) и граничных условий (2) составляют математическую модель нашей задачи. Значения x1 и x2 являются искомыми объемами производства или планом продукции.
Решение в Excel.
1. Создайте таблицу см рисунок 30.
Рисунок 30
2. В ячейки введите формулы:
| ячейка | формула |
| Система ограничений | |
| Е2 | =B2*$B$7+ C2*$C$7 |
| E3 | =B3*$B$7+ C3*$C$7 |
| E4 | =B4*$B$7+ C4*$C$7 |
| E5 | =B5*$B$7+ C5*$C$7 |
| Целевая ячейка | |
| B8 | =B6*$B$7+ C6*$C$7 |
Или можно использовать функцию Excel СУММПРОИЗВ
| ячейка | формула |
| Система ограничений | |
| Е2 | =СУММПРОИЗВ(B2:C2;$B$7:$C$7) |
| E3 | =СУММПРОИЗВ(B3:C3;$B$7:$C$7) |
| E4 | =СУММПРОИЗВ(B4:C4;$B$7:$C$7) |
| E5 | =СУММПРОИЗВ(B5:C5;$B$7:$C$7) |
| Целевая ячейка | |
| B8 | =СУММПРОИЗВ(B6:C6;B7:C7) |
Пояснение. Функция СУММПРОИЗВ (массив1;массив2;массив3;...) перемножает массивы 1, 2, 3.., а затем суммирует эти произведения,
т.е. формула =СУММПРОИЗВ(B2:C2;$B$7:$C$7) будет аналогична формуле =B2*$B$7+ C2*$C$7.
Знак абсолютной ссылки $ мы ставим, чтобы было можно автоматически заполнять ячейки копированием (протягивая мышкой).
3. Выделите целевую ячейку, которая должна принять необходимое значение, и выберите команду:
- В Excel 2007 Данные/Анализ/Поиск решения;
- В Excel 2003 Сервис > Поиск решения. Поле Установить целевую ячейку открывшегося диалогового окна надстройки будет содержать адрес целевой ячейки.
Рисунок 31
4. В Равной установите переключатель, задающий значение целевой функции в целевой ячейки - максимальному значению (мы ищем план производства, при котором прибыль максимальна).
5. В поле Изменяя ячейки укажите ячейки искомого плана (см. рисунок 30, 31).
6. В списке Ограничения задайте граничные условия и систему ограничений. Для этого щелкните на кнопке Добавить и в диалоговом окне Добавление ограничения определите ограничение.
7. Щелкните на кнопке Параметры, и в появившемся окне установите переключатель Неотрицательные значения, Линейная модель (задача, которую вы решаете, относится к линейным моделям).
Рисунок 32
8. Щелкните на кнопке Выполнить. Когда появится диалоговое окно Результаты поиска решения, выберите переключатель Сохранить найденное решение. Результат решения на рисунке 33.
Рисунок 33
Как видим, нам чтобы достичь максимальной прибыли необходимо произвести 56 единиц выпечки1 и 82 - выпечки2, при этом прибыль будет 19184 руб. Так же по решению мы видим, что полностью не исчерпываем запасы по сахару и маслу.