利用Excel自定义函数同时满足多种数据处理要求

黄文超



利用Excel自定义函数同时满足多种数据处理要求

黄文超

三明市环境监测站

通过Excel软件中的自定义函数功能，使Excel表格的计算功能符合环境监测质量保证中对小数点位数、有效位数、四舍六入等数据处理要求，充分发挥Excel软件的普及性和易用性，提高环境监测数据处理时的自动化水平及工作效率。

数据处理 自定义函数 小数点位数 有效位数 四舍六入

1 背景介绍

Excel作为日常办公数据处理软件，具有巨大的优势，它普及性广，易用性强，是环境监测数据处理中重要的工具软件之一。但环境监测质量保证中对数据处理有更高的要求，比如数据四舍六入处理的要求，保留小数点位数的要求，保留有效位数的要求，这些都限制了Excel在监测数据处理中的使用。通过Excel自带的VBA编程语言，我们能够极大地扩展Excel的功能，定制出符合环境监测质量保证所要求的自定义函数。

目前用VBA定制的四舍六入的函数很多，但同时包含处理保留小数点位数要求及保留有效位数要求的自定义函数还没有见过，由于笔者在日常工作中经常需要对监测数据进行处理，因此自己编写了满足质控要求的自定义函数，在此基础上还加入了对不同项目，相同项目不同方法的处理，最核心的程序和思路将在下文介绍。

2 程序编制

首先打开Excel程序（以Excel2003为例），在工具栏中点击“工具”，在下拉菜单中选择“宏”，在其右侧弹出菜单中选择“Visual Basic 编辑器”进入，点击工具栏上的“插入”菜单，选择“模块”，就可以开始自定义函数编辑工作了。

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

'datafunction: 自定义函数名，并且将其结果按字符数据类型输出。onum为引用参数，即需处理的数据，d为分析项目对小数点位数要求的参数，a为分析项目对有效位数要求的参数。

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为小数点位数限制参数，anum为有效位数限制参数。

dnum = d

anum = a

程序中对同时有小数点位数限制和有效位数限制是这样处理的，当数据有效位数中的小数点部分位数大于小数点位数限制时，以小数点位数限制来修约数据，否则以有效位数来修约数据。

tnum = Abs(onum)

pnum = Abs(onum)

With Application.WorksheetFunction

'首先判断数值是否为零。

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中数值默认以双精度浮点数来存储，这样的结果就是超过15位的有效数值其实都是近似数，不仅计算无意义，而且比较表达式不能获得等值结果。

datafunction = "超出存储精度"

Exit Function

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

'判断原始数据是否是小于1，并且其小数点后的数据串位数是否大于小数点位数的限制。

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

以上代码是根据条件对原始数据进行判断，排除不需要进行四舍六入处理的数据。

'以下是对于需要进行四舍六入计算的数据进行预处理：

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

以下是对数据进行四舍六入的处理：

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

'当程序出错时进行的处理，避免陷入死循环。

errorhandler:

Exit Function

End Function

3 实例和扩展

自定义函数编辑完成后，就可以在表格中像Excel自带函数一样调用它，另外还可以将需要使用的自定义函数都编辑在一个模块内，然后右键点击模块名，弹出一个菜单，选择“导出文件”，命名后保存，再导入其它电子表格，这样其它的表格就都可以使用该模块内的自定义函数，方便而快速。下图是一些数据用该函数处理后的结果，日常使用中未发现错误。需要说明的是，计算结果以科学计数法表示，若需要直接显示，原程序稍加修改即可实现。

在此函数基础上，还可以建立各类分析项目库的函数、分析方法库的函数及相应的检出限库的函数，将这些库函数与该函数相结合，定制出非常方便实用的数据处理表格。比如地表水的表格，通过选择表格上的分析项目及分析方法，检出限、有效位数、小数点位数等限制会自动显示并参与到计算中，这样的数据处理表格准确、方便、高效，如下图所举例子。

4 结论

VBA自定义函数让Excel数据统计功能更加符合环境监测数据处理的质量保证要求，通过自己编写和导入相应的函数可以准确、快速、方便地对监测数据进行处理，大大提高监测人员的工作效率。同时还能根据分析方法的要求进行相应的调整和修改（例如总氮的HJ 636-2012标准就提出根据分析结果采取不同的数据处理方式）。笔者抛砖引玉，希望有更多更好的方法能够运用于环境监测工作中，为环境监测工作的快速发展尽一份力。

[1] 中国环境监测总站《环境水质监测质量保证手册》编写组. 环境水质监测质量保证手册[M].北京：化学工业出版社，1994.

[2] GB 8170-87，数值修约规则[S].

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