В поле с именем Изменяя ячейки:ввести абсолютный адрес ячеек $B$2:$E$2.

Методика решения линейной модели на MS Excel

Приведем пример использования возможностей Excel 2003 для решения оптимизационных задач.

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

Элемент модели	Вид продукции	Располагаемый ресурс
A	B	C	D
Ресурсы:
Обязательный минимум вы- пуска продукции			не лимитировано	не лимитировано	–
Прибыль от реализации единицы продукции, тыс. тнг					–
План	х 1	х 2	х 3	х 4	–

 

Экономико-математическая модель задачи имеет следующий вид:

Целевая функция

Z = 3· х ₁ + 3· х ₂ + 4· х ₃ + 5· х ₄ ® max.

Ограничения по ресурсам

2· х ₁ + 2· х ₂ + 3· х ₃ + 4· х ₄ ≤ 200;

х ₁ + 3· х ₂ + 5· х ₃ + 2· х ₄ ≤ 400;

3· х ₁ + 4· х ₂ + 6· х ₃ + 6· х ₄ ≤ 600.

Ограничения по плану производства

х ₁ ³ 20;

х ₂ ³ 25;

x_j ³ 0,

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

1. Внесем необходимые надписи в ячейки A1:F1, A2:A9, B4, G2, F4:I4. Следует отметить, что конкретное содержание этих надписей не оказывает никакого влияния на решение рассматриваемой задачи ЛП.

2. В ячейки B3:E3 введем значения коэффициентов целевой функции: с₁ = 3, с₂ = 3, с₃ = 4, с₄ = 5.

3. В ячейку F2 введем формулу: =СУММПРОИЗВ(B2:E2;B3:E3), которая представляет целевую функцию.

4. В ячейки B5:E9 введем значения коэффициентов ограничений.

5. В ячейки H5:H9 введем значения правых частей ограничений: b₁ = 200, b₂ = 400, b₃ = 600, b₄ = 20, b₅ = 25.

6. В ячейку F5 введем формулу: =СУММПРОИЗВ($B$2:$E$2;B5:E5), которая представляет левую часть первого ограничения.

Скопируем формулу, введенную в ячейку F5,в ячейки F6 – F9.

8. В ячейку I5 введем формулу: =H5–F5, которая представляет возможный недоиспользованный ресурс.

Скопируем формулу, введенную в ячейку I5,в ячейки I6 – I9.

Внешний вид рабочего листа Excel с исходными данными для решения рассматриваемой задачи имеет следующий вид (рис. 1.1):

Рис. 1.1. Исходные данные для решения задачи

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

После появления диалогового окна Поиск решения следует выполнить следующие действия:

В поле с именем Установить целевую ячейку:ввести абсолютный адрес ячейки $F$2.

2. Для группы Равной: выбрать вариант поиска – максимальному значению.

В поле с именем Изменяя ячейки:ввести абсолютный адрес ячеек $B$2:$E$2.

4. Добавить 5 ограничений и условие неотрицательности переменных. С этой целью выполнить следующие действия:

· для задания ограничений в исходном диалоговом окне Поиск решения нажать кнопку с надписью Добавить (рис.1.2);

· в появившемся дополнительном окне выбрать диапазон ячеек $F$5:$F$7, который должен отобразиться в поле с именем Ссылка на ячейку;

· в качестве знака ограничения из выпадающего списка выбрать нестрогое неравенство «<=»;

Рис. 1.2. Диалоговое окно Поиск решения

· в качестве значения правой части этих ограничений выбрать диапазон ячеек $H$5:$H$7;

· для добавления 4-го и 5-го ограничений в дополнительном окне нажать кнопку с надписью Добавить ( рис. 1.3).

Рис. 1.3. Дополнительное окно Добавление ограничения

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

· в дополнительном окне выбрать диапазон ячеек $B$2:$E$2, который должен отобразиться в поле с именем Ссылка на ячейку;

· в качестве знака ограничения из выпадающего списка выбрать нестрогое неравенство «>=»;

· в качестве значения правой части ограничения в поле с именем Ограничение: ввести значение 0;