Лабораторная работа №3-4. Задачи оптимизации в MS Excel

Цель работы: получить навыки решения оптимизационных задач средствами MS Excel.

Содержание работы:

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

2. Формирование базовых таблиц.

3. Использование функции «Поиск решения»

Методические рекомендации

Последовательность работ рассмотрим на примере задачи распределения ресурсов.

Фирма выпускает продукцию четырех типов Продукт1, Продукт2, Продукт3, Продукт4, для изготовления которой требуются ресурсы трех видов: трудовые, сырье, финансы. Количество ресурса каждого вида, необходимое для выпуска единицы продукции данного типа, называется нормой расхода. Норма расхода, а также прибыль, получаемая от реализации единицы каждого типа продукции, приведены в табл., там же приведено наличие располагаемого ресурса. Требуется определить, в каком количестве надо выпускать продукцию каждого типа, чтобы суммарная прибыль была максимальной (таблица 1).

Таблица 1 – Исходные данные

Составим математическую модель, для чего введем следующие обозначения:

x_j- количество выпускаемой продукции j-го типа j=1,2,3,4;

b_i- количество располагаемого ресурса i-го вида i=1,2,3;

a_ij- норма расхода i-го ресурса для выпуска единицы продукции j-го типа;

c_j- прибыль, получаемая от реализации единицы продукции j-го типа.

Из табл. видно, что для выпуска единицы Продукта1 требуется 6 единиц сырья, значит, для выпуска всей продукции первого типа требуется 6x₁ единиц сырья, где x₁- количество выпускаемой продукции Продукт1. С учетом того, что для других видов продукции зависимости будут аналогичны, ограничение по сырью будет иметь вид:

6 x₁+5 x₂+4 x₃+3 x₄ ≤ 110.

В этом ограничении левая часть равна величине требуемого ресурса, а правая показывает количество имеющегося ресурса.

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

Математическая модель задачи выглядит следующим образом.

Целевая функция имеет вид:

60 x₁+70 x₂+120 x₃+130 x₄ → max

Ограничения имеют вид:

x₁+x₂+x₃+x₄≤16

6 x₁+5 x₂+4 x₃+3 x4≤110

4 x₁+6 x₂+10 x₃+13 x4≤100

x_j>=0; j=1..n.

1. Форма ввода условий задачи представлена на рисунке 1. Весь текст на рисунке (и в дальнейшем) является комментарием и на решение задачи не влияет.

Рисунок 1 –Форма ввода условий

2. Необходимые исходные данные приведены на рисунке 2.

Рисунок 2 – Исходные данные

3. Рассмотрим алгоритмы ввода уравнений целевой функции и ограничений:

· Установить курсор в ячейку, содержащую целевую функцию (F6).

· Щелкнуть мышью по кнопке -Мастер функций (на панели инструментов). На экране: диалоговое окно «Мастер функций шаг 1 из 2» (рисунок 3).

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

· Выбрать категорию Мат. и тригонометрия

· Выбрать функцию СУММПРОИЗВ

· Щелкнуть по кнопке Шаг >. На экране: диалоговое окно «Мастер функций шаг 2 из 2» (рисунок 4).

Рисунок 4 – Функция СУММПРОИЗВЕД

· В массив 1 ввести $B$3:$E$3.

Следует отметить, что во все диалоговые окна адреса ячеек удобно вводить не с клавиатуры, а протаскивая мышь по ячейкам, чьи адреса следует ввести.

· В массив 2 ввести B6:E6.

· Щелкнуть по кнопке Закончить.

В ячейке F6 отображается значение целевой функции, оно равно 0.

Ввод ограничений (в ячейки F9, F10, F11) осуществляется аналогичным образом, с заданием соответствующих адресов. Однако значительно проще можно выполнить данную процедуру используя мышь. Для этого подведите курсор мыши к ячейке с целевой функцией (F6), нажмите клавишу <Ctrl> (при этом рядом с изображением курсора мыши должен появиться знак «+»). Удерживая <Ctrl> перетащите содержимое ячейки F6 в ячейку F9. Содержимое F6 скопировано в F9. Ячейка F9 стала активной, об этом свидетельствует черная рамка вокруг нее, также называемая курсором. В правом нижнем углу курсора-рамки имеется маленький квадрат. Подведите курсор мыши к нему (курсор мыши превратится в черный крестик), «ухватите» мышью квадрат и тяните вниз до ячейки F11 включительно. Таким образом вы скопируете формулу из F9 в ячейки F10 и F11.

Теперь таблица примет вид, представленный на рисунке 5. В режиме представления формул она показана на рисунке 6.

Рисунок 5 – Заполненная таблица

Рисунок 6 – Заполненная таблица с формулами

Все необходимые условия внесены в таблицу в виде формул. Следующим этапом будет поиск решения задачи средствами Excel.