Оптимизационные расчеты и анализ

вариантов решения средствами MS EXCEL

 

Сформированная выше модель легко реализуется средствами MS EXCEL. Соответствующие EXCEL-таблицы (файл с именем ФСА. xls) представлены на рис. 2.3 – 2.6.

На рис. 2.4, а, б приведены фрагменты EXCEL-таблицы с исходными данными, промежуточными вычислениями и результатами оптимизационных расчетов. Первый фрагмент содержит исходные данные из табл. 2.2 – 2.5, коэффициенты значимости функций первого уровня – табл. 2.6 и промежуточные вычисления – в соответствии с табл. 2.7 – 2.10 по формулам (2.13).

 

 

Рис. 2.3, а. Окно надстройки «Поиск решения».

Первый фрагмент условий задачи

 

 

Рис. 2.3, б. Окно надстройки «Поиск решения».

Второй фрагмент условий задачи

 

Второй фрагмент (рис. 2.4, б) отображает данные модели (2.8) – (2.23): переменные – ячейки D41 – D43, D47 – D75, имена переменных (C41 – C43, C47 – C75), коэффициенты q_jvi (E41 – E73) и c_jvi (F41 – F73), соответствие которых условиям (2.8) – (2.23) дополнительных пояснений не требует.

 

 

Рис. 2.4, а. Первый фрагмент EXCEL-таблицы с задачей (2.8) – (2.23)

Ячейки H41, H47, H62, H66 содержат значения соответственно Q ₁, Q ₂, Q ₃, Q ₄; H74 – выражение (2.14), т.е. Q _об. Ячейки J41, J47, J62, J66 содержат значения соответственно S ₁, S ₂, S ₃, S ₄; J74 – выражение (2.15), т.е. S _об. Ячейки K41, K47, K62, K66 содержат левые части условий выбора (2.8), (2.9), (2.10), (2.11) соответственно. Ячейка J42 соответствует левой части ограничения (2.12). Ячейка N41 содержит левую часть ограничения (2.19). Ячейка N47 содержит левую часть ограничения (2.20).

 

 

Рис. 2.4, б. Второй фрагмент EXCEL-таблицы с задачей (2.8) – (2.23)

 

 

Ячейки M41, M42, M47, M62, M66, P41, P47 содержат правые части ограничений (2.8), (2.9), (2.10), (2.11), (2.12), (2.19) и (2.20) соответственно. Ячейка M74 соответствует значению P – правой части ограничения (2.18). На рис. 2.4, б это значение равно $400.

Для решения оптимизационных задач в EXCEL используется надстройка «Поиск решения». Данное средство вызывается из меню Сервис (рис. 2.6). На рис. 2.3, а и 2.3, б отображено диалоговое окно процедуры поиска решения, в котором задаются: целевая ячейка (соответствует критерию эффективности), переменные задачи (окно «изменяя ячейки») и ограничения задачи (окно «ограничения»). Все данные вводятся из EXCEL-таблицы
(рис. 2.4, а и 2.4, б) с помощью стандартных диалогов Windows. В диалогах создаются ссылки на ячейки, содержащие формулы – ограничения задачи и критерий эффективности. Рис. 2.3, а и б отображают все необходимые условия для решения задачи (2.8) – (2.23).

Кроме этого, для успешного поиска оптимальных решений задачи (2.8) – (2.23) необходимо установить дополнительные параметры вычислений (кнопка «параметры»). Их значения приведены на рис. 2.5.

На рис. 2.4, б приведен также один из результатов оптимизационных расчетов, отображаемый в ячейках переменных и ограничений. Этот результат интерпретируется следующим образом:

1) x ₁₁ = 1, u ₁₁ = 3, u ₁₂ = 0, что означает выбор типа ОП SDRAM.133, 3 микросхем по 128Мб. Общая емкость ОП составит при этом 384 Мб, цена – $99, Q ₁ = 7, 5 балла;

2) x ₂₅₃ = 1, что означает выбор одного НЖМД Seagate емкостью 40 Гб, ценой $90, Q ₂ = 17, 4 балла;

3) x ₃₂ = 1, что означает выбор самого дешевого видеоадаптера Savage 2000 за $30. Качество, правда, также самое низкое –
Q ₃ = 0, 112 балла;

 

 

Рис. 2.5. Параметры поиска решения для задачи (2.8) – (2.23)

 

Рис. 2.6. Вызов процедуры «Поиск решения» в MS EXCEL

 

4) x ₄₄₁ = 1, что означает выбор процессора Athlon фирмы AMD с тактовой частотой 850 Мгц, ценой $160 и Q ₄ = 18 баллов.

В сумме получаем S _об = $379 и Q _об = 43, 012 балла.

 

Результаты параметрического анализа модели на всем интервале значений P интерпретируются аналогичным образом (см. табл. 2.11).

 

 

Т а б л и ц а 2.11