Этап В

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

Рис. 9

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Этап С.

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

Рис.3

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

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

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

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

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

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

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

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

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

Задача о планировании производства

Постановка задачи. Предприятие должно производить изделия n видов: u₁, u₂,...и_п, причем количество каждого выпускаемого изделия, не должна превысить спрос β₁, β₂,... β_n одновременно не должно быть меньше запланированных величин b₁,b₂,...,b_n соответственно, на изготовление уходит m видов сырья s_1, s_2, ..., s_m, запасы которых ограничены соответственно величинами. . Известно, что на изготовление i - ro изделия идет единиц j - го сырья. Прибыль, получаемая от реализации изделий u_l, u_2, ..., u_n равна соответственно с_1, с_2, ...,с_п. Нужно так спланировать производство изделий, чтобы прибыль была максимальной и при этом выполнялся план на производство каждого изделия, но не превышался спрос на него.

Математическая модель. Обозначим через х₁,х₂,...х_п количества единиц изделий и₁, и₂,...и_п, выпускаемых предприятием. Прибыль, принесенный планом (целевая функция), будет равна:

Ограничения на выполнение плана запишется в виде: для i = 1,2,..., n. Чтобы не превысить спрос, надо ограничить выпуск изделий: , для j = 1,2,..., n. Чтобы не превысить спрос, надо ограничить выпуск изделий:

при условии, что x₁,x_2,...,x_n неотрицательные.

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

Условие задачи. Предприятие выпускает два вида железобетонных изделий: лестничные марши и балконные плиты. Для производства одного лестничного марша нужно потратить 3,5 куб.м. бетона и 1 упаковки арматуры, а для производства плиты - 1 куб.м. бетона и 2 упаковки арматуры. На каждую единицу продукции приходится 1 человеко -день трудозатрат. Прибыль от продажи 1 лестничного марша составляет 200грн., А одной плиты - 100 грн. На предприятии работает 150 человек, причем известно, что в день предприятие производит не более 350 куб.м. бетона и завозится не больше 240 упаковок арматуры. Требуется составить такой производственный план, чтобы прибыль от продукции был максимальным.

Решение:

1. На письме книги MS Excel заполните таблицу параметров задачи (р.10).

2. Создайте модель задачи и заполните ячейки для значений переменных (сначала ячейки х1 и х2 заполняются произвольными числовыми значениями, например, значением 10), целевой функции (ячейка содержит формулу) и ограничений (ячейки содержат формулы) (рис. 10).

3. Выполните команду Сервис ► Поиск решения и установите необходимые значения в полях диалогового окна Поиск решения, добавляя ограничения в окне Добавление ограничений.

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

4. Нажмите на кнопку выполнять и установите параметры в окне Результаты поиска решения (переключатель Сохранить найденное решение или Восстановить исходные значения и Тип отчета).

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

5. В результате в ячейках с переменными задачи появятся значения, соответствующие оптимальному плановые (80 лестничных маршей и 70 плит перекрытия в день), а в ячейке для целевой функции - значение прибыли (23 000 грн.), Что соответствует данному плановые (рис. 11).

рис. 11

6. В случае если полученное решение является удовлетворительным, можно сохранить оптимальный план и ознакомиться с результатами поиска, выводятся на отдельный лист (рис.12)

Рис. 12

Если данную модель предполагается применять неоднократно, то ее можно сохранить для последующего использования. Для этого нужно выполнить следующие действия.

1. Сформулируйте задачу.

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

3. Нажмите кнопку Параметры.

4. Нажмите кнопку Сохранить модель.

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

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

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

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

2. Нажмите кнопку Параметры.

3. Нажмите кнопку Загрузить модель.

4. Введите ссылку на весь диапазон ячеек с областью модели