Praktikum_2008
.pdf
строки «значения переменных хj», копируется в остальные строки этого столбца.
Для удобства ввода условий в процедуру Поиск решения их следует сгруппировать. Например, вначале записать все условия типа ≤, затем ≥, затем =.
При такой подготовке в процедуру придется ввести не больше трех Групповых условий, что для больших задач существенно сокращает время ввода.
После подготовки исходных данных вызывается процедура
Поиск решения (Данные → Анализ →Поиск решения).
Веё диалоговом окне (рис. 3.29) устанавливается адрес ячейки, где находится целевая функция (D3), нужное значение экстремума (max), диапазон адресов значений переменных (B2:C2), диапазон адресов левых и правых частей условий типа ≤ (Добавить →D4:D5≤F4:F5 → Добавить), диапазон адресов левых и правых частей условий типа ≥ (Добавить – D6≥F6 – «OK»).
Вследующем диалоговом окне Параметры устанавливают-
ся флажки в позициях Линейная модель и Неотрицательные значения переменных. Вернувшись к первому диалоговому окну, нажимаем Выполнить.
Рис. 3.29
В строке «Значения переменных хj» таблицы (рис. 3.30) получаем оптимальные значения неизвестных (х1=6, х2=0), а в ячей-
131
ке целевой функции (D3) - соответствующее её максимальное значение (Z=6).
Рис. 3.30
Если решение получено, отмечается позиция Сохранить решение и интересующие пользователя типы отчетов (результаты, устойчивость, пределы).
Отчеты выводятся на отдельные листы Excel, которые вызывают из командной строки.
Если выводится сообщение, что решение не найдено, то либо задача поставлена некорректно (условия несовместимы или экстремум в бесконечности), либо поставленные в диалоговом окне Параметры ограничения по числу итераций, времени решения и точности слишком жесткие и их нужно ослабить (увеличить число итераций и время решения, уменьшить требования к точности).
Для решения задачи с нелинейной целевой функцией нужно в ячейку целевой функции ввести нужное выражение и в диалоговом окне Параметры снять флажок в позиции Линейная модель.
Например, чтобы решить рассмотренную выше задачу с целевой функцией
R = х12 −2х22 → max
набираем эту функцию в ячейке (F3) и указываем ее в диалоговом окне Поиск решения.
В результате получим х1=6,35, х2=1,06, R=38,12 (рис. 3.31).
132
Рис. 3.31
Для получения целочисленных решений достаточно в ограничения добавить диапазон изменяемых ячеек с условием целочисленные (цел).
Добавив условия целочисленности, в нашей задаче получим х1=6, х2=0, R=36 (в диалоговом окне Поиск решения - Добавить
→ В2:С2=целое).
Если просто округлить предыдущее решение до целых чисел х1=6, х2=1, то целевая функция R=34, что хуже целочисленного max.
Процедура Поиск решения успешно справляется не только с задачами квадратичного программирования, но и с задачами собственно нелинейного программирования.
Пусть нужно найти max целевой функции
R =
при условиях
−2х1 +3х22 ≤ 12,
≤ 18,
х12 + х22 ≥ 6,
x1 , x2 ≥ 0.
Исходные данные можно записать, например, в соответствии с рис. 3.32.
133
Рис. 3.32
В результате решения получим х1=2.470, х2=0.309, R=14.89
При решении транспортной задачи ЛП тоже важно удобно расположить исходные данные в таблице Excel.
Пусть нужно найти min затрат при перемещении грузов из трех пунктов отправления с запасами а1=8, а2=5, а3=7 в четыре пункта назначения с потребностями b1=4, b2=4, b3=2, b4=10.
Затраты на перемещение единицы груза по каждому маршруту соответствуют матрице:
Можно рекомендовать расположить исходные данные, как показано на рис. 3.33
3 |
4 |
5 |
6 |
4 |
4 |
2 |
1 |
2 |
1 |
6 |
8 |
134
Рис. 3.33
Исходные значения всех неизвестных xij принимают произвольно, например, равными единицам.
Целевая функция формируется функцией СУММПРОИЗВ, в диалоговое окно которой первый массив сij и второй xij вводятся движениями курсора по главным диагоналям соответствующих матриц. Значения левых частей условий по запасам и по потребностям формируют функцией сумм (рис. 3.33).
Отметим, что условием существования решения является равенство суммы запасов и суммы потребностей:
m
∑ai
i=1
n
= ∑bj .
j=1
|
m |
n |
|
|
|
|
Если |
∑ai > ∑bj |
, для обеспечения баланса добавляется фик- |
||||
i=1 |
j=1 |
|||||
|
|
|
|
m |
n |
|
тивный пункт назначения с потребностями |
bn+1 = ∑ai −∑bj |
. Чтобы |
||||
i=1 |
j=1 |
|||||
не менять величину целевой функции, затраты на перемещение грузов к фиктивному пункту принимаются равными нулю:
Ci, n+1 = 0 (i=1, …, m).
135
|
m |
n |
|
|
Аналогично, если |
∑ai < ∑bj |
, добавляется фиктивный пункт |
||
i=1 |
j=1 |
|||
|
|
m |
n |
|
отправления с запасами |
am+1 = ∑bj −∑ai |
и затратами на перемеще- |
||
|
i=1 |
j=1 |
||
ние
Cm+1, j = 0 (j=1, …, n).
Вдиалоговом окне Поиск решения устанавливают адрес целевой функции, вид экстремума, вводят адреса массива неизвестных (курсор по диагонали матрицы хij).
Вокно условий вводят (Добавить) диапазон адресов левых частей условий по запасам, знак равенства и соответствующий диапазон правых частей условий по запасам. Затем выполняют то же с условиями по потребностям (рис. 3.34).
Вокне Параметры отмечают Линейная модель и Неотри-
цательные значения. Компоненты решения получают в матрице
хij, а соответствующее значение целевой функции Z = ∑ ∑ci, j xi, j в
ячейке F2 (рис. 3.35).
Рис. 3.34
136
Рис. 3.35
При необходимости создаются отчеты по результатам, устойчивости, пределам.
Задание
Решить систему линейных уравнений из задания к §8 с помощью процедуры Поиск решения. В качестве целевой функции
возьмите выражение L = ∑N x j . N – индивидуальный номер.
j
§12. Графическое представление данных с помощью диаграмм
Диаграммы, как правило, используются для наглядного представления соотношений между какими–либо величинами или динамики их изменения. Excel предоставляет в распоряжение пользователя целый ряд средств работы с диаграммами. Во многих случаях целесообразно перед созданием диаграммы выделить ячейки, содержащие необходимые данные.
Покажем на графике затраты студентов (рис. 3.36).
Для вызова конструктора диаграмм надо выбрать Вставка —› Диаграммы. Затем необходимо указать данные для графика Кон-
137
структор—›Данные—›Выбрать данные (рис.3.37). Появится диалоговое окно Выбор источника данных. Нажав на кнопку в правой части поля Диапазон данных для диаграммы, выделим столбцы «ФИО» и «Затраты» в таблице (без заголовков). Несмежные столбцы выделяются при нажатой клавише «Ctrl».
Рис. 3.36
138
Рис. 3.37
Чтобы задать заголовок графику, надо нажать на кнопку Изменить в поле Элементы легенды и ввести название графика
(рис. 3.38).
Следует также отметить, что вертикальная ось — ось значений, а горизонтальная — ось категорий, т.е. на ней все названия, даже если они имеют форму цифровых значений, воспринимаются как текст и располагаются на равных расстояниях. Это следует иметь в виду при построении графиков, что возможно.
Если дважды щёлкнуть мышью по созданной диаграмме, вокруг неё появится контур, что свидетельствует о возможности редактирования диаграммы. В частности, потянув левой клавишей мыши какой-либо из квадратов на этом контуре, можно изменять размер области диаграммы. Пользователь имеет возможность дополнить диаграмму новыми данными, например, показать на графике и «Доход» (рис. 3.39).
139
Рис. 3.38
Щёлкнув мышью по какой-либо части диаграммы, в области построения, пользователь получает возможность редактировать отдельные её элементы.
140