2.1 Поиск решения и решение оптимизационных задач

Часто в повседневной жизни мы сталкиваемся с необходимостью решать оптимизационные задачи. Например, каждый раз заходя в магазин, мы стоим перед дилеммой максимального удовлетворения тех или иных наших потребностей, соизмеряя их с возможностями нашего кошелька. Что же говорить о менеджерах, экономистах, которые постоянно должны решать разнообразные проблемы, начиная от планирования штата сотрудников, фонда зарплаты и заканчивая составлением оптимального плана производства, планированием рекламной кампании по продвижению продукции на рынок и оптимизацией капиталовложении. Несмотря на все многообразие этих задач MS Excel предлагает единый, мощный инструмент их решения — Поиск решения (Solver). От вас только требуется грамотно сформулировать для MS Excel вашу задачу, а оптимальное решение он най­дет сам.

Линейная оптимизационная задача

Продемонстрируем, как при помощи средства По­иск решения решаются линейные оптимизационные задачи на примере задачи планирования производства.

2.2 Постановка задачи об оптимальном производстве красок

Рассмотрим следующую задачу планирования производства. Небольшая фаб­рика выпускает два типа красок: для внутренних (i) и наружных работ (е). Продукция обоих видов поступает в оптовую продажу. Для производства красок используются два исходных продукта – А и В. Максимально возможные суточные запасы этих продуктов составляют 6 т и 8 т соответственно расходы А и В на 1 т соответствующих красок приведены в таблице 1

Таблица 1. Исходные данные задачи о производстве красок

Исходный продукт	Расход исходных продуктов (в тоннах) на тонну краски		Максимально возможный запас, т
	краска Е	краска 1
А В	1 2	2 1	6 8

Изучение рынка сбыта показало, что суточный спрос на краску I никогда не превышает спроса на краску Е более чем на 1 т. Кроме того, установлено , что спрос на краску I никогда не превышает 2 т в сутки.

Оптовые цены одной тонны красок равны: 3000 руб. для краски Е и 2000 руб. для краски I.

Какое количество краски каждого вида должна производить фабрика, чтобы доход от реализации продукции был максимальным?

Для решения этой задачи необходимо сначала построить математическую модель. Процесс построения модели можно начать с ответа на следующие три вопроса.

• Для определения каких величин строится модель? Что является переменными модели?

• В чем состоит цель, для достижения которой из множества всех допустимых значений переменных выбираются оптимальные?

• Каким ограничениям должны удовлетворять неизвестные?

В нашем случае фабрике необходимо спланировать объем производства красок так, чтобы максимизировать прибыль. Поэтому, переменными являются X_I и X_E соответственно суточные объемы производства красок I и E.

Суммарная суточная прибыль от производства X_I краски I и X_E краски E равна

Z= 3000*X_I + 2000*X_E

Целью фабрики является определение среди всех допустимых значений X_I и X_E таких, которые максимизируют суммарную прибыль, т.е. целевую функцию Z.

Перейдем к ограничениям, которые налагаются на X_I и X_E. Объем производ­ства красок не может быть отрицательным. Следовательно,

X_I и X_E ≥ 0

Расход исходного продукта для производства обоих видов красок не может превосходить максимально возможный запас данного исходного продукта.

Таким образом,

2*X_I + X_E ≤ 6

X_I + 2*X_E ≤ 8

Кроме того, ограничения на величину спроса на краски имеют вид:

X_I - X_E ≤ 1

X_I ≤ 2

Таким образом, математическая модель данной задачи имеет следующий вид.

Максимизировать:

Z = 3000* X_E + 2000* X_I

при ограничениях:

2*X_I + X_E ≤ 6

X_I + 2*X_E ≤ 8

X_I - X_E ≤ 1

X_I ≤ 2

X_I и X_E ≥ 0

Заметим, что данная модель является линейной, т. к. целевая функция и ограничения линейно зависят от переменных.

Перейдем к вводу исходных данных на рабочем листе для решения задачи о красках. Для этого:

1. Отведите ячейки А3 и В3 под значения переменных X_I и X_E соответствен­но.

2. Введите в ячейку С4 функцию цели

=3000*АЗ+2000*ВЗ

3. Введите в ячейки диапазона А7:А10 левые части ограничений, а в ячейки диапазона В7:В10 соответствующие правые части ограничений:

Ячейка	Формула	Ячейка	Значение
А7	=АЗ+2*ВЗ	В7	6
А8	=2*АЗ+ВЗ	В8	8
А9	=ВЗ-АЗ	В9	1
А10	=ВЗ	В10	2

4.Вызвать из меню Сервис команду Поиск решения и заполнить соответствующие поля контекстного меню.