饮用水水质检测方法 [基于Excel,link主成分分析的水质评价方法]
时间：2020-03-05 07:33:33 来源：雅意学习网 本文已影响 人 
摘 要：水质评价的主成分分析涉及十分复杂的矩阵运算和评价成果图的制作，本文在Excel平台上应用Excel link插件，给出了实现了水质评价的主成分分析计算程序，并与文献[1]所应用的SPSS软件进行了比较，指出了本程序的优越性。
关键词：Excel link Matlab 主成分分析
水质评价是多变量综合作用的结果，通常需要采用多元统计方法进行分析。近年来，主成分分析在水质评价中的应用十分广泛 [1-2]。这种方法的实现往往比较复杂，其数据对象通常是矩阵和向量，具体计算步聚涉及到矩阵的乘积、转置、求逆等，过去一般采用Fortran、Basic和C等高级语言编写相应的程序进行处理而得到结果[3]，因此要求操作人员应具有一定的计算机技能和编程能力，而且程序调试过程费时、费力，很难得到推广应用。但是，Matlab的界面功能比较弱，给友好界面的开发应用或软件演示系统带来不便。
为了提高工程计算软件的开发效率和质量，MatlhWorks公司在开发Matlab的同时，同样开发了Matlab Excel link插件,正是这个插件使得当前流行的Microsoft Excel电子表格软件，不仅具备Excel的全部功能，而且还具备Matlab的数据运算能力和灵活自如的数据可视化能力。这里以文献[1]――“基于主成分分析的明江河水质评价”为例，介绍在Microsoft Excel环境下通过Excel link函数实现主成分分析在水质评价中的应用。
1 主成分分析的主要方法和步骤
1.1主成分分析方法和评价模型
主成分因子分析是将多个指标化为少数指标且能保持最大原始数据的相关性的一种方法。在主成分分析中较为重要的方差贡献βi，表示第个公因子在消除个公因子影响后，使方差贡献取到的最大值。用它主要衡量第个公因子的重要程度。因此我们可以以βi为权重，建立相应的评价模型：，其中F1，F2，…,Fk为相应的用来综合描述原始指标的k个公因子，计算综合得分并排序。
1.2主成分分析算法的Excel VBA程序设计
要完成模型计算内容，且使计算程序具有通用性（与样本数和变量大小无关），程序设计是关键。这里程序设计采用VBA宏语言编写，因为这种方法比在Excel电子表格内编程灵活，而且程序能得到有效的保护（有关Excel link函数和Matlab中主成分分析的主要函数详见有关文献，在此不再论述）。
程序设计首先在Excel工具菜单中打开Visual Baics编辑器，然后在VBE属性窗口中插入一个模块，在模块代码窗口内依次输入下面代码，该程序大致分为5部分：
（1）获取数据区域行数和列数
rr = Range("A1").CurrentRegion.Rows.Count
cc = Range("A1").CurrentRegion.Columns.Count
t = 64 + cc
（2）将原始数据、变量和样本数传给Matlab工作区
Mlputmatrix "data", adata
Mlputmatrix "p", p
Mlputmatrix "n", n
（3）使用Matlab内置函数计算协方差、主成分等参数和主成分得分
Mlevalstring "sr=cov(data)"
Mlevalstring "[pcs,newdata,variances]=pcacov(sr)"
Mlevalstring "y=data*pcs"
（4）将matlab的计算结果送到excel工作表
Mlgetmatrix "y", Chr(66) & (rr + 3) & ""
Mlgetmatrix "n", Chr(65) & (rr + 3) & ""
Mlgetmatrix "pcs", Chr(t + 2) & 2 & ""
Mlgetmatrix "variances", "L12"
（5）生成前两个主成分平面图
Mlevalstring “plot(y(:,1),y(:,2),’b.’);xlabel(‘主成份1’);ylabel(‘主成份2’)”
Mlevalstring "text(y(:,1),y(:,2),num2str((1:21)"))
2 应用实例
2.1计算实例
为了便于比较，这里引用文献[1] ――“基于主成分分析的明江河水质评价”为例，该文选取了4个具有代表性的点位和5个常规水质监测指标pH（x1）、溶解氧（x2）、高锰酸盐指数（x3）、化学需氧量（x4）、总大肠菌群（x5）作为研究对象，由于各项指标为数据差别较大，故进行标准化处理如表1所示。
图3 4个水质点位主成分（1、2）的分布图
图1是表1的4个水质点位标准化数据在“Sheel”中的输入结果；图2中“sheet2”为计算结果输出表；图3为4个水质点位主成分（1，2）二维平面分布。
其程序步骤如下：
①打开已建好的上述程序的Excel软件，
②按图1输入水质标准化数据,
③在《工具栏》菜单上选择列表《主成分分析》按钮。
进行了上述3个步骤操作后，计算结果见图2和图3。
2.2实例结果比较
为了便于比较，表2、表3和表4列出了2种软件的计算结果。
从表3和表4看出，两种软件计算的特征向量和主成分得分（F1）是吻合的，但从表2两种软件计算的主成分特征值和贡献率可见，前三个主成分的特征值和贡献率相当吻合，而后的主成分4、5的特征值和贡献率不尽相同，文献[1]应用SPSS软件计算，主成分5的特征值和贡献率竟然出现了负值，这从主成分分析原理上讲是无法解释的，应该是一个错误的结果。
应用本软件计算不仅能给出了全部的主成分的特征向量和主成分得分，而且还能得出全部的主成分特征值和贡献率，这样的结果有利于对原始数据的全面分析和评价。此外，还能自动绘制出前2个主成分得分（F1）的平面分布图，从而进一步说明水质点位的污染分布和水质的优劣。
3 结束语
在Excel开发平台上，用Excel link插件实现主成分分析的水质评价的计算程序，具有计算简便、计算结果准确可靠等特点。该计算程序不仅适用于水质评价，而且还可用于其它环境质量质量评价，笔者还用该程序对大气环境质量进行了评价，也取得了令人满意的结果。
参考文献：
[1]吴浩东、胡衡生 基于主成分分析法的明江水质评价[J]，湖北农业科学，2010，49（10）：2407-2409.
[2]邹海明、蒋良富、李粉茹 基于主成分分析的水质评价方法[J]，数学的实践与认识，2008，38（8）85-90.
[3]张敏、李陶深、钏淑瑛 基于Matlab的主成分分析方法的实现[J]，广西大学学报（自然科学版），2005，30（增刊）74-77.
注：本文中所涉及到的图表、注解、公式等内容请以PDF格式阅读原文
推荐访问:水质 成分 评价 基于Excel link主成分分析的水质评价方法 excel数据分析 excel数据下载
