Варианты задач для выполнения лабораторной работы будут даны на занятии.
Nbsp;
Использование надстройки Excel для решения задач линейного программирования.
Поиск решения – это надстройка EXCEL, которая позволяет решать оптимизационные задачи. Если команда Поиск решения или группа Анализ отсутствует, необходимо загрузить надстройку Поиск решения.
На вкладке Файл выберите команду Параметры, а затем – категорию Надстройки (рис. 1).
Рис. 1.
В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти.В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения (рис. 2) и нажмите кнопку ОК.
Рис. 2
Решения задач линейного программирования в MS Excel 2010
Схема решения задач линейного программирования (ЗЛП)
1 этап. Составить математическую модель.
2 этап. Ввести на рабочий лист Excel условия задачи:
а) создать форму на рабочем листе для ввода условий задачи;
б) ввести исходные данные, целевую функцию, ограничения и граничные условия.
3 этап. Указать параметры в диалоговом окне Поиск решения.
4 этап. Проанализировать полученные результаты.
Пример. Задача определения оптимального ассортимента продукции
Предприятие изготавливает два вида продукции – П 1 и П 2 , которая поступает в оптовую продажу. Для производства продукции используются два видасырья – А и В. Максимально возможные запасы сырья в сутки составляют9 и 13 ед. соответственно. Расход сырья на единицу продукции вида П 1 и П 2 –
|
|
|
Опыт работы показал, что суточный спрос на продукцию П 1 никогда не превышает спроса на продукцию П 2 более чем на 1 ед. Кроме того, известно, что спрос на продукцию П 2 никогда не превышает 2 ед. в сутки. Оптовые цены единицы продукции равны: 3 д. е. – для П 1 и 4 д. е. – для П 2 .Какое количество продукции каждого вида должно производить предприятие, чтобы доход от реализации продукции был максимальным?
Решение.
1 этап. Построим математическую модель для решения поставленной задачи.
Предположим, что предприятие изготовит x 1 единиц продукции П 1 и x 2 единиц продукции П 2 . Поскольку производство продукции ограничено имеющимися в распоряжении предприятия сырьем каждого вида и спросом на данную продукцию, а также учитывая, что количество изготовляемых изделий не может быть отрицательным, должны выполняться следующие неравенства:
Доход от реализации x 1 единиц продукции П 1 и x 2 единиц продукции П 2 составит:
Cреди всех неотрицательных решений данной системы линейных неравенств требуется найти такое, при котором функция F принимает максимальное значение .
2 этап. Создадим на рабочем листе форму для ввода исходных данных (рис. 3).
|
|
|
Заливкой выделены ячейки для ввода функций.
Рис. 3
В ячейку E5 введем формулу для целевой функции (рис. 4). Для этого, используя обозначения соответствующих ячеек в Excel, формулу для расчета целевой функции можно записать как сумму произведений каждой из ячеек, отведенной для значений переменных задачи (B3, C3), на соответствующие ячейки, отведенные для коэффициентов целевой функции (B5, C5).
Рис. 4
Аналогично в ячейки D10:D11 введены формулы для расчета левой части ограничений (рис. 5).
Рис. 5
3 этап. Указать параметры в диалоговом окне Поиск решения.
На вкладке Данные в группе Анализ выберем команду Поиск решения. В диалоговом окне Параметры поиска решения установим следующее
(рис. 6):
· в поле Оптимизировать целевую функцию выбираем ячейку со значением целевой функции – Е5;
· выбираем, максимизировать или минимизировать целевую функцию;
· в поле Изменяя ячейки переменных выбираем ячейки со значениями искомых переменных B3:C3 (пока в них нули или пусто);
· в области В соответствии с ограничениями с помощью кнопки Добавить размещаем все ограничения нашей задачи (см дополнительно Добавление ограничений (рис. 7);
· в поле Выберите метод решения указываем Поиск решения линейных задач симплекс-методом;
|
|
|
· нажимаем кнопку Найти решение.
Добавление ограничений. Для неравенств указываем в поле Ссылка на ячейки диапазон D10:D11, выбираем в раскрывающемся списке знак неравенства, в поле Ограничение выделяем диапазон F10:F11 и нажимаем кнопку Добавить (рис. 7), чтобы принять ограничение и добавить следующее ограничение. Для принятия ограничения и возврата к диалоговому окну Поиск решения нажмите кнопку Ok.
Рис. 7
Окна для добавления ограничений:
Ограничения на переменные записаны в виде системы неравенств. Каждое из неравенств преобразуется в условие для конкретной переменной. Например, условие 
преобразуем в 
(рис. 8);
Рис. 8
Условие 
(рис.9)
Рис. 9
Если условия одинаковые, то ограничения можно установить одновременно на все рассматриваемые переменные. Например, условие 
(рис. 10)
Рис. 10.
После выбора кнопки Найти решение появляется окно Результаты поиска решения (рис. 11).
Рис. 11
Для сохранения полученного решения необходимо использовать переключатель Сохранить найденное решение в открывшемся окне диалога Результаты поиска решения. После чего рабочий лист примет вид, представленный на рис. 12.
|
|
|
Рис. 12.
Сохранить модель поиска решения можно следующим образом:
1) при сохранении книги Excel после поиска решения все значения, введенные в окнах диалога Поиск решения, сохраняются вместе с данными рабочего листа. С каждым рабочим листом в рабочей книге можно сохранитьодин набор значений параметров Поиска решения;
2) если в пределах одного рабочего листа Excel необходимо рассмотретьнесколько моделей оптимизации (например, найти максимум и минимум одной функции или максимальные значения нескольких функций), то удобнее сохранить эти модели, используя кнопку Загрузить/Сохранить окна Параметрыпоиска решения. Диапазон для сохраняемой модели содержит информациюо целевой ячейке, об изменяемых ячейках, о каждом из ограничений и все значения диалога Параметры. Выбор модели для решения конкретной оптимизационной задачи осуществляется с помощью кнопки Загрузить/сохранить диалогового окна Параметры поиска решения;
3) сохранить модель можно в виде именованных сценариев, для этогонеобходимо нажать на кнопку Сохранить сценарий диалогового окна Результаты поиска решений (см. рис. 11).
Варианты задач для выполнения лабораторной работы будут даны на занятии.
Дата добавления: 2019-03-09; просмотров: 178; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!