巧用宏在Excel中实现叠代计算

巧用宏在Ｅｘｃｅｌ中实现叠代计算

[摘要]基于Excel强大的数据自动处理和计算能力，以及直观的表达能力，在工程计算中常用来替代程序计算。但由于Excel的局限，在用叠代计算解方程时遇到难题。可以以输电线路设计中求解状态方程为例，巧妙利用宏录制解决了这个难题。

[关键词]Excel软件宏叠代解方程

Excel是Microsoft公司推出的一套很著名的电子表格软件，是用来管理和处理各种数据的，其强大的功能体现在对数据的自动处理和计算方面。Excel作为目前最常用的电子表格软件，其主要特色就是能够按预先编制的公式进行计算。Excel函数作为Excel处理数据的一个最重要手段，功能是十分强大的。Excel 提供了多方面的函数，又能提供良好的文字编排功能，便于编制需保留的计算过程，因而它在工作实践中有诸多应用，甚至替代了早期的许多工程的计算程序。但作为解方程的数学方法之一的“叠代计算”在Excel中运用就遇到麻烦，叠代计算是要将上一次的计算结果作为下一次输入，在程序中用存贮器保存上一次计算结果,但在Excel中引用上次计算结果就会出现“循环引用”错误，使我们的公式输入无法进行下去。好在Excel引入“宏”方式，它可以把一些手动计算方式用记录下来，然后实现自动计算,从而在Excel中解决了“循环引用”问题。下面以架空输电线路设计中求解不定方程为例，谈一下如何在Excel中用“宏”实现迭代计算。

一、问题提出

在架空输电线路设计中，为了求导线在某一运行状态下的应力σ需要解方程：

这是一个三次不定方程，无法用一般方程求解方法去解。早期主要采用计算尺用逼近法求解。后来有计算机以后采用叠代法在计算机中求解，但要涉及专们编程，且使用不方便。由于Excel功能强大，函数丰富，计算表现直观，很适合一般人员建模板进行工程计算，我决定用它来建立我的状态方程式求解模板，这样求解出来的数组很方便地引用到AutoCAD中绘制应力曲线。

二、数学模型

在工程计算方法中有一个例题：

三、建立Excel模板

这里我们利用已建成输电线路特性曲线计算模板中求解σ值的建模过程来说明叠代计算的建模方法，其余的模板建立都采用的常规方法建模，其它资料有介绍，这里就不赘述。见图一也象前面这样输入。

四、录制宏

在视图→工具栏→窗体上打“钩”，这时显示窗体工具栏。单击工具栏中的“控制”，在工作表中插入“控制”按钮，显示出“指定宏”窗口，点“录制”，然后“确定”，鼠标指向C43右键“复制”，指向C42右键“选择性粘贴”，选“数值”选项，然后“确定”，按下宏录制的停止按钮。宏录制完成。

打开工具→宏→Visual Basic 编辑器，双击刚才生成的模块名称，可看见代码如下：

Sub 按钮1_单击()

按钮1_单击Macro

宏由MC SYSTEM 录制，时间: 2007-2-25

Range(“C43”).Select

Selection.Copy

Range(“C42”).Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

End Sub

将中间：

Range(“C43”).Select

Selection.Copy

Range(“C42”).Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

段代码复制到记事本，在记事本中复制后修改生成其它例的代码：

Range(“C43”).Select

Selection.Copy

Range(“C42”).Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

Range(“D43”).Select

Selection.Copy

Range(“D42”).Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

……

Range(“X43”).Select

Selection.Copy

Range(“X42”).Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

这是一行的代码（为了减小篇幅，省略中间部份列的代码），然后将这组代码复制13组，修改成其它各行的代码，复制回Visual Basic代码窗，替换掉原来的代码，注意保留首尾的这些代码：

Sub 按钮1_单击()

按钮1_单击Macro

宏由MC SYSTEM 录制，时间: 2007-2-25

…

End Sub

将控制按钮的名称改为“迭代计算按钮”，至此宏命令录制即完成

五、计算示例

计算时，不断单击“迭代计算按钮”，直到输入值和计算值趋近相同时，这数值就是我们要求的解：下面是一组计算结果例表，从表中可以看出，计算结果收敛是相当快的：

右图是由计算数据生成的曲线：

六、结语

Excel功能是十分强大。提供了多方面的函数，又能提供良好的文字编排功能，我们在用Excel写工程计算稿，稿子写完，结果自然出来了，如对数据不满意，可修改原始数据，原始数据一改，结果自动改正，它生成的工程计算稿可直接打印，并且第一次形成的计算稿就是计算模板，以后相似的工程就只改原始数据了，因此替代了早期的许多工程的计算程序。但Excel没有存贮器概念，使得在一些循环计算无法进行，这里利用宏命令使这一状况得到改变，从而拓展了Excel的应用范围。

参考文献：

[1] 《工程数学.计算方法》华中工学院数学教研室、软件教研室编（人民教育出版社1978年8月）

[2] 《电力工程高压送电线路设计手册（第二版）》国家电力公司东北电力设计院（中国电力出版社1999年9月）

[3] Kathy Conrad Carlberg 著，《中文Excel2000参考大全》[美] 希望图书创作室译（北京希望电子出版社1999年12月）

注：本文中所涉及到的图表、注解、公式等内容请以PDF格式阅读原文。