Практическая работа №1 по МТП

Цель работы: освоение инструментария Поиск решения для решения оптимизационных задач в MS Excel.

Пример 1: Задача определения оптимального ассортимента продукции
Предприятие изготавливает четыре вида продукции – A, B, C и D. Для производства продукции используются ресурсы – трудовые, материальные, финансовые. Максимальный запас ресурсов на производстве 800, 2000, 2900 соответственно. Расход ресурсов на единицу производства продукции
A, B, C и D и предельно допустимые значения выпуска каждого вида даны в табл. 1.

Прибыль от реализации единицы продукции равны: 8 д. е. – для A, 10 д. е. – для B, 7 д. е. – для C, 8 д. е. – для D.
Какой объем продукции каждого вида должно производить предприятие, чтобы прибыль от реализации продукции была максимальной?

Решение. Составим математическую модель для решения поставленной задачи.
Обозначим переменные:
x1 – объем произведенной продукции вида А;
x2 – объем произведенной продукции вида B;
x3 – объем произведенной продукции вида C;
x4 – объем произведенной продукции вида D
Поскольку производство продукции ограничено имеющимися в распоряжении предприятия ресурсами и спросом на данную продукцию, а также учитывая, что объем изготовляемой продукции не может быть отрицательным, должны выполняться следующие неравенства:

Прибыль от реализации продукции составит:
Cреди всех неотрицательных решений системы линейных неравенств требуется найти такое, при котором функция F принимает максимальное значение Fmax.
Рассматриваемая задача относится к разряду типовых задач оптимизации производственной программы предприятия. В качестве критериев оптимальности в этих задачах могут быть также использованы прибыль, себестоимость, номенклатура производимой продукции, затраты станочного времени и др.

Создадим на рабочем листе EXEL. таблицу для ввода исходных данных. Заливкой выделены ячейки для ввода формул и вывода результата.

Заполним таблицу.
Блок ячеек В3:Е3 содержит оптимальное решение, значение этих ячеек будет получено в результате решения задачи.
Блок ячеек В4:Е4 содержит значения прибыли от реализации продукции. В ячейках В9: Е13 отображен расход ресурсов на единицу производства продукции A, B, C и D и предельно допустимые значения выпуска каждого вида.
Для вычисления целевой функции в ячейке F4 используем функцию
=СУММПРОИЗВ(B3:E3;B4:E4)

В ячейки F9:F11 введены формулы для расчета ограничений по ресурсам. Ниже представлена таблица с исходными данными, целевой функцией, ограничениями и граничными условиями.

Остановимся подробно на добавлении ограничений в область В соответствии с ограничениями.
Все ограничения указаны в системе. Для добавления ограничения необходимо выбрать кнопку Добавить. Отобразится окно диалога Добавление ограничений.
Добавляем ограничения для неравенств:
8?1 + 3?2 + 4?2 + 4?2 ≤ 800
7?1 + 8?2 + 12?2 + 10?2 ≤ 2000
15?1 + 14 + 13?2 + 14?2 ≤ 2900

В поле Ссылка на ячейки указываем адрес диапазона F9:F11, выбираем в раскрывающемся списке знак неравенства ≤, в поле Ограничение выделяем диапазон G9:G11 и нажимаем кнопку Добавить. Результатом этого действия будет добавление текущего ограничения в список ограничений, поля окна Добавление ограничения будут очищены для ввода следующего ограничения.
Порядок ввода ограничений не имеет значения. Главное — не забыть ни одно из ограничений.

Ограничения ?2 ≥ 0 и ?4 ≥ 0 можно не добавлять, т.к. в окне Параметры поиска решения установлен флажок в поле Сделать переменные без ограничений неотрицательными.
Для принятия последнего ограничения и возврата к диалоговому окну.
Параметры поиска решения нажмем кнопку OK.
После указания всех необходимых параметров в диалоговое окно Параметры поиска решения примет вид

На вкладке Данные в группе Анализ выберем команду Поиск решения.
На экране отобразится диалоговое окно Параметры поиска решения, в котором установим следующие параметры:

в поле Оптимизировать целевую функцию указываем адрес ячейки со значением целевой функции – F4;
выбираем нахождение максимума целевой функции;
в поле Изменяя ячейки переменных указываем адреса ячеек со значениями искомых переменных B3:Е3;
в области В соответствии с ограничениями с помощью кнопки Добавить размещаем все ограничения задачи (добавление ограничений будет рассмотрено ниже);
установим флажок в поле Сделать переменные без ограничений неотрицательными;
в списке Выберите метод решения указываем Поиск решения линейных задач симплекс-методом;
нажимаем кнопку Найти решение.

После выбора кнопки Найти решение отобразится окно Результаты поиска решения.

Для сохранения полученного решения необходимо установить переключатель Сохранить найденное решение и нажать кнопку ОК. После чего на рабочем листе отобразится решение задачи.

Таким образом, максимальная прибыль при реализации продукции будет получена в размере 1601 д. е. при следующем плане производства:
12,00 – объем продукции типа А;
25,00 – объем продукции типа B;
3,00 – объем продукции типа C;
124,25– объем продукции типа D;

Кроме вставки оптимальных значений в изменяемые ячейки, Поиск решения позволяет представлять результаты в виде трех отчетов: Результаты, Устойчивость и Пределы. Для генерации одного или нескольких отчетов необходимо выделить их названия в окне диалога Результаты поиска решения. Для выбора нескольких отчетов из списка использовать клавишу Shift.

Отчет по результатам содержит три таблицы: в первой приведены сведения о целевой функции до начала вычисления и окончательное значение, во второй – значения искомых переменных: исходные и полученные в результате решения задачи, в третьей – результаты оптимального решения для ограничений. Этот отчет также содержит информацию о таких параметрах каждого ограничения, как состояние и допуск. Состояние принимает значение «Привязка», если вводимые ограничения совпадают с ограничениями, полученными в результате вычислений, и значение «Без привязки» в противном случае.

По значениям столбца Допуск можно сделать вывод о недоиспользованных ресурсах. В рассматриваемой задаче трудовые ресурсы были использованы полностью (значение в столбце Допуск равно 0), материальные ресурсы использованы не полностью (недоиспользованными оказались 137,5 единиц), также недоиспользовано 171,5 ед. финансовых ресурсов.

Отчет по устойчивости содержит два блока: Ячейки переменных и Ограничения. Первый блок содержит информацию по допустимому увеличению и уменьшению коэффициентов целевой функции при условии, что объем оптимальной продукции не изменится. Второй блок касается увеличения и уменьшения значений ограничений.

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