用電子表格Excel實(shí)現(xiàn)層次分析法AHP的簡(jiǎn)捷計(jì)算

1、用電子表格（Excel）實(shí)現(xiàn)層次分析法（AHP）的簡(jiǎn)捷計(jì)算先鋒 （華南農(nóng)業(yè)大學(xué) 林學(xué)院，廣東 廣州 510640）摘要：傳統(tǒng)的層次分析法算法具有構(gòu)造判斷矩陣不容易、計(jì)算繁多重復(fù)且易出錯(cuò)、一致性調(diào) 整比較麻煩等缺點(diǎn)。層次分析法 Excel 算法利用常用的辦公軟件電子表格（Excel）的運(yùn)算功能，設(shè)置簡(jiǎn)明易懂的計(jì)算表格和步驟，使得判斷矩陣的構(gòu)造、層次單排序和層次總排序的計(jì)算以及一致性檢驗(yàn)和檢驗(yàn)之后對(duì)判斷矩陣的調(diào)整變得十分簡(jiǎn)單。從而可以為層次分析法的學(xué)習(xí)、應(yīng)用、推廣和改進(jìn)探討提供方便。 關(guān)鍵詞：層次分析法 Excel1 層次分析法（AHP）的應(yīng)用難點(diǎn) 層次分析法 （Analytical Hierar

2、chy Process,簡(jiǎn)稱 AHP）是美國(guó)匹茲堡大學(xué)教授 A.L.Saaty ， 于 20 世紀(jì) 70 年代提出的一種系統(tǒng)分析方法， 它綜合了定性與定量分析， 模擬人的決策思維 過程，具有思路清晰、方法簡(jiǎn)便、適用面廣、系統(tǒng)性強(qiáng)等特點(diǎn)，是分析多目標(biāo)、多因素、多準(zhǔn)則的復(fù)雜大系統(tǒng)的有力工具。層次分析法的基本原理簡(jiǎn)單說就是用下一層次因素的相對(duì)排序來求得上一層次因素的相對(duì)排序。 應(yīng)用層次分析法解決問題的思路是：首先把要解決的問題分出系列層次，即根據(jù)問題的性質(zhì)和要達(dá)到的目標(biāo)將問題分解為不同的組成因素，按照因素之間的相互影響和隸屬關(guān)系將各層次各因素聚類組合,形成一個(gè)遞階的有序的層次結(jié)構(gòu)模型；然后對(duì)模型中每

3、一層次每一因素的相對(duì)重要性，依據(jù)人們對(duì)客觀現(xiàn)實(shí)的判斷給予定量表示（也可以先進(jìn)行定性判斷，再予賦值量化），再利用數(shù)學(xué)方法確定每一層次全部因素相對(duì)重要性次序的權(quán)值；最后通過綜合計(jì)算各層因素相對(duì)重要性的權(quán)值，得到最低層（方案層）相對(duì)于較高層（分目標(biāo)或準(zhǔn)則層）和最高層（總目標(biāo)）的相對(duì)重要性次序的組合權(quán)值，以此 進(jìn)行進(jìn)行方案排序，作為評(píng)價(jià)和選擇方案的依據(jù)。 層次分析法在多個(gè)領(lǐng)域得到廣泛應(yīng)用，但在應(yīng)用中也是確實(shí)存在著不少難點(diǎn)。1.1 構(gòu)造一個(gè)合適的判斷矩陣不容易 建立層次結(jié)構(gòu)模型和構(gòu)造判斷矩陣是層次分析法的主要基本工作，構(gòu)造判斷矩陣是關(guān)鍵之關(guān)鍵。要從“1/9-9”的數(shù)字范圍內(nèi)挑選標(biāo)度值并要盡量符合判斷的“

4、一致性” ，構(gòu)造合適的判斷矩陣比建立層次結(jié)構(gòu)模型困難得多，特別是要構(gòu)造 5 階以上的高階判斷矩陣的話。層次分析法的使用就是為了解決多目標(biāo)、多準(zhǔn)則、多層次的復(fù)雜系統(tǒng)問題，但是系統(tǒng)越復(fù)雜，涉及層次和因素越多，構(gòu)造合適的判斷矩陣就越加困難。1.2 計(jì)算繁多、重復(fù)且易出錯(cuò) 層次分析法計(jì)算的根本問題是如何計(jì)算出判斷矩陣的最大特征根max 及其對(duì)應(yīng)的正 規(guī)化特征向量w，向量w的分量 Wi 即是相應(yīng)因素的單排序的權(quán)值，或者直接稱為層次單排序結(jié)果。常用的計(jì)算方法有冪法、和積法、方根法等，計(jì)算原理本來簡(jiǎn)單，但過程卻仍因涉及因素的增多而趨于復(fù)雜、繁瑣，其中包括很多重復(fù)或相似的運(yùn)算，令人不勝其煩且易出錯(cuò)。如果使用電

5、腦計(jì)算，加之已有人開發(fā)出相應(yīng)的程序，上述計(jì)算工作已經(jīng)大為簡(jiǎn)化。但是現(xiàn)有 的層次分析法程序都是另行編制的，需要重新安裝才能使用，里面所涉及的 Basic 語(yǔ)言等亦非現(xiàn)在眾多普通的“視窗”一族所熟悉，故至今使用者少。從親身觀察和文章分析來看，學(xué)校里的多數(shù)學(xué)生以及目前的部分研究者仍然是抱著計(jì)算器來計(jì)算層次分析法， 工作量大、 精確性差，有待改進(jìn)。1.3 如果達(dá)不到滿意一致性“返工”調(diào)整比較麻煩 層次分析法計(jì)算不單是要得到一個(gè)結(jié)果，而且是要得到一個(gè)具有滿意一致性的結(jié)果， 否 則排序結(jié)果沒有實(shí)用意義。如果一致性檢驗(yàn)通不過，就要調(diào)整或重新構(gòu)造判斷矩陣，每調(diào)整 或重構(gòu)一次判斷矩陣， 與之相應(yīng)的計(jì)算過程和一致

6、性檢驗(yàn)就要全盤重來一次， 工作量成倍增大不說， 二次出錯(cuò)的可能性也增加了。事實(shí)上在進(jìn)行多因素分析，需要構(gòu)造高階矩陣的時(shí)候，一次成功的機(jī)會(huì)并不多，“返工”調(diào)整是經(jīng)常的事。1.4 以上難點(diǎn)的不良后果 以上難點(diǎn)對(duì)層次分析法的學(xué)習(xí)、 推廣和應(yīng)用構(gòu)成阻滯。許多人因?yàn)閷哟畏治龇ㄓ?jì)算復(fù)雜、驗(yàn)算困難而失去學(xué)習(xí)層次分析法的耐心和信心，也因此不敢或不愿使用層次分析法解決現(xiàn)實(shí)決策問題，特別是面對(duì)多種因素需要構(gòu)造復(fù)雜的高階判斷矩陣的時(shí)候。在學(xué)而煩、學(xué)不會(huì)、不敢用、用不準(zhǔn)的心理影響下，層次分析法的應(yīng)用和推廣價(jià)值受到很大削弱。2 用電子表格（Excel）計(jì)算層次分析法的基本原理和步驟 為了解決以上難題，為了讓層次分析法的

7、學(xué)習(xí)變得簡(jiǎn)單易行，為了讓普通人士都可以輕 松應(yīng)用層次分析法，筆者嘗試?yán)矛F(xiàn)在常用的辦公軟件電子表格（Excel）的運(yùn)算功能，設(shè)置簡(jiǎn)明易懂的計(jì)算表格和步驟，使得判斷矩陣的構(gòu)造、層次單排序（權(quán)重系數(shù)）和層次總排序的計(jì)算以及一致性檢驗(yàn)和檢驗(yàn)之后對(duì)判斷矩陣的調(diào)整變得十分簡(jiǎn)單。因?yàn)槭且?Excel 為運(yùn) 算載體，故稱之為層次分析法 Excel 算法。2.1 層次分析法 Excel 算法的基本原理 層次分析法 Excel 算法充分利用 Excel 的函數(shù)運(yùn)算、公式編輯、自動(dòng)計(jì)算等功能和單元格等式引用規(guī)則，設(shè)計(jì)成步步相連的計(jì)算過程，達(dá)到只要輸入一個(gè)判斷值（矩陣標(biāo)度值）就可以立即得到相應(yīng)的各層次單排序和總排序

8、結(jié)果以及一致性檢驗(yàn)指標(biāo)的目的。如果對(duì)結(jié)果不滿意，可以通過調(diào)整判斷矩陣的標(biāo)度值來修正結(jié)果，調(diào)整可以是任意的，每次調(diào)整的結(jié)果也是一步得出。2.2 層次分析法 Excel 算法的運(yùn)算設(shè)計(jì)2.2.1 層次分析法的運(yùn)算步驟簡(jiǎn)介層次分析法的主要運(yùn)算步驟包括：建立層次結(jié)構(gòu)模型；構(gòu)造判斷矩陣；用和積法或方根 法等求得特征向量 W（向量 W 的分量 Wi 即為層次單排序）；計(jì)算最大特征根max；計(jì)算一致性指標(biāo) CI、RI、CR 并判斷是否具有滿意的一致性。該步驟已經(jīng)為人熟悉，故不詳述。2.2.2 用實(shí)例說明的層次分析法 Excel 算法過程 例： 假設(shè)某林業(yè)經(jīng)濟(jì)單位要選擇適當(dāng)?shù)臉浞N來調(diào)整經(jīng)濟(jì)結(jié)構(gòu)， 樹種選擇考慮

9、的因素包括四個(gè)方面：經(jīng)濟(jì)效益、生態(tài)效益、社會(huì)效益和技術(shù)要求，可選樹種包括松樹、杉木和桉樹，請(qǐng)問應(yīng)當(dāng)怎樣對(duì)供選樹種進(jìn)行優(yōu)劣排序？根據(jù)題意可以建立層次結(jié)構(gòu)模型如圖1： 層次分析法的計(jì)算方法有多種， 假如判斷矩陣是完全一致的話，用和積法或方根法計(jì)算的結(jié)果是一樣的，如果判斷矩陣不一致，那么計(jì)算出的權(quán)重系數(shù)值會(huì)有不同，但排序次序應(yīng)當(dāng)一樣。由于和積法需要進(jìn)行列規(guī)范化，相當(dāng)于又形成一個(gè)矩陣，占用的頁(yè)面會(huì)比方根法稍大，故本文按方根法依照前述步驟在電子表格（Excel）中設(shè)計(jì)出層次分析法運(yùn)算過程如下 （見下頁(yè)圖 2 和圖 3） ： （1）判斷矩陣的設(shè)置和矩陣元素的輸入 a 判斷矩陣表格化和“一邊倒” 由于是在

10、Excel 中運(yùn)作，判斷矩陣要制成表格形式，形成沒有矩陣形狀的矩陣區(qū)（見圖 1 的“B12:D15”區(qū)域。在矩陣區(qū)的主對(duì)角線單元格全部輸入數(shù)值 1，以此主對(duì)角線為分界，右上角單元格對(duì)稱地編輯成左下角單元格的倒數(shù)（比如把 E12 單元格編輯成“=1/B15” ），簡(jiǎn)稱“一邊倒” ，目的是一旦在左下角單元格中輸入數(shù)值，就可以立即得出右上角的相應(yīng)的倒數(shù)（比如在 B15 單元格中輸入 1/2，E12 單元格立即出現(xiàn) 2），需要調(diào)整判斷矩陣的時(shí)候也只需變動(dòng)矩陣區(qū)左下角的數(shù)字。判斷矩陣通常采用的是比例標(biāo)度，為了表達(dá)這種習(xí)慣形式，可以通過“設(shè)置單元格格式”把矩陣區(qū)設(shè)置成“數(shù)字分?jǐn)?shù)”形式，這樣無(wú)論輸入什么數(shù)值

11、都將表現(xiàn)為分?jǐn)?shù)或整數(shù)。 b 標(biāo)題和因素名稱（指標(biāo)）的輸入 為了讓運(yùn)算清晰有序，標(biāo)題和指標(biāo)（或因素名稱）以及運(yùn)算提示比如“按行相乘”“開n次方”“CI=(-n)/(n-1)”等不應(yīng)省略。在 Excel 中輸入文本亦有省事的技巧，比如可以將單元格 B12、C12、D12、E12 分別編輯成“=A12”“=A13”“=A14”“=A15”（凡如“=？” 均表示在 Excel 中的編輯形式，以下同），這樣當(dāng)在矩陣區(qū)左邊欄單元格 A12、A13、A14、 A15 中分別輸入經(jīng)濟(jì)效益、社會(huì)效益、生態(tài)效益、技術(shù)要求等文本的時(shí)候，會(huì)立即出現(xiàn)在矩 陣區(qū)的右上邊欄。 其他凡是重復(fù)出現(xiàn)的文本或數(shù)值亦都用此方法引用，

12、從而構(gòu)成 “一動(dòng)俱動(dòng)”，方便調(diào)整的效果。（2）層次單排序計(jì)算 c 用 PRODUCT 乘積函數(shù)和自動(dòng)計(jì)算實(shí)現(xiàn)矩陣元素按行相乘。 比如將單元格 F12 編輯成 “= PRODUCT(B12:E12)” ，然后鼠標(biāo)左鍵按住單元格 F12 下拉，即可得到其余 F13 到 F15 的運(yùn)算結(jié)果。 d 用 POWER 乘冪函數(shù)和自動(dòng)計(jì)算實(shí)現(xiàn)將 c 步驟所得乘積分別開 n 次方（即 1/n 次冪）。比如編輯 G12“=POWER(F12,1/4)”再下拉自動(dòng)計(jì)算。 e 用 SUM 求和函數(shù)求得 d 步驟結(jié)果的總和。即 G16“=SUM(G12:G15)” 。 f 將 d 步驟值分別除以e步驟值，得到特征向量

13、W及其分量Wi，即層次單排序結(jié)果。編輯首個(gè)單元格 H12“=G12/G$16”即可，其余通過下拉自動(dòng)計(jì)算。 （3）判斷矩陣一致性檢驗(yàn) g 將判斷矩陣的各行元素分別與向量 W 的分量 Wi 相乘之后相加， 得到向量 AW 及其分量 AWi。本 步 驟 可 以 直 接 編 輯 乘 積 求 和 公 式 再 自 動(dòng) 計(jì) 算 ， 比 如 可 以 編 輯 I12 “=B12*H$12+C12*H$13+D12*H$14+E12*H$15”再下拉自動(dòng)計(jì)算，也可以先將橫排的矩陣元素通過編輯等式引用成豎排，然后用 SUMPRODUCT 數(shù)組對(duì)應(yīng)元素乘積求和函數(shù)進(jìn)行自動(dòng)計(jì)算。 h 將 AWi 分別除以 Wi 并自

14、動(dòng)計(jì)算得到 AWi/Wi。 J12“=I12/H12” ，然后下拉自動(dòng)計(jì)算。 i 用 AVERAGE 算術(shù)平均函數(shù)求得 h 步驟結(jié)果的平均值，即最大特征根max。 max=J16 “=AVERAGE(J12:J15)” 。 j 編輯公式計(jì)算平均一致性指標(biāo) CI=(max-n)/(n-1)。本例中目標(biāo)層的 n=4，準(zhǔn)則層的 n=3，故 CI=K15“=(J16-4)/(4-1)” 。 k 通過查閱平均隨機(jī)一致性指標(biāo) RI 和編輯公式計(jì)算判斷矩陣的隨機(jī)一致性比例 CR=CI/RI，是否符合 CR0.10。本例中 4 階矩陣的 RI=0.8931，3 階矩陣的 RI=0.51491，故 CR=L15

15、“=K15/0.8931” 。 以上是層次單排序計(jì)算過程，列舉的具體演算針對(duì)的是圖 2 中的第一個(gè)計(jì)算表， 其他計(jì)算表原理相同。在 Excel 中，只要先列出一個(gè)過程，其余類似的計(jì)算過程可以通過復(fù)制和少量的修改來完成（見圖 2 中的 3 個(gè)計(jì)算表和圖 3 中的前 2 個(gè)計(jì)算表） ，加上使用自動(dòng)計(jì)算，故計(jì)算表格雖多，工作量并不大。 （4）層次總排序計(jì)算 當(dāng)所有的層次單排序計(jì)算都完成后，就可以如下表所示計(jì)算出層次總排序結(jié)果，為了更加直觀，在 Excel 中計(jì)算還可以細(xì)化，先算出 aibin，再計(jì)算aibin，即得到總排序結(jié)果（見圖3下半部分） 。 （5）層次總排序一致性檢驗(yàn) 緊跟在層次總排序計(jì)算表

16、后通過編輯等式，引用列出與層次總排序?qū)?yīng)的單排序的一致 性指標(biāo)和平均隨機(jī)一致性指標(biāo)，用 SUMPRODUCT 數(shù)組對(duì)應(yīng)元素乘積求和函數(shù)求得層次總 排序一致性指標(biāo) CI=aiCIi 和層次總排序平均隨機(jī)一致性指標(biāo) RI=aiRIi，再算出層次總排序 隨機(jī)一致性比例 CR=CI/RI，判斷是否符合 CR0.10（見圖 3 中的第 55-58 行）。本例中在圖3的 I57、I58 單元格出現(xiàn)相同的隨機(jī)一致性比例 CR 值，而 I57“=G57/H57” ，I58 “=SUMPRODUCT(B51:E51,B58:E58)” ，表明兩種計(jì)算可以得到同樣的結(jié)果。 （6）根據(jù)需要進(jìn)行調(diào)整 對(duì)于層次單排序結(jié)

17、果和層次總排序結(jié)果，只要符合滿意一致性即隨機(jī)一致性比例 CR 0.10 就可以結(jié)束計(jì)算并認(rèn)同排序結(jié)果，否則就要返回調(diào)整不符合滿意一致性的判斷矩陣。在層次分析法 Excel 算法中，返回調(diào)整只需要改動(dòng)判斷矩陣，即只要?jiǎng)?a 步驟就可以了，a 步驟動(dòng)則上述（1）-（6）步驟全盤皆動(dòng)，新的計(jì)算結(jié)果立即出現(xiàn)，新的一致性檢驗(yàn)也同步進(jìn)行。在本例中方案層對(duì)于經(jīng)濟(jì)效益準(zhǔn)則的層次單排序的 CR=0.171810.10，方案層對(duì)于技術(shù)要求準(zhǔn)則的層次單排序的 CR=0.131690.10，以及層次總排序的 CR=0.11869790.10，均不符合滿意一致性（圖 2、圖 3 中不符合滿意一致性的單元格 K23、K4

18、7、I57 和 I58 有意加了顏色表示），故需要調(diào)整。由于運(yùn)算過程已經(jīng)緊密扣接，故調(diào)整成為輕而易舉的事，比如當(dāng)把方案層對(duì)于經(jīng)濟(jì)效益準(zhǔn)則的判斷矩陣中的 B22、C22單元格數(shù)值分別改為 6、9，就會(huì)發(fā)現(xiàn)不僅 K23 單元格的 CR 值變成了0.00894，而且 B57 單元格的層次總排序 CR 值也隨之變成了 0.0401851， 排序數(shù)值也因之發(fā)生變動(dòng)，3 種樹種的排序由“0.2746、0.2534、0.472” 變成了“0.2678、0.2339、0.4984” 。此時(shí)的層次總排序已經(jīng)符合滿意一致性，但仍存在瑕疵，因?yàn)榉桨笇訉?duì)于技術(shù)要求準(zhǔn)則的層次單排序的 CR=0.131690.10，還是

19、不符合滿意一致性 的，于是可以再行調(diào)整，亦是一步到位，當(dāng)把方案層對(duì)于技術(shù)要求準(zhǔn)則的判斷矩陣中的 B46、 C46 單元格數(shù)值改為 1/3、1/4，就會(huì)發(fā)現(xiàn) K47 單元格的 CR值變成了 0.01777，樹種總排序結(jié)果變成了“0.2566、0.2395、0.5039” ，層次總排序的 CR值變成了 0.0193216，至此無(wú)論單排序還是總排序均符合滿意一致性，排序結(jié)果即可認(rèn)同（關(guān)于調(diào)整后的計(jì)算表與圖 2、圖 3 只有少許差別，故略）。3 層次分析法 Excel 算法的優(yōu)勢(shì)總結(jié)3.1 應(yīng)用條件易得層次分析法 Excel 算法以廣泛使用的辦公軟件 Excel 作為運(yùn)算平臺(tái)， 普通電腦都可安裝，尋常

20、人士多會(huì)使用，無(wú)需掌握深?yuàn)W的計(jì)算機(jī)專業(yè)知識(shí)和術(shù)語(yǔ)，有很好的推廣應(yīng)用基礎(chǔ)。3.2 計(jì)算結(jié)果精確層次分析法 Excel算法的所有計(jì)算結(jié)果和數(shù)據(jù)均保留最高位數(shù)的精確度，可以不在任何 環(huán)節(jié)進(jìn)行四舍五入，當(dāng)然也可以根據(jù)需要設(shè)置小數(shù)位，從而最大限度地減少了誤差。3.3 計(jì)算過程簡(jiǎn)捷 層次分析法 Excel 算法的計(jì)算步驟設(shè)計(jì)成環(huán)環(huán)相扣、步步跟蹤，步驟設(shè)計(jì)完畢后，只有判斷矩陣的一半（本文選的是矩陣左下角， 用右上角結(jié)果完全一樣） 可以按需要填充或變更，其余數(shù)據(jù)和結(jié)果均可以在填充或變更判斷矩陣之后立即得出，使得整個(gè)運(yùn)算過程簡(jiǎn)捷、輕松。另外，相似的矩陣區(qū)和計(jì)算區(qū)可以通過復(fù)制完成，只需改動(dòng)少量單元格。3.4 一致性檢驗(yàn)方便 層次分析法 Excel 算法將一致性檢驗(yàn)也同時(shí)計(jì)算出來，決策者和判斷者可以即時(shí)知道自己的判斷是否具有滿意的一致性并可以隨時(shí)和簡(jiǎn)單地進(jìn)行調(diào)整直到符合滿意一致