Excel插值在工程設計中的實用技巧

王錦峰，楊鑫平

(1.陜西省水利電力勘測設計研究院，西安 710001；2.中國電建集團西北勘測設計研究院有限公司,西安 710065)

文章編號：1006—2610(2015)05—0075—04

Excel插值在工程設計中的實用技巧

王錦峰1，楊鑫平2

(1.陜西省水利電力勘測設計研究院，西安 710001；2.中國電建集團西北勘測設計研究院有限公司,西安 710065)

介紹幾種Excel插值計算的方法，并對各種不同方法的優化缺點進行了分析和論述，實現使用Excel進行水利水電工程設計計算的程序化，能夠在很大程度上提高工作效率，提高計算精度。

Excel；插值；數組；趨勢線；矩陣

0 前 言

Excel是Microsoft Office系列軟件中的一個電子表格程序，功能強大且容易學習和掌握，在工程計算中得到廣泛應用。在工程設計計算過程中，經常需要通過查曲線或表格來進行參數取值，比如洪水過程線、水位庫容曲線、各種計算參數曲線等，如果這個過程僅靠人工查找輸入，或借助其他軟件計算，都不能實現Excel的計算程序化，工作效率較低。筆者在從事水利工程設計中摸索總結了幾種Excel插值方法，與Excel其他函數配合使用，使得很多設計計算工作實現程序化，而不需要依賴專用軟件。本文通過簡單的工程實例對Excel插值方法進行講述，和廣大工程設計人員分享。

1 工程實例

某重力壩壩基高程880.00 m，上游為鉛直面，壩頂中部布置一孔泄流表孔，堰頂高程950.00 m，堰寬10 m，設計定型水頭6 m，求出表孔的泄流曲線。泄量的計算公式為：

(1)

式中:m值需要根據H0/Hd查WES堰流量系數(表1)表求得，其他參數可由公式求出或為定值。設計定型水頭6 m，P1/Hd=70/6>1.33，按P1/Hd≥1.33這一列數據進行插值。

2 數組函數線性插值

線性插值的基本公式為：

(2)

(3)

Excel中的回歸分析函數Forecast，通過線性回歸擬合線返回一個預測值，當數據只有2組時，即為線性插值。

表1 WES堰流量系數m值表

通過Excel中的Match、Lookup、Index、Forecast幾個數組函數，可以實現序列的自動線性插值。比如要求得H0/Hd=0.52對應的m值，首先使用函數Lookup找出小于等于0.52的值0.5，Match函數返回在X序列中0.5的相對位置為第3行，Index函數分別返回x序列第4行對應的xi+1值0.6、Y序列第3行的yi值0.451、Y序列第4行對應的yi+1值0.464，再用Forecast函數求出x=0.52對應的y值為0.454。具體過程見表2。

表2 Excel中函數插值計算過程表

LOOKUP(C2,$A:$A)=0.5，找出小于等于0.52的值0.5；

MATCH(E2,$A:$A,0)=3，返回在X序列中0.5的相對位置為第3行；

INDEX($A:$A,D2+1)=0.6，返回X序列第4行對應的xi+1值0.6；

INDEX($B:$B,D2)=0.451，返回Y序列第3行的yi值0.451；

INDEX($B:$B,D2+1)=0.464，返回Y序列第4行對應的yi+1值0.464；

FORECAST(C2,G2:H2,E2:F2)=0.454，求出x=0.52對應的y值為0.454。

對于高堰，按P1/Hd≥1.33這一列數據進行插值為一維數組插值，但對于低堰P1/Hd<1.33的情況，則需要進行二維數組插值。比如P1/Hd=0.45，則需要先求出P1/Hd=0.45對應的Y序列，仍然通過上述方法求出，再進行一維數組插值求出任意H0/Hd對應的m值。

3 趨勢線預測

Excel中繪制散點圖(圖1)，在圖中添加趨勢線，趨勢線預測方法有線性、對數、多項式、乘冪、指數、移動平均等幾種。選用多項式預測方法，階數取6(Excel軟件中最高為6)，例如對高堰P1/Hd≥1.33這一列數據進行樣條曲線擬合，擬合的曲線方程為：

y=0.5556x6- 2.8077x5+ 5.7479x4-6.0812x3

+ 3.4159x2- 0.812x+ 0.4828

計算中可直接輸入方程求出。

圖1 6階多項式趨勢線預測散點圖

由于Excel軟件中最高階數為6，如果數據大于7組，擬合的曲線和原曲線在結點上不重合，數據組數越多，誤差就越大。要消除階數太小所產生的誤差，對于組數n的數據，就需要擬合n-1次方程，用矩陣求解的方法可以得到更高次的擬合方程。

4 矩陣擬合高次方程

用矩陣求解線性方程組：

擬合方程為：y=a0x0+a1x1+a2x2+…+an-2xn-2+an-1xn-1,矩陣表達式為：A·X=Y

若X可逆，則A=X-1·Y

對于P1/Hd≥1.33數據先列出矩陣X：

在Excel表格中列出矩陣X計算值，見表3。

表3 Excel表格中列出矩陣X計算值表

用矩陣函數MINVERSE求出X逆矩陣X-1，見表4。

MINVERSE(B17:K26)

將矩陣Y列在X逆矩陣旁邊，再用逆矩陣X-1與Y相乘便得到A。

A=MMULT(B29:K38,L29:L38)

擬合的n-1次方程為：

y=-0.384+7.876627x-34.3374x2+89.43993x3-154.615x4+188.7616x5-164.583x6+97.55291x7-34.7222x8+5.511464x9。

5 各種方法的特點

對幾種不同插值方法的結果進行比較，插值的步長取0.025，3種方法的插值結果見表5。

5.1 矩陣計算n-1次方程擬合

擬合數據在原數據結點處的擬合值與原數據一致，而且由于采用曲線插值，擬合結果最為準確。采用矩陣計算擬合方程，當原數據組數不變，改變原數據時，擬合方程的各個參數隨之改變。對于數據組數固定的計算，只要建立好模板，在以后的計算過程中就只需要改變原數據就可以自動計算結果。比如WES堰在P1/Hd取其他值時，改變表中m的值，擬合方程自動改變。但這種方法的缺點是如果數據組數變化，擬合方程的參數數量就會改變，就需要重新編寫計算表格計算擬合方程的參數。

表4 用矩陣函數MINVERSE求出X逆矩陣X-1表

表5 各種插值方法結果比較表

5.2 趨勢線預測方法

擬合數據在原數據結點處的擬合值與原數據不完全一致，擬合結果的誤差也相對較大。該方法優點是操作簡單，不需要輸入任何參數，在數據組數小于7的情況下，可以計算出較為準確擬合方程，而且改變數列中的數據，即使數據組數改變，擬合方程會自動改變。缺點是當數據組數大于7時，會有一定的誤差，方程的曲率越大，誤差越大。

5.3 數組函數線性插值

數據在原數據結點處的擬合值與原數據一致，但由于采用線性插值，會有一定的誤差。該方法優點是對整列數據進行計算，任意改變數據組數和數值，計算結果自動改變。

6 結 語

以上3種方法都有各自的優缺點，在實際工程中，如果對計算誤差要求不高，筆者建議采用數組函數線性插值的方法；如果對計算精度要求高，最好采用矩陣計算擬合高次方程的方法。趨勢線預測方法，筆者認為在數據組數不是很多的情況下可以使用。

在實際工程設計計算工作中，應用Excel軟件進行計算，采用適當的插值方法實現一維插值、二維插值，在編寫Excel表格的過程中，逐漸積累經驗，設計者能夠根據需要調整設計參數，并且能夠認識各個計算參數對設計結果的影響。設計者可以一次編寫好計算模板，以后的同類設計只需要改變表格的中參數就可以完成相關計算。

[1] DL5108—1999,混凝土重力壩設計規范[S].北京:中國電力出版社，2000.

[2] 楊開科.Excel 2003 使用詳解[M].北京:人民郵電出版社，2009.

[3] 同濟大學數學教研室.線性代數[M].北京:高等教育出版社，1999.

Practical Application of Excel Interpolation in Engineering Design

WANG Jin-feng1, YANG Xin-ping2

(1. Shaanxi Province Institute of Water Resources and Electric Power Investigation and Design, Xi'an 710001，China；2. POWERCHINA Northwest Engineering Co., Ltd., Xi'an 710065，China)

Several calculation methods by Excel interpolation are introduced as well as advantages and disadvantages of the different methods are analyzed and demonstrated. The Excel application has the design calculation of hydropower engineering programmed, improving work efficiency and calculation precision. Key words:Excel; interpolation; array; trendline; matrix

2015-04-03

王錦峰(1976- )，男，陜西省西安市人，高級工程師，從事水利水電工程設計工作.

TP391

A

10.3969/j.issn.1006-2610.2015.05.022