excel最小二乘法拟合_LINEST函数在最小二乘法求直线拟合中的应用
时间：2019-01-06 03:26:32 来源：雅意学习网 本文已影响 人 
摘 要： 最小二乘法求直线拟合是在大学物理实验数据处理中常见的一种方法，在实验数据比较多的情况下，利用最小二乘法公式求线性参数和相关系数运算量大，费时费力。提出用Excel软件中的LINEST函数进行数据处理的方法及优点。
关键词： 最小二乘法 直线拟合 LINEST函数 应用
一、最小二乘法求直线拟合的原理
在大学物理实验中，有不少直接从实验的数据求某种物理规律的经验方程即函数关系的问题，此类问题称为方程的回归问题。方程的回归的首要问题就是确定函数形式，两个物理量x、y之间存在：y=a+bx（1）的线性关系，如用自由下落物体测量重力加速度，在气垫导轨上验证牛顿第二定律，用拉脱法测量液体表面张力系数实验中力敏传感器的定标，等等，（1）式中a、b均为常数，且只有一个变量x，此类关系也称为一元线性回归。回归的问题可以认为是用实验数据来确定方程中的待定常数，即求解参数a、b。例如实验测得的数据是x=x，x，…，x时，与之对应的y=y，y，…，y。假设x的误差可以忽略，仅y具有相互独立满足正态分布的测量误差，记作d，d，…，d。这样，把实验数据代入（1）式中，有：y=a+bx+dy=a+bx+d……y=a+bx+d（2），此方程由于未知数比方程数多，故不能直接求解，要想得到合理的a、b值，就要根据最小二乘原理，使y的残差平方和RSS=?蒡（y-（a+bx））（3）为极小值。由=0和=0，分别可得?蒡（y-（a+bx））=0（4）和?蒡（y-（a+bx））x=0（5），联立上式可得：a=（6），b=（7），进一步可得x和y的相关系数r：r==（8）。
二、LINEST函数的应用举例
拉脱法测量液体表面张力系数实验是大学物理实验中的一个经典实验。随着实验仪器的更新，传统的焦利氏称逐渐被操作简便准确度更高的FD-NST-Ⅰ型液体表面张力系数测定仪所取代，实验仪器如图1所示。
在该实验中，记下吊环即将拉断液柱前一瞬间数字电压表读数值，拉断时瞬间数字电压表读数U，便可依据公式f=（U-U）/b（9）测得液体表面张力f，（9）式中b为硅压阻力敏传感器的灵敏度。在力敏传感器上分别加各种质量的砝码，测出相应的电压输出值，结果见表1所示。
力敏传感器为测力装置，在拉力小于0.098N时，拉力和数字电压表的输出值成y=a+bx的线性关系，其中b为力敏传感器的灵敏度。得到b值的过程我们称为力敏传感器的定标。在定标过程中需要用最小二乘法拟合仪器的灵敏度b，该计算很繁琐，但根据误差理论此方法最佳，我们可利用Excel软件中的LINEST函数进行数据处理，方便简洁不易出现错误。
打开Excel软件，在A栏和B栏分别输入数字电压表的输出值和砝码对应的拉力数值，其中B栏数值的单位为N，如图2。
选C、D栏为放计算结果的区间，鼠标点击“插入”栏选择“插入函数”，弹出“插入函数”二级界面后，在“或选择类别”栏选择“统计”，在“选择函数栏”点击LINEST函数，如图3所示。
鼠标点击确定后进入如图4所示的界面，在Known_y’s栏输入A1∶A7，在Known_x’s栏输入B1∶B7，Const和Stats栏分别输入true。
按Ctrl+Shift+Enter键，便得到了最小二乘法求直线拟合后的数据，如图5所示。其中C1栏显示为斜率，即经最小二乘法拟合后的仪器的灵敏度b，b=3.015×10mV/N。C3栏为拟合的线性相关系数r=0.9994。
三、结语
通过以上的实例分析可知，在大学物理实验数据处理中，用传统方法求解一元线性回归方程的参数计算量大，容易出现错误，学生在处理数据时也易产生抵触心理。合理利用Excel软件中的LINEST函数进行数据处理，简单方便，不失为最小二乘法求直线拟合的一种好方法。
参考文献：
［１］杨述武.普通物理实验（2版）.北京：高等教育出版社，1993.3.
［2］朱鹤年.物理测量的数据处理与实验设计.北京：高等教育出版社，2003.12.
本文为全文原貌 未安装PDF浏览器用户请先下载安装 原版全文
