二维表查找和双线性插值算法的设计与应用  

Design and Application of Two-dimensional Table Lookup and Bilinear Interpolation Algorithm

作　　者：莫林利[1] 赵秀绍[2] 郑伟[2] 王旭[2] 

机构地区：[1]华东交通大学软件学院,江西南昌330013 [2]华东交通大学土木建筑学院,江西南昌330013

出　　处：《华东交通大学学报》2015年第5期93-98,共6页Journal of East China Jiaotong University

基　　金：国家自然科学基金项目(51208198);江西省教育厅基金项目(JXJG-13-5-15);华东交通大学校立科研基金(12RJ02)

摘　　要：二维表查找及双线性插值计算在岩土工程中应用较多,用推导公式进行计算时工作量大且容易出错。以实际工程中求地基基本承载力为例,利用线性插值的基本原理,推导双线性插值的计算公式,其计算相对复杂。利用TREND函数可以线性拟合数据组的特性,分析当参数为两组数据拟合时,拟合直线正好通过由此两组数据形成坐标点,且线性拟合的相关系数为1。因此可借助TREND函数进行线性插值计算,并设计了双线性插值TREND函数的实现算法。自动查表是利用MATCH函数查找双指标的插值区间,用INDEX函数确定合理的插值区域,配合TREND函数可以完成双指标的二维表自动查找和双线性插值。对二维表边界上的查找插值区间需要特殊处理,经特殊设计后可进行外延线性插值。论文设计并制作的计算表格方便可靠,可作为承载力查表插值的计算工具。Two-dimensional table lookup and bilinear interpolation calculation is widely applied in geotechnical engineering, but the workload is big and error-prone in derived formula calculation. It is rather complex to deduce the bilinear interpolation formula by using basic principle of linear interpolation and the basic bearing capacity in actual engineering projects. This paper uses characteristic of linear fitting data for TREND function and finds out that the coefficient is 1 when the function＇s parameters are two groups＇ data with linear fitting line just through the two points formed by the two groups of data. Accordingly, TREND function can be used for linear interpolation calculation and algorithm of bilinear interpolation is then designed. Automatic look-up table method is carried out through the application of MATCH function to obtain dual index interpolation interval, the INDEX function to determine reasonable interpolation region, and the TREND function to achieve automatic search and bilinear interpolation of two-dimensional table. Special treatment for lookup interpolation interval on the boundaries of two-dimensional table is needed, and extension of linear interpolation can be achieved by special treatment. The calculation form designed in this paper is convenient and reliable, which can be used as a tool for bearing capacity interpolation, greatly facilitating the practical engineering application.

关 键 词：Excel函数法 二维表查找 双线性插值 外延插值 

分 类 号：TP311[自动化与计算机技术—计算机软件与理论] TU411[自动化与计算机技术—计算机科学与技术]