Разработка управленческого решения

• прямая- такой способ дифференцирования установлен по умолчанию.
• центральная- этот способ требует больше вычислений на рабочем листе, но он может помочь в тех случаях, когда Вы получаете сообщение о том, что Поиск Решений не может улучшить решение.

Метод- параметры метод определяют, какой алгоритм поиска используется при каждой итерации для направления поиска. Нужно указать либо метод Ньютона, либо метод сопряженного градиента.

• метод Ньютона- это метод поиска по умолчанию, использующий квази-ньютоновский подход. Этот метод обычно требует больше памяти, чем метод сопряженного градиента, но меньшее количество итераций.
• метод сопряженного градиента- поиск методом сопряженного градиента требует меньше памяти, чем ньютоновский метод, но обычно большее число итераций для достижения конкретного уровня точности. Если проблема достаточно велика и важно экономное использование памяти, то стоит применить этот метод. Он также особенно полезен, если Вы видите, что последовательные итерации дают слишком малое отличие последовательных приближений.

Загрузить модель- выводит окно диалога "Загрузить Модель", в котором можно указать, какую именно модель нужно загрузить.

Сохранить модель- выводит окно диалога "Сохранить Модель", в котором можно указать, где именно нужно сохранить данную модель. Используйте кнопку Сохранить модель только в том случае, если нужно сохранить более, чем одну модель Поиска Решения вместе с данным рабочим листом. Первая модель Поиска Решений автоматически сохраняется вместе с рабочим листом.

Установите флажок Линейная модель, остальные параметры будем использовать по умолчанию.

6). Нажмите OK, затем кнопку Выполнить в окне "Поиск решения". Через некоторое время на экране появится окно "Результаты поиска решения" (рис. 15).

Рис. 15

Окно диалога "Результаты поиска решения" выводит результаты последнего вычисления, используя значения ячеек, наиболее близкие к нужному решению.

Когда Поиск Решения завершает попытки отыскания решения, то на экран в верху окна диалога "Результаты поиска решений" выводится сообщение о завершении.

Сохранить найденное решение- принимает решение, найденное Поиском Решения, и подставляет найденные значения в соответствующие ячейки.

Восстановить исходные значения- восстанавливает исходные значения в изменяемых ячейках.

Сохранить сценарий- открывает окно диалога Сохранить сценарий, в котором можно сохранить данную проблему для использования Диспетчером Сценариев пакета Microsoft Excel.

Отчеты- создает указанный тип отчета. Каждый отчет появляется на отдельном листе рабочей книги.

• Результаты- перечисляет изменяемые ячейки и ячейку в окне Установить целевую ячейку вместе с исходным и конечным значением. Также показывает ограничения и информацию о них.
• Устойчивость- предоставляет информацию о том, насколько чувствительно решение к малым изменениям в формуле окна Установить целевую ячейку или ограничениях. Для нелинейных моделей, отчет предоставляет двойственные значения (нормированные градиенты и множители Лагранжа). Для линейных моделей отчет включает редуцированную стоимость, теневые цены, objective coefficient (с допустимыми отклонениями в обе стороны), и ограничения на изменение правой стороны равенства.
• Пределы- перечисляет изменяемые ячейки вместе с соответствующими значениями, ячейку в окне Установить целевую ячейку, верхние и нижние пределы и целевые значения. Нижний предел есть наименьшее значение, которое может находиться в изменяемой ячейке, если фиксировать остальные ячейки и удовлетворить все ограничения. Верхний предел есть наибольшее значение. Целевое значение есть значение ячейки в окне Установить целевую ячейку, когда значение изменяемой ячейки достигает наименьшего или наибольшего предела.

Результаты поиска появятся в таблице (рис. 16).

Рис. 16

На рис. 16 видно, что в оптимальном решении Продукт1=B3=10; Продукт2=C3=0; Продукт3=D3=6; Продукт4=E3=0. При этом максимальная прибыль будет составлять F6=1320, а количество использованных ресурсов равно: трудовых=F9=16, сырья=F10=84, финансов=F11=100.

Таково оптимальное решение рассматриваемой задачи распределения ресурсов. Однако решение задачи находится не всегда. Если условия задачи несовместны, на экране появится диалоговое окно (рис. 17):

Рис. 17

Если целевая функция неограничена, то на экране появится диалоговое окно (рис. 18):

Рис. 18

4. Примеры решения оптимизационных  задач средствами Excel

4. 1. Получение требуемого сплава

Предприятию требуется изготовить некоторое количество сплава, содержащего не менее 15 компонент олова, 55 компонент цинка и 30 компонент свинца. Требуемый сплав изготавливается из трех исходных сплавов, в которых содержатся вышеуказанные составляющие. Данные о содержании олова, цинка и свинца в исходных материалах приведены в таблице, там же задана стоимость единицы каждого сплава.

Следует определить, какие из исходных сплавов и в каких количествах нужно использовать для получения требуемого сплава, чтобы суммарные затраты на исходные сплавы были минимальными.

Составляющие	Кол-во компонент составляющих в исходных материалах			Необходимое кол-во компонент в сплаве
	Сплав1	Сплав2	Сплав3
Свинец	40	30	25	30
Цинк	40	60	45	55
Олово	20	10	30	15
Цена единицы материала (руб.)	50	40	70

Математическая модель задачи выглядит следующим образом.

Целевая функция имеет вид:

5× x1+4× x2+7× x3® min,

Ограничения имеют вид:

40× x1+30× x2+25× x3=30

40× x1+60× x2+45× x3=55

20× x1+10× x2+30× x3=15

xj³ 0, j= .

Вид электронной таблицы Excel, созданной для решения задачи, представлен на рис. 19.

Поясним содержание некоторых ячеек таблицы.

В блоке ячеек В3:D3 находятся искомые значения xj, которые до выполнения поиска решения были равны 0. Адрес данного блока входит в поле ввода Изменяя ячейки в окне “Поиск решения” (см. рис. 21). Ячейки блока выполняют роль переменных целевой функции и ограничений- xj.

Рис. 19

Блок ячеек B4:D4 содержит правые части граничных неравенств (граничных условий). В ячейках блока содержатся нулевые значения (см. рис. 19).

Блок ячеек B6:D6 содержит данные о цене единицы исходных материалов, каждая его ячейка играет роль коэффициента при целевой функции в математической модели.

В блоках ячеек B10:D12 и G10:G12 находятся данные, соответствующие коэффициентам aij и bi ограничений математической модели.

Рис. 20

Сами формулы целевой функции и ограничений расположены соответственно в ячейке E6 и ячейках E10, E11, E12 (см. рис. 19 и 20). Вид электронной таблицы в режиме отображения формул представлен на рис. 20.

После ввода условий задачи необходимо перейти к поиску решения, инициировав соответствующее диалоговое окно (см. рис. 21). Данные в окно удобно вводить с помощью мыши. Для этого необходимо установить курсор в соответствующее поле ввода, затем выделить на рабочем листе требуемые ячейки, адреса выбираемых ячеек автоматически будут появляться в поле ввода.

Обычно в поле ввода Изменяя ячейки (см. рис. 21) заносятся адреса ячеек, которые выполняют роль переменных математической модели. Таким образом, под x1, x2, x3 отводятся ячейки B3, C3, D3.

Ограничения удобно задавать поблочно. Первое ограничение данного примера (см. рис. 21) представляет собой запись граничных условий, в которой каждая ячейка левого блока больше либо равна каждой ячейке правого блока. Левый блок означает, как известно, совокупность переменных xj, правый- множество нижних границ переменных. В данном примере нижней границей всех xj является 0, поэтому можно было бы записать: $B$3:$D$3>=0. Вторая запись в группе Ограничения представляет три ограничения по содержанию требуемых компонентов в сплаве. В каждой ячейке левого блока содержится формула одного из ограничений (см. рис. 20), ячейки правого блока содержат требования bi. По-прежнему, знак “>=“ относится каждой ячейке обоих блоков.

Рис. 21

Способ задания ограничений поблочно существенно облегчает ввод однотипных ограничений. Так, для данного примера двумя записями были заданы шесть ограничений.

Результаты поиска решения заносятся в ячейки таблицы (см. рис. 19). Прежде всего это значения xj. Для получения требуемого сплава нужно 0,44 единицы сплава1 и 0,63 единиц сплава2. Стоимость нового сплава равна 46,88 д. е., количество компонент свинца, цинка и олова равно соответственно 36,25; 55; 15 компонент.

4. 2. Транспортная задача

Три поставщика одного и того же продукта располагают в планируемый период следующими запасами этого продукта: первый- 120 условных единиц, второй- 100 и третий 80 единиц. Этот продукт должен быть перевезен к трем потребителям, спросы которых соответственно равны 90, 90 и 120 условных единиц. Приведенная ниже таблица содержит показатели затрат, связанных с перевозкой продукта из i-го пункта отправления в j-й пункт потребления.

Требуется перевезти продукт с минимальными затратами.

Поставщики	Потребители и их спрос			Запасы
	А	Б	В
I	7	6	4	120
II	3	8	5	100
III	2	3	7	80
Спрос	90	90	120

Математическая модель задачи выглядит следующим образом.

Целевая функция имеет вид:

7× x11+6× x12+4× x13+3× x21+8× x22+5× x23+2× x31+3× x32+7× x33® min,

Ограничения имеют вид:

x11+x12+x13=120,

x21+x22+x23=100,

x31+x32+x33=80,

x11+x21+x31=90,

x12+x22+x32=90,

x13+x23+x33=120,

xij³ 0, i, j= .

Вид электронной таблицы Excel, созданной для решения задачи, представлен на рис. 22

Искомые значения xij находятся в блоке ячеек B4:D6. Адрес данного блока входит в поле ввода Изменяя ячейки в окне “Поиск решения” (см. рис. 24). Требования к ограничениям по спросу и запасам представлены соответственно в ячейках B7:D7 и E4:E6. Коэффициенты ЦФ, означающие затраты на доставку расположены в блоке ячеек B12:D14.

Рис. 22

Формулы целевой функции и ограничений находятся соответственно в ячейке F8 и ячейках B8:D8 (ограничения по спросу), F4:F6 (ограничения по запасам) (см. рис. 22 и 23). Вид электронной таблицы в режиме отображения формул представлен на рис. 23.

Рис. 23

Первая запись в группе Ограничения (см. рис. 24) представляет ограничения по нижней границе xij. Вторая и третья записи выражают ограничения по уровню спроса и запасов соответственно.

Рис. 24

Результаты поиска решения представлены на рис. 22.

4. 3. Рациональное использование  имеющихся площадей

Фермерское хозяйство выращивает три вида сельскохозяйственных культур: пшеница, кукуруза, соя. Для выращивания оно имеет три вида земельных угодий, отличающихся по урожайности каждой из культур (см. табл.). Общая площадь земли каждого вида составляет 50, 75, 50 га. Минимальные требования к урожаю пшеницы, кукурузы и сои составляют 1000, 700, 900 центнеров соответственно. Известны цены одного центнера каждой из культур (см. табл.). Какую площадь необходимо отвести на каждом поле под каждую культуру, чтобы прибыль от выращенной продукции была максимальной.

Поле	Урожайность (центнеров с гектара)			Площадь поля (га)
	Пшеница	Кукуруза	Соя
1	60	10	40	50
2	15	30	15	75
3	20	20	15	50
Мин. урожай (цн.)	1000	700	900
Ст-ть цен-ра (руб.)	1000	900	800