蔣帆 劉一 李業(yè)/上海市計量測試技術研究院

0 引言

Excel 是目前最常用的數(shù)據統(tǒng)計工具之一，它更方便于數(shù)據處理和數(shù)據分析工作，為計量工作無紙化提供了很大便利。大量數(shù)據處理工作可以由Excel快速實現(xiàn)，但是Excel是按照“四舍五入”這個最基本的規(guī)則來保留數(shù)據計算位數(shù)[1]，而在計量科學中要求對數(shù)據采取“四舍六入，逢五奇進偶舍”的修約方法處理，所以常常會出現(xiàn)人工計算與計算機計算不符的現(xiàn)象，有時甚至會影響結果的判定[2]。為了保證數(shù)據處理的準確可靠，有些工程師提出判斷保留末位奇偶的方法[3]，不過過程比較復雜，而且沒有給出實現(xiàn)修約的具體解決方案。有些機構在設備精度有限的前提下，根據其數(shù)據的特點，提出了保留兩位有效數(shù)字或三位有效數(shù)字的解決方案[4]。但是，隨著科學的發(fā)展和技術的進步，計量設備的準確度不斷提高，因此，測量結果對數(shù)據處理提出更高的要求。

本文通過工作中的問題報告，說明Excel參與計算的數(shù)據會受到前一步計算的影響，導致系統(tǒng)結果偏差較大，增加報告出錯的可能性。根據預設保留的小數(shù)位數(shù)建立定位函數(shù)，達到準確定位修約的擬舍棄位和保留末位，實現(xiàn)“四舍六入、逢五取偶”，快速得出修約結果的方法，為利用Excel進行計量數(shù)據處理提供一種解決方案。

1 實例

在計量檢測過程中，工程師通過對試驗得到的原始數(shù)據進行處理，得到需要的檢測結果，其中包括重復性、穩(wěn)定性、均勻度等結果。標準洛氏硬度塊的穩(wěn)定性是表征其質量的一個重要指標。根據國家標準，標準硬度塊的標定應該在其工作面均勻分布地壓出5個壓痕，標準洛氏硬度塊的硬度值為各點壓痕硬度值的算術平均值[5]。標準塊的均勻度是指測得各點硬度值之間的最大差值，標準塊的穩(wěn)定性用前后兩次測量所得硬度平均值之差的絕對值表示。

利用Excel對一塊標準洛氏硬度塊的測量數(shù)據進行計算。根據國家檢定規(guī)程，標準硬度塊的硬度值應符合檢定規(guī)程中對標準塊均勻度和穩(wěn)定性等計量性能要求[6]，當硬度值在60～70 HRC范圍內，硬度塊的穩(wěn)定性和均勻度要求不大于0.4 HRC。因此，對數(shù)據修約，應該保留一位小數(shù)，標準塊硬度值的處理結果見表1。

表1每個單元格由相應的列和行表示，列數(shù)為A～K，行數(shù)為1～2，其中A2～E2為本次測量得到的5點硬度值，H2為標準塊上次檢定的硬度值，硬度單位J2為HRC，計算結果保留一位小數(shù)，其他數(shù)據在Excel中定義計算公式如下：

本次檢定標準洛氏硬度塊的硬度值：F2=TEXT(AVERAGE(A2:E2),"0.0")；

表1 洛氏硬度塊硬度值處理結果 單位：HRC

本次檢定標準洛氏硬度塊的均勻度：G2=TEXT(MAX(A2:E2)-MIN(A2:E2),"0.0")；

本次檢定標準洛氏硬度塊的穩(wěn)定性：I2=TEXT(ABS(H2-F2),"0.0")。

按照以上計算公式進行數(shù)據處理，本次檢定結果為合格。而在原始數(shù)據處理過程中，計算得到本次檢定標準塊5點硬度值的算術平均值為61.45 HRC，按照GB/T 8170-2008《數(shù)值修約規(guī)則與極限數(shù)值的表示和判定》的修約要求，當取用精度位數(shù)后一位數(shù)字是5且尾數(shù)為0時，需要觀察前位的奇偶性，修約后，本次檢定的標準塊硬度值應該為61.4 HRC，穩(wěn)定性為0.5 HRC，此硬度塊的檢定結果為不合格。Excel中對數(shù)據的處理過程采用“四舍五入”，導致結果系統(tǒng)偏向大數(shù)，由此可見，如果不對數(shù)據進行正確修約，就有可能對后續(xù)計算以及結果判定產生影響。

2 數(shù)據修約要求及函數(shù)的編寫

根據數(shù)值修約規(guī)則的要求[7]：

1）擬舍棄數(shù)字的最左一位數(shù)字小于5，則舍去，保留其余各位數(shù)字不變；

2）擬舍棄數(shù)字的最左一位數(shù)字大于5，則進一，即保留數(shù)字的末尾數(shù)字加1；

3）擬舍棄數(shù)字的最左一位數(shù)字是5，且其后有非零數(shù)字時進一，即保留數(shù)字的末位數(shù)字加1；

4）擬舍棄數(shù)字的最左一位數(shù)字為5，且其后無非零數(shù)字時，若所保留的末位數(shù)字為奇數(shù)（1、3、5、7、9）則進一，即保留數(shù)字的末位數(shù)字加1；若所保留的末位數(shù)字為偶數(shù)（0，2，4，6，8），則舍去；

5）負數(shù)修約時，先將它的絕對值按1～4的規(guī)定進行修約，然后在所得值前面加上負號。

由于負數(shù)與正數(shù)的修約規(guī)則一致，僅添加了負號，這種結果在Excel中通過絕對值函數(shù)很容易實現(xiàn)，所以本文不單獨對負數(shù)進行分析，在不考慮負數(shù)的情況下，修約的邏輯關系如圖1所示。

如圖1所示，深色代表邏輯判斷，淺色可以用相關的函數(shù)進行計算，以此為邏輯關系，實現(xiàn)Excel數(shù)值修約。為了實現(xiàn)以上判斷邏輯，同時兼顧更靈活的修約位數(shù)，通過字符串的位置，引入定位函數(shù)INT(MID(F2,FIND(".",F2)+K2+1,1)，確定擬舍棄位的最左數(shù)字，定位函數(shù)是通過查找數(shù)據修約的小數(shù)位數(shù)定位到擬舍棄數(shù)字，所以原始數(shù)據需要包含小數(shù)或以科學計數(shù)法的形式儲存。

圖1 數(shù)據修約的邏輯關系

通過 IF(INT(MID(F2,FIND(".",F2)+K2+1,1)) = 5 判斷擬舍棄位的最左數(shù)字是否為5。如果不為5，通過ROUND(F2,K2)函數(shù)，“四舍五入”直接進行修約。如果為 5，通過 IF(F2-ROUND(F2,K2+1) = 0，判斷5后是否有其他數(shù)字。如果5后有其他數(shù)字，則選擇進位修約，此時，進位修約與“四舍五入”的規(guī)則相同，采用ROUND(F2,K2)進行修約。如果5后再無其他數(shù)字，不但要判斷保留末位奇偶，而且應該給出最后的修約結果，通過MOD(INT(MID(F2,FIN D(".",F2)+K2,1)),2)*0.1^K2+ROUNDDOWN(F2,K2)，實現(xiàn)“逢五奇進偶舍”，給出符合測量結果修約規(guī)則的最終結果，并可以參與后續(xù)數(shù)據處理。如果原始數(shù)據是由設備自動導入，有可能出現(xiàn)擬舍棄位都為0的情況，此時定位函數(shù)受本身定義所限無法運行。為了避免此類情況發(fā)生，需要在以上函數(shù)前嵌入判斷函數(shù)，引入空集的概念，通過IF(MID(F2,FIND(".",F2)+K2+1,1)=""，判斷擬舍棄位是否都為0，利用ROUND(F2,K2)函數(shù)輸出數(shù)據，避免定位函數(shù)報錯。如果數(shù)據為負數(shù)，則將數(shù)據的絕對值進行修約，最后在所得值前面加上負號即可。

3 結語

本文通過定位函數(shù)和邏輯判定，在Excel中提供了一種更符合硬度計量測量結果數(shù)字修約規(guī)則的方法，實現(xiàn)對數(shù)據“四舍六入、逢五奇進偶舍”的修約規(guī)則。此方法可以根據具體情況，定義修約的位數(shù)，減少數(shù)據處理判斷時間，具有普遍的適用性，為計量數(shù)據處理的數(shù)字化和自動化提供一種解決方案。本方法亦有其局限性，針對沒有小數(shù)點的數(shù)據，定位函數(shù)無法定位到擬舍棄位和保留末位，需要以科學計數(shù)法的形式記錄數(shù)據才能進行修約。