一種利用Excel實(shí)現(xiàn)硬度計(jì)量數(shù)據(jù)修約的方法

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

0 引言

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

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

1 實(shí)例

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

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

表1每個(gè)單元格由相應(yīng)的列和行表示，列數(shù)為A～K，行數(shù)為1～2，其中A2～E2為本次測(cè)量得到的5點(diǎn)硬度值，H2為標(biāo)準(zhǔn)塊上次檢定的硬度值，硬度單位J2為HRC，計(jì)算結(jié)果保留一位小數(shù)，其他數(shù)據(jù)在Excel中定義計(jì)算公式如下：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

圖1 數(shù)據(jù)修約的邏輯關(guān)系

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

3 結(jié)語(yǔ)

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