☰

我如何使用 Excel 求解器来解决这个问题？

tag-icon • tag-icon microsoft-excel solver
我如何使用 Excel 求解器来解决这个问题？

ABC 公司是一家全球电气零部件分销商。该公司签订合同从欧洲购买零部件，并将它们运送到三个欧洲港口 E1、E2、E3 的仓库。各种零部件根据美国客户的需求装入集装箱。每个港口每月都有固定数量的集装箱可用。然后，集装箱通过集装箱船运往海外 P1、P2、P3 和 P4 港口。从这些海港，集装箱通常与卡车相连，并运往 I1、I2 和 I3 的内陆港口。每个港口每月都有固定数量的货运拖车。这些内陆港口有时被称为“货运村”或联运枢纽，集装箱在这里被收集并从一种运输方式转移到另一种运输方式（即从卡车到铁路，反之亦然）。从内陆港口，集装箱被运输到 ABC 在 D1、D2、D3、D4 和 D5 的配送中心。以下是该海外供应链中各个启运地点与目的地之间的处理和运输成本（美元/集装箱）以及每个港口的可用集装箱数量：

Excel 表示例

红色单元格表示特定路线不可行，因此无法考虑。确定从该海外供应链上每个启运点到每个目的地的最佳运输方式，从而实现最低的总运输成本和每个阶段产生的总运输成本。

答案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 之前“方程式”选项卡的屏幕截图。有几行被隐藏了。

在此处输入图片描述

求解器设置如下：

  1. 设定目标：是$M$19
  2. 收件人： 是 Min
  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 美元，所有需求都得到满足，并且没有违反任何约束。以下是约束表的屏幕截图...

在此处输入图片描述

下面是成本表的屏幕截图，其中所有零路线均被过滤掉……

在此处输入图片描述

答案2

如果您想要一次性答案，最便宜的路线E2 > P4 > I2 > D2费用为 1,452 美元。

我使用的方法并不优雅。我制作了一个包含所有可能选项的表格，使用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，并调整每个字段以引用正确的范围。最后，总成本字段只是将这三段航程加在一起。我按升序排序并找到了最便宜的路线。所有错误（使用不可用路线的错误）都被发送到底部。

如果您想要一个持续的系统来跟踪每段旅程中当前有多少交通工具在路上，以便根据当前可用的交通工具选择最便宜的路线，您可能需要查看数据库解决方案。或者，您可以使用排序列表并从顶部开始，一直向下查找，直到找到每段旅程当前可用的路线。

相关内容