LINEST函數在最小二乘法求直線擬合中的應用

摘 要： 最小二乘法求直線擬合是在大學物理實驗數據處理中常見的一種方法，在實驗數據比較多的情況下，利用最小二乘法公式求線性參數和相關系數運算量大，費時費力。提出用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.