-1

Корпорация ABC является глобальным дистрибьютором электрических компонентов и компонентов. Компания заключает контракт на закупку комплектующих и запчастей из Европы и доставляет их на склады в трех европейских портах, E1, E2, E3. Различные компоненты и детали загружаются в контейнеры по требованию клиентов из США. Каждый порт имеет ограниченное фиксированное количество контейнеров, доступных каждый месяц. Контейнеры затем отправляются за границу контейнеровозами в порты P1, P2, P3 и P4. Из этих морских портов контейнеры обычно соединяются с грузовыми автомобилями и перевозятся во внутренние порты в I1, I2 и I3. В каждом порту каждый месяц имеется фиксированное количество грузовых автоперевозчиков. Эти внутренние порты иногда называют «грузовыми деревнями» или интермодальными узлами, где контейнеры собираются и перемещаются из одного вида транспорта в другой (т.е. из грузовика в железнодорожный транспорт и обратно). Из внутренних портов контейнеры транспортируются в распределительные центры ABC в D1, D2, D3, D4 и D5. Ниже приводятся данные об обработке и стоимости доставки ($/ контейнер) между каждым из пунктов отправления и назначения по этой зарубежной цепочке поставок и доступными контейнерами в каждом порту:

Образец листа Excel

Красная ячейка означает, что конкретный маршрут не является жизнеспособным вариантом и, следовательно, не может рассматриваться. Определите оптимальные отгрузки от каждой точки посадки до каждого пункта назначения в этой зарубежной цепочке поставок, что приведет к минимальной общей стоимости доставки и общей стоимости доставки, понесенной на каждом этапе.

2 ответа2

0

Если вы хотите получить однократный ответ, самый дешевый маршрут это E2 > P4 > I2 > D2 по цене $ 1452.

Метод, который я использовал, не был элегантным. Я составил таблицу всех возможных вариантов, использовал INDEX и MATCH чтобы посмотреть стоимость каждого этапа поездки, а затем отсортировал их по возрастанию общей стоимости. Вот как выглядит мой лист:

Spreadhseet

Данные слева - это то, что я скопировал из вашей таблицы. Таблица справа - это то, что я создал. Для каждого из полей E, P, I и D я просто вручную вводил их в группы. D1, D2, D3, D4, D5. Скопируйте эти пять и вставьте их много. I1, копировать для набора D, I2, копировать для набора D и т.д., Пока таблица не будет заполнена. Для поля $ EP я объединил INDEX и MATCH чтобы найти стоимость перехода от E(что угодно) до P(что угодно).

=1/(1/INDEX($B$3:$E$5,MATCH([@E],$A$3:$A$5,0),MATCH([@P],$B$2:$E$2,0)))

INDEX принимает массив и возвращает ячейку из этого массива. Чтобы найти нужную строку, я использовал MATCH чтобы найти значение E в диапазоне A3:A5 . Чтобы найти столбец, я искал значение P в B2:B4 . Это дало мне стоимость перехода от E к P. Я добавил обертку 1/(1/...) вокруг всей вещи, чтобы она возвращала ошибку, если нога поездки была нулевой, указывая на то, что она была недоступна. ,

Затем я скопировал эту формулу в поля $ PI и $ ID, настроив каждое из них для ссылки на правильные диапазоны. Наконец, поле Total Cost просто сложило эти три ноги вместе. Я отсортировал по возрастанию и нашел самый дешевый маршрут. Все ошибки - те, которые используют недоступные маршруты - были отправлены на дно.


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

0

Вот пример настройки вашей проблемы для Солвера. Возможно, вы сможете сделать его более компактным, но способ, которым я его настрою, позволяет легко диагностировать любые проблемы.

  1. Используйте ваши исходные данные на вкладке "Данные".
  2. Создайте новую вкладку, которую я назвал "Уравнение", чтобы настроить задачу Решателя.
  3. Столбцы от A до D используются для настройки всех возможных комбинаций маршрутов. например, строка 1 - заголовки; Строка 2 - это E1, P1, I1, D1; Строка 2 - это E1, P1, I1, D2; и т.п.
  4. В столбцах с E по G указаны затраты, связанные с Европой до порта США, от порта США до внутреннего порта и от внутреннего порта до распределительного центра. Это заполняется с помощью VLOOKUP и MATCH. например, в E2 это =VLOOKUP(A2,Data!$A$3:$E$5,MATCH(B2,Data!$A$2:$E$2,0),FALSE) ; в F2 есть =VLOOKUP(B2,Data!$A$11:$D$14,MATCH(C2,Data!$A$10:$D$10,0),FALSE) ; в G2 это =VLOOKUP(C2,Data!$A$20:$F$22,MATCH(D2,Data!$A$19:$F$19,0),FALSE) . Это все заполнены.
  5. Столбец H - это общая стоимость единицы. Чтобы сделать нежизнеспособные маршруты очевидными, я установил для них общую стоимость 1 000 000. например, H2 =IF(OR(E2=0,F2=0,G2=0),1000000,SUM(E2:G2)) . Это заполнено.
  6. Столбец I - это количество единиц (контейнеров), которые перемещаются по этому маршруту. Эта колонка будет управляться решателем. Я инициализировал каждую строку с 1.
  7. Столбец J - это общая стоимость маршрута. например, J2 =H2*I2 и заполнен.
  8. Другая таблица построена на той же вкладке, чтобы содержать целевую функцию и ограничения. Это от L1 до O19. Вот некоторые примеры расчетов: nUnits - это суммы из предыдущей таблицы, поэтому число контейнеров из E1 равно =SUMIFS($I$2:$I$181,$A$2:$A$181,L2) , количество контейнеров из P1 =SUMIFS($I$2:$I$181,$B$2:$B$181,L5) и т. д .; maxUnits извлекаются из вкладки Data, поэтому максимальное количество контейнеров из E1 равно =VLOOKUP(L2,Data!$A$3:$F$5,6,FALSE) , максимальное количество контейнеров из I1 =HLOOKUP(L9,Data!$B$10:$D$15,6,FALSE) и т.д .; аналогично, единицы спроса извлекаются из вкладки "Данные".
  9. Я добавил дополнительное ограничение, чтобы гарантировать, что не будут выбраны нежизнеспособные маршруты.
  10. Целью является общая стоимость, которая является суммой столбца J.

Вот снимок экрана с вкладкой Equation перед запуском Solver. Несколько рядов скрыты.

Solver настроен следующим образом:

  1. Установить цель: это $M$19
  2. До: это мин
  3. Изменяя переменные ячейки: это $I$2:$I$181
  4. Сделать неотрицательные переменные неотрицательными не выбран (это обрабатывается с ограничениями)
  5. Выберите метод решения: эволюционный. Эволюционный гораздо быстрее, чем другие методы при обработке целочисленных ограничений.
  6. С учетом ограничений: это много ...
    • $I$2:$I$181 = integer - должно иметь целые числа контейнеров
    • $I$2:$I$181 >= 0 - отрицательные расходы не могут быть
    • $M$2 <= $N$2 дублируется для каждой строки до $M$11 <=$N$11 - не нарушать максимальное количество контейнеров
    • $M$12 = $O$12 дублируется для каждой строки до $M$16 = $O$16 - удовлетворить спрос
    • $M$17 = $N$17 - не использовать нежизнеспособные опции

Ниже приведен снимок экрана диалогового окна «Параметры решателя» ...

С этой установкой я получаю общую стоимость в размере 1 661 119,00 долл. США, при этом все требования удовлетворены, а ограничения не нарушены. Ниже приведен скриншот таблицы ограничений ...

Ниже приведен снимок экрана с таблицей затрат со всеми отфильтрованными нулевыми маршрутами ...

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .