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

Для решения задач линейного программирования в MS Excel имеется надстройка Поиск решения. По умолчанию в Excel надстройка Поиск решения отключена. Чтобы активизировать ее в MS Excel 2007, щелкните значок Кнопка Microsoft Office, щелкните Параметры MS Excel, а затем выберите категорию Надстройки. В поле Управление выберите значение Надстройки MS Excel и нажмите кнопку Перейти (см. рис. 5.1).

В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК (см. рис. 5.2).

В Excel 2003 и ниже выберите команду Сервис → Надстройки, в появившемся диалоговом окне Надстройки установите флажок Поиск решения и щелкните на кнопке ОК. Если вслед за этим на экране появится диалоговое окно с предложением подтвердить ваши намерения, щелкните на кнопке Да. (Возможно, вам понадобится установочный компакт-диск MSOffice).

рис. рис 5.1

рис. 5.2

Рассмотрим использование данной надстройки для решения задачи линейного программирования из примера 6.

1. Для решения задачи с помощью функции Поиск решения необходимо внести исходные данные – это диапазон ячеек (A5:F10) на рис. 5.3.

рис. 5.3

2. Затем определить ячейки для результата решения задачи-неизвестные величины x_j, отражающие план производства изделий A, B, С и D. Ячейки для неизвестных величин заполнить нулями – это диапазон ячеек (B15:E15) на рис. 5.3.

рис. 5.3

3. В ячейке F15 для вычисления значения общей стоимости всей произведенной предприятием продукции - ввести формулу =СУММПРОИЗВ(В10:E10;В15:E15), которая находит сумму попарных произведений ячеек с ценами (В10:E10) на ячейки со значениями неизвестных величин (В15:E15).

4. Для задания ограничений по видам сырья в ячейку G7 скопировать формулу из ячейки F15. Она скопируется в виде СУММПРОИЗВ(C2:F2;C7:F7). Необходимо заменить диапазон (C2:F2) на диапазон параметров расхода сырья (В7:E7), а диапазон (C7:F7) на диапазон (В15:E15)- значения неизвестных величин.

Диапазон (В15:E15) преобразуется к абсолютному виду для удобства дальнейшего копирования формулы в ячейки с ограничениями. Для такого преобразования необходимо при наборе формулы после выделения нужного диапазона ячеек в таблице (В15:E15) нажать клавишу F4, чтобы получилась формула =СУММПРОИЗВ(B7:E7;$B$15:$E$15).

Для задания остальных ограничений скопировать вновь введенную формулу в ячейки G8 и G9. На рис. 5.3 ячейки содержащие формулы закрашены серым цветом.

5. После создания таблицы с исходными данными установить курсор в ячейку с формулой целевой функции (F15) и выбрав в меню Сервис функцию Поиск решения в Excel 2003 или Данные → Анализ → Поиск решения в Excel 2007. Затем заполнить поля в появившемся окне

рис. 5.3

в поле Установить целевую ячейку должен появиться адрес ячейки с формулой целевой функции (в данном случае $F$15);

•установить переключатель вида оптимизации в поле Равной: в положение максимальное (минимальное) значение, при необ­ходимости найти максимум или минимум целевой функции;

• в поле Изменяя ячейки указать диапазон ячеек со значениями неизвестных задачи, выделив его в таблице. В данном примере это ячейки $B$15:$E$15;

• в поле Ограничения задать вид и значения ограничений. Для этого установить курсор в поле ввода ограничений и нажать кнопку Добавить. После чего в появившемся окне Добавление ограничения ввести в поле Ссылка на ячейку адрес ячейки с формулой соответствующего ограничения (например, $G$7 для ресурса I –го вида). Затем ввести в поле Огра­ничение предельное значение соответствующего ресурса (оно находится в ячейке $F$7) и выбрать вид от­ношения (<, >, = и т.п.). (см. рис. 5.4).

	рис. 5.4

После нажатия кнопки Добавить (или OK для ввода послед­него ограничения) данное ограничение попадает в список огра­ничений задачи.

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

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

В этом окне можно так же определить параметры процесса решения: предельное время поиска решения, максимальное ко­личество итераций, точность и т.п.

Флажок Показывать результа­ты итераций позволяет по шагам следить за поиском решения.

Флажок Автоматическое масштабирование включается в том случае, когда разброс значений параметров очень велик.

рис. 5.5

Дата добавления: 2014-12-26