EXCEL позволяет проводить анализ решения, полученного с использованием программы “Поиск решения”.
Для проведения анализа можно воспользоваться тремя типами отчетов, генерируемых программой “Поиск решения”. К этим отчетам относятся: отчет о результатах, отчет о чувствительности, отчет о пределах. Выбор требуемого отчета осуществляется курсором в диалоговом окне “Результаты поиска решения”.
7.1 Отчет о результатах
Отчет о результатах содержит:
|
|
Начальное и оптимальное значение целевой ячейки; |
|
|
Начальное и оптимальное значение изменяемых ячеек; |
|
|
Сведения об ограничениях. |
В отчете (см. рис.7.2) для каждого ограничения приводятся :
|
|
значение левой части ограничения; |
|
|
формула, определяющая ограничение; |
|
|
состояние ограничения (связанное или несвязанное); |
|
|
разница между правой и левой частями ограничения. |
Если разница между правой и левой частями ограничения равна нулю, то ограничение считается связанным, в противном случае - несвязанным. В частности если ограничение моделирует использование некоторого вида ресурса, то экономический смысл разницы - остаток этого ресурса.
7.2 Отчет о чувствительности
В отчете о чувствительности содержаться сведения, показывающие, как влияют на решение задачи изменения в величинах, входящих в формулы, используемые в задаче.
В EXCEL предусмотрено два вида отчета о чувствительности в зависимости от вида модели - линейная или нелинейная. Напомним, что вид модели определяется флажком “Линейная модель” в диалоговом окне “Параметры поиска решения”.
Пример отчета о чувствительности для задачи определения наиболее прибыльного объема продукции показан на рис.7.1.
|
Microsoft Excel 9.0 Отчет по устойчивости |
; | ; | ||
|
Рабочий лист: [TABL5.xls]Лист1 |
; | ; | ||
|
Отчет создан: 19.12.00 15:58:38 |
; | ; | ||
| ; | ; | ; | ; | ; |
|
Изменяемые ячейки |
; | ; | ||
| ; | ; | ; |
Результ. |
Нормир. |
| ; |
Ячейка |
Имя |
значение |
градиент |
| ; |
$B$18 |
Оптимальный выпуск изделие 1 |
65 |
0 |
| ; |
$C$18 |
Оптимальный выпуск изделие 2 |
40 |
-1052,784433 |
| ; |
$D$18 |
Оптимальный выпуск изделие 3 |
46 |
0 |
| ; |
$E$18 |
Оптимальный выпуск изделие 4 |
4 |
0 |
|
Ограничения |
; | ; | ||
| ; | ; | ; |
Результ. |
Лагранжа |
| ; |
Ячейка |
Имя |
значение |
Множитель |
| ; |
$G$5 |
токарное использов |
64270 |
0,317488972 |
| ; |
$G$6 |
фрезерное использов |
4800 |
0,303288464 |
| ; |
$G$7 |
сверлильное использов |
17098 |
0 |
| ; |
$G$8 |
расточное использов |
21860 |
0 |
| ; |
$G$9 |
шлифовальное использов |
7900 |
7,278684615 |
| ; |
$G$10 |
Комплект.детали (шт). использов |
505 |
0 |
| ; |
$G$11 |
Сбор.- нал.раб. (чел-ч). использов |
697,5 |
0 |
рис 7.1.
|
Microsoft Excel 9.0 Отчет по результатам |
; | ; | ; | ; | ||
|
Рабочий лист: [TABL5.xls]Лист1 |
; | ; | ; | ; | ||
|
Отчет создан: 19.12.00 15:58:28 |
; | ; | ; | ; | ||
| ; | ; | ; | ; | ; | ; | ; |
|
Целевая ячейка (Макс) |
; | ; | ; | ; | ||
| ; |
Ячейка |
Имя |
Исходно |
Результат |
; | ; |
| ; |
$G$22 |
Итоговая прибыль |
0 |
40683,16638 |
; | ; |
| ; | ; | ; | ; | ; | ; | ; |
|
Изменяемые ячейки |
; | ; | ; | ; | ||
| ; |
Ячейка |
Имя |
Исходно |
Результат |
; | ; |
| ; |
$B$18 |
Оптимальный выпуск изделие 1 |
0 |
65 |
; | ; |
| ; |
$C$18 |
Оптимальный выпуск изделие 2 |
0 |
40 |
; | ; |
| ; |
$D$18 |
Оптимальный выпуск изделие 3 |
0 |
46 |
; | ; |
| ; |
$E$18 |
Оптимальный выпуск изделие 4 |
0 |
4 |
; | ; |
|
Ограничения |
; | ; | ; | ; | ||
| ; |
Ячейка |
Имя |
Значение |
Формула |
Состояние |
Разница |
| ; |
$G$5 |
токарное использов |
64270 |
$G$5<=$F$5 |
связанное |
0 |
| ; |
$G$6 |
фрезерное использов |
4800 |
$G$6<=$F$6 |
связанное |
0 |
| ; |
$G$7 |
сверлильное использов |
17098 |
$G$7<=$F$7 |
не связан. |
5262 |
| ; |
$G$8 |
расточное использов |
21860 |
$G$8<=$F$8 |
не связан. |
4380 |
| ; |
$G$9 |
шлифовальное использов |
7900 |
$G$9<=$F$9 |
связанное |
0 |
| ; |
$G$10 |
Комплект.детали (шт). использов |
505 |
$G$10<=$F$10 |
не связан. |
15 |
| ; |
$G$11 |
Сбор.- нал.раб. (чел-ч). использов |
697,5 |
$G$11<=$F$11 |
не связан. |
22,5 |
| ; |
$B$18 |
Оптимальный выпуск изделие 1 |
65 |
$B$18>=0 |
не связан. |
65 |
| ; |
$C$18 |
Оптимальный выпуск изделие 2 |
40 |
$C$18>=0 |
не связан. |
40 |
| ; |
$D$18 |
Оптимальный выпуск изделие 3 |
46 |
$D$18>=0 |
не связан. |
46 |
| ; |
$E$18 |
Оптимальный выпуск изделие 4 |
4 |
$E$18>=0 |
не связан. |
4 |
| ; |
$C$18 |
Оптимальный выпуск изделие 2 |
40 |
$C$18>=$C$12 |
связанное |
0 |
| ; |
$D$18 |
Оптимальный выпуск изделие 3 |
46 |
$D$18<=$D$13 |
не связан. |
74 |
рис 7.2.
В отчете о чувствительности для каждой изменяемой ячейки дается ее оптимальное значение и компоненты приведенного градиента. Кроме того, для каждого ограничения приводится множитель Лагранжа.
Каждая компонента приведенного градиента показывает, на сколько изменится значение; целевой ячейки при увеличении значения в изменяемой ячейке на одну единицу. Множитель Лагранжа показывает, на сколько изменится (в нашем случае увеличится) значение в целевой ячейке при увеличении запаса некоторого ресурса на одну единицу.
В отчете о чувствительности для линейной задачи (см.рис.7.3) для каждой изменяемой ячейки приводятся следующие сведения:
|
|
результирующее (оптимальное) значение; |
|
|
приведенная стоимость; |
|
|
коэффициент целевой функции; |
|
|
допустимое увеличение; |
|
|
допустимое уменьшение. |
Приведенная стоимость показывает, на сколько изменится значение в целевой ячейке при увеличении значения в изменяемой ячейке на одну единицу.
Коэффициент целевой функции (целевой коэффициент) показывает взаимосвязь между изменяемой ячейкой и целевой функцией.
Допустимое увеличение показывает, на сколько можно увеличить значение целевого коэффициента до начала изменения оптимального значения в любой из изменяемых ячеек. рис 7.3.
Допустимое уменьшение показывает, на сколько можно уменьшить значение целевого коэффициента до начала изменения оптимального значения в любой из изменяемых ячеек.
|
Microsoft Excel 9.0 Отчет по устойчивости |
; | ; | ; | ||
|
Рабочий лист: [TABL5.xls]Лист1 |
; | ; | ; | ||
|
Отчет создан: 19.12.00 16:09:14 |
; | ; | ; | ||
| ; | ; | ; | ; | ; | ; |
|
Изменяемые ячейки |
; | ; | ; | ||
| ; | ; | ; |
Результ. |
Редуц. |
Целевой |
| ; |
Ячейка |
Имя |
значение |
стоимость |
Коэффициент |
| ; |
$B$18 |
Оптимальный выпуск изделие 1 |
65 |
0 |
315 |
| ; |
$C$18 |
Оптимальный выпуск изделие 2 |
40 |
-855,3147059 |
278 |
| ; |
$D$18 |
Оптимальный выпуск изделие 3 |
46 |
0 |
537 |
| ; |
$E$18 |
Оптимальный выпуск изделие 4 |
4 |
0 |
370 |
| ; | ; | ; | ; | ; | ; |
|
Изменяемые ячейки |
; | ; | ; | ||
| ; | ; | ; |
Допустимое |
Допустимое |
; |
| ; |
Ячейка |
Имя |
Увеличение |
Уменьшение |
; |
| ; |
$B$18 |
Оптимальный выпуск изделие 1 |
573,14516 |
35,56451613 |
; |
| ; |
$C$18 |
Оптимальный выпуск изделие 2 |
855,31471 |
1E+30 |
; |
| ; |
$D$18 |
Оптимальный выпуск изделие 3 |
40,090909 |
252 |
; |
| ; |
$E$18 |
Оптимальный выпуск изделие 4 |
420 |
66,81818182 |
; |
| ; | ; | ; | ; | ; | ; |
|
Ограничения |
; | ; | ; | ||
| ; | ; | ; |
Результ. |
Теневая |
Ограничение |
| ; |
Ячейка |
Имя |
значение |
Цена |
Правая часть |
| ; |
$G$5 |
токарное использов |
64270 |
0,494117647 |
64270 |
| ; |
$G$6 |
фрезерное использов |
4800 |
1,080882353 |
4800 |
| ; |
$G$7 |
сверлильное использов |
17098 |
0 |
22360 |
| ; |
$G$8 |
расточное использов |
21860 |
0 |
26240 |
| ; |
$G$9 |
шлифовальное использов |
7900 |
6,967647059 |
7900 |
| ; |
$G$10 |
Комплект.детали (шт). использов |
505 |
0 |
520 |
| ; |
$G$11 |
Сбор.- нал.раб. (чел-ч). использов |
697,5 |
0 |
720 |
| ; | ; | ; | ; | ; | ; |
|
Ограничения |
; | ; | ; | ||
| ; | ; | ; |
Допустимое |
Допустимое |
; |
| ; |
Ячейка |
Имя |
Увеличение |
Уменьшение |
; |
| ; |
$G$5 |
токарное использов |
3400 |
23460 |
; |
| ; |
$G$6 |
фрезерное использов |
255 |
247,2727273 |
; |
| ; |
$G$7 |
сверлильное использов |
1E+30 |
5262 |
; |
| ; |
$G$8 |
расточное использов |
1E+30 |
4380 |
; |
| ; |
$G$9 |
шлифовальное использов |
411,29032 |
295,6521739 |
; |
| ; |
$G$10 |
Комплект.детали (шт). использов |
1E+30 |
15 |
; |
| ; |
$G$11 |
Сбор.- нал.раб. (чел-ч). использов |
1E+30 |
22,5 |
; |
рис 7.3.
В отчете о чувствительности для линейной модели для каждого ограничения приводятся:
- ; ; значение левой части ограничения;
- ; ; теневая (условная) цена;
- ; ; значение правой части ограничения;
- ; ; допустимое увеличение;
- ; ; допустимое уменьшение.
Значение левой части ограничения показывает, например, фактическое использование ресурсов.
Теневая (условная цена) показывает изменение ( в нашем случае увеличение) значение целевой ячейки при увеличении правой части ограничения на одну единицу.
Правая часть ограничения показывает , например, имеющийся запас ресурсов.
Допустимое увеличение показывает, на сколько можно увеличить правую часть ограничения до начала изменения оптимального значения любой из изменяемых ячеек.
Допустимое уменьшение показывает, на сколько можно уменьшить правую часть ограничения до начала изменения оптимального значения любой из изменяемых ячеек.
7.3 Отчет по пределам
Содержит (результирующее) оптимальное значение целевой ячейки, а также результирующие (оптимальные) значения изменяемых ячеек с их нижними и верхними пределами и соответствующими целевыми результатами (см.рис.7.4).
Нижний предел - это наименьшее значение, которое может иметь изменяемая ячейка при условии, что ограничения еще выполняются, а значения остальных изменяемых ячеек фиксированы (равны оптимальным).
Верхний предел - это наибольшее значение, которое может иметь изменяемая ячейка при условии, что ограничения еще выполняются, а значения остальных изменяемых ячеек фиксированны (равны оптимальным).
Целевой результат - это значение целевой ячейки, когда значение изменяемой ячейки равно ее нижнему или верхнему пределу.
|
Microsoft Excel 9.0 Отчет по пределам |
; | ; | |
|
Рабочий лист: [TABL5.xls]Лист1 |
; | ; | |
|
Отчет создан: 19.12.00 16:06:24 |
; | ; | |
| ; | ; | ; | ; |
| ; | ; | ; | ; |
| ; |
Целевое |
; | ; |
|
Ячейка |
имя |
Значение |
; |
|
$G$22 |
Итоговая прибыль |
40683,17 |
; |
| ; | ; | ; | ; |
| ; | ; | ; | ; |
| ; |
Изменяемое |
; | ; |
|
Ячейка |
имя |
Значение |
; |
|
$B$18 |
Оптимальный выпуск изделие 1 |
65 |
; |
|
$C$18 |
Оптимальный выпуск изделие 2 |
40 |
; |
|
$D$18 |
Оптимальный выпуск изделие 3 |
46 |
; |
|
$E$18 |
Оптимальный выпуск изделие 4 |
4 |
; |
| ; | ; | ; | ; |
| ; |
Изменяемое |
Нижний |
Целевой |
|
Ячейка |
имя |
предел |
результат |
|
$B$18 |
Оптимальный выпуск изделие 1 |
0 |
27195,63169 |
|
$C$18 |
Оптимальный выпуск изделие 2 |
40 |
40683,16638 |
|
$D$18 |
Оптимальный выпуск изделие 3 |
0 |
20284,85459 |
|
$E$18 |
Оптимальный выпуск изделие 4 |
0 |
39203,16638 |
| ; | ; | ; | ; |
| ; |
Изменяемое |
Верхний |
Целевой |
|
Ячейка |
имя |
предел |
результат |
|
$B$18 |
Оптимальный выпуск изделие 1 |
65 |
40683,16638 |
|
$C$18 |
Оптимальный выпуск изделие 2 |
40 |
40683,16638 |
|
$D$18 |
Оптимальный выпуск изделие 3 |
46 |
40683,16638 |
|
$E$18 |
Оптимальный выпуск изделие 4 |
4 |
40683,16638 |
рис 7.4.