Задание исходных данных
На листе окна Excel постройте исходную таблицу, как показано на рис. 3.1. Ячейки с адресами В2:Е5 резервируются для переменных задачи – это изменяемые ячейки. В ячейках А8:A11 заносятся запасы грузов в пунктах отправления, включая и фиктивный, в ячейках B12:E12 заносятся потребности (спрос) в грузах в пунктах назначения. После занесения в ячейку числа или формулы необходимо нажать клавишу ENTER.
Рис. 3.1. Фрагмент листа рабочей книги с исходными данными
В ячейки с адресами В8:Е11 занесены коэффициенты матрицы затрат на перевозку грузов – транспортные тарифы. Четвертая строка этой матрицы соответствует перевозкам от фиктивного поставщика.
Ячейки А2:А5 содержат формулы для расчета ограничений (1) – (4). Формула ограничения (1) занесена в ячейку А2 и имеет вид =СУММ(B2:E2). Соответственно ограничения (2), (3) и (4) занесены в ячейки А3, А4 и А5 в виде формул =СУММ(B3:E3), =СУММ(B4:E4) и =СУММ(B5:E5).
В ячейках В6:Е6 содержатся формулы ограничений (5)–(8), которые соответственно имеют вид: =СУММ(B2:B5), =СУММ(C2:C5), =СУММ(D2:D5), =СУММ(E2:E5).
Формула для расчета целевой функции (9) занесена в ячейку В14 и имеет вид: =СУММПРОИЗВ(B2:E5;B8:E11). В эту же ячейку будет занесено вычисленное оптимальное значение целевой функции.
Решение задачи
Поставить курсор мыши в ячейку В14, в которой будет содержаться вычисленное значение целевой функции, и нажать на левую кнопку мыши.
Войти в меню Сервис, выбрать в нем Поиск решения и щелкнуть на нем левой кнопкой мыши. На экране появится диалоговое окно Поиск решения (рис. 3.2).
Рис. 3.2. Диалоговое окно Поиск решения с внесенными ограничениями
В поле Установить целевую ячейку заносится $В$14. Для этого проще всего установить курсор мыши внутрь ячейки, щелкнуть в ней левой кнопкой мыши, затем щелкнуть мышью на ячейке В14.
Поскольку ищется минимум целевой функции, то после слова Равной выделим Минимальному значению, щелкнув в соответствующем кружочке мышью.
В поле Изменяя ячейки занесем диапазон $B$2:$Е$5, так как именно эти ячейки отведены под значения вычисляемых переменных. Для этого поставим курсор в поле Изменяя ячейки, затем поставим курсор на ячейке В2 и при нажатой левой кнопке мыши переведем курсор на ячейку Е5. В поле Изменяя ячейки появится необходимый диапазон ячеек.
В поле Ограничения занесем ограничения (1)–(8). Для этого щелкнем мышью на кнопке Добавить. Появится диалоговое окно Добавление ограничения (рис. 3.3).
Рис. 3.3. Диалоговое окно Добавление ограничения
Например, второе ограничение занесено следующим образом: в окне Поиск решения щелкнуть на кнопке Добавить. Откроется диалоговое окно Добавление ограничения. В поле Ссылка на ячейку поставить курсор мыши нажать на левую ее кнопку. Затем поставить курсор мыши на ячейку В6, где введено ограничение и при нажатой кнопке мыши провести вплоть до ячейки с адресом Е6. В среднем поле окна Добавить ограничения выбираем знак равенства, а в последнем поле описанным выше способом заносим ячейки В12:Е12, спрос потребителей. Аналогично заносятся другие ограничения.
В поле Поиск решения (рис.3.2) щелкнуть мышью на кнопке Параметры. На экране появится диалоговое окно Параметры поиска решения (рис. 3.4).
Рис. 3.4. Диалоговое окно Параметры поиска решения
В этом окне устанавливаются параметры поиска решения. Здесь отметить квадратики Линейная модель, Неотрицательные значения, Автоматическое масштабирование. Щелкнуть на кнопке ОК.
Снова попадаем в диалоговое окно Поиск решения. В этом окне (рис.3.2) щелкнем левой кнопкой мыши на кнопку Выполнить. На экран выводится окно Результаты поиска решения. Чтобы сохранить полученное решение, надо левой кнопкой мыши нажать сначала на кружок Сохранить найденное решение,а затем на кнопку ОК.
Одновременно на Листе экрана также появляются результаты решения задачи (рис.3.5): в столбце А2:A5 и строке B6:E6 выводятся рассчитанные значенияограничений.
Рис. 3.5. Окно с результатами решения задачи
В ячейках В2:Е5 содержатся рассчитанные значения переменных – объемов перевозок от каждого пункта отправления к каждому пункту назначения. В ячейке с целевой функцией В14 – рассчитанное значение целевой функции.
Таким образом, найденное оптимальное решение имеет вид:Fmin = 5200, x11 = 0, x12 = 0, x13= 300, x14 = 300, x21 = 200, x22= 600, x23 = 0, x24 =0, x31 = 700, x32= 0, x33 = 0, x34= 300, и фиктивные переменные x41 = x42 = x44= 0, x43= 500.
Замечание. Прежде чем приступать к решению транспортной задачи данного варианта, необходимо проверить баланс между общим запасом груза у поставщиков и общей потребностью у потребителей. Если соответствующие суммы не равны между собой, необходимо ввести либо фиктивного поставщика, либо фиктивного потребителя.
Индивидуальные задания:
Вариант № 1
Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе:
| Склад | Магазины - заказчики | Запасы на складе (ед. прод.) | ||||
| “Анна” | “Вада” | “Ева” | “Алла” | “Мех” | ||
| “Таганка” | ||||||
| “ВВЦ” | ||||||
| “Щёлково” | ||||||
| “Коньково” | ||||||
| Объём заказа |
Вариант № 2
Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе:
 Магазин
Склад
| Магазины - заказчики | Запасы на складе (ед. продук- ции) | ||||
| Росстек | Шер | Ткани | Мода | Вита | ||
| Иваново | ||||||
| Москва | ||||||
| Новгород | ||||||
| Серпухов | ||||||
| Объём заказа (ед.прод) |
Вариант № 3
Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе:
| Магазин
Склад
| Магазины - заказчики | Запасы на складе (ед. прод.) | ||||||||
| “Всё для дома” | “Здоро- вый сон” | “Фея” | “Ночное царство” | “Мех” | ||||||
| “Вороново” | ||||||||||
| “Фили” | ||||||||||
| “Беляево” | ||||||||||
| “Выхино” | ||||||||||
| Объём заказа (ед.прод) | ||||||||||
Вариант № 4
Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе:
| Склад | Магазины - заказчики | Запасы на складе (ед.прод.) | ||||
| ВДНХ | Юго- Западная | Фили | Арбат- ская | Соколь- ники | ||
| Пролетарская | ||||||
| Митино | ||||||
| Строгино | ||||||
| Объём заказа (ед.прод) |
Вариант № 5
Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе:
| Склад | Магазины - заказчики | Запасы на складе (ед.прод.) | |||
| Тверь | Рязань | Тула | Чехов | ||
| Москва | |||||
| Санкт-Петербург | |||||
| Саратов | |||||
| Самара | |||||
| Объём заказа |
Вариант № 6
Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе:
| Склад | Магазины - заказчики | Запасы на складе (ед.прод.) | ||||
| Сокол | Рижская | ВДНХ | Киевская | Царицыно | ||
| Пражская | ||||||
| Волжская | ||||||
| Курская | ||||||
| Савёловская | ||||||
| Объём заказа |
Вариант № 7
Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе:
| Склад | Магазины - заказчики | Запасы на складе (ед.прод.) | ||||
| Новгород | Москва | Самара | Саратов | Тверь | ||
| Нижний Новгород | 0,5 | |||||
| Саратов | 0,5 | |||||
| Самара | 0,5 | |||||
| Санкт-Петербург | 4,5 | |||||
| Объём заказа |
Вариант № 8
Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе:
| Склад | Магазины - заказчики | Запасы на складе (ед.прод.) | |||
| “Колбасы” | “Мясо” | “Мясные деликатесы” | “Дина” | ||
| Черкизово | 0,5 | ||||
| Царицыно | |||||
| Бородино | 2,5 | ||||
| Вешняки | 1,5 | ||||
| Объём заказа (ед.прод) |
Вариант № 9
Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе:
| Склад | Магазины - заказчики | Запасы на складе (ед. прод.) | ||||
| “Булочная” | “Хлеб” | “Сла- дости” | “Сдоба” | “Сладко- ежка” | ||
| “Крекер” | 2,5 | 1,5 | ||||
| “Славянка” | 3,5 | 1,6 | ||||
| “Сластёна” | 2,5 | |||||
| Объём заказа (ед.прод) |
Вариант № 10
Необходимо решить транспортную задачу: минимизировать расходы на доставку продукции заказчикам со складов фирмы, учитывая следующие затраты на доставку одной единицы продукции, объём заказа и количество продукции, хранящейся на каждом складе:
| Склад | Магазины - заказчики | Запасы на складе (ед. прод.) | ||||
| “Диана” | ГУМ | ЦУМ | “Прага” | “Елена” | ||
| “Перово” | 1,5 | |||||
| “Волжская” | ||||||
| “Пражская” | 2,5 | 3,5 | ||||
| “Беговая” | 3,5 | 1,5 | ||||
| Объём заказа (ед.прод) |