Технология решения ЗАДАЧ ЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ с помощью функции ПОИСКА РЕШЕНИЙ

Задача оптимального использования ресурсов

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

Фабрика имеет в своем распоряжении определенное количество ресурсов: рабочую силу, деньги, сырье, оборудование, производственные площади и т.п. Допустим, например, ресурсы трех видов: рабочая сила, сырье и оборудование - имеются в количестве соответственно 80 (чел/дней), 480 (кг) и 130 (станко/ч). Фабрика может выпускать ковры четырех видов. Информация о количестве единиц каждого ресурса, необходимых для производства одного ковра каждого вида, и доходах, получаемых предприятием от единицы каждого вида товаров, приведена в таблице 1.

Таблица 1

Укладываясь в отведенные ресурсы, требуется найти такой план выпуска продукции, при котором будет максимальной общая стоимость выпускаемой продукции.

Обозначим через Х₁,Х₂,Х₃,X₄ количество ковров каждого типа.

Экономико-математическая модель задачи.

Целевая функция - это выражение, которое необходимо максимизировать: F(x) = 3X₁ + 4Х₂ + ЗХ₃ + Х₄.

Ограничения по ресурсам

7Х₁ + 2Х₂ + 2Х₃ + 6Х₄ <= 80,

5Х₁ + 8Х₂ + 4Х₃ + ЗХ₄ <= 480,

2Х₁+4Х₂ + Хз + 8Х₄<=130,

Х₁, Х₂,Хз,Х₄>=0.

Технология решения ЗАДАЧ ЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ с помощью функции ПОИСКА РЕШЕНИЙ

Поиск решения - это надстройка EXCEL, которая позволяет решать оптимизационные задачи. Если в меню Сервис отсутствует команда "Поиск решения, значит, необходимо загрузить надстройку следующим образом.

Выберите команду Сервис – Надстройки (Add-Ins) и активизируйте надстройку Поиск решения (Solver- ADD-in) .

Для решения задачи линейного программирования (ЗЛП) необходимо:

1. Создать форму для ввода условий задачи.

2. Указать адреса ячеек, в которые будет помещен результат решения (изменяемые ячейки).

3. Ввести исходные данные.

4. Ввести зависимость для целевой функции.

5. Ввести зависимости для ограничений.

6. Указать назначение целевой функции (установить целевую ячейку).

7. Ввести ограничения.

8. Ввести параметры для решения ЗЛП

Для задачи подготовим форму для ввода условий (см. рис. 1). Весь текст на рис. 1 является комментарием и на решение задачи не влияет.

Рис. 1. Введена форма для ввода данных

1. В нашей задаче оптимальные (изменяемые количества продукции) значения вектора Х= (Х₁, Х₂, X₃, Х₄) будут помещены в ячейках ВЗ:ЕЗ, оптимальное значение целевой функции - в ячейке F4.

2. Введем исходные данные в созданную форму. Получим результат, показанный на рис. 2.

Рис. 2 Данные введены

3. Введем зависимость для целевой функции (обозначим через М1 следующее действие – «один щелчок левой кнопкой мыши»):

• Курсор в F4.

• Курсор на кнопку Мастер функций (Insert – Function).

• M1. На экране диалоговое окно Мастер функций шаг 1 из 2.

• Курсор в окно Категория (Or select a category) на категорию Математические(Math & Trig).

• M1.

• Курсор в окно Функции на СУММПРОИЗВ(SUMPRODUCT).

• M1.

• В массив 1 ввести (адреса ячеек во все диалоговые окна удобно вводить не с клавиатуры, а протаскивая мышь и выделяя те ячейки, чьи адреса следует ввести) - В$3:Е$3.

• В массив 2 ввести В4:Е4.

• ОК. На экране: в F4 введена функция, как показано на рис.3.

Рис.3. Вводится функция для вычисления целевой функции.

5. Введем зависимость для левых частей ограничений:

• Курсор в F4.

• Копировать в буфер.

• Курсор в F7.

• Вставить из буфера.

• Курсор в F8.

• Вставить из буфера.

• Курсор в F9.

• Вставить из буфера.

На этом ввод зависимостей закончен.

Запуск Поиска решения

После выбора команд Сервис(Tools) =>Поиск решения(Solver) появится диалоговое окно Поиск решения(Solver Parameters).

В диалоговом окне Поиск решения есть три основных параметра, которые следует установить:

• Установить целевую ячейку (Set target cell)
• Изменяя ячейки(By Changing cells)
• Ограничения(Subject to the Constraints)

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

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

6. Назначение целевой функции (установить целевую ячейку).

• Курсор в поле «Установить целевую ячейку».
• Ввести адрес $F$4.
• Ввести направление целевой функции: Максимальному значению. Ввести адреса искомых переменных:
• Курсор в поле «Изменяя ячейки».
• Ввести адреса: В$3:Е$3.

12
• Далее ⇒