Решение задач оптимизации с использованием ms Excel

Контрольный пример линейной оптимизационной задачи

Для производства столов и шкафов мебельная фабрика использует необходимые ресурсы. Нормы затрат ресурсов на одно изделие данного вида, прибыль от реализации одного изделия и общее количество имеющихся ресурсов каждого вида приведены в табл. 1.

Таблица 1

Ресурсы	Нормы затрат ресурсов на одно изделие		Общее количество ресурсов
	Стол	Шкаф
Древесина 1 вида	0,2	0,1	40
Древесина 2 вида	0,1	0,3	60
Трудоемкость (человеко-часов)	1,2	1,5	371,4
Прибыль от реализации одного изделия (руб.)	6	8

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

Для решения этой задачи необходимо построить математическую модель. Процесс построения модели можно начать с ответа на следующие три вопроса:

1.    Для определения каких величин строится модель?

2.    В чем состоит цель, для достижения которой из множества всех допустимых значений переменных выбираются оптимальные?

3.    Каким ограничениям должны удовлетворять неизвестные?

В данном случае мебельной фабрике необходимо спланировать объем производства столов и шкафов так, чтобы максимизировать прибыль. Поэтому переменными являются: х1 – количество столов, х2 – количество шкафов

Суммарная прибыль от производства столов и шкафов равна z=6×x1+8×x2. Целью фабрики является определение среди всех допустимых значений х1 и х2 таких, которые максимизируют суммарную прибыль, т.е. целевую функцию z.

Ограничения, которые налагаются на х1 и х2:

• объем производства шкафов и столов не могут быть отрицательным, следовательно х1, х2 ³0.

• нормы затрат древесины на столы и шкафы не могут превосходить максимально возможный запас данного исходного продукта, следовательно

0,2x1+0,1x2£40,

0,1x1+0,3x2£60.

Кроме того, ограничение на трудоемкость не превышает количества затрачиваемых ресурсов:

1,2x1+1,5х2£371,4.

Таким образом, математическая модель данной задачи имеет следующий вид:

максимизировать функцию

z=6х1+8х2

при следующих ограничениях:

0,2x1+0,1x2£40

0,1x1+0,3x2£60

1,2x1+1,5х2£371,4.

Данная модель является линейной, т.к. целевая функция и ограничения линейно зависят от переменных.

Решение задачи с помощью MS Excel

1. Отвести ячейки A3 и ВЗ под значения переменных х1 и х2 (рис. 1).

Рис. 1. Диапазоны, отведенные под переменные, целевую функцию и ограничения

2. В ячейку С4 ввести функцию цели: =6×АЗ+8×ВЗ, в ячейки А7:А9 ввести левые части ограничений:

=0,2×А3+0,1×ВЗ

=0,1×А3+0,3×ВЗ

=1,2×АЗ+1,5×ВЗ,

а в ячейки В7:В9 – правые части ограничений (рис. 2.1).

3. Выбрать команду Сервис/Поиск решения (Tools/Solver) и заполнить открывшееся диалоговое окно Поиск решения (Solver) так, как показано на рис. 2. Средство поиска решений является одной из надстроек Excel. Если в меню Сервис (Тоо1s) отсутствует команда Поиск решения (Solver), то для ее установки необходимо выполнить команду Сервис/ Надстройки/ Поиск решения (Tools/Add-ins/Solver). Для ввода ограничений нажмите кнопку Добавить.

Рис. 2. Диалоговое окно Поиск решения задачи о максимизации прибыли на фабрике

Внимание! В диалоговом окне Параметры поиска решения (Solver Options) необходимо установить флажок Линейная модель (Assume Linear Model) (рис. 3).

Рис. 3. Диалоговое окно Параметры поиска решения

4. После нажатия кнопки Выполнить (Solve) открывается окно Результаты поиска решения (Solver Results), которое сообщает, что решение найдено (рис. 4).

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

5. Результаты расчета задачи представлены на рис. 5, из которого видно, что оптимальным является производство 102 столов и 166 шка­фов. Этот объем производства принесет фабрике 1940 руб. прибыли.

Рис. 5. Результаты расчета с помощью средства поиска решений для задачи максимизации выпуска столов и шкафов

Индивидуальное задание

1.    Построить математическую модель задачи согласно вашему варианту.

2.    Решить задачу с помощью средства MS Excel Поиск решения.

3.    Сделать соответствующие выводы.

Задача 1.

Для производства двух видов изделий А и В используется токарное, фрезерное и шлифовальное оборудование. Нормы затрат времени для каждого из типов оборудования на одно изделие данного вида приведены в табл. 2. В ней же указан общий фонд рабочего времени каждого из типов оборудования, а также прибыль от реализации одного изделия.

Таблица 2

Тип оборудования	Затраты времени (станко-часов) на обработку одного изделия		Общий фонд полезного рабочего времени
	А	В
Фрезерное Токарное Шлифовальное	10 5 6	8 10 12	168 180 144
Прибыль от реализации одного изделия (руб.)	14	18

Определить план выпуска изделий вида А и В, обеспечивающий максимальную прибыль от их реализации.

Задача 2.

На звероферме могут выращиваться черно-бурые лисицы и песцы. Для обеспечения нормальных условий их выращивания используется три вида кормов. Количество корма каждого вида, которое должны ежедневно получать лисицы и песцы, приведено в табл. 3. В ней же указаны общее количество корма каждого вида, которое может быть использовано зверофермой, и прибыль от реализации одной шкурки лисицы и песца.

Найти оптимальное соотношение количества кормов и численности поголовья лис и песцов.

Таблица 3

Вид корма	Количество единиц корма, которое ежедневно должны получать		Общее количество корма
	А	В
Вид 1 Вид 2 Вид 3	2 4 6	3 1 7	180 240 426
Прибыль от реализации одной шкурки (руб.)	16	12

Контрольный пример транспортной задачи

Фирма имеет 4 фабрики и 5 центров распределения ее товаров. Фабрики фирмы располагаются в Денвере, Бостоне, Новом Орлеане и Далласе с производственными возможностями 200, 150, 225 и 175 еди­ниц продукции ежедневно соответственно. Центры распределения товаров фирмы располагаются в Лос-Анджелесе, Далласе, Сент-Луисе, Вашингтоне и Атланте с потребностями в 100, 200, 50, 250 и 150 единиц продукции ежедневно соответственно. Хранение на фабрике единицы продукции, не поставленной в центр распределения, обходится в $0,75 в день, а штраф за просроченную поставку единицы продукции, заказанной потребителем в центре распределения, но там не находящейся, равен $2,5 в день. Стоимость перевозки единицы продукции с фабрик в пункты распределения приведена в табл. 4.

Таблица 4

		1	2	3	4	5
		Лос-Анджелес	Даллас	Сен-Луис	Вашингтон	Атланта
1	Денвер	1,50	2,00	1,75	2,25	2,25
2	Бостон	2,50	2,00	1,75	1,00	1,50
3	Новый Орлеан	2,00	1,50	1,50	1,75	1,75
4	Даллас	2,00	0,50	1,75	1,75	1,75

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

Поскольку данная модель сбалансирована (суммарный объем произведенной продукции равен суммарному объему потребностей в ней), то в этой модели не надо учитывать издержки, связанные как со складированием, так и с недопоставками продукции. В противном случае в модель нужно было бы ввести:

• в случае перепроизводства – фиктивный пункт распределения, стоимость перевозок единицы продукции в который полагается равной стоимости складирования, а объемы перевозок объемам складирования излишков продукции на фабриках;

• в случае дефицита – фиктивную фабрику, стоимость перевозок единицы продукции с которой полагается равной стоимости штрафов за недопоставку продукции, а объемы перевозок – объемам недопоставок продукции в пункты распределения.

Для решения данной задачи построим ее математическую модель:

Неизвестными в данной задаче являются объемы перевозок. Пусть x_ij – объем перевозок с i-й фабрики в j-й центр распределения. Функция цели – это суммарные транспортные расходы, т. е. где с_ij – стоимость перевозки единицы продукции с i-й фабрики j-й центр распределения.

Неизвестные в данной задаче должны удовлетворять следующим ограничениям:

• Объемы перевозок не могут быть отрицательными.

• Так как модель сбалансирована, то вся продукция должна быть вывезена с фабрик, а потребности всех центров распределения должны быть полностью удовлетворены.

В результате имеем следующую модель:

 – минимизировать при ограничениях:

, j Î [1,5]

x_ij ³ 0, iÎ [1,4], jÎ [1,5]

, iÎ [1,4],

где a_ij – объем производства на i-й фабрике, bj – спрос в j-м центре распределения.

Решение задачи с помощью MS Excel

1.    Ввести данные, как показано на рис. 6.

Рис. 6. Исходные данные транспортной задачи

В ячейки А1:Е4 введены стоимости перевозок. Ячейки А6:Е9 отведены под значения неизвестных (объемы перевозок). В ячейки G6:G9 введены объемы производства на фабриках, а в ячейки А11:Е11 введена потребность в продукции в пунктах распределения. В ячейку F10 введена целевая функция =СУММПРОИЗВ(А1:Е4; А6:Е9).

В ячейки А10:Е10 введены формулы

=СУММ(А6:А9)

=СУММ(В6:В9)

=СУММ(С6:С9)

=СУММ(D6:D9)

=СУММ(Е6:Е9), определяющие объем продукции, ввозимой в центры распределения.

В ячейки F6:F9 введены формулы

=СУММ(А6:Е6)

=СУММ(А7:Е7)

=СУММ(А8:Е8)

=СУММ(А9:Е9), вычисляющие объем продукции, вывозимой с фабрик.

2.    Выбрать команду Сервис/Поиск решения (Tools/Solver) и заполнить открывшееся диалоговое окно Поиск решения (Solver), как показано на рис. 7.

Внимание! В диалоговом окне Параметры поиска решения (Solver Options) необходимо установить флажок Линейная модель (Assume Linear Model).

Рис. 7. Диалоговое окно Поиск решения для транспортной задачи

3. После нажатия кнопки Выполнить (Solve) средство поиска решений находит оптимальный план поставок продукции и соответствующие ему транспортные расходы (рис. 8).

Рис. 8. Оптимальное решение транспортной задачи

Индивидуальное задание

1.    Построить математическую модель задачи согласно вашему варианту.

2.    Решить задачу с помощью средства MS Excel Поиск решения.

3.    Сделать соответствующие выводы.

Задача 3.

Решить транспортную задачу со следующими условиями (табл. 5).

Таблица 5

Пункты отправления	Пункты назначения				Запасы
	В1	В2	В3	В4
А1	3	4	6	1	460
А2	5	1	2	3	340
A3	4	5	8	1	300
Потребности	350	200	450	100

Задача 4.

Для строительства трех объектов используется кирпич, изготовляемый на трех заводах. Ежедневно каждый из заводов может изготовлять 100, 150 и 50 уcл. ед. кирпича. Ежедневные потребности в кирпиче на каждом из строящихся объектах соответственно равны 75, 80, 60 и 85 усл. ед. Известны также тарифы перевозок 1 усл. ед. кирпича с каждого с заводов к каждому из строящихся объектов:

_.

Составить такой план перевозок кирпича к строящимся объектам, при котором общая стоимость перевозок является минимальной.

Задача 5.

На трех железнодорожных станциях скопилось 120, 110 и 130 неза­груженных вагонов. Эти вагоны необходимо перегнать на железнодорожные станции В1, В2, ВЗ, В4 и В5. На каждой из этих станций потребность в вагонах соответственно равна 80, 60, 70, 100 и 50. Тарифы перевозок задаются матрицей

_.

Составить такой план перегонок вагонов, при котором общая стоимость была бы минимальной.