EXCEL数据拟合及图形处理.ppt

《EXCEL数据拟合及图形处理.ppt》由会员分享，可在线阅读，更多相关《EXCEL数据拟合及图形处理.ppt（67页珍藏版）》请在三一文库上搜索。

1、第四章 EXCEL数据拟合及图形处理,主要内容,4.1 线性回归 4.1.1一元线性回归 4.1.2 用“数据分析”线性拟合 4.1.3 线性回归统计 4.2 多元线性回归 4.3 非线性回归 4.4 Excel初步图形处理,回归分析简介,实验结果中，各物理量之间的关系极其重 要 欧美学派：重视理论推导与探索； 前苏联学派：重视实验数据拟合。 通常用y计算f(x)近似代表实验数据点（xi ，yi）之间的关系。为此必须找到与数据 （xi，yi）吻合最好的函数系数，这一过程称 为曲线拟合。,回归分析简介,最佳拟合的判据是实验数据点与拟合曲线的偏差的 平方和最小（最小二乘法），即： 用最小二乘法求最

2、佳拟合参数的过程称为回归分析。,回归分析简介,回归分析是一种统计技术，用以定量表达实验变量 之间的关系和相关程度。 目的之一是根据已知的体系变量间的函数关系（数 学模型）已知，回归出系数； 另一目的是获得回归参数的标准偏差和相关系数以 确定模型是否适合实验数据。,4.1 线性回归,4.1 .1 一元线性回归 将一组数据拟合成一直线最简单： y=ax+b 在化工实验中最常见的应用是分析产物组成 时作标准曲线。 示例：在萃取分离乙苯和辛烷时，要用气相 色谱分析产品（乙苯-辛烷混合溶液）中乙苯 的含量。首先配制好一系列已知浓度的乙苯- 辛烷标准溶液，用色谱逐个分析，得到峰面 积数据，与已知浓度拟合得

3、到标准曲线。,4.1 .1 一元线性回归,1.用函数SLOPE()和INTERCEPT() SLOPE(y值数列，x值数列），返回线性回归 直线的斜率a INTERCEPT(y值数列，x值数列），返回截距b,4.1 .1 一元线性回归,2.用函数LINEST() LINEST可处理的线性方程的通式为： y=ax+b 或 y=a1x1+a2x2+amxm+b LINEST的语法为： LINEST(y值数列,x值数列,常数_逻辑,统计_逻辑) 常数_逻辑是一逻辑值，指明是否强制使常数b为0。 如果它是TRUE、1或被省略，回归参数包括截距b； 如果它是FALSE或0，则拟合不包括b，即得到过原点

4、的直线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中的线性

5、回归分析,R2表示回归分析方程的结果反映变量间关系的程度 的标志，若R2 0为 正相关，直线的斜率为正； 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值也不相同。统计上可以

6、用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）打开“工具”“数据分析”，出现“数据分析”对话框。选其中的“回归”，出现“回归”对话框。

7、2）在“输入”区域，分别在“Y值输入区域”和“X值输入区域”内输入y数据区域的引用：$K$2: $K$12和x数据区域引用$J$2: $J$12 3) 选取“标志”(注意与Y、X输入区域一致！)和“置信度”复选框，如果要强制回归直线过原点，则选中“常数为零”，本例不选。,4.1.2 使用“数据分析”线性拟合,4）在“输出选项”区有3个单选框，用来指定回归分析数据输出的位置。 若选中“新工作表组”，则可在当前工作簿中插入新工作表，并由新工作表的A1单元格开始粘贴计算结果。如果需要给新工作表命名，可在右侧的编辑框中键入名称。 若选中“新工作簿”，则可创建一个新工作簿，并在新工作簿中的新工作表中粘贴

8、结果。 若选中“输出区域”，则计算结果粘贴在原工作表上。本例选中此项，要求在右边的编辑框内输入“汇总输出表”左上角单元格的位置。,4.1.2 使用“数据分析”线性拟合,4.1.2 使用“数据分析”线性拟合,5）单击确定，给出汇总输出表(SUMMARY OUTPUT),其中包括回归统计、方差分析和回归系数及统计,4.1.3 线性回归统计,1. 自由度df的概念 在方差分析表中有3种自由度： 回归平方和SS回归的自由度：df回归mk-1 残差平方和SS残差的自由度：df残差N-k 总平方和SS的自由度：dfSSN-1 m：自变量x的系数数目，k：线性回归参数的数目， N：实验点的数目。 回归方差M

9、S回归= SS回归/ df回归 残差方差MS残差= SS残差/ df残差,4.1.3 线性回归统计,2. 回归统计的作用 回归统计可决定回归系数的值及其有效性，例如用最 小二乘法拟合的曲线是否有效，回归参数的精度及置 信区间，回归方程拟合好坏等。 残差平方和在一定程度上反映了拟合好坏。然而在检 验回归线有无意义、因变量y与自变量x间是否真符合 回归方程所示的函数关系、需要定量反映拟合好坏时 ，还要经常用到相关系数R和F-统计。,4.1.3 线性回归统计,3 相关系数R和F-统计 从相关系数R检验表中查到在某一自由度df和指定置 信水平下的相关系数临界值Rdf,，若计算得到的 相关系数R大于Rd

10、f,， 则x和y显著相关。一般R应在 0.99以上。 F-统计也有类似的F检验表，根据给定的置信度可以 查得临界值F(df回归，df残差），若计算的F值大于F 检验表临界值，则x和y显著相关。,4.1.3 线性回归统计,4 t-检验 t-检验常用于比较两条回归曲线。同样，有现成的 t 检验表，表中列出在某一自由度df和指定置信水平 下的tdf,。以适当的方法计算t值，与查得的临界 值进行比较，若计算值大于等于临界值，则比较的 两个量有显著差别，否则无显著差别。 注：上述检验必须在指定的置信度范围内进行，最 常用的置信度为95。若置信度太高（如99）， 一些有用的数据可能被排除在外；若太低，则一

11、些 无意义的数据将被作为有效数据加以考虑。,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键即

12、可。,4.2.1 多元线性回归,计算Y值时注意代入参数次序与回归参数是相反的！,4.2.1 多元线性回归,数据分析工具：工具-数据分析-回归,4.2 多元线性回归,4.2.2 多元线性回归系数的相关性,对烃类溶质在离子液体中无限稀释活度系数的 定量结构相关（QSPR）模型，可由其t-统计看出：,4.2.2 多元线性回归系数的相关性,多元回归系数的t-Stat均大于1，说明这些系数预测 无限稀释活度系数的有效性。其中水合能的t- Stat最大，说明它在预测上更有效。 多元线性拟合的两个或多个自变量间可能相关，即 一个自变量是另外一个自变量的线性函数。如果两 个自变量完全相关，则可能在单元格中出现

13、#NUM! 符号。“数据分析”中有“相关系数”程序，用它可以 检查自变量的相关性。,4.2.2 多元线性回归系数的相关性,4.2.2 多元线性回归系数的相关性,由下图可见，这是一个对称矩阵，对角元素为自变 量与自身的相关系数，因此均为1。非对角元素是两 个不同自变量间的相关系数。如果该值接近于1时， 说明两自变量间有显著相关性。,4.2.3 多项式拟合,有时实验数据表现为一曲线，相应的拟合函数未知 ，需要一种普适的函数拟合曲线。常用方法之一就 是用多项式拟合。原则上任何连续函数均可用多项 式展开： 若将变量进行变换： 则多项式化为多元一次函数：,4.2.3 多项式拟合,可用LINEST和“回归

14、”求多项式的参数b、a1、 a2an及其回归统计。 通常到三次方就有中等精度。在实际工作中，在满 足拟合精度的前提下多项式的阶数要尽可能的低。 对于N个数据点，用于拟合的多项式最高阶数为N-1,4.2.3 多项式拟合,示例：1-己烯的分压P与其在离子液体BMIMPF6 中的溶解度x间为非直线关系，试用一多项式经验 公式表示P x的函数关系。,4.3 非线性回归规划求解法,1. 操作过程 1）在工作表上端适当单元格内输入待定系数的初始 值（可变单元格）。 2）输入需要拟合的实验数据：自变量x数列和因变 量y实验值数列。 3）添加y计算值数据列。它们是用拟合函数计算所得 的数值，公式中含x值和一个

15、或数个待定系数。 4）另一列填入每一数据点的残差平方( y实验值- y计算值)2 5）选定一单元格，在此计算残差平方和(目标单元格) 6）用“规划求解”改变拟合函数的待定系数（可变单元 格），使得残差平方和的值极小。,4.3 非线性回归规划求解法,注意： 1）“规划求解”是一搜索程序，若设置的初始值接近最 终值，则它能以最快和最有效的方式找到解。反之， 若设置的初始值偏离最终目标值太远，“规划求解”可 能得不到方程的解。 2）为保证“规划求解”得到的是全局性解而不是区域性 解，最好用几套不同的初始值求解。 3）“规划求解”得到的最小二乘法拟合系数可能会因起 始值不同而略有差别。,4.3 非线性

16、回归规划求解法,示例： 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-C

17、4)2，并在D26单元格求出残差平方和： =SUM(D4:D25)。,4.3 非线性回归规划求解法,规划求解过程： 3）打开“工具”菜单，选“规划求解”，出现规划求解对 话框。“设置目标单元格”选残差平方和单元格位置 “$D$26。根据最小二乘法，残差平方和应为极小，因 此在”等于“的三个单选框中选“最小”。“可变单元格”选相互作用参数数值所在单元格：$D$1:$D$2。 4）不要填“约束”栏。用最小二乘法进行数据拟合时，不要对解做任何约束，否则求得的残差平方和不是“全局最小”。 5）单击“求解”,4.3 非线性回归规划求解法,规划求解界面1：,4.3 非线性回归规划求解法,规划求解界面2：,

18、4.3 非线性回归规划求解法,运算结果报告：,4.3 非线性回归规划求解法,初始值选择的重要性： 上例中相互作用参数的初始值选100，100，则会给出 出错信息：,4.4 Excel初步图形处理,Excel提供了15种类型的二维图形和三维图表，每 种类型都有几种不同的变化。 化工中最常用的是散点图，它显示数值的xy关 系，x是自变量，图象中是横轴，y是因变量，图 象中为纵轴。有时为了同时比较多组数据，可以 有多组因变量。,4.4.1 XY散点图的绘制,XY散点图的基本组成：,4.4.1 XY散点图的绘制,绘制步骤1,4.4.1 XY散点图的绘制,绘制步骤2,4.4.1 XY散点图的绘制,绘制步

19、骤3,4.4.1 XY散点图的绘制,绘制步骤3,4.4.1 XY散点图的绘制,绘制步骤3,绘制步骤4,4.4.2 XY散点图的修改,1、“图表区”格式 1）图案 边框、区域 均选择“无”， 或者区域选 自动同时填 充效果选白 色。,4.4.2 XY散点图的修改,1、“图表区”格式 2）字体 去掉“自动 缩放”,4.4.2 XY散点图的修改,1、“图表区”格式 3）属性 一般不用,4.4.2 XY散点图的修改,2、“绘图区”格式 边框选自动 区域选“无”,4.4.2 XY散点图的修改,3、修改“图表标题” 单击选中，再单击进入修改。修改在Excel工具栏中进 行，也可去掉,4.4.2 XY散点图

20、的修改,4、修改“图例” 双击选中， 图案里边 框和区域 均选择无,4.4.2 XY散点图的修改,4、修改“图例” 字体设置,4.4.2 XY散点图的修改,4、修改“图例” 位置设置， 一般不在此 处设置，单 击选中后按 住鼠标左键 可任意拖拽 移动位置。,4.4.2 XY散点图的修改,5、修改“数值(X)轴标题”和“数值(Y)轴标题” 单击选中，再单击，在Excel工具栏、编辑栏修改,4.4.2 XY散点图的修改,6、修改“数值(X)轴”和“数值(Y)轴” 双击选中，在对话框内修改。,修改图案格式， 主要是根据期 刊、论文的要 求修改刻度线 类型。,4.4.2 XY散点图的修改,6、修改“数值(X)轴”和“数值(Y)轴”,修改“刻度”，主 要是更改最小值 和最大值使图形 紧凑一些,4.4.2 XY散点图的修改,6、修改“数值(X)轴”和“数值(Y)轴” 字体、数字、 对齐均可修改。 右图是修改Y轴 的数字类型。,4.4.2 XY散点图的修改,7、修改曲线 双击选中，在对话框内修改,修改“图案”，主 要是对线形和数 据标记进行修改。,