Модели линейной оптимизации в MS EXCEL

2.2.1. Решение задач линейного программирования в MS EXCEL

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

Пусть имеет некоторая целевая функция z, которая зависит от параметров , удовлетворяющих некоторым ограничениям α.

z = z(x,α).

Требуется найти такие значения параметров или функций,которые обращают величину z в максимум или минимум. Такие задачи – отыскание значений параметров, обеспечивающих экстремум функции при наличии ограничений, наложенных на аргументы, – носят общее название задач математического программирования и решаются ме­тодами теории исследования операций.

Среди задач математического программирования самы­ми простыми являются задачи линейного программирова­ния (ЗЛП).

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

Инструментом для решений задач оптимизации в MS Ехсеl служит надстройка Поиск решения. Процедура поиска решения позволяет найти оптимальное значение фор­мулы, содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданный результат, процедура изменяет значения во влияющих ячейках.

Если данная надстройка установлена, то Поиск решения запускается из меню Сервис. Если такого пункта нет, следует выполнить команду Сервис → Надстройки... и выставить флажок против надстройки Поиск решения.

Решения задачи оптимизации состоит из нескольких этапов.

А. Создание модели задачи оптимизации.

В. Поиск решения задачи оптимизации.

С. Анализ найденного решения задачи оптимизации.

Пример

Предприятие выпускает два вида железобетонных изделий: лестничные марши и балконные плиты. Для производства одного лестничного марша требуется 3,5 куб.м. бетона и 1 упаковку арматуры, а для производства плиты 1 куб.м. бетона и 2 упаковку арматуры. На каждую единицу продукции приходится 1 человеко-день трудозатрат. Прибыль от продажи одного лестничного марша составляет 200 руб., а одной плиты – 100 руб. На предприятии работает 150 человек, причем известно, что в день предприятие получает не более 240 упаковок арматуры и производит не более 350 куб.м. бетона. Требуется составить производственный план, чтобы прибыль была максимальной

Решение

1. На листе рабочей книги EXCEL заполните таблицу параметров задачи.

2. Составьте модель задачи и заполните ячейки для значений переменных (первоначально ячейки х1 и х2 заполняются числовыми значениями, например, значением 10), целевой функции (ячейка содержит формулу) и ограничений (ячейки содержат формулы) (рис. 8).

Рис. 8. Параметры задачи

3. Выполните команду Сервис →Поиск решения и установите необходимые значения в полях диалогового окна Поиск решения, добавляя ограничения в окне Добавление ограничений (рис. 9).

Рис. 9. Окно Поиск решения.

4. Нажмите на кнопку Выполнить. На экране появится окно Результаты поиска решения (рис. 10).

Рис. 10. Результаты поиска решений

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

5. В результате в ячейках с переменными задачи появятся значения соответствующие оптимальному плану (80 лестничных маршей и 70 плит в день), а в ячейке для целевой функции – значение прибыли (23000 руб.), соответствующее данному плану (рис. 11).

Рис. 11. Параметры задачи

6. В случае если полученное решение является удовлетворительным, можно сохранить оптимальный план и ознакомиться с результатами поиска, которые выводятся на отдельный лист (рис. 12).

Рис. 12. Результаты поиска

2.2.2. Двойственная задачалинейного программирования

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

Предположим, что требуется узнать, при каких ценах на ресурсы, используемые для производства бетонных из­делий, бу­дет выгоднее продать эти ресурсы, чем производить из них продукцию? Какую минимальную сумму можно получить в виде прибыли от продажи ресурсов?

Построим модель данной задачи (рис. 13).

Рис. 13. Модель двойственной задачи линейного программирования

Обозначим через y₁,y₂,y₃ цены на единицу бетона, арматуры и стоимость труда за один рабочий день. Такие цены характеризуют степень ценности ресурса для производителя и называются теневыми ценами. Целевая функция – это, с одной стороны, прибыль, которая может быть получена от продажи всех ресурсов по данным ценам. Она равна сумме произве­дений цен на значение запаса соответствующего ресурса. Но с точки зрения покупателя ресурсов значение целевой функции – это его издержки, которые желательно сде­лать как можно меньше (купить дешевле). То есть значе­ние целевой функции требуется минимизировать. Что касается ограничений задачи, то здесь необходимо учесть, что производитель стремится продать ресурсы по таким ценам, чтобы прибыль была не меньше той, которую он получил бы при производстве продукции из этих ресурсов. Таким образом, например, ему надо продать 3,5 куб. м бетона, 1 упаковку арматуры и 1 день труда в сумме не меньше, чем прибыль от производства одного лестничного марша. То есть должно выполниться ограничение:

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

Рис. 14. Исходные данные

Поиск решения дает следующий результат: теневая цена на бетон – 40, на арматуру – 0, на труд – 60 (рис. 15).

Дата добавления: 2015-07-11