层次分析法在生态环境质量评价中的应用

VBA 在确定生态环境确定生态环境质量质量质量评价权重评价权重评价权重中的应用中的应用中的应用韩波1 彭福祥彭福祥2 张钧张钧2（1.北海市环境监测中心站 北海 536000；2.北海市气象局 北海536000) 摘要摘要：：层次分析是生态环境质量评价应用相当广泛的方法，根据目前文献基于Matlab 和Excel 实现层次分析的报导中存在的不足，提出了一种基于VBA 在Excel 下编写宏程序轻松实现层次分析，使用户进行权重计算时彻底从繁琐手工操作中解放出来，最终达到在Excel 中实现“傻瓜”层次分析目标。

关键词关键词：：层次分析；权重；Excel；VBA生态环境质量评价是一个多层次、多类指标所构成的复杂的决策系统。

在实践过程中，经常运用模糊数学、层次分析法等进行评价[ 1-2 ]，而在评价之前首先需要确定相关各项评价指标的权重，不然权重的细徽变化对整个评价结果产生重大影响，并直接影响到生态环境保护经济投资效益问题。

因此确定生态环境质量评价因素的权重显得非常重要。

目前，常用的方法有层次分析法、因子分析法等来确定权重。

其中因子分析法是建立在大量的数据样本的基础上来实现，应用中很难寻找到适合的生态环境样本；层次分析法是建立在评价指标之间能够进行重要程度的比较基础上，因此应用中很容易实现。

由于受计算条件的限制，董守贵提出了Matlab 数学软件实现[ 3-4 ]，虽然程序简短，但软件需要占用较大的计算机内存；李玉心等人提出Excel VBA 来实现[ 5 ]，设计了较多的表格和冗长的计算程序，通用性和可读性差。

本文利用VBA 在Excel 下编写了一段简短的宏程序，一步就能实现生态环境质量层次分析的计算目标。

VBA Environment Quality Evaluation in Determining the Weight of the ApplicationHan Bo 1 Zhang Jun 2(Environmental Monitoring Center Beihai, Beiha 536000) (Weather Bureau Beihai, Beihai 536000) Abstract: The level of analysis is the application of eco-environmental quality evaluation of a wide range of methods, according to the literature based on Matlab and Excel to achieve the reported level of analysis of the shortcomings, is proposed based on VBA macro in Excel to prepare easy-level analysis, the user data processing from the tedious manual completely freed, and ultimately achieving in Excel to achieve "a fool"-level analysis of the target.Keywords: AHP; weight; Excel; VBA１ 层次分析数学原理层次分析数学原理层次分析法是一种有效的多目标规划方法，也是一种最优化技术。

它把所要规划的问题表示为有序的递阶层次结构，通过人的判断对规划方案的优劣进行排序，并据此求得最佳规划方案。

运用层次分析法作系统规划，大致经过5个步骤。

1．1明确问题明确问题1．2 建立递阶层次结构建立递阶层次结构递阶层次结构如图1所示。

1．3 3 构造判断矩阵构造判断矩阵构造判断矩阵设要比较n 个因素11{,,,}n X x x x =⋅⋅⋅对目标Z 的影响，确定它们在Z 中所占的比重。

每次取2个因素i x 和j x 对Z 的影响之比，得到两两比较判断矩阵：()ij n n A a ×= （1）其中，0,1/()ij ji ij a a a i j >=≠。

1(,1,2,...,)ij a i j n == （2）使（1）式成立的矩阵称为正负反矩阵。

1． 4 4 计算权重计算权重计算权重根据判断矩阵，采用方根法求出其特征向量和所对应的最大特征根。

方根法的计算过程 如下：① 计算判断矩阵()ij n n A a ×=的每行元素之积1ni ij j M a ==∏ ，（ i =1，2，…，n ） （3） ② 计算M i 的n 次方根n i i M =ϖ，（ i =1，2，…，n ） （4）③ 对向量W =（n ϖϖω，，，…21）T 做归一化处理，令∑==ni i i i 1/ϖϖω，（ i =1，2，…，n ） （5）④ 求出判断矩阵的最大特征值max 11n i i iAW n λω==∑（） （6）1．5一致性检验一致性检验以上得到的权重分配是否合理，还需要对判断矩阵进行一致性检验。

检验使用公式：/CR CI RI = （7）式中，CR 为判断矩阵的随机一致性比率； CI 为判断矩阵的一般性指标。

它由下式给出：max ()/(1)CI n n λ=−− （8）RI 为判断矩阵的平均随机一致性指标，1～9阶的判断矩阵的RI 值如表1。

表1 1 平均随机一致性指标平均随机一致性指标RI 的值的值 n 12 3 4 5 6 7 8 9 RI0 0 0.58 0.90 1.12 1.24 1.32 1.41 1.45 当判断矩阵A 的CR <0.1时或max n λ=时，A 具有完全一致性，否则需要调整A 中的元素以使其具有满意的一致性。

2 2 Excel VBA 建立层次分析法权重计算的实现建立层次分析法权重计算的实现2．1 Excel 计算表设计计算表设计新建一个工作薄，把工作薄的“sheet1”工作表作为数据输入和计算的数据表，如截图1所示。

截图1中，“sheet1”工作表的第一列和第一行设计为数据输入的属性，其它单元格区域为数据输入区域和计算结果显示区域。

截图1 1 层次分析的计算表层次分析的计算表层次分析的计算表2．2 VBA 程序设计程序设计要完成上述层次分析的计算内容，且使计算程序具有通用性（与样本指标多少无关），程序设计是关键。

这里程序设计采用VBA 宏语言编写，因为这种方法比在Excel 电子表格内编程灵活，而且程序能得到有效的保护。

程序设计首先在Excel 工具菜单中打开Visual Baics编辑器，然后在VBE属性窗口中插入一个模块，在模块代码窗口内依次输入截图2所示的代码。

次分析权重计算的代码清单代码清单2 层层次分析权重计算的截图2代码清单运行界面设计3 运行界面设计2．3运行界面设计在本地工作薄的“sheet1”适合的位置上添加一个命令按钮（如截图1），在命令按钮“宏”中指定名为“层次分析”，最后将该工作薄保存一个名为“层次分析”的Excel文件。

此时，系统设计完成。

实际应用3实际应用以文献[3]为例。

文献[3]给出了澜沧江大朝山水电站环境影响评价施工期生态环境因子判断矩阵，运用Excel VBA求解环境因子的权重，判断矩阵A见表1。

其程序计算步骤如下：打开已建好的上述程序名为“层次分析”Excel软件；然后将表1的数据分别输入截图3所示的第二行至第六行空白单元格内；单击“层次分析”按钮。

进行上述3个步骤操作后，计算结果见截图3。

运用VBA求解环境因子的判断矩阵A表11 运用─────────────────────────────────监测指标 固体废物x1 工区水质x2 噪声x3 大气污染x4 地貌与景观x5─────────────────────────────────固体废物x1 1 1 3 3 5工区水质x2 1 1 3 3 3噪声x3 1/3 1/3 1 1 1大气污染x4 1/3 1/3 1 1 1地貌与景观x5 1/5 1/3 1 1 1────────────────────────────────截图33 数据输入和计算结果表数据输入和计算结果表数据输入和计算结果表CR≤，则截图3输入的判断矩阵具有令人满 从截图3可见，计算出的一致性比率0.1W=0.602,0.3252,0.1084,0.1084,0.0979)的分量分别可以作为固体废物、意的一致性，(工区水质、噪声、大气污染、地貌与景观指标的权重。

结果与讨论4结果与讨论利用常用办公软件Office中的Excel便于矩阵输入和显示的特点,结合Excel中内嵌的VBA可编程功能,编写了层次分析法权重计算的VBA代码,给出了Excel中利用该宏代码进行层次分析权重计算的具体操作步骤,并取得了很好的计算结果。

与文献[3]、文献[4]基于Matlab比较，应用Excel不需要安装Matlab数学软件进行复杂的矩阵计算，输入和输出结果直观，更容易推广应用；与文献[5]基于Excel VBA 比较，本程序代码更加简洁，可读性强，一步操作就能完成全部的计算结果。

参考文献参考文献：：[1] 王金叶，程道品，胡新添等.广西生态环境评价指标体系及模糊评价[J].西北林学院学报，2006，21(4)：5-8.[2] 李博，宋亚楠，杨冰冰等. 沈阳生态环境质量评价研究 [J].沈阳师范大学学报，2009，27(3)：373-377.[3] 柴西龙，孔令辉，海热提.Matlab 软件在确定环境影响评价权重过程中的应用[J].环境影响评价动态，2003，(3)：28-33.[4] 董守贵. 基于MATLAB 的层次分析法实现[J]. 航空兵器，2003，(4)：16-18.[5] 李心玉 黄 娟. Excel VBA 在层次分析法中的应用[J]. 电脑编程技巧与维护，2010，(21)：15-19.。