Excel在二等水準測量自動檢查計算中的應用

陳德紹 羅桂發

（柳州鐵道職業技術學院,廣西 柳州 545616）

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

本文將針對在Excel中二等水準測量自動計算檢查的實現進行研究，通過分析二等水準測量的要求、競賽規則以及學生學習的特點進行功能需求分析，并對Excel函數進行研究，以實現功能需求。

1 調查問卷分析

1.1 界面需求分析

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

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

表1 二等水準測量手簿

表2 水準平差配賦表

表3 錄入表

1.2 技術難點分析

測量手簿需要完成自動計算，并按手簿的格式要求填寫。對不符合二等水準測量技術要求的數據要進行標記。對測量數據進行平差計算，判斷測量成果是否合格，并填寫完成平差配賦表。平均高差取值時尾數逢五奇進偶不進的計算規則、每測段的測站數不確定性對水平配賦表的影響。

2 Excel記錄手簿的自動計算

2.1 二等水準測量手簿的自動計算

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

數據提取到測量手簿后，需要對測量手簿數據進行計算。視距差單元格B6=B4-C4；累計視距差C6=B6（第一測站累計視距差等于視距差，第二測站累計視距差等于上一測站的累計視距差加上本測站的視距差）；兩次讀數差G4=E4-F4；G5=E5-F5；高差E6=E4-E5；F6=F4-F5；高差之差G6=E6-F6；把高差之差G6最后一位數進行奇進偶不進取整，并換算成以米為單位(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。

對單元格數值取位與精度進行設置，數據格式全部設置為數值，距離以米為單位，取到小數點后1位，標尺讀數取整，平均高差以米為單位，取到小數點后5位。

運用同樣的運算規則，可以對剩下的測站進行設置。

2.2 測量限差超限的提示

在二等水準測量手簿中有5個限差（如表4），單元格對其數值進行判斷是否超限，如果超限則把單元填充顏色改為紅色作為提醒。

表4 二等水準測量技術要求（2 m水準標尺）

在“條件格式”→“突出顯示單元格規則”中對相應的單元格進行設置。視線長度限差：當B4單元格的值未介于3和50之間時，把B4的填充顏色設置為紅色，C4單元格設置與B4一致。前后視距差限差：當B6單元格的值未介于-1.5和1.5之間時，把B6的填充顏色設置為紅色。前后視距累計差限差：當C6單元格的值未介于-6和6之間時，把C6的填充顏色設置為紅色。視線高度限差：單元格E4、E5、F4、F5的單元格規則一致，單元格的值未介于055 000和180 000之間時，把填充顏色設置為紅色。兩次讀數所得高差之差限差：當單元格G6的值未介于-60和60之間時，把G6的填充顏色設置為紅色。

運用同樣的運算規則，可以對剩下的測站進行設置。

3 閉合水準路線平差配賦表的自動計算

3.1 各測站的距離和高差進行累計

由于每段的水準測站數均為偶數，所以只需對偶數站進行累計。

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

表5 距離高差統計

3.1.1 各測段累計距離與累計高差的提取

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

表6 各測段測站數輸入與數據提取

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

根據表5與表6表格的數據，進行各測段累計高差的提取。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 閉合水準路線平差配賦表的自動計算

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

表7 閉合水準路線平差配賦表

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

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

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

3.2 保護表格

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

4 問題與解決辦法

4.1 在計算平均高差時奇進偶不進的取值

在Excel表格的計算，保留位數進位是按四舍五入法進行，而在水準測量平均高差計算中，要求按照奇進偶不進偶數法則對數據進行化整位數處理[8]。利用Excel內置函數可以解決該問題：(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為解決奇進偶不進偶數法則流程圖。

圖1 奇進偶不進偶數法則流程圖

4.2 使用到的函數

4.2.1 IF函數

IF函數根據指定的條件來判斷其“真”（TRUE）、“假”（FALSE），根據邏輯計算的真假值，從而返回相應的內容。可以使用IF函數對數值和公式進行條件檢測，IF(test,value1,value2)，IF是條件判斷函數，即如果滿足“test”測試條件，則顯示“value1”，如果不滿足“test”，則顯示“value2”。“test”表示計算結果為“TRUE”或“FALSE”的任意值或表達式。

4.2.2 TRUNC函數

TRUNC函數返回以指定元素格式截去一部分的值，直接對數字格式的數據進行截斷。TRUNC（numbe，[num_digits]），numbe為待截尾取整的數字，[num_digits]為需保留小數點后面的位數。

4.2.3 RIGHT函數

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

4.2.4 MOD函數

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

4.2.5 ROUND函數

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

4.3 檢查表要適用不同的測站數

由于每次安排的任務不一樣，并且不同的學生測量所需的測站數是不一樣的，檢查表需要適應不同的測站數。

首先把測站高差和距離進行累計，水準測量要求偶數站，所以只需累計偶數站的高差和距離。對測段的測站數進行累計[10]。根據測段的測站數累計，進行高差和距離累計值的取值。拿相鄰測段的累計高差和距離進行相減，即可得到每一測段的高差和距離。

5 結語

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

（1）在Excel中實現了二等水準記錄表與平差配賦表的自動計算，能適用測站數不相同的水準路線；

（2）在記錄表與平差配賦表中實現了6個限差超限的提醒；

（3）在Excel中實現了奇進偶不進的計算規則。

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