在水库变形观测外业中Excel电子表格应用的探讨
2011-04-19李明
李 明
(四川省长葫灌区管理局,四川 威远,642450)
随着在水库变形观测外业中笔记本电脑的应用,使观测工作更加快捷和精确,只要笔记本电脑中装有excel电子表格,就能完成变形观测中外业工作的各种计算。excel电子表格具有强大的计算功能,特别是在函数的应用上功能齐全。有些复杂的计算虽然不能直接调用一个函数就能完成,但是调用多个函数基本都能达到要求。本文将分两部分对变形观测外业中excel电子表格应用遇到的精度取舍问题和角度计算问题进行探讨。
1 精度取舍问题
1.1 概述
在水库变形观测中,数据取舍采用的原则是“四舍六入,五留双”,即小数后第三位是6或以上就收入,小数后第三位是4或以下就舍去。例如12.246、12.237保留两位小数分别为12.25、12.24。例如12.244、12.231保留两位小数分别为12.24、12.23。小数后第三位是5,就要看5前一位是偶或奇,如果前一位是偶就舍去,是奇就收入,例如12.245、12.235保留两位小数都应为12.24。在excel电子表格中,对12.235这样第二位是奇的数值进行取舍就非常容易,调用ROUND函数就行了,输入公式=ROUND(A3,2),式中A3为单元格位置(需要进行四舍五入的数值或计算公式),2为保留两位小数。但对12.245这样的数据就不能只调用一个函数就能完成,需要对数值进行判定,再进行截取或四舍五入取舍。
1.2 选用函数
(1)TRUNC函数:将数值截为整数或保留指定位数的小数;
(2)ROUND函数:按指定的位数对数值进行四舍五入;
(3)EXACT函数:比较两个字符串是否完全相同。如果它们完全相同,则返回TRUE;否则,返回FALSE;
(4)ISEVEN函数:如果数值是偶,返回值为TRUE(属于加载宏函数,后有注明);
(5)RIGH T函数:从一个文本字符串最后一个字符开始返回指定个数的字符;
(6)IF函数:判定一个条件是否满足,如果满足返回一个值,如果不满足则返回另一个值;
(7)AND函数:检查是否所有参数均为TRUE,如果所有参数均为TRUE,则返回TRUE。
1.3 原理及公式
由于我们讨论的数据取舍考虑因素有两个:一是指定保留小数位数的最后位的数字是否是偶;二是指定保留小数位数的最后位的下一位数字是否是5。以12.235和保留两位小数为例,就要判定小数后第二位数(12.235数值中的3)是否是偶数,小数后第三位数(12.235数值中的5)是否是5。故对需要判定的数值的逻辑关系为:假如小数后第二位数是偶数和第三位数是5,就将此数值截为保留2位的小数,否则将此数值按保留2位小数进行四舍五入。单元格公式为:
=IF(AND(EXACT(RIGHT(TRUNC(C4,3),1),5),ISEVEN(RIGH T(TRUNC(C4,2),1))),TRUNC(C4,2),ROUND(C4,2))。
式中,C4为单元格位置(需要进行四舍五入的数值或单元格计算公式);
EXACT(RIGHT(TRUNC(C4,3),1),5):比较C4中小数后第三位数是否是5;
TRUNC(C4,3):截取数值为保留3位数的小数;
RIGHT(TRUNC(C4,3),1):返回截取数值为保留3位数的小数的最后一个字符;
ISEVEN(RIGHT(TRUNC(C4,2),1):为C4中第二位数字是否是偶;
TRUNC(C4,2):截取数值为保留2位数的小数;
RIGHT(TRUNC(C4,2),1):返回截取数值为保留2位数的小数的最后一个字符;TRUNC(C4,2):将C4截为保留2位数的小数;ROUND(C4,2):将C4按保留2位数的小数进行四舍五入。
注:本公式中,ISEVEN属于加载宏函数,要使用必须保证加载了分析工具库。加载方法:点击excel电子表格菜单-工具-加载宏,勾选“分析工具库”即可。
1.4 应用案例
图1为水库大坝视准线水平位移观测,对拱冠进行2个测回的观测,每个测回分正、反镜观测。在图1中F4为Ⅰ测回的平均值,输入公式:
=IF(AND(EXACT(RIGHT(TRUNC((E4+E5)/2,3),1),5),ISEVEN(RIGHT(TRUNC((E4+E5)/2,2),1))),TRUNC((E4+E5)/2,2),ROUND((E4+E5)/2,2))
F6为Ⅱ测回平均值,在单元格中复制/粘贴以上公式,只是公式中E4+E5改为了E6+E7(可用excel多种方法)。G4为各测回平均值,在单元格中复制/粘贴以上公式,只是公式中E4+E5改为了F4+F6。图1表明:Ⅰ测回观测值为51.27和51.30,平均数为51.285,保留2位小数为51.28,每个测回平均值为51.28和51.03,平均数为51.155,保留2位小数为51.16,完全符合“四舍六入,五留双”的原则。
图1 水库大坝视准线水平位移观测值
2 角度计算问题
2.1 概述
在水库变形观测中,水平方向观测记录有很多需要角度相加、减及角度平均的计算,而在excel电子表格中是十进位,这就给六十进位的角度计算带来难题,但采用excel电子表格时间模拟计算就能解决这一问题。
2.2 模拟计算
2.2.1 格式设置
对需要进行角度计算的单元格进行格式设置。格式设置步骤:格式-单元格-数字—自定义-类型。在类型中根据你的计算需要设定成以下几种类型中的一种:
(1)度/分/秒,例21°36′45″ 类型:[h]“°”mm“′”ss“″”
(2)度/分,例24°21′ 类型:[h]“°”mm“′”
(3)分/秒,例34′21″ 类型:mm“′”ss“″”
(4)秒,例21″ 类型:ss“″”
(5)秒后小数,例22.4 类型:ss.0(以上(1)、(3)也可按此设置小数)
2.2.2 角度输入:
单击单元格后按时间格式和你设置的格式类型输入
(1)度/分/秒,21°36′45″ 输入:21:36:45
(2)度/分,24°21′ 输入:24:21
(3)分/秒,34′21″ 输入:34:21
(4)秒,21″ 输入:0:21
(5)秒,22.4 输入:0:22.4(注:输入后按回车结束,但再次双击进入单元格后,excel会自动对小数进行取舍,需重新输入小数。)
2.2.3 角度计算:
2.2.3.1 角度相加。有两种情况,一是在单元格中设置公式单元格直接相加;二是两单元格或多单元格相加>360°,需要减去360°时的公式:
=IF(E6+E12>15,SUM(E6+E12)-15,SUM(E6+E12))
式中,E6、E12为单元格位置;15是360/24(excel电子表格时间1∶00∶00为24)。
注:如果单元格相加需要减去180°,式中15就改为7.5(180/24)。
2.2.3.2 角度相减。有两种情况,一是两个方向的夹角的公式=IF(E6<E12,E6-E12,E12-E6);二是一个方向减另一个方向的公式=IF(E6<E12,E6+15-E12,E6-E12)。式中,15是360/24(excel电子表格时间1∶00∶00为24),E6、E12为单元格位置。
2.2.3.3 角度平均。设置单元格公式直接计算,并套上精度取舍公式。
2.3 应用案例
图2为水平方向观测手簿,按2.2模拟计算,对excel电子表格作如下设置:
2.3.1 单元格格式设置
图2中:M6至M 14设置格式为:[h]“°”mm“′”ss“″”.0。
B7至K14设置格式为:常规
L7至L14为隐藏列,设置格式:[h]“°”mm“′”ss“″”.0(作用在2.3.2说明)
2.3.2 公式设置
E7为(D7+D8)的平均值,套上精度取舍,E7公式:
同理设置E9∶E13、I7∶I13的公式;
J7:J13为2C,公式设置E-I;
左+右/2为盘左的平均值+盘右的平均值的平均值,公式设置(E+I)/2套上精度取舍公式;
L9、L11为隐藏列,作用是将度、分、秒单元格内的数值合并。L9公式=VALUE(B9&“:”&C9&“:”&K9);L11=VALUE(B11&“:”&C11&“:”&K11);
M6为需要减去后归零的值,等于合并B7、C7和K7+K13的平均值套上精度取舍:
M6=VALUE(B7&“:”&C7&“:”&IF(AND(EXACT(RIGHT(TRUNC((K7+K13)/2,2),1),5),ISEVEN(RIGH T(TRUNC((K7+K13)/2,1),1))),TRUNC((K7+K13)/2,1),ROUND((K7+K13)/2,1)));
M9、M11为方向值,公式分别为:M 9=IF
(L9>L7,L9-L7,L9+15-L7)、M11=IF(L11>M6,L11-M6,L11+15-M6)。
2.3.3 输入计算。在单元格B7:D13、F7:H 13输入观测值,excel电子表格自动生成图2。
3 结语
“四舍六入,五留双”取舍原则应用广泛,本方法只需根据保留位数对公式稍加改动即可应用。角度计算按本文时间模拟计算方法,能适应角度加、减等各种格式的计算。为了使用方便,可将设置好的各种计算表格保存为excel电子表格模板,这样每次使用时,只要选择此模板建立新的文件即可。 ■