試用Excel函數(shù)實現(xiàn)檢測數(shù)據(jù)的修約

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

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

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

1 修約規(guī)定及檢測數(shù)據(jù)的報出要求

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

2 數(shù)據(jù)的分解

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

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

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

3 數(shù)據(jù)的處理及命令的編寫

3.1 數(shù)據(jù)的篩選處理

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

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

通過觀察，當(dāng)數(shù)據(jù)擴(kuò)大10倍并且再乘以2時，只有小數(shù)點后一位數(shù)是0或5并且0或5后邊無數(shù)字或數(shù)字全為零的結(jié)果是整數(shù)，其它的數(shù)據(jù) (包括小數(shù)點后第二位是5但其后邊有數(shù)字的)乘以2以后的結(jié)果均不成整數(shù)，因此對結(jié)果進(jìn)行取整，看取整與不取整的結(jié)果是否相等，將小數(shù)點后第二位數(shù)是0或5并且0或5后邊沒有數(shù)字的數(shù)據(jù)篩選出來，即 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。再觀察篩選出來的數(shù)據(jù)，發(fā)現(xiàn)小數(shù)點后第二位是5的數(shù)乘以10的結(jié)果不是整數(shù)，而小數(shù)點后第二位數(shù)是0的數(shù)乘以10以后是整數(shù)，因此對結(jié)果進(jìn)行取整，令檢測結(jié)果乘以10取整后的結(jié)果小于檢測結(jié)果乘以10，就將小數(shù)點后第二位是5并且5后邊無數(shù)字或全為零的數(shù)據(jù)篩選出來，即10.05、10.15、 10.25、 10.35、 10.45、 10.55、 10.65、10.75、10.85、10.95。

3.2 數(shù)據(jù)的分析處理

3.2.1 ≥10的處理

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

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

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

為了使數(shù)據(jù)中的整數(shù)返回結(jié)果為小數(shù)點后兩位，將結(jié)果全部按規(guī)定的位數(shù)取整。

3.2.2 ＜10的數(shù)據(jù)的處理

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

被舍去，小數(shù)點后第二位是奇數(shù)時5向前進(jìn)一，其他數(shù)據(jù)執(zhí)行四舍五入原則，保留小數(shù)點后兩位。為了使數(shù)據(jù)中的整數(shù)返回結(jié)果為小數(shù)點后為兩位數(shù)，將結(jié)果全部按規(guī)定的位數(shù)取整。

表1 數(shù)據(jù)10.00～10.99篩選處理過程

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

為了使數(shù)據(jù)中的整數(shù)返回結(jié)果為小數(shù)點后兩位，將結(jié)果全部按規(guī)定的位數(shù)取整。

3.3 命令的編寫

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

=IF(number＞ =10，F(xiàn)IXED(IF(AND(INT(number*20) = (number*20)，INT(number*10) ＜ (number*10))，EVEN(INT(number*10))/10， ROUND(number，1))，1)，F(xiàn)IXED(IF(AND(INT(number*200) =(number*200)，INT(number*100) ＜ (number*100))，EVEN(INT(number*100))/100，ROUND(number，2))，2))

表2 所編命令在實際工作中的應(yīng)用效果

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

4 應(yīng)用驗證

4.1 應(yīng)用效果驗證

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

4.2 應(yīng)用穩(wěn)定性驗證

再將199個樣品的1194個檢測數(shù)據(jù)應(yīng)用所編命令進(jìn)行處理，以考查其穩(wěn)定性。通過應(yīng)用，這1194個檢測數(shù)據(jù)經(jīng)處理后所返回的結(jié)果完全遵守數(shù)據(jù)修約規(guī)定，數(shù)據(jù)處理的準(zhǔn)確率達(dá)到100%，證明此命令的應(yīng)用穩(wěn)定性較好。

以上驗證表明，此命令完全滿足數(shù)據(jù)修約規(guī)則的要求，對數(shù)據(jù)的處理準(zhǔn)確、快速，在檢測數(shù)據(jù)的處理上具有較強(qiáng)的實用價值。

5 結(jié)論

應(yīng)用 Excel中 的 IF、AND、INT、EVEN、FIXED、ROUND函數(shù)編寫命令:=IF(number＞=10，F(xiàn)IXED(IF(AND(INT(number*20) =(number*20)，INT(number*10) ＜ (number* 10))， EVEN (INT(number* 10))/10，ROUND(number，1))，1)，F(xiàn)IXED(IF(AND(INT(number*200) = (number*200)，INT(number*100) ＜ (number*100))，EVEN(INT(number*100))/100，ROUND(number，2))，2))，可完全滿足數(shù)據(jù)修約規(guī)則的要求，對數(shù)據(jù)的處理準(zhǔn)確、穩(wěn)定，具有極強(qiáng)的可操作性和實用價值。

［1］GB/T8170－2008，數(shù)據(jù)修約規(guī)則與極限數(shù)值的表示和判定［S］．

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

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

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

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

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