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