2. Поиск решения.

Функционирование процедуры поиска решения рассмотрим на примере.

Задача. Составить производственную программу выпуска изделий А и В по критерию максимальной рентабельности при заданных ограничениях производственной мощности четырёх цехов завода. Оба изделия последовательно обрабатываются в цехах. Время возможной работы цехов составляет соответственно 12, 8, 16 и 12 часов за плановый период, а нормы затрат времени на изготовление единицы товара А по цехам соответственно равна 2, 1, 4 и 0 часов, а на изделие В – 2, 2, 0 и 4 часов. Прибыль о реализации единицы продукции типа А составляет 2 грн., а типа В – 3 грн.

Решение.

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

	Изделие А	Изделие В	Норма затрат времени, час.
1 цех	2	2	12
2 цех	1	2	8
3 цех	4	0	10
4 цех	0	4	12
Прибыль	2 грн	3 грн.	max

Тогда математическую модель можно записать в виде системы линейных неравенств:

Где х₁, х₂ – количество изделий А и В соответственно.

Целевая функция будет иметь вид: .

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

Перед запуском процедуры поиска решения входные данные должны быть поданы в виде таблицы с установленными зависимостями между данными (в формульном режиме таблица представлена на рис. 5).

Ввходные данные записаны в диапазон В3:D7. В этих ячейках находятся коэффициенты при неизвестных х₁, х₂ системы линейных неравенств (строки 3-6 рабочего листа Excel) и целевой функции (строка 7), а также соответствующие свободные члены системы (в столбце С «Норма затрат времени»). В ячейках В9 и С9 будут находиться искомые решения неизвестных х₁, х₂ (в начальном варианте принимаем эти значения равными 0). Формула по которой вычисляется целевая функция, находится в ячейке Е7. В ячейках Е3:Е6 записаны формулы, по которым можно вычислить время которое затрачивается на изготовление изделий в каждом из цехов (взаимосвязь между коэффициентами на неизвестные системы).

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

В поле Установить целевую ячейку введите адрес ячейки, значение которой будет использоваться как критерий оптимизации (в нашем случае – ячейка Е7, в которой находится формула для вычисления прибыли от реализации продукции. Значения в этой ячейке должно быть максимальным (для этого необходимо активировать соответствующий переключатель максимальному значению в области Равной:).

В поле Изменяя ячейки укажите, в каких ячейках программа должна изменять значения для получения оптимального решения (в нашем решении это ячейки В9:С9, в которых будет находиться решение задачи х₁ и х₂).

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

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

В поле Ссылка на ячейку необходимо ввести адрес ячейки, на содержание которой накладывается определённое ограничение (в нашем случае – это линейная комбинация неизвестных и соответствующих коэффициентов, которые определяют затраты времени на изготовление обеих деталей для каждого из цехов). В поле Ограничения можно ввести либо конкретное ограничивающее число, либо адрес ячейки, где находится ограничение. Между этими полями необходимо выбрать необходимый оператор (<=, =, >=, цел., двоич.), который устанавливает отношения между ячейкой, на которую накладываются ограничения, и самим ограничением. После ввода каждого из ограничений необходимо нажимать кнопку Добавить.

После введения всех ограничений в поле Ограничения окна Поиск решения появятся строки с заданными ограничениями.

Если все параметры процедуры заданы, можно переходить к непосредственному решению задачи, которое начнётся после нажатия кнопки Выполнить.

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

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

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

Ответ: х₁=4, х₂=2, z=14.