Как мне использовать Excel Solver, чтобы решить эту проблему?

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

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

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

Источник

2 ответа

Решение

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

  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 долл. США, при этом все требования удовлетворены, а ограничения не нарушены. Ниже приведен скриншот таблицы ограничений...

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

Источник

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

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

Данные слева - это то, что я скопировал из вашей таблицы. Таблица справа - это то, что я создал. Для каждого из полей 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 просто сложило эти три ноги вместе. Я отсортировал по возрастанию и нашел самый дешевый маршрут. Все ошибки - те, которые используют недоступные маршруты - были отправлены на дно.

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

Источник
Другие вопросы по тегам