Рассмотрим один из видов оптимизационных задач, которые можно решать с помощью инструмента "Поиск решения" в Excel. Пусть нам надо максимизировать прибыль от продажи товаров (1, 2, 3) с заданной ценой (G6:G8) при ограничениях:
1. на время работы техников (Тех А, Тех Б, Тех С, Тех D), которые производят детали:
В таблице A5-E8 на пересечении строк и столбцов стоят нули для тех деталей, которые мастер не производит, а для остальных - время на их производство. В строке 11 - ограничение на общее время работы каждого мастера.
2. Максимальный объем спроса на каждый вид товара
Находится в столбце H.
Теперь об искомых значениях и сопутствующих решению формулах.
Так, на первом рисунке в строке 9 располагается фактическое общее время работы каждого мастера (это сумма произведений количества произведенной каждой детали на время для этого, например, СУММПРОИЗВ(B6:B8;B14:B16)), которое должно удовлетворять ограничению.
На пересечении столбцов B, C, D, E и строк 14-16 программа заполнит искомые объемы для произведенных деталей каждым мастером (возможны, дробные количества), чтобы максимизировать прибыль. В F14-F16 указана формула Суммы, подсчитывающая общее количество произведенных деталей 1,2 и 3, чтобы не нарушать ограничение.
И наконец, прибыль считается как =СУММПРОИЗВ(G6:G8;F14:F16) - ее нам надо максимизировать.
Теперь запускаем "Поиск решения" на вкладке "Данные", указываем целевую функцию прибыли, ставим флажок максимизации, заполняем ограничения (на нулевые значения для продукции не производимой конкретным мастером, спрос на продукцию и время работы мастеров), выбираем "Поиск решения линейных задач симплекс-методом" и жмем "Найти решение":
В результате получим:
Обратите внимание на значение прибыли и объемов произведенной продукции каждым мастером.
