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

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

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

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

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

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

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

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

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

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

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

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

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

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

14、動計算得到 AWi/Wi。 J12“=I12/H12” ，然后下拉自動計算。 i 用 AVERAGE 算術平均函數(shù)求得 h 步驟結(jié)果的平均值，即最大特征根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）層次總排序計算 當所有的層次單排序計算都完成后，就可以如下表所示計算出層次總排序結(jié)果，為了更加直觀，在 Excel 中計算還可以細化，先算出 aibin，再計算aibin，即得到總排序結(jié)果（見圖3下半部分） 。 （5）層次總排序一致性檢驗 緊跟在層次總排序計算表

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

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

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

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

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