用EXCEL实现VCF值的自动查找及插值计算

2013-12-06龚家强

大众科技 2013年6期

龚家强

(广西博士海意信息科技有限公司，广西南宁 530028)

1 前言

利用EXCEL实现石油及其产品的计量报告输出以及其中的VCF值的自动查找及插值计算，在用于鉴定工作时，具有如下特点： VCF值的查找计算方便、准确、快速；程序开发过程不复杂，开发周期短，无需进行复杂的报表输出介面设计，界面简单直观，便于修改报表格式；体积小巧，免安装绿色软件，可以U盘为载体，便于携带及数据保护和备份；系统崩溃时不会丢失数据，避免了复杂的数据灾难恢复过程；使用人员无须专门培训，易于上手。而且电脑目前普遍都装有 EXCEL，当所携带的电脑或便携打印机出现软件或硬件故障时，可以迅速转换工作电脑,不用重新安装打印机驱动程序，即可利用现场的其它电脑来完成计算及计量报告输出工作，不致于延误工作时间，特别是当船需要抢潮水快速卸货，或是需要速遣等，对工作时间有一定要求的时候。

2 算法基本思想

表1 T60A原油VCF值表节选表

注意到表1为一有限二维表格，第一行是20℃下的标准密度值，第一列是温度值，其余为相应的标准密度值和温度值所交叉对应的VCF值。温度值从负20度开始线性递增，每次递增值为0.25，到150度结束。考察温度值的递增情况，注意到每一度的温度值都分为四个值域区间：xyz.00-xyz.25；xyz.25-xyz.50；xyz.50-xyz.75；xyz.75-xy(z+1).0（以 x、y和z分别代表温度值的百位数、十位数和个位数），而每一个区间的端点值即为表中值。因此，只要将取得的待查温度值与这四个值域区间的边界值相比较，就可以确定待查温度值温度值落在哪一个值域区间之内，再将这一值域区间两端的值即表中温度值查找出来，就完成了温度值的查找工作。

同时，密度值从610.0开始等值递增，递增值为2，到1090.0结束。与温度的查找算法同理，查找范围也为四个值域区间，把待查密度值所在值域区间的两个端点值查找出来。

当两个区间的端点值都找出来之后，即可将其所交叉对应的表中四个VCF值也查找出来，进行插值计算，得到所需的VCF值。这就是算法的基本思想。

3 程序语句

3.1 算法中所用到的函数主要为EXCEL自带的四个函数：ADDRESS（）、INDIRECT（）、MATCH（）、TRUNC（）

ADDRESS（）：按照给定的行号和列标，建立文本类型的单元格地址；

INDIRECT（）：返回指定单元格中包含的地址字符串所引用的单元格中的数值；

MATCH（）：返回在指定方式下与指定数值匹配的数组中元素的相应位置；

TRUNC（）：将数字的小数部分截去，返回整数。

3.2 为算法的八条主要实现语句

3.2.1 语句一

ADDRESS(IF((TRUNC(LOOK_TEMP.)+0.25)＞LOOK_TEMP.,M ATCH(TRUNC(LOOK_TEMP.),temp._value_range),IF((TRUNC(LOOK_TEMP.)+0.5)＞LOOK_TEMP.,MATCH(TRUNC(LOOK_TEMP.)+0.25,temp._value_range),IF((TRUNC(LOOK_TEMP.)+0.75)＞LOOK_TEMP.,MATCH(TRUNC(LOOK_TEMP.)+0.5,temp._value_r ange),MATCH(TRUNC(LOOK_TEMP.)+0.75,temp._value_range)))),1))。

语句实现的功能是，查找小于或等于待查温度值的表中温度值的单元格地址。当得到值所在的单元格的行号和列号之后，就可使用 ADDRESS（）函数得到表中温度值的单元格地址，然后再用INDIRECT（）函数来返回指定单元格中包含的地址字符串所引用的单元格中的数值，以进行插值计算。在这里需要说明的是，因为在EXCEL2003中ADDRESS（）函数无法直接嵌套在INDIRECT（）函数之中，所以，对于ADDRESS函数所得到的单元格地址，还需要用INDIRECT（）函数在另一个单元格中返回数值。

语句中的参数 Look_temp为现场取得的待查表温度值,在EXCEL中使用本语句时，此变量就用录入的待查找温度值所在的单元格代替，如A10等。参数TEMP._VALUE_RANGE指出录入的温度值所在的区域, 例如，若为$A$1∶$A$682，即表示数据录入在工作表中第一列的第一行到第682行之内，相应温度值为从-20度到150度。

本语句嵌套了三层IF条件语句，使用了MATCH函数的精确匹配查找功能。外层IF语句的作用是，将LOOK_TEMP.取整后，再加上 0.25，即为第一个值域区间的最大值。如果LOOK_TEMP.大于此最大值，则说明LOOK_TEMP.不在这一值域区间之中，IF语句逻辑值为假，执行嵌套的第二层IF语句，即 IF(…)。否则执行第一条语句，即：MATCH(TRUNC(LOOK_TEMP.), TEMP._VALUE_RANGE)，得出此值域区间的最小值，即VCF值表中所显示的温度值，xyz.00，在VCF值表中第几行。在这里，MATCH()函数就相当于一个循环语句，从录入温度所在列的第一行一直比较到最后一行，直到找到匹配值为止。同理，嵌套的内二层 IF语句判断LOOK_TEMP处于余下的三个值域区间的哪一个之中。

需要说明的是，语句中的最后一个数字“1”，是偏移量。如果VCF二维数据值域不录入在EXCEL工作表的第一列，而是从第N列M行开始录入的话，那么，因为本语句中MATCH（）函数找出的只是对应于录入的VCF二维数据值域的第一列的相对偏移量，而ADDRESS函数是居于绝对偏移量，即相对于整个工作表第一列的偏移量来引用参数的。所以，在这里要把MATCH（）函数得出的相对偏移量转变为绝对偏移量。具体要根据所录入的 VCF值域录入在工作表的哪一列哪一行，来确定偏移量是多少。换句话来说，即在一个大的二维工作表中，所划分出来的任意位置的一个子二维表格的第一列，相对于整个大的二维表格的第一列，偏移了多少列。

3.2.2 语句二

ADDRESS(IF(LOOK_TEMP.＞(TRUNC(LOOK_TEMP.)+0.75),M ATCH(TRUNC(LOOK_TEMP.)+1,temp._value_range),IF(LOOK_TEMP.＞(TRUNC(LOOK_TEMP.)+0.5),MATCH(TRUNC(LOOK_TEMP.)+0.75,temp._value_range),IF(LOOK_TEMP.＞(TRUNC(LOOK_TEMP.)+0.25),MATCH(TRUNC(LOOK_TEMP.)+0.5,temp._value_range),IF(LOOK_TEMP.＞(TRUNC(LOOK_TEMP.)),MATCH(TRUN C(LOOK_TEMP.)+0.25,temp._value_range),MATCH(LOOK_TEM P.,temp._value_range))))),1))。

语句实现的功能是，查找刚好大于或等于待查温度值的表中温度值的单元格地址。

3.2.3 语句三

ADDRESS(1,IF(TRUNC(LOOK_DENSITY/10)*10=LOOK_DENS ITY,MATCH(TRUNC(LOOK_DENSITY/10)*10,density_value_ra nge),IF((TRUNC(LOOK_DENSITY/10)*10+2)＞LOOK_DENSITY,M ATCH(TRUNC(LOOK_DENSITY/10)*10,density_value_range),IF((TRUNC(LOOK_DENSITY/10)*10+4)＞LOOK_DENSITY,MATCH(TRUNC(LOOK_DENSITY/10)*10+2,density_value_range),IF((TRUNC(LOOK_DENSITY/10)*10+6)＞LOOK_DENSITY,MATCH(TRU NC(LOOK_DENSITY/10)*10+4,density_value_range),IF((TR UNC(LOOK_DENSITY/10)*10+8)＞LOOK_DENSITY,MATCH(TRUNC(LOOK_DENSITY/10)*10+6,density_value_range),MATCH(TRU NC(LOOK_DENSITY/10)*10+8,density_value_range)))))))。

语句实现的功能是，查找刚好小于或等于待查密度值的表中密度值的单元格地址。在密度值查找中，对于密度值是分为五个取值区间来进行判断。LOOK_DENSITY为所待查的20摄氏度下的标准密度。

3.2.4 语句四

ADDRESS(1,IF(LOOK_DENSITY＞TRUNC(LOOK_DENSITY/10)*1 0+8,MATCH(TRUNC(LOOK_DENSITY/10)*10+10,density_value_r ange),IF(LOOK_DENSITY＞(TRUNC(LOOK_DENSITY/10)*10+6),MA TCH(TRUNC(LOOK_DENSITY/10)*10+8,density_value_range),I F(LOOK_DENSITY＞(TRUNC(LOOK_DENSITY/10)*10+4),MATCH(TRU NC(LOOK_DENSITY/10)*10+6,density_value_range),IF(LOOK_DENSITY＞(TRUNC(LOOK_DENSITY/10)*10+2),MATCH(TRUNC(LOOK_DENSITY/10)*10+4,density_value_range),IF(LOOK_DENSITY＞(TRUNC(LOOK_DENSITY/10)*10),MATCH(TRUNC(LOOK_DENSITY/10)*10+2,density_value_range),MATCH(TRUNC(LOOK_DENSITY/10)*10,density_value_range)))))))。

语句实现的功能是，查找刚好大于或等于待查密度值的表中密度值的单元格地址。

3.2.5 语句五

ADDRESS(IF((TRUNC(LOOK_TEMP.)+0.25)＞LOOK_TEMP.,M ATCH(TRUNC(LOOK_TEMP.),$A$1∶$A$682),IF((TRUNC(LOOK_T EMP.)+0.5)＞LOOK_TEMP.,MATCH(TRUNC(LOOK_TEMP.)+0.25,$A$1∶$A$682),IF((TRUNC(LOOK_TEMP.)+0.75)＞LOOK_TEMP.,M ATCH(TRUNC(LOOK_TEMP.)+0.5,$A$1∶$A$682),MATCH(TRUNC(LOOK_TEMP.)+0.75,$A$1∶$A$682)))),

IF(TRUNC(LOOK_DENSITY/10)*10=LOOK_DENSITY,

MATCH(TRUNC

(LOOK_DENSITY/10)*10,density_value_range),IF((TRUNC(LOOK_DENSITY/10)*10+2)＞LOOK_DENSITY,MATCH(TRUNC(LOOK_DENSITY/10)*10,density_value_range),IF((TRUNC(LOOK_DENSITY/10)*10+4)＞LOOK_DENSITY,MATCH(TRUNC(LOOK_DENS ITY/10)*10+2,density_value_range),IF((TRUNC(LOOK_DEN SITY/10)*10+6)＞LOOK_DENSITY,MATCH(TRUNC(LOOK_DENSITY/10)*10+4,density_value_range),IF((TRUNC(LOOK_DENSIT Y/10)*10+8)＞LOOK_DENSITY,MATCH(TRUNC(LOOK_DENSITY/10)*10+6,density_value_range),MATCH(TRUNC(LOOK_DENSITY/10)*10+8,density_value_range)))))))。

语句实现的功能是，查找刚好小于或等于待查温度值的表中值和刚好小于或等于待查的密度值的表中密度值所交叉对应的VCF值的单元格地址。语句中行号的查找语句使用的是语句一中行号的查找语句，列号的查找语句则是语句三中列号的查找语句。

3.2.6 语句六

IF(LOOK_DENSITY＞TRUNC(LOOK_DENSITY/10)*10+8,MATCH(TR UNC(LOOK_DENSITY/10)*10+10,density_value_range),IF(L OOK_DENSITY＞(TRUNC(LOOK_DENSITY/10)*10+6),MATCH(TRUN C(LOOK_DENSITY/10)*10+8,density_value_range),IF(LOOK_DENSITY＞(TRUNC(LOOK_DENSITY/10)*10+4),MATCH(TRUNC(L OOK_DENSITY/10)*10+6,density_value_range),IF(LOOK_DE NSITY＞(TRUNC(LOOK_DENSITY/10)*10+2),MATCH(TRUNC(LOOK_DENSITY/10)*10+4,density_value_range),IF(LOOK_DENSI TY＞(TRUNC(LOOK_DENSITY/10)*10),MATCH(TRUNC(LOOK_DENS ITY/10)*10+2,density_value_range),MATCH(TRUNC(LOOK_D ENSITY/10)*10,density_value_range)))))))。

语句实现的功能是，查找刚好小于或等于待查温度值的表中温度值和刚好大于或等于待查密度值的表中密度值所交叉对应的VCF值的单元格地址。

3.2.7 语句七

IF(TRUNC(LOOK_DENSITY/10)*10=LOOK_DENSITY,MATCH(TRUN C(LOOK_DENSITY/10)*10,density_value_range),IF((TRUNC(LOOK_DENSITY/10)*10+2)＞LOOK_DENSITY,MATCH(TRUNC(LOO K_DENSITY/10)*10,density_value_range),IF((TRUNC(LOOK_DENSITY/10)*10+4)＞LOOK_DENSITY,MATCH(TRUNC(LOOK_DEN SITY/10)*10+2,density_value_range),IF((TRUNC(LOOK_DE NSITY/10)*10+6)＞LOOK_DENSITY,MATCH(TRUNC(LOOK_DENSIT Y/10)*10+4,density_value_range),IF((TRUNC(LOOK_DENSI TY/10)*10+8)＞LOOK_DENSITY,MATCH(TRUNC(LOOK_DENSITY/1 0)*10+6,density_value_range),MATCH(TRUNC(LOOK_DENSIT Y/10)*10+8,density_value_range)))))))。

语句实现的功能是，查找刚好大于或等于待查温度值的表中温度值和刚好小于或等于待查密度值的表中密度值所交叉对应的VCF值的单元格地址。

3.2.8 语句八

语句实现的功能是，查找刚好大于或等于待查温度值的表中温度值和刚好大于或等于待查密度值的表中密度值所交叉对应的VCF值的单元格地址。

3.3 查找结果说明

设测量的的温度为 37.3度，20摄氏度下标准密度为0.881，则语句一查找到的表中值为37.25，语句2查找到的值为37.50，语句三查找到的值为0.880，语句四查找到的值为0.882，语句五查找到的值为37.25和0.880所对应的VCF值0.9863.同理，语句六、七、八查到的VCF值分别为0.9864、0.9861、0.9862。得到所有的相关值后，即可进行插值计算，得到所需的VCF值。

又设测量的温度值和所给定的密度值在表中都可以直接查到，如当温度值为37.5，密度为0.880时，则语句一、二查到的值为37.5，语句三、四查到的值为0.880，语句五、六、七、八查到的值都为0.9863，也可求得所需要的值。

3.2仅是为了具体地说明查找过程，语句还可以进一步简化。请读者思考。

4 《油品计量员读本》查找算法

对于《油品计量员读本》中，温度取较接近值不插值，而密度值及其所对应的VCF值进行插值计算，可以用以下两语句，来实现较接近待查温度值的表中温度值与表中两个用于插值计算的密度值所交叉对应的VCF值的查找：

4.1 语句一

ADDRESS(IF(LOOK_TEMP＞(TRUNC(LOOK_TEMP)+(0.75+0.1 25)),MATCH(TRUNC(LOOK_TEMP)+1,temp._value_range),IF(LOOK_TEMP＞(TRUNC(LOOK_TEMP)+(0.5+0.125)),MATCH(TRUNC(LOOK_TEMP)+0.75,temp._value_range),IF(LOOK_TEMP＞(TR UNC(LOOK_TEMP)+(0.25+0.125)),MATCH(TRUNC(LOOK_TEMP)+0.5,temp._value_range),IF(LOOK_TEMP＞(TRUNC(LOOK_TEMP)+0.125),MATCH(TRUNC(LOOK_TEMP)+0.25,temp._value_ran ge),MATCH(TRUNC(LOOK_TEMP),temp._value_range))))),IF((TRUNC(LOOK_DENSITY/10)*10+2)＞LOOK_DENSITY,MATCH(TR UNC(LOOK_DENSITY/10)*10,density_value_range),IF((TRU NC(LOOK_DENSITY/10)*10+4)＞LOOK_DENSITY,MATCH(TRUNC(L OOK_DENSITY/10)*10+2,density_value_range),IF((TRUNC(LOOK_DENSITY/10)*10+6)＞LOOK_DENSITY,MATCH(TRUNC(LOOK_DENSITY/10)*10+4,density_value_range),IF((TRUNC(LOO K_DENSITY/10)*10+8)＞LOOK_DENSITY,MATCH(TRUNC(LOOK_DE NSITY/10)*10+6,density_value_range),MATCH(TRUNC(LOOK_DENSITY/10)*10+8,density_value_range))))))。

即为查找较接近待查温度值的表中温度值和刚好小于或等于待查密度值的表中密度值所交叉对应的VCF值的单元格地址。

4.2 语句二

ADDRESS(IF(LOOK_TEMP＞(TRUNC(LOOK_TEMP)+(0.75+0.1 25)),MATCH(TRUNC(LOOK_TEMP)+1,temp._value_range),IF(LOOK_TEMP＞(TRUNC(LOOK_TEMP)+(0.5+0.125)),MATCH(TRUNC(LOOK_TEMP)+0.75,temp._value_range),IF(LOOK_TEMP＞(TR UNC(LOOK_TEMP)+(0.25+0.125)),MATCH(TRUNC(LOOK_TEMP)+0.5,temp._value_range),IF(LOOK_TEMP＞(TRUNC(LOOK_TEMP)+0.125),MATCH(TRUNC(LOOK_TEMP)+0.25,temp._value_ran ge),MATCH(TRUNC(LOOK_TEMP),temp._value_range))))),IF(LOOK_DENSITY＞TRUNC(LOOK_DENSITY/10)*10+8,MATCH(TRUN C(LOOK_DENSITY/10)*10+10,density_value_range),IF(LOO K_DENSITY＞(TRUNC(LOOK_DENSITY/10)*10+6),MATCH(TRUNC(LOOK_DENSITY/10)*10+8,density_value_range),IF(LOOK_D ENSITY＞(TRUNC(LOOK_DENSITY/10)*10+4),MATCH(TRUNC(LOO K_DENSITY/10)*10+6,density_value_range),IF(LOOK_DENS ITY＞(TRUNC(LOOK_DENSITY/10)*10+2),MATCH(TRUNC(LOOK_D ENSITY/10)*10+4,density_value_range),IF(LOOK_DENSITY＞(TRUNC(LOOK_DENSITY/10)*10),MATCH(TRUNC(LOOK_DENSIT Y/10)*10+2,density_value_range),MATCH(TRUNC(LOOK_DEN SITY/10)*10,density_value_range)))))))。

即为查找较接近待查温度值的表中温度值和刚好大于或等于待查密度值的表中密度值所交叉对应的VCF值的单元格地址。

5 GB/T 1885-1998查找算法

在GB/T 1885-1998标准中，温度和密度都不插值计算，全都使用较接近值的算法，则只需使用一条语句，来实现较接近待查温度值和待查密度值的表中温度值和表中密度值所交叉对应的VCF值的查找：

ADDRESS(IF(LOOK_TEMP＞(TRUNC(LOOK_TEMP)+(0.75+0.1 25)),MATCH(TRUNC(LOOK_TEMP)+1,temp._value_range),IF(LOOK_TEMP＞(TRUNC(LOOK_TEMP)+(0.5+0.125)),MATCH(TRUNC(LOOK_TEMP)+0.75,temp._value_range),IF(LOOK_TEMP＞(TR UNC(LOOK_TEMP)+(0.25+0.125)),MATCH(TRUNC(LOOK_TEMP)+0.5,temp._value_range),IF(LOOK_TEMP＞(TRUNC(LOOK_TEMP)+0.125),MATCH(TRUNC(LOOK_TEMP)+0.25,temp._value_ran ge),MATCH(TRUNC(LOOK_TEMP),temp._value_range))))),IF(LOOK_DENSITY＞TRUNC(LOOK_DENSITY/10)*10+9,MATCH(TRUN C(LOOK_DENSITY/10)*10+10,density_value_range),IF(LOO K_DENSITY＞(TRUNC(LOOK_DENSITY/10)*10+7),MATCH(TRUNC(LOOK_DENSITY/10)*10+8,density_value_range),IF(LOOK_D ENSITY＞(TRUNC(LOOK_DENSITY/10)*10+5),MATCH(TRUNC(LOO K_DENSITY/10)*10+6,density_value_range),IF(LOOK_DENS ITY＞(TRUNC(LOOK_DENSITY/10)*10+3),MATCH(TRUNC(LOOK_D ENSITY/10)*10+4,density_value_range),IF(LOOK_DENSITY＞(TRUNC(LOOK_DENSITY/10)*10+1),MATCH(TRUNC(LOOK_DENS ITY/10)*10+2,density_value_range),MATCH(TRUNC(LOOK_D ENSITY/10)*10,density_value_range)))))))

6 结语

本文中的程序语句均在EXCEL2003下通过。

在实际应用中，语句仍需要进行改进。对输入的温度值和密度值，应进行值域检查，即对输入值的有效性进行检查，同时也对边界值的情况进行处理。当输入无效值时，给出提示，以避免无效的计算结果，使程序完善，具有健壮性和稳定性。

因为原油时常来自同一产区，所以其品质基本上变化不大。因此只需将常用的VCF值域输入EXCEL表格中。仅需输入较少的数据量，就能达到预期的目的。而对于需用到的未录入的VCF数据，也可现场输入到二维表格中，就可利用程序进行相应的查找插值计算，减少了计算的工作量及强度，提高了工作效率。

本文语句实现了温度、密度及温度和密度所交叉对应的VCF值都进行插值计算,仅密度和密度所对应的VCF值进行插值计算,无插值计算三种查找算法，并且仅在EXCEL的工作界面中实现查找并得到所需的结果，应用相对简单。对于同一个VCF数据表，可以根据实际情况的需要来选用不同的算法，以适合工作之需要。而且本文语句具有很强通用性，稍加修改，即可建立一个空白的查找模板。使用时只要将表格数据录入，即可完成任意二维表格数据的查找及插值计算工作，基本上可以应用于鉴定工作中所用到的大多数表格。

[1] 石油计量表原油部分(GB/T 1885-1998专用).

[2] GB/T 19779-2005,石油和液体石油产品油量计算静态计量.

[3] John Walkenbach．Excel 2003 Formulas.Wiley Publishing,Inc.2004.

[4] 严蔚敏,吴伟民.数据结构[M].清华大学出版社,1997.

[5] 谭浩强.C语言程序设计[M].清华大学出版社,2000.

[6] 肖素琴.油品计量员读本[M].中国石化出版社,2001.