用电子表格(Excel)实现层次分析法(AHP)的简捷计算.doc

用电子表格（Excel）实现层次分析法（AHP）的简捷计算先锋 （华南农业大学 林学院，广东 广州 510640）摘要：传统的层次分析法算法具有构造判断矩阵不容易、计算繁多重复且易出错、一致性调 整比较麻烦等缺点。层次分析法 Excel 算法利用常用的办公软件电子表格（Excel）的运算功能，设置简明易懂的计算表格和步骤，使得判断矩阵的构造、层次单排序和层次总排序的计算以及一致性检验和检验之后对判断矩阵的调整变得十分简单。从而可以为层次分析法的学习、应用、推广和改进探讨提供方便。 关键词：层次分析法 Excel1 层次分析法（AHP）的应用难点 层次分析法 （Analytical Hierarchy Process,简称 AHP）是美国匹兹堡大学教授 A.L.Saaty ， 于 20 世纪 70 年代提出的一种系统分析方法， 它综合了定性与定量分析， 模拟人的决策思维 过程，具有思路清晰、方法简便、适用面广、系统性强等特点，是分析多目标、多因素、多准则的复杂大系统的有力工具。层次分析法的基本原理简单说就是用下一层次因素的相对排序来求得上一层次因素的相对排序。 应用层次分析法解决问题的思路是：首先把要解决的问题分出系列层次，即根据问题的性质和要达到的目标将问题分解为不同的组成因素，按照因素之间的相互影响和隶属关系将各层次各因素聚类组合,形成一个递阶的有序的层次结构模型；然后对模型中每一层次每一因素的相对重要性，依据人们对客观现实的判断给予定量表示（也可以先进行定性判断，再予赋值量化），再利用数学方法确定每一层次全部因素相对重要性次序的权值；最后通过综合计算各层因素相对重要性的权值，得到最低层（方案层）相对于较高层（分目标或准则层）和最高层（总目标）的相对重要性次序的组合权值，以此 进行进行方案排序，作为评价和选择方案的依据。 层次分析法在多个领域得到广泛应用，但在应用中也是确实存在着不少难点。1.1 构造一个合适的判断矩阵不容易 建立层次结构模型和构造判断矩阵是层次分析法的主要基本工作，构造判断矩阵是关键之关键。要从“1/9-9”的数字范围内挑选标度值并要尽量符合判断的“一致性” ，构造合适的判断矩阵比建立层次结构模型困难得多，特别是要构造 5 阶以上的高阶判断矩阵的话。层次分析法的使用就是为了解决多目标、多准则、多层次的复杂系统问题，但是系统越复杂，涉及层次和因素越多，构造合适的判断矩阵就越加困难。1.2 计算繁多、重复且易出错 层次分析法计算的根本问题是如何计算出判断矩阵的最大特征根max 及其对应的正 规化特征向量w，向量w的分量 Wi 即是相应因素的单排序的权值，或者直接称为层次单排序结果。常用的计算方法有幂法、和积法、方根法等，计算原理本来简单，但过程却仍因涉及因素的增多而趋于复杂、繁琐，其中包括很多重复或相似的运算，令人不胜其烦且易出错。如果使用电脑计算，加之已有人开发出相应的程序，上述计算工作已经大为简化。但是现有 的层次分析法程序都是另行编制的，需要重新安装才能使用，里面所涉及的 Basic 语言等亦非现在众多普通的“视窗”一族所熟悉，故至今使用者少。从亲身观察和文章分析来看，学校里的多数学生以及目前的部分研究者仍然是抱着计算器来计算层次分析法， 工作量大、 精确性差，有待改进。1.3 如果达不到满意一致性“返工”调整比较麻烦 层次分析法计算不单是要得到一个结果，而且是要得到一个具有满意一致性的结果， 否 则排序结果没有实用意义。如果一致性检验通不过，就要调整或重新构造判断矩阵，每调整 或重构一次判断矩阵， 与之相应的计算过程和一致性检验就要全盘重来一次， 工作量成倍增大不说， 二次出错的可能性也增加了。事实上在进行多因素分析，需要构造高阶矩阵的时候，一次成功的机会并不多，“返工”调整是经常的事。1.4 以上难点的不良后果 以上难点对层次分析法的学习、 推广和应用构成阻滞。许多人因为层次分析法计算复杂、验算困难而失去学习层次分析法的耐心和信心，也因此不敢或不愿使用层次分析法解决现实决策问题，特别是面对多种因素需要构造复杂的高阶判断矩阵的时候。在学而烦、学不会、不敢用、用不准的心理影响下，层次分析法的应用和推广价值受到很大削弱。2 用电子表格（Excel）计算层次分析法的基本原理和步骤 为了解决以上难题，为了让层次分析法的学习变得简单易行，为了让普通人士都可以轻 松应用层次分析法，笔者尝试利用现在常用的办公软件电子表格（Excel）的运算功能，设置简明易懂的计算表格和步骤，使得判断矩阵的构造、层次单排序（权重系数）和层次总排序的计算以及一致性检验和检验之后对判断矩阵的调整变得十分简单。因为是以 Excel 为运 算载体，故称之为层次分析法 Excel 算法。2.1 层次分析法 Excel 算法的基本原理 层次分析法 Excel 算法充分利用 Excel 的函数运算、公式编辑、自动计算等功能和单元格等式引用规则，设计成步步相连的计算过程，达到只要输入一个判断值（矩阵标度值）就可以立即得到相应的各层次单排序和总排序结果以及一致性检验指标的目的。如果对结果不满意，可以通过调整判断矩阵的标度值来修正结果，调整可以是任意的，每次调整的结果也是一步得出。2.2 层次分析法 Excel 算法的运算设计2.2.1 层次分析法的运算步骤简介层次分析法的主要运算步骤包括：建立层次结构模型；构造判断矩阵；用和积法或方根 法等求得特征向量 W（向量 W 的分量 Wi 即为层次单排序）；计算最大特征根max；计算一致性指标 CI、RI、CR 并判断是否具有满意的一致性。该步骤已经为人熟悉，故不详述。2.2.2 用实例说明的层次分析法 Excel 算法过程 例： 假设某林业经济单位要选择适当的树种来调整经济结构， 树种选择考虑的因素包括四个方面：经济效益、生态效益、社会效益和技术要求，可选树种包括松树、杉木和桉树，请问应当怎样对供选树种进行优劣排序？根据题意可以建立层次结构模型如图1： 层次分析法的计算方法有多种， 假如判断矩阵是完全一致的话，用和积法或方根法计算的结果是一样的，如果判断矩阵不一致，那么计算出的权重系数值会有不同，但排序次序应当一样。由于和积法需要进行列规范化，相当于又形成一个矩阵，占用的页面会比方根法稍大，故本文按方根法依照前述步骤在电子表格（Excel）中设计出层次分析法运算过程如下 （见下页图 2 和图 3） ： （1）判断矩阵的设置和矩阵元素的输入 a 判断矩阵表格化和“一边倒” 由于是在 Excel 中运作，判断矩阵要制成表格形式，形成没有矩阵形状的矩阵区（见图 1 的“B12:D15”区域。在矩阵区的主对角线单元格全部输入数值 1，以此主对角线为分界，右上角单元格对称地编辑成左下角单元格的倒数（比如把 E12 单元格编辑成“=1/B15” ），简称“一边倒” ，目的是一旦在左下角单元格中输入数值，就可以立即得出右上角的相应的倒数（比如在 B15 单元格中输入 1/2，E12 单元格立即出现 2），需要调整判断矩阵的时候也只需变动矩阵区左下角的数字。判断矩阵通常采用的是比例标度，为了表达这种习惯形式，可以通过“设置单元格格式”把矩阵区设置成“数字分数”形式，这样无论输入什么数值都将表现为分数或整数。 b 标题和因素名称（指标）的输入 为了让运算清晰有序，标题和指标（或因素名称）以及运算提示比如“按行相乘”“开n次方”“CI=(-n)/(n-1)”等不应省略。在 Excel 中输入文本亦有省事的技巧，比如可以将单元格 B12、C12、D12、E12 分别编辑成“=A12”“=A13”“=A14”“=A15”（凡如“=？” 均表示在 Excel 中的编辑形式，以下同），这样当在矩阵区左边栏单元格 A12、A13、A14、 A15 中分别输入经济效益、社会效益、生态效益、技术要求等文本的时候，会立即出现在矩 阵区的右上边栏。 其他凡是重复出现的文本或数值亦都用此方法引用，从而构成 “一动俱动”，方便调整的效果。（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步骤值，得到特征向量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 并自动计算得到 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“=K15/0.8931” 。 以上是层次单排序计算过程，列举的具体演算针对的是图 2 中的第一个计算表， 其他计算表原理相同。在 Excel 中，只要先列出一个过程，其余类似的计算过程可以通过复制和少量的修改来完成（见图 2 中的 3 个计算表和图 3 中的前 2 个计算表） ，加上使用自动计算，故计算表格虽多，工作量并不大。 （4）层次总排序计算 当所有的层次单排序计算都完成后，就可以如下表所示计算出层次总排序结果，为了更加直观，在 Excel 中计算还可以细化，先算出 aibin，再计算aibin，即得到总排序结果（见图3下半部分） 。 （5）层次总排序一致性检验 紧跟在层次总排序计算表后通过编辑等式，引用列出与层次总排序对应的单排序的一致 性指标和平均随机一致性指标，用 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 算法中，返回调整只需要改动判断矩阵，即只要动 a 步骤就可以了，a 步骤动则上述（1）-（6）步骤全盘皆动，新的计算结果立即出现，新的一致性检验也同步进行。在本例中方案层对于经济效益准则的层次单排序的 CR=0.171810.10，方案层对于技术要求准则的层次单排序的 CR=0.131690.10，以及层次总排序的 CR=0.11869790.10，均不符合满意一致性（图 2、图 3 中不符合满意一致性的单元格 K23、K47、I57 和 I58 有意加了颜色表示），故需要调整。由于运算过程已经紧密扣接，故调整成为轻而易举的事，比如当把方案层对于经济效益准则的判断矩阵中的 B22、C22单元格数值分别改为 6、9，就会发现不仅 K23 单元格的 CR 值变成了0.00894，而且 B57 单元格的层次总排序 CR 值也随之变成了 0.0401851， 排序数值也因之发生变动，3 种树种的排序由“0.2746、0.2534、0.472” 变成了“0.2678、0.2339、0.4984” 。此时的层次总排序已经符合满意一致性，但仍存在瑕疵，因为方案层对于技术要求准则的层次单排序的 CR=0.131690.10，还是不符合满意一致性 的，于是可以再行调整，亦是一步到位，当把方案层对于技术要求准则的判断矩阵中的 B46、 C46 单元格数值改为 1/3、1/4，就会发现 K47 单元格的 CR值变成了 0.01777，树种总排序结果变成了“0.2566、0.2395、0.5039” ，层次总排序的 CR值变成了 0.0193216，至此无论单排序还是总排序均符合满意一致性，排序结果即可认同（关于调整后的计算表与图 2、图 3 只有少许差别，故略）。3 层次分析法 Excel 算法的优势总结3.1 应用条件易得层次分析法 Excel 算法以广泛使用的办公软件 Excel 作为运算平台， 普通电脑都可安装，寻常人士多会使用，无需掌握深奥的计算机专业知识和术语，有很好的推广应用基础。3.2 计算结果精确层次分析法 Excel算法的所有计算结果和数据均保留最高位数的精确度，可以不在任何 环节进行四舍五入，当然也可以根据需要设置小数位，从而最大限度地减少了误差。3.3 计算过程简捷 层次分析法 Excel 算法的计算步骤设计成环环相扣、步步跟踪，步骤设计完毕后，只有判断矩阵的一半（本文选的是矩阵左下角， 用右上角结果完全一样） 可以按需要填充或变更，其余数据和结果均可以在填充或变更判断矩阵之后立即得出，使得整个运算过程简捷、轻松。另外，相似的矩阵区和计算区可以通过复制完成，只需改动少量单元格。3.4 一致性检验方便 层次分析法 Excel 算法将一致性检验也同时计算出来，决策者和判断者可以即时知道自己的判断是否具有满意的一致性并可以随时和简单地进行调整直到符合满意一致性。3.5 矩阵调整简单 如果一致性指标不能令人满意，用本方法可以比较容易地实现对判断矩阵的调整，可以实现对判断的“微调” ，使得逼近最大程度的“满意一致性”甚至“完全一致性”而又不必进行繁重运算成为可能。这也许是本方法最具实用价值的一点好处，笔者曾经搜看许多关于层次分析法应用的文章，发现一个有意思的现象，即绝大部分文章所举的层次分析法应用例子的排序结果都符合随机一致性比率 CR 0.10 的要求，难道文章作者和决策者们都这么幸运，一次构造判断矩阵一次计算就得到满意的排序结果，因此都无需调整判断矩阵？这是可以存疑的，根据笔者学习和运用层次分析法的经验，构造 2 阶判断矩阵自然不会有不一致的问题，如果构造 3 阶、4 阶判断矩阵就要略费思量，如果要构造更高阶的判断矩阵就需大费周折。多阶矩阵意味着计算过程更加复杂，如果遇到一致性不达标，要从判断矩阵开始调整，等于是重做一遍又一遍并且难以保证精确性，将是十分浩繁的工程。由此可以推测，现有文献中关于层次分析法的应用大多是回避了调整判断矩阵的问题，本方法的采用对于解决 此问题能提供一定帮助。3.6 为进一步探讨提供可能 关于判断矩阵的构造和调整以及层次分析法的改进引起许多学者的讨论，但是层次分析 法的传统算法由于不便进行反复运算和检验，往前跨越殊为不易，如果运用 Excel 算法，层 次分析法的改进探讨和现实应用就可能变得轻松易行。笔者通过文章分析，发现判断矩阵的标度方式和层次分析法应用的可靠性可能经不起推敲， 将另行著文进行推导和验证，正是得益于层次分析法 Excel 算法的简便和快捷。