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

主要内容 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）吻合最好的函数系数，这一过程称 为曲线拟合。 回归分析简介 最佳拟合的判据是实验数据点与拟合曲线的偏差的 平方和最小（最小二乘法），即： 用最小二乘法求最佳拟合参数的过程称为回归分析。 回归分析简介 回归分析是一种统计技术，用以定量表达实验变量 之间的关系和相关程度。 目的之一是根据已知的体系变量间的函数关系（数 学模型）已知，回归出系数； 另一目的是获得回归参数的标准偏差和相关系数以 确定模型是否适合实验数据。 4.1 线性回归 4.1 .1 一元线性回归 将一组数据拟合成一直线最简单： y=ax+b 在化工实验中最常见的应用是分析产物组成 时作标准曲线。 示例：在萃取分离乙苯和辛烷时，要用气相 色谱分析产品（乙苯-辛烷混合溶液）中乙苯 的含量。首先配制好一系列已知浓度的乙苯- 辛烷标准溶液，用色谱逐个分析，得到峰面 积数据，与已知浓度拟合得到标准曲线。 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，即得到过原点 的直线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表示回归分析方程的结果反映变量间关系的程度 的标志，若R2 0.9，说明实验数据拟合较差。 R称为相关系数。R越大表明线性关系越好。R0为 正相关，直线的斜率为正； R0为负相关，直线的 斜率为负。R=0为不相关，x与y无线性关系。 3. LINEST中的线性回归分析 在实际工作中也经常用到F-检验： 自由度df＝N-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$12 3) 选取“标志”(注意