Заглавная страница Избранные статьи Случайная статья Познавательные статьи Новые добавления Обратная связь КАТЕГОРИИ:  Археология Биология Генетика География Информатика История Логика Маркетинг Математика Менеджмент Механика Педагогика Религия Социология Технологии Физика Философия Финансы Химия ЭкологияТОП 10 на сайте Приготовление дезинфицирующих растворов различной концентрацииТехника нижней прямой подачи мяча. Франко-прусская война (причины и последствия) Организация работы процедурного кабинета Смысловое и механическое запоминание, их место и роль в усвоении знаний Коммуникативные барьеры и пути их преодоления Обработка изделий медицинского назначения многократного применения Образцы текста публицистического стиля Четыре типа изменения баланса Задачи с ответами для Всероссийской олимпиады по праву 
Мы поможем в написании ваших работ! ЗНАЕТЕ ЛИ ВЫ?
Влияние общества на человека
Приготовление дезинфицирующих растворов различной концентрации Практические работы по географии для 6 класса Организация работы процедурного кабинета Изменения в неживой природе осенью Уборка процедурного кабинета Сольфеджио. Все правила по сольфеджио Балочные системы. Определение реакций опор и моментов защемления |
Решение задач линейного программирования С помощью ExcelСтр 1 из 3Следующая ⇒
РЕШЕНИЕ ЗАДАЧ ЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ С ПОМОЩЬЮ EXCEL 1 Цель лабораторной работы Научиться составлять математическую модель и находить оптимальное решение. Задание к лабораторной работе Решить задачу распределения ресурсов. Определить в каком количестве надо выпускать продукцию каждого типа, чтобы получить максимальную прибыль. Для этого необходимо выполнить следующее: 1. Создать форму для ввода условий задачи. 2. Ввести исходные данные в виде таблицы. 3. Ввести в отдельные ячейки таблицы зависимости из математической модели. 4. Найти оптимальное решение с помощью команды Поиск решения/меню Сервис. 5. Получить оптимальное решение задачи распределения ресурсов, вывести на экран три типа отчетов: по результатам, по устойчивости, по пределам и тщательно проанализировать их. 6. Привести свои выводы и предложения по поводу получившихся результатов решения данной задачи распределения ресурсов. Исходные данные Выпускается продукция четырех типов: продукт 1, продукт 2, продукт 3, продукт 4. · Для выпуска требуется 3 вида ресурсов: трудовые, сырьевые, финансовые. Известно: · Нормы расхода (количество ресурса каждого вида), необходимые для выпуска единицы продукции данного типа. · Сколько ресурса имеется в наличии. · Прибыль, получаемая от реализации единицы каждого типа продукции. Требуется определить, в каком количестве надо выпускать продукцию четырех типов Прод1, Прод2, Прод3, Прод4, для изготовления которой требуются ресурсы трех видов: трудовые, сырье, финансы. Оформим данные в виде таблицы 1: Таблица 1 - Таблица представления результатов
Постановка математической модели Введем обозначения: Xj- количество выпускаемой продукции j- го типа (j=1…4); Bi- количество имеющегося в наличие ресурса i-го вида (i=1…3); Aij- норма расхода i- го ресурса для выпуска единицы продукции j-го типа; Сij- прибыль, получаемая от реализации продукции j- го типа. F=60x1+70x2+120x3+130x4 ®max x1+x2+x3+x4<=16 6x1+5x2+4x3+3x4<=110 4x1+6x2+10x3+13x4<=100 Ход выполнения лабораторной работы
1 На рабочем листе EXCEL подготовим форму для ввода условий задачи: Для этого в ячейку D1 введем слово Переменные, в D7 – Ограничения. Далее в диапазон клеток B2:E3 ввести соответственно Продукция 1, Продукция 2, Продукция 3, Продукция 4 (введите в ячейку B2 Прод1 и щелкнув мышкой по прямоугольнику внизу ячейки протяните до Е3. Вводим в ячейку A2 Имя, в А3- значение, А4 – нижняя граница, А5 – верхняя граница, А6 – коэффициенты в целевой функции и т. д., как указано в таблице 1. Весь этот текст является комментарием и на решение задачи не влияет.
Рисунок 1 – Компьютерный эквивалент задачи
2 Введите исходные данные в форму. 3 Далее введите зависимости для целевой функции и левой части ограничений: - Курсор ставим в клетку F6, выбираем команду Мастер функций на панели инструментов и щелкаем левой кнопкой мыши. На экране появляется диалоговое окно Мастер функций шаг 1 из 2. Устанавливаем курсор в окно Категория на категорию Математические функции, щелкаем левой кнопкой мыши и выбираем курсором в окне Функции Суммпроизв. Щелкаем на кнопку мыши. Нажимаем на команду Далее. Открывается диалоговое окно Мастер функций, где в строке массив 1 вводим диапазон ячеек B$3:E$3. Следует заметить, что во все диалоговые окна адреса ячеек удобно вводить не с клавиатуры, а протаскивая мышь по ячейкам, чьи адреса следует ввести. В массив 2 ввести B6:E6. Затем щелкаем на кнопку ОК.
Рисунок 2 – Функция MS Excel СУММПРОИЗВ
Устанавливаем курсор в клетку F6 и копируем эту формулу в диапазон F9:F11. На этом ввод данных закончен.Он оформляется в виде (3):
Рисунок 3 – Итоговая таблица ввода исходных данных
4 Организация поиска решений. В меню Сервис выбираем команду Поиск решения. Следующие действия производятся в диалоговом окне. Уставить целевую ячейку (щелкнуть мышкой в F6) В этом же диалоговом окне указываем, какое значение должна принимать целевая функция (максимальное или минимальное). Вводим адреса искомых переменных, для этого ставим курсор в поле Изменяя ячейки и вводим адреса: B3:E3.
Далее нажмите кнопку Добавить. Рисунок 4 – Окно ПОИСК РЕШЕНИЯ
На экране появится диалоговое окно Добавление ограничения.
Рисунок 5 – Окно ДОБАВЛЕНИЕ ОГРАНИЧЕНИЯ
Вводим граничные условия на переменные. В окне Ссылка на ячейку ввести B3. Курсор устанавливаем на стрелку и щелкаем на левую кнопку мыши. Устанавливаем курсор на знак >= и щелкаем на левую кнопку мыши. Переводим курсор в правое окно и вводим там B4. Щелкаем мышкой на команду Добавить. На экране опять появится диалоговое окно Добавление ограничения. Аналогично вводим граничные условия для остальных переменных. $B$3>=$B$4 $C$3>=$C$4 $D$3>=$D$4 $E$3>=$E$4 Аналогично вводим и другие ограничения: F9<=H9, F10<=H10, F11<=H11. После ввода последнего ограничения вместо Добавить ввести Ок. На экране появится диалоговое окно Поиск решения с введенными условиями. Если при вводе задачи возникает необходимость в изменении или удалении внесенных ограничений или граничных условий, то это делается с помощью команд Изменить…, Удалить. На этом ввод условий задачи заканчивается. На очереди следующий шаг – решение задачи. Решение задачи производится сразу же после ввода данных, когда на экране находится диалоговое окно Поиск решения.
Выбираем опцию Параметры… На экране появляется диалоговое окно Параметры поиска решения. Рисунок 6 – Окно ПАРАМЕТРЫ ПОИСКА РЕШЕНИЯ
-- С помощью команд, находящихся в этом диалоговом окне, можно вводить условия для решения задач оптимизации всех классов. С наиболее важными командами, применимыми при решении конкретных задач, мы будем знакомиться по мере необходимости. Вместе с тем, команды, используемые по умолчанию, подходят для решения большей части практических задач. -- Устанавливаем флажок Линейная модель, что обеспечивает применение симпликс – метода. -- Ок. -- На экране появляется уже знакомое диалоговое окно Поиск решения. -- Выполнить. На экране: диалоговое окно Результаты поиска решения. Решение найдено и результат оптимального решения задачи приведены в таблице.
Рисунок 7 – Окно РЕЗУЛЬТАТЫ ПОИСКА РЕШЕНИЯ
На экране появляется оптимальное решение. Оно представлено в виде таблицы 2:
Таблица 2 - Оптимальное решение задачи линейного программирования
Результаты моделирования . Вызовим на экран диалоговое окно Результат поиска решения (т. е. произведите операцию поиска решения заново). Щелкним на тип отчета: Результаты/ОК. Внизу экрана появится надпись Отчет по результатам, щелкните по ней мышкой. Аналогично получим отчет по устойчивости. Эти отчеты представлены в таблицах 3 и 4. Таблица 3 - Отчет по результатам
Таблица 4 - Отчет по устойчивости
Структура отчетов 1. Отчет по результатам состоит из трех таблиц: --- Таблица 1 приводит сведения о целевой функции. --- Таблица 2 приводит значения искомых переменных, полученные в результате решения задачи.
--- Таблица 3 показывает результаты оптимального решения для ограничений и для граничных условий. 2. Отчет по устойчивости состоит из двух таблиц: --- В первой таблице приводятся следующие значения для переменных: · Результат решения задачи; · Нормируемая стоимость, т. е. дополнительные двойственные переменные, которые показывают, на сколько изменяется целевая функция при принудительном включении единицы этой продукции в оптимальное решение; · Коэффициенты целевой функции; · Придельные значения приращения коэффициентов целевой функции, при которых сохраняется набор переменных, входящих в оптимальное решение. --- В таблице 2 приводятся аналогичные значения для ограничений: · Величина использованных ресурсов; · Теневая цена, т. е. двойственные оценки, которые показывают, как изменится целевая функция при изменении ресурсов на единицу; · Значения приращения ресурсов, при которых сохраняется оптимальный набор переменных, входящих в оптимальное решение. Для нелинейных моделей отчет по устойчивости содержит данные для градиентов и множителей Лагранжа (нормируемая стоимость и теневые цены) или, как еще они называются в американской литературе, объективно - обусловленные оценки (двойственные оценки). 3. Отчет по пределам показывает, в каких пределах может изменяться выпуск продукции, вошедшей в оптимальное решение, при сохранении структуры оптимального решения: · Приводятся значения, полученные для продукции 1,2,3 и 4 видов в оптимальном решении; · Приводятся нижние пределы изменения значений этих продукций. Анализ полученных отчетов Таким образом, для получения максимального дохода (прибыли) в данной модели следует производить 10 единиц продукции 1 вида и 6 единиц продукции 3 вида. Прибыль при этом составит 1320 $. Любое другое сочетание производимой продукции приведет к снижению прибыли. В данной модели трудовые и финансовые ресурсы использованы полностью. Их недоиспользование приведет к снижению прибыли в расчете на единицу трудовых ресурсов на 20 $, на 1 $ финансовых ресурсов прибыли снизится на 10 $ (теневая цена из отчета устойчивости). Показатель нормируемая стоимость из отчета по устойчивости показывает, что включение в производство продукции второго и четвертого видов нецелесообразно, экономически невыгодно предприятию, т. к. производство продукции в объеме одна единица приведет к снижению прибыли на 10 и 20 $ соответственно (нормируемая стоимость для этих видов продукции отрицательна). Нулевые двойственные оценки (нулевая нормируемая стоимость) переменных показывают, что производство продукции 1 и 3 вида экономически выгодно. Степень выгодности производства продукции 2 и 4 видов в данном случае выражается количественно. Так как трудовые и финансовые ресурсы использованы полностью, то ограничения являются жесткими (связанными). Их смягчение привело бы к увеличению прибыли. В данном случае – это привлечение трудовых и финансовых ресурсов (это привлечение будет эффективным, пока сырьевые ресурсы не будут исчерпаны).
В столбце целевой результат указаны значения целевой функции при выпуске данного типа продукции на нижнем приделе. Так, при значении 720 видно, что F=60*0+70*0+120×6+130*0=720. Выводы и предложения В оптимальном решении мы получили, что производство продукции 2 и 4 видов экономически невыгодно. С одной стороны, при этом мы будем получать максимально возможную прибыль, но с другой стороны при этом у нас упадет ассортимент продукции, то есть продукция предприятия не будет полностью удовлетворять потребностям потребителей. Спрос на продукцию упадет, следовательно, упадет и прибыль предприятия. Для повышения ассортимента продукции (для включения в производство 2 и 4 видов продукции) необходимо:
Ввод в производство 2 и 4 видов продукции позволит увеличить прибыль предприятия, но для ее выпуска необходимо привлечь дополнительные инвестиции и принять на работу дополнительный рабочий персонал. То есть, если есть возможность привлечь дополнительные трудовые и финансовые ресурсы, то нужно расширять производство и увеличивать ассортимент выпускаемой продукции. 6 Используемая литература 1. Моделирование социально – экономических процессов: Методические рекомендации по выполнению лабораторных работ средствами Excel 97/ Сост.А.С.Орлов; НовГУ им. Ярослава Мудрого.- Новгород, 1999.- 58с.
ЛАБОРАТОРНАЯ РАБОТА №2. ПРЕОДОЛЕНИЕ НЕСОВМЕСТНОСТИ. Цель лабораторной работы Преодоление несовместности задачи. Задание к лабораторной работе Общие положения. Задание 1. Изменить условия исходной задачи. 2. Преодолеть несовместность условий задачи.
Таблица 5 - Таблица исходных данных
4Вызвать исходную таблицу (лабораторная работа № 1). 4Выбрать Сервис/Поиск решения 4Изменить граничные условия для Прод 1: - В окне Ограничения установить курсор на строку $B$3 >= $B$4. - Изменить … (на экране: диалоговое окно Изменить ограничение). - Ввести изменение: $B$3 = 10. - ОК. 4Аналогично ввести значение для Прод 3: $D$3 = 6. 4Ввести дополнительное условие для Прод 2: - Добавить. - Ввести: $C$3 = 5. - ОК. 4Сервис/Поиск решения/Выполнить.
На экране появится диалоговое окно: Рисунок 1 – Окно РЕЗУЛЬТАТЫ ПОИСКА РЕШЕНИЯ Появление этого диалогового окна – признак несовместного решения. Результаты моделирования . Вызовим на экран диалоговое окно Результат поиска решения (т. е. произведите операцию поиска решения заново). --- Щелкним на тип отчета: Результаты/ОК. Внизу экрана появится надпись Отчет по результатам, щелкните по ней мышкой. Аналогично получим отчет по устойчивости и отчет по пределам. а) Отчет по результатам. Таблица 7 - Отчет по результатам
б) Отчет по устойчивости. Таблица 8 - Отчет по устойчивости
Структура отчетов 1. Отчет по результатам состоит из трех таблиц: --- Таблица 1 приводит сведения о целевой функции. --- Таблица 2 приводит значения искомых переменных, полученные в результате решения задачи. --- Таблица 3 показывает результаты оптимального решения для ограничений и для граничных условий. 2. Отчет по устойчивости состоит из двух таблиц: --- В первой таблице приводятся следующие значения для переменных: · Результат решения задачи; · Нормируемая стоимость, т. е. дополнительные двойственные переменные, которые показывают, на сколько изменяется целевая функция при принудительном включении единицы этой продукции в оптимальное решение; · Коэффициенты целевой функции; · Придельные значения приращения коэффициентов целевой функции, при которых сохраняется набор переменных, входящих в оптимальное решение. --- В таблице 2 приводятся аналогичные значения для ограничений: · Величина использованных ресурсов; · Теневая цена, т. е. двойственные оценки, которые показывают, как изменится целевая функция при изменении ресурсов на единицу; · Значения приращения ресурсов, при которых сохраняется оптимальный набор переменных, входящих в оптимальное решение. Для нелинейных моделей отчет по устойчивости содержит данные для градиентов и множителей Лагранжа (нормируемая стоимость и теневые цены) или, как еще они называются в американской литературе, объективно - обусловленные оценки (двойственные оценки). 3. Отчет по пределам показывает, в каких пределах может изменяться выпуск продукции, вошедшей в оптимальное решение, при сохранении структуры оптимального решения: · Приводятся значения, полученные для продукции 1,2,3 и 4 видов в оптимальном решении; · Приводятся нижние пределы изменения значений этих видов продукции. Анализ полученных отчетов Таким образом, для производства дополнительных 5 единиц продукции второго вида требуется 5 дополнительных единиц трудовых ресурсов и 30$ дополнительных финансовых ресурсов. Это значит, что для заданного выпуска продукции (продукция1 – 10 ед., продукция2 – 5 ед., продукция3 – 6 ед.) необходимо иметь следующее количество ресурсов: Трудовые 16 + 5 = 21(ед.) Сырьевые 110 + 0 = 110(ед.) Финансовые 100 + 30 = 130($) Прибыль при производстве 10 единиц продукции первого типа, 5 единиц продукции второго типа и 6 единиц продукции третьего типа составит 1670 $. Любое другое сочетание производимой продукции приведет к снижению прибыли, так как полученное решение является наиболее оптимальным из всех возможных. В данной модели трудовые и финансовые ресурсы использованы полностью, значит, они являются дефицитными ресурсами, в то время как сырье является не дефицитным ресурсом (оно используется в размере 109 единиц вместо 110).Так как трудовые и финансовые ресурсы в нашем случае использованы полностью, то соответствующие ограничения целевой функции являются жесткими (связанными). Показатель нормируемая стоимость из отчета по устойчивости показывает, что производство запланированных видов продукции экономически выгодно, так как показатели «нормируемая стоимость» для указанных видов продукции больше либо равны нолю. Степень выгодности производства продукции в данном случае выражается количественно. Выводы и предложения В оптимальном решении мы получили, что для производства дополнительных 5 единиц продукции второго вида требуется 5 дополнительных единиц трудовых ресурсов и 30$ дополнительных финансовых ресурсов. .С одной стороны, при этом мы будем получать максимально возможную прибыль, но с другой стороны при этом у нас упадет ассортимент продукции, то есть продукция предприятия не будет полностью удовлетворять потребностям потребителей. Спрос на продукцию упадет, следовательно, упадет и прибыль предприятия. Для повышения ассортимента продукции (для включения в производство 4 вида продукции) необходимо:
Ввод в производство 4 вида продукции позволит увеличить прибыль предприятия, но для ее выпуска необходимо привлечь дополнительные инвестиции и принять на работу дополнительный рабочий персонал, а также закупить дополнительное сырье. То есть, если есть возможность привлечь дополнительные трудовые финансовые, а также сырьевые ресурсы, то нужно расширять производство и увеличивать ассортимент выпускаемой продукции. 6 Используемая литература 1. Моделирование социально – экономических процессов: Методические рекомендации по выполнению лабораторных работ средствами Excel 97/ Сост.А.С.Орлов; НовГУ им. Ярослава Мудрого.- Новгород, 1999.- 58с.
ЛАБОРАТОРНАЯ РАБОТА №3. ПАРАМЕТРИЧЕСКИЙ АНАЛИЗ Цель лабораторной работы Выполнение параметрического анализа для задачи, решаемой при различных значениях имеющихся финансов. Задание к лабораторной работе Общие положения Под параметрическим анализом будем понимать решение задачи оптимизации при различных значениях того параметра, который ограничивает улучшение целевой функции. Задание 1. Выполнить параметрические расчеты, то есть изменить значение ограничивающего фактора (параметра) «финансы» в исходной таблице, полученной в лабораторной работе №1. Таблица 9 - Таблица исходных данных
2. Для каждого значения изменяемого параметра (50,100,150,200,250) получить оптимальное решение, пользуясь командой Поиск Решения/меню Сервис и командой Сохранить сценарий. 3. Получить итоговый сценарий, пользуясь командой Сценарии/ меню Сервис и отредактировать его. 4. Построить гистограммы для искомых переменных, получаемой прибыли и сырьевых ресурсов при всех значениях изменяемого параметра(50,100,150,200,250). 5. Вывести отчеты по устойчивости для значений изменяемого параметра: финансы=200 и финансы=250. Рисунок 3 – Окно Диспетчер сценариев - Отчет - Структура… - Ок 3. На экране появляется отчет Итоговый сценарий. В нем приведены результаты решения задачи для всех значений финансов, для различных вариантов. Для удобства работы выполняется редактирование Итогового сценария. - Для размещения на экране всего отчета Итоговый сценарий в окне маштаба назначить 50%. - Удалить столбцы B и D. - Удалить строки 5 и 10. - Ввести: - Прод1: Прод4 в ячейки C5:C8. - Прибыль в C9. - Виды ресурсов: трудовые, сырье, финансы в ячейки C10:C12. - Увеличить ширину столбца С. - Для наглядного представления данных на диаграммах необходимо: - В дробных значениях Прод1: Прод4 назначить два знака после запятой. - Дробные значения в строках Прибыль, трудовые ресурсы, сырье и финансы округлить до целых чисел. - Убрать примечание. Если не получится отредактировать Итоговый сценарий таким образом, то набрать Сценарий самостоятельно (при этом используйте только запятые). Его вид показан в таблице 4: Таблица 12 - Итоговый сценарий
Для наглядного представления результатов параметрического анализа на основании отредактированной таблицы следует построить графики. 4. n Построить гистограмму для искомых переменных. - Выделить А2:F7.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
Последнее изменение этой страницы: 2016-04-23; просмотров: 2082; Нарушение авторского права страницы; Мы поможем в написании вашей работы! infopedia.su Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав. Обратная связь - 13.58.39.23 (0.157 с.) |
