Технология решения оптимизационных задач в среде MS Excel 2007

 

Рассмотрим порядок действий при решении задач с помощью надстройки «Поиск решения» на примере конкретной задачи [Хазанова].

Предприятие производит изделия трех видов, поставляет их заказчикам и реализует на рынке. Заказчикам требуется 1000 изделий первого вида, 2000 изделий второго вида и 2500 изделий третьего вида.

Условия спроса на рынке ограничивают число изделий первого вида 2000 единицами, второго — 3000 и третьего — 5000 единицами.

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

Как организовать производство, чтобы:

1. обеспечить заказчиков;

2. не допустить затоваривания;

3. получить максимальную прибыль?

 

Таблица 1 – Данные о количестве ресурсов и размере прибыли

Тип ресурсов	Вид изделий	Всего ресурсов
				25 000 000
				30 000 000
				20 000 000
				40 000 000
Прибыль

 

Построение экономико - математической модели задачи.

Управляющие переменные:

Х₁ — число изделий первого вида;

X₂ число изделий второго вида;

X₃ — число изделий третьего вида;

Ограничения: обеспечить заказчиков, не превысить запас ресурсов, не допустить затоваривания рынка.

В соответствии с этими ограничениями выпишем область допустимых решений задачи:

(3)

 

Первые три неравенства в системе (3) соответствуют спросу заказчиков. Неравенства с четвертое по шестое формализуют спрос на рынке. Последние четыре неравенства соответствуют ограничениям по ресурсам.

Целевая функция или критерий эффективности задачи имеет вид:

 

(4)

 

После того как модель задачи составлена, приступаем к решению. Для этого выполняется следующий алгоритм:

1. В Excel создаем текстовую форму для решения задачи (рис. 2)

 

Рисунок 2 – Текстовая форма для решения задачи

 

Все значения, внесенные в таблицу, были взяты из условий задачи.

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

2. Формируется зависимость для левой части каждого из неравенств системы ограничений и целевой функции. Для первого неравенства порядок действий такой:

Курсор в ячейку E6;

Во вкладке «Формулы» нажать кнопку «Вставить функцию» (рис. 3)

 

Рисунок 3 – Библиотека функций

 

В появившемся диалоговом окне в строке «Категория» выбрать «Математические», функция «СУММПРОИЗВ» (рис. 4А), нажать кнопку «OK».

А

Б

Рисунок 4 – Мастер функций

 

В строку «Массив1» вводятся значения коэффициентов при переменных в неравенстве, а в строку «Массив2» - значения самих переменных (рис. 4Б). Нажать кнопку «OK».

Аналогично заполняются ячейки E7:E15 и ячейка целевой функции E17.

3. Поиск оптимального решения.

Установить курсор в ячейку E17. Во вкладке «Данные» выбрать команду «Поиск решения» (рис. 1). В появившемся диалоговом окне настраиваем параметры решения (рис. 5).

Рисунок 5 – Диалоговое окно надстройки «Поиск решения»

 

В качестве целевой ячейки установлена автоматически та, на которой стоял курсор.

В строке «Равной:» переключатель устанавливаем на «максимальному значению» (если бы задача была связана с поиском минимума целевой функции, то переключатель был бы установлен на «минимальному значению»).

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

Заполнение строки «Ограничения:» производится следующим образом:

1. Нажать кнопку «Добавить»;

2. В появившемся диалоговом окне «Добавление ограничения» (рис. 6) в строку «Ссылка на ячейку:» добавляется ссылка на ту ячейку, в которой установлена зависимость для левой части данного ограничения. В строку «Ограничение:» добавляется ссылка на ячейку, в которой указано значение правой части этого неравенства. Между строками «Добавление ограничения» и «Ссылка на ячейку:» устанавливается знак в соответствии с условиями задачи.

Рисунок 6 - Диалоговое окно «Добавление ограничения»

 

3. Нажать кнопку «Добавить», если нужно добавить следующее ограничение, или кнопку «ОК», если введенное ограничение было последним.

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

Следующее действие – настройка параметров поиска решения. Нажать кнопку «Параметры» и в появившемся диалоговом окне установить флажки на строках «Линейная модель» и «Неотрицательные значения», нажать кнопку «ОК» (рис. 7).

 

Рисунок 7 – Диалоговое окно «Параметры поиска решения»

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

В появившемся диалоговом окне «Результаты поиска решения» нажать кнопку «ОК» (рис. 8).

Рисунок 8 - Диалоговое окно «Результаты поиска решения»

Ячейки значений управляющих переменных, целевой функции и зависимостей для ограничений будут заполнены (рис. 9).

 

Рисунок 9 – Решение задачи.

Таким образом, предприятие получит максимальную прибыль при следующем плане производства: изделия первого вида 2 000 шт., второго вида 3 000 шт., третьего – 5 000 шт. При этом спрос на рынке будет удовлетворен полностью, а ресурсы использованы частично. Так, остаток ресурса 1 составит 18 100 000 ед., ресурса 2 – 28 700 000 ед., ресурса 3 –
17 800 000 ед., а ресурса 4 – 37 200 000 ед.