Поиск решений Задача 1

Найти оптимальные объемы выпуска трех видов продукции для получения максимальной прибыли от их продажи.

	A	B	C	D
1
2		Количество	Прибыль на 1 шт.	Доход
3	Изделие А	100	13	1300
4	Изделие В	100	18	1800
5	Изделие С	100	22	2200
6	Всего	300		5300

При решении данной задачи должны быть учтены следующие ограничения:

• общий объем производства – всего 300 изделий;

• должно быть произведено не менее 50 изделий А;

• должно быть произведено не менее 40 изделий В;

• должно быть произведено не более 40 изделий С.

Технология:

1. Вести в новый рабочий лист данные для вычисления прибыли от продажи трех видов продукции, причем в ячейки столбца D, и в ячейку B6 должны быть введены формулы.

2. Запустить задачу поиска решений. Для этого: выполнить команду Сервис/ Поиск решений … и в диалоге «Поиск решений» ввести данные:

• в поле «Установить целевую ячейку» указать адрес D6;

• установить флажок «Равной максимальному значению»;

• в поле «Изменяя ячейки» определить изменяемые ячейки (B3:B5);

• в поле «Ограничения» по одному добавить каждое из следующих четырех ограничений задачи (B6=300; B3>=50; B4>=40; B5<=40). Для этого щелкнуть по кнопке «Добавить» и в появившемся окне «Добавление ограничения» ввести ссылку на ячейку (B6), оператор ограничения (=) и значение (300), для добавления следующего ограничения щелкнуть кнопку «Добавить» и повторить процедуру добавления ограничения; после ввода последнего ограничения щелкнуть кнопку «ОК»;

• в диалоговом окне «Поиск решения» щелкнуть кнопку «Выполнить»;

• в диалоге «Результаты поиска решения» установить переключатель «Сохранить найденное решение», в окне «Тип отчета» выбрать «Результаты» и нажать кнопку «Ok»;

• ознакомиться с отчетом по результатам, помещенным на новом листе.

Задача 2

С помощью средства «Поиск решения» решить задачу минимизации расходов на перевозку грузов.

Постановка задачи:

Компания имеет 3 склада, которые расположены в разных районах города. Заказы на перевозку грузов поступают из сети розничных магазинов, распределенных по всей территории города. Цель задачи – удовлетворить потребность всех шести розничных магазинов в товарах, находящихся на трех складах и сохранить при этом общие расходы на перевозку на минимальном уровне.

Исходные данные:

	A	B	C	D	E	F
1	Таблица стоимости перевозок
2				Склад 1	Склад 2	Склад 3
3			Магазин 1	58	47	108
4			Магазин 2	87	46	100
5			Магазин 3	121	30	57
6			Магазин 4	149	66	83
7			Магазин 5	62	115	164
8			Магазин 6	128	28	38

	A	B	C	D	E	F	G
9
10			Потребность в товаре	Количество перевезенного товара со склада
11				Склад 1	Склад 2	Склад 3	Всего
12		Магазин 1	150	25	25	25	75
13		Магазин 2	225	25	25	25	75
14		Магазин 3	100	25	25	25	75
15		Магазин 4	250	25	25	25	75
16		Магазин 5	120	25	25	25	75
17		Магазин 6	150	25	25	25	75
18		Всего	995	150	150	150	450

	A	B	C	D	E	F
19
20		Запасы на складе		400	350	500
21		Запасы после отпуска		=D20-D18	...	...

	A	B	C	D	E	F	G
24		Стоимость перевозок		=СУММПРОИЗВ (D3:D8;D12:D17)	…	…	…

В ячейке G24 подводится общая стоимость перевозок для всех заказов.

В результате поиска оптимального решения должны быть найдены такие значения ячеек D12:F17, при которых каждый розничный магазин будет получать желаемое количество товара и общая стоимость перевозок будет минимальна. Нужно минимизировать значение, находящееся в ячейке G24, изменяя значение диапазона ячеек D12:D17 с учетом следующих ограничений:

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

• значения в изменяемых ячейках не могут быть отрицательными;

• количество запасов на каждом складе не должно быть отрицательным.