《EXCEL数据拟合及图形的处理方案分析》由会员分享，可在线阅读，更多相关《EXCEL数据拟合及图形的处理方案分析（67页珍藏版）》请在金锄头文库上搜索。
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在化工实验中最常见的应用是分析产物组成时作标准曲线 示例 在萃取分离乙苯和辛烷时 要用气相色谱分析产品 乙苯 辛烷混合溶液 中乙苯的含量 首先配制好一系列已知浓度的乙苯 辛烷标准溶液 用色谱逐个分析 得到峰面积数据 与已知浓度拟合得到标准曲线 4 1 1一元线性回归 1 用函数SLOPE 和INTERCEPT SL
3、OPE 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 统计 逻辑是一逻辑值 指明是否返回附加的回归统计值 如果它是TRU
4、E或1 LINEST除给出回归系数ai和b外 还给出回归统计数组 3 LINEST中的线性回归分析 设有N个数据点 xi yi 总平方和为 3 LINEST中的线性回归分析 残差平方和SS残差反映测量值yi与按拟合函数计算值y计算的偏差 残差平方和越小 表明拟合越好 回归平方和反映在y的总平方和中由x和y的线性关系引起的y的变化 其数值越大越好 拟合好坏取决于SS回归在SS中所占比例 因此定义判定系数 3 LINEST中的线性回归分析 R2表示回归分析方程的结果反映变量间关系的程度的标志 若R20为正相关 直线的斜率为正 R 0为负相关 直线的斜率为负 R 0为不相关 x与y无线性关系 3 L
5、INEST中的线性回归分析 在实际工作中也经常用到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
6、 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输入区域一致 和 置信度 复选框 如果要强制回归直线过原点 则选中 常数为零
7、 本例不选 4 1 2使用 数据分析 线性拟合 4 在 输出选项 区有3个单选框 用来指定回归分析数据输出的位置 若选中 新工作表组 则可在当前工作簿中插入新工作表 并由新工作表的A1单元格开始粘贴计算结果 如果需要给新工作表命名 可在右侧的编辑框中键入名称 若选中 新工作簿 则可创建一个新工作簿 并在新工作簿中的新工作表中粘贴结果 若选中 输出区域 则计算结果粘贴在原工作表上 本例选中此项 要求在右边的编辑框内输入 汇总输出表 左上角单元格的位置 4 1 2使用 数据分析 线性拟合 4 1 2使用 数据分析 线性拟合 5 单击确定 给出汇总输出表 SUMMARYOUTPUT 其中包括回归统计
8、 方差分析和回归系数及统计 4 1 3线性回归统计 1 自由度df的概念在方差分析表中有3种自由度 回归平方和SS回归的自由度 df回归 m k 1残差平方和SS残差的自由度 df残差 N k总平方和SS的自由度 dfSS N 1m 自变量x的系数数目 k 线性回归参数的数目 N 实验点的数目 回归方差MS回归 SS回归 df回归残差方差MS残差 SS残差 df残差 4 1 3线性回归统计 2 回归统计的作用回归统计可决定回归系数的值及其有效性 例如用最小二乘法拟合的曲线是否有效 回归参数的精度及置信区间 回归方程拟合好坏等 残差平方和在一定程度上反映了拟合好坏 然而在检验回归线有无意义 因变
9、量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 以适当的方法计
10、算t值 与查得的临界值进行比较 若计算值大于等于临界值 则比较的两个量有显著差别 否则无显著差别 注 上述检验必须在指定的置信度范围内进行 最常用的置信度为95 若置信度太高 如99 一些有用的数据可能被排除在外 若太低 则一些无意义的数据将被作为有效数据加以考虑 4 2多元线性回归 LINEST和 数据分析 的 回归 可以对多个自变量xi的函数y a1x1 a2x2 amxm b进行线性拟合 也可以对x的多项式进行拟合 4 2 1多元线性回归示例 烃类溶质在离子液体中无限稀释活度系数的定量结构相关 QSPR 模型 两种方法 LINEST 函数和 数据分析 工具 4 2 1多元线性回归 室温离
11、子液体简介 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 Sta
12、t最大 说明它在预测 上更有效 多元线性拟合的两个或多个自变量间可能相关 即一个自变量是另外一个自变量的线性函数 如果两个自变量完全相关 则可能在单元格中出现 NUM 符号 数据分析 中有 相关系数 程序 用它可以检查自变量的相关性 4 2 2多元线性回归系数的相关性 4 2 2多元线性回归系数的相关性 由下图可见 这是一个对称矩阵 对角元素为自变量与自身的相关系数 因此均为1 非对角元素是两个不同自变量间的相关系数 如果该值接近于1时 说明两自变量间有显著相关性 4 2 3多项式拟合 有时实验数据表现为一曲线 相应的拟合函数未知 需要一种普适的函数拟合曲线 常用方法之一就是用多项式拟合 原则
13、上任何连续函数均可用多项式展开 若将变量进行变换 则多项式化为多元一次函数 4 2 3多项式拟合 可用LINEST和 回归 求多项式的参数b a1 a2 an及其回归统计 通常到三次方就有中等精度 在实际工作中 在满足拟合精度的前提下多项式的阶数要尽可能的低 对于N个数据点 用于拟合的多项式最高阶数为N 1 4 2 3多项式拟合 示例 1 己烯的分压P与其在离子液体 BMIM PF6 中的溶解度x间为非直线关系 试用一多项式经验公式表示P x的函数关系 4 3非线性回归 规划求解法 1 操作过程1 在工作表上端适当单元格内输入待定系数的初始值 可变单元格 2 输入需要拟合的实验数据 自变量x数
14、列和因变量y实验值数列 3 添加y计算值数据列 它们是用拟合函数计算所得的数值 公式中含x值和一个或数个待定系数 4 另一列填入每一数据点的残差平方 y实验值 y计算值 25 选定一单元格 在此计算残差平方和 目标单元格 6 用 规划求解 改变拟合函数的待定系数 可变单元格 使得残差平方和的值极小 4 3非线性回归 规划求解法 注意 1 规划求解 是一搜索程序 若设置的初始值接近最终值 则它能以最快和最有效的方式找到解 反之 若设置的初始值偏离最终目标值太远 规划求解 可能得不到方程的解 2 为保证 规划求解 得到的是全局性解而不是区域性解 最好用几套不同的初始值求解 3 规划求解 得到的最小
15、二乘法拟合系数可能会因起始值不同而略有差别 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
16、 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种类型的二维图形和三维图表 每种类型都有几种不同的变化 化工中最常用的是散点图 它显示数值的x y关系 x是自变量 图象中是横轴 y是因变量 图象中为纵轴 有时为了同时比较多组数据 可以有多组因变量 4 4 1XY散点图的绘制 XY散点图
