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

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

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

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

1. Создание модели задачи оптимизации.
2. Поиск решения задачи оптимизации.
3. Анализ найденного решения задачи оптимизации.

Рассмотрим подробнее эти этапы.

Этап А.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. В меню Сервис выберите команду Поиск решения.
2. В поле Установить целевую ячейку введите адрес или имя ячейки, в которой находится формула оптимизируемой модели.
3. Чтобы максимизировать значение целевой ячейки путем изменения значений влияющих ячеек, установите переключатель в положение максимальному значению.

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

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

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

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

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

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

Информационные технологии в экономике Этап С.

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

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

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

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

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

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

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

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

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

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

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