Лабораторная работа №1

Решение задач линейной оптимизации средствами EXCEL

Цель работы: Получение практических навыков формирования математических моделей прикладных задач линейного программирования и их решения средствами EXCEL.

Лабораторные задания.

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

2. Провести параметрический анализ решая задачу при пяти различных значениях ресурса оборудования первого типа (варианты 1-3), второго типа (варианты 4-7) и третьего типа (варианты 8-10).

3. Решить задачу вручную с использованием симплекс-метода и графического метода.

4. Сравнить результаты, полученные при выполнении лабораторных заданий 1 и 2.

Отчет по лабораторной работе должен содержать:

1. Постановку задачи.

2. Математическую оптимизационную модель.

3. Краткое описание основных этапов решения в среде EXCEL.

4. Результат решения задачи (итоговую таблицу в EXCEL).

5. Отчеты по результатам, устойчивости, пределам.

6. Построенные диаграммы.

7. Результаты параметрического анализа с соответствующими выводами.

8. Решение задачи вручную с использованием симплекс-метода и графического метода.

9. Выводы.

Для производства двух видов изделий A и B используются три типа технологического оборудования. Для производства одного изделия A оборудование первого типа используется в течение a₁ часов, оборудование второго типа – a₂ часов, оборудование третьего типа – a₃ часов. Для производства одного изделия B оборудование первого типа используется в течение b₁ часов, оборудование второго типа – b₂ часов, оборудование третьего типа – b₃ часов. На изготовление всех изделий предприятие может использовать оборудование первого типа не более чем t₁ часов, оборудование второго типа – не более t₂ часов, оборудование третьего типа – не более t₃ часов. Прибыль от реализации одного готового изделия A составляет a денежных единиц, а изделия В - b денежных единиц. Составить план производства изделий A и B, обеспечивающий максимальную прибыль от их реализации.

Вариант	a1	a2	a3	b1	b2	b3	t1	t2	t3	a	b

Методические указания для выполнения работы

Решение задач в среде EXCEL начинается с ввода условий задачи. Ввод условий задачи состоит из следующих основных шагов:

- создание формы для ввода условий задачи;

- ввод исходных данных;

- ввод зависимостей из математической модели;

- назначение целевой функции;

- ввод ограничений и граничных условий.

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

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

Таблица 1

Ресурсы	Виды продукции	Запасы ресурсов
Прод. 1	Прод. 2	Прод.3	Прод.4
Трудовые
Сырье
Финансы
Прибыль

Математическая модель для решения данной задачи:

F=7x₁+3x₂+6x₃+12x₄®max;

3x₁+x₂+2x₃+4x₄ £440;

x₁+8x₂+6x₃+2x₄ £200;

x₁+4x₂+7x₃+2x₄ £320;

x_j ³0, j= .

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

Рис. 1

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

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

Рис.2

При этом ячейки B3:E3 являются изменяемыми и в них будут заноситься значения переменных.

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

осуществляется с использованием Мастера функций. Для этого необходимо активизировать требуемую ячейку (F6) и вызвать Мастер функций. В левой части появившегося диалогового окна нужно выбрать категорию функции Математическа я, а в правом окне выделить функцию СУММПРОИЗВ и нажать клавишу ОК. Затем на экране отобразится диалоговое окно второго шага (рис.8), где требуется ввести как первый (B$3:E$3), так и второй массивы (B6:E6). При вводе первого массива используются абсолютные ссылки на ячейки, при вводе второго - относительные, что в дальнейшем будет удобно при копировании формул. Во все окна адреса ячеек удобно вводить не с клавиатуры, а протаскивая мышь по соответствующим ячейкам.

Рис.3

Зависимости для левых частей ограничений вводятся аналогично. При этом необходимо лишь менять адреса ячеек. Для ускорения и удобства ввода можно скопировать содержимое ячейки F6 в ячейки F9, F10 и F11 (при этом все относительные ссылки изменятся автоматически).

Окончательная таблица с исходными данными представлена на рис.4.

Рис. 4

После окончания ввода исходных данных осуществляется вызов программы Поиск решения. Для этого необходимо выбрать в меню пункт Сервис, а в нем – Поиск решения, в результате чего на экране появится окно поиска решения (рис.5).

Рис.5

В окне Установить целевую ячейку требуется ввести имя ячейки, в которую введена зависимость для целевой функции (в данном случае F6). В качестве направления оптимизации выбирается максимизация. В окне Изменяя ячейки вводятся адреса ячеек, соответствующих варьируемым переменным задачи ($B3:$E3). Далее необходимо ввести ограничения. Для добавления ограничений выбирается пункт Добавить, после чего появляется окно добавления ограничений (рис.6)

Рис.6

Вводятся граничные условия для переменных (Прод1 - Прод4) ³ 0: $B3>= $B4, $C3 >= $C4, $D3 >= $D4, $E3 >= $E4 (нулевые значения ячеек B4-E4 можно не устанавливать). Ограничения можно также ввести в виде $B3 >= 0, $C3 >= 0, $D3 >= 0, $E3 >= 0. Затем вводятся ограничения на ресурсы: $F9 <= $H9, $F10 <= $H10, $F11 <= $H11. Ограничения вводят последовательно. Сначала выбирается пункт Добавить,далее в появившихся диалоговых окнах вводится левая часть, знак и правая часть каждого ограничения. После ввода последнего ограничения и нажатия OK произойдет возврат в окно Поиск решения.

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

Рис.7

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