2.3 Решение задач линейного программирования в ms Excel

Инструментом для решений задач оптимизации в MS Excel служит надстройка Поиск решения. Процедура поиска решения позволяет найти оптимальное значение формулы , находящейся в ячейке , которая называется целевой . Эта процедура работает с группой ячеек , прямо или косвенно связанных с формулой в целевом ячейки . Чтобы получить по формуле, находится в целевом ячейки , заданный результат , процедура изменяет значения в ячейках , что влияют.

Если данная надстройка установлена, то Поиск решения запускается из меню Сервис. Если такого пункта нет, следует выполнить команду Сервис → Надстройки … и выставить флажок против надстройки Поиск решения (рис. 8).

Решение задачи оптимизации состоит из нескольких этапов.

A. Создание модели задачи оптимизации .

B. Поиск решения задачи оптимизации .

C. Анализ найденного решения задачи оптимизации . Рассмотрим подробнее эти этапы .

Этап А.

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

Рис.8

Этап В.

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

Рис. 9

В окне Поиск решения являются следующие поля:

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

Равной - служит для выбора варианта оптимизации значения целевого ячейки ( максимизация , минимизация или подбор заданного числа). Чтобы установить число , введите его в поле.

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

Предположить - используется для автоматического поиска ячеек , влияющих на формулу , ссылка на которую дана в поле Установить целевую ячейку . Результат поиска отображается в поле Изменяя ячейки.

Ограничения - служит для отображения списка граничных условий поставленной задачи .

Добавить - служит для отображения диалогового окна Добавить ограничения.

Изменить - служит для отображения диалогового окна Изменить ограничения.

Удалить - служит для снятия указанного ограничения .

Выполнить - служит для запуска поиска решения поставленной задачи .

Закрыть - служит для выхода из окна диалога без запуска поиска решения поставленной задачи . При этом сохраняются установки сделаны в окнах диалога , появлявшихся после нажатий на кнопки Параметры , Добавить , Изменить или Удалить .

Параметры - служит для отображения диалогового окна Параметры поиска решения , в котором можно загрузить или сохранить модель, оптимизируется , и указать предусмотренные варианты поиска решения .

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

Для решения задачи оптимизации выполните следующие действия .

1 . В меню Сервис выберите команду Поиск решения.

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

3 . Чтобы максимизировать значение целевого ячейки путем изменения значений ячеек , влияющих установите переключатель в положение максимальной значению .

Чтобы минимизировать значение целевого ячейки путем изменения значений ячеек , влияющих установите переключатель в положение минимальному значению .

Чтобы установить значение в целевом ячейки равным некоторому числу путем изменения значений ячеек , влияющих установите переключатель в положение значению и введите в соответствующее поле требуемое число .

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

Автоматический поиск все ячейки , влияющие на формулу модели , нажмите кнопку предположить .

5 . В поле Ограничения введите все ограничения , наложенные на поиск решения.

6 . Нажмите кнопку Выполнить .

7 . Чтобы сохранить найденное решение , установите переключатель в диалоговом окне Результаты поиска решения в положение Сохранить найденное решение.

Чтобы восстановить исходные данные , установите переключатель в положение Восстановить исходные значения.

Этап С.

Для вывода итогового сообщения о результате решения используется диалоговое окно Результаты поиска решения.

Рис.3

Диалоговое окно Результаты поиска решения содержит следующие поля:

Сохранить найденное решения - служит для сохранения найденного решения в ячейках модели, влияют.

Восстановить исходные значения - служит для восстановления исходных значений ячеек модели, влияют.

Отчеты - служит для указания типа отчета , размещаемого на отдельном листе книги.

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

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

Ограничения. Используется для создания отчета , состоящий из целевого ячейки и списка ячеек модели , влияющих их значений , а также нижних и верхних границ . Такой отчет не создается для моделей , значения в которых ограничены множеством целых чисел. Нижней границей является наименьшее значение , что может содержать ячейку, влияет , в то время как значение других ячеек , влияющих фиксированные и удовлетворяют наложенным ограничениям. Соответственно, верхней границей называется наибольшее значение .

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

Рассмотрим несколько конкретных моделей линейной оптимизации и примеры их решения с помощью MS Excel.