Решение оптимизационных задач

EXCEL позволяет решать различные задачи оптимизации или, по другому, задачи линейного программирования. В этих задачах требуется найти некоторый оптимальный план выпуска определенной продукции при заданных ограничениях, накладываемые на предполагаемый результат. Например, количество выпускаемых изделий должно быть выражено целым числом.

Ранее такие задачи можно было решить с помощью специальных пакетов линейного программирования, таких, как, например, PLP-88.

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

Ниже приводятся вид диалогового окна, справка по его использованию и пример решения оптимизационной задачи, заимствованный из папки Program Files/Microsoft Office//Office10Samples/SOLVSAMP.XLS.

Элементы диалогового окна «Поиск решения»

Установить целевую ячейку. Служит для указания целевой ячейки, значение которой необходимо максимизировать, минимизировать или установить равным заданному числу. Эта ячейка должна содержать формулу.

Равно. Служит для выбора варианта оптимизации значения целевой ячейки (максимизация, минимизация или подбор заданного числа). Чтобы установить число, введите его в поле.

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

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

Ограничения. Служит для отображения списка граничных условий поставленной задачи.

Добавить. Служит для отображения диалогового окна Добавить ограничение.

Изменить. Служит для отображения диалоговое окна Изменить ограничение.

Удалить. Служит для снятия указанного ограничения.

Выполнить. Служит для запуска поиска решения поставленной задачи.

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

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

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

5.3. Пример решения задачи оптимизации

Завод производит электронные приборы трех видов: прибор А, прибор В и прибор С, используя при их сборке микросхемы трех типов: тип 1, тип 2 и тип 3. Расход микросхем задается следующей таблицей:

	Прибор А	Прибор В	Прибор С
Тип 1
Тип 2
Тип 3

Стоимость изготовленных приборов одинакова. Ежедневно на склад завода поступает 500 микросхем типа 1 и по 400 микросхем типа 2 и 3. Каково оптимальное соотношение дневного производства приборов различного вида, если производственные мощности позволяют использовать запас поступивших микросхем полностью?

Создайте документ "Лист Microsoft Excel", задайте имя "Оптимизация", откройте его и на листе 1 документа наберите следующую таблицу:

A	B	C	D	E	F	G
			Прибор А	Прибор В	Прибор С

Тип 1
Тип 2
Тип 3

Выполните следующие действия:

1. Впишите во вторую строку в ячейки D2, E2 и F2 нули, а в ячейку G2 – формулу, в которой должна получиться сумма значений в диапазоне ячеек D2:F2. Таблица должна принять следующий вид:

A	B	C	D	E	F	G
			Прибор А	Прибор В	Прибор С
						=СУММ(D2:F2)
Тип 1
Тип 2
Тип 3

2) Впишите в ячейки C3, C4 и C5 формулы с тем, чтобы таблица приняла следующий вид:

A	B	C	D	E	F	G
			Прибор А	Прибор В	Прибор С
						=СУММ(D2:F2)
Тип 1		=D$2D3+E$2E3+F$2*F3
Тип 2		=D$2D4+E$2E4+F$2*F4
Тип 3		=D$2D5+E$2E5+F$2*F5

3. С помощью пункта меню Сервис / Надстройки… выберите вставку нового приложения "Поиск решения" и нажмите кнопку "ОК". Сделайте активной ячейку G2. Теперь с помощью нового пункта меню Сервис / Поиск решения… выполните переход к диалоговому окну Поиск решения. Данное диалоговое окно заполняется по следующему образцу: