Расстояния между складами и пунктами доставки

	Пункт 1	Пункт 2	Пункт 3	Пункт 4
Склад №1	6	4	5	11
Склад №2	12	6	4	9
Склад №3	15	7		4
Склад №4	9	5	12	5
Склад №5	3	7	12	11

На пересечении столбца конкретного пункта доставки со строкой склада находится информация о расстояниях между этими пунктом доставки и складом. Например, расстояние между 3 пунктом доставки и складом №3 равно 10 километрам.

Для решения задачи подготовим исходную таблицу по схеме рис. 1 - 2:

Рис.1 Изменяемые ячейки переменных задачи Xij (в рамке) которые необходимо определить. Начальные значения равны нулю.

Значения ячеек по столбцу В с четвертой по восьмую строку определяются суммированием данных ячеек соответствующих строк начиная со столбца С до столбца F.

Например, значение ячейки B4=СУММ(C4:F4).

Значения ячеек по 9 строке по столбцам от С до F определяются суммированием данных ячеек соответствующих столбцов с 4 по 8 строки. Например, значение ячейки С9=СУММ(C4:C8).

Каждое значение в ячейках на пересечении столбца конкретного пункта доставки и строки склада Xij - означает количество ящ., поставляемых с этого склада в данный пункт потребления. В нижней строке (строка 9) суммируется общее количество товаров, поставляемых в определенный пункт доставки, а во втором столбце (столбец В) суммируется количество доставленного с конкретного склада.

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

Рис.2 Исходная информация.

В строке 16 по столбцам C- F определим грузооборот по каждому пункту доставки. К примеру для 1 пункта (ячейка С16) это рассчитывается с помощью формулы:

В С16 формула = С4*С11+С5*С12+С6*С13+С7*С14+С8*С15,

либо можно использовать функцию:

СУММПРОИЗВ: = СУММПРОИЗВ(C4:C8;C11:C15)

В ячейке С4 находится количество товара, перевозимых со склада №1 в 1 пункт доставки, а в ячейке С11 - расстояние от склада №1 до 1 пункта доставки. Соответственно первое слагаемое в формуле означает полный грузооборот по данному маршруту. Вся же формула вычисляет полный грузооборот перевозок товара в 1 пункт доставки.

В ячейке В16 по формуле = СУММ(С16:F16) будет вычисляться общий объем грузооборота товара.

Таким образом, исходная информация на рабочем листе примет следующий вид (рис. 3)

Рис. 3. Рабочий лист, подготовленный для решения транспортной задачи

РЕШЕНИЕ:

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

Рис. 4. Диалоговое окно Поиск решения

1. Поскольку в качестве критерия оптимизации нами выбрана минимизация грузооборота, в поле Установить целевую ячейку введите ссылку на ячейку, содержащую формулу расчета общего объема грузооборота товаров. В нашем случае это ячейка $B$16. Чтобы минимизировать значение конечной ячейки --, переключатель установите в положение минимальному значению;

2. В поле Изменяя ячейки введите ссылки на изменяемые ячейки, указывая первую и последнюю ячейку, разделяя их двоеточием ($С$4:$F$8). Это означает, что для достижения минимального грузооборота перевозок будут меняться значения в ячейках с С4 по F8, то есть будут изменяться количество груза, перевезенного по конкретному маршруту.

3. Далее надо задать некоторые ограничения для поиска решения.

В группе полей Ограничения нажмите кнопку Добавить. Появится диалог Добавление ограничения (рис. 5)

Рис. 5. Диалоговое окно Добавление ограничения

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

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

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

Первое условие $B$4:$B$8 <=$B$11:$B$12. Оно означает, что значение в ячейке В4 должно быть меньше или равно значению в В11, в В5 меньше или равно, чем в В12, и так далее до В8 и В15.

В ячейках с В4 по В8 на листе находятся объемы поставок с конкретных складов. В ячейках с В11 по В15 - запасы на этих же складах. Так как невозможно вывести со склада больше, чем на нем есть, первое значение должно быть не больше второго.

Второе условие $С$4:$F$8>=0. Оно означает, что объем перевозок не может быть отрицательным, то есть, если на складе не хватает товаров, их не везут с пункта доставки, на который эти товары были завезены ранее. Грузопоток имеет только одно направление - от складов к пунктам доставки товаров.

И наконец, третье, и последнее условие $С$9:$F$9>=$C$10:$F$10. Оно означает, что значения в ячейках девятой строки должны быть больше или равны значениям в ячейках десятой строки, то есть запросы пунктов доставки продуктов должны быть выполнены полностью. Перевыполнение объема поставок допустимо, а недовыполнение - нет.

5. Введенные условия должны позволить найти наиболее оптимальный вариант решения задачи.. Нажмите кнопку Выполнить для подбора решения.

После нахождения решения появляется диалог: Результаты поиска решения (рис. 7)

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

6. Нажав кнопку ОК, вы занесете вариант решения Задачи на рабочий лист (рис. 8).

Рис. 8. Решенная транспортная задача

Ответ: Минимальный грузооборот перевозок при соблюдении всех условий равен 3540 ящ.- км.

Пример 2.2 Задача определения оптимального плана

Ресторан выпускает 2 фирменных блюда (продукция). Цена единицы 1 вида продукции - 250, 2 вида продукции – 500. Для изготовления продукции используются три вида сырья, запасы которого 37, 57,6 и 7 условных единиц. Нормы затрат каждого сырья на единицу продукции представлены в следующей таблице (серый цвет).

Продукция	Запасы сырья
1-й вид продукции	2-й вид продукции
1,2	1,9
2,3	1,8	57,6
0,1	0,7

Требуется определить плановое количество выпускаемой продукции таким образом, чтобы доход от продажи(стоимость) произведенной продукции был максимальный

Выполнение.

Главное меню: Сервис / Надстройки / Установить флажок «Поиск решения» / OK.

После загрузки инструмента «Поиск решения» в меню Сервис появляется команда «Поиск решения». Выполнение этой команды начинается с вывода диалогового окна, в котором вводятся исходные данные задачи на основании модели задачи.