EXCEL数据拟合及图形的处理方案分析

第四章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）吻合最好的函数系数，这一过程称为曲线拟合。,回归分析简介,最佳拟合的判据是实验数据点与拟合曲线的偏差的平方和最小（最小二乘法），即：用最小二乘法求最佳拟合参数的过程称为回归分析。,回归分析简介,回归分析是一种统计技术，用以定量表达实验变量之间的关系和相关程度。目的之一是根据已知的体系变量间的函数关系（数学模型）已知，回归出系数；另一目的是获得回归参数的标准偏差和相关系数以确定模型是否适合实验数据。,4.1线性回归,4.1.1一元线性回归将一组数据拟合成一直线最简单：y=ax+b在化工实验中最常见的应用是分析产物组成时作标准曲线。示例：在萃取分离乙苯和辛烷时，要用气相色谱分析产品（乙苯-辛烷混合溶液）中乙苯的含量。首先配制好一系列已知浓度的乙苯-辛烷标准溶液，用色谱逐个分析，得到峰面积数据，与已知浓度拟合得到标准曲线。,4.1.1一元线性回归,1.用函数SLOPE()和INTERCEPT()SLOPE(y值数列，x值数列），返回线性回归直线的斜率aINTERCEPT(y值数列，x值数列），返回截距b,4.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），总平方和为：,3.LINEST中的线性回归分析,残差平方和SS残差反映测量值yi与按拟合函数计算值y计算的偏差，残差平方和越小，表明拟合越好。回归平方和反映在y的总平方和中由x和y的线性关系引起的y的变化，其数值越大越好。拟合好坏取决于SS回归在SS中所占比例，因此定义判定系数：,3.LINEST中的线性回归分析,R2表示回归分析方程的结果反映变量间关系的程度的标志，若R20为正相关，直线的斜率为正；R0为负相关，直线的斜率为负。R=0为不相关，x与y无线性关系。,3.LINEST中的线性回归分析,在实际工作中也经常用到F-检验：自由度dfN-k，一元线性回归参数k为2。F-统计用以判定设计的关系式是否有效。测量精度用y值的标准误差SE(y)来估计：SE(y)越小，根据拟合直线预测的y值越准确。,3.LINEST中的线性回归分析,y是随机的，因此由实验数据得到的回归参数a、b也是随机的，即同一实验做若干次或不同人做同一实验，每次实验得到的a、b值也不相同。统计上可以用a、b的标准偏差来衡量a、b数值的波动：,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回归mk-1残差平方和SS残差的自由度：df残差N-k总平方和SS的自由度：dfSSN-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显著相关。,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多元线性回归,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多项式拟合,有时实验数据表现为一曲线，相应的拟合函数未知，需要一种普适的函数拟合曲线。常用方法之一就是用多项式拟合。原则上任何连续函数均可用多项式展开：若将变量进行变换：则多项式化为多元一次函数：,4.2.3多项式拟合,可用LINEST和“回归”求多项式的参数b、a1、a2an及其回归统计。通常到三次方就有中等精度。在实际工作中，在满足拟合精度的前提下多项式的阶数要尽可能的低。对于N个数据点，用于拟合的多项式最高阶数为N-1,4.2.3多项式拟合,示例：1-己烯的分压P与其在离子液体BMIMPF6中的溶解度x间为非直线关系，试用一多项式经验公式表示Px的函数关系。,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模型关联实验结果，得出两个相互作用参数。,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$2*A4)。根据残差平方的定义，在D列得到残差平方：=(B4-C4)2，并在D26单元格求出残差平方和：=SUM(D4:D25)。,4.3非线性回归规划求解法,规划求解过程：3）打开“工具”菜单，选“规划求解”，出现规划求解对话框。“设置目标单元格”选残差平方和单元格位置“$D$26。根据最小二乘法，残差平方和应为极小，因此在”等于“的三个单选框中选“最小”。“可变单元格”选相互作用参数数值所在单元格：$D$1:$D$2。4）不要填“约束”栏。用最小二乘法进行数据拟合时，不要对解做任何约束，否则求得的残差平方和不是“全局最小”。5）单击“求解”,4.3非线性回归规划求解法,规划求解界面1：,4.3非线性回归规划求解法,规划求解界面2：,4.3非线性回归规划求解法,运算结果报告：,4.3非线性回归规划求解法,初始值选择的重要性：上例中相互作用参数的初始值选100，100，则会给出出错信息：,4.4Excel初步图形处理,Excel提供了15种类型的二维图形和三维图表，每种类型都有几种不同的变化。化工中最常用的是散点图，它显示数值的xy关系，x是自变量，图象中是横轴，y是因变量，图象中为纵轴。有时为了同时比较多组数据，可以有多组因变量。,4.4.1XY散点图的绘制,XY散点图的基本组成：,4.4.1XY散点图的绘制,绘制步骤1,4.4.1XY散点图的绘制,绘制步骤2,4.4.1XY散点图的绘制,绘制步骤3,4.4.1XY散点图的绘制,绘制步骤3,4.4.1XY散点图的绘制,绘制步骤3,绘制步骤4,4.4.2XY散点图的修改,1、“图表区”格式1）图案边框、区域均选择“无”，或者区域选自动同时填充效果选白色。,4.4.2XY散点图的修改,1、“图表区”格式2）字体去掉“自动缩放”,4.4.2XY散点图的修改,1、“图表区”格式3）属性一般不用,4.4.2XY散点图的修改,2、“绘图区”格式边框选自动区域选“无”,4.4.2XY散点图的修改,3、修改“图表标题”单击选中，再单击进入修改。修改在Excel工具栏中进行，也可去掉,4.4.2XY散点图的修改,4、修改“图例”双击选中，图案里边框和区域均选择无,4.4.2XY散点图的修改,4、修改“图例”字体设置,4.4.2XY散点图的修改,4、修改“图例”位置设置，一般不在此处设置，单击选中后按住鼠标左键可任意拖拽移动位置。,4.4.2XY散点图的修改,5、修改“数值(X)轴标题”和“数值