Excel求解旅行商问题及实现

2017-01-12 23:17刘芊
东方教育 2016年13期

刘芊

摘要:旅行商问题历来是大家感兴趣的一个难题,对其求解也有各种算法。Excel中规划求解也是有效解决问题的方案之一,通过介绍其基本原理,求解思路和在旅行商问题中的具体实现步骤,希望能对读者学习Excel的高级应用有很好的借鉴作用。

关键词:旅行商问题;规划求解;加载宏

1 引言

旅行商问题(TSP),是假设一个旅行商出于业务需要,要到N个城市去,那么如何找出一条最佳的路径使其能既经过每个城市,又路径最短。旅行商问题在实际工作中有很多具体应用,如货物配送、家用管网规划、网络路由选择等。该类问题与普通的求最短路径的根本区别是:既要经过已知的所有节点,又要从起点到终点形成封闭回路,在满足这两个前提下路径最短。对于较大规模的该类问题,需要通过智能算法(蝙蝠、蚁群等)求解,对于一定规模的旅行商问题可采用Excel的规划求解来解决。

2 方法概述

规划求解是Excel的“可使用加载宏”的一种,它能够对存在多个变量的线性或非线性问题求解,以求出最优值。通过规划求解,可以帮助用户得到最优的设计方案。其工作原理是可以设计多个可调整的单元格,给出这些单元格需遵守的约束条件,同时给出目标单元格的公式,通过改变可变单元格的值,求出目标单元格的最大值、最小值或指定值。

3解决方案:

以下图为例,共有A、B、C、D、E、F六个城市,它们之间的路径及距离如图1所示

3.1 打开Excel 2010,建立一个工作簿,名为“旅行商问题”

3.2 在C4:C19单元格,用“9999”来替换“-”,如图所示,该步骤是给不存在的路径用一个很大的数来表示,防止以后选择该路径。

3.3 建立解决模型,如下图所示。

在以上模型中,我们用“来源唯一性”限制出发地,用“目标唯一性”限制抵达地,以确保都任何时候只能走一条路径,不能同时存在多条路径。

打开Excel 2010的“加载宏”选择,选择其中的“规划求解加载项”前的复选框,然后“确定”,操作后我们再打开“数据”选项卡,就能看到“规划求解”。

给单元格输入公式,其中,I14 单元格 =sum(c14:h14),填充I15:I1

C20 单元格=sum(c14:c19),填充D20:H20

J14 单元格 =sumproduct(c4:h4,c14:h14),填充J15:J19

J20 单元格 =sum(j14:j19)

C14:H19 的“单元格格式”我们可以设置为“数字”,选择“自定义”中的“0”,我们用该区域表示实际路径的选择情况,选择即为“1”,不选即为“0”,然后对应出发地、抵达地,形成一条回路。这个区域是可变区域,代表了不同路径的组合可能,J20是规划求解的目标单元格,即总路径,在这里,根据题意我们给J20选“最小值”。

3.4 观察求解结果,可以看到,选择的路径是:C-A-C,B-D-B,E-F-E,它们不能形成封闭回路,所以不符合要求,为解决这个问题,我们需要加上限制条件,防止返回情况发生,也就是E14和C16不能同时为“1”,F15和D17不能同时为“1”,H18和G19不能同时为“1”

在下面可选C23:C25输入这些“添加条件”,C22单元格录入“添加条件”,C23至 C25录入公式。

C23单元格 =E14+C16

C24单元格 =F15+D17

C25单元格 =H18+G19

3.5 继续规划求解,可以在“规划求解参数”对话框中增加约束条件,设置C23:C25<=1

3.6 得出新的规划求解结果,如下图所示。

3.7 经观察,这次求得路径为B-A-C-B,D-F-E-D,仍让没有形成闭合回路,那就要同上增加新的约束条件,C26单元格=D14+E15,C27单元格=F19+G17,

3.8 然后重新设计“规划参数”,如下图所示。

3.9 得出最优的规划求解结果,如下图所示。

3.10 观察下,这次形成的路径是B-D-F-E-C-A-B,为封闭路径,符合要求,那么旅行商路径即为B-D-F-E-C-A-B,反向路径B-A-C-E-F-D-B 也是旅行商的解,路径长同为250。

4 结束语

用Excel2010求解旅行商问题及实现如上分析,具体节点数不同,数据不同,求解过程会有差异,但总体思路是一致的,根据具体路线,有繁有简,实现的步骤也会略有差异。

参考文献:

[1]金晓龙. Excel高级应用实例教程,2012第一版:144-150