Разработка управленческого решения

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

Содержание

3. РЕШЕНИЕ ЗАДАЧ ЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ  С ПОМОЩЬЮ EXCEL

3. 1. Ввод условий задачи

3. 2. Работа в диалоговом окне "Поиск  решения"

4. ПРИМЕРЫ РЕШЕНИЯ ОПТИМИЗАЦИОННЫХ ЗАДАЧ СРЕДСТВАМИ EXCEL

4. 1. Получение требуемого сплава

4. 2. Транспортная задача

4. 3. Рациональное использование имеющихся  площадей

4. 4. Рациональное использование технологических  участков

4. 5. Закрепление самолетов за воздушными  линиями

4. 6. Задача о ранце

4. 7. Назначение механизмов на работы

4. 8. Задача коммивояжера

4. 9. Задача о доставке

5. ВАРИАНТЫ ЗАДАНИЙ

 

 

 

 

 

 

 

 

 

3. Решение задач линейного программирования  с помощью Excel

3. 1. Ввод условий задачи

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

1). Создание формы для ввода условий  задачи.

2). Ввод исходных данных (коэффициентов  математической модели).

3). Ввод целевой функции, ограничений  и граничных условий.

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

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

Ресурс	Продукт1	Продукт2	Продукт3	Продукт4	Наличие
Трудовые	1	1	1	1	16
Сырье	6	5	4	3	110
Финансы	4	6	10	13	100
Прибыль	60	70	120	130

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

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

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

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

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

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

6× x1+5× x2+4× x3+3× x4 £ 110.

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

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

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

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

60× x1+70× x2+120× x3+130× x4® max

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

x1+x2+x3+x4£ 16

6× x1+5× x2+4× x3+3× x4£ 110

4× x1+6× x2+10× x3+13× x4£ 100

xj³ 0; j= .

Рис. 6

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

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

Рис. 7

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

• Установить курсор в ячейку, содержащую целевую функцию (F6).
• Щелкнуть мышью по кнопке -Мастер функций (на панели инструментов). На экране: диалоговое окно "Мастер функций шаг 1 из 2" (рис. 8).
• Выбрать категорию Мат. и тригонометрия
• Выбрать функцию СУММПРОИЗВ
• Щелкнуть по кнопке Шаг >. На экране: диалоговое окно "Мастер функций шаг 2 из 2" (рис. 9).
• В массив 1 ввести $B$3:$E$3.

Рис. 8

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

• В массив 2 ввести B6:E6.
• Щелкнуть по кнопке Закончить.

Рис. 9

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

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

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

Рис. 10

Рис. 11

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

3. 2. Работа в диалоговом окне "Поиск решения"

1). Выберите последовательно опции  меню Сервис, Поиск решения. На экране появится соответствующее окно (рис. 12).

Рис. 12

Поясним смысл элементов окна.

Установить целевую ячейку- определяет целевую ячейку, значение которой необходимо максимизировать или минимизировать, или сделать равным конкретному значению.

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

Предположить- отыскивает все неформульные ячейки, прямо или непрямо зависящие от формулы в окне Установить целевую ячейку, и помещает их ссылки в окно Изменяя ячейки.

Ограничения- перечисляет текущие ограничения в данной проблеме.

Добавить- выводит окно диалога “Добавить ограничение”, в котором можно добавить ограничения к текущей проблеме.

Изменить- выводит окно диалога “Изменить ограничение”, в котором можно модифицировать имеющиеся ограничения.

Удалить- удалить выделенное ограничение.

Выполнить- запускает процесс решения определенной проблемы.

Закрыть- закрывает окно диалога, не решая проблемы. Сохраняются лишь изменения, сделанные при помощи кнопок Параметры, Добавить, Изменить и Удалить. Не сохраняются изменения, произведенные после использования данных кнопок.

Параметры- выводит окно диалога “Параметры поиска решения”, в котором можно контролировать различные аспекты процесса отыскания решения, а также загрузить или сохранить некоторые параметры, такие, как выделение ячеек и ограничений, для какой-то конкретной проблемы на рабочем листе.

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

Курсор ввода с клавиатуры установлен в поле Установить целевую ячейку. Сюда необходимо внести адрес ячейки, содержащей целевую функцию. Для того чтобы сделать это щелкните мышью на той ячейке рабочего листа, где содержится ЦФ (F6). Вокруг F6 появился движущийся пунктирный контур, а в поле окна- соответствующий адрес. Следует отметить, что подобным способом можно вносить все остальные необходимые данные, это удобнее, чем вводить их с клавиатуры.

2). В поле Равной выберите флажок Максимальному значению.

3). Введите адреса искомых переменных, для этого выделите мышью область  таблицы B3:E3.

4). Ввод ограничений задачи. Щелкните на кнопке Добавить. На экране появилось окно "Добавление ограничения" (рис. 13). Excel воспринимает ограничения в виде ссылок на ячейки в которых содержатся соответствующие формулы, при этом левая часть ограничения представляет собой, как правило, ссылку на формулу, а правая- значение: число или ссылку на ячейку, содержащую значение. Адреса ячеек должны содержать символ $. Если определяется интервал ячеек, то он должен быть той же формы и тех же размеров, что и интервал в окне Ссылка на ячейку. Некоторые из ограничений примера представлены на рис. 12.

Рис. 13

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

Ограничение- определяет условие, налагаемое на содержимое окна Ссылка на ячейку. Выберите из списка отношение, которое нужно установить между ячейкой или интервалом и ограничением, которое нужно ввести в окне справа от списка. Можно выбрать <=, =, >=, или "цел". Если Вы выбрали "цел" для указания на то. что переменная должна быть целочисленной, то слово "Целое" появляется в окне справа от списка.

Добавить- в окне диалога “Добавить ограничение” можно добавить новое ограничение без возврата в диалог “Параметры поиска решений”.

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

5). Установка параметров решения. Щелкните  мышью по кнопке Параметры. На экране появится окно "Параметры поиска решения" (рис. 14).

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

Рис. 14

Поясним элементы окна.

Максимальное время- ограничивает время, требующееся для процесса отыскания решения. Это значение должно быть положительным целым числом. Значение по умолчанию равно 100 (секунд), что вполне годится для большинства малых задач, хотя Вы можете ввести любое значение до 32767.

Число итераций- ограничивает время, требующееся для процесса отыскания решения, путем ограничения числа промежуточных вычислений. Это значение должно быть положительным целым числом до 32767.

Точность- контролирует точность ответов, получаемых при поиске решений. Число, вводимое в поле Точность:

• используется при определении того, удовлетворяет ли значение ячейки ограничения нужному равенству или находится ли оно в указанных границах.
• должно быть дробным числом от 0 до 1 (не включая концы).
• имеет значение по умолчанию равно 0,000001.

указывает на меньшую точность, если число введено с меньшим количеством десятичных знаков; например, 0,0001.

Вообще говоря, чем большая точность определяется (чем меньше число), тем больше времени понадобится для поиска решения. Методы, используемые Поиском Решения, позволяют существенно ускорить поиск, если установить исходное значение, достаточно близкое к искомому решению.

Допустимое отклонение- проблемы, связанные с изменяемыми ячейками, которые должны содержать целые значения, могут требовать большого количества времени, так как при этом необходимо решать несколько подпроблем, каждая из которых есть задача для Поиска Решений с целочисленными ограничениями. Можно подобрать величину отклонения, которая представляет процент допустимого отклонения от оптимального решения при целочисленных ограничениях для всех элементов задачи. Чем выше отклонение (допустимое отклонение в процентах), тем быстрее процесс решения. Установка отклонения не играет роли, если не введены целочисленные ограничения.

Линейная модель- ускоряет процесс отыскания решения. Команда может быть использована только, если все связи в модели линейны.

Показать результаты итераций- прерывает Поиск Решения и показывает результаты после каждой итерации.

Автоматический масштаб- включает автоматический масштаб. Это полезно, когда параметры ввода (Изменяя ячейки) и вывода (Установить целевую ячейку и Ограничения) сильно различаются по величине; например, максимизация прибыли в процентах по отношению к вложениям, исчисляемым в миллионах рублей.

Оценка- эти флажки определяют подход, используемый для получения исходных оценок основных переменных в каждом одномерном поиске.

• линейная- использует линейную экстраполяцию вдоль касательного вектора.
• квадратичная- использует квадратичную экстраполяцию; это дает лучшие результаты для нелинейных проблем.

Производная- параметры группы Производная определяют способ вычисления производной при оценке частных производных целевых и ограничивающих функций. Эти варианты существенно отличаются своим действием на функциях, чье графическое представление недостаточно гладко или непрерывно. Для таких функций следует использовать вариант Центральная.