基于Excel link主成分分析水质评价方法.doc

基于Excel link主成分分析水质评价方法摘 要：水质评价的主成分分析涉及十分复杂的矩阵运算和评价成果图的制作，本文在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所示