Решение задач оптимизации в Excel

Если вы научились строить математические модели задач линейного программирования, но не умеете аккуратно проводить вычисления, особенно в дробях, либо задача имеет не очень простую по структуре модель, то можно использовать специальную надстройку Excel "Поиск решения", которая позволяет решать задачи на поиск оптимального значения целевой функции. Надстройка вызывается из меню Сервис, пункт Поиск решения. Если в меню Сервис нет такого пункта, то можно установить его, выполнив команду Сервис | Надстройки. В окне диалога "Надстройки" установите флажок на строке Поиск решения и нажмите OK.

Рассмотрим задачу, аналогичную задаче 8 (о плане). Пусть условие задачи записано в таблице на листе Excel. Модель задачи имеет вид:

Для решения задачи заполните лист Excel, выполнив следующие действия:

1. Определим ячейки для хранения переменных В7 и С7 (изменяемые ячейки), первоначально зададим им нулевые значения.

2. Определим ячейку для целевой функции В8, введем в нее формулу, определяющую целевую функцию. Не забудьте, любая формула в Excel начинается со знака «=».

3. Введем в ячейки А14:А16 левые части формул ограничений. Если использовать абсолютные ссылки на изменяемые ячейки, то формулу можно ввести один раз и копировать, используя маркер заполнений. Каждый раз при нажатии на enter формулы пересчитываются и в ячейке стоит значение 0.

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

5. Аккуратно заполните поля ввода для целевой функции, изменяемых ячеек. Активизируйте кнопку Равной максимальному значению. (Если бы задача была на поиск минимального значения, то Равной минимальному значению). В поле ввода Изменяя ячейки введите В7:С7 и нажмите на кнопку Добавить в окне Ограничения.

6. Окно сворачивается в строку, состоящую из трех полей ввода. В первое поле вводим ссылаемся на ячейку первого ограничения, во-втором выбираем знак «£», в третьем ссылаемся на ячейку, содержащую правые части ограничения, можно ввести цифрой, но удобнее сослаться. Команда Добавить очищает поля для ввода следующего ограничения. Не забудьте ввести условия неотрицательности для изменяемых ячеек и целостности, если в этом есть необходимость по смыслу задачи. После ввода всех ограничений нажмите «ОК» (см. рис. 4.1).

7. Кнопка Выполнить на форме позволяет запустить процесс поиска решения. Можно предварительно установить нужные параметры поиска, кнопка Параметры.

Рис. 4.1Пример оформления задачи и ввода первоначальных данных

8. Получаем сообщение Решение найдено. На листеExcel в изменяемых ячейках и целевой будут находиться оптимальные решения задачи. Удобно запросить отчет по результатам (см. рис. 4.2). В автоматически сформированном отчете кроме значений переменных и целевой функции, каждому ограничению присваивается статус «связанное» или «несвязанное», что характеризует дефицитность ресурса. Если ограничение несвязанное, то соответствующий ресурс не является дефицитным, имеется в избытке, в графе разница явно указан излишек. Если ограничение связанное, то ресурс дефицитен, излишков нет.

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

Рис.4.2.Окно диалога "Результаты поиска решений"