Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Практическая работа № 2
Тема: Задачи оптимизации (поиск решения)
Цель работы: изучить технологию поиска решения для задач оптимизации (минимизация, максимизация).
Очень часто математическая постановка экономических задач, связанных с управлением, может быть сформулирована в общем виде следующим образом.
Пусть имеется некоторая целевая функция z, которая зависит от параметров, х = (x1, х2, х,…, хn,), удовлетворяющих некоторым ограничениям α, z = z(x,α).
Требуется найти значения параметров или функций, которые обращают величину z в максимум или минимум.
Такие задачи – отыскание значений параметров, обеспечивающих экстремум функции при наличии ограничений, наложенных на аргументы, носят общее название задач математического программирования и решаются методами теории исследования операций.
Среди задач математического программирования самы ми простыми являются задачи линейного программирова ния (ЗЛП).
Основная задача линейного программирования заключается в нахождении неотрицательных значений переменных, удовлетворяющих условиям-равенствам и обращающие в максимум линейную функцию этих переменных. Допустимое решение, максимизирующее целевую функцию, называется оптимальным решением (планом).
Инструментом для решений задач оптимизации в MS Ехсеl служит надстройка «Поиск решения». Процедура поиска решения позволяет найти оптимальное значение формулы, содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданный результат, процедура изменяет значения во влияющих ячейках.
Решения задачи оптимизации состоит из нескольких этапов:
– создание модели задачи оптимизации;
– поиск решения задачи оптимизации;
– анализ найденного решения задачи оптимизации.
Задача 1.
Предприятие выпускает три вида изделий А,В,С. Прибыль от производства одного изделия вида А составляет 15 руб., прибыль, получаемая от производства одного изделия вида В – 10 руб., прибыль, получаемая от производства одного изделия вида С – 12 руб. Для изготовления одного изделия вида А необходимо затратить 3 единицы сырья и 1,2 часов работы; для изготовления одного изделия вида В необходимо затратить 2 единицы сырья и 0,6 часов работы, для изготовление одного изделия вида С необходимо затратить 1 единицу сырья и 0,8 часа работы. Следует учитывать, что время работы ограничено – не более 40 часов в неделю. Кроме того, ограничены запасы сырья: предприятие не может использовать более 98 единиц сырья в неделю. Имеются также ограничения на выпуск изделий: в течение недели необходимо произвести не более 20 единиц изделия А, не более 30 единиц изделия В и не более 25 единиц изделия С. Необходимо найти оптимальный план производства, чтобы прибыль была максимальной.
Ход работы
Для того чтобы составить модель задачи, запустите редактор электронных таблиц MS EXCEL и введите исходные данные как показано на рис. 1. Рабочий лист обязательно должен содержать целевую ячейку и изменяемые ячейки (на рис. 1 они выделены серым цветом). В целевой ячейке должна содержаться формула, значение которой зависит от значений в изменяемых ячейках.
Так как значения в ячейках (В8:В10) неизвестны, в эти ячейки нужно ввести любое разумное значение, потому что в процессе работы процедуры «Поиск решения», эти значения будут изменяться и после успешного выполнения поиска, в этих ячейках будет отражен ответ на поставленный вопрос к задаче.
Ячейка С8 рассчитывается по формуле =С2*В8, ячейки С9, С10 рассчитываются аналогично.
Ячейка D8 рассчитывается по формуле =B2*В8, ячейки D9, D10 рассчитываются аналогично.
Ячейка E8 рассчитывается по формуле =E2*В8, ячейки С9, С10 рассчитываются аналогично.
Рис. 1. Исходные данные для задачи 1
2. После того, как подготовлен лист с исходными данными, в меню Данные «вызовите» надстройку «Поиск решения». Если эта надстройка установлена, появится окно (рис. 2).
3. После того, как появится окно «Поиск решения», необходимо последовательно его «заполнить», т. е. задать все условия для решения задачи.
Для этого в поле Установить целевую ячейку укажите ячейку (Е11), содержащую формулу для расчета суммарного дохода от производства.
В поле Равной выбираем переключатель Максимальному значению, так как по условию задачи необходимо составить план, который позволит получать максимальную прибыль.
Рис. 2. Окно «Поиск решения»
В поле Изменяя ячейки указываем адреса ячеек, значения которых будут меняться в процессе поиска решения, т. е. в нашей задаче – это ячейки В8:В10.
Заполняя поле Ограничения, необходимо учесть все ограничения данных в условии задачи.
Для того чтобы добавить ограничения нажмите кнопку Добавить и введите необходимое ограничение в появившемся окне «Добавление ограничений» (рис. 3).
Рис. 3. Окно «Добавление ограничений»
После того, как ограничение введено, нажмите кнопку ОК, оно отобразится в поле Ограничения. Если в задаче не одно ограничение, тогда, после ввода первого ограничения, в окне «Добавление ограничений» нажмите кнопку Добавить и продолжайте вводить следующее ограничение, при этом все ограничения отразятся в поле Ограничения окна «Поиск решения».
Например, введем первое ограничение (рис. 4) – время работы не должно превышать 40 часов в неделю. В поле Ссылка на ячейку укажите ячейку (D11), затем в раскрывающемся списке операторов выберите оператор меньше или равно (<=), в поле Ограничение введите значение 40.
Рис. 4. Добавление ограничений
Все остальные ограничения введите также.
Если при вводе ограничений была допущена ошибка, выделите это ограничение и щелкните по кнопке Изменить. Откроется окно «Изменение ограничения» (рис. 5), оно аналогично окну «Добавление ограничений». После исправления ошибок щелкните по кнопке ОК, чтобы вернуться в окно «Поиск решения».
Рис. 5. Окно «Изменение ограничений»
Чтобы удалить ограничение щелкните по кнопке Удалить.
Для ввода параметров щелкните кнопку Параметры в окне «Поиск решения». Параметры, установленные по умолчанию в окне «Параметры поиска решения», подходят для большинства задач, а для данной задачи необходимо поставить две галочки, напротив строк Линейная модель и Неотрицательные значения (рис. 6). Остальные параметры не нуждаются в корректировке.
Для того чтобы выполнить поиск решения, щелкните кнопку «Выполнить», через некоторое время на экране появится диалоговое окно «Результаты поиска» решения (рис. 7), в котором вы сможете выполнить следующие действия:
– сохранить найденное решение. Для этого щелкните на кнопке ОК. При этом исходные значения в целевой и изменяемых ячейках заменяются новыми значениями, найденными в процессе поиска решения;
– создать отчет о процедуре поиска решения. Выберите один или несколько отчетов в списке Тип отчета и щелкните на кнопке ОК. Каждый отчет будет помещен на новый рабочий лист с соответствующим именем;
– сохранить решение в виде сценария (кнопка Сохранить сценарий). Этот сценарий затем может быть использован в средстве Диспетчер сценариев;
– восстановить исходные значения в целевой и изменяемых ячейках. Если найденное решение вас не удовлетворяет, щелкните на кнопке Отмена.
Рис. 6. Окно ввода параметров поиска решения
Рис. 7. Окно «Результаты поиска решения»
В данной работе необходимо Сохранить найденное решение. В случае, если решение не может быть найдено, необходимо проверить введенные исходные данные и условия в окне «Поиск решения», исправить если есть необходимость и проделать процедуру поиска снова.
Результат работы представлен на рис. 8. Из него видно, что при производстве изделия А в количестве 2 шт., изделия В в количестве 30 шт., изделия С в количестве 30 шт., а максимальный доход при этом будет составлять 625 руб.
4. Следующим шагом является анализ полученного решения. Сделайте вывод по полученным результатам и напишите его на листе в вашей книге EXCEL.
Рис. 8. Результат работы
Задача 2.
Пусть известно, что для нормальной работы компании требуется:
5–6 программистов;
3–5 менеджеров проектов;
1 аналитик бизнес-процессов;
2 функциональных аналитика;
1 инженер по технической документации;
2 юриста;
1 бухгалтер;
2–5 начальников отделов;
1 технический директор;
1 генеральный директор.
Общий месячный фонд заработной платы должен быть минимальным. Необходимо определить, какими должны быть оклады сотрудников компании, при условии, что оклад программиста не должен быть меньше 30000 руб. Следует также учитывать, что заработная плата рассчитывается по формуле: ЗП = Аi×х+Вi, где х – оклад программиста, Аi – во сколько раз превышается значение х; Вi – на сколько превышается значение х.
Ход работы
1. Для того чтобы составить модель задачи, запустите редактор электронных таблиц MS EXCEL и введите исходные данные как показано на рис. 9. Исходными данными к задаче будут столбцы: «Должность», «Коэффициент А», «Коэффициент В», «Количество сотрудников». Рабочий лист обязательно должен содержать целевую ячейку и изменяемые ячейки (на рис. 9 они выделены серым цветом). В целевой ячейке должна содержаться формула, значение которой зависит от значений в изменяемых ячейках.
Так как значения в ячейках Е5, Е6, Е10 не указаны в условии задачи точно, следует в эти ячейки ввести числа в указанном диапазоне. Значение в ячейке С15 неизвестно, поэтому в эту ячейку нужно ввести любое разумное значение. Значения необходимо ввести обязательно, так как в процессе работы процедуры «Поиск решения» эти значения будут изменяться и после успешного выполнения поиска в этих ячейках будет отражен ответ на поставленный вопрос к задаче.
В столбце «Зарплата сотрудника» (столбец D на рис. 9) введите формулу для расчета заработной платы по формуле заданной в условии. Например, для ячейки D3 формула будет иметь следующий вид: =$C$15*B3+C3, (где ячейка С15 задана с абсолютной ссылкой, что позволит дальше просто скопировать формулу в остальные ячейки, при помощи функции автозаполнения).
Рис. 9. Исходные данные к задаче 2
В столбец «Суммарная зарплата» (столбец F, на рис. .9) введите формулу для расчета заработной платы всех работающих на этой должности. Например, для ячейки F3 формула будет выглядеть так: =D3*E3. Далее скопируйте эту формулу вниз по столбцу при помощи функции автозаполнения.
В ячейке F13 рассчитайте суммарный фонд заработной платы компании.
2. В меню Сервис активизируйте процедуру «Поиск решения», также как указано в п.2 для решения задачи 1.
3. После того как появится окно «Поиск решения», необходимо последовательно его «заполнить», т. е. задать все условия для решения задачи.
Для этого в поле Установить целевую ячейку укажите ячейку F13, содержащую формулу для расчета суммарного фонда заработной платы.
В поле Равной выбираем переключатель Минимальному значению, так как по условию задачи необходимо минимизировать фонд заработной платы.
В поле Изменяя ячейки указываем адреса ячеек, значения которых будут меняться в процессе поиска решения, т. е. в нашей задаче – это ячейки (Е5;Е6;Е10 и С15).
Заполняя поле Ограничения, необходимо учесть все условия-ограничения, данные в условии задачи.
В данном случае необходимо ввести следующие ограничения:
$Е$5≥3;
$Е$5≤5;
$Е$6≥5;
$Е$6≤6;
$Е$10≥2;
$Е$10≤5;
$С$15≤3000.
Все введенные условия задачи отображены на рис. 10.
Рис. 10. Окно «Поиск решения»
Для того чтобы выполнить поиск решения, щелкните кнопку Выполнить, через некоторое время на экране появится диалоговое окно Результаты поиска решения (рис. 11), в котором вы сможете выполнить следующие действия:
– сохранить найденное решение. Для этого щелкните на кнопке ОК. При этом исходные значения в целевой и изменяемых ячейках заменяются новыми значениями, найденными в процессе поиска решения.
Рис. 11. Окно «Результаты поиска решения»
Результат работы представлен на рис. 12. На нем видно, что чем меньше сотрудников и чем меньше их оклад, тем меньше месячный фонд заработной платы.
Рис. 12. Результаты решения задачи 2
КОНТРОЛЬНЫЕ ВОПРОСЫ И ЗАДАНИЯ
1. По какому принципу работает процедура «Поиск решения»?
2. Какая ячейка называется «целевой»?
3. Какие ячейки называются «зависимыми»?
4. Какие типы отчетов можно получить для анализа решения задачи?
5. Выберите согласно варианту, который вам назначит преподаватель, новые условия для задачи и решите ее. Варианты приведены в табл. 1. Ограничения остаются прежними.
Таблица 1
Варианты заданий для задачи
|
Вариант |
Наименование |
Время на 1 единицу |
Расход сырья |
Доход |
|
1 |
А |
1 |
12 |
22 |
|
В |
2 |
13 |
24 |
|
|
С |
3 |
15 |
26 |
|
|
2 |
А |
0,6 |
5 |
20 |
|
В |
1 |
8 |
15 |
|
|
С |
0,8 |
2 |
22 |
|
|
3 |
А |
1,6 |
12 |
20 |
|
В |
0,3 |
15 |
12 |
|
|
С |
2 |
11 |
18 |
|
|
4 |
А |
2 |
12 |
22 |
|
В |
2 |
13 |
24 |
|
|
С |
1 |
15 |
26 |
|
|
5 |
А |
0,8 |
4 |
14 |
|
В |
0,6 |
6 |
10 |
|
|
С |
0,3 |
8 |
18 |
|
|
6 |
А |
0,3 |
5 |
26 |
|
В |
2 |
8 |
20 |
|
|
С |
2 |
2 |
15 |
|
|
7 |
А |
2 |
8 |
20 |
|
В |
1 |
2 |
15 |
|
|
С |
0,8 |
12 |
22 |
|
|
8 |
А |
0,6 |
11 |
18 |
|
В |
0,3 |
12 |
22 |
|
|
С |
0,3 |
13 |
24 |
|
|
9 |
А |
2 |
5 |
10 |
|
В |
3 |
8 |
18 |
|
|
С |
0,6 |
2 |
26 |
|
|
10 |
А |
1 |
13 |
15 |
|
В |
0,8 |
15 |
22 |
|
|
С |
0,6 |
5 |
18 |
6. Предприятие выпускает телевизоры, стереосистемы и акустические системы, используя общий склад комплектующих. Запасы кинескопов на складе составляют 250 шт., динамиков 800 шт., блоков питания 450 шт., плат 600 шт. При этом на каждое изделие расходуется следующее количество комплектующих (табл. 2).
Таблица 2
Расход комплектующих на одну единицу изделия
|
Наименование изделия |
Расход на 1 единицу |
|||
|
Кинескоп |
Дина мик |
Блок питания |
Плата |
|
|
Стереосистема |
0 |
2 |
1 |
6 |
|
Телевизор |
1 |
2 |
2 |
8 |
|
Акустическая система |
0 |
5 |
2 |
4 |
Прибыль от производства одного телевизора составляет 90 у.е., одной стереосистемы – 50 и аудиосистемы – 45.
Задание: Необходимо найти оптимальное соотношение объемов выпуска изделий, при котором прибыль от производства всей продукции будет максимальной.