利用Excel软件进行非线性拟合的非编程方法

(4页)

'利用Excel软件进行非线性拟合的非编程方法'
利用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 待拟合原始数据C1.604.526.808.1611.512.718.229.038.957.3q170.7228.1258.0283.7321.3335.4378.6434.6401.3429.0  应用非线性最小二乘法估计下式中的参数:q=bC/(1+aCβ)  显然这个非线性函数是无法直接将它线性化的,必须进行非线性拟合求解。根据经验将初始参数指定为:a=0.3,b=100,β=0.8。表2为在Excel中进行求解的数据准备情况。表2 数据准备因变量自变量因变量的估计值误差平方170.71.6111.34808083522.650312228.14.52225.67944295.8590968672586.8284.4755518700.9548418283.78.16312.8002249846.8230885321.311.5368.96834552272.271162335.412.7385.81487812541.659938378.618.2448.69847534913.796239434.629533.43227049767.817679401.338.9588.366487934993.8708942957.3662.449980754498.8935误差平方和->114064.5968  表格从左到右各列分别为:因变量的原始数据、自变量的原始数据、根据参数计算的估计因变量数据、单个样本点的误差平方。将待定参数的初始值填写存储在准备用于计算的单元格区域F1:F3,并使单元格F4的数值等于由误差平方累计的误差平方和数值。  设定规划求解对话框使目标单元为误差平方和数值对应的单元格F4,并设目标为求极小值,设定可变单元为待定参数数值对应的3个单元格(F1:F3),约束条件为空。经过计算可得对应的3个参数分别为:a=0.654,b=185.100,β=0.878。此时对应的因变量估计值以及误差数据如表3。图1为数据点和拟合线的对照。图1 数据点和拟合线对照表3 计算结果因变量自变量因变量的估计值误差平方170.71.6148.9687391472.2476993228.14.52241.8820534189.94499692586.8278.5091076420.6234954283.78.16294.4132802114.7743716321.311.5323.40220014.419245341335.412.7331.548215314.83624515378.618.2360.1970036338.6702774434.629395.47094831531.082687401.338.9416.8885563243.00308942957.3444.4917783239.9951964误差平方和->114064.59683 方法讨论和结论  从以上实例可以看出,在求解过程中没有使用任何程序的概念。求解过程准备阶段的工作为Excel表格的公式计算,求解过程中人工操作的仅仅是对话框填充。所以本方法在使用上是非常简便的。  由于问题的定义对使用者透明,所以使用者能很方便地根据实际要求进行修正。比如根据已知参数的物理
关 键 词:
利用 Excel 软件 进行 非线性 拟合 编程 方法
 天天文库所有资源均是用户自行上传分享,仅供网友学习交流,未经上传用户书面授权,请勿作他用。
关于本文
本文标题:利用Excel软件进行非线性拟合的非编程方法
链接地址: https://www.wenku365.com/p-44814159.html
关于我们 - 网站声明 - 网站地图 - 资源地图 - 友情链接 - 网站客服点击这里,给天天文库发消息,QQ:1290478887 - 联系我们

本站为“文档C2C交易模式”,即用户上传的文档直接卖给(下载)用户,本站只是中间服务平台,本站所有文档下载所得的收益归上传人(含作者)所有【成交的100%(原创)】。本站是网络服务平台方,若您的权利被侵害,侵权客服QQ:1290478887 欢迎举报。

1290478887@qq.com 2017-2027 https://www.wenku365.com 网站版权所有

粤ICP备19057495号 

收起
展开