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

(Руководство пользователя)

Решение задач линейного программирования с использованием Excel 7.0 осуществляется с помощью инструментального средства Поиск решения. Для запуска этого инструмента выполните команду Сервис/Настройки. Появится окно диалога «Настройки», в котором установите флажок на строке Поиск решения и нажмите кнопку ОК.

После загрузки инструмента Поиск решения в списке опций ниспадающего меню Сервис появится новая команда Поиск решения. В результате выполнения этой команды появится окно диалога «Поиск решения».

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

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

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

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

Предположим, что предприятие выпускает два вида продукции. Цена единицы I-го вида продукции равна 25000 р., II-го вида – 50000 р. При производстве продукции используются три вида сырья, запасы которого оцениваются в 37, 57.6 и 7 условных единиц. Для каждого вида сырья определён коэффициент его расхода при производстве единицы продукции. Соответствующие коэффициенты приведены в табл.П.3.1. В этой же таблице приведена цена одной единицы продукции I-го и II-го видов.

Таблица П.3.1

Обозначим количество произведённой продукции I вида через с1, II вида – с2. В этом случае показатель качества распределения ресурсов (или, по-другому, целевая функция) есть выражение следующего вида:

I(c1,c2) = 25000*c1 + 50000*c2.

Эта функция позволяет количественно оценивать стоимость произведённой продукции. В соответствии с содержательной постановкой задачи на величины с1 и с2 накладываются ограничения следующего вида:

1.2*c1 + 1.9*c2 <= 37;

2.3*c1 + 1.8*c2 <= 57.6;

0.1*c1 + 0.7*c2 <= 7;

c1 >= 0; c2 >= 0.

Задача линейного программирования формализована. Теперь можно приступать к её решению с помощью табличного процессора Excel 7.0.

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

1. Введите в ячейку А1 формулу для целевой функции: =25000*c1 + 50000*c2.

2. Введите в ячейку А3 формулу для ограничения: =1.2*c1 + 1.9*c2.

3. Введите в ячейку А4 формулу для ограничения: =2.3*c1 + 1.8*c2.

4. Введите в ячейку А5 формулу для ограничения: =0.1*c1 + 0.7*c2.

5. Введите в ячейку А6 формулу для ограничения: = с1.

6. Введите в ячейку А6 формулу для ограничения: = с2.

7. Введите в ячейки С1:С2 начальные значения переменных. В рассматриваемой задаче их значение равняется нулю.

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

9. В поле ввода Установить целевую ячейку введите ссылку на ячейку А1 (необходимо ввести следующую запись - $A$1).

В поле ввода Изменяя ячейки укажите ссылки на ячейки С1:С2 (необходимо ввести следующую запись = $C$1:$C$2).

11. Начинаем вводить информацию в поле ввода Ограничения. Нажмите кнопку Добавить. Появится окно диалога «Добавить ограничения». В поле ввода Ссылка на ячейку введите ссылку на ячейку А3 (вводится $A$3). В поле ввода Ограничение введите <= и число 37.

12. Воспользуйтесь кнопкой Добавить для ввода остальных ограничений. Для изменения ограничения установите на него курсор и нажмите кнопку Изменить.

13. Нажмите кнопку Выполнить. После окончания расчёта Excel откроет окно диалога «Результаты поиска решения».

14. Выберите в окне «Тип отчёта» Результаты и нажмите кнопку ОК. Перед тем листом, где записана постановка задачи, будет вставлен лист «Отчёт по результатам 1», а на экране будут представлены результаты решения рассматриваемой задачи линейного программирования. В ячейках С1 и С2 отображаются значения переменных, которые максимизируют целевую функцию I(c1, c2).

15. Нажмите мышью ярлык «Отчёт по результатам 1». На экране ПЭВМ появится отчёт Excel о решённой задаче. Следует обратить внимание, что в данной задаче при полученном плане производства продукции с1 и с2 (19,38 и 7,23 соответственно), выделенные ресурсы 1, 2, 3 видов оказываются полностью израсходованными.

16. Для исследования устойчивости полученных решений к изменению исходных данных и определения пределов изменения оптимальных решений при сохранении структуры оптимального плана выпуска продукции необходимо в окне «Тип отчёта» выбрать Устойчивость, Пределы и получить соответствующие отчёты.