基于Excel的数学模型求解

2023年12月8日发(作者：奔驰b级旅行车)

基于Excel的数学模型求解

2011年第7期

中图分类号:TP311文献标识码:A文章编号:1009—2552(2011)07—0125—05

基于Excel的数学模型求解

于学文

(北京交通职业技术学院,北京102200)

摘要:Excel不仅是一款优秀的办公自动化软件,而且在数学模型求解方~-g-.有专业数学软件

不可比拟的优势,它既可以用于线性规划模型的求解,也可以用于图论,动态规划和某些非线

性规划模型的求解.通过三个实例,以图表的方式列举了Excel在层次分析法,最大流问题和动

态规划模型求解中的具体操作.

关键词:Excel;层次分析法;最大流问题;动态规划

MethodsforsolvingmathematicalmodelbasedonExcel

(BeijingJiaotongVocationalTechnicalCoHege,BeUing102200,China)

Abstract:Excelisnotonlyagoodofficeautomationsoftware,andhastheadvantageinsolvingthe

eusedeitherforlinearprogrammingmodel,alsocanbeusedingraphtheory

paper,threeexamplesare

enumeratedtheExcelchartinanalytichierarchyprocess,themaximumflowproblemandthedynamic

programmingmodelofthespecificoperation.

Keywords:Excel;analytichierarchyprocess(AHP);maximumflowproblem;dynamicprog ramming

0引言

在数学建模时,人们往往借助于Matlab,Mathe—

matics和Lingo等专业数学软件求解模型.但这些

软件专业化程度较高,对于文科生和高职生有一定

难度.与专业化的数学软件相比,Excel有其自身的

优势.首先,Madab,Mathematics和Lingo等专业数

学软件都是英文的,而Excel汉化水平非常高,更容

易推广和进一步挖掘其功能.其次,其他专业软件

需要记住一些命令和编程,而Excel使用菜单操作,

拥有大量的函数,公式和数据处理功能,操作起来非

常方便.再次,专业数学软件需要购买和安装,而

Excel目前几乎每台电脑都安装,不需专门购买和安

装.专业数学软件多数人没有基础,需要从头学起,

而Excel是目前使用最广泛的办公自动化软件,人

们都有一定的基础,很容易进一步学习,所以使用

Excel软件,转换成本低.

1层次分析法

层次分析法是由美国运筹学家等人

于2O世纪70年代提出的对复杂问题作出决策的一

种简明有效的方法,该方法一般将决策问题分为三

个层次,最上层为目标层,最下层为方案层,中间层

为准则层.层次分析法通常通过四步来完成,即建

立层次结构模型,构造成对比较矩阵,计算权向量和

组合权向量,进行一致性检验和组合一致性检验来

实现.其中第一步和第二步往往需要通过大量的调

研或由经验丰富的专家来完成,第三步和第四步完

全是计算性的,其计算工作量较大.利用Excel进

行求解,会使计算过程变得简单,清晰. 假设某公司欲采购商品,现有s1,s2,s3和S4

等4个供应商可供选择,公司决策层决定从质量,价

格,服务与交货期4个方面进行考虑.要求用层次

分析法给出供应商的层次.

分析与解答:经决策层讨论,将问题分为三个层

收稿日期:201l一04—21

作者简介:于学文(1965一),男,管理学硕士,会计师,税务师.现主

要从事高职数学教学及研究工作.

一

125—

次,即目标层为选择供应商,方案层为可供选择的4

个供应商s1,s2,s3和S4,准则层为质量,价格,服

务与交货期四个方面.具体做法如下.

第一步:完成两两判断矩阵,即矩阵主对角线上

的数据都是1,其余数据满足=1/x.如图1单

元格区域B2:E5所示.

第二步:计算各指标权重,权重提供了对每个指

标相对重要性的测度.

(1)将各评分值除以各列之和,在单元格B8

中输人\"=B2/sum(B$2:B$5)\",选中B8,拖拽填充

柄至单元格区域B11;分别选中单元格B8,B9,B10,

Bll拖拽填充柄至E8,E9,El0,E11.

(2)按行求和,计算各指标权重.在单元格

中输入\"=sum(B8:E8)\",然后选中B8拖拽填充柄

至F11;在F12中输入\"=SUM(F8:F11)\";在G8中

输入\"=SUM(B8:E8)/$F$12\",然后选中G8拖拽

填充柄至G11.

(3)一致性检验.在H8中输入\"=MMULT

(B2:E2,$G$8:$c$11)/$G8\",然后选中H8拖拽至 H11;在H12中输人\"=AVERAGE(H8:HI1)\",即

为最大特征值A;分别在单元格H13,H14中输入

\"

=

(H12—4)/3\"和\"=H13/O.9\",得到一致性指标

CI和随机一致性比例CR,如图1所示.

,一￡lDE.I?|t,1L匿强n鼍矗

|重要质量价格服务夺常湘

,譬譬,12唾3l/l判:矩阵

3{价格lt/2t3r.

服务l114i/3l2l

l5{交贫l1131131/21l

一

—I质量价格服务交货是按和求和指标投重f=麟ULT(B2E2,SG$8:$GSn)/$G8}8

鬻质量0.480.54.5O.哇O,331.8293760.4574.2l

皇j价格0.孔0.2730.35O.331.1990020.34.9～I:A哪E(H8:m1)llO一服务0.12O.0孽O12O.220.5507780.384.08,一

1rl交货O.60.O露l0.O60..0.4208q4O.05405/

:l24以地aX4j3—一}=(I-I12—4j/3

Il3lcI0.04

0.O5l—u1々,^0l

图1指标权重及一致性检验

第三步:计算供应商间各指标的两两比较矩阵的步骤一样.唯一区别就是对每一个指标都有相应

并做一致性检验.就是对4个供应商就每个指标进的比较矩阵.决策者首先就某一指标对供应商进行

行两两比较,这个过程与建立指标的两两比较矩阵两两比较,然后对其他指标重复上述过程.

0 图2供应商间各指标的两两比较矩阵及一致性检验

第四步:供应商层次总排序并做组合一致性检(1)将第二步的\"指标权重\"和第三步的\"权

验.总排序的结果是通过对各供应商各指标下的权重\",最大特征值A缸,一致性指标CI和随机一致性

重,用对应指标权重求加权平均得到的.比例CR分别转入图3. ———

126—--——

(2)组合一致性指标cI和随机一致性比例CR

的计算见图3中的标注.

(3)确定方案.从比较结果可以看出,应当选

择s1,其次是s4,如图3所示.

38

SlS253s4指标根重

质量:q丰U?l

41价格0.3030.5TO.O80.0464850.3|

42服务0.5970.1畦0.2i0.0500780.138,

43交费辩0.1510.O60.6哇5|/一=HULT(G40:G43,C45:F45)

4蠢ma:.123d.14.14.049995一—r-,ITT,^l

sCI0.04lOO唾O.O5OO66650.O西{/—■\'-,-J,uuTn1l

46CR0.0450.04O.O50.O.85l78.9,O.0

供应商.\'0.240.1寸o.2943lI

49=SU~PRODUCT(c40:C43,$G$40:$G$唾3:l

图3供应商总排序及组合一致性检验

2最大流问题

最大流问题是一类应用极为广泛的问题,是图

论中的一种理论与方法,用于研究网络中的一类最

优化问题.例如,在交通运输网络中有人流,车流, 货物流,供水网络中有水流,金融系统中有现金流,

通信系统有信息流等等.福特和福尔克森设计了求

解最大流的标号法,后来又有人加以改造,使得求解

最大流的方法更加丰富和完善.事实上,最大流问

题也可以用Excel来实现.

假设从三口油井1,2,3经管道将油输至脱水处

理厂7和8,中间经4,5和6三个泵站.已知图4弧

旁数字为各管道通过的最大能力(吨//J,时),求从

油井每小时能输送到处理厂的最大流量.

图4油井至脱水厂管道示意

分析与解答:

第一步:根据图4的已知数据构建各节点间的

容量矩阵.

(1)在单元格区域A2:I10区域内构建\"各结点

间的容量矩阵\"空表,在单元格区域A3:A10中输入

\"1,2,3,4,5,6,7,8\"作为起点,在单元格区域B2:I2

中输入\"1,2,3,4,5,6,7,8\"作为终点.

(2)输入每个结点的容量.根据已知条件,按

起点到终点的方向,在相应位置输入结点间允许的

最大流量.如在单元格E3中输人\"20\"代表结点

l间的最大允许流量为20,在单元格F5中输入

\"15\"代表结点3—5间的最大允许流量为15.各结

点问的容量矩阵如图5所示.

第二步:构建最大流求解表.

(1)构造表格:选择单元格区域A2:IlO,复制粘

贴到单元格区域A15:I23中;删除单元格区域B16:

I23中的所有数字.

l结点问的容量矩阵

2i0②④④⑤@0@ 3{①20

4⑦102050

5惩}l5

6201010

7嚏》303O

8⑤5020

—⑦

l.嬗I

图5结点同的容量矩阵

(2)定义约束条件.

①定义结点流出和:在单元格J15中输入\"结点

流出和\",在单元格J16中输人\"=SUM(B16:

I16)\",再选中J16,移光标到右下角,呈十字时按住

鼠标左键拖至J23.

②定义终点流人和:在单元格A24中输入\"结

点流人和\",在单元格B24中输人\"=SUM(B16:

B23)\",选中B24,移光标到右下角,呈十字时按住

鼠标左键拖至K24.

③定义结点流差:在单元格A25中输入\"结点

流差\",选中单元格区域B25:I25,输入\"=TRANS.

POSE(J16:J23)一B24:I24\",同时按组合键Ctrl+

Shift+Ener确定.

④定义结点流差限制:在单元格A26中输人

\"结点流差限制\",在单元格E26:G26中输人0.

⑤定义目标函数:在单元格J24中输入\"=SUM

(J16:J18)\".最大流量求解表如图6所示.

l5.i①⑦@④@@@@结点流出和

篙Q

l8③ }i④一

@

L乳j⑤j

誊⑦

2誊@I

结点巍凡和OO00O0OOl

污{缔罐0O00000O

—j舌结点滴差隧l制0O0

图6最大流量求解表

一

127—

第三步:线性规划求解.

(I)选择\"工具\"\"规划求解\",弹出\"规划求解

参数\"对话框,如图7所示定义各选项.

潮嘱莉唬培毽):～圈

《于:$默值瞧)0曩啦∞0值鸯∞一io……一

礴要荤惩播建一一…一一～

鋈匦二:二二二二二::霾辽甄匿囝

警t一

}蠹蠹i$1菇523浮?弱贰……]匮萤亟.{牡Sl6:=整数{

_;嬲:l船:蝴黝

;l曼隧堂j

西鳓

[莉

疆酗

蘑嘲

疆霞晒口

图7规划求解参数对话框设置 (2)求解,得到最大流量线性规划求解结果,如

图8所示.

图8最大流量求解结果

把最大流量求解结果填写进图4,得如图9所示

最大流量图.从图9可知,最大流量为110Ⅱ屯/小时,最

大流量安排方案如图9中括号内标注数字所示.

10(1O)

,

30(10)

50(50)

20(20)

③/30(30)————@

图9最大流量图示

3动态规划模型

动态规划是解决多阶段决策过程最优化问题的

一

种方法.在实际的决策过程中,由于涉及的参数

比较多,往往需要将问题分成若干个阶段,对不同阶

段采取不同的决策,从而使整个决策达到最优.显

然,由于各个阶段选择的策略不同,对应的整个过程

就可以有一系列不同的策略.这种方法把困难的多

阶段决策问题变换成一系列互相联系的比较容易解

决的单阶段问题.应该指出,动态规划只是求解某

类问题的一种方法,是考察问题的一种途径,而不是

一

种特殊的算法.因而,它不像线性规划那样有一

个标准的数学表达式和明确定义的一组规则,而必

一

】28一 须对具体问题进行具体分析处理.

动态规划是现代企业管理中的一个重要决策方

法,利用Excel软件中\"公式\"和\"规划求解\"的强大

功能,能够较好地实现对背包问题,生产经营问题,

资金管理问题和资源分配等问题的求解.本文仅以

资源分配问题的求解进行说明.

假设某部门根据国家计划的安排,拟将某种高

效率的设备5台,分配给所属的工厂1,工厂2和工

厂3,各工厂若获得这种设备之后,可以为国家提供

的盈利如表1所示.问:这五台设备如何分配给各

工厂,才能使国家得到的盈利最大.

表1三个\"7-厂提供的盈利与分配设备的关系

Ir

0

3

7

9

12

13

1工厂2工厂3

0

5

10

ll

11

U

0

4

6

l1 12

12

分析与解答:

第一步:根据问题建立数学模型.C#-表示对工

厂i投资台设备产生的盈利.决策变量‰表示是

否向工厂i分配_『台设备(1一是,0一否)(i=1,2,3;

J=1,2,3,4,5).根据表1所给情况得到数学模型

如下:

∑≤1J=l

35

∑∑≤5i=1』=1

0,1

第二步:输入原始数据.

(1)在B3:P3中输入决策变量名称xl1,

x12,…,x35;在134:Q8中分别输入各决策变量在目

标函数和约束条件中相应的系数.

(2)在单元格Q4中输入\"=SUMPRODUCT($B

$2:$P$2,B4:I)4)\",选中Q4,移光标到右下角,呈十

字时按住鼠标左键拖至Q8.如图1O所示.

xtlz12xl3x14xlgu21z22x23x24x25z3x32x33x34z35;

1114;6l1l2l2,c

1l11i一

l1lll

i1li日

2345123451235;

,l=St~IPROD[啊『f{W2:P$乙B4:P《I)I

图l0输入原始数据及公式

第三步:线性规划求解.

～ 一

C∑

,

∑

=

Z

X

aM

(1)选择\"工具\"\"规划求解\",弹出\"规划求解

参数\"对话框,如图11所示定义各选项.

图11规划求解参数对话框设置

(2)单击\"选项\"按钮,弹出\"规划求解选项\"对

话框,选择\"采用线性模型\"和\"假定非负\"复选框

后,单击\"确定\"按钮,如图l2所示.

最长运篁时阍【):

迭代q,\'

耩度∞:

兜许谡萋遥

收敛魔∞:

国菜用线性糖基毽)

国骰定辜负@

I钞

;10o,

l00130001

5l

13.∞Ot

口宜动按比鲥缝救氆)

口显示遮代结果毽) 营计导熬搜索

固芷踊教∞,$煳辖蓉势鬯)牛幢法∞

0:涝程0中心差分《)0共瑰法

图12规划求解选项对话框

(3)求解,得到线性规划求解结果,如图13所

示.即最优方案是工厂2分配2台,工厂3分配3

台,最大盈利为2l万元.

图13最优方案求解结果

通过以上实例可以看出,利用Excel求解数学

模型,操作简单,能够使学生把大量的精力放在分析

问题和建立模型上.进一步挖掘Excel求解数学模

型的功能,能够更好地营造\"用数学\",\"做数学\"的

氛围,从而提升他们解决实际问题的信心和能力.

参考文献:

[1]包凤达,等.Excel在管理技术中的应用与拓宽[M].清华大学

出版社.2010.

[2]胡运权.运筹学习题集[M].修订版.清华大学出版社,1995.

[3]李佐锋.数学建模[M].中央广播电视大学出版社,2003.

[4]《运筹学》教材编写组,运筹学[M].3版.清华大学出版社,

2005.

[5]叶向.实用运筹学一运用Excel建模和求解[M].中国人民大学

出版社,2007.责任编辑:肖滨

(上接第124页)

本文研究算法仍然有效.

7结束语

本文针对一般支持向量机不能辨识模型参数的

问题,提出基于线性核函数SMO的模型参数方法,

并对其原理进行了详细阐述.针对SMO参数的选

择问题,将PSO算法和SMO算法相结合,采用PSO— SMO算法,并对SMO算法进行改进,以提高辨识速

度和精度.将该方法用于不加噪声,加噪声,大迟延

的ARX模型和长期预测模型的参数辨识中均取得

了较好的效果,尤其对误差累计的长期预测动态模

型,辨识精度比文献[8]中其他方法高很多,充分证

明了该算法的有效性.

参考文献:

[1]王文栋,郭伟.基于SVR的控制系统辨识建模研究[J].燃气涡

轮试验与研究.2009.22(3):33—36.

[2]tialminimaloptimization:Afastalgorithmfortraining

supportvectormachines[J].AdvancesinKernelMethods—support

VectorLearn?ing,1999,11(6):23—35.

[3]翟永杰,杨金芳,徐大平,等.应用序列最小优化算法的火电厂

协调系统的预测[J].动力工程,2005,25(6):849—854.

[4]陈金环,王冠,王东云.粒子群算法及特性的研究[J].中原工

学院,2007,18(4):13—15,71.

[5]苏高利,邓芳萍.关于支持向量回归机的模型选择[J].科技通

报,2006,22(2):154—158.

[6]徐建国.支持向量机在广义预测控制中的应用与实测分

析[D].浙江大学学位论文,2006.

[7]彭辉,沈德耀.一种改进白校正动态矩阵控制算法[J].控制理

论与应用,1998,15(6):945—948.

[8]XuewuDai,ZukhraKamalova,TimBreikin,ided

ReducedOrderModellingofaTwoShaftGasTurbineEngine[J].

ComplexSystemsandApplications?Modeling,ControlandSimula-

fions,2007,14(2):1299—1303.责任编辑:刘新影

一

129一

一一一一一