• 140736
  • 636
  • 41
Нравится блог?
Подписывайтесь!

Как оптимизировать грузоперевозки, транспортная задача

Привет всем, рассмотрим оптимизацию грузоперевозок с помощью MS Excel. Дело довольно таки не сложное. Для начала ставим для себя задачу.

Имеется пять складов и четыре пункта потребителей, естественно что на складах разное количество товара и потребители нуждаются в разном количестве товара. Как минимально затратить деньги на доставку и доставить потребителям то что они хотят - это я сейчас рассчитаю.

И так вот сами ресурсы.

Склады Наличие товара, т.
Склад №1

200

Склад №2

190
Склад №3

220

Склад №4

145

Склад №5

280

А это то что ожидают потребители.

Пункты

Потребность в товарах, т.

1 пункт

200

2 пункт

150

3 пункт

220

4 пункт

330

Расстояния между складами и потребителями неотъемлемая часть расчета.

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

Думаю объяснять тут не нужно что за циферки такие здесь? Смотрим по пересечениям и узнаем расстояние.

Подготовка к решению

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

Рисунок 1. Изменяемые ячейки.

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

Например, значение ячейки B6=СУММ(C6:F6)

Значения ячеек по 9 строке по столбцам от C до F определяется суммированием данных ячеек соответствующих столбцов с 4 по 8 строки.

Например, значение ячейки D9=СУММ(D4:D8)

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

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

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

В строке 16 по столбцам C-F определим грузооборот по каждому пункту доставки. К примеру для 1 пункта (ячейка С16) это рассчитывается с помощью формулы С16=СУММПРОИЗВ(C4:C8;C11:C15)

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

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

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

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

Решение задачи

Для решения транспортной задачи нужно воспользоваться процедурой “Поиск решения”, которая находится в меню “Сервис”. Да да, именно там, если нету - пишите, решим проблему.

После выбора данной команды появится диалоговое окно.

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

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

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

Если сейчас запустить процесс подбора параметров, то будет найден вариант, где все переменные равны нулю. И это правильно – если не перевозить ничего, то это самый дешевый вариант. Но нам необходимо перевезти товар, поэтому надо наложить некоторые ограничения для поиска решения.

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

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

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

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

Для изменения и удаления ограничений в списке “Ограничения” диалогового окна “Поиск решения” укажите ограничение, которое требуется изменить или удалить. Выберите команду “Изменить” и внесите изменения либо нажмите кнопку “Удалить”.

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

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

В ячейках с B4 по B8 на листе находятся объемы поставок с конкретных складов. В ячейках с B11 по B15 – запасы на этих же складах. Так как невозможно вывести со склада больше, чем на нем есть, первое значение должно быть не больше второго.

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

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

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

Нажмите кнопку “Выполнить” для подбора решения.

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

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

Нажав кнопку “ОК”, вы занесете вариант решения на рабочий лист.

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

Минимальный грузооборот перевозок, при соблюдении всех условий, равен 3540 т./км.

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

Виктор ZEX-VIK
5 декабря 2011, 1:17
2213

Загрузка...

Комментарии

Оставьте свой комментарий

Спасибо за открытие блога в Yvision.kz! Чтобы убедиться в отсутствии спама, все комментарии новых пользователей проходят премодерацию. Соблюдение правил нашей блог-платформы ускорит ваш переход в категорию надежных пользователей, не нуждающихся в премодерации. Обязательно прочтите наши правила по указанной ссылке: Правила

Также можно нажать Ctrl+Enter

Популярные посты

Только на 10-й раз он смог поступить в Кембридж! Герой с Кармакшинского района

Только на 10-й раз он смог поступить в Кембридж! Герой с Кармакшинского района

Свыше 10 известных университетов приглашают его обучаться за рубежом, но выбор будет оставаться за Сакеном. Он выберет обучение в The University of Edinburgh и отправится в эту удивительную страну...
socium_kzo
30 нояб. 2016 / 11:06
  • 11560
  • 11
Японец о Казахстане: «Ваши девушки уж сильно себе набивают цену...»

Японец о Казахстане: «Ваши девушки уж сильно себе набивают цену...»

"Мужчины должны у вас тут права качать, ибо их процент в вашей численности населения уступает проценту женщин". Я машинально начала уверять, что у нас в стране таковых не имеется...
Sapientia
вчера / 10:52
  • 5496
  • 65
Взгляд со стороны: Назарбаев глазами кыргыза

Взгляд со стороны: Назарбаев глазами кыргыза

В чем уникальность этой личности? В чем его успех или провалы? Эти вопросы требуют глубокой аналитики и исследований. Я же хочу рассказать о Нурсултане Абишевиче глазами рядового кыргыза...
maxes
1 дек. 2016 / 8:05
  • 5377
  • 20
Мысли вслух. Почему казахи перестали общаться с родственниками и ходить в гости?

Мысли вслух. Почему казахи перестали общаться с родственниками и ходить в гости?

Дастархан в те времена был скромен. Не было понятия «сынау» - осуждения кто как живет, какой в доме ремонт и т.д. Пока взрослые обсуждали задержку заработной платы, мы играли в армию, жмурки, строили городки...
socium_kzo
вчера / 15:19
  • 4923
  • 14
Молчание врачей. Дети ЮКО, заражённые ВИЧ 10-11 лет назад, узнают о диагнозе-приговоре

Молчание врачей. Дети ЮКО, заражённые ВИЧ 10-11 лет назад, узнают о диагнозе-приговоре

Как сообщают новостные издания, в ближайшее время в Южном Казахстане 102 детям в возрасте 11-12 лет сообщат об их страшном диагнозе. Все эти дети были заражены ВИЧ, причём большинство было инфицировано по вине врачей.
openqazaqstan
2 дек. 2016 / 13:57
  • 4236
  • 4
Почему Дональд Трамп назвал Казахстан чудом. Президент подтягивается по географии

Почему Дональд Трамп назвал Казахстан чудом. Президент подтягивается по географии

Трамп и не подозревает, что 16 декабря 1991 Казахстан не создал, а восстановил свою национальную государственность. Иначе бы он упомянул не только 25 лет, а больше чем 550 лет казахской истории.
Stehlikova
2 дек. 2016 / 9:02
  • 4686
  • 88
Аэропорт Схипхол и Алматы. Смотришь на это и ощущение, что мы лет на тридцать отстали

Аэропорт Схипхол и Алматы. Смотришь на это и ощущение, что мы лет на тридцать отстали

Недавно пролетал через аэропорт Амстердама - Схипхол. Так как улетал из аэропорта Алматы, то не мог не начать сравнивать эти аэропорты.
Superkurt
30 нояб. 2016 / 10:09
  • 3681
  • 14
Мой личный опыт использования Astra Plat: мелочи в моем кармане заметно стало меньше

Мой личный опыт использования Astra Plat: мелочи в моем кармане заметно стало меньше

Давно ждал запуска электронного билетирования в общественном транспорте Астаны. В ноябре 2016 года этот день настал. Мой опыт насчитывает последние 2 недели и мне есть чем поделиться.
iamYerlan
1 дек. 2016 / 15:24
  • 3290
  • 15
Понять и простить: почему большинство стран бывшего СССР отказалось от амнистий

Понять и простить: почему большинство стран бывшего СССР отказалось от амнистий

Президент Назарбаев предложил амнистию для совершивших преступления небольшой тяжести несовершеннолетних, пожилых, женщин и других социально уязвимых категорий осуждённых.
openqazaqstan
30 нояб. 2016 / 13:45
  • 3134
  • 21