任務二　查表

知識目標

掌握查表的兩種方法插值和曲線擬合，掌握線性內插法原理，了解其他插值原理和方法；了解曲線擬合的原理和方法。

能力目標

能用Excel軟件采用單元格驅動、ExcelVBA自定義插值函數進行插值，能用Excel軟件進行曲線擬合。

在生產和科學實驗中，經常采用表格的形式來反映實際生產數據和實驗觀測數據，如表1-7為碳酸氫鈉溶解度實驗數據表，這種用數據表格形式給出的因變量溶解度（Ct）與自變量溫度（t）之間的關系，即函數Ct=f（t），通常稱作列表函數。

列表函數通常具有以下特點：①自變量與函數值一一對應（不允許多值）；②函數值具有相當可靠的精確度；③自變量與函數間的解析表達式可能不清楚，或者函數關系的解析表達式非常復雜不便于計算。

☆思考：1.何謂插值？常用的插值方法有哪幾種？

　　2.何謂線性插值、Lagrange插值、三次樣條插值？

　　3.何謂曲線擬合？

由表1-7可知，表格中只提供了幾個溫度為整數值時所對應的溶解度數據，當我們需要某一表格中未給出溫度（如26.8℃）的溶解度數據時，該如何處理呢？

目前，處理表格函數的方法可分為兩大類：①插值；②曲線擬合。插值就是通過列表函數中若干點數據構造一個比較簡單的函數來近似表達原表格函數進行數據處理的方法，顯然選用不同的插值函數，就有不同的計算方法和計算結果。曲線擬合也稱經驗建模，通常是將表格中的全部數據作為處理對象，將其描述成數學表達式的方法。

本任務將著重介紹插值中的線性插值法和Lagrange多項式插值法及借助于Excel進行曲線擬合的方法。

一、采用常用插值方法處理表格函數

為便于寫出計算通式，將表1-7中的實驗數據抽象為表1-8中的表格函數。

（一）線性插值法

線性插值就是將表格函數中的相鄰兩點之間的函數關系視為直線關系，即通過兩點的數據構造一直線方程來處理位于兩點之間數據關系的方法，以點1、點2為例，兩點之間的因變量（y）的計算式為：

線性插值又稱兩點插值，其幾何意義見圖1-9。可見，線性插值將原函數（曲線）f（x）近似為直線p（x）處理，原函數f（x）在點Q的值yQ被現函數p（x）在點Q’的值所替代，這勢必導致誤差的存在。因此，線性插值是一種比較近似的插值處理方法，只有當原函數f（x）近似為直線或插值區間（x1，x2）比較小時才可應用，否則誤差較大。

【例1-3】　利用表1-7采用線性插值方法查溫度為26.8℃時NaHCO3的溶解度？

分析：該題在利用公式（1-2）時，公式中自變量x對應于本題中的溫度t，因變量y對應于系統中的溶解度Ct。

解：由題意可得如下數據表

由式（1-2）得：

（二）拉格朗日（Lagrange）分段插值法

下面以一元三點拉格朗日插值法為例說明分段插值法的要點。

已知n+1個數據點的數值，其順序依次為x0，x1，…，xn。插值點的數值為x，則

①x≤xi時，使用結點xi-1、xi、xi+1（i=1，2，…，n-1）；

②x>xn-1時，使用結點xn-2、xn-1、xn。

Lagrange插值計算式如下：

【例1-4】　利用Lagrange插值方法重新計算例1-3中溫度為26.8℃時溶解度值。

解：由式（1-3）得：

此結果與線性內插值不完全一致，相對而言，查表時采用拉格朗日分段插值法的精度要高于線性插值，尤其是當表格函數偏離直線關系時此法更適用。

*（三）其他插值法

前面所述的線性插值法和分段拉格朗日插值法只是眾多插值法中的兩種，工程上還存在著多種其他類型的插值法，限于篇幅以下只簡單介紹一下其他幾種常用的插值方法的名稱和定義，詳細內容可參閱有關書籍。

（1）差商與牛頓插值法

拉格朗日插值法定義具有直觀性，計算機程序也很簡明，這是它的優點。但如精度不滿足要求，需要增加插值節點時原來計算出的數據不能利用，必須重新計算。牛頓插值公式能克服這一缺點。

（2）差分與等距節點插值法

上面所介紹的插值法是采用節點任意分布的插值公式，但實際應用時經常遇到等距節點的情形，這時插值公式可進一步簡化，計算也簡單得多。這就是差分與等距節點插值法的優點。

（3）分段插值法

由于有些函數會在某些取值范圍發生突變，因此，對于處于此種類型下的插值，并不意味插值點越多精度越高，在實際進行插值計算時，通常將插值范圍分為若干段，然后在每個分段上使用低階插值（如線性插值或二次插值），這就是所謂的分段插值法。

（4）三次樣條插值

在工程上經常要求通過平面上n+1個已知點作一條連接光滑的曲線。譬如船體放樣與機翼設計均要求曲線不僅連續而且處處光滑。就高速飛機機翼的設計來說，要求盡可能采用流線型，使氣流沿機冀表面能形成平滑的流線，以減少空氣的阻力。解決此類問題，當節點很多時，構造一個高次插值多項式是不理想的，可能出現龍格（C.Runge）現象（所謂龍格現象即插值多項式在插值區間發生劇烈振蕩，出現函數不收斂的現象）。所以，在工程上進行放樣時，描圖員常用富有彈性的細長木條稱樣條，把它用壓鐵固定在樣點上，其他地方讓它自由彎曲，然后畫下長條的曲線，稱為樣條曲線。該曲線可以看成由一段一段的三次多項式曲線拼合而成，在拼接處，不僅函數自身是連續的，而且它的一階和二階導數也是連續的，這種對描圖員描出的樣條曲線進行數學模擬得出的函數叫做樣條插值函數。此插值函數需通過求解一個三對角矩陣（采用追趕法）才能求得。

☆思考：如何采用Excel進行線性插值、Lagrange插值？

二、基于Excel單元格的常用插值法

Excel具有強大的計算功能，同樣可用來處理上述插值問題。下面采用單元格驅動法以例1-3的線性插值和例1-4的拉格朗日（Lagrange）分段插值為例加以說明。

步驟1：打開Excel表，在單元格B19、C19、D19、E19、B21、D21、E21依次輸入已知各數據點和待插數據點，如圖1-10所示。

步驟2：在待插值單元格C21中輸入線性插值計算公式（1-2），即：

=B21+（D21-B21）/（D19-B19）*（C19-B19），按“Enter”鍵，得計算值10.62；

若采用Lagrange插值計算法，則在單元格C23中輸入：

=B21*（C19-D19）*（C19-E19）/（B19-D19）/（B19-E19）+D21*（C19-B19）*（C19-E19）/（D19-B19）/（D19-E19）+E21*（C19-B19）*（C19-D19）/（E19-B19）/（E19-D19）

按“Enter”鍵，得計算值10.61。

由圖1-10可知，插值計算結果與前面例題計算結果完全一致。

☆思考：如何利用ExcelVBA開發線性插值和Lagrange插值自定義函數？

三、采用Excel曲線擬合法處理表格函數

線性插值處理表格函數時需要相鄰兩點數據且要求數據密度較大或相互之間有直線關系或近似直線關系，Lagrange分段插值需相鄰三點數據，改變插值要求，常常需要重新選擇插值區間。當需同時處理若干插值點時，計算工作量相對相大。當然，通過編寫VBA自定義函數（參閱本任務技能拓展部分）也能利用插值方法實現對離散數據表連續查詢數據的功能。

若能將表格中離散的數據描述成數學表達式，則可很方便地實現連續插值。在數學上利用回歸分析可實現將離散型數據描述成函數式，這種方法也稱曲線擬合或經驗建模（常用的方法有線性與非線性最小二乘法，具體可參閱有關數學資料，本任務的知識拓展部分簡單介紹了線性二乘法的相關知識）。Excel軟件已具備了部分曲線擬合功能，擬合的表達式類型有：①線性；②指數；③對數；④多項式；⑤冪；⑥移動平均六種類型，以下通過具體例子詳細說明Excel曲線擬合過程。

【例1-5】　將表1-7的溶解度數據，用Excel擬合成函數關系Ct=f（t），并利用此函數計算溫度為26.8℃時的溶解度值。

步驟1：將表1-7中的數據按列依次輸入如圖1-11所示A27～I28的區域。

步驟2：選中表中數據區，即單元格A27～I28范圍，選擇“插入”?“散點圖”?如圖1-11。單擊“第一張散點圖”，出現如圖1-12所示的散點圖。

步驟3：選中數據點?右擊數據點，出現如圖1-13所示的右鍵菜單。

步驟4：單擊如圖1-13“添加趨勢線”，出現如圖1-14所示的“設置趨勢線格式”對話框。在“趨勢預測/回歸分析類型”中選擇“多項式”，在其后的“順序”選擇“3”，在“顯示公式”、“顯示R平方值”之前打勾，?如圖1-15所示的擬合公式，即：

y=2E-06x3+0.0004x2+0.126x+6.8595

式中“2E-06”是Excel科學記數表達形式，即2×10-6。

注意：

①在圖1-14中，Excel默認的趨勢線是“線性”，在選擇時可根據曲線形狀和“R平方值”來選擇回歸分析類型，R2值越趨近于1，所擬合的方程與數據點之間的關系越接近，利用此方程計算的結果與原數據點的誤差就越小。

②從圖1-15中可看出，Excel初次擬合的表達式中自變量、應變量分別用x、y表示，可通過修改成為t、Ct，使之與實際情況相符。

③初次表達式中，當系數較小時，Excel默認采用科學記數格式且只保留1位整數，直接應用此表達式時可能會帶來一定的誤差，此時可通過改變數據表達方式進行改善。

步驟5：選中圖1-15的表達式，右擊?選中右鍵菜單“設置趨勢線標簽格式”，如圖1-16?“設置趨勢線標簽格式”對話框，如圖1-17，選擇其中“科學記數”，在“小數位數”中輸入你想要的位數，如“5”?保留了5位小數的表達式。

由圖1-17得擬合的表達式為：

Ct=2.31664×10-6t3+3.96321×10-4t2+0.126676t+6.85955

將t=26.8℃代入上式得：

Ct=2.31664×10-6×26.83+3.96321×10-4×26.82+0.126676×26.8+6.85955

　=10.58（g/100g）

由此可見，計算結果與例1-3、例1-4十分接近。同時通過例1-3～例1-5的計算表明，即使同一數據來源處理相同的數據，當采用不同的處理方法時，其最終的結果不完全一致，具體采用何種方法，需認真考慮，正確選擇。

用擬合表達式重新計算表1-7中的實驗點的溶解度數據并列于同一表格中，如表1-9所示。

由表1-9可看出，每一點的擬合值與實驗值相對誤差均很小，最大誤差值不超過0.71％，工程上完全可用此擬合的表達式代替表1-7。

【例1-6】　如表1-10所示為一彈簧荷重與彈簧伸長之間的關系實驗數據，試用線性內插法、Lagrange分段內插法、Excel曲線擬合法求當荷重為11.24kg時彈簧長度？

解：（1）線性內插法

由式（1-2）得：

（2）Lagrange分段內插法

由式（1-3）得：

（3）Excel曲線擬合法

處理步驟同例1-5相同，具體步驟省略，其最終擬合結果如圖1-18。

根據力學知識，在彈簧的彈性限度內，符合虎克定律，彈簧伸長y與荷重x成正比，即y是x的線性函數，因此擬合時選擇“線性”。

由圖1-18得擬合的直線方程為：

y=0.62275x+30.00578

將荷重x=11.24kg代入上式得：

y=0.62275×11.24+30.00578=37.01（cm）

將擬合結果與實驗值比較，如表1-11。

可見，擬合結果與實驗值相當符合。因此，針對此題而言，方法（3）的計算結果優于方法（1）和方法（2）。

【例1-7】　某化學反應速率常數k與熱力學溫度T的實驗數據如表1-12所示，試用線性內插法、Lagrange分段內插法、Excel曲線擬合法求當T=388.5K時的反應速率？

解：（1）線性內插法

由式（1-2）得：

（2）Lagrange分段內插法

由式（1-3）得：

（3）Excel曲線擬合法

處理步驟同例1-5相同，具體步驟省略，其最終擬合結果如圖1-19。

由圖1-19得擬合的方程為：

k=2.05974×10-13e0.0667705T

將T=388.5K代入上式得：k=2.05974×10-13e0.0667705×388.5=3.7980×10-2（min-1）

由反應動力學可知，反應動力學方程通常表示為，其中反應速率常數k與溫度T的關系符合阿累尼烏斯（S.A.Arrhenius）方程，即k=k0exp［-E/（RT）］的形式，顯然T-k之間的關系不符合直線關系。

本例若要使擬合方程符合阿累尼烏斯方程，可將原數據表1-12中T-k之間的關系轉換為1/T-k之間的關系，再利用Excel進行曲線擬合，具體結果如圖1-20所示。

由圖1-20得擬合的方程為：

k=3.27786×109e-9771.68/T

將T=388.5K代入上式得：k=3.27786×109e-9771.68/388.5=3.9091×10-2（min-1）

將兩種曲線擬合結果與原實驗數據進行比較，計算結果如表1-13所示。

例1-7小結：

①由表1-13可看出，若已知自變量與因變量之間的關系，采用正確的擬合方程（1/T-k關系），所得結果與實驗值之間的誤差極小；若以1/T-k關系擬合計算k值為基準，則將其余三種方法的計算結果與之比較的誤差值列于表1-14。

②由表1-14看出，線性插值法計算的誤差較大，Lagrange分段插值的計算結果優于直接采用T-k關系擬合曲線的計算結果。

因此，若能已知原表格函數的函數類型，再與Excel曲線擬合相結合，可以得到更好的擬合效果。

四、技能拓展——ExcelVBA自定義插值函數插值

從Office97開始，微軟為所有的Office組件引入了統一的應用程序自動化語言——Visual Basic For Application（VBA），并提供了VBA的IDE（Integrated Development Environment）環境。VBA集成開發環境是進行VBA程序和代碼編寫的地方，同一版本的Office共享同一IDE。VBA代碼和Excel文件是保存在一起的，可以通過打開VBA的IDE環境進行程序設計和代碼編寫，以下以Excel2007為例介紹線性插值和Lagrange插值自定義函數LineIn、LagrangeIn的具體開發步驟。

步驟1：打開Excel?開發工具?Visual Basic，如圖1-21所示。

步驟2：選擇菜單“插入”?“模塊”，如圖1-22所示。在未插入模塊之前，“過程”是灰色的，不可用。

步驟3：選擇菜單“插入”?“過程”?“添加過程”對話框，如圖1-23。在名稱輸入框中輸入過程名：LineIn（意為線性插值，此過程名可根據函數的用途由用戶自己取名），在“類型”中選擇“函數”，“范圍”中兩者皆可選擇，此處選擇“公共的”。按“確定”按鈕，出現圖1-24的模塊1（代碼）編輯窗口。

步驟4：在函數LineIn的括號內依次輸入計算用的變量：Y1、Y2、X1、X2、X，在函數體部分輸入具體的計算公式，完整的代碼如下：

Public Function LineIn（Y2，Y1，X2，X1，X）As Double

LineIn=Y1+（Y2-Y1）/（X2-X1）*（X-X1）　　　　　'線性內插計算公式

End Function

步驟5：利用已開發的線性插值自定義函數即可在Excel中進行插值計算，如圖1-25在單元格C22中輸入：=LINEIN（D21，B21，D19，B19，C19），按“Enter”鍵，可得與圖1-10單元格C21相同的計算結果。

Lagrange自定義插值函數LagrangeIn的開發步驟與LineIn自定義函數的完全相同，其完整代碼如下：

Public Function Lagrange In（Y2，Y1，Y0，X2，X1，X0，X）AsDouble　　　'Lagrange內插函數

Dim A1，A2，A3AsDouble

A1=Y0*（X-X1）*（X-X2）/（X0-X1）/（X0-X2）

A2=Y1*（X-X0）*（X-X2）/（X1-X0）/（X1-X2）

A3=Y2*（X-X0）*（X-X1）/（X2-X0）/（X2-X1）

LagrangeIn=A1+A2+A3

End Function

上述Lagrange自定義插值函數開發完成后，在圖1-25的單元格C24中輸入：

=LagrangeIn（E21，D21，B21，E19，D19，B19，C19），即可得結果10.61。顯然，采用該法與在單元格中直接輸入計算公式相比，既簡單又可避免輸入錯誤。

注意：在函數的括號內輸入的變量不分大小寫，輸入順序也無規定，只要在使用此函數時變量的順序與此函數的變量順序一致即可。

五、知識拓展——線性最小二乘法

（一）關聯函數的選擇和線性化

實測數據關聯成數學模型的方法一般有以下幾種：

①具有一定的理論依據，可直接根據機理選擇關聯函數的形式。

如反應動力學方程通常表示為，其中反應速率常數k與溫度T的關系符合阿累尼烏斯（S.A.Arrhenius）方程，k=k0exp［-E/（RT）］的形式。此法的關鍵在于確定上述公式中k、n、k0、E等未知系數，以使模型密切逼近實測數據。這種模型稱為半經驗模型，工作要點在于參數估計。

②尚無任何理論依據，但已有一些經驗公式可選擇。

很多物性數據如熱容、密度、飽和蒸氣壓等與溫度的關系常表示為：

ф（T）=b0+b1T+b2T2+b3T3+b4lnT+b5/T

當然不一定上述公式中六個系數都很重要，有的物性也只取前三、四項即可滿足精度要求，這樣可使模型簡單化。

③沒有任何經驗可循的情況。

對于此類情況，通常只能將實驗數據畫出圖形與已知函數圖形進行比較，選擇圖形接近的函數形式作擬合模型。

不論上述哪種情況，在選定關聯函數的形式之后，就是如何根據實驗數據去確定所選關聯函數中的待定系數，最常用的方法是最小二乘法，具體可參閱有關數學專著。對于一些相對比較簡單的函數類型，通常采用線性最小二乘法，以下介紹線性二乘法原理及其應用。

☆思考：何謂最小二乘法？

一元線性模型：

Y=A+BX　　（1-4）

多元線性模型：

Y=B0+B1X1+B2X2+……　　（1-5）

對于一些非線性模型，應事先將其變換成線性形式，即線性化處理，然后再用線性最小二乘法進行關聯。

表1-15列出了化工中常用的幾種函數類型及線性化的方法。此表中所列均為單變量問題，經線性化處理后的線性模型均可統一用式（1-4）表示。

對于多變量函數關系

y=f（x1，x2，…）

若采用冪函數的乘積作為關聯函數，即將上面的函數關系寫成如下形式

可作如下線性化處理，令

Y=lny，X1=lnx1，X2=lnx2，…

B0=lna，B1=b，B2=c，…

經線性化處理后的模型即式（1-5）。

對于一元非線性化方程，如：

y=a+bx+cx2+dx3…　　（1-7）

令

Y=y，X1=x，X2=x2，…

B0=a，B1=b，B2=c，…

經線性化處理后的模型也為式（1-5）。

（二）線性最小二乘法

關聯函數的形式確定之后，如何由實驗數據比較精確地去確定關聯函數中的待定系數仍是一個重要問題，最常用的方法就是線性最小二乘法，以下先以例1-6的表1-10數據加以說明。

將表1-10數據點畫在圖1-26上，可以看出盡管荷重與伸長兩者呈直線關系，但不同的人可能所畫的直線并不嚴格在一條直線上，說明由于讀數或其他影響因素造成數據包含有隨機誤差。

根據力學上的虎克定律，彈簧伸長y應該與荷重x成正比，即y是x的線性函數，通過實驗確定比例系數（彈簧的彈性系數）。一般地直線方程模型表示為

y'=a+bx　　（1-8）

如果用直尺將圖1-26上的點連成直線，由于9個點不在一直線上，所以可以畫出多條直線。也即式（1-8）線性模型中參數a和b可以有多種取值，于是產生這樣一個問題，圖1-26眾多的連線中哪一條直線最能體現物理現象的本質呢？換句話說線性模型式（1-8）中截距a和斜率b取什么值為最佳選擇？為說明這個問題，這里引入“殘差”的概念。

設有n對實驗數據（xi，yi）（i=1，2，…，n），需要尋找一個近似函數模型y'=f（x）來擬合這一組數據。令第i點實測函數值yi與模型計算值之差為殘差，即

顯然，δi刻劃了yi與回歸模型計算值的偏離程度。如果每一個點的殘差δi=0，說明實驗數據（xi，yi）完全可用直線擬合，但出于存在實驗誤差，δi=0是不可能的。也就是說最佳的a和b應使δi的和最小。但用δi的和最小原則估計參數a和b，在應用上不很方便，所以，一般采用最小二乘法，其原理可以這樣描述：所謂最小二乘原理就是使殘差的平方和最小，即

用最小二乘原理選擇最佳擬合模型的物理意義是顯見的，即在上例中找一條直線，使它與各實測點的距離（即δi）平方加和最小，這樣得到的擬合方程就可完全替代原數據表中數據之間的關系。這種采用使擬合函數值與原函數值之間的差值即殘差平方和最小的方法來確定擬合函數的方法稱最小二乘法。

將式（1-9）代入式（1-10），可得

顯然，Q是a和b的函數。由數學分析多元函數求極值的必要條件，使Q最小的a、b必須滿足以下方程組

由式（1）得

其中

分別表示yi、xi的平均值。由式（2）可推得

由式（3）、式（4）經整理可得回歸系數b的計算公式

或

具體計算時，先由式（1-11）或式（1-12）求得b，再代入式（3）得a

由于計算a、b的公式中所有的量都可以從觀測數據得出，因此回歸直線方程

y'=a+bx

便可確定。

為了簡化公式，將上述公式中的（下同）均用∑代替。令任一數據點xi與其平均值之差稱為離差，xi的離差的平方和記為lxx，即

同樣，將yi的離差的平方和記為lyy，即

將xi的離差與yi的離差的乘積之和記為lxy，即

則式（1-12）可表示為

利用式（1-17）、式（1-13）計算表1-10中的數據，列于表1-16中。

lxx=816-（72）2/9=240

lxy=2668.58-72×314.89/9=149.46

b=lxy/lxx=149.46/240=0.62275

a=314.89/9-0.62275×72/9=30.006

故回歸方程為

y'=30.006+0.62275x

可見，與圖1-18Excel擬合的曲線表達式完全一致。

對于非線性模型作了線性化處理后的回歸方程

Y'=A+BX

需注意，此模型并非最終模型，最后需恢復線性化處理前的模型原樣。

以例1-7表1-12數據為例，由反應動力學知，反應速率常數與熱力學溫度的關系一般服從阿累尼烏斯（S.A.Arrhenius）方程，即：

k=k0exp［-E/（RT）］

由于此式為非線性函數，需進行線性化處理，兩邊取對數后得

令

Y=lnk，X=1/T，A=lnk0，B=-E/R

則

Y=A+BX

將原k-T數據組換算成X-Y數據組列于表1-17。

將相關數據代入式（1-11）得：

代入式（1-13）得

A=-18.190/5-（-9771.68）×13.073×10-3/5=21.910

因為A=lnk0，所以k0=3.27786×109

所以原關聯方程為：k=3.27786×109exp（-9771.68/T）

同樣與圖1-20由Excel擬合的完全一致。

（三）曲線擬合效果分析

1.線性相關系數與顯著性檢驗

需要指出，曲線擬合處理的是隨機變量問題，觀測值x與y不存在確定性函數關系，而只是一種相關關系。線性最小二乘法只適宜處理變量x與y具有相關的問題，但在線性最小二乘法應用過程中，并不需要限制兩個變量之間一定具有線性相關關系，就是說即使平面圖上一堆完全雜亂無章的散點，也可用此方法給它們配一條直線方程模型。顯然這樣做是毫無意義的。只有當兩個變量大致呈線性關系時才適宜用直線模型去擬合數據，因此必須給出一個數量性指標描述兩個變量線性關系的密切程度，該指標稱相關系數，通常記作r，其表達式為

圖1-27說明了r取各種不同數值時散點的分布情況。

①r=0，此時lxy=0，因此b=0，即根據最小二乘法確定的回歸直線平行于x軸，這說明y的變化與x無關，此時x與y毫無線性關系，通常這時散點分布是完全不規則的。如圖1-27中的（a）。

②0<|r|<1，這是絕大多數情形，x與y之間存在一定的線性關系。當|r|越接近于1，說明線性相關越大，也就是散點與回歸直線越靠近。當r>0時，b>0，y隨x增加而增加，稱為正相關。當r<0時，b<0，y隨x增加而減小，稱為負相關。如圖1-27中的（b）、（c）。

③|r|=1，所有數據都在回歸直線上，此時，x與y完全相關，實際上此時x，y間存在確定的線性函數關系。如圖1-27中的（d）。

利用式（1-18）對表1-10中彈簧荷重與彈簧伸長長度之間關系的回歸方程進行線性相關系數的計算。

由式（1-16）得

lyy=11110.42-（314.89）2/9=93.124

由此可見，此例變量間線性相關程度很好。

必須指出，相關系數只表示x與y的線性關系的密切程度，當r很小或為零時，并不表示x與y不存在其他關系。如圖1-27中的（e），x，y呈某種曲線關系。可以對它進行線性化處理，變換成為Y=A+BX直線方程模型，此時可以用相關系數來討論新變量X與Y之間線性相關程度，但是，新變量X與Y線性相關程度并不能直接說明原始數據x，y與非線性模型擬合效果的優劣。因此，對于非線性模型擬合的效果常用另一指標——相關指數來衡量，記作R2，Excel曲線擬合中提供的R2即相關指數，其計算式如下：

式中　yi——未經線性變換的原始數據；

y'i——非線性模型的計算值；

y——原始數據的平均值。

顯然R2<1，R2值越接近于1，擬合曲線效果越好，當R2=1時，說明yi與趨于一致，實測點完全落在擬合曲線上。

利用表1-16的數據采用式（1-19）計算相關指數R2，其計算數據列于表1-18。

其中=314.89/9=34.988

將表1-18中的數據代入式（1-19）得：

與圖1-18中由Excel擬合的結果R2=0.99949完全一致。

同樣，利用表1-17的數據采用式（1-19）也可進行R2計算，其計算數據列于表1-19。

其中=0.19645/5=3.9290×10-2

將表1-19中的數據代入式（1-19）得：

與圖1-20中由Excel擬合的結果R2=0.999998完全一致。

2.相關系數r與顯著性水平α

對于一個具體問題，只有當相關系數r的絕對值大到一定程度時方可用回歸直線來近似表示x與y之間的關系。表1-20給出了r的起碼值，它與觀測次數n及顯著性水平α有關，當|r|大于表中相應的值時，所回歸的直線才有意義。舉例來說，當n-2=3時，即用5個數據來回歸直線時，相關系數r至少為0.878，所得直線方程的置信度為95％。