EXCEL最小二乘法拟合直线.pdf

《EXCEL最小二乘法拟合直线.pdf》由会员分享，可在线阅读，更多相关《EXCEL最小二乘法拟合直线.pdf（4页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、EXCEL 最小二乘法拟合直线 最小二乘法处理数据 直线拟合求最佳经验公式的一种数据处理方法是最小二乘法(又称作一元线性回归)，它可克服用作图法求直线公式时图线的绘制引入的误差，结果更精确，在科学实验中得到了广泛的应用。1.最小二乘法的理论基础:若两物理量 x、y 满足线性关系，并由实验等精度地测得一组实验数据 ，且假定实验误差主要出现在上，设拟合直线公式为，当所测各值与拟合直线上各估计值之间偏差的平方和最小，即 时，所得拟合公式即为最佳经验公式。2.用最小二乘法求最佳经验公式:设由实验数据求得最佳经验公式为y=a+bx，根据最小二乘法原理有:即:化为:其解为:将得出的、代入即可得最佳经验公式

2、。、的不确定度与很多因素有关，如实验数据的多少、实验数据之间的关系与直线关系的符合程度(即以下介绍的相关系数)、实验数据的分散度等等，在此不作介绍。3.直线拟合的相关系数:对任何两个变量 x、y 的一组实验数据都可按上述计算方法拟合一条直线，但必须指出只有当 x 和 y 之间存在线性关系时，拟合的直线才有意义，为此我们引入一个参量:相关系数，它定义为:，其中 表示两变量之间的函数关系与线性的符合程度，绝对值越接近于 1，x 和 y 的线性关系越好;如果接近于 0，可以认为 x 和 y 之间不存在线性关系。物理实验中 r 绝对值如能达到 0.999 以上(3 个 9 以上)就表示实验数据线性良好

3、。最小二乘法直线拟合时除给出截距 a、斜率 b 外，还要给出相关系数 r 值。4.最小二乘法的推广应用:物理实验中，有很多情况下两物理量 x、y 之间满足的是曲线方程，我们可以通过变量变换使一些特殊的曲线拟合问题转化为直线拟合的问题来求解(但应注意原来等精度的实验点变换后可能会不等精度，需要用到加权拟合)，举例如下:令 转化为直线拟合问题:则 令 转化为直线拟合问题:则 令 转化为直线拟合问题:先通过仔细画图取一点()有:两式相减化为:令 转化为直线拟合问题:则:5.a、b、r 的具体求解方法:计算器、计算机的普及使得 a、b、r 的求解简便易行，以下简单介绍几种方法:1(用有二维统计功能的计

4、算器可直接求得 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.1 32.0 40.1 47.0 54.0 60.8 67.1 73.7 R()21.34 21.91 22.58 23.15 23.73 24.27 24.80 25.32 t 1.把 t、R 数据按列对应输入 Excel 表格内，如把 t 输在第一列(A1:A8),R 输在 tt

5、第二列(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”。计算最大非线性误差:绘制散

6、点图,然后添加一元回归趋势线,显示趋势线公式.例如是:y=4.1167x+42.75 在数据区右边加辅助列 A B C 1 34=ABS(4.1167*A2+42.75-B2)2 56=ABS(4.1167*A3+42.75-B3)3 57=ABS(4.1167*A4+42.75-B4)4 69=ABS(4.1167*A5+42.75-B5)5 68=ABS(4.1167*A6+42.75-B6)6 68=ABS(4.1167*A7+42.75-B7)7 68=ABS(4.1167*A8+42.75-B8)8 70=ABS(4.1167*A9+42.75-B9)9 80=ABS(4.1167*A10+42.75-B10)=MAX(C2:C10)max 那个单元格就是最大偏差.