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制表技巧的同时，灵活使用现有函数自定义公式，可实现数据的安全传递。本文提供了一种解决二等水准测量自动检查计算的方法，期望相关研究者能从中得到启发，以提高解决类似问题的能力。