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

李 明

(四川省長葫灌區(qū)管理局,四川 威遠,642450)

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

1 精度取舍問題

1.1 概述

在水庫變形觀測中,數(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ù)都應為12.24。在excel電子表格中,對12.235這樣第二位是奇的數(shù)值進行取舍就非常容易,調(diào)用ROUND函數(shù)就行了,輸入公式=ROUND(A3,2),式中A3為單元格位置(需要進行四舍五入的數(shù)值或計算公式),2為保留兩位小數(shù)。但對12.245這樣的數(shù)據(jù)就不能只調(diào)用一個函數(shù)就能完成,需要對數(shù)值進行判定,再進行截取或四舍五入取舍。

1.2 選用函數(shù)

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

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

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

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

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

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

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

1.3 原理及公式

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

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

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

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ù)的最后一個字符;

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

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

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

注:本公式中,ISEVEN屬于加載宏函數(shù),要使用必須保證加載了分析工具庫。加載方法:點擊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,平均數(shù)為51.285,保留2位小數(shù)為51.28,每個測回平均值為51.28和51.03,平均數(shù)為51.155,保留2位小數(shù)為51.16,完全符合“四舍六入,五留雙”的原則。

圖1 水庫大壩視準線水平位移觀測值

2 角度計算問題

2.1 概述

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

2.2 模擬計算

2.2.1 格式設置

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

(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ù))

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

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設置格式為:常規(guī)

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為隱藏列,作用是將度、分、秒單元格內(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 輸入計算。在單元格B7:D13、F7:H 13輸入觀測值,excel電子表格自動生成圖2。

3 結(jié)語

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