Excel在二等水準(zhǔn)測(cè)量自動(dòng)檢查計(jì)算中的應(yīng)用

陳德紹 羅桂發(fā)

（柳州鐵道職業(yè)技術(shù)學(xué)院,廣西 柳州 545616）

在二等水準(zhǔn)測(cè)量中，有大量的數(shù)學(xué)計(jì)算，還涉及特殊的計(jì)算規(guī)則——逢五奇進(jìn)偶不進(jìn)，同時(shí)還有限差的判斷。在二等水準(zhǔn)測(cè)量實(shí)踐教學(xué)中，學(xué)生測(cè)量記錄產(chǎn)生了大量的數(shù)據(jù)，教師需要對(duì)這些數(shù)據(jù)進(jìn)行檢查糾錯(cuò)，發(fā)現(xiàn)問題，讓學(xué)生改正，提高實(shí)踐教學(xué)效果。這些數(shù)據(jù)都由教師一個(gè)人檢查其正確性，是一項(xiàng)非常艱巨的任務(wù)。廣西職業(yè)院校技能大賽測(cè)繪賽項(xiàng)在柳州鐵道職業(yè)技術(shù)學(xué)院舉行，賽項(xiàng)中有一項(xiàng)內(nèi)容為二等水準(zhǔn)測(cè)量。由于參賽隊(duì)伍眾多，比賽時(shí)間緊迫，對(duì)二等水準(zhǔn)測(cè)量成果的準(zhǔn)確快速評(píng)分是急需解決的大問題。如果能提供一種自動(dòng)計(jì)算檢查數(shù)據(jù)的工具，對(duì)于競(jìng)賽測(cè)量成果快速評(píng)分、學(xué)生自行檢查糾錯(cuò)有極大的幫助[1-4]。

本文將針對(duì)在Excel中二等水準(zhǔn)測(cè)量自動(dòng)計(jì)算檢查的實(shí)現(xiàn)進(jìn)行研究，通過分析二等水準(zhǔn)測(cè)量的要求、競(jìng)賽規(guī)則以及學(xué)生學(xué)習(xí)的特點(diǎn)進(jìn)行功能需求分析，并對(duì)Excel函數(shù)進(jìn)行研究，以實(shí)現(xiàn)功能需求。

1 調(diào)查問卷分析

1.1 界面需求分析

為了易于使用者理解和掌握，Excel水準(zhǔn)記錄表與平差配賦的格式要與所使用的二等水準(zhǔn)記錄表格式一致。為了整體直觀性，需要水準(zhǔn)記錄表和平差配賦表分別在一個(gè)頁(yè)面展示出來(lái)，同時(shí)也要便于數(shù)據(jù)的錄入[5-6]。

基于以上需求，把Excel設(shè)置成三個(gè)表格，分別為二等水準(zhǔn)測(cè)量手簿（如表1）、平差配賦表（如表2）、錄入表（如表3）。二等水準(zhǔn)測(cè)量手簿和平差配賦表的格式與測(cè)量時(shí)所記錄的格式一致，便于使用者查看，同時(shí)為了防止單元格的命令設(shè)置被修改，關(guān)閉表格的編輯功能，只保留平差配賦表中起算點(diǎn)高程的輸入，測(cè)量手簿是連續(xù)編排的，測(cè)段間沒有停頓分割。輸入表是一張專門用來(lái)錄入數(shù)據(jù)的表格，編排格式要便于錄入、便于理解。

表1 二等水準(zhǔn)測(cè)量手簿

表2 水準(zhǔn)平差配賦表

表3 錄入表

1.2 技術(shù)難點(diǎn)分析

測(cè)量手簿需要完成自動(dòng)計(jì)算，并按手簿的格式要求填寫。對(duì)不符合二等水準(zhǔn)測(cè)量技術(shù)要求的數(shù)據(jù)要進(jìn)行標(biāo)記。對(duì)測(cè)量數(shù)據(jù)進(jìn)行平差計(jì)算，判斷測(cè)量成果是否合格，并填寫完成平差配賦表。平均高差取值時(shí)尾數(shù)逢五奇進(jìn)偶不進(jìn)的計(jì)算規(guī)則、每測(cè)段的測(cè)站數(shù)不確定性對(duì)水平配賦表的影響。

2 Excel記錄手簿的自動(dòng)計(jì)算

2.1 二等水準(zhǔn)測(cè)量手簿的自動(dòng)計(jì)算

數(shù)據(jù)的錄入完全由錄入表完成后，測(cè)量手簿需要把錄入表中的數(shù)據(jù)提取過來(lái)。如表1、3所示，測(cè)量手簿中單元格B4=錄入表！C2；C4=錄入表！C3；E4=錄入表！C4；F4=錄入表！C5；E5=錄入表！C6；F5=錄入表！C7。以上是測(cè)站1的測(cè)量數(shù)據(jù)，同理把后面的測(cè)站數(shù)據(jù)全部提取到測(cè)量手簿中。

數(shù)據(jù)提取到測(cè)量手簿后，需要對(duì)測(cè)量手簿數(shù)據(jù)進(jìn)行計(jì)算。視距差單元格B6=B4-C4；累計(jì)視距差C6=B6（第一測(cè)站累計(jì)視距差等于視距差，第二測(cè)站累計(jì)視距差等于上一測(cè)站的累計(jì)視距差加上本測(cè)站的視距差）；兩次讀數(shù)差G4=E4-F4；G5=E5-F5；高差E6=E4-E5；F6=F4-F5；高差之差G6=E6-F6；把高差之差G6最后一位數(shù)進(jìn)行奇進(jìn)偶不進(jìn)取整，并換算成以米為單位(IF(RIGHT(TRUNC(G7,1),1)="5",IF(M OD(RIGHT(TRUNC(G7,0),1),2)=0,ROUNDDOWN(G7,0),ROUNDU P(G7,0)),ROUND(G7,0)))/100000。

對(duì)單元格數(shù)值取位與精度進(jìn)行設(shè)置，數(shù)據(jù)格式全部設(shè)置為數(shù)值，距離以米為單位，取到小數(shù)點(diǎn)后1位，標(biāo)尺讀數(shù)取整，平均高差以米為單位，取到小數(shù)點(diǎn)后5位。

運(yùn)用同樣的運(yùn)算規(guī)則，可以對(duì)剩下的測(cè)站進(jìn)行設(shè)置。

2.2 測(cè)量限差超限的提示

在二等水準(zhǔn)測(cè)量手簿中有5個(gè)限差（如表4），單元格對(duì)其數(shù)值進(jìn)行判斷是否超限，如果超限則把單元填充顏色改為紅色作為提醒。

表4 二等水準(zhǔn)測(cè)量技術(shù)要求（2 m水準(zhǔn)標(biāo)尺）

在“條件格式”→“突出顯示單元格規(guī)則”中對(duì)相應(yīng)的單元格進(jìn)行設(shè)置。視線長(zhǎng)度限差：當(dāng)B4單元格的值未介于3和50之間時(shí)，把B4的填充顏色設(shè)置為紅色，C4單元格設(shè)置與B4一致。前后視距差限差：當(dāng)B6單元格的值未介于-1.5和1.5之間時(shí)，把B6的填充顏色設(shè)置為紅色。前后視距累計(jì)差限差：當(dāng)C6單元格的值未介于-6和6之間時(shí)，把C6的填充顏色設(shè)置為紅色。視線高度限差：?jiǎn)卧馝4、E5、F4、F5的單元格規(guī)則一致，單元格的值未介于055 000和180 000之間時(shí)，把填充顏色設(shè)置為紅色。兩次讀數(shù)所得高差之差限差：當(dāng)單元格G6的值未介于-60和60之間時(shí)，把G6的填充顏色設(shè)置為紅色。

運(yùn)用同樣的運(yùn)算規(guī)則，可以對(duì)剩下的測(cè)站進(jìn)行設(shè)置。

3 閉合水準(zhǔn)路線平差配賦表的自動(dòng)計(jì)算

3.1 各測(cè)站的距離和高差進(jìn)行累計(jì)

由于每段的水準(zhǔn)測(cè)站數(shù)均為偶數(shù)，所以只需對(duì)偶數(shù)站進(jìn)行累計(jì)。

如表5所示，第二測(cè)站累計(jì)距離單元格B2=水準(zhǔn)記錄表!B4+水準(zhǔn)記錄表!C4+水準(zhǔn)記錄表!B8+水準(zhǔn)記錄表!C8，第四測(cè)站累計(jì)距離單元格B3=B2+水準(zhǔn)記錄表!B12+水準(zhǔn)記錄表!C12+水準(zhǔn)記錄表!B16+水準(zhǔn)記錄表!C16，以此類推，計(jì)算第六、八、十以后的測(cè)站距離累計(jì)。如表5所示，第二測(cè)站累計(jì)高差單元格C2=水準(zhǔn)記錄表!E7+水準(zhǔn)記錄表!E11，第四測(cè)站累計(jì)高差單元格C3=C2+水準(zhǔn)記錄表!E15+水準(zhǔn)記錄表!E19，以此類推，計(jì)算第六、八、十以后的測(cè)站高差累計(jì)。

表5 距離高差統(tǒng)計(jì)

3.1.1 各測(cè)段累計(jì)距離與累計(jì)高差的提取

如表6所示，根據(jù)測(cè)繪技能競(jìng)賽的賽程，閉合水準(zhǔn)路線分為4個(gè)測(cè)段，由于每個(gè)測(cè)段的測(cè)站數(shù)存在不確定性，所以需要手動(dòng)輸入各測(cè)段的測(cè)站數(shù)[7]。然后對(duì)各進(jìn)行累計(jì)測(cè)段的測(cè)站數(shù)，第一測(cè)段累計(jì)測(cè)站數(shù)G2=F2，第二測(cè)段累計(jì)測(cè)站數(shù)G3=G2+F3，第三測(cè)段累計(jì)測(cè)站數(shù)G4=G3+F4，第四測(cè)段累計(jì)測(cè)站數(shù)G5=G4+F5。

表6 各測(cè)段測(cè)站數(shù)輸入與數(shù)據(jù)提取

根據(jù)表5與表6的數(shù)據(jù)，進(jìn)行各測(cè)段累計(jì)距離的提取。第一測(cè)段累計(jì)距離H2=VLOOKUP(G2,A2:C17,2,FALSE)；第二測(cè)段累計(jì)距離H3=VLOOKUP(G3,A2:C17,2,FALSE)；第三測(cè)段累計(jì)距離H4=VLOOKUP(G4,A4:C19,2,FALSE)；第四測(cè)段累計(jì)距離H5=VLOOKUP(G5,A4:C19,2,FALSE)。

根據(jù)表5與表6表格的數(shù)據(jù)，進(jìn)行各測(cè)段累計(jì)高差的提取。I2=VLOOKUP（G2,A2:C17,3,FALSE)；I3=VLOOKUP（G3,A3:C18,3,FALSE)；I4=VLOOKUP（G4,A4:C19,3,FALSE）；I5=VLOOKUP（G5,A5:C20,3,FALSE）。

3.1.2 閉合水準(zhǔn)路線平差配賦表的自動(dòng)計(jì)算

根據(jù)表7各測(cè)段累計(jì)距離數(shù)據(jù)，對(duì)測(cè)段距離進(jìn)行計(jì)算：L21=H2；L23=H3-H2；L25=H4-H3；L27=H5-H4。根據(jù)表7各測(cè)段累計(jì)高差數(shù)據(jù)，對(duì)測(cè)段高差進(jìn)行計(jì)算：M21=I2；M23=I3-I2；M25=I4-I3；M27=I5-I4。計(jì)算總距離L29=SUM(L21:L28)，計(jì)算總高差M29=SUM(M21:M28)。閉合水準(zhǔn)路線閉合差限差的計(jì)算公式為，L為水準(zhǔn)路線的總距離，以公里為單位；為了保證成果的可靠性，水準(zhǔn)閉合差限差小數(shù)點(diǎn)第二位數(shù)字后全部省略）M30=ROUNDDOWN((4*SQRT((L29)/1000)),1)，水準(zhǔn)路線閉合差計(jì)算并取絕對(duì)值O30=ABS(M29*1000)，對(duì)測(cè)量成果是否合格進(jìn)行判斷P30=IF(O30>M30,“不及格”,“及格”)，并進(jìn)行標(biāo)紅提示，當(dāng)P30單元格的值等于“不及格”時(shí)，把P30的填充顏色設(shè)置為紅色。

表7 閉合水準(zhǔn)路線平差配賦表

改正數(shù)計(jì)算，計(jì)算公式：-單段距離×（閉合差÷總距離）。尾數(shù)取值規(guī)則，四舍五入。N21=-(M29/L29)*L21；N23=-(M29/L29)*L23；N25=-(M29/L29)*L25；N29=-M29-N21-N23-N25（改正數(shù)總和要與測(cè)量誤差反號(hào)的值相等，所以在最后一測(cè)段改正數(shù)進(jìn)行調(diào)整）。把改正數(shù)進(jìn)行相加N29=SUM(N21:N28)。在單元格格式中把N21、N23、N25、N27的改為5位小數(shù)的數(shù)值。

改正后的高差等于觀測(cè)高差加上改正數(shù)：O21=M21+N21；O23=M23+N23；O25=M25+N25；O27=M27+N27。改正后高差總和O30=SUM(O21:O28)。在單元格格式中把O21、O23、O25、O27的改為5位小數(shù)的數(shù)值。

高程計(jì)算，起算高程P21需要手動(dòng)輸入，P22=P21+O21，P24=P22+O23，P26=P24+O25，P28=P26+O27。在單元格格式中把P21、P22、P24、P26、P28的改為3位小數(shù)的數(shù)值。

3.2 保護(hù)表格

為了防止表格被修改出現(xiàn)錯(cuò)誤，對(duì)Excel進(jìn)行了表格保護(hù)，只允許使用者對(duì)錄入表中C2～C193單元格，以及平差配賦表中各測(cè)段的測(cè)站數(shù)F2～F5單元格、起算高程P21進(jìn)行修改，其他單元格全部寫保護(hù)，禁止修改。

4 問題與解決辦法

4.1 在計(jì)算平均高差時(shí)奇進(jìn)偶不進(jìn)的取值

在Excel表格的計(jì)算，保留位數(shù)進(jìn)位是按四舍五入法進(jìn)行，而在水準(zhǔn)測(cè)量平均高差計(jì)算中，要求按照奇進(jìn)偶不進(jìn)偶數(shù)法則對(duì)數(shù)據(jù)進(jìn)行化整位數(shù)處理[8]。利用Excel內(nèi)置函數(shù)可以解決該問題：(IF(RIGHT(TRUNC(G7,1),1)="5",IF(MOD(RIGH T(TRUNC(G7,0),1),2)=0,ROUNDDOWN(G7,0),ROUNDUP(G7,0)),ROUND(G7,0)))/100000。圖1為解決奇進(jìn)偶不進(jìn)偶數(shù)法則流程圖。

圖1 奇進(jìn)偶不進(jìn)偶數(shù)法則流程圖

4.2 使用到的函數(shù)

4.2.1 IF函數(shù)

IF函數(shù)根據(jù)指定的條件來(lái)判斷其“真”（TRUE）、“假”（FALSE），根據(jù)邏輯計(jì)算的真假值，從而返回相應(yīng)的內(nèi)容?？梢允褂肐F函數(shù)對(duì)數(shù)值和公式進(jìn)行條件檢測(cè)，IF(test,value1,value2)，IF是條件判斷函數(shù)，即如果滿足“test”測(cè)試條件，則顯示“value1”，如果不滿足“test”，則顯示“value2”?！皌est”表示計(jì)算結(jié)果為“TRUE”或“FALSE”的任意值或表達(dá)式。

4.2.2 TRUNC函數(shù)

TRUNC函數(shù)返回以指定元素格式截去一部分的值，直接對(duì)數(shù)字格式的數(shù)據(jù)進(jìn)行截?cái)?。TRUNC（numbe，[num_digits]），numbe為待截尾取整的數(shù)字，[num_digits]為需保留小數(shù)點(diǎn)后面的位數(shù)。

4.2.3 RIGHT函數(shù)

RIGHT函數(shù)的主要功能是從一個(gè)文本字符串的最后一個(gè)字符開始，截取指定數(shù)目的字符。格式為RIGHT(string,length)，string為所要取值的文本字符串，length為從右端起始指定的字節(jié)數(shù)。

4.2.4 MOD函數(shù)

MOD函數(shù)是一個(gè)求余函數(shù)，其格式為MOD(nExp1,nExp2)，即兩個(gè)數(shù)值表達(dá)式作除法運(yùn)算后的余數(shù)。特別注意，在Excel中，MOD函數(shù)是用于返回兩數(shù)相除的余數(shù)，返回結(jié)果的符號(hào)與除數(shù)（divisor）的符號(hào)相同。nExp1為被除數(shù)，nExp2為除數(shù)。

4.2.5 ROUND函數(shù)

ROUND函數(shù)按指定的位數(shù)對(duì)數(shù)值進(jìn)行四舍五入，格式為ROUND(number,num_digits)，number是要四舍五入的數(shù)字，num_digits是位數(shù)，按此位數(shù)對(duì)number參數(shù)進(jìn)行四舍五入。若要進(jìn)行向下舍入（朝向0），則使用ROUNDOWN函數(shù)[9]。

4.3 檢查表要適用不同的測(cè)站數(shù)

由于每次安排的任務(wù)不一樣，并且不同的學(xué)生測(cè)量所需的測(cè)站數(shù)是不一樣的，檢查表需要適應(yīng)不同的測(cè)站數(shù)。

首先把測(cè)站高差和距離進(jìn)行累計(jì)，水準(zhǔn)測(cè)量要求偶數(shù)站，所以只需累計(jì)偶數(shù)站的高差和距離。對(duì)測(cè)段的測(cè)站數(shù)進(jìn)行累計(jì)[10]。根據(jù)測(cè)段的測(cè)站數(shù)累計(jì)，進(jìn)行高差和距離累計(jì)值的取值。拿相鄰測(cè)段的累計(jì)高差和距離進(jìn)行相減，即可得到每一測(cè)段的高差和距離。

5 結(jié)語(yǔ)

綜上所述，本文主要解決的問題如下：

（1）在Excel中實(shí)現(xiàn)了二等水準(zhǔn)記錄表與平差配賦表的自動(dòng)計(jì)算，能適用測(cè)站數(shù)不相同的水準(zhǔn)路線；

（2）在記錄表與平差配賦表中實(shí)現(xiàn)了6個(gè)限差超限的提醒；

（3）在Excel中實(shí)現(xiàn)了奇進(jìn)偶不進(jìn)的計(jì)算規(guī)則。

應(yīng)用Excel制表進(jìn)行數(shù)據(jù)分析與處理是一個(gè)包含多個(gè)數(shù)據(jù)傳遞和處理的過程。在熟練掌握Excel制表技巧的同時(shí)，靈活使用現(xiàn)有函數(shù)自定義公式，可實(shí)現(xiàn)數(shù)據(jù)的安全傳遞。本文提供了一種解決二等水準(zhǔn)測(cè)量自動(dòng)檢查計(jì)算的方法，期望相關(guān)研究者能從中得到啟發(fā)，以提高解決類似問題的能力。