Решение задач оптимизации с помощью MS Excel

Решение задач оптимизации в Microsoft Excel производится с помощью надстройки «Поиск решения».

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

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

Рассмотрим процесс решения задач оптимизации на прмерах двух задач: задачи планирования производства, и транспортной задачи, используя возможности таблиц Excel.

Решение задачи планирования производства

Рассмотрим задачу, приведенную в примере 6.2.

Математическая модель этой задачи, как описано выше имеет вид:

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

Последовательность действий:

1. Подготовим таблицу как показано на рис.6.15. Ячейки, содержащие целевую функцию (А10) и изменяемые ячейки (проектные параметры x₁, x₂) В10: С10 тонируем. Для контроля счета в ячейки В10: С10 введем единицы. Значения проектных параметров х ₁=1 и х ₂=1 можно рассматривать как начальное приближение решения задачи.

2. Систему ограничений (6.12) запишем в виде, как показано на рис.6.15, т.е.:

a_i x₁ + b_i x₂ - d_i ≥ 0, i=1, 2, 3, 4, 5.

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

3. В ячейки В3: D7 введем коэффициенты системы ограничений.

Рис.6.15. Расчетная схема для примера 6.2

4. В ячейку E3 введем формулу для вычисления левой части 1-го ограничения, т.е.

E3=СУММПРОИЗВ($B$10: $C$10; B3: C3)+D3,

и после ввода скопируем ее вниз до конца таблицы. Будет не лишним проверить результаты счета для заданных значений х₁ =1 и х ₂=1.

5. Формулу для целевой функции запишем в ячейке A10:

A10=1-В10-С10.

6. Выберем команду меню Данные\Поиск решения и в появившемся окне сделаем соответствующие установки (рис.6.16). Ограничения устанавливаются с помощью кнопки Добавить, которая вызывает окно Добавление ограничения для ввода этих ограничений (см. рис. 6.16).

Рис.6.16. Окно «Поиск решения»

7. После щелчка на кнопке Выполнить, при условии, что введено все верно, результат решения будет иметь вид, как показано на рис.6.17.

Рис.6.17. Результаты решения примера 6.2

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