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), 一些有用的数据可能被排除在外;若太低,则一
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- EXCEL 数据 拟合 图形 处理
链接地址:https://www.31doc.com/p-2771990.html