《用电子表格(Excel)实现层次分析法(AHP)的简捷计算.doc》由会员分享，可在线阅读，更多相关《用电子表格(Excel)实现层次分析法(AHP)的简捷计算.doc（8页珍藏版）》请在金锄头文库上搜索。
1、用电子表格（Excel）实现层次分析法（AHP）的简捷计算先锋 （华南农业大学 林学院，广东 广州 510640）摘要：传统的层次分析法算法具有构造判断矩阵不容易、计算繁多重复且易出错、一致性调 整比较麻烦等缺点。层次分析法 Excel 算法利用常用的办公软件电子表格（Excel）的运算功能，设置简明易懂的计算表格和步骤，使得判断矩阵的构造、层次单排序和层次总排序的计算以及一致性检验和检验之后对判断矩阵的调整变得十分简单。从而可以为层次分析法的学习、应用、推广和改进探讨提供方便。 关键词：层次分析法 Excel1 层次分析法（AHP）的应用难点 层次分析法 （Analytical Hierar
2、chy Process,简称 AHP）是美国匹兹堡大学教授 A.L.Saaty ， 于 20 世纪 70 年代提出的一种系统分析方法， 它综合了定性与定量分析， 模拟人的决策思维 过程，具有思路清晰、方法简便、适用面广、系统性强等特点，是分析多目标、多因素、多准则的复杂大系统的有力工具。层次分析法的基本原理简单说就是用下一层次因素的相对排序来求得上一层次因素的相对排序。 应用层次分析法解决问题的思路是：首先把要解决的问题分出系列层次，即根据问题的性质和要达到的目标将问题分解为不同的组成因素，按照因素之间的相互影响和隶属关系将各层次各因素聚类组合,形成一个递阶的有序的层次结构模型；然后对模型中每
3、一层次每一因素的相对重要性，依据人们对客观现实的判断给予定量表示（也可以先进行定性判断，再予赋值量化），再利用数学方法确定每一层次全部因素相对重要性次序的权值；最后通过综合计算各层因素相对重要性的权值，得到最低层（方案层）相对于较高层（分目标或准则层）和最高层（总目标）的相对重要性次序的组合权值，以此 进行进行方案排序，作为评价和选择方案的依据。 层次分析法在多个领域得到广泛应用，但在应用中也是确实存在着不少难点。1.1 构造一个合适的判断矩阵不容易 建立层次结构模型和构造判断矩阵是层次分析法的主要基本工作，构造判断矩阵是关键之关键。要从“1/9-9”的数字范围内挑选标度值并要尽量符合判断的“
4、一致性” ，构造合适的判断矩阵比建立层次结构模型困难得多，特别是要构造 5 阶以上的高阶判断矩阵的话。层次分析法的使用就是为了解决多目标、多准则、多层次的复杂系统问题，但是系统越复杂，涉及层次和因素越多，构造合适的判断矩阵就越加困难。1.2 计算繁多、重复且易出错 层次分析法计算的根本问题是如何计算出判断矩阵的最大特征根max 及其对应的正 规化特征向量w，向量w的分量 Wi 即是相应因素的单排序的权值，或者直接称为层次单排序结果。常用的计算方法有幂法、和积法、方根法等，计算原理本来简单，但过程却仍因涉及因素的增多而趋于复杂、繁琐，其中包括很多重复或相似的运算，令人不胜其烦且易出错。如果使用电
5、脑计算，加之已有人开发出相应的程序，上述计算工作已经大为简化。但是现有 的层次分析法程序都是另行编制的，需要重新安装才能使用，里面所涉及的 Basic 语言等亦非现在众多普通的“视窗”一族所熟悉，故至今使用者少。从亲身观察和文章分析来看，学校里的多数学生以及目前的部分研究者仍然是抱着计算器来计算层次分析法， 工作量大、 精确性差，有待改进。1.3 如果达不到满意一致性“返工”调整比较麻烦 层次分析法计算不单是要得到一个结果，而且是要得到一个具有满意一致性的结果， 否 则排序结果没有实用意义。如果一致性检验通不过，就要调整或重新构造判断矩阵，每调整 或重构一次判断矩阵， 与之相应的计算过程和一致
6、性检验就要全盘重来一次， 工作量成倍增大不说， 二次出错的可能性也增加了。事实上在进行多因素分析，需要构造高阶矩阵的时候，一次成功的机会并不多，“返工”调整是经常的事。1.4 以上难点的不良后果 以上难点对层次分析法的学习、 推广和应用构成阻滞。许多人因为层次分析法计算复杂、验算困难而失去学习层次分析法的耐心和信心，也因此不敢或不愿使用层次分析法解决现实决策问题，特别是面对多种因素需要构造复杂的高阶判断矩阵的时候。在学而烦、学不会、不敢用、用不准的心理影响下，层次分析法的应用和推广价值受到很大削弱。2 用电子表格（Excel）计算层次分析法的基本原理和步骤 为了解决以上难题，为了让层次分析法的
7、学习变得简单易行，为了让普通人士都可以轻 松应用层次分析法，笔者尝试利用现在常用的办公软件电子表格（Excel）的运算功能，设置简明易懂的计算表格和步骤，使得判断矩阵的构造、层次单排序（权重系数）和层次总排序的计算以及一致性检验和检验之后对判断矩阵的调整变得十分简单。因为是以 Excel 为运 算载体，故称之为层次分析法 Excel 算法。2.1 层次分析法 Excel 算法的基本原理 层次分析法 Excel 算法充分利用 Excel 的函数运算、公式编辑、自动计算等功能和单元格等式引用规则，设计成步步相连的计算过程，达到只要输入一个判断值（矩阵标度值）就可以立即得到相应的各层次单排序和总排序
8、结果以及一致性检验指标的目的。如果对结果不满意，可以通过调整判断矩阵的标度值来修正结果，调整可以是任意的，每次调整的结果也是一步得出。2.2 层次分析法 Excel 算法的运算设计2.2.1 层次分析法的运算步骤简介层次分析法的主要运算步骤包括：建立层次结构模型；构造判断矩阵；用和积法或方根 法等求得特征向量 W（向量 W 的分量 Wi 即为层次单排序）；计算最大特征根max；计算一致性指标 CI、RI、CR 并判断是否具有满意的一致性。该步骤已经为人熟悉，故不详述。2.2.2 用实例说明的层次分析法 Excel 算法过程 例： 假设某林业经济单位要选择适当的树种来调整经济结构， 树种选择考虑
9、的因素包括四个方面：经济效益、生态效益、社会效益和技术要求，可选树种包括松树、杉木和桉树，请问应当怎样对供选树种进行优劣排序？根据题意可以建立层次结构模型如图1： 层次分析法的计算方法有多种， 假如判断矩阵是完全一致的话，用和积法或方根法计算的结果是一样的，如果判断矩阵不一致，那么计算出的权重系数值会有不同，但排序次序应当一样。由于和积法需要进行列规范化，相当于又形成一个矩阵，占用的页面会比方根法稍大，故本文按方根法依照前述步骤在电子表格（Excel）中设计出层次分析法运算过程如下 （见下页图 2 和图 3） ： （1）判断矩阵的设置和矩阵元素的输入 a 判断矩阵表格化和“一边倒” 由于是在
10、Excel 中运作，判断矩阵要制成表格形式，形成没有矩阵形状的矩阵区（见图 1 的“B12:D15”区域。在矩阵区的主对角线单元格全部输入数值 1，以此主对角线为分界，右上角单元格对称地编辑成左下角单元格的倒数（比如把 E12 单元格编辑成“=1/B15” ），简称“一边倒” ，目的是一旦在左下角单元格中输入数值，就可以立即得出右上角的相应的倒数（比如在 B15 单元格中输入 1/2，E12 单元格立即出现 2），需要调整判断矩阵的时候也只需变动矩阵区左下角的数字。判断矩阵通常采用的是比例标度，为了表达这种习惯形式，可以通过“设置单元格格式”把矩阵区设置成“数字分数”形式，这样无论输入什么数值
11、都将表现为分数或整数。 b 标题和因素名称（指标）的输入 为了让运算清晰有序，标题和指标（或因素名称）以及运算提示比如“按行相乘”“开n次方”“CI=(-n)/(n-1)”等不应省略。在 Excel 中输入文本亦有省事的技巧，比如可以将单元格 B12、C12、D12、E12 分别编辑成“=A12”“=A13”“=A14”“=A15”（凡如“=？” 均表示在 Excel 中的编辑形式，以下同），这样当在矩阵区左边栏单元格 A12、A13、A14、 A15 中分别输入经济效益、社会效益、生态效益、技术要求等文本的时候，会立即出现在矩 阵区的右上边栏。 其他凡是重复出现的文本或数值亦都用此方法引用，
12、从而构成 “一动俱动”，方便调整的效果。（2）层次单排序计算 c 用 PRODUCT 乘积函数和自动计算实现矩阵元素按行相乘。 比如将单元格 F12 编辑成 “= PRODUCT(B12:E12)” ，然后鼠标左键按住单元格 F12 下拉，即可得到其余 F13 到 F15 的运算结果。 d 用 POWER 乘幂函数和自动计算实现将 c 步骤所得乘积分别开 n 次方（即 1/n 次幂）。比如编辑 G12“=POWER(F12,1/4)”再下拉自动计算。 e 用 SUM 求和函数求得 d 步骤结果的总和。即 G16“=SUM(G12:G15)” 。 f 将 d 步骤值分别除以e步骤值，得到特征向量
13、W及其分量Wi，即层次单排序结果。编辑首个单元格 H12“=G12/G$16”即可，其余通过下拉自动计算。 （3）判断矩阵一致性检验 g 将判断矩阵的各行元素分别与向量 W 的分量 Wi 相乘之后相加， 得到向量 AW 及其分量 AWi。本 步 骤 可 以 直 接 编 辑 乘 积 求 和 公 式 再 自 动 计 算 ， 比 如 可 以 编 辑 I12 “=B12*H$12+C12*H$13+D12*H$14+E12*H$15”再下拉自动计算，也可以先将横排的矩阵元素通过编辑等式引用成竖排，然后用 SUMPRODUCT 数组对应元素乘积求和函数进行自动计算。 h 将 AWi 分别除以 Wi 并自
14、动计算得到 AWi/Wi。 J12“=I12/H12” ，然后下拉自动计算。 i 用 AVERAGE 算术平均函数求得 h 步骤结果的平均值，即最大特征根max。 max=J16 “=AVERAGE(J12:J15)” 。 j 编辑公式计算平均一致性指标 CI=(max-n)/(n-1)。本例中目标层的 n=4，准则层的 n=3，故 CI=K15“=(J16-4)/(4-1)” 。 k 通过查阅平均随机一致性指标 RI 和编辑公式计算判断矩阵的随机一致性比例 CR=CI/RI，是否符合 CR0.10。本例中 4 阶矩阵的 RI=0.8931，3 阶矩阵的 RI=0.51491，故 CR=L15
15、“=K15/0.8931” 。 以上是层次单排序计算过程，列举的具体演算针对的是图 2 中的第一个计算表， 其他计算表原理相同。在 Excel 中，只要先列出一个过程，其余类似的计算过程可以通过复制和少量的修改来完成（见图 2 中的 3 个计算表和图 3 中的前 2 个计算表） ，加上使用自动计算，故计算表格虽多，工作量并不大。 （4）层次总排序计算 当所有的层次单排序计算都完成后，就可以如下表所示计算出层次总排序结果，为了更加直观，在 Excel 中计算还可以细化，先算出 aibin，再计算aibin，即得到总排序结果（见图3下半部分） 。 （5）层次总排序一致性检验 紧跟在层次总排序计算表
16、后通过编辑等式，引用列出与层次总排序对应的单排序的一致 性指标和平均随机一致性指标，用 SUMPRODUCT 数组对应元素乘积求和函数求得层次总 排序一致性指标 CI=aiCIi 和层次总排序平均随机一致性指标 RI=aiRIi，再算出层次总排序 随机一致性比例 CR=CI/RI，判断是否符合 CR0.10（见图 3 中的第 55-58 行）。本例中在图3的 I57、I58 单元格出现相同的随机一致性比例 CR 值，而 I57“=G57/H57” ，I58 “=SUMPRODUCT(B51:E51,B58:E58)” ，表明两种计算可以得到同样的结果。 （6）根据需要进行调整 对于层次单排序结果和层次总排序结果，只要符合满意一致性即随机一致性比例 CR 0.10 就可以结束计算并认同排序结果，否则就要返回调整不符合满意一致性的判断矩阵。在层次分析法 Excel 算法中，返回
