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

4.2 多元线性回归 第三十一页，共六十八页。 4.2.2 多元线性回归系数的相关性 对烃类溶质在离子液体中无限稀释活度系数的 定量结构相关（QSPR）模型，可由其t-统计看出： 第三十二页，共六十八页。 4.2.2 多元线性回归系数的相关性 多元回归系数的t-Stat均大于1，说明这些系数预测 无限稀释活度系数γ的有效性。其中水合能的t- Stat最大，说明它在预测γ上更有效。 多元线性拟合的两个或多个自变量间可能相关，即 一个自变量是另外一个自变量的线性函数。如果两 个自变量完全相关，则可能在单元格中出现#NUM! 符号。“数据分析”中有“相关系数”程序，用它可以 检查自变量的相关性。 第三十三页，共六十八页。 4.2.2 多元线性回归系数的相关性 第三十四页，共六十八页。 4.2.2 多元线性回归系数的相关性 由下图可见，这是一个对称矩阵，对角元素为自变 量与自身的相关系数，因此均为1。非对角元素是两 个不同自变量间的相关系数。如果该值接近于1时， 说明两自变量间有显著相关性。 第三十五页，共六十八页。 4.2.3 多项式拟合 有时实验数据表现为一曲线，相应的拟合函数未知 ，需要一种普适的函数拟合曲线。常用方法之一就 是用多项式拟合。原则上任何连续函数均可用多项 式展开： 若将变量进行变换： 则多项式化为多元一次函数： 第三十六页，共六十八页。 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数列和因变 量y实验值数列。 3）添加y计算值数据列。它们是用拟合函数计算所得 的数值，公式中含x值和一个或数个待定系数。 4）另一列填入每一数据点的残差平方( y实验值- y计算值)2 5）选定一单元格，在此计算残差平方和(目标单元格) 6）用“规划求解”改变拟合函数的待定系数（可变单元 格），使得残差平方和的值极小。 第三十九页，共六十八页。 4.3 非线性回归－规划求解法 注意： 1）“规划求解”是一搜索程序，若设置的初始值接近最 终值，则它能以最快和最有效的方式找到解。反之， 若设置的初始值偏离最终目标值太远，“规划求解”可 能得不到方程的解。 2）为保证“规划求解”得到的是全局性解而不是区域性 解，最好用几套不同的初始值求解。 3）“规划求解”得到的最小二乘法拟合系数可能会因起 始值不同而略有差别。 第四十页，共六十八页。 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)^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 非