Практическая работа № 5. « Расчет задач линейного программирования с использованием MS Excel»

Цель работы: Формирование умения решать задачи линейного программирования (ЛП) в табличном редакторе Microsoft Excel.

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

1. Ввести условие задачи:

a) создать экранную форму для ввода условия задачи:

· переменных,

· целевой функции (ЦФ),

· ограничений,

· граничных условий;

b) ввести исходные данные в экранную форму:

· коэффициенты ЦФ,

· коэффициенты при переменных в ограничениях,

· правые части ограничений;

c) ввести зависимости из математической модели в экранную форму:

· формулу для расчета ЦФ,

· формулы для расчета значений левых частей ограничений;

d) задать ЦФ (в окне "Поиск решения"):

· целевую ячейку,

· направление оптимизации ЦФ;

e) ввести ограничения и граничные условия (в окне "Поиск решения"):

· ячейки со значениями переменных,

· граничные условия для допустимых значений переменных,

· соотношениямежду правыми и левыми частями ограничений.

2. Решить задачу:

a) установить параметры решения задачи (в окне "Поиск решения");

b) запустить задачу на решение (в окне "Поиск решения");

c) выбрать формат вывода решения (в окне "Результаты поиска решения").

Итак, для решения задач оптимизации в MS Excel имеется пакет "Поиск решения».

Пример 5.1. Рассмотрим использование данного пакета для решения задачи технического контроля.

Можно сформулировать следующую задачу:

минимизировать F(x)=40x₁+36x₂

при ограничениях:

x₁≤8, x₂≤10,

5x₁+3x₂≥45,

x₁≥0, x₂≥0.

Рассмотрим алгоритм решения задачи:

Шаг 1. Ввод данных:

Создание экранной формы и ввод в неё условия задачи. Экранная форма для ввода условий задачи вместе с введёнными в неё данными представлена на рис. 5.1.

Рис.5. 1. Экранная форма задачи

В ячейку D6 вводим целевую функцию = 40x1+36x2. Для этого воспользуемся функцией СУММПРОИЗ(). Для вызова этой функции набираем команду Формулы/Математические/СУММПРОИЗВ. Появится окно Аргументы функции (см. рис. 5.2). В поле Массив1 вводим диапазон ячеек B3:C3 (аргументы функции; в поле Массив2 вводим диапазон ячеек B6:C6 (коэффициенты целевой функции) и щелкаем по кнопке ОК.

Рис. 5.2. Окно Аргументы функции.

Вводим целевую функцию. Аналогично вводим функции для ограничений (см. рис. 5.3).

Рис.5.3. Ввод ограничений

Рис. 5.4. Экранная форма задачи после ввода всех необходимых формул

Шаг 2. Решить задачу:

· Установить параметры решения задачи (в окне "Поиск решения");

· Запустить задачу на решение (в окне "Поиск решения");

· Выбрать формат вывода решения (в окне "Результаты поиска решения").

Выполняем первый пункт. На вкладке Данные в группе Анализ выбираем команду Поиск решения. В окне Параметры поиска решения в поле Оптимизировать целевую функцию вводим ячейку, содержащую целевую функцию. Она находится в ячейке D6. В группе кнопок ДО выбираем кнопку Минимум, в поле Изменяя значения переменных вводим диапазон ячеек, где находятся аргументы нашей задачи. В поле В соответствии с ограничениями вводим ограничения. Для этого щелкаем по кнопке Добавить. Появится окно Добавление ограничения. Вводим первое ограничение (см. рис. 5.5) и щелкаем по кнопке Добавить появится новое окно Добавление ограничения, вводим второе ограничение и щелкаем снова по кнопке Добавить, вводим третье ограничение. Так как все ограничения введены, щелкаем по кнопке ОК. Появится окно Параметры поиска решения, где в окне Параметры поиска решения мы видим введённые ограничения см. рис.5. 6.

Рис. 5.5. Вводим первое ограничение в окно Добавление ограничения

Далее в поле Выберите метод решения окна Параметры поиска решения в списке методов выбираем метод решения – Поиск решения линейных задач симплекс методом и щелкаем по кнопке Найти решение.

Рис. 5.6. Окно "Поиск решения" для задачи технического контроля

Появится окно Результаты поиска решения (справа на рис.5.7). В ячейках B3 и C3 находится решение нашей задачи: x₁=8, x₂=1,67.

Рис. 5.7. Сообщение об успешном решении задачи и результаты решения

Таким образом, при оптимальном режиме работы ОТК необходимо использовать восемь контролеров разряда 1 и 1.67 контролеров разряда 2. Дробное значение х₂=1.6 соответствует использованию одного из контролеров разряда 2 в течение неполного рабочего дня. При недопустимости неполной загрузки контролеров дробное значение обычно округляют, получая приближенное оптимальное целочисленное решение х_1opt=8, х_2opt=2, F_min=380.