- •Методические указания
- •2 Примеры экстремальных задач
- •2.1 Задача определения наиболее прибыльного объема выпуска продукции
- •2.2 Транспортная задача
- •3 Включение компьютера и запуск excel
- •3.1 Порядок включения компьютера
- •3.2 Порядок запуска программы excel
- •4 Основные приемы работы в excel
- •4.2 Селекция блока ячеек
- •4.3 Ввод данных в ячейки
- •4.4 Копирование формул
- •4.5 Установка границ ячеек и блоков ячеек
- •4.6 Присвоение имен ячейкам и блокам ячеек
- •4.1 Селекция ячейки
- •4.7 Изменение ширины столбца
- •4.8 Изменение цвета ячейки и блоков ячеек
- •5 Решение транспортной задачи
- •5.1 Ввод исходных данных
- •5.2 Разметка блоков ячеек рабочего листа excel
- •5.3 Формирование элементов математической модели
- •5.4 Формирование целевой функции
- •6 Решение задачи определения наиболее прибыльного объема выпуска продукции
- •6.1 Ввод исходных данных
- •6.2 Разметка блоков ячеек рабочего листа excel
- •6.3 Формирование элементов математической модели
- •6.4 Формирование целевой функции
- •6.5 Настройка программы “Поиск решения”
- •7. Анализ результатов решения
- •7.1 Отчет о результатах
- •7.2 Отчет о чувствительности
- •7.3 Отчет по пределам
- •8 Выход из excel и выключение компьютера
- •8.1 Выход из excel
- •8.2 Выключение компьютера
- •9 Контрольные вопросы и задания
- •Задание 4 [ 3 ]
- •Задание 5 [ 3 ]
- •Задание 6 [ 3 ]
- •Задание 7 [ 3 ]
- •Задание 8 [ 3 ]
- •Задание 9 [ 3 ]
- •Задание 10 [ 3 ]
- •Задание 11 [ 3 ]
- •Задание 12 [ 3 ]
- •11 Ответы
7. Анализ результатов решения
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.
В отчете о чувствительности для линейной модели для каждого ограничения приводятся:
- ; ; значение левой части ограничения;
- ; ; теневая (условная) цена;
- ; ; значение правой части ограничения;
- ; ; допустимое увеличение;
- ; ; допустимое уменьшение.
Значение левой части ограничения показывает, например, фактическое использование ресурсов.
Теневая (условная цена) показывает изменение ( в нашем случае увеличение) значение целевой ячейки при увеличении правой части ограничения на одну единицу.
Правая часть ограничения показывает , например, имеющийся запас ресурсов.
Допустимое увеличение показывает, на сколько можно увеличить правую часть ограничения до начала изменения оптимального значения любой из изменяемых ячеек.
Допустимое уменьшение показывает, на сколько можно уменьшить правую часть ограничения до начала изменения оптимального значения любой из изменяемых ячеек.