在水庫(kù)變形觀測(cè)外業(yè)中Excel電子表格應(yīng)用的探討

李 明

(四川省長(zhǎng)葫灌區(qū)管理局,四川 威遠(yuǎn),642450)

隨著在水庫(kù)變形觀測(cè)外業(yè)中筆記本電腦的應(yīng)用,使觀測(cè)工作更加快捷和精確,只要筆記本電腦中裝有excel電子表格,就能完成變形觀測(cè)中外業(yè)工作的各種計(jì)算。excel電子表格具有強(qiáng)大的計(jì)算功能,特別是在函數(shù)的應(yīng)用上功能齊全。有些復(fù)雜的計(jì)算雖然不能直接調(diào)用一個(gè)函數(shù)就能完成,但是調(diào)用多個(gè)函數(shù)基本都能達(dá)到要求。本文將分兩部分對(duì)變形觀測(cè)外業(yè)中excel電子表格應(yīng)用遇到的精度取舍問(wèn)題和角度計(jì)算問(wèn)題進(jìn)行探討。

1 精度取舍問(wèn)題

1.1 概述

在水庫(kù)變形觀測(cè)中,數(shù)據(jù)取舍采用的原則是“四舍六入,五留雙”,即小數(shù)后第三位是6或以上就收入,小數(shù)后第三位是4或以下就舍去。例如12.246、12.237保留兩位小數(shù)分別為12.25、12.24。例如12.244、12.231保留兩位小數(shù)分別為12.24、12.23。小數(shù)后第三位是5,就要看5前一位是偶或奇,如果前一位是偶就舍去,是奇就收入,例如12.245、12.235保留兩位小數(shù)都應(yīng)為12.24。在excel電子表格中,對(duì)12.235這樣第二位是奇的數(shù)值進(jìn)行取舍就非常容易,調(diào)用ROUND函數(shù)就行了,輸入公式=ROUND(A3,2),式中A3為單元格位置(需要進(jìn)行四舍五入的數(shù)值或計(jì)算公式),2為保留兩位小數(shù)。但對(duì)12.245這樣的數(shù)據(jù)就不能只調(diào)用一個(gè)函數(shù)就能完成,需要對(duì)數(shù)值進(jìn)行判定,再進(jìn)行截取或四舍五入取舍。

1.2 選用函數(shù)

(1)TRUNC函數(shù):將數(shù)值截為整數(shù)或保留指定位數(shù)的小數(shù);

(2)ROUND函數(shù):按指定的位數(shù)對(duì)數(shù)值進(jìn)行四舍五入;

(3)EXACT函數(shù):比較兩個(gè)字符串是否完全相同。如果它們完全相同,則返回TRUE;否則,返回FALSE;

(4)ISEVEN函數(shù):如果數(shù)值是偶,返回值為T(mén)RUE(屬于加載宏函數(shù),后有注明);

(5)RIGH T函數(shù):從一個(gè)文本字符串最后一個(gè)字符開(kāi)始返回指定個(gè)數(shù)的字符;

(6)IF函數(shù):判定一個(gè)條件是否滿足,如果滿足返回一個(gè)值,如果不滿足則返回另一個(gè)值;

(7)AND函數(shù):檢查是否所有參數(shù)均為T(mén)RUE,如果所有參數(shù)均為T(mén)RUE,則返回TRUE。

1.3 原理及公式

由于我們討論的數(shù)據(jù)取舍考慮因素有兩個(gè):一是指定保留小數(shù)位數(shù)的最后位的數(shù)字是否是偶;二是指定保留小數(shù)位數(shù)的最后位的下一位數(shù)字是否是5。以12.235和保留兩位小數(shù)為例,就要判定小數(shù)后第二位數(shù)(12.235數(shù)值中的3)是否是偶數(shù),小數(shù)后第三位數(shù)(12.235數(shù)值中的5)是否是5。故對(duì)需要判定的數(shù)值的邏輯關(guān)系為:假如小數(shù)后第二位數(shù)是偶數(shù)和第三位數(shù)是5,就將此數(shù)值截為保留2位的小數(shù),否則將此數(shù)值按保留2位小數(shù)進(jìn)行四舍五入。單元格公式為:

=IF(AND(EXACT(RIGHT(TRUNC(C4,3),1),5),ISEVEN(RIGH T(TRUNC(C4,2),1))),TRUNC(C4,2),ROUND(C4,2))。

式中,C4為單元格位置(需要進(jìn)行四舍五入的數(shù)值或單元格計(jì)算公式);

EXACT(RIGHT(TRUNC(C4,3),1),5):比較C4中小數(shù)后第三位數(shù)是否是5;

TRUNC(C4,3):截取數(shù)值為保留3位數(shù)的小數(shù);

RIGHT(TRUNC(C4,3),1):返回截取數(shù)值為保留3位數(shù)的小數(shù)的最后一個(gè)字符;

ISEVEN(RIGHT(TRUNC(C4,2),1):為C4中第二位數(shù)字是否是偶;

TRUNC(C4,2):截取數(shù)值為保留2位數(shù)的小數(shù);

RIGHT(TRUNC(C4,2),1):返回截取數(shù)值為保留2位數(shù)的小數(shù)的最后一個(gè)字符;TRUNC(C4,2):將C4截為保留2位數(shù)的小數(shù);ROUND(C4,2):將C4按保留2位數(shù)的小數(shù)進(jìn)行四舍五入。

注:本公式中,ISEVEN屬于加載宏函數(shù),要使用必須保證加載了分析工具庫(kù)。加載方法:點(diǎn)擊excel電子表格菜單-工具-加載宏,勾選“分析工具庫(kù)”即可。

1.4 應(yīng)用案例

圖1為水庫(kù)大壩視準(zhǔn)線水平位移觀測(cè),對(duì)拱冠進(jìn)行2個(gè)測(cè)回的觀測(cè),每個(gè)測(cè)回分正、反鏡觀測(cè)。在圖1中F4為Ⅰ測(cè)回的平均值,輸入公式:

=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為Ⅱ測(cè)回平均值,在單元格中復(fù)制/粘貼以上公式,只是公式中E4+E5改為了E6+E7(可用excel多種方法)。G4為各測(cè)回平均值,在單元格中復(fù)制/粘貼以上公式,只是公式中E4+E5改為了F4+F6。圖1表明:Ⅰ測(cè)回觀測(cè)值為51.27和51.30,平均數(shù)為51.285,保留2位小數(shù)為51.28,每個(gè)測(cè)回平均值為51.28和51.03,平均數(shù)為51.155,保留2位小數(shù)為51.16,完全符合“四舍六入,五留雙”的原則。

圖1 水庫(kù)大壩視準(zhǔn)線水平位移觀測(cè)值

2 角度計(jì)算問(wèn)題

2.1 概述

在水庫(kù)變形觀測(cè)中,水平方向觀測(cè)記錄有很多需要角度相加、減及角度平均的計(jì)算,而在excel電子表格中是十進(jìn)位,這就給六十進(jìn)位的角度計(jì)算帶來(lái)難題,但采用excel電子表格時(shí)間模擬計(jì)算就能解決這一問(wèn)題。

2.2 模擬計(jì)算

2.2.1 格式設(shè)置

對(duì)需要進(jìn)行角度計(jì)算的單元格進(jìn)行格式設(shè)置。格式設(shè)置步驟:格式-單元格-數(shù)字—自定義-類型。在類型中根據(jù)你的計(jì)算需要設(shè)定成以下幾種類型中的一種:

(1)度/分/秒,例21°36′45″ 類型:[h]“°”mm“′”ss“″”

(2)度/分,例24°21′ 類型:[h]“°”mm“′”

(3)分/秒,例34′21″ 類型:mm“′”ss“″”

(4)秒,例21″ 類型:ss“″”

(5)秒后小數(shù),例22.4 類型:ss.0(以上(1)、(3)也可按此設(shè)置小數(shù))

2.2.2 角度輸入:

單擊單元格后按時(shí)間格式和你設(shè)置的格式類型輸入

(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(注:輸入后按回車(chē)結(jié)束,但再次雙擊進(jìn)入單元格后,excel會(huì)自動(dòng)對(duì)小數(shù)進(jìn)行取舍,需重新輸入小數(shù)。)

2.2.3 角度計(jì)算:

2.2.3.1 角度相加。有兩種情況,一是在單元格中設(shè)置公式單元格直接相加;二是兩單元格或多單元格相加＞360°,需要減去360°時(shí)的公式:

=IF(E6+E12＞15,SUM(E6+E12)-15,SUM(E6+E12))

式中,E6、E12為單元格位置;15是360/24(excel電子表格時(shí)間1∶00∶00為24)。

注:如果單元格相加需要減去180°,式中15就改為7.5(180/24)。

2.2.3.2 角度相減。有兩種情況,一是兩個(gè)方向的夾角的公式=IF(E6＜E12,E6-E12,E12-E6);二是一個(gè)方向減另一個(gè)方向的公式=IF(E6＜E12,E6+15-E12,E6-E12)。式中,15是360/24(excel電子表格時(shí)間1∶00∶00為24),E6、E12為單元格位置。

2.2.3.3 角度平均。設(shè)置單元格公式直接計(jì)算,并套上精度取舍公式。

2.3 應(yīng)用案例

圖2為水平方向觀測(cè)手簿,按2.2模擬計(jì)算,對(duì)excel電子表格作如下設(shè)置:

2.3.1 單元格格式設(shè)置

圖2中:M6至M 14設(shè)置格式為:[h]“°”mm“′”ss“″”.0。

B7至K14設(shè)置格式為:常規(guī)

L7至L14為隱藏列,設(shè)置格式:[h]“°”mm“′”ss“″”.0(作用在2.3.2說(shuō)明)

2.3.2 公式設(shè)置

E7為(D7+D8)的平均值,套上精度取舍,E7公式:

同理設(shè)置E9∶E13、I7∶I13的公式;

J7:J13為2C,公式設(shè)置E-I;

左+右/2為盤(pán)左的平均值+盤(pán)右的平均值的平均值,公式設(shè)置(E+I)/2套上精度取舍公式;

L9、L11為隱藏列,作用是將度、分、秒單元格內(nèi)的數(shù)值合并。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 輸入計(jì)算。在單元格B7:D13、F7:H 13輸入觀測(cè)值,excel電子表格自動(dòng)生成圖2。

3 結(jié)語(yǔ)

“四舍六入,五留雙”取舍原則應(yīng)用廣泛,本方法只需根據(jù)保留位數(shù)對(duì)公式稍加改動(dòng)即可應(yīng)用。角度計(jì)算按本文時(shí)間模擬計(jì)算方法,能適應(yīng)角度加、減等各種格式的計(jì)算。為了使用方便,可將設(shè)置好的各種計(jì)算表格保存為excel電子表格模板,這樣每次使用時(shí),只要選擇此模板建立新的文件即可。 ■