怎么用EXCEL做线性拟合

怎么用EXCEL做线性拟合

在数据分析中，对于成对成组数据的拟合是经常遇到的，涉及到的任务有线性描述，趋势预测和残差分析等等。很多专业读者遇见此类问题时往往寻求专业软件，比如在化工中经常用到的Origin和数学中常见的MATLAB等等。它们虽很专业，但其实使用Excel就完全够用了。我们已经知道在Excel自带的数据库中已有线性拟合工具，但是它还稍显单薄，今天我们来尝试使用较为专业的拟合工具来对此类数据进行处理。
　　注：本功能需要使用Excel扩展功能，如果您的Excel尚未安装数据分析，请依次选择“工具”-“加载宏”，在安装光盘支持下加载“分析数据库”。加载成功后，可以在“工具”下拉菜单中看到“数据分析”选项

　　实例 某溶液浓度正比对应于色谱仪器中的峰面积，现欲建立不同浓度下对应峰面积的标准曲线以供测试未知样品的实际浓度。已知8组对应数据，建立标准曲线，并且对此曲线进行评价，给出残差等分析数据。

　　这是一个很典型的线性拟合问题，手工计算就是采用最小二乘法求出拟合直线的待定参数，同时可以得出R的值，也就是相关系数的大小。在Excel中，可以采用先绘图再添加趋势线的方法完成前两步的要求。

　　选择成对的数据列，将它们使用“X、Y散点图”制成散点图。

　　在数据点上单击右键，选择“添加趋势线”-“线性”，并在选项标签中要求给出公式和相关系数等，可以得到拟合的直线。

　　由图中可知，拟合的直线是y=15620x+6606.1，R2的值为0.9994。

　　因为R2 >0.99，所以这是一个线性特征非常明显的实验模型，即说明拟合直线能够以大于99.99%地解释、涵盖了实测数据，具有很好的一般性，可以作为标准工作曲线用于其他未知浓度溶液的测量。

直接用菜单的：插入|函数，里面这些功能都有，一把完成。

最小二乘法是一种数学优化技术，它通过最小化误差的平方和找到一组数据的最佳函数匹配。 用最简的方法求得一些绝对不可知的真值，而令误差平方之和为最小。 通常用于曲线拟合。很多其他的优化问题也可通过最小化能量或最大化熵用最小二乘形式表达。
　　偏最小二乘回归≈多元线性回归分析＋典型相关分析＋主成分分析
　　与传统多元线性回归模型相比，偏最小二乘回归的特点是：（1）能够在自变量存在严重多重相关性的条件下进行回归建模；（2）允许在样本点个数少于变量个数的条件下进行回归建模；（3）偏最小二乘回归在最终模型中将包含原有的所有自变量；（4）偏最小二乘回归模型更易于辨识系统信息与噪声（甚至一些非随机性的噪声）；（5）在偏最小二乘回归模型中，每一个自变量的回归系数将更容易解释。
　　在计算方差和协方差时，求和号前面的系数有两种取法：当样本点集合是随机抽取得到时，应该取1/(n-1)；如果不是随机抽取的，这个系数可取1/n。
　　多重相关性的诊断
　　1 经验式诊断方法
　　1、在自变量的简单相关系数矩阵中，有某些自变量的相关系数值较大。
　　2、回归系数的代数符号与专业知识或一般经验相反；或者，它同该自变量与y的简单相关系数符号相反。
　　3、对重要自变量的回归系数进行t检验，其结果不显著。
　　特别典型的是，当F检验能在高精度下通过，测定系数R2的值亦很大，但自变量的t检验却全都不显著，这时，多重相关性的可能性将很大。
　　4、如果增加（或删除）一个变量，或者增加（或删除）一个观测值，回归系数的估计值发生了很大的变化。
　　5、重要自变量的回归系数置信区间明显过大。
　　6、在自变量中，某一个自变量是另一部分自变量的完全或近似完全的线性组合。
　　7、对于一般的观测数据，如果样本点的个数过少，样本数据中的多重相关性是经常存在的。