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

《EXCEL数据拟合及图形处理方案分析.ppt》由会员上传分享，免费在线阅读，更多相关内容在PPT专区-天天文库。

1、第四章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）吻合最好的函数系数，这一过程称为曲线拟合。回归分析简介最佳拟合的判据是实验数据点与拟合曲线的偏差的平方

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

3、。首先配制好一系列已知浓度的乙苯-辛烷标准溶液，用色谱逐个分析，得到峰面积数据，与已知浓度拟合得到标准曲线。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值数列,常数_逻辑,统计_逻辑

4、)常数_逻辑是一逻辑值，指明是否强制使常数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与按拟合函数计算值

5、y计算的偏差，残差平方和越小，表明拟合越好。回归平方和反映在y的总平方和中由x和y的线性关系引起的y的变化，其数值越大越好。拟合好坏取决于SS回归在SS中所占比例，因此定义判定系数：3.LINEST中的线性回归分析R2表示回归分析方程的结果反映变量间关系的程度的标志，若R2<0.9，说明实验数据拟合较差。R称为相关系数。R越大表明线性关系越好。R>0为正相关，直线的斜率为正；R<0为负相关，直线的斜率为负。R=0为不相关，x与y无线性关系。3.LINEST中的线性回归分析在实际工作中也经常用到F-检

6、验：自由度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

7、值越离散（也就是说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$

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