2015-05-22
362Практическая работа
Нахождение оптимального решения с помощью СППР Excel
Теоретическое обоснование
Задачу, независимо от формы постановки, мгновенно решаются в надстройке «Поиск решения» процессора Excel.
Пример. Решить задачу максимизации
Решение.
Запускаем лист процессора Excel.
1. Оставляем ячейки Al, A2, A3 за переменными 
, соответственно.
В ячейке В1 задаем целевую функцию: =al+3*a2+a3.
2. В ячейках Cl, C2 задаем, соответственно, левые части нетривиальных ограничений:
=al+4*a2+3*a3
=3*а1-2*а2+а3
3. Открываем диалоговое окно «Поиск решения» и вводим (рис. 4.1) данные:
Рис. 4.1
Ограничения задаются через кнопку «Добавить», автоматически могут появиться знаки $.
4.Щелчок ЛКМ по кнопке «Выполнить» дает (рис. 4.2):
Рис. 4.2
5.Щелчком ЛКМ по кнопке ОК закрываем диалоговое окно «Результаты поиска решения» и выписываем ответ.
Ответ: (2, 0, 0),min z = 2.
Пример. Решить задачу
Решение. Пусть независимые переменные 
находятся в ячейках А1, А2, соответственно, целевая функция задана формулой =5+а1+3*а2 в ячейке В1, в ячейке С1 формула =al+a2. Задаем (рис. 4.3) сценарий решения:
Рис. 4.3
Нажатием «Выполнить» получаем (рис. 4.4) результаты:
Рис. 4.4
Ответ: (2, 8), max z = 31.
Рассмотрим графическое решение последней задачи.
1. В столбце А задаем последовательность значений переменной 
как
арифметическую прогрессию с первым членом, равным нулю, разностью 0,2, предельным значением 6.
2. В ячейке В1 вводим формулу = 10-а1 и копируем ее в столбец В. Прямые
зададим позже как границы рисунка.
3.Построение линии уровня, соответствующей значению 
, проводится по таблице значений. Вводим в ячейку С1 формулу =-(5+а1)/3 и копируем ее в столбец С.
4.Выделяем диапазон А1:С31, командами Диаграмма 
Точечная приходим (рис. 4.5) к рисунку:
Рис. 4.5
5. Уберем на рисунке лишнее. Устанавливаем СКМ на оси X так, чтобы ниже ее появилась надпись Ось X (категорий), и щелкаем ПКМ. Появляется (рис. 4.6) контекстное меню
Рис. 4.6
Щелчок ЛКМ по строке «Формат оси» открывает (рис. 4.7) диалоговое окно:
Рис. 4.7
Устанавливаем в нем максимальное значение: 6, нажимаем ОК. Аналогичным образом по оси Y задаем максимальное значение: 8.
6. Таким же образом с помощью ПКМ, задается граница области построения и форматируются ряды данных. В результате рис. 4.5 приводится, к виду (рис. 4.8):
Рис. 4.8
Из него видно, что точкой выхода линий уровня из многоугольника допустимых решений является точка (2, 8).
Если последняя задача имеет канонический вид
(1)
то в ячейки B1, C1, C2, С3 соответственно вводятся формулы:
=5+a1+3*a1, =a1+a2+a3, =a1+a4, =a2+a5
Задается (рис..4.9) следующий сценарий решения:
Рис. 4.9
Результаты поиска решения (рис. 4.10) совпадают с результатами, полученными ранее:
Рис. 4.10
Индивидуальные варианты заданий
| Вариант | Задание 1,2 | Задание 3, 4 |
| -7 x 1-6 x 2-10 x 3 ≥ -2 -12 x 1+3 x 2-14 x 3 ≥ -9 L (x) = 11 x 1+6 x 2+12 x 3 → max. | -7 x 1-6 x 2 ≥ -2 -12 x 1+3 x 2 ≥ -9 L (x) = 11 x 1+6 x 2 → max. | |
| 8 x 1+3 x 2-12 x 3 ≤ 7 - x 1+6 x 2+9 x 3 ≤ 13 L (x) = -10 x 1-12 x 2-4 x 3 → min. | 8 x 1+3 x 2 ≤ 7 - x 1+6 x 2 ≤ 13 L (x) = -10 x 1-12 x 2 → min. | |
| -8 x 1+13 x 2≥10 8 x 1+2 х 2≤2 L (x)=8 x 1+4 x 2→ max. | -8 x 1+13 x 2≥10 8 x 1+2 х 2≤2 L (x)=8 x 1+4 x 2→ max. | |
| 2 x 1+3 x 2+4 x 3≥15 x 1+2 x 2+3 х 3≥12 L (x)=-3 x 1-4 х 2-3.5 х 3→ max. | 2 x 1+3 x 2≥15 x 1+2 x 2≥12 L (x)=-3 x 1-4 х 2→ max. | |
| 2.5 x 1+ х 2≥10 2.8 x 1+2 х 2≥8 3 x 1+5 х 2≥12 L (x)=4 x 1+5 x 2→ min. | 2.5 x 1+ х 2≥10 2.8 x 1+2 х 2≥8 3 x 1+5 х 2≥12 L (x)=4 x 1+5 x 2→ min. | |
| 2 x 1+3 х 2+ х 3≥16 x 1+4 х 2+2 х 3≥12 x 1+5 x 2+ х 3≥10 L (x)=2 x 1+4 x 2+3 x 3→ min. | 2 x 1+3 х 2≥16 x 1+4 х 2≥12 L (x)=2 x 1+4 x 2→ min. | |
| 2 x 1+ x 2+3 x 3+2 x 4≥12 1.5 x 1+2 x 2+2 х 3+ x 4≥10 L (x)=2 x 1+3 x 2+ х 3+2.5 x 4→ min. | 2 x 1+ x 2≥12 1.5 x 1+2 x 2≥10 L (x)=2 x 1+3 x 2→ min. | |
| 8 x 1-7 х 2≥3 14 x 1-6 x 2≥10 L (x)=4 x 1+11 x 2→ min. | 8 x 1-7 х 2≥3 14 x 1-6 x 2≥10 L (x)=4 x 1+11 x 2→ min. | |
| -13 x 1+6 x 2-13 x 3-2 x 4>12 -11 x 1+9 x 2+13 x 3+14 x 4<-8 L (x)=9 x 1+8 x 2+8 x 3+7 x 4→ max. | -13 x 1+6 x 2>12 -11 x 1+9 x 2<-8 L (x)=9 x 1+8 x 2→ max. | |
| 6 x 1+5 x 2≥5 -7 x 1+11 x 2≥9 -l0 x 1+5 x 2 ≥3 L (x)=-8 x 1-14 x 2→ max. | 6 x 1+5 x 2≥5 -7 x 1+11 x 2≥9 -l0 x 1+5 x 2 ≥3 L (x)=-8 x 1-14 x 2→ max. | |
| 8 x 1+8 x 2≥4 -5 x 1+9 x 2≥7 L (x)=11 x 1+6 x 2→ max. | 8 x 1+8 x 2≥4 -5 x 1+9 x 2≥7 L (x)=11 x 1+6 x 2→ max. | |
| - х 1+ х 2+3 х 3+ х 4≤2 x 1+ x 2+ x 3+ x 4≥3 L (x)=4 x 1+10 x 2+9 x 3+3 x 4→ max. | - х 1+ х 2≤2 x 1+ x 2≥3 L (x)=4 x 1+10 x 2→ max. | |
| 3 x 1- x 2-2 x 3≥1 -2 x 1-4 х 2+5 х 3≤-3 x 1-2 x 2+ х 3≤-1 L (x)=-4 x 1-3 x 2- х 3→ max. | 3 x 1- x 2≥1 -2 x 1-4 х 2≤-3 x 1-2 x 2≤-1 L (x)=-4 x 1-3 x 2→ max. | |
| - x 1+ x 2+3 х 3- x 4≤2 x 1+ x 2+ х 3+3 х 4≥3 L (x)=4 x 1+10 x 2+9 x 3+3 x 4→ max. | - x 1+3 х 2≤2 x 1+ х 2≥3 L (x)=4 x 1+9 x 2→ max. | |
| - x 1+ x 2+3 х 3- x 4≤2 - x 1+ x 2+ х 3-3 x 4≥3 L (x)=4 x 1+10 x 2+9 x 3+3 x 4→ max. | 3 х 1- x 2≤2 х 1-3 x 2≥3 L (x)=9 x 1+3 x 2→ max. | |
| -8 x 1+13 x 2≥10 8 x 1+2 х 2≤2 L (x)=8 x 1+4 x 2→ max. | -8 x 1+13 x 2≥10 8 x 1+2 х 2≤2 L (x)=8 x 1+4 x 2→ max. | |
| 2 x 1+3 x 2+4 x 3≥15 x 1+2 x 2+3 х 3≥12 L (x)=-3 x 1-4 х 2-3.5 х 3→ max. | 2 x 1+3 x 2≥15 x 1+2 x 2≥12 L (x)=-3 x 1-4 х 2→ max. | |
| 2.5 x 1+ х 2≥10 2.8 x 1+2 х 2≥8 3 x 1+5 х 2≥12 L (x)=4 x 1+5 x 2→ min. | 2.5 x 1+ х 2≥10 2.8 x 1+2 х 2≥8 3 x 1+5 х 2≥12 L (x)=4 x 1+5 x 2→ min. | |
| 2 x 1+3 х 2+ х 3≥16 x 1+4 х 2+2 х 3≥12 x 1+5 x 2+ х 3≥10 L (x)=2 x 1+4 x 2+3 x 3→ min. | 2 x 1+3 х 2≥16 x 1+4 х 2≥12 x 1+5 x 2≥10 L (x)=2 x 1+4 x 2→ min. | |
| 2 x 1+ x 2+3 x 3+2 x 4≥12 1.5 x 1+2 x 2+2 х 3+ x 4≥10 L (x)=2 x 1+3 x 2+ х 3+2.5 x 4→ min. | 2 x 1+ x 2≥12 1.5 x 1+2 x 2≥10 L (x)=2 x 1+3 x 2→ min. | |
| - х 1+ х 2+3 х 3+ х 4≤2 x 1+ x 2+ x 3+ x 4≥3 L (x)=4 x 1+10 x 2+9 x 3+3 x 4→ max. | - х 1+3 х 2≤2 x 1+ x 2≥3 L (x)=4 x 1+9 x 2→ max. | |
| - x 1+ x 2+3 х 3- x 4≤2 x 1+ x 2+ х 3+3 х 4≥3 L (x)=4 x 1+10 x 2+9 x 3+3 x 4→ max. | - x 1- x 2≤2 x 1+3 х 2≥3 L (x)=4 x 1+3 x 2→ max. | |
| - x 1+ x 2+3 х 3- x 4≤2 - x 1+ x 2+ х 3-3 x 4≥3 L (x)=4 x 1+10 x 2+9 x 3+3 x 4→ max. | 3 х 1- x 2≤2 х 1-3 x 2≥3 L (x)=9 x 1+3 x 2→ max. | |
| -8 x 1+13 x 2≥10 8 x 1+2 х 2≤2 L (x)=8 x 1+4 x 2→ max. | -8 x 1+13 x 2≥10 8 x 1+2 х 2≤2 L (x)=8 x 1+4 x 2→ max. | |
| 2 x 1+3 x 2+4 x 3≥15 x 1+2 x 2+3 х 3≥12 L (x)=-3 x 1-4 х 2-3.5 х 3→ max. | 2 x 1+3 x 2≥15 x 1+2 x 2≥12 L (x)=-3 x 1-4 х 2→ max. | |
| 2.5 x 1+ х 2≥10 2.8 x 1+2 х 2≥8 3 x 1+5 х 2≥12 L (x)=4 x 1+5 x 2→ min. | 2.5 x 1+ х 2≥10 2.8 x 1+2 х 2≥8 3 x 1+5 х 2≥12 L (x)=4 x 1+5 x 2→ min. | |
| 2 x 1+3 х 2+ х 3≥16 x 1+4 х 2+2 х 3≥12 x 1+5 x 2+ х 3≥10 L (x)=2 x 1+4 x 2+3 x 3→ min. | 3 х 1+ х 2≥16 4 х 1+2 х 2≥12 L (x)=4 x 1+3 x 2→ min. | |
| 2 x 1+ x 2+3 x 3+2 x 4≥12 1.5 x 1+2 x 2+2 х 3+ x 4≥10 L (x)=2 x 1+3 x 2+ х 3+2.5 x 4→ min. | x 1+2 x 2≥12 2 x 1+ x 2≥10 L (x)=3 x 1+2.5 x 2→ min. | |
| 8 x 1-7 х 2≥3 14 x 1-6 x 2≥10 L (x)=4 x 1+11 x 2→ min. | 8 x 1-7 х 2≥3 14 x 1-6 x 2≥10 L (x)=4 x 1+11 x 2→ min. | |
| 8 x 1+8 x 2≥4 -5 x 1+9 x 2≥7 L (x)=11 x 1+6 x 2→ max. | 8 x 1+8 x 2≥4 -5 x 1+9 x 2≥7 L (x)=11 x 1+6 x 2→ max. |
Задания:
1. С использованием надстройки «Поиск решения» найти неотрицательные значения переменных, удовлетворяющие системе ограничений, и обращающие в максимум целевую функцию.
2. С использованием надстройки «Поиск решения» найти неотрицательные целые значения переменных, удовлетворяющие системе ограничений.
3. Решить графически систему.
4. Решение задачу симплекс методом и сравнить с результатами, полученными в предыдущих заданиях.
Отчет о лабораторной работе должен содержать:
1. Условие задачи.
2. Описание порядка решения.
3. Графики и диаграммы (если предусмотрено условием задачи).
3. Ответ.
Литература:
1. А.В. Леоненков. Решение задач оптимизации в среде MS Excel. - С.Пб.: БХВ,- 2005.
2. О.А. Сдвижков. Математика в Excel 2002. –М.: СОЛОН-Пресс, 2004.
