Как мне использовать решатель Excel для решения этой задачи?

Question

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

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

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

Answer 1

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

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 извлекаются из вкладки Данные, поэтому максимальное количество контейнеров из E1 равно =VLOOKUP(L2,Data!$A$3:$F$5,6,FALSE), максимальное количество контейнеров из I1 равно =HLOOKUP(L9,Data!$B$10:$D$15,6,FALSE)и т. д.; аналогично, единицы спроса извлекаются из вкладки Данные.
9. Я добавил дополнительное ограничение, чтобы гарантировать, что не будут выбраны нежизнеспособные маршруты.
10. Целью является общая стоимость, которая представляет собой сумму столбца J.

Вот снимок экрана вкладки «Уравнение» перед запуском 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 при удовлетворении всего спроса и отсутствии нарушенных ограничений. Ниже представлен снимок экрана таблицы ограничений...

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

Answer 2

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

---

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