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

0 引言

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

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

1 基于工具EXCEL2007

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

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

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

圖1 擬合曲線

曲線擬合最基本的原理是基于最小二乘法的應(yīng)用，如何將上述曲線擬合出線性回歸方程y = kx + b，其中x 為自變量，此處為測量溫度點。y為因變量，此處為擴展不確定度。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)用非常繁瑣?，F(xiàn)從實際應(yīng)用出發(fā)，尋求更加簡單的方法自動生成線性回歸方程與曲線。

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

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

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

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

2 驗證趨勢線的正確性

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

圖2 回歸曲線與回歸方程

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

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

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

圖3 生成散點圖與趨勢線

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

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

3 結(jié)語

本文利用原始的最小二乘算法，EXCEL自帶的函數(shù)應(yīng)用，EXCEL2007中新增的曲線分析，達到了相同的效果，從底層算法開始推導(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.