利用EXCEL進(jìn)行線性擬合的應(yīng)用

徐含青 / 蘇州市計量測試研究所

0 引言

在日常計量工作中，常常要處理較為繁瑣的數(shù)據(jù)。作為一線計量人員，如何不斷地提高自己的實踐與理論應(yīng)用，一直是追求的目標(biāo)。

本文針對日常工作中的數(shù)據(jù)處理與不確定度評定分析中經(jīng)常要用到的線性曲線擬合與線性方程推導(dǎo)的多種方法應(yīng)用進(jìn)行介紹，從而提高工作效率。

1 基于工具EXCEL2007

表1以一組熱電偶擴(kuò)展不確定評定數(shù)據(jù)為例說明具體應(yīng)用（基于工具EXCEL2007）。

表1 一組熱電偶擴(kuò)展不確定評定數(shù)據(jù)

選中所有數(shù)據(jù)，“插入-散點圖-帶平滑線和帶數(shù)據(jù)標(biāo)記的散點圖”，初步提取出擬合曲線，如圖1所示。

圖1 擬合曲線

曲線擬合最基本的原理是基于最小二乘法的應(yīng)用，如何將上述曲線擬合出線性回歸方程y = kx + b，其中x 為自變量，此處為測量溫度點。y為因變量，此處為擴(kuò)展不確定度。k為未知斜率，b為未知截距。根據(jù)最小二乘法原理，回歸分析式推導(dǎo)出：

利用表1數(shù)據(jù)，通過計算得出表2數(shù)據(jù)。

表2 計算所得數(shù)據(jù)

x y xy x2 x y 500 2.5 1 250 250 000 600 2.7 1 620 360 000 700 2.8 1 960 490 000 800 3.2 2 560 640 000 900 3.5 3 150 810 000 1 000 3.7 3 700 1 000 000 1 100 3.8 4 180 1 210 000 1 200 3.8 4 560 1 440 000 1 300 4.2 5 460 1 690 000 1 400 4.4 6 160 1 960 000 1 500 4.5 6 750 2 250 000 1 600 4.6 7 360 2 560 000

繼續(xù)計算出下列結(jié)果：

從而得到回歸方程：y = 0.002x + 1.305

于是，可以利用公式生成曲線從而完成擬合。

這是從最原始的數(shù)據(jù)推導(dǎo)出斜率與截距，每次這樣應(yīng)用非常繁瑣。現(xiàn)從實際應(yīng)用出發(fā)，尋求更加簡單的方法自動生成線性回歸方程與曲線。

用EXCEL中現(xiàn)有函數(shù)LINEST（）與索引函數(shù)index（）取出算得的參數(shù)：

從而直接求取得到斜率與截距。

如果每次輸入公式較為繁瑣或者對函數(shù)應(yīng)用不太熟悉的話，介紹一種最簡單的方法，能夠更加迅速簡單地得到曲線與回歸方程。

在EXCEL2007中，選中生成最初的擬合曲線，然后點擊“布局-趨勢線-其他趨勢線”選項，選擇曲線類型為“線性”，選擇“顯示公式”復(fù)選框,從而可以一步到位地生成出回歸曲線與回歸方程，結(jié)果如圖2所示?？梢娚傻幕貧w方程與一開始利用最小二乘法算得的斜率與截距一致，從而達(dá)到殊途同歸的效果。

2 驗證趨勢線的正確性

下文進(jìn)一步對直接計算得出的方程生成曲線驗證趨勢線的正確性。

圖2 回歸曲線與回歸方程

利用名稱管理器添加兩個名稱，名稱x“引用位置”為”sheet1! A4”,名稱y“引用位置”輸入宏表函數(shù)=”EVLUATE（sheet1！ E 2）”，拖拽后生成C列數(shù)據(jù)（見表3）。利用表3數(shù)據(jù)生成散點圖與趨勢線（見圖3）。

表3 生成數(shù)據(jù)

發(fā)現(xiàn)此處兩條直線雖然截距與斜率均一致，但生成的直線并不重合，仔細(xì)探究原因，是因為數(shù)據(jù)準(zhǔn)確度導(dǎo)致的誤差，雙擊趨勢線方程調(diào)整數(shù)據(jù)準(zhǔn)確度后發(fā)現(xiàn)真正的回歸方程為

圖3 生成散點圖與趨勢線

調(diào)整函數(shù)表達(dá)式，重新生成散點圖與趨勢線，如圖4所示。兩條直線重合，從而以上方程與曲線得到嚴(yán)格驗證。

圖4 重新生成散點圖與趨勢線

3 結(jié)語

本文利用原始的最小二乘算法，EXCEL自帶的函數(shù)應(yīng)用，EXCEL2007中新增的曲線分析，達(dá)到了相同的效果，從底層算法開始推導(dǎo)，通過不斷摸索，得到了更為簡便的應(yīng)用方法。

[1]全國溫度工作器具計量技術(shù)委員會. JJG141-2000 [S]. 北京：中國計量出版社，2000.

[2]全國溫度計量技術(shù)委員會. JJG351-1996[S]. 北京：中國計量出版社，1996.

[3]全國法制計量技術(shù)委員會. JJF1059-1999[S]. 北京：中國計量出版社，1999.

[4]陸健. 最小二乘法及其應(yīng)用[J]. 中國西部科技, 2007(9)： 19-21.