Оптимизационное моделирование. Будем искать решение как этой задачи, так и остальных, рассматриваемых в текущем

Будем искать решение как этой задачи, так и остальных, рассматриваемых в текущем разделе, путем оптимизационного компьютерного моделирования в среде электронных таблиц MS Excel.

Построение модели

1. На рабочий лист Excel введите исходные данные, как показано на Рис. 15.

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

2. В ячейках В12:D12 – разместите имена переменных.

3. Ячейки B13:D13 – предназначены для значений переменных (это изменяемые в процессе поиска решения ячейки), в которых появятся искомые количества сумок женских, мужских и дорожных по завершению поиска решения.

4. E16 – целевая ячейка, в которой будет размещена формула целевой функции (2).

5. Значения ячеек, в которых размещена оптовая цена сумок являются коэффициентами ЦФ.

6. Для нахождения ЦФ следует использовать функцию Excel СУММПРОИЗВ из категории математических:

=СУММПРОИЗВ(B11: D11;B13:D13).

Значение введенной ЦФ равно нулю, т.к. значения объема производства тоже пока нулевые (Рис. 16. и 17).

Рис. 16. Диалоговое окно функции СУММПРОИЗВ

Рис. 17. Фрагмент листа Excel в режиме формул. ЦФ и влияющие массивы ячеек.

7. Для ограничений удобно построить еще одну таблицу на этом же листе Excel (Рис. 18). При вводе правых частей ограничений используйте формулы ссылок на ячейки столбца ЗАПАСЫ (строки СПРОС) – как на Рис.20. Использование формул ссылок избавит вас от дублирования содержимого ячеек с данными в ячейки таблицы ограничений, кроме того, изменения в таблице исходных данных будут синхронно отражаться в таблице ограничений.

Рис.18. Таблица ограничений в символьном режиме без формул ограничений

8. При вводе формул (левых частей) ограничений по материалу следует вновь использовать формулу СУММПРОИЗВ. При этом формулу достаточно ввести один раз в ячейку B22, сделав абсолютные ссылки[1] на диапазон ячеек, где хранятся значения переменных X1, X1 и X3 ($B$13:$D$13) (Рис. 19). Затем необходимо скопировать введенную формулу для всех остальных ограничений по материалу в ячейки диапазона B23:B28.

Рис 19. Диалоговое окно ввода функции СУММПРОИЗВ для вода ограничений по материалу

9. При вводе ограничений по спросу в левой части достаточно сделать ссылки на ячейки с искомыми значениями переменных (B13:D13). В правой части ограничения вводятся данные по условию (с помощью ссылок на ячейки диапазона B14:D14.

10. Результат ввода ограничений в режиме формул – на Рис.20.

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

Исследование модели

Оптимизация рассматриваемой модели, т.е. поиск неизвестных, при которых достигается максимум целевой функции и удовлетворяются все введенные условия, выполняется встроенной процедурой автоматического поиска решения. Из меню СЕРВИС командой ПОИСК РЕШЕНИЯ (или на закладке ДАННЫЕ в MS Office 2007 в группе АНАЛИЗ ДАННЫХ) необходимо вызывать одноименное диалоговое окно, в котором произвести следующие установки:

1. В поле УСТАНОВИТЬ ЦЕЛЕВУЮ ЯЧЕЙКУ введите адрес ЦФ E16 (щелчком по указанной ячейке).

2. Ниже, для строки РАВНОЙ, выберите параметр МАКСИМАЛЬНОМУ ЗНАЧЕНИЮ (Рис. 21.).

3. В поле ИЗМЕНЯЯ ЯЧЕЙКИ введите диапазон ячеек с искомыми переменными B13:D13 (Рис. 21).

Рис.21. Фрагмент диалогового окна ПОИСК РЕШЕНИЯ

4. Щелчком по кнопке ДОБАВИТЬ вызовите окно ДОБАВЛЕНИЕ ОГРАНИЧЕНИЯ. В этом окне выполните ссылки на ячейки подготовленной таблицы ограничений, а также выберите оператор ограничений, причем в нашем случае однотипные ограничения (по материалу и по спросу) можно ввести не отдельно по каждой строке, а массивами, например как на Рис 22. – по материалу.

Рис. 22. Окно добавления ограничения по МАТЕРИАЛУ

5. Аналогично введите ограничения по спросу:

$B$30:$B32>=$E$30:$E$32 (Рис. 24).

6. Введите ограничение по целостности переменных (оператор «целое» не вводите вручную, а выберите его среди прочих операторов ограничений);

7. Задав ограничения, из окна ПОИСК РЕШЕНИЯ кнопкой ПАРАМЕТРЫ вызовите окно ПАРАМЕТРЫ ПОИСКА РЕШЕНИЯ, где установите флажок ЛИНЕЙНАЯ МОДЕЛЬ и нажмите OK (флажок НЕОТРИЦАТЕЛЬНЫЕ ЗНАЧЕНИЯ можно в нашем случае и не устанавливать, т.к. в ограничениях эти условия уже предусмотрены и являются избыточными) - Рис. 23.

Рис. 23. Настройка Параметров поиска решения

8. Происходит возврат в окно поиска решения. Настроенное окно поиска

решения показано на Рис. 24.

Рис 24. Настройка диалогового окна ПОИСК РЕШЕНИЯ

9. Кнопкой ВЫПОЛНИТЬ запустите процедуру выполнения поиска решения.

10. Выполнение процедуры завершается выводом сообщения о завершении поиска и найденном решении (Рис 25).

Рис 25. Фрагмент рабочего листа с окном результата поиска решения