Ecel 财务应用 最短路问题.docxVIP

Excel 财务应用 最短路问题 在网络优化问题中，许多问题都可以使用最短路优化的模型解决问题，如设备更新、管 道铺设、线路安排和厂区布局等。本节就以铺设管道为例，介绍如何利用规划求解，解决最短路问题。 已知条件某个新建住宅小区要从A 点到 F 点铺设供水管道，中间经过B、C、D、E 点，要求水源与各点相通，而且各点到水源间的路径最短，该管道各点之间的距离如图 9-69 所示。求从A 点到F 点铺设管道的最短路径。 已知条件 图 9-69 各点之间的距离 根据已知条件，在 Excel 工作表中，输入相关数据，创建该问题的规划求解模型，如图9-70 所示。 图 9-70 创建求解模型 在创建该模型时，可以将出发地看成供应量为 在创建该模型时，可以将出发地看成供应量为1 的供应点，目的地为需求量为 1 的点， 网络中其他点的净流量为 0，并将各点之间的距离看作成本，即可将该问题转化为一个特殊的最小费用流问题。 接下来，计算各节点的净流量。选择节点 A 净流量所对应的单元格，即 F14 单元格，在 【编辑栏】中，输入“=SUMIF(A14:A28,E14,C14:C28)-SUMIF(B14:B28,E14,C14:C28)”公式，即 输入可计算该节点的净流量，如图 9-71 所示。再使用相同的方法计算其他节点的净流量。 输入 图 9-71 节点A 的净流量 选择最短总距离所对应的单元格，即 G27 单元格，在【编辑栏】中，输入“=SUMPRO 输入DUCT(C14:C28,D14:D28)”公式，即可得到最短总距离的值，如图9-72 所示。 输入 图 9-72 计算最短总距离 设置然后，再次选择G27 单元格，单击【规划求解】按钮，在【规划求解参数】对话框中， 选择【最小值】单选按钮，设置可变单元格的数据区域，并添加“$F$14:$F$19=$H$14:$H$1 9”约束，如图 9-73 所示。 设置 图 9-73 设置规划求解参数 最后，单击【求解】按钮，保留规划求解结果之后，即可得出该问题的最优解，如图9 求解结果-74 所示。 求解结果 图 9-74 规划求解结果 由规划求解结果可以得出结论：流量值为 由规划求解结果可以得出结论：流量值为 1 的节点代表最短路径上的两个节点，流量值为 0 则表示不经过这两个节点。因此，该问题的最短路径应该为A 点到C 点，再从C 点到 F 点，并且铺设管道的最短距离为 53。