资源描述：
《excel在实验数据回归分析中的应用》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、Excel在实验数据回归分析中的应用高晓林(北京理工大学化工与环境学院,北京100081)摘要:介绍了Excel在数据回归中的应用方法,重点介绍了非线性模型回归的方法,无需编程即可准确快捷地得到计算结果。这种方法可用于教学和工程计算。关键词:Excel;回归;线性;非线性中图分类号:TP391113文献标识码:B文章编号:100224956(2005)05200282041前言在化工实验数据处理中,我们经常会遇到对实验数据进行回归分析的问题,即通过一定的经验关联式来描述变量之间存在的函数关系。处理这类问题,通常采用一定的算法优化模型参数,使计算值与观测值之间的残差的平方和最小,即常说的
2、最小二乘法[1]。过去要实现上述过程,需要有一定的算法和编程背景的专业人员,而且在程序维护、图形化等方面都不太方便。随着专业计算机软件的发展,一些无需任何编程即可实现过去需要成百上千行代码才能实现的功能的软件已经应运而生。作为MicrosoftOffice套件之一的Excel正是这样一款功能强大的优秀软件。MicrosoftExcel是目前最优秀的电子表格软件之一,它具有卓越的图表和数值计算功能,广泛地应用于财务、统计、决策等领域,同时它又是一个集成了许多高等数学方法、功能强大的数据处理工具。本文笔者根据自身工作经验按照线性模型回归、非线性模型回归的分类通过几个具体的实例介绍了Exce
3、l在实验数据回归分析中的应用,其中重点介绍Excel在非线性回归中的应用。本文所用软件为MicrosoftExcel2000。2数据处理2.1线性回归线性回归分析是自变量与因变量呈线性关系,线性模型的一般式为:y=α+∑βiXi(1)如果因变量只有一个,则称为一元线性回归,如果有多个因变量则称为多元线性回归。对于一元线性回归,最简单的办法是做出数据的散点图,通过添加趋势线的办法得到。其具体步骤和实例,文献[3]有详细的介绍,这里不再重复。同时也可以利用Slope收稿日期:2004207209作者简介:高晓林(1948—),男,工程师1和Intercept函数分别得到回归直线的斜率和截距
4、[2]。对于多元线性回归,可以利用linest函数[2],但需要运用数组计算,而且结果不直观,而数据分析工具中的回归分析工具则显得更具优势。数据分析工具由Excel自带的加载宏提供,需要单独安装。具体的安装即调用步骤见文献[2]。下面以二元混合溶液的粘度与组分浓度之间的二元线性函数为例,介绍利用数据分析工具回归过程。待回归二元函数为:μ=a0+a1CA+a1CB(2)如图1所示,在A至C列输入数据,数据取自文献[6]。选择“工具”—“加载宏”,在弹出的对话框中选择“分析工具库”,然后再次选择“工具”—“数据分析”,在弹出的对话框中选择“回归”,则弹出图1中右边的对话框,在Y值区域中选择
5、＄C＄2:＄C＄16,在X值区域中＄A＄2:＄B＄16,在输出选项中选择结果输出的位置,这里选择新工作表。确定后就可以得到如图2所示结果(a0=22714325,a1=0102371,a2=0140953)。从图2可以看到,回归工具给出了明确、全面的结果,可方便地进行进一步的显著性检验和方差分析。图1数据分析工具回归多元线性模型的数据输入图2数据分析工具回归多元线性模型的结果输出形式较为简单的非线性模型,如axb,1/(ax+b),可以通过简单的变量变换,变成线性形式利用前面介绍的线性模型回归的方法处理。文献[1]对多种可线性化的非线性模型的处理方法进行了介绍,对于那些不能线性化的非线
6、性模型则可以利用Excel中的规划求解工具来回归。与数据分析工具一样,规划求解工具也是由Excel自带的加载宏提供,需要单独安装[2]。下面以回归关联苯甲酸在水中的溶解度的λ-h方程[4]为例说明利用规划求解工具回归非线性模型的过程。λ-h方程形式如方程(3),式中X为苯甲酸的摩尔分数,温度的单位为kelvin,Tm为苯甲酸的熔点(39515K),λ-h为待回归参数。1+λ1-X11ln=λh(3)-XTTm如图(2)首先在A,C列输入实验数据,数据取自文献[5],在B列利用对数公式和填充柄将摄氏温度变为绝对温度,在C1至C2处填入参数λ和h的初值1和3000。在D列利用公式(3)得到
7、目前参数下的计算值,需要注意的是填入公式必须用绝对地址[2],以D2为例,它的计算公式是:=＄C＄1/(EXP(＄C＄13＄C＄23(1/B4-1/39515))-1+＄C＄1)。根据最小二乘的原理,构造如下目标函数:N1∑X2(4)F=-XexpcalNi=1这样在E列计算各个数据点的残差的平方,利用填充柄填充一直计算到E21,在E22单元格内计算目标函数值,填充公式为:=Average(E4:E21)。至此数据输入完成,从图(2)中可以看到
