一种利用Excel实现硬度计量数据修约的方法

蒋帆 刘一 李业/上海市计量测试技术研究院

0 引言

Excel 是目前最常用的数据统计工具之一，它更方便于数据处理和数据分析工作，为计量工作无纸化提供了很大便利。大量数据处理工作可以由Excel快速实现，但是Excel是按照“四舍五入”这个最基本的规则来保留数据计算位数[1]，而在计量科学中要求对数据采取“四舍六入，逢五奇进偶舍”的修约方法处理，所以常常会出现人工计算与计算机计算不符的现象，有时甚至会影响结果的判定[2]。为了保证数据处理的准确可靠，有些工程师提出判断保留末位奇偶的方法[3]，不过过程比较复杂，而且没有给出实现修约的具体解决方案。有些机构在设备精度有限的前提下，根据其数据的特点，提出了保留两位有效数字或三位有效数字的解决方案[4]。但是，随着科学的发展和技术的进步，计量设备的准确度不断提高，因此，测量结果对数据处理提出更高的要求。

本文通过工作中的问题报告，说明Excel参与计算的数据会受到前一步计算的影响，导致系统结果偏差较大，增加报告出错的可能性。根据预设保留的小数位数建立定位函数，达到准确定位修约的拟舍弃位和保留末位，实现“四舍六入、逢五取偶”，快速得出修约结果的方法，为利用Excel进行计量数据处理提供一种解决方案。

1 实例

在计量检测过程中，工程师通过对试验得到的原始数据进行处理，得到需要的检测结果，其中包括重复性、稳定性、均匀度等结果。标准洛氏硬度块的稳定性是表征其质量的一个重要指标。根据国家标准，标准硬度块的标定应该在其工作面均匀分布地压出5个压痕，标准洛氏硬度块的硬度值为各点压痕硬度值的算术平均值[5]。标准块的均匀度是指测得各点硬度值之间的最大差值，标准块的稳定性用前后两次测量所得硬度平均值之差的绝对值表示。

利用Excel对一块标准洛氏硬度块的测量数据进行计算。根据国家检定规程，标准硬度块的硬度值应符合检定规程中对标准块均匀度和稳定性等计量性能要求[6]，当硬度值在60～70 HRC范围内，硬度块的稳定性和均匀度要求不大于0.4 HRC。因此，对数据修约，应该保留一位小数，标准块硬度值的处理结果见表1。

表1每个单元格由相应的列和行表示，列数为A～K，行数为1～2，其中A2～E2为本次测量得到的5点硬度值，H2为标准块上次检定的硬度值，硬度单位J2为HRC，计算结果保留一位小数，其他数据在Excel中定义计算公式如下：

本次检定标准洛氏硬度块的硬度值：F2=TEXT(AVERAGE(A2:E2),"0.0")；

表1 洛氏硬度块硬度值处理结果 单位：HRC

本次检定标准洛氏硬度块的均匀度：G2=TEXT(MAX(A2:E2)-MIN(A2:E2),"0.0")；

本次检定标准洛氏硬度块的稳定性：I2=TEXT(ABS(H2-F2),"0.0")。

按照以上计算公式进行数据处理，本次检定结果为合格。而在原始数据处理过程中，计算得到本次检定标准块5点硬度值的算术平均值为61.45 HRC，按照GB/T 8170-2008《数值修约规则与极限数值的表示和判定》的修约要求，当取用精度位数后一位数字是5且尾数为0时，需要观察前位的奇偶性，修约后，本次检定的标准块硬度值应该为61.4 HRC，稳定性为0.5 HRC，此硬度块的检定结果为不合格。Excel中对数据的处理过程采用“四舍五入”，导致结果系统偏向大数，由此可见，如果不对数据进行正确修约，就有可能对后续计算以及结果判定产生影响。

2 数据修约要求及函数的编写

根据数值修约规则的要求[7]：

1）拟舍弃数字的最左一位数字小于5，则舍去，保留其余各位数字不变；

2）拟舍弃数字的最左一位数字大于5，则进一，即保留数字的末尾数字加1；

3）拟舍弃数字的最左一位数字是5，且其后有非零数字时进一，即保留数字的末位数字加1；

4）拟舍弃数字的最左一位数字为5，且其后无非零数字时，若所保留的末位数字为奇数（1、3、5、7、9）则进一，即保留数字的末位数字加1；若所保留的末位数字为偶数（0，2，4，6，8），则舍去；

5）负数修约时，先将它的绝对值按1～4的规定进行修约，然后在所得值前面加上负号。

由于负数与正数的修约规则一致，仅添加了负号，这种结果在Excel中通过绝对值函数很容易实现，所以本文不单独对负数进行分析，在不考虑负数的情况下，修约的逻辑关系如图1所示。

如图1所示，深色代表逻辑判断，浅色可以用相关的函数进行计算，以此为逻辑关系，实现Excel数值修约。为了实现以上判断逻辑，同时兼顾更灵活的修约位数，通过字符串的位置，引入定位函数INT(MID(F2,FIND(".",F2)+K2+1,1)，确定拟舍弃位的最左数字，定位函数是通过查找数据修约的小数位数定位到拟舍弃数字，所以原始数据需要包含小数或以科学计数法的形式储存。

图1 数据修约的逻辑关系

通过 IF(INT(MID(F2,FIND(".",F2)+K2+1,1)) = 5 判断拟舍弃位的最左数字是否为5。如果不为5，通过ROUND(F2,K2)函数，“四舍五入”直接进行修约。如果为 5，通过 IF(F2-ROUND(F2,K2+1) = 0，判断5后是否有其他数字。如果5后有其他数字，则选择进位修约，此时，进位修约与“四舍五入”的规则相同，采用ROUND(F2,K2)进行修约。如果5后再无其他数字，不但要判断保留末位奇偶，而且应该给出最后的修约结果，通过MOD(INT(MID(F2,FIN D(".",F2)+K2,1)),2)*0.1^K2+ROUNDDOWN(F2,K2)，实现“逢五奇进偶舍”，给出符合测量结果修约规则的最终结果，并可以参与后续数据处理。如果原始数据是由设备自动导入，有可能出现拟舍弃位都为0的情况，此时定位函数受本身定义所限无法运行。为了避免此类情况发生，需要在以上函数前嵌入判断函数，引入空集的概念，通过IF(MID(F2,FIND(".",F2)+K2+1,1)=""，判断拟舍弃位是否都为0，利用ROUND(F2,K2)函数输出数据，避免定位函数报错。如果数据为负数，则将数据的绝对值进行修约，最后在所得值前面加上负号即可。

3 结语

本文通过定位函数和逻辑判定，在Excel中提供了一种更符合硬度计量测量结果数字修约规则的方法，实现对数据“四舍六入、逢五奇进偶舍”的修约规则。此方法可以根据具体情况，定义修约的位数，减少数据处理判断时间，具有普遍的适用性，为计量数据处理的数字化和自动化提供一种解决方案。本方法亦有其局限性，针对没有小数点的数据，定位函数无法定位到拟舍弃位和保留末位，需要以科学计数法的形式记录数据才能进行修约。