Решение транспортных задач в MS Excel

1.

2. Инструмент Поиск Решения

Для решения транспортной задачи в EXCEL
используется инструмент ПОИСК РЕШЕНИЯ. При
этом необходимо:
Ввести исходные данные в ячейки рабочего листа
EXCEL;
Разметить блоки ячеек на рабочем листе EXCEL,
необходимые для моделирования объемов
перевозок и формирования элементов
математической модели задачи;
Сформировать на рабочем листе EXCEL элементы
математической модели;
Настроить программу " Поиск решения" и
выполнить ее.

3. Пример

Для строительства четырех объектов используется кирпич,
изготавливаемый на трех заводах. Ежедневно каждый из заводов
может изготовить 100, 150 и 50 условных единиц кирпича.
Потребности в кирпиче на каждом из строящихся объектов
ежедневно составляют 75, 80, 60 и 85 условных единиц. Тарифы
перевозок одной условной единицы кирпича с каждого из заводов к
каждому из строящихся объектов задаются матрицей транспортных
расходов С.
6 7 3 5
C 1 2 5 6
8 10 20 1
Требуется составить такой план перевозок кирпича к строящимся
объектам, при котором общая стоимость перевозок будет
минимальной

4.

Исходными данными для решения транспортной
задачи являются:
◦ матрица транспортных расходов;
◦ предложение поставщиков;
◦ спрос потребителей.
Для наглядности блоки ячеек с введенными данными
желательно обвести рамками

5.

6.

Кроме исходных данных на рабочем листе EXCEL
размещают вспомогательные блоки ячеек:
"Матрица перевозок» для моделирования объемы перевозок;
"Фактически реализовано", для моделирования фактической
реализация продукции;
"Фактически получено", для моделирования фактического
удовлетворение спроса;
"Транспортные расходы по потребителям", для вычисления
транспортных расходов по каждому потребителю;
Ячейку "Итого расходы", в которой вычисляются итоговые
транспортные расходы по всем потребителям (целевая ячейка).
Для наглядности указанные блоки ячеек целесообразно
обвести рамками.

7. Пример

8.

Сформируем блок "Фактически реализовано»:
◦ В первую ячейку блока "Фактически реализовано" (ячейка I14)
внесем формулу, суммирующую ячейки той же строки в блоке
«Матрица перевозок» (ячейки С14:F14)
◦ Скопируйте формулу на все остальные ячейки блока.
Сформируем блок "Фактически получено":
◦ В первую ячейку блока "Фактически получено" (ячейка С18) внесем
формулу, суммирующую ячейки того же столбца в блоке
«Матрица перевозок» (ячейки С14:С16);
◦ Скопируйте формулу на все остальные ячейки блока.

9.

Формируем блок “Транспортные расходы по
потребителям”:
В первую ячейку блока (ячейка С21) введем формулу
=СУММ (С6:С8*С14:С16):
◦ Наведите курсор на кнопку автосуммирования и щелкните левой
клавишей мыши;
◦ Нажмите клавишу “Delete ”;
◦ Селектируйте первый столбец блока “Матрица Транспортных
расходов” (столбец С6:С8);
◦ Нажмите клавишу *;
◦ Селектируйте первый столбец блока “Матрица превозок” (столбец
С14:С16);
◦ Активируйте строку формул, наведя на неё курсор и щелкнув затем
левой клавишей мыши;
◦ Нажмите одновременно три клавиши: “CTRL”+“SHIFT”+“ENTER”;
Копируем формулу в остальные ячейки блока

10.

Сформируем целевую функцию транспортной задачи
в ячейке “Итого расходы” (ячейка I21) - внесем
формулу, суммирующую ячейки той же строки в
блоке «Транспортные расходы по потребителям»
(ячейки С21:F21)

11.

После формирования элементов математической модели и целевой
функции транспортной задачи рабочий лист EXСEL примет вид

12.

Селектируйте ячейку “Итого расходы” (ячейка I21);
Выберите пункт "Поиск решения" меню "Сервис" Убедитесь, что в
поле “Установить целевую ячейку” диалогового окна программы
“Поиск решения” указана ячейка $I$21.
Установите курсор на переключатель “Равной Минимальному
значению”;
Установите курсор в поле “Изменяя ячейки” и селектируйте блок
ячеек “Матрица перевозок” (блок С14:F16)

13.

Для задания ограничений щелкните кнопку “Добавить”
◦ В диалоговом окне команды “Добавление ограничения” селектируйте блок
“Фактически реализовано” (ячейки I14:I16);
◦ Убедитесь, что оператор сравнения <= уже выбран.
◦ В поле “Ограничение” селектируйте блок ячеек “Предложение поставщиков”
(блок I6:I8)
Убедитесь, что окно диалога команды “Добавление ограничения” имеет
вид, показанный на рисунке

14.

Продолжим ввод ограничений:
◦ Щелкните кнопку “Добавить” и селектируйте блок “Фактически получено”
(ячейки С18:F18);
◦ Установите курсор на значение >= (больше или равно);
◦ В поле “Ограничение” селектируйте блок “Спрос потребителей” (ячейки
С10:F10);
Убедитесь, что окно диалога команды “Добавление ограничения”
имеет вид, показанный на рисунке

15.

Введем тривиальные ограничения:
◦ Щелкните кнопку “Добавить” и селектируйте блок ячеек “Матрица
перевозок” (блок С14:F16);
◦ Установите курсор на значение >= (больше или равно);
◦ В поле “Ограничение” наберите 0.
Убедитесь, что окно диалога команды “Добавление
ограничения” имеет вид, показанный на рисунке
Тот же результат достигается установкой флажка
"Неотрицательные значения" в окне диалога "Параметры
поиска решения".

16.

Окно программы “Поиск решения” примет вид

17.

При необходимости установим параметры поиска с помощью
кнопки “Параметры”:
◦ в появившемся окне диалога “Параметры поиска решения”
установим флажок “Линейная модель”;
◦ можно изменить другие параметры поиска;
◦ Вернемся в окно «Поиск решения»
В окне "Поиск решения" щелкаем на кнопке "Выполнить»

18.

На рабочем листе EXCEL в блоке "Матрица перевозок"
появляется решение транспортной задачи

19.

В диалоговом окне "Результаты поиска решения" выберите
"Восстановить исходные значения». Для завершения расчетов
щелкните на кнопке ОК.