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

用电子表格（Excel）实现层次分析法（AHP）的简捷计算先锋（华南农业大学林学院，广东广州510640）摘要：传统的层次分析法算法具有构造判断矩阵不容易、计算繁多重复且易出错、一致性调整比较麻烦等缺点。层次分析法Excel算法利用常用的办公软件电子表格（Excel）的运算功能，设置简明易懂的计算表格和步骤，使得判断矩阵的构造、层次单排序和层次总排序的计算以及一致性检验和检验之后对判断矩阵的调整变得十分简单。从而可以为层次分析法的学习、应用、推广和改进探讨提供方便。关键词：层次分析法Excel1层次分析法（AHP）的应用难点层次分析法（AnalyticalHierarchyProcess,简称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，是否符合CR≤0.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，判断是否符合CR≤0.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.17181＞0.10，方案层对于技术要求准则的层次单排序的CR=0.13169＞0.10，以及层次总排序的CR=0.1186979＞0.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”变