化工计算机数据与图形处理PPT-4

第四章EXCEL数据拟合及图形处理主要内容4.1线性回归4.1.1一元线性回归4.1.2用“数据分析”线性拟合4.1.3线性回归统计4.2多元线性回归4.3非线性回归4.4Excel初步图形处理回归分析简介实验结果中，各物理量之间的关系极其重要欧美学派：重视理论推导与探索；前苏联学派：重视实验数据拟合。通常用y计算＝f(x)近似代表实验数据点（xi，yi）之间的关系。为此必须找到与数据（xi，yi）吻合最好的函数系数，这一过程称为曲线拟合。回归分析简介最佳拟合的判据是实验数据点与拟合曲线的偏差的平方和最小（最小二乘法），即：用最小二乘法求最佳拟合参数的过程称为回归分析。2expcal1()NiSSyy残差＝回归分析简介回归分析是一种统计技术，用以定量表达实验变量之间的关系和相关程度。目的之一是根据已知的体系变量间的函数关系（数学模型）已知，回归出系数；另一目的是获得回归参数的标准偏差和相关系数以确定模型是否适合实验数据。4.1线性回归4.1.1一元线性回归将一组数据拟合成一直线最简单：y=ax+b在化工实验中最常见的应用是分析产物组成时作标准曲线。示例：在萃取分离乙苯和辛烷时，要用气相色谱分析产品（乙苯-辛烷混合溶液）中乙苯的含量。首先配制好一系列已知浓度的乙苯-辛烷标准溶液，用色谱逐个分析，得到峰面积数据，与已知浓度拟合得到标准曲线。4.1.1一元线性回归1.用函数SLOPE()和INTERCEPT()SLOPE(y值数列，x值数列），返回线性回归直线的斜率aINTERCEPT(y值数列，x值数列），返回截距b4.1.1一元线性回归2.用函数LINEST()LINEST可处理的线性方程的通式为：y=ax+b或y=a1x1+a2x2+…amxm+bLINEST的语法为：LINEST(y值数列,x值数列,常数_逻辑,统计_逻辑)常数_逻辑是一逻辑值，指明是否强制使常数b为0。如果它是TRUE、1或被省略，回归参数包括截距b；如果它是FALSE或0，则拟合不包括b，即得到过原点的直线y=ax。4.1.1一元线性回归2.用函数LINEST()统计_逻辑是一逻辑值，指明是否返回附加的回归统计值。如果它是TRUE或1，LINEST除给出回归系数ai和b外，还给出回归统计数组。3.LINEST中的线性回归分析设有N个数据点（xi，yi），总平方和为：21()NiiSSyy平均SSSSSS残差回归＋21()NiiSSyy残差计算21()NiSSyy回归平均计算3.LINEST中的线性回归分析残差平方和SS残差反映测量值yi与按拟合函数计算值y计算的偏差，残差平方和越小，表明拟合越好。回归平方和反映在y的总平方和中由x和y的线性关系引起的y的变化，其数值越大越好。拟合好坏取决于SS回归在SS中所占比例，因此定义判定系数：21SSSSRSSSS回归残差3.LINEST中的线性回归分析R2表示回归分析方程的结果反映变量间关系的程度的标志，若R20.9，说明实验数据拟合较差。R称为相关系数。R越大表明线性关系越好。R0为正相关，直线的斜率为正；R0为负相关，直线的斜率为负。R=0为不相关，x与y无线性关系。3.LINEST中的线性回归分析在实际工作中也经常用到F-检验：自由度df＝N-k，一元线性回归参数k为2。F-统计用以判定设计的关系式是否有效。测量精度用y值的标准误差SE(y)来估计：SE(y)越小，根据拟合直线预测的y值越准确。22(2)1NSSRFRSS回归残差dfSE()SSyNk残差3.LINEST中的线性回归分析y是随机的，因此由实验数据得到的回归参数a、b也是随机的，即同一实验做若干次或不同人做同一实验，每次实验得到的a、b值也不相同。统计上可以用a、b的标准偏差来衡量a、b数值的波动：212121SE()()()()SE()()NiiNiiNiixbSEyNxxSEyaxx平均平均3.LINEST中的线性回归分析SE（a）、SE（b）的波动性与标准偏差SE（y）的大小有关，也与x值的波动性有关，x值越离散（也就是说x取值区间越大），SE（a）、SE（b）的值越小。此外，SE（b）还与测量点数N有关，测量次数越多，SE（b）越小。3.LINEST中的线性回归分析示例：乙苯－辛烷标准曲线的回归4.1.2使用“数据分析”线性拟合4.1.2使用“数据分析”线性拟合Excel“数据分析”步骤：以标准曲线制作为例1）打开“工具”－“数据分析”，出现“数据分析”对话框。选其中的“回归”，出现“回归”对话框。2）在“输入”区域，分别在“Y值输入区域”和“X值输入区域”内输入y数据区域的引用：$K$2:$K$12和x数据区域引用$J$2:$J$123)选取“标志”(注意与Y、X输入区域一致！)和“置信度”复选框，如果要强制回归直线过原点，则选中“常数为零”，本例不选。4.1.2使用“数据分析”线性拟合4）在“输出选项”区有3个单选框，用来指定回归分析数据输出的位置。若选中“新工作表组”，则可在当前工作簿中插入新工作表，并由新工作表的A1单元格开始粘贴计算结果。如果需要给新工作表命名，可在右侧的编辑框中键入名称。若选中“新工作簿”，则可创建一个新工作簿，并在新工作簿中的新工作表中粘贴结果。若选中“输出区域”，则计算结果粘贴在原工作表上。本例选中此项，要求在右边的编辑框内输入“汇总输出表”左上角单元格的位置。4.1.2使用“数据分析”线性拟合4.1.2使用“数据分析”线性拟合5）单击确定，给出汇总输出表(SUMMARYOUTPUT),其中包括回归统计、方差分析和回归系数及统计4.1.3线性回归统计1.自由度df的概念在方差分析表中有3种自由度：回归平方和SS回归的自由度：df回归＝m＝k-1残差平方和SS残差的自由度：df残差＝N-k总平方和SS的自由度：dfSS＝N-1m：自变量x的系数数目，k：线性回归参数的数目，N：实验点的数目。回归方差MS回归=SS回归/df回归残差方差MS残差=SS残差/df残差4.1.3线性回归统计2.回归统计的作用回归统计可决定回归系数的值及其有效性，例如用最小二乘法拟合的曲线是否有效，回归参数的精度及置信区间，回归方程拟合好坏等。残差平方和在一定程度上反映了拟合好坏。然而在检验回归线有无意义、因变量y与自变量x间是否真符合回归方程所示的函数关系、需要定量反映拟合好坏时，还要经常用到相关系数R和F-统计。4.1.3线性回归统计3相关系数R和F-统计从相关系数R检验表中查到在某一自由度df和指定置信水平α下的相关系数临界值Rdf,α，若计算得到的相关系数R大于Rdf,α，则x和y显著相关。一般R应在0.99以上。F-统计也有类似的F检验表，根据给定的置信度可以查得临界值Fα(df回归，df残差），若计算的F值大于F检验表临界值，则x和y显著相关。/df/dfSSFSS回归回归残差残差4.1.3线性回归统计4t-检验t-检验常用于比较两条回归曲线。同样，有现成的t检验表，表中列出在某一自由度df和指定置信水平α下的tdf,α。以适当的方法计算t值，与查得的临界值进行比较，若计算值大于等于临界值，则比较的两个量有显著差别，否则无显著差别。注：上述检验必须在指定的置信度范围内进行，最常用的置信度为95％。若置信度太高（如99％），一些有用的数据可能被排除在外；若太低，则一些无意义的数据将被作为有效数据加以考虑。4.2多元线性回归LINEST和“数据分析”的“回归”可以对多个自变量xi的函数y=a1x1+a2x2+…amxm+b进行线性拟合，也可以对x的多项式进行拟合。4.2.1多元线性回归示例：烃类溶质在离子液体中无限稀释活度系数的定量结构相关（QSPR）模型。两种方法：LINEST()函数和“数据分析”工具4.2.1多元线性回归室温离子液体简介4.2.1多元线性回归Linest法：先选中五行连续单元格（列数由系数的个数确定），输入＝linest(Y数列，多个X数列,1,1)，按Ctrl+Shift+Enter键即可。4.2.1多元线性回归计算Y值时注意代入参数次序与回归参数是相反的！！4.2.1多元线性回归数据分析工具：工具-数据分析-回归4.2多元线性回归012345012345lnγi∞计算值关联值预测值lnγi∞实验值ln2.69820.29061*logP0.43135*0.19536*0.32404*iEH4.2.2多元线性回归系数的相关性对烃类溶质在离子液体中无限稀释活度系数的定量结构相关（QSPR）模型，可由其t-统计看出：4.2.2多元线性回归系数的相关性多元回归系数的t-Stat均大于1，说明这些系数预测无限稀释活度系数γ的有效性。其中水合能的t-Stat最大，说明它在预测γ上更有效。多元线性拟合的两个或多个自变量间可能相关，即一个自变量是另外一个自变量的线性函数。如果两个自变量完全相关，则可能在单元格中出现#NUM!符号。“数据分析”中有“相关系数”程序，用它可以检查自变量的相关性。4.2.2多元线性回归系数的相关性4.2.2多元线性回归系数的相关性由下图可见，这是一个对称矩阵，对角元素为自变量与自身的相关系数，因此均为1。非对角元素是两个不同自变量间的相关系数。如果该值接近于1时，说明两自变量间有显著相关性。4.2.3多项式拟合有时实验数据表现为一曲线，相应的拟合函数未知，需要一种普适的函数拟合曲线。常用方法之一就是用多项式拟合。原则上任何连续函数均可用多项式展开：若将变量进行变换：则多项式化为多元一次函数：212...nnybaxaxax212,,...,nnxxxxxx1122...nnybaxaxax4.2.3多项式拟合可用LINEST和“回归”求多项式的参数b、a1、a2…an及其回归统计。通常到三次方就有中等精度。在实际工作中，在满足拟合精度的前提下多项式的阶数要尽可能的低。对于N个数据点，用于拟合的多项式最高阶数为N-14.2.3多项式拟合示例：1-己烯的分压P与其在离子液体[BMIM][PF6]中的溶解度x间为非直线关系，试用一多项式经验公式表示P～x的函数关系。0.00.10.204812Px实验值二阶拟合值三阶拟合值四阶拟合值4.3非线性回归－规划求解法1.操作过程1）在工作表上端适当单元格内输入待定系数的初始值（可变单元格）。2）输入需要拟合的实验数据：自变量x数列和因变量y实验值数列。3）添加y计算值数据列。它们是用拟合函数计算所得的数值，公式中含x值和一个或数个待定系数。4）另一列填入每一数据点的残差平方(y实验值-y计算值)25）选定一单元格，在此计算残差平方和(目标单元格)6）用“规划求解”改变拟合函数的待定系数（可变单元格），使得残差平方和的值极小。4.3非线性回归－规划求解法注意：1）“规划求解”是一搜索程序，若设置的初始值接近最终值，则它能以最快和最有效的方式找到解。反之，若设置的初始值偏离最终目标值太远，“规划求解”可能得不到方程的解。2）为保证“规划求解”得到的是全局性解而不是区域性解，最好用几套不同的初始值求解。3）“规划求解”得到的最小二乘法拟合系数可能会因起始值不同而略有差别。4.3非线性回归－规划求解法示例：1-己烯在离子液体中活度系数的Wilson模型关联:实验测得283.15K时不同x己烯下己烯在离子液体中的活度系数值γ己烯，试用Wilson模型关联实验结果，得出两个相互作用参数。122111121111212111lnln((1))(1)(1)1xxxxxxx4.3非线性回归－规划求解法规划求解过程：1）在A、B两列列出x己烯和γ己烯的实验结果，并给出两个相互作用参数Λ12和Λ21的初始值。2）在C列根据Wilson模型得出γ己烯的计算值:=-LN(A4+$D$1*(1-A4))+(1-A4)*($D$1/(A4+$D$1*(1-A4))-$D$2/(1-A4+$D$