Excel插值在工程設(shè)計(jì)中的實(shí)用技巧

王錦峰，楊鑫平

(1.陜西省水利電力勘測(cè)設(shè)計(jì)研究院，西安 710001；2.中國(guó)電建集團(tuán)西北勘測(cè)設(shè)計(jì)研究院有限公司,西安 710065)

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

Excel插值在工程設(shè)計(jì)中的實(shí)用技巧

王錦峰1，楊鑫平2

(1.陜西省水利電力勘測(cè)設(shè)計(jì)研究院，西安 710001；2.中國(guó)電建集團(tuán)西北勘測(cè)設(shè)計(jì)研究院有限公司,西安 710065)

介紹幾種Excel插值計(jì)算的方法，并對(duì)各種不同方法的優(yōu)化缺點(diǎn)進(jìn)行了分析和論述，實(shí)現(xiàn)使用Excel進(jìn)行水利水電工程設(shè)計(jì)計(jì)算的程序化，能夠在很大程度上提高工作效率，提高計(jì)算精度。

Excel；插值；數(shù)組；趨勢(shì)線；矩陣

0 前 言

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

1 工程實(shí)例

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

(1)

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

2 數(shù)組函數(shù)線性插值

線性插值的基本公式為：

(2)

(3)

Excel中的回歸分析函數(shù)Forecast，通過(guò)線性回歸擬合線返回一個(gè)預(yù)測(cè)值，當(dāng)數(shù)據(jù)只有2組時(shí)，即為線性插值。

表1 WES堰流量系數(shù)m值表

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

表2 Excel中函數(shù)插值計(jì)算過(guò)程表

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

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

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

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

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

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

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

3 趨勢(shì)線預(yù)測(cè)

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

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

+ 3.4159x2- 0.812x+ 0.4828

計(jì)算中可直接輸入方程求出。

圖1 6階多項(xiàng)式趨勢(shì)線預(yù)測(cè)散點(diǎn)圖

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

4 矩陣擬合高次方程

用矩陣求解線性方程組：

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

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

對(duì)于P1/Hd≥1.33數(shù)據(jù)先列出矩陣X：

在Excel表格中列出矩陣X計(jì)算值，見(jiàn)表3。

表3 Excel表格中列出矩陣X計(jì)算值表

用矩陣函數(shù)MINVERSE求出X逆矩陣X-1，見(jiàn)表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 各種方法的特點(diǎn)

對(duì)幾種不同插值方法的結(jié)果進(jìn)行比較，插值的步長(zhǎng)取0.025，3種方法的插值結(jié)果見(jiàn)表5。

5.1 矩陣計(jì)算n-1次方程擬合

擬合數(shù)據(jù)在原數(shù)據(jù)結(jié)點(diǎn)處的擬合值與原數(shù)據(jù)一致，而且由于采用曲線插值，擬合結(jié)果最為準(zhǔn)確。采用矩陣計(jì)算擬合方程，當(dāng)原數(shù)據(jù)組數(shù)不變，改變?cè)瓟?shù)據(jù)時(shí)，擬合方程的各個(gè)參數(shù)隨之改變。對(duì)于數(shù)據(jù)組數(shù)固定的計(jì)算，只要建立好模板，在以后的計(jì)算過(guò)程中就只需要改變?cè)瓟?shù)據(jù)就可以自動(dòng)計(jì)算結(jié)果。比如WES堰在P1/Hd取其他值時(shí)，改變表中m的值，擬合方程自動(dòng)改變。但這種方法的缺點(diǎn)是如果數(shù)據(jù)組數(shù)變化，擬合方程的參數(shù)數(shù)量就會(huì)改變，就需要重新編寫計(jì)算表格計(jì)算擬合方程的參數(shù)。

表4 用矩陣函數(shù)MINVERSE求出X逆矩陣X-1表

表5 各種插值方法結(jié)果比較表

5.2 趨勢(shì)線預(yù)測(cè)方法

擬合數(shù)據(jù)在原數(shù)據(jù)結(jié)點(diǎn)處的擬合值與原數(shù)據(jù)不完全一致，擬合結(jié)果的誤差也相對(duì)較大。該方法優(yōu)點(diǎn)是操作簡(jiǎn)單，不需要輸入任何參數(shù)，在數(shù)據(jù)組數(shù)小于7的情況下，可以計(jì)算出較為準(zhǔn)確擬合方程，而且改變數(shù)列中的數(shù)據(jù)，即使數(shù)據(jù)組數(shù)改變，擬合方程會(huì)自動(dòng)改變。缺點(diǎn)是當(dāng)數(shù)據(jù)組數(shù)大于7時(shí)，會(huì)有一定的誤差，方程的曲率越大，誤差越大。

5.3 數(shù)組函數(shù)線性插值

數(shù)據(jù)在原數(shù)據(jù)結(jié)點(diǎn)處的擬合值與原數(shù)據(jù)一致，但由于采用線性插值，會(huì)有一定的誤差。該方法優(yōu)點(diǎn)是對(duì)整列數(shù)據(jù)進(jìn)行計(jì)算，任意改變數(shù)據(jù)組數(shù)和數(shù)值，計(jì)算結(jié)果自動(dòng)改變。

6 結(jié) 語(yǔ)

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

在實(shí)際工程設(shè)計(jì)計(jì)算工作中，應(yīng)用Excel軟件進(jìn)行計(jì)算，采用適當(dāng)?shù)牟逯捣椒▽?shí)現(xiàn)一維插值、二維插值，在編寫Excel表格的過(guò)程中，逐漸積累經(jīng)驗(yàn)，設(shè)計(jì)者能夠根據(jù)需要調(diào)整設(shè)計(jì)參數(shù)，并且能夠認(rèn)識(shí)各個(gè)計(jì)算參數(shù)對(duì)設(shè)計(jì)結(jié)果的影響。設(shè)計(jì)者可以一次編寫好計(jì)算模板，以后的同類設(shè)計(jì)只需要改變表格的中參數(shù)就可以完成相關(guān)計(jì)算。

[1] DL5108—1999,混凝土重力壩設(shè)計(jì)規(guī)范[S].北京:中國(guó)電力出版社，2000.

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

[3] 同濟(jì)大學(xué)數(shù)學(xué)教研室.線性代數(shù)[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- )，男，陜西省西安市人，高級(jí)工程師，從事水利水電工程設(shè)計(jì)工作.

TP391

A

10.3969/j.issn.1006-2610.2015.05.022