EXCEL最小二乘法拟合直线

最小二乘法处理数据直线拟合求最佳经验公式的一种数据处理方法是最小二乘法（又称作一元线性回归），它可克服用作图法求直线公式时图线的绘制引入的误差，结果更精确，在科学实验中得到了广泛的应用。1.最小二乘法的理论基础：若两物理量x、y满足线性关系，并由实验等精度地测得一组实验数据，且假定实验误差主要出现在上，设拟合直线公式为，当所测各值与拟合直线上各估计值之间偏差的平方和最小，即时，所得拟合公式即为最佳经验公式。2.用最小二乘法求最佳经验公式：设由实验数据求得最佳经验公式为y=a+bx，根据最小二乘法原理有：即：化为：其解为：将得出的、代入即可得最佳经验公式。、的不确定度与很多因素有关，如实验数据的多少、实验数据之间的关系与直线关系的符合程度（即以下介绍的相关系数）、实验数据的分散度等等，在此不作介绍。3.直线拟合的相关系数：对任何两个变量x、y的一组实验数据都可按上述计算方法拟合一条直线，但必须指出只有当x和y之间存在线性关系时，拟合的直线才有意义，为此我们引入一个参量：相关系数，它定义为：，其中表示两变量之间的函数关系与线性的符合程度，，绝对值越接近于1，x和y的线性关系越好；如果接近于0，可以认为x和y之间不存在线性关系。物理实验中r绝对值如能达到0.999以上(3个9以上)就表示实验数据线性良好。最小二乘法直线拟合时除给出截距a、斜率b外，还要给出相关系数r值。4.最小二乘法的推广应用：物理实验中，有很多情况下两物理量x、y之间满足的是曲线方程，我们可以通过变量变换使一些特殊的曲线拟合问题转化为直线拟合的问题来求解(但应注意原来等精度的实验点变换后可能会不等精度，需要用到加权拟合)，举例如下：令转化为直线拟合问题：则令转化为直线拟合问题：则令转化为直线拟合问题：先通过仔细画图取一点()有：两式相减化为：令转化为直线拟合问题：则：5.a、b、r的具体求解方法：计算器、计算机的普及使得a、b、r的求解简便易行，以下简单介绍几种方法：1．用有二维统计功能的计算器可直接求得a、b、r；2．用计算机程序Excel中的intercept、slope、correl函数也可直接求得a、b、r；3．可以根据实际情况自己编程求a、b、r。6.用Excel求解a、b、r设铜丝电阻随温度变化的实验数据如下，用Excel求解a、b、r求解步骤t(℃)25.132.040.147.054.060.867.173.7Rt(Ω)21.3421.9122.5823.1523.7324.2724.8025.321.把t、Rt数据按列对应输入Excel表格内，如把t输在第一列（A1:A8）,Rt输在第二列（B1:B8）2.在相邻两个空白格（如C1、D1格）内，分别输入说明和函数。如在C1中输入说明“'a=”，回车；在D1中输入函数“=intercept(B1:B8,A1:A8)”,其中A1:A8、B1:B8分别给出自变量、因变量的对应位置，回车，即显示“a=19.28848”。3．在C2、D2空白格内分别输入说明“'b=”，函数“=slope(B1:B8,A1:A8)”，回车后，显示“b=0.082021”。4.同样可在C3、D3空白格内分别输入说明“'r=”,函数“=correl(B1:B8,A1:A8)”，回车后，显示“r=0.99998”。计算最大非线性误差：绘制散点图,然后添加一元回归趋势线,显示趋势线公式.例如是:y=4.1167x+42.75在数据区右边加辅助列ABC134=ABS(4.1167*A2+42.75-B2)256=ABS(4.1167*A3+42.75-B3)357=ABS(4.1167*A4+42.75-B4)469=ABS(4.1167*A5+42.75-B5)568=ABS(4.1167*A6+42.75-B6)668=ABS(4.1167*A7+42.75-B7)768=ABS(4.1167*A8+42.75-B8)870=ABS(4.1167*A9+42.75-B9)980=ABS(4.1167*A10+42.75-B10)=MAX(C2:C10)max那个单元格就是最大偏差.