1、环境监测数据处理的一种新方法刘振旗作者简介：刘振旗（1972-） ，男，工程师，主要从事环境监测与科研 摘要：详细介绍了 Excel 与 MALTAB 集成的方法，并应用 VBA 宏语言，实现了 MALTAB 在 Excel 处理环境监测数据的能力。 关键词：MALTABExcel linkExcel 中图分类号：X83 文献标识码： A 文章编号： 在环境监测工作中，监测数据的处理是一项重要而繁重的工作，对采集到大量的样本数据需要用一些统计量来描述数据的集中程度和离散程度，并通过这些指标对数据的总体特征进行归纳。描述样本数据集中趋势的统计量有算术平均值、中位数、众数、几何均值等，描述样本数据
2、离中趋势的统计量包括极差、平均差、平均绝对差、方差和标准差，此外还有峰度、偏度、分位数和相关系数等统计量。Excel 是一个十分强大和有生命力的数据处理软件，虽然应用广泛，但是对于数值计算方面其能力欠佳1，与之相比，Matlab 是新近发展起来的一种深受广大编程人员喜欢的数学工具软件。Matlab 语言可以提供与矩阵有关的强大数据处理和图形显示功能，为软件开发人员在程序编制过程中实现数值计算和图形显示新添了又一行之有效的开发平台。但是，Matlab 的界面功能比较弱，给友好界面的开发应用带来不便。 为了发挥 Excel 和 Matlab 各自的优点，自 Matlab 问世，Matlab 工具箱
3、就提供了集成 Excel 和 Matlab 软件一个 Excel link 插件，该插件不仅使得 Excel 在表格处理、科学计算和工程设计中营造了一个更加完美统一的开发环境，而且还具备 Matlab 的数值计算能力和灵活自如的图形可视化功能。这里给出环境监测数据处理计算公式、Excel link 插件函数用法、 VBA 编程环境实现的程序和实例应用。 1、数据处理计算公式 11 集中趋势 研究数据总体向分配中心集中的现象。反映集中趋势的综合特征量主要有算术平均值、几何平均数等。 （1）化算术平均值 算术平均值抹掉个体特征值之间的差异，反映整体特征的典型水平，可以作为整体的代表值。算术平均值可
4、以消除偶然因素所形成的差异，从而揭示客观规律，在数理统计中占有重要地位。 若 x1,x2,.,xn 是一组数据，其算术平均值的计算公式为： （1） （2） 几何平均数 几何平均数的计算公式为： （2） 在求几何平均数时，可以对两边取对数，即 （3） 然后再计算反对数。 几何平均数受极端数据的影响比较小，但如果在数据中包括零或负数就没有办法求几何平均数。 12 离散程度 反映离散程度的综合特征量有范围、平均差、方差和标准差等。 （1）范围 所谓范围就是数列的极大值和极小值之差，是对数列离散程度的最粗略估计。 （2）标准差 方差是离差平方后的结果，与原来数据的计量单位不一致，意义很不明确。为了使所
5、得到的结果与原来的计量单位一致，需要将方差开方。这就是标准差，用公式可以写成： （4） 13 偏度和峰度、百分位数和相关系数 平均数和标准差等还不足以完全描述数字的特征，除了集中趋势和离散趋势外，数字分配还有对称的特征和峰度高低的特征，需要用偏度系数和峰度系数加以量化。 （1）偏度系数 偏度系数的计算公式为： （5） 在分配对称时，由于正负离差的绝对值完全一致，3 次方后正负离差可以抵消，这时 g1 为 0。如果分配不对称，正负离差的 3 次方不能完全抵消，离差绝对值越大，偏度系数的绝对值也越大，就越不对称。g1可以是正值，也可以是负值。正值时称为正偏，负值时称为负偏。 （2）峰度系数 峰度系
6、数的计算公式为： （6） 峰度系数描述次数曲线的高峰形态，常常与正态分布进行比较，比正态分布高称为高狭峰，反之称低阔峰。 14、百分位数和相关系数 （1）百分位数 百分位数（percentile）是将 n 个观察值从小到大依次排列，再把它们的位次依次转化为百分位。百分位数的另一个重要用途是确定医学正常参考值范围。 百分位数用 Px 表示，0 x 100,如 25%位数表示为P25。在频数表上，百分位数的计算公式为： （7） （2）相关系数 相关系数是用以反映变量之间相关关系密切程度的统计指标。相关系数是按积差方法计算，同样以两变量与各自平均值的离差为基础，通过两个离差相乘来反映两变量之间相关程
7、度；着重研究线性的单相关系数。相关系数计算公式为： （8） 2 Excel link 简介 Excel link 是一个软件插件2-3，它将 Excel 和 Matlab 在微软视窗环境下进行集成。它把 Excel 作为 Matlab 的前端，通过在 Excel 的工作表或宏中使用 Excel link 函数，就能获得 Matlab 的数值计算和图形自动显示功能。Excel link 提供了 9 个数据管理函数，但在 VBA 编程环境中用得最多的通常为 3 个函数。以下仅给出 3 个函数用法，其它函数的用法详见有关文献，在此不再赘述。 MLEvalString 运行 MATLAB 命令。 ML
8、GetMatrix 把 MATLAB 矩阵的内容写到 Excel 工作表。 MLPutMatrix 用 Excel 工作表中的数据创建或覆盖于 MATLAB 矩阵。 Excel link 函数在 VBA 中的应用程序 程序设计首先在工具菜单中打开 Visual Baics 编辑器，然后在 VBE属性窗口中插入一个模块，在模块代码窗口内输入下面代码： Sub 统计量() Sheets(“Sheet1“).Select 获取数据区域行数和列数 rr = Range(“A1“).CurrentRegion.Rows.Count cc = Range(“A1“).CurrentRegion.Colum
9、ns.Count t = 64 + cc 定义数据区域对象 Set adata = Sheet1.Range(“B2:“ & Chr(t) & rr & “) Set p = Sheet1.Range(“B1:“ & Chr(t) & 1 & “) Set n = Sheet1.Range(“A2:“ & Chr(65) & rr & “) 将数据区域对象送到 matlab 工作区 Mlevalstring “clear“ Mlputmatrix “data“, adata Mlputmatrix “n“, n Mlputmatrix “p“, p 清除表的内容 Sheets(“土壤统计“).
10、Select Sheets(“土壤统计“).Range(“B1:W13,A15:W200“).ClearContents 计算相关系数 x、均值 x1、几何均值 x2 和标准差 x3 及 x8 百分位数等参数 Mlevalstring “x=corrcoef(data)“ Mlevalstring “x1=mean(data)“ Mlevalstring “x2=geomean(data),x4=min(data),x5=max(data)“ Mlevalstring “x3=std(data)“ Mlevalstring “x8=prctile(data,5 25 50 75 95)“ Ml
11、evalstring “x6=kurtosis(data)“计算偏度 K Mlevalstring “x7=skewness(data)“计算峰度 将 MATLAB 计算结果送到 Excel 表格中 Mlgetmatrix “p“, “B1“ Mlevalstring “k=p“ Mlgetmatrix “k“, “A15“ For i = 1 To 8 ii = i + 1 Mlgetmatrix “x“ & i & “, “B“ & ii & “ Next i Mlgetmatrix “x“, “B15“ MATLABRequest End Sub 应用实例 土壤污染监测经常需要统计上述统
12、计量，应用上述程序计算时Excel“sheet1”表格为数据输入表，Excel“sheet2”为计算结果输出表。图 1 中的“sheet1”表格为一组土壤污染监测数据实例。其程序计算步骤如下: 1.打开已建好的上述程序的 Excel 软件， 2.按图 1 输入土壤监测数据, 3.在工具菜单上选择列表宏 ，在打开宏窗体中的选择框中单击“统计量”然后单击运行按钮。 按上述三个步骤操作后，计算结果见图 2。 图 1 土壤污染监测数据表 图 2 计算结果 结语 以上介绍了 Excel 与 MALTAB 集成技术，实现了环境监测数据处理的一个实例。若用户在 Excel“sheet1”中输入其它数据同样也
13、能得出向“sheet2”中那样显示的正确结果。采用上述介绍的 Excel link 插件函数和 VBA 编程方法还能扩充 Excel 在环境数据处理的应用范围： （1）在环境评价中的应用：多元回归分析、主成分分析、因子分析、聚类分析、判别分析、对应分析、典型相关分析等。 （2）在环境规划中的应用：线性规划、二次规划、非线性规划、多目标规划、0-1 规划等。 （3）在环境污染预测中的应用：曲线拟合、人工神经网络等。 参考文献 1韩波，孙利. Excel 加 VBA 在实验室内质量控制中的应用.J.中国环境监测 2006，22（4）：29-33 2苏金明，刘宏，刘波.Matlab 高级编程M.北京:电子工业出版社，2005：234-242. 3许国根，许萍萍.化学化工中的数学方法及 Matlab 实现M.北京：化学工业出版社，2008：1-16.
