Оптимизационные задачи в microsoft excel

Microsoft Excel обеспечивает решение оптимизационных задач методами задач линейного и нелинейного программирования. Модель имеет ограниченную размерность – 200 переменных.

Для оптимизационных расчетов с помощью команды меню СервисНадстройки устанавливается надстройка Microsoft Excel «Поиск решения». Модель задачи линейного программирования:

где – коэффициенты линейной функции.

– искомые оптимальные значения переменных, которые могут принимать любое, в том числе неотрицательное или целое значение;

линейная функция переменных и их коэффициентов может стремиться к минимуму, максимуму или принимать фиксированное значение.

–норма расхода «ресурса» j–го вида на выпуск «единицы» .

–ограничение на суммарный расход «ресурса» j–го вида.

Информационная технология решения оптимизационной задачи рассматривается на примере определения оптимального выпуска продукции по критерию максимума прибыли:

1. Целевая функция – линейная, стремится к максимуму.

2. Коэффициенты целевой функции – прибыль на единицу продукции.

3. Нормативы затрат ресурсов на выпуск единицы продукции считаются заданными.

4. Ограничение – объем ресурсов различного вида (трудовые, материальные ресурсы, оборудование).

5. Дополнительное ограничение – выпуск каждого вида продукции – целые числа.

Схема размещения информации на рабочем листе для оптимизационных задач приведена на рис. 13.

Рисунок 13

Порядок подготовки листа рабочей книги:

1. Вставить новый лист – команда меню ПравкаЛист (если необходимо).

2. Переименовать лист в Поиск решения 1 – команда меню ФорматЛистПереименовать.

3. Создать имя блока ячеек для коэффициентов целевой функции. Имя блока – Коэффициенты.

4. Ввести значения коэффициентов целевой функции (прибыль для единицы продукции).

5. Создать имя блока ячеек для переменных. Имя блока – Результаты.

6. Ввести 0 в ячейки блока Результаты для проверки правильности вводимых формул.

 Размерности блоков РЕЗУЛЬТАТЫ и КОЭФФИЦИЕНТЫ совпадают.

7. Ввести в свободную ячейку формулу для вычисления целевой функции: =СУММПРОИЗВ(КОЭФФИЦИЕНТЫ;РЕЗУЛЬТАТЫ).

8. Создать имя блока для ячейки целевой функции. Имя блока – ЦЕЛЬ.

9. Ввести нормативы затрат ресурсов на выпуск единицы продукции.

10. Создать имя блока нормативов затрат ресурсов на выпуск единицы продукции. Имя блока – НОРМАТИВЫ.

 Размерность блока НОРМАТИВЫ по горизонтали (число столбцов) совпадает с размерностью блоков РЕЗУЛЬТАТЫ и КОЭФФИЦИЕНТЫ.

11. Создать блок ячеек для расчета потребности в ресурсах на выпуск продукции. Имя блока – ПОТРЕБНОСТИ.

12. Ввести формулу расчета потребности первого ресурса: =СУММПРОИЗВ($A8:$F8;РЕЗУЛЬТАТ).

A8:F8 – ячейки первой строки блока НОРМАТИВЫ.

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

13. Тиражировать формулу потребности для первого ресурса во все ячейки блока ПОТРЕБНОСТИ.

14. Создать имя блока ячеек для задания ограничений расхода ресурсов. Имя блока – ЗАПАСЫ.

 Размерности блоков ПОТРЕБНОСТИ и ЗАПАСЫ совпадают. Высота этих блоков соответствует по вертикали размеру блока НОРМАТИВЫ.

15. Ввести ограничения на расход ресурсов в ячейки блока ЗАПАСЫ.

В табл. 11 приведены исходные данные для решения оптимизационной задачи.

Таблица 11

16. Выделить блок ячеек КОЭФФИЦИЕНТЫ и снять защиту ячеек – команда ФорматЯчейки, вкладка Защита.

17. Выделить блок ячеек НОРМАТИВЫ и снять защиту ячеек – команда ФорматЯчейки, вкладка Защита.

18. Выделить блок ячеек ЗАПАСЫ и снять защиту ячеек – команда ФорматЯчейки, вкладка Защита.

19. Установить защиту на лист – команда меню СервисЗащитаЗащитить лист.

 Установка защиты обеспечивает сохранение всех формул (ячейки блоков ПОТРЕБНОСТЬ, ЦЕЛЬ).

20. Выполнить команду меню СервисПоиск решения для построения модели задачи оптимизации производственной программы.

21. В диалоговом окне Поиск решения сформировать модель оптимизационной задачи вида – рис. 14.

Рисунок 14

2. Задать параметры для оптимизации – кнопка Параметры в диалоговом окне Поиск решения – рис. 15.

Рисунок 15

2. Нажать кнопку ОК и запустить задачу на решение.

Результат решения – значения ячеек блока Результаты – табл. 12. Оптимальный план: продукции Х4 – 4, продукции Х5 – 1 единица. Максимум целевой функции – 24.

2. Сформировать отчет Результаты для анализа ограничений на расход ресурсов.

 Для задач с ограничением на целые значения переменных не формируются отчеты Устойчивость и Пределы.

Таблица 12

• Отчет Результаты содержит целевую ячейку и список влияющих ячеек модели, их исходные и конечные значения, формулы для расчета ограничений. Пример отчета – табл. 13.

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

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

Таблица 13