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

用电子表格（Excel）实现

层次分析法（AHP）的简捷计算

先锋

（华南农业大学林学院，广东广州510640）

摘要：传统的层次分析法算法具有构造判断矩阵不容易、计算繁多重复且易出错、一致性调整比较麻烦等缺点。层次分析法Excel算法利用常用的办公软件电子表格（Excel）的运算功能，设置简明易懂的计算表格和步骤，使得判断矩阵的构造、层次单排序和层次总排序的计算以及一致性检验和检验之后对判断矩阵的调整变得十分简单。从而可以为层次分析法的学习、应用、推广和改进探讨提供方便。

关键词：层次分析法Excel

1层次分析法（AHP）的应用难点

层次分析法（Analytical Hierarchy Process，简称AHP），是美国匹兹堡大学教授A.L.Saaty 于20世纪70年代提出的一种系统分析方法，它综合了定性与定量分析，模拟人的决策思维过程，具有思路清晰、方法简便、适用面广、系统性强等特点，是分析多目标、多因素、多准则的复杂大系统的有力工具。层次分析法的基本原理简单说就是用下一层次因素的相对排序来求得上一层次因素的相对排序。应用层次分析法解决问题的思路是：首先把要解决的问题分出系列层次，即根据问题的性质和要达到的目标将问题分解为不同的组成因素，按照因素之间的相互影响和隶属关系将各层次各因素聚类组合，形成一个递阶的有序的层次结构模型；然后对模型中每一层次每一因素的相对重要性，依据人们对客观现实的判断给予定量表示（也可以先进行定性判断，再予赋值量化），再利用数学方法确定每一层次全部因素相对重要性次序的权值；最后通过综合计算各层因素相对重要性的权值，得到最低层（方案层）相对于较高层（分目标或准则层）和最高层（总目标）的相对重要性次序的组合权值，以此进行进行方案排序，作为评价和选择方案的依据。

层次分析法在多个领域得到广泛应用，但在应用中也是确实存在着不少难点。

1.1构造一个合适的判断矩阵不容易

建立层次结构模型和构造判断矩阵是层次分析法的主要基本工作，构造判断矩阵是关键之关键。要从“1/9-9”的数字范围内挑选标度值并要尽量符合判断的“一致性”，构造合适的判断矩阵比建立层次结构模型困难得多，特别是要构造5阶以上的高阶判断矩阵的话。层次分析法的使用就是为了解决多目标、多准则、多层次的复杂系统问题，但是系统越复杂，涉及层次和因素越多，构造合适的判断矩阵就越加困难。

1.2计算繁多、重复且易出错

层次分析法计算的根本问题是如何计算出判断矩阵的最大特征根λmax及其对应的正规化特征向量W，向量W的分量W i即是相应因素的单排序的权值，或者直接称为层次单排序结果。常用的计算方法有幂法、和积法、方根法等，计算原理本来简单，但过程却依涉及因素的增多而趋于复杂、繁琐，其中包括很多重复或相似的运算，令人不胜其烦且易出错。如果使用电脑计算，加之已有人开发出相应的程序，上述计算工作已经大为简化。但是现有的层次分析法程序都是另行编制的，需要重新安装才能使用，里面所涉及的Basic语言等亦

非现在众多普通的“视窗”一族所熟悉，故至今使用者少。从亲身观察和文章分析来看，学校里的多数学生以及目前的部分研究者仍然是抱着计算器来计算层次分析法，工作量大、精确性差，有待改进。

1.3如果达不到满意一致性“返工”调整比较麻烦

层次分析法计算不单是要得到一个结果，而且是要得到一个具有满意一致性的结果，否则排序结果没有实用意义。如果一致性检验通不过，就要调整或重新构造判断矩阵，每调整或重构一次判断矩阵，与之相应的计算过程和一致性检验就要全盘重来一次，工作量成倍增大不说，二次出错的可能性也增加了。事实上在进行多因素分析，需要构造高阶矩阵的时候，一次成功的机会并不多，“返工”调整是经常的事。

1.4以上难点的不良后果

以上难点对层次分析法的学习、推广和应用构成阻滞。许多人因为层次分析法计算复杂、验算困难而失去学习层次分析法的耐心和信心，也因此不敢或不愿使用层次分析法解决现实决策问题，特别是面对多种因素需要构造复杂的高阶判断矩阵的时候。在学而烦、学不会、不敢用、用不准的心理影响下，层次分析法的应用和推广价值受到很大削弱。

2用电子表格（Excel）计算层次分析法的基本原理和步骤为了解决以上难题，为了让层次分析法的学习变得简单易行，为了让普通人士都可以轻松应用层次分析法，笔者尝试利用现在常用的办公软件电子表格（Excel）的运算功能，设置简明易懂的计算表格和步骤，使得判断矩阵的构造、层次单排序（权重系数）和层次总排序的计算以及一致性检验和检验之后对判断矩阵的调整变得十分简单。因为是以Excel为运算载体，故称之为层次分析法Excel算法。

2.1层次分析法Excel算法的基本原理

层次分析法Excel算法充分利用Excel的函数运算、公式编辑、自动计算等功能和单元格等式引用规则，设计成步步相连的计算过程，达到只要输入一个判断值（矩阵标度值）就可以立即得到相应的各层次单排序和总排序结果以及一致性检验指标的目的。如果对结果不满意，可以通过调整判断矩阵的标度值来修正结果，调整可以是任意的，每次调整的结果也是一步得出。

2.2层次分析法Excel算法的运算设计

2.2.1层次分析法的运算步骤简介

层次分析法的主要运算步骤包括：建立层次结构模型；构造判断矩阵；用和积法或方根法等求得特征向量W（向量W的分量W i即为层次单排序）；计算最大特征根λ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中分别输入经济效益、社会效益、生态效益、技术要求等文本的时候，会立即出现在矩阵区的右上边栏。其他凡是重复出现的文本或数值亦都用此方法引用，从而构成“一动俱动”，方便调整的效果。

准则层B 图1树种选择的层次结构模型

目标层A 方案层C

图2 部分层次单排序计算

图3 部分层次单排序计算和总排序计算

（2）层次单排序计算

c用PRODUCT乘积函数和自动计算实现矩阵元素按行相乘。比如将单元格F12编辑成“= PRODUCT(B12:E12)”，然后鼠标左键按住单元格F12下拉，即可得到其余F13到F15

的运算结果。

5d 用POWER 乘幂函数和自动计算实现将c 步骤所得乘积分别开n 次方（即1/n 次幂）。

比如编辑G12“=POWER(F12,1/4)”再下拉自动计算。

e 用SUM 求和函数求得d 步骤结果的总和。即G16“=SUM(G12:G15)”。

f 将d 步骤值分别除以e 步骤值，得到特征向量W 及其分量W i ，即层次单排序结果。

编辑首个单元格H12“=G12/G$16”即可，其余通过下拉自动计算。

（3）判断矩阵一致性检验

g 将判断矩阵的各行元素分别与向量W 的分量W i 相乘之后相加，得到向量AW 及其分

量AW i 。本步骤可以直接编辑乘积求和公式再自动计算，比如可以编辑I12“=B12*H$12+C12*H$13+D12*H$14+E12*H$15”再下拉自动计算，也可以先将横排的矩阵元素通过编辑等式引用成竖排，然后用SUMPRODUCT 数组对应元素乘积求和函数进行自动计算。

h 将AW i 分别除以W i 并自动计算得到AW i /W i 。J12“=I12/H12”，然后下拉自动计算。

i 用A VERAGE 算术平均函数求得h 步骤结果的平均值，即最大特征根λmax 。λmax=J16

“=A VERAGE(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 中计算还可以细化，先算出a i b i n ，再计算∑a i b i n ，即得到总排序结果（见图3下半部分）。

表1 层次总排序计算表 A 1

A 2 … A m 层次A a 1

a 2 … a m B 层次的总排序 B 1

b 11

b 21 … b m 1 ∑=m i i i b a 11 B 2 b 12

b 22 … b m 2 ∑=m i i i b a 12 … …

… … … …

B n b 1n b 2n … b m n ∑=m i i n i b a 1

1 郭亚军著，《综合评价理论与方法》，科学出版社，2002.8，第32页

（5）层次总排序一致性检验

紧跟在层次总排序计算表后通过编辑等式，引用列出与层次总排序对应的单排序的一致性指标和平均随机一致性指标，用SUMPRODUCT数组对应元素乘积求和函数求得层次总排序一致性指标CI=∑a i CI i和层次总排序平均随机一致性指标RI=∑a i RI i，再算出层次总排序随机一致性比例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”变成了“0.2678、0.2339、0.4984”。此时的层次总排序已经符合满意一致性，但仍存在瑕疵，因为方案层对于技术要求准则的层次单排序的CR=0.13169＞0.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算法的简便和快捷。

A New Arithmetic Using Microsoft Excel for Windows

on the Analytic Hierarchy Process

xianfeng

(Forestry College of South China Agricultural University，Guangzhou，Guangdong，510640)

Abstract：Traditional algorithm on the Analytic Hierarchy Process has following defect: Not easy in construction of appropriate judgement matrix, Calculation in AHP is various, repetition and making mistakes easily, comparatively trouble in consistency checking for the AHP Judgment Matrix，etc. The new arithmetic using Excel on the Analytic Hierarchy Process adopt the office software Excel’s algorithm in common use ，Put up concise comprehensible calculational sheet and process ，Cause constituting judgement Matrix, single hierarchy ordering, total hierarchy ordering and consistency checking and adjusting change into fully simply .Thereby AHP may be learning, application, spreading and improve conveniently .

Key words：AHP ; Excel