КАТЕГОРИИ: Архитектура-(3434)Астрономия-(809)Биология-(7483)Биотехнологии-(1457)Военное дело-(14632)Высокие технологии-(1363)География-(913)Геология-(1438)Государство-(451)Демография-(1065)Дом-(47672)Журналистика и СМИ-(912)Изобретательство-(14524)Иностранные языки-(4268)Информатика-(17799)Искусство-(1338)История-(13644)Компьютеры-(11121)Косметика-(55)Кулинария-(373)Культура-(8427)Лингвистика-(374)Литература-(1642)Маркетинг-(23702)Математика-(16968)Машиностроение-(1700)Медицина-(12668)Менеджмент-(24684)Механика-(15423)Науковедение-(506)Образование-(11852)Охрана труда-(3308)Педагогика-(5571)Полиграфия-(1312)Политика-(7869)Право-(5454)Приборостроение-(1369)Программирование-(2801)Производство-(97182)Промышленность-(8706)Психология-(18388)Религия-(3217)Связь-(10668)Сельское хозяйство-(299)Социология-(6455)Спорт-(42831)Строительство-(4793)Торговля-(5050)Транспорт-(2929)Туризм-(1568)Физика-(3942)Философия-(17015)Финансы-(26596)Химия-(22929)Экология-(12095)Экономика-(9961)Электроника-(8441)Электротехника-(4623)Энергетика-(12629)Юриспруденция-(1492)Ядерная техника-(1748) |
II. Решение задачи распределения ресурсов в EXCEL.
1) Ввод данных примера 1 в таблицу EXCEL (рис.4). Рис.4 На рис.4 «краска 1» обозначает краску для внутренних работ, «краска 2» – краску для наружных работ. Для переменных задачи x1 и x2 отведены ячейки B3 и C3. Эти ячейки называются рабочими или изменяемыми ячейками. В изменяемые ячейки ничего не заносится и в результате решения задачи в этих ячейках будет оптимальные значения переменных. В ячейку D4 вводится формула для вычисления целевой функции задачи (дохода) Z=2x1+x2. Чтобы сделать это надо выполнить следующие действия: курсор в D4; курсор на кнопку fx (мастер функций); В появившемся окне выбрать “Математические” и “СУММПРОИЗВ” (рис. 5).
Рис.5. В окне мастера функций нажать Далее>, в появившемся окне (рис.6) в поле “массив 1” ввести (протаскивая курсор мыши по ячейкам) адреса изменяемых ячеек B3:C3. В поле “массив 2” вводятся адреса ячеек содержащих цены на краски B4:C4, после нажать Готово. Рис.6 В ячейку D7 вводится формула для вычисления израсходованного количества продукта А: x1+2x2, а в ячейку D8 вводится формула для израсходованного количества продукта B: 3x1+x2. Обе формулы вводятся аналогично целевой функции (рис.7 и 8).
Рис.7 Рис.8
Проверить результаты ввода можно следующим образом: при установке курсора в ячейку D4 в строке ввода должно появиться: “=СУММПРОИЗВ(B3:C3; B4:C4)”; в ячейки D7: “=СУММПРОИЗВ(B3:C3; B7:C7)”; в ячейки D8: “=СУММПРОИЗВ(B3:C3; B8:C8)”. Окончательно после ввода формул и данных экран имеет вид (рис.9):
Рис.9 2) Работа в окне “Поиск решения” В меню “Сервис” выбираем процедуру “Поиск решения” В появившемся окне (рис.10) нужно установить адрес целевой ячейки D4, значение целевой ячейки: максимальное, адреса изменяемых ячеек B3:C3.
Рис.10
Чтобы ввести ограничения задачи, нажать кнопку «Добавить». В появившемся диалоговом (рис.11) окне слева ввести адрес D7 (израсходованное количество продукта А), затем выбрать знак <= и в правой части количество продукта А на складе, равное 3 (или адрес ячейки E7). Рис.11
После ввода нажать кнопку «Добавить» и аналогично ввести второе ограничение: D8 <= 3. Снова нажать кнопку «Добавить» и ввести ограничение: B3:C3 >= 0 (соответствующее ограничению x1, x2 >= 0). После ввода последнего ограничения нажать ОК. После ввода ограничений окно «Поиска решений имеет» будет иметь вид (рис. 12):
Рис.12
3) Настройка параметров решения задачи. В окне «Поиск решения» нажать «Параметры» в появившемся окне (рис. 13) установить флажок в пункте «Линейная модель». В этом случае при решении задачи будет использоваться симплекс - метод. Остальные значения можно оставить без изменения. После нажать кнопку ОК Рис.13
Для решения задачи в окне «Поиск решения» нажать кнопку «Выполнить». Если решение найдено появляется окно (рис.14):
Рис.14 Для просмотра результатов выбираем тип отчета: «Результаты» и нажимаем кнопку ОК. В появившихся трех таблицах (рис.15) приводятся результаты поиска. Из этих таблиц видно, что в оптимальном решении: производство краски 1 = B3 = 0.6; производство краски 2 = С3 = 1.2; при этом доход = D4 = 2.4; расход ресурса A = D7 = 3; расход ресурса B = D8 = 3; таким образом, оба ресурса дефицитные (соответствующие ограничения называются связанными).
Рис.15
«Отчет по результатам» состоит из трех таблиц (рис.15): в таблице 1 приводятся сведения о целевой функции; в таблице 2 приводятся значения переменных задачи; в таблице 3 показаны результаты поиска для ограничений задачи. Первоначальная таблица EXCEL заполняется результатами, полученными при решении (на рис.16 появившиеся значения в темных ячейках). Рис.16
Пример 1. Определение оптимального ассортимента продукции
Предприятие изготавливает два вида продукции П1 и П2, которая поступает в оптовую продажу. Для производства используются два вида сырья и . Максимально возможные запасы сырья в сутки составляют 9 и 13 единиц соответственно. Расход сырья на единицу продукции приведен в таблице.
Таблица 2.1
Маркетинговые исследования показали, что суточный спрос на продукцию П1 не превышает спрос на продукцию П2 более чем на Оптовые цены единицы продукции равны для П1 3 д.е., для
Решение
Очевидно, фирме требуется определить объемы производства каждого вида продукции в тоннах, максимизирующие доход в д.е. от реализации продукции, с учетом ограничений на спрос и расход исходных продуктов. Предположим, что предприятие изготовит единиц продукции П1 и единиц продукции П2. Поскольку производство продукции ограничено имеющимся в распоряжении предприятия сырьем каждого вида и спросом на данную продукцию, а также учитывая, что количество изготовляемых изделий не может быть отрицательным, получим следующую систему ограничений
Доход от реализации продукции (целевая функция) составит
Таким образом, данная простая задача сводится к максимизации целевой функции при учете вышеприведенных ограничений. Проведем решение задачи в Excel. Введем данные на рабочий лист так, как показано на Рис 2.1. Искомые значения переменных будут располагаться в ячейках A10 и B10 соответственно, целевая функция – в ячейке E10.
Рис. 2.1 В ячейки A3, A4 введем левые части функций – ограничений: =2*A10+3*B10 и = 3*A10+2*B10 соответственно. В ячейку C10 введем левую часть третьей функции-ограничения: =A10-B10. Далее, запускаем пакет Поиск решения (Сервис ® Поиск решения) и устанавливаем целевую и изменяемые ячейки, а также вводим необходимые ограничения (Рис.2.2)
Рис. 2.2 Окно диалога Поиск решения
Поиск решения дает ответ
Пример 2.Использование мощностей оборудования
Предприятие имеет моделей машин различных мощностей. Задан план по времени и номенклатуре: - время работы каждой машины; продукции - го вида должно быть выпущено не менее единиц. Необходимо составить такой план работы оборудования, чтобы обеспечить минимальные затраты на производство, если известны производительность каждой - машины по выпуску - го вида продукции и стоимость единицы времени, затрачиваемого -й машиной на выпуск - го вида продукции . Другими словами, задача для предприятия состоит в следующем: требуется определить время работы время работы - машины по выпуску - го вида продукции , обеспечивающее минимальные затраты на производство при соблюдении ограничений по общему времени работы машин и заданному количеству продукции . Решение. По условию задачи машины работают заданное время , поэтому данное ограничение можно представить в следующем виде
Ограничение по заданному количеству продукции имеет вид
Задача решается на минимум затрат на производство
В данной постановке задачи предполагается, что количество выпускаемой продукции должно быть, по крайней мере, не менее . В некоторых случаях не допускается превышение плана по номенклатуре; очевидно в этом случае в ограничениях по количеству продукции необходимо использовать знак равенства.
Проведем решение задачи в Excel. Введем данные на рабочий лист так, как показано на Рис 2.3. В ячейки B7:E7 введем формулы для ограничений по объему выпускаемой продукции
()
в диапазон ячеек F19:F21 – формулы для ограничений по времени работы машин
()
В качестве целевой ячейки выберем H11 и введем в нее формулу минимизируемой функции. информационный оптимизация линейный модель Рис. 2.3. Данные для решения примера 2
С помощью Поиска решения получим следующий ответ:
Искомое значение минимальных затрат на производство составляет 725,32 д.е.
Следующие два рассматриваемых нами примера относятся к области целочисленной оптимизации.
Пример 3. Оптимизация производственной программы
Автомобилестроительный завод выпускает три модели автомобилей, которые изготавливаются последовательно в трех цехах. Мощность цехов составляет 300, 250 и 200 человеко-дней в декаду. В первом цехе для сборки одного автомобиля первой модели требуется 6 человеко-дней, второй модели 4 и третьей модели – 2 человеко-дня в неделю соответственно. Во втором цехе трудоемкость равна 3, 4 и 5 человеко-дней соответственно, в третьем – по 3 человеко-дня на каждую модель. Прибыль, получаемая от продажи автомобиля каждой модели, составляет соответственно 15, 13 и 10 тыс. д.е. Требуется построить модель оптимального плана и определить оптимальные количества моделей каждого типа, т.е. такие, при которых прибыль завода будет максимальной. Решение. Пусть - количество выпускаемых автомобилей -й модели в течение декады (). Модель может быть описана следующей целевой функцией и системами ограничений
(2.5)
Решение
Введем данные на рабочий лист так, как показано на Рис. 2.4. Искомые значения переменных будут размещаться в ячейках A10:B10, целевая функция – в ячейке E10. В ячейки A3:A5 введем левые части функций – ограничений, соответствующих второму, третьему и четвертому соотношению из (2.5). С помощью Поиска решения получим ответ
Рис. 2.4 Данные для решения примера 3
Пример 4. Размещение проектов на предприятиях
Имеется инвестиционных возможностей (вариантов проектов), которые можно реализовать на предприятиях. Эффективность реализации каждой инвестиции на каждом из объектов задана в таблице 2.2.
Таблица 2.2
Целевой функцией, подлежащей оптимизации, является функция
где - искомые распределения инвестиций по объектам. Таким образом, по смыслу величина есть ожидаемый результат от осуществления всех инвестиционных проектов. Ограничениями в данном случае являются следующие соотношения
означающие, что на каждом объекте может быть реализован лишь один проект, и
означающие, что должны быть реализованы все проекты. Необходимо распределить проекты по объектам таким образом, чтобы суммарная эффективность от реализации всех проектов была максимальной.
Решение
Введем данные на рабочий лист (Рис.2.5.). В ячейку B17 введем формулу =СУММ(B12:B16) и скопируем эту формулу в диапазон C17:F17. Аналогично, введем формулу =СУММ(B12:F12) в ячейку G12 и скопируем ее в диапазон G13:G16. Введем в ячейку для целевой функции (I13) формулу =СУММПРОИЗВ(B4:F8;B12:F16)
Рис. 2.5 Данные для решения примера 4
Для решения задачи с помощью Поиска решения необходимо ввести ограничения в соответствии с приведенным ниже рисунком.
Поиск решения дает ответ
(остальные ), .
Дата добавления: 2017-02-01; Просмотров: 544; Нарушение авторских прав?; Мы поможем в написании вашей работы! Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет |