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