Решение задачи линейного программирования в табличном процессоре Excel

 

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

Рассмотрим пример.

Решить следующую задачу линейного программирования:

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

1. Создание формы для ввода условий задачи.

2. Ввод исходных данных.

3. Ввод зависимостей из математической модели.

4. Ввод целевой функции, ограничений и граничных условий.

5. Решение задачи.

На рисунке 2 показаны: форма для ввода условий задачи, исходных данных, ограничений и целевой функции. В ячейку C3 вводим формулу: «=СУММПРОИЗВ(A$2:B$2;A3:B3)», а затем ее копируем в ячейки C4, C5, C6. Если задача содержит много ограничений и переменных, для отображения названий переменных при прокрутке, необходимо использовать команду «Закрепить области» из меню «Окно», предварительно выделив соответствующую строку.

 

Рисунок 1 - Форма для ввода условий задачи, данных и
зависимостей

После ввода данных в ячейки электронной таблицы выходим в диалоговое окно «Поиск решения»: «Сервис», «Поиск решения».

 

Рисунок 2 - Диалоговое окно «Поиск решения»

 

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

 

Рисунок 3 - Ввод ограничений

Если при вводе задачи возникает необходимость в изменении или удалении внесенных ограничений, то это делается с помощью команд «Изменить», «Удалить». Ввод условий задачи заканчивается.

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

 

Рисунок 4 - Диалоговое окно «Параметры поиска решения»

Устанавливаем флажок «Линейная модель», что обеспечивает применение симплекс-метода. Так как значения переменных неотрицательные, то устанавливаем флажок «Неотрицательные значения». Указанные максимальное время и число итераций подходит для решения большинства задач.Остальные параметры оставляем также без изменений. Нажимаем на кнопку «OK» и выходим в диалоговое окно «Поиск решения». Переходим к решению задачи, для этого выбираем команду «Выполнить». После поиска оптимального решения на экране появляется: диалоговое окно «Результаты поиска решения».

 

Рисунок 5- Диалоговое окно «Результаты поиска решения»

Результаты решения записываются в ячейки электронной таблицы A2 и B2. Оптимальное решение равно:

X ₁=7,2; X ₂=1,6.