Составление смеси

Задача  составления смеси различных веществ — одна из актуальных  задач производства. В данном случае задача оптимизации формулируется следующим образом: требуется составить  смесь заданного  состава, используя несколько  видов сырья с различным содержанием нужных компонентов. Все виды сырья имеют различную  стоимость.  При  составлении смеси необходимо  не только  обеспечить  заданный  состав из нескольких компонентов,  но и минимизировать ее стоимость.

Классическая постановка задачи

На рис. 6.39 приводятся исходные данные к задаче такого вида.

При решении задачи происходит подбор значений  ячеек B16:B18. Значения ячеек C16:D18 (процентный состав необходимых  компонентов) вычисляются по

аналогичным формулам,  например,  в ячейке  C16 находится формула  =B16*B9. Стоимость  необходимого  количества  сырья вычисляется с учетом его стоимости и количества, так, в ячейке E16 находится формула =B16*D9. Итоговые  значения  содержания компонентов  и стоимости  вычисляются суммированием соответствующих данных. Ячейка E19 содержит суммарную стоимость смеси компонентов,  это значение необходимо минимизировать.

Ограничения, используемые при решении задачи, показаны на рис. 6.40. В данном случае для итоговых значений содержания компонентов используются ограничения, не допускающие превышения их содержания. Например, для значения содержания компонента №1 (ячейка C19) используется формула $C$19<=$D$5. При необходимости эти ограничения могут быть изменены.

Рис. 6.39. Исходные данные к задаче о составлении смеси

Рис. 6.40. Ограничения к задаче о составлении смеси

. Производственные расчеты

Результат решения  задачи  представлен  на рис. 6.41. Все поставленные условия  выполнены, получена смесь нужного состава.

Рис. 6.41. Решение задачи о составлении смеси

Решение задачи при дополнительных ограничениях

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

Предположим, что необходимо произвести заданное количество смеси при ограниченных запасах сырья. Исходные данные для такого варианта задачи представлены  на рис. 6.42.

В ячейках  D7 и E10:E12 находятся  дополнительные данные,  значения  ячеек F17:F19 вычисляются аналогичным образом. Например, в ячейке F17 находится  формула =$D$7*B17.

Ограничения к задаче (рис. 6.43) дополнены условиями $F$17:$F$19<=$E$10:

$E$12 (ограничения на запасы компонентов) и $F$20>=$D$7 (требуемое  коли-

чество смеси).

На рис. 6.44 показаны результаты решения задачи. Сравнив эти результаты с предыдущим вариантом (рис. 6.41), можно убедиться, что дополнительные ограничения на запасы сырья несколько изменили результат. Так как сырья «B» оказалось недостаточно, то используется сырье «A», которое не использовалось ранее. Результатом такого изменения явилось  повышение стоимости смеси.

Рис. 6.42. Исходные данные к задаче о составлении смеси с дополнительными ограничениями

Рис. 6.43. Дополнительные ограничения к задаче о составлении смеси

СОВЕТ

Легко  проверить, что при достаточно больших запасах сырья решение  задачи в такой формулировке полностью совпадает с предыдущим вариантом.

Необходимо отметить, что при дополнительных ограничениях для  подобной задачи не всегда легко находится решение. Например, при показанных на рис. 6.43 исходных данных решение находится быстро, но  достаточно изменить некоторые из значений исходных данных (например содержимое ячейки E12 заменить на 20), как возникают проблемы при поиске решения. В подобных случаях следует пробовать различные варианты начальных значений изменяемых ячеек (B17:B19) или проверить действующие ограничения.

. Производственные расчеты

Рис. 6.44. Решение задачи о составлении смеси с дополнительными ограничениями

Источник: Трусов А. Ф. Excel 2007 для менеджеров и экономистов: логистические,  производственные и оптимизационные расчеты (+CD). — СПб.: Питер, 2009. — 256 с.: ил.

Похожие посты:

Вы можете оставить комментарий, или ссылку на Ваш сайт.

Оставить комментарий