黃文超

?

利用Excel自定義函數(shù)同時(shí)滿足多種數(shù)據(jù)處理要求

黃文超

三明市環(huán)境監(jiān)測(cè)站

通過Excel軟件中的自定義函數(shù)功能，使Excel表格的計(jì)算功能符合環(huán)境監(jiān)測(cè)質(zhì)量保證中對(duì)小數(shù)點(diǎn)位數(shù)、有效位數(shù)、四舍六入等數(shù)據(jù)處理要求，充分發(fā)揮Excel軟件的普及性和易用性，提高環(huán)境監(jiān)測(cè)數(shù)據(jù)處理時(shí)的自動(dòng)化水平及工作效率。

數(shù)據(jù)處理 自定義函數(shù) 小數(shù)點(diǎn)位數(shù) 有效位數(shù) 四舍六入

1 背景介紹

Excel作為日常辦公數(shù)據(jù)處理軟件，具有巨大的優(yōu)勢(shì)，它普及性廣，易用性強(qiáng)，是環(huán)境監(jiān)測(cè)數(shù)據(jù)處理中重要的工具軟件之一。但環(huán)境監(jiān)測(cè)質(zhì)量保證中對(duì)數(shù)據(jù)處理有更高的要求，比如數(shù)據(jù)四舍六入處理的要求，保留小數(shù)點(diǎn)位數(shù)的要求，保留有效位數(shù)的要求，這些都限制了Excel在監(jiān)測(cè)數(shù)據(jù)處理中的使用。通過Excel自帶的VBA編程語(yǔ)言，我們能夠極大地?cái)U(kuò)展Excel的功能，定制出符合環(huán)境監(jiān)測(cè)質(zhì)量保證所要求的自定義函數(shù)。

目前用VBA定制的四舍六入的函數(shù)很多，但同時(shí)包含處理保留小數(shù)點(diǎn)位數(shù)要求及保留有效位數(shù)要求的自定義函數(shù)還沒有見過，由于筆者在日常工作中經(jīng)常需要對(duì)監(jiān)測(cè)數(shù)據(jù)進(jìn)行處理，因此自己編寫了滿足質(zhì)控要求的自定義函數(shù)，在此基礎(chǔ)上還加入了對(duì)不同項(xiàng)目，相同項(xiàng)目不同方法的處理，最核心的程序和思路將在下文介紹。

2 程序編制

首先打開Excel程序（以Excel2003為例），在工具欄中點(diǎn)擊“工具”，在下拉菜單中選擇“宏”，在其右側(cè)彈出菜單中選擇“Visual Basic 編輯器”進(jìn)入，點(diǎn)擊工具欄上的“插入”菜單，選擇“模塊”，就可以開始自定義函數(shù)編輯工作了。

Public Function datafunction(onum As Double, d As Integer, a As Integer) As Variant

'datafunction: 自定義函數(shù)名，并且將其結(jié)果按字符數(shù)據(jù)類型輸出。onum為引用參數(shù)，即需處理的數(shù)據(jù)，d為分析項(xiàng)目對(duì)小數(shù)點(diǎn)位數(shù)要求的參數(shù)，a為分析項(xiàng)目對(duì)有效位數(shù)要求的參數(shù)。

Dim dnum As Integer, anum As Integer, tnum As Double, pnum As Double, ynum As Double, m As Integer, xnum As Double, x As Double, n As Integer, tail As String

On Error GoTo errorhandler

'dnum為小數(shù)點(diǎn)位數(shù)限制參數(shù)，anum為有效位數(shù)限制參數(shù)。

dnum = d

anum = a

程序中對(duì)同時(shí)有小數(shù)點(diǎn)位數(shù)限制和有效位數(shù)限制是這樣處理的，當(dāng)數(shù)據(jù)有效位數(shù)中的小數(shù)點(diǎn)部分位數(shù)大于小數(shù)點(diǎn)位數(shù)限制時(shí)，以小數(shù)點(diǎn)位數(shù)限制來修約數(shù)據(jù)，否則以有效位數(shù)來修約數(shù)據(jù)。

tnum = Abs(onum)

pnum = Abs(onum)

With Application.WorksheetFunction

'首先判斷數(shù)值是否為零。

If onum = 0 Then

If anum = 1 Then

datafunction = 0

Else

If anum - 1 >= dnum Then

datafunction = 0 & "." & .Rept("0", dnum)

Else

datafunction = 0 & "." & .Rept("0", anum - 1)

End If

End If

Exit Function

ElseIf dnum > 14 Then

'excel中數(shù)值默認(rèn)以雙精度浮點(diǎn)數(shù)來存儲(chǔ)，這樣的結(jié)果就是超過15位的有效數(shù)值其實(shí)都是近似數(shù)，不僅計(jì)算無意義，而且比較表達(dá)式不能獲得等值結(jié)果。

datafunction = "超出存儲(chǔ)精度"

Exit Function

ElseIf -Int(.Log(pnum)) > dnum And .Log(pnum) < 0 Then

'判斷原始數(shù)據(jù)是否是小于1，并且其小數(shù)點(diǎn)后的數(shù)據(jù)串位數(shù)是否大于小數(shù)點(diǎn)位數(shù)的限制。

If anum = 1 Then

datafunction = 0

Else

If anum - 1 >= dnum Then

datafunction = 0 & "." & .Rept("0", dnum)

Else

datafunction = 0 & "." & .Rept("0", anum - 1)

End If

End If

Exit Function

End If

以上代碼是根據(jù)條件對(duì)原始數(shù)據(jù)進(jìn)行判斷，排除不需要進(jìn)行四舍六入處理的數(shù)據(jù)。

'以下是對(duì)于需要進(jìn)行四舍六入計(jì)算的數(shù)據(jù)進(jìn)行預(yù)處理：

If -(Int(.Log(pnum)) - anum + 1) > dnum Then

tnum = pnum * 10 ^ (dnum)

xnum = dnum

Else

tnum = pnum / 10 ^ (Int(.Log(pnum)) - anum + 1)

xnum = -(Int(.Log(pnum)) - anum + 1)

End If

以下是對(duì)數(shù)據(jù)進(jìn)行四舍六入的處理：

If Right(tnum, 2) = 0.5 And Right(Int(tnum), 1) Mod 2 = 0 Then

pnum = .RoundDown(pnum, xnum)

Else

pnum = .Round(pnum, xnum)

End If

If Int(.Log(pnum)) + 1 >= anum Then

If anum = 1 Then

tail = "#" & "E+#"

Else

tail = "#." & .Rept("0", anum - 1) & "E+#"

End If

Else

tail = "0." & .Rept("0", xnum)

End If

datafunction = .Text(Sgn(onum) * pnum, tail)

End With

'當(dāng)程序出錯(cuò)時(shí)進(jìn)行的處理，避免陷入死循環(huán)。

errorhandler:

Exit Function

End Function

3 實(shí)例和擴(kuò)展

自定義函數(shù)編輯完成后，就可以在表格中像Excel自帶函數(shù)一樣調(diào)用它，另外還可以將需要使用的自定義函數(shù)都編輯在一個(gè)模塊內(nèi)，然后右鍵點(diǎn)擊模塊名，彈出一個(gè)菜單，選擇“導(dǎo)出文件”，命名后保存，再導(dǎo)入其它電子表格，這樣其它的表格就都可以使用該模塊內(nèi)的自定義函數(shù)，方便而快速。下圖是一些數(shù)據(jù)用該函數(shù)處理后的結(jié)果，日常使用中未發(fā)現(xiàn)錯(cuò)誤。需要說明的是，計(jì)算結(jié)果以科學(xué)計(jì)數(shù)法表示，若需要直接顯示，原程序稍加修改即可實(shí)現(xiàn)。

在此函數(shù)基礎(chǔ)上，還可以建立各類分析項(xiàng)目庫(kù)的函數(shù)、分析方法庫(kù)的函數(shù)及相應(yīng)的檢出限庫(kù)的函數(shù)，將這些庫(kù)函數(shù)與該函數(shù)相結(jié)合，定制出非常方便實(shí)用的數(shù)據(jù)處理表格。比如地表水的表格，通過選擇表格上的分析項(xiàng)目及分析方法，檢出限、有效位數(shù)、小數(shù)點(diǎn)位數(shù)等限制會(huì)自動(dòng)顯示并參與到計(jì)算中，這樣的數(shù)據(jù)處理表格準(zhǔn)確、方便、高效，如下圖所舉例子。

4 結(jié)論

VBA自定義函數(shù)讓Excel數(shù)據(jù)統(tǒng)計(jì)功能更加符合環(huán)境監(jiān)測(cè)數(shù)據(jù)處理的質(zhì)量保證要求，通過自己編寫和導(dǎo)入相應(yīng)的函數(shù)可以準(zhǔn)確、快速、方便地對(duì)監(jiān)測(cè)數(shù)據(jù)進(jìn)行處理，大大提高監(jiān)測(cè)人員的工作效率。同時(shí)還能根據(jù)分析方法的要求進(jìn)行相應(yīng)的調(diào)整和修改（例如總氮的HJ 636-2012標(biāo)準(zhǔn)就提出根據(jù)分析結(jié)果采取不同的數(shù)據(jù)處理方式）。筆者拋磚引玉，希望有更多更好的方法能夠運(yùn)用于環(huán)境監(jiān)測(cè)工作中，為環(huán)境監(jiān)測(cè)工作的快速發(fā)展盡一份力。

[1] 中國(guó)環(huán)境監(jiān)測(cè)總站《環(huán)境水質(zhì)監(jiān)測(cè)質(zhì)量保證手冊(cè)》編寫組. 環(huán)境水質(zhì)監(jiān)測(cè)質(zhì)量保證手冊(cè)[M].北京：化學(xué)工業(yè)出版社，1994.

[2] GB 8170-87，數(shù)值修約規(guī)則[S].

[3] Microsoft Excel Visual Basic Reference[EB/OL]. http://msdn.microsoft.com /en-us/ library/aa269683(v=office.10)