《Excel在实验数据回归分析中的应用》由会员分享，可在线阅读，更多相关《Excel在实验数据回归分析中的应用（4页珍藏版）》请在金锄头文库上搜索。
1、Excel在实验数据回归分析中的应用高晓林(北京理工大学化工与环境学院 ,北京100081 )摘摘要要 : 介绍了 Exce l在数据回归中的应用方法 ,重点介绍了非线性模型回归的方法 ,无需编程即可准确快捷地得到计算结果 。这种方法可用于教学和工程计算 。关关键词键词 : Exce l;回归 ;线性 ;非线性中中图图分分类类号号 : TP391113 文献文献标标识识码码 : B 文文章章编编号号 : 1002 24956 ( 2005) 05 200282041 前前言言在化工实验数据处理中 ,我们经常会遇到对实验数据进行回归分析的问题 ,即通过一 定的经验关联式来描述变量之间存在的函数关
2、系 。处理这类问题 ,通常采用一定的算法优化模型参数 ,使计算值与观测值之间的残差的平方和最小 , 即常说的最小二乘法 1 。过去要实现上述过程 ,需要有一定的算法和编程背景的专业人员 ,而且在程序维护 、 图形化等方面都不太方便 。随着专业计算机软件的发展 ,一些无需任何编程即可实现过去需 要成百上千行代码才能实现的功能的软件已经应运而生 。作为 M ic ro soft O ffice套件之一的 Exce l正是这样一款功能强大的优秀软件 。M ic ro soft Exce l是目前最优秀的电子表格软件之一 ,它具有卓越的图表和数值计算功 能 ,广泛地应用于财务 、 统计 、 决策等领域
3、 ,同时它又是一个集成了许多高等数学方法 、 功能强大的数据处理工具 。本文笔者根据自身工作经验按照线性模型回归 、 非线性模型回归的分类通过几个具体的实例介绍了 Exce l在实验数据回归分析中的应用 ,其中重点介 绍 Exce l在非线性回归中的应用 。本文所用软件为 M ic ro soft Exce l2000。2 数数据据处处理理2. 1 线线性性回回归归线性回归分析是自变量与因变量呈线性关系 ,线性模型的一般式为 :y = + i X i( 1 )如果因变量只有一个 ,则称为一元线性回归 ,如果有多个因变量则称为多元线性回归 。对于一元线性回归 ,最简单的办法是做出数据的散点图 ,
4、通过添加趋势线的办法得 到 。其具体步骤和实例 ,文献 3 有详细的介绍 , 这里不再重复 。同时也可以利用 Slop e收稿收稿日日期期 : 2004 207 209作者作者简简介介 : 高晓林 ( 1948 ) ,男 ,工程师 1和 In te rcep t函数分别得到回归直线的斜率和截距 2 。对于多元线性回归 ,可以利用 line st函数 2 ,但需要运用数组计算 ,而且结果不直观 ,而数据分析工具中的回归分析工具则显 得更具优势 。数据分析工具由 Exce l自带的加载宏提供 ,需要单独安装 。具体的安装即调用步骤见文献 2 。下面以二元混合溶液的粘度与组分浓度之间的二元线性函数为
5、例 , 介绍利用数据分析工具回归过程 。待回归二元函数为 : = a0 + a1 CA + a1 CB( 2 )如图 1所示 ,在 A 至 C列输入数据 , 数据取自文献 6 。选择“ 工具 ” “ 加载宏 ”,在弹出的对话框中选择“ 分析工具库 ”,然后再次选择“ 工具 ” “ 数据分析 ”, 在弹出的对 话框中选择“ 回归 ”,则弹出图 1 中右边的对话框 ,在 Y值区域中选择 C 2: C 16 , 在 X值区域中 A 2: B 16 ,在输出选项中选择结果输出的位置 ,这里选择新工作表 。确定后就可以得到如图 2所示结果 ( a0 = 22714325 , a1 = 0102371 ,
6、 a2 = 0140953 ) 。从图 2 可以看到 ,回归工具给出了明确 、 全面的结果 ,可方便地进行进一步的显著性检验和方差 分析 。图 1数据分析工具回归多元线性模型的数据输入图 2数据分析工具回归多元线性模型的结果输出形式较为简单的非线性模型 ,如 axb , 1 / ( ax + b ) ,可以通过简单的变量变换 ,变成线性形式利用前面介绍的线性模型回归的方法处理 。文献 1 对多种可线性化的非线性模型的 处理方法进行了介绍 ,对于那些不能线性化的非线性模型则可以利用 Exce l中的规划求 解工具来回归 。与数据分析工具一样 , 规划求解工具也是由 Exce l自带的加载宏提供
7、,需 要单独安装 2 。下面以回归关联苯甲酸在水中的溶解度的 - h方程 4 为例说明利用规 划求解工具回归非线性模型的过程 。 - h方程形式如方程 ( 3 ) ,式中 X 为苯甲酸的摩尔 分数 ,温度的单位为 ke lvin, Tm 为苯甲酸的熔点 ( 39515 K) , - h为待回归参数 。1 + 1 -X11ln= h( 3 )-XTTm如图 ( 2 )首先在 A , C列输入实验数据 ,数据取自文献 5 ,在 B 列利用对数公式和填 充柄将摄氏温度变为绝对温度 ,在 C1至 C2处填入参数 和 h的初值 1 和 3000。在 D 列 利用公式 ( 3 )得到目前参数下的计算值 ,
8、 需要注意的是填入公式必须用绝对地址 2 , 以D2为例 ,它的计算公式是 : = C 1 / ( EXP ( C 1 3 C 2 3 ( 1 /B 4 - 1 /39515 ) ) - 1+ C 1 ) 。根据最小二乘的原理 ,构造如下目标函数 :N1 X2( 4 )F =- Xexp ca lNi = 1 这样在 E列计算各个数据点的残差的平方 ,利用填充柄填充一直计算到 E21 ,在 E22 单元格内计算目标函数值 ,填充公式为 : = A ve rage ( E4: E21 ) 。至此数据输入完成 ,从图 ( 2 )中 可以看到目前条件下的目标函数值为 010058。图 3 苯甲酸 2
9、 水体系 - h方程回归初始数据选择“ 工具 ” “ 规划求解 ”, 调用规划求解工具 。在对话框中设定目标单元 格 为E 22 ,“ 等于 ” 项中选择最小值 ,“ 可变单元格 ” 选择 C 1: C2。当最优化过程为有约束条件时 ,可以在“ 约束 ” 栏填入约束条件 ,本例子可以不填 。点击“ 求解 ” 按钮 ,立刻就 得到图 ( 4 ) 所示 的 结果 。可以 看到 C 1: C2 中 的参 数值 已 经变 化 , 得 到 了 最 优 值10 - 6 ,在018190 和 256015383 ,优化后的目标函数值已经由原来的D 列也得到了最新的计算值 ,可以看到计算结果非常好 。0100
10、58 下降到2144 3图图 4 苯甲酸苯甲酸 2 水水体系体系 - h方程方程回回归归最最终终结结果果应用 Exce l强大的数据分析功能和优化功能 ,无需任何计算机语言编程即可完成化工数据处理中常见的各类数学模型参数回归的工作 ,使用方便灵活 ,结果准确 、 直观 ,大大 提高了工作效率 。该方法可以用于教学 、 科研和工程计算 。参考参考文文献献 : 1 江体乾 1化工数据处理 M 1北京 :化学工业出版社 , 19841 2 杜茂康 1Exce l与数据处理 M 1北京 :电子工业出版社 , 20021 3 汪永涛 ,刘 卉 ,方 芳 1利用 Exce l软件处理纯液体饱和蒸汽压数据
11、J 1安徽大学学报 , 1999 , 23 ( 1 ) : 912951 4 Dom an ska, U1, Hofm an, T1, So lub ility co rre la tion of monoca rbonxylic ac id in one2componen t so lven ts, Ind1Eng J 1Chem1P roce ss1D e s1D ev1, 1986, 25 (4) , 996210081 5 Pe isheng, M a, Q ing, X ia1, D e te rm ina tion and Co rre la tion fo r So lub ility of A rom a tic A c id in So lven ts J 1 Ch ine se J1of Chem1Eng1, 2001, 9 (1) , 39 2441 6 钟 秦 ,俞马宏 1化工数值计算 M 1北京 :化学工业出版社 , 20031
