利用Excel软件进行非线性拟合的非编程方法
余亮
摘 要：一种在Excel软件中进行非线性拟合的方法，并通过实例说明了该方法的有效性和实用性。
关键词：数据分析 非线性曲线拟合 非线性规划
  曲线拟合是数据分析和数据处理的重要工作之一。在利用数据对系统的物理和化学现象进行深入研究时，往往需要利用机理数学模型和试验数据拟合。另外由于机理数学模型是在一定的物理化学理论基础上建立的，所以各个参数以及不同数据的性质也是各不相同的。因此在进行数据分析的时候，不同的数据往往需要根据它在模型中的地位和特性进行特殊的处理。
  微软公司Office套件中的Excel已经成为许多场合下的数据台帐工具。作为一种标准的数据记录和管理工具，它具有大多数数据分析时所需的基本工具，包括图形和线性回归等。为了能够利用Excel进行更多的数据分析工作，一些人提出了利用其内嵌BASIC（VBA）进行
编程处理的方法。但是由于要求进行程序编制，所以并不是一般工程技术人员可以容易掌握的一种途径。
  对于非线性拟合这个特殊的问题，经过适当转换，可以将它转换为一个非线性规则问题，从而利用Excel附属的规划求解工具能很容易地进行处理。
1 拟合和规划的等价关系及Excel中的规划求解工具
  （1）拟合问题的数学表示
  不失一般性，以最小二乘法为例，拟合问题的数学表示如下：
  有数据x和y各为以列向量，假定它们具有关系y＝f（x，a，b），其中：a为已知常数共n个、b为待定参数共m个。
  定义最小二乘误差为：
  问题为：求b使得E为最小。
  （2）规划问题的数学表示目标函数：f（b0）
  限制条件：G（b0）＜0
  其中：b0为待定参数。
  不失一般性，假定需要得到目标函数的最小值。
  问题为：在满足限制条件的前提下求解目标函数的最小值以及相应的参数b0。
  （3）拟合与规划之间的等价关系
  显然，令拟合问题中的待定参数b为规划问题中的参数b0，令拟合问题中的最小二乘误差函数E为规划问题中的目标函数，令规划问题中的限制条件为空，则求解该规划问题就可以得到拟合问题的解。显然，因为拟合问题中的E函数为非线性函数，在这里需要规划问题也是一个非线性规划问题。
  （4）Excel中的规划求解工具
  当安装Office套件选择了规划求解工具时，在软件菜单的工具菜单中会出现规划求解项目，选中该项目，填写对话框以后，工具会根据对话框中的定义自动进行规划求解。
  Microsoft Excel的“规划求解”取自德克萨斯大学奥斯汀分校的Leon Lasdon和克里夫兰州立大学的Allan Waren共同开发的Generalized Reduced Gradient（GRG2）非线性最优化代码。线性和整数规划取自Frontline Systems公司的John Watson和Dan Fylstra提供的有界变量单纯形法和分支边界法。Microsoft Excel Solver程序代码是以宏的方式提供调用的。使用时不需要关心其具体的实现方法，只需要和它的对话框进行交互就可以了。
2 一个实例
  下面是液相吸附平衡式的实例。
  往DBS水溶液中投入活性炭，在等温下放置到达吸附平衡．DBS的平衡浓度C与投入活性炭的吸附量q之间的关系列于表1中。
表1　待拟合原始数据
vba编程技巧
C
1.60
4.52
6.80
8.16
11.5
12.7
18.2
29.0
38.9
57.3
q
170.7
228.1
258.0
283.7
321.3
335.4
378.6
434.6
401.3
429.0
  应用非线性最小二乘法估计下式中的参数：
q＝bC／（1＋aCβ）
  显然这个非线性函数是无法直接将它线性化的，必须进行非线性拟合求解。根据经验将初始参数指定为：a＝0．3，b＝100，β＝0．8。表2为在Excel中进行求解的数据准备情况。
表2　数据准备
因变量
自变量
因变量的估计值
误差平方
170.7
1.6
111.3480808
3522.650312
228.1
4.52
225.6794429
5.859096867
258
6.8
284.4755518
700.9548418
283.7
8.16
312.8002249
846.8230885
321.3
11.5
368.9683455
2272.271162
335.4
12.7
385.8148781
2541.659938
378.6
18.2
448.6984753
4913.796239
434.6
29
533.4322704
9767.817679
401.3
38.9
588.3664879
34993.87089
429
57.3
662.4499807
54498.8935
误差平方和->114064.5968
  表格从左到右各列分别为：因变量的原始数据、自变量的原始数据、根据参数计算的估计因变量数据、单个样本点的误差平方。将待定参数的初始值填写存储在准备用于计算的单元格区域F1：F3，并使单元格F4的数值等于由误差平方累计的误差平方和数值。
  设定规划求解对话框使目标单元为误差平方和数值对应的单元格F4，并设目标为求极小值，设定可变单元为待定参数数值对应的3个单元格（F1：F3），约束条件为空。经过计算可得对应的3个参数分别为：a＝0．654，b＝185．100，β＝0．878。此时对应的因变量估计值以及误差数据如表3。图1为数据点和拟合线的对照。
图1　数据点和拟合线对照
表3 计算结果
因变量
自变量
因变量的估计值
误差平方
170.7
1.6
148.9687391
472.2476993
228.1
4.52
241.8820534
189.9449969
258
6.8
278.5091076
420.6234954
283.7
8.16
294.4132802
114.7743716
321.3
11.5
323.4022001
4.419245341
335.4
12.7
331.5482153
14.83624515
378.6
18.2
360.1970036
338.6702774
434.6
29
395.4709483
1531.082687
401.3
38.9
416.8885563
243.003089
429
57.3
444.4917783
239.9951964
误差平方和->114064.5968
3 方法讨论和结论
  从以上实例可以看出，在求解过程中没有使用任何程序的概念。求解过程准备阶段的工作为Excel表格的公式计算，求解过程中人工操作的仅仅是对话框填充。所以本方法在使用上是非常简便的。
  由于问题的定义对使用者透明，所以使用者能很方便地根据实际要求进行修正。比如根据已知参数的物理化学意义设定参数的变化范围（利用规划问题的约束条件）；或者指定某参数为整数（利用规划求解中的整数规划或者混合规划求解器）。
  另外，由于问题的定义是直接在Excel表上构造的，所以可以方便地改变问题的构造方法，从而引入其它的拟合计算方法。比如在表格的误差列中，将原来的计算方法由误差平方更改为误差和原值的比值，则可以按照相对误差的最小重新求得参数的估计值。这种改变对于数值变化范围比较大的情况具有很实用的意义，而在求解时则只需要像书写公式一样更改表格中的计算公式，没有增加任何多余的操作。
余亮（安徽华东冶金学院计算机科学系 243002）
参考文献
［1］张治文，何磊．中文Excel 7．0 for Windows 95教程．北 京：科学出版社，1997
［2］朱中南，戴迎春．化工数据处理与实验设计．烃加工出版 社，1989
［3］邓乃洋．无约束最优化方法．北京：科学出版社，1982
［4］Jonathan，Bard．Nonlinear Parameter Estimation．New York：Academic Press，1974

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。