- •Лекция 5. Решение оптимизационных задач в электронной таблице Excel
- •Рассмотрим последовательность решения оптимизационной задачи на примере задачи получении оптимальной смеси, рассмотренной в предыдущей лекции.
- •Отчет по результатам
- •Вид итоговой таблицы, полученной в результате решения
- •Отчет по устойчивости
- •Решение, полученное после увеличения имеющегося объема ресурса 2 на 30 единиц
- •Решение модели после ввода в неё нового вида продукции
- •Отчет по пределам
- •Microsoft Excel 5.0 Отчет по пределам
- •Целочисленные линейные задачи оптимизации
- •Решение целочисленной линейной задачи
- •Целочисленные задачи с бинарными переменными
- •Исходные данные по инвестиционным проектам
- •Модель планирования капитальных вложений
- •Окончательный вид модели планирования капитальных вложений
- •Дальнейшее усложнение модели
- •Нормы времени по видам продукции
- •Усложнение модели определения производственной программы
- •Оптимальное решение усложненной модели определения производственной программы
- •Нелинейные задачи оптимизации
- •Построение нелинейной модели
- •Окончательный вид решения нелинейной модели
- •Новые понятия и термины
- •Вопросы для самопроверки
Исходные данные по инвестиционным проектам
Теперь запишем данную задачу в виде целевой функции и ограничений следующим образом.
Целевая функция: 141Х1+187Х2+121Х3+83Х4+265Х5+127Х6 max
Ограничения на объемы требуемых средств:
Ограничения на управляемые переменные:
Как нетрудно заметить, целевая функция выражает требование максимизации чистой текущей стоимости доходов от всех выбранных инвестиционных проектов, а ограничения - условия сбалансированности имеющихся и потребных средств по годам, а также бинарность управляемых переменных.
Реализация данной модели в электронной таблице Excel приведена в Табл.7.18. Данные по каждому из проектов указаны в отдельных строках. Ячейки В6:В11 резервированы для шести бинарных управляемых переменных. Формула для расчета левой части ограничения на объемы средств первого года записана в ячейке D13, а затем скопирована в ячейки Е13:Н13. Заметим, что здесь вместо ввода формулы, представляющей собой набор соответствующих сумм произведений, может быть использована функция =СУММПРОИЗВ(D6:D11, $В$6:$В$11), автоматизирующая эти расчеты.
Таблица 7.18
Модель планирования капитальных вложений
Правые части ограничений на объемы средств записаны в ячейки D14:H14. И, наконец, целевая функция записана соответствующей формулой (или функцией =СУММПРОИЗ(С6:С11, В6:В11)) в ячейку D16.
Чтобы решить модель в электронной таблице, мы должны сообщить программе ПОИСК РЕШЕНИЯ, где (в каких ячейках) находятся целевая функция, управляемые переменные и ограничения. На Рис. 7.10 мы видим диалоговое окно, которое появляется на экране после запуска указанной программы в меню СЕРВИС. В нем мы указываем, что целевая функция находится в ячейке D16, для управляемых переменных резервированы ячейки В6:В11 и добавляем соответствующие ограничения.
Рис. 7.10. Диалоговое окно "Поиск решения" при решении задачи планирования капитальных вложений
Первые три ограничения определяют бинарный характер управляемых переменных, находящихся в ячейках В6:В11. Последнее четвертое ограничение в диалоговом окне определяет требование, в соответствие с которым значения в ячейках D13:H13 должны быть меньше, чем соответствующие значения в ячейках D14:H14. Как нетрудно заметить, это требование есть не что иное как условие сбалансированности имеющихся и требуемых средств по годам реализации рассматриваемых инвестиционных проектов.
Поскольку данная модель содержит 6 управляемых бинарных переменных, в принципе может существовать 26=64 её возможных решения. Однако, если предположить, что ряд решений не попадут в область допустимых значений управляемых переменных, количество возможных вариантов еще уменьшится. Таким образом, есть все основания ожидать, что при решении данной задачи у компьютера трудностей не возникнет.
Поэтому мы можем установить самый высокий показатель точности решения (параметр "Допустимое отклонение"=0) в диалоговом окне "Параметры поиска решения" (см. Рис. 7.11). Окончательный вид полученного решения модели приведен на Табл. 7.19.
Рис. 7. 11. Диалоговое окно "Параметры поиска решения" при решении задачи планирования капитальных вложений
Таблица 7.19