利用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 待拟合原始数据
应用非线性最小二乘法估计下式中的参数：
q＝bC／（1＋aCβ）
显然这个非线性函数是无法直接将它线性化的，必须进行非线性拟合求解。根据经验将初始参数指定为：a＝0．3，b＝100，β＝0．8。表2为在Excel中进行求解的数据准备情况。
表2 数据准备
表格从左到右各列分别为：因变量的原始数据、自变量的原始数据、根据参数计算的估计因变量数据、单个样本点的误差平方。将待定参数的初始值填写存储在准备用于计算的单元格区域F1：F3，并使单元格F4的数值等于由误差平方累计的误差平方和数值。
设定规划求解对话框使目标单元为误差平方和数值对应的单元格F4，并设目标为求极小值，设定可变单元为待定参数数值对应的3个单元格（F1：F3），约束条件为空。经过计算可得对应的3个参数分别为：a＝0．654，b＝185．100，β＝0．878。此时对应的因变量估计值以及误差数据如表3。图1为数据点和拟合线的对照。
图1 数据点和拟合线对照
表3 计算结果
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
利用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 待拟合原始数据
应用非线性最小二乘法估计下式中的参数：
q＝bC／（1＋aCβ）
显然这个非线性函数是无法直接将它线性化的，必须进行非线性拟合求解。根据经验将初始参数指定为：a＝0．3，b＝100，β＝0．8。表2为在Excel中进行求解的数据准备情况。
表2 数据准备
表格从左到右各列分别为：因变量的原始数据、自变量的原始数据、根据参数计算的估计因变量数据、单个样本点的误差平方。将待定参数的初始值填写存储在准备用于计算的单元格区域F1：F3，并使单元格F4的数值等于由误差平方累计的误差平方和数值。
设定规划求解对话框使目标单元为误差平方和数值对应的单元格F4，并设目标为求极小值，设定可变单元为待定参数数值对应的3个单元格（F1：F3），约束条件为空。经过计算可得对应的3个参数分别为：a＝0．654，b＝185．100，β＝0．878。此时对应的因变量估计值以及误差数据如表3。图1为数据点和拟合线的对照。
图1 数据点和拟合线对照
表3 计算结果
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