試用Excel函數實現檢測數據的修約

寸松梅，楊一云，楊國先，楊軍慧

(云南省煙草公司保山市公司，云南 保山 678000)

在檢測工作中，檢測結果是符合精密度要求的兩次測定結果的平均值，并按照數據修約規則進行修約后報出。而Excel提供的ROUND函數遵循的是四舍五入的原則，修約規則遵循的是四舍六入五考慮的原則，即用Excel現成的函數無法實現檢測數據的修約，一般情況下只能采取人工修約。當檢測樣品數量在20個以上，要對每個樣品的各檢測項目的檢測平均值進行人工修約時，每個數據都要使用一遍修約規則，一是費時，二是容易疏漏，三是疏漏后不容易檢查，給檢測人員帶來了很大的麻煩。為了解決這一問題，筆者應用 Excel中的 IF、AND、INT、EVEN、FIXED、ROUND函數編寫命令，使Excel應用軟件在數據處理時能按數據修約規則進行修約。

1 修約規定及檢測數據的報出要求

當擬舍棄數據的最左一位數字為5，且其后無數字或皆為0時，若所保留的末位數字為奇數(1，3，5，7，9)則進一;若所保留的末位數字為偶數 (0，2，4，6，8)，則舍去［1］。檢測數據的報出以兩次測定的平均值作為測定結果，測得的項目含量≥10.0%，結果精確至 0.1%［2］; ＜10.0%，結果精確至 0.01%［2～6］。

2 數據的分解

按檢測數據的報出要求，將檢測數據分解為≥10.0%與＜10.0%兩部分，然后又分別對每一部分進行分解。

如圖1所示，這兩部分數據的分解是相同的，所不同之處僅在于小數位數的不同，≥10%的考慮的是小數點后第二位數的進舍，＜10%的考慮的是小數點后第三位數的進舍。

按欲舍棄數字的最左一位＜5、=5、＞5將檢測數據分成三個部分，其中欲舍棄數字的最左一位＜5、＞5的這兩部分數據按四舍五入原則進行修約;欲舍棄數字的最左一位=5的這一部分數據，又分成5右邊的數字為零或不為零兩部分，其中5右邊的數字不為零的數據按四舍五入原則進行修約，5右邊無數字或為零的又可分成保留的末位數為偶數或奇數兩個部分，其中保留的末位數為奇數時5就要進，保留末位數為偶數時5就要舍。

3 數據的處理及命令的編寫

3.1 數據的篩選處理

按照數據分解的思路，先考慮≥10這一部分，將數據分成10～20、20～30、30～40等幾個段，這幾個段的數據都是小數點后兩位數從00逐漸逼近99，我們主要看每一段中小數點后第二位數是5并且5后邊沒有數字的數據，也就是說發生進或舍的是小數點后第二位的數字，小數點前面的數字不存在進舍也就不存在變化，因此我們只要找出其中一段的處理方法，就可以解決其他段。推而廣之，＜10的檢測數據同樣可以這樣分段，只不過考慮的是小數點后第三位的數字。

以10.00～10.99的數據為例來進行篩選。如表1所示，先將小數點后第二位數是5，并且它后邊的數全部為零或沒有數字的這一類數據篩選出來。因為保留位數為小數點后一位，考慮的是小數點后兩位的數字。

通過觀察，當數據擴大10倍并且再乘以2時，只有小數點后一位數是0或5并且0或5后邊無數字或數字全為零的結果是整數，其它的數據 (包括小數點后第二位是5但其后邊有數字的)乘以2以后的結果均不成整數，因此對結果進行取整，看取整與不取整的結果是否相等，將小數點后第二位數是0或5并且0或5后邊沒有數字的數據篩選出來，即 10.00、10.05、10.10、10.15、10.20、10.25、10.30、10.35、10.40、10.45、10.50、10.55、10.60、10.65、10.70、10.75、10.80、10.85、10.90、10.95。再觀察篩選出來的數據，發現小數點后第二位是5的數乘以10的結果不是整數，而小數點后第二位數是0的數乘以10以后是整數，因此對結果進行取整，令檢測結果乘以10取整后的結果小于檢測結果乘以10，就將小數點后第二位是5并且5后邊無數字或全為零的數據篩選出來，即10.05、10.15、 10.25、 10.35、 10.45、 10.55、 10.65、10.75、10.85、10.95。

3.2 數據的分析處理

3.2.1 ≥10的處理

數據篩選完成后，考慮的就是篩選出來的數據的處理。按照規則要求，小數點后一位是奇數的使5向前進一，小數點后一位是偶數的將5舍去。

再如表1所示，來看篩選出來的數據乘以10以后的結果，即 100.5、101.5、102.5、103.5、104.5、105.5、106.5、107.5、108.5、109.5，數據乘以10以后就全部變成小數點后一位數為5。將結果取整，這時小數點后第二位的5就全部被舍去，變成整數100～109。若再將結果向上取偶數，由于取的是向上最接近的偶數，因此取整后的結果是:偶數的仍然不變 (如100取整為100、102取整為102、104取整為104、106取整為106、108取整為108)，取整是奇數的則被向上進一成為偶數 (如101取整為102、103取整為104、105取整為106、107取整為108、109取整為110)，這時將取整結果再除以10，將檢測數據還原為10.0、10.2、10.4、10.6、10.8、11.0，實現了小數點后第一位是偶數的其小數點后第二位的5被舍去，而小數點后第一位是奇數的其小數點后第二位的5被向前進一。

除篩選出來以外的數據，按四舍五入原則進行修約，保留一位小數。

為了使數據中的整數返回結果為小數點后兩位，將結果全部按規定的位數取整。

3.2.2 ＜10的數據的處理

＜10的數據的篩選和處理方法與≥10的相同，不同之處在于因數據保留小數點后兩位小數，因此將條件中的乘以20改為乘以200，乘以10改為乘以100后，同樣實現小數點后第三位數為5并且5后邊無數字或全為零，小數點后第二位是偶數時5

被舍去，小數點后第二位是奇數時5向前進一，其他數據執行四舍五入原則，保留小數點后兩位。為了使數據中的整數返回結果為小數點后為兩位數，將結果全部按規定的位數取整。

表1 數據10.00～10.99篩選處理過程

除篩選出來以外的數據，按四舍五入原則進行修約，保留兩位小數。

為了使數據中的整數返回結果為小數點后兩位，將結果全部按規定的位數取整。

3.3 命令的編寫

綜上所述，應用Excel的真假值判斷函數IF、條件檢驗函數AND、取整函數INT、向上取偶函數EVEN、四舍五入函數ROUND、按小數位數取整函數FIXED，將命令編寫為:

=IF(number＞ =10，FIXED(IF(AND(INT(number*20) = (number*20)，INT(number*10) ＜ (number*10))，EVEN(INT(number*10))/10， ROUND(number，1))，1)，FIXED(IF(AND(INT(number*200) =(number*200)，INT(number*100) ＜ (number*100))，EVEN(INT(number*100))/100，ROUND(number，2))，2))

表2 所編命令在實際工作中的應用效果

即當數據≥10，如同時滿足數據乘以20的結果與乘以20后取整的結果相等，并且數據乘以10取整的結果小于數據乘以10后的結果時，返回數據乘以10取整后向上取偶數再除以10的結果，如果不能同時滿足條件的就返回四舍五入的結果，保留一位小數，且結果全部按保留一位小數取整;當數據不是≥10，如同時滿足數據乘以200的結果與乘以200后取整的結果相等，并且數據乘以100取整的結果小于數據乘以100后的結果時，返回數據乘以100取整后向上取偶數再除以100的結果，不能同時滿足條件的就返回四舍五入的結果，保留兩位小數，且結果全部按保留兩位小數取整。其中的number指的是數據所在單元格的位置。

4 應用驗證

4.1 應用效果驗證

隨機抽取30個樣品的180個檢測平均值進行少量驗證。表2給出了平均、修約和差值的統計情況，其中:平均就是各個樣品各項目的檢測平均值，修約就是將平均值應用所編命令處理后所得的值，差值就是修約和平均之間的差，通過差值的范圍分析進或舍的情況，同時在差值一欄應用數據的篩選功能將符合五考慮原則的數據篩選出來，來檢查處理后的數據是否滿足我們的要求，以驗證所命令的應用效果。通過人工復核后證明，應用所編命令的數據處理準確率達到100%，完全達到數據修約的規則要求。

4.2 應用穩定性驗證

再將199個樣品的1194個檢測數據應用所編命令進行處理，以考查其穩定性。通過應用，這1194個檢測數據經處理后所返回的結果完全遵守數據修約規定，數據處理的準確率達到100%，證明此命令的應用穩定性較好。

以上驗證表明，此命令完全滿足數據修約規則的要求，對數據的處理準確、快速，在檢測數據的處理上具有較強的實用價值。

5 結論

應用 Excel中 的 IF、AND、INT、EVEN、FIXED、ROUND函數編寫命令:=IF(number＞=10，FIXED(IF(AND(INT(number*20) =(number*20)，INT(number*10) ＜ (number* 10))， EVEN (INT(number* 10))/10，ROUND(number，1))，1)，FIXED(IF(AND(INT(number*200) = (number*200)，INT(number*100) ＜ (number*100))，EVEN(INT(number*100))/100，ROUND(number，2))，2))，可完全滿足數據修約規則的要求，對數據的處理準確、穩定，具有極強的可操作性和實用價值。

［1］GB/T8170－2008，數據修約規則與極限數值的表示和判定［S］．

［2］YC/T 159－2002，煙草及煙草制品 水溶性糖的測定 連續流動分析法 ［S］．

［3］YC/T 160－2002，煙草及煙草制品總植物堿的測定連續流動分析法 ［S］．

［4］YC/T 161－2002，煙草及煙草制品 總氮的測定 連續流動分析法 ［S］．

［5］YC/T 162－2002，煙草及煙草制品 氯的測定 連續流動分析法［S］．

［6］YC/T 217－2007，煙草及煙草制品鉀的測定連續流動分析法［S］．