用电子表格Excel实现层次分析法AHP的简捷计算_精品文档表格文件下载.xls

1、用电子表格（Excel）实现层次分析法（AHP）的简捷计算准则层对目标层判断树种选择 经济效益 社会效益 生态效益 技术要求 按行相乘 开N次方权重W1经济效益1 3 4 2 24 2.213363839 0.482052062社会效益 1/31 1/2 1/2 0.083333 0.537284966 0.117016155生态效益 1/42 1 2 11 0.217791604技术要求 1/22 1/21 1/2 0.840896415 0.1831401794.591545221方案层对经济效益判断经济效益 松树杉木桉树按行相乘 开N次方权重W1AW1松树1 2 1/7 0.285714

2、 0.658634 0.156996136 0.498765457杉木 1/21 1/40.1250.5 0.119183183 0.378636421桉树7 4 1 28 3.036589 0.723820681 2.2995263644.195223方案层对社会效益判断社会效益 松树杉木桉树按行相乘 开N次方权重W1AW1松树1 1/2 1/50.1 0.464159 0.122020192 0.366511392杉木2 1 1/3 0.6666670.87358 0.2296507940.68980085桉树5 3 1 15 2.466212 0.648329014 1.94738235

3、73.803951方案层对生态效益判断生态效益 松树杉木桉树按行相乘 开N次方权重W1AW1松树1 2 3 6 1.817121 0.527836133 1.611811805杉木 1/21 3 1.5 1.144714 0.332515928 1.015377811桉树 1/3 1/31 0.1111110.48075 0.139647939 0.4264319593.442585方案层对技术要求判断技术要求 松树杉木桉树按行相乘 开N次方权重W1AW1松树1 1/26 31.44225 0.380648228 1.193564712杉木2 1 4 82 0.5278534811.65514

4、31桉树 1/6 1/41 0.041667 0.346681 0.091498291 0.2869030323.78893层次总排序计算四准则ai 经济效益 社会效益 生态效益 技术要求aibin三方案bin 0.482052 0.117016 0.2177920.18314松树0.1569960.12202 0.527836 0.3806480.07568 0.014278334 0.114958278杉木0.119183 0.332516 0.332516 0.527853 0.057452 0.038909735 0.072419177桉树0.723821 0.648329 0.139

5、648 0.091498 0.348919 0.075864968 0.030414149层次总排序一致性检验CIi0.088464 0.001847 0.026811 0.067805CI=aiCIiRI=aiRIiRIi0.51490.51490.51490.51490.0611175370.5149CRi0.171808 0.0035880.05207 0.131687AW1AW1/W1CI=（入-n）/（n-1）CR=CI/RI2.070547 4.295277 0.073322510.89310.0820990.478166 4.0863250.938617 4.3097040.76

6、7094 4.1885644.219968AW1/W1CI=（入-n）/（n-1）CR=CI/RI3.176928 0.0884640.51490.1718083533.1769283.1769283.176928AW1/W1CI=（入-n）/（n-1）CR=CI/RI3.003695 0.0018470.51490.0035876853.0036953.0036953.003695AW1/W1CI=（入-n）/（n-1）CR=CI/RI3.053622 0.0268110.51490.0520698933.0536223.0536223.053622AW1/W1CI=（入-n）/（n-1）C

7、R=CI/RI3.135611 0.0678050.51490.1316865843.1356113.1356113.135611总排序aibin0.069712 0.2654530.096671 0.2654530.016757 0.471955CR=CI/RI0.118698用电子表格（Excel）实现层次分析法（AHP）的简捷计算准则层对目标层判断树种选择 经济效益 社会效益 生态效益技术要求 按行相乘 开N次方权重WiAWi经济效益 1 3 4 2.0024.00002.21340.48212.0705社会效益 1/31 1/20.500.08330.53730.11700.4782生

8、态效益 1/42 1 2.001.00001.00000.21780.9386技术要求 1/22 1/21.000.50000.84090.18310.76714.5915max=判断矩阵A-B权重向量WimaxAB1B2B3aij开N次方权重Wi（AW）i（AW）i/WiB11 1/3 1/3 0.110.48070.14290.42863.0000B23 1 1 3.001.44220.42861.28573.0000B33 1 1 3.001.44220.42861.28573.00003.3652max=3.0000方案层对社会效益判断社会效益 松松树树杉木桉树按行相乘 开N次方权重W

9、iAWiAWi/Wi松树1 1/2 1/50.100.46420.12200.36653.0037杉木2 1 1/30.670.87360.22970.68983.0037桉树5 3 1 15.002.46620.64831.94743.00373.80403.0037方案层对生态效益判断生态效益 松树杉木桉树按行相乘 开N次方权重W1AW1AW1/W1松树1 2 3 6 1.817121 0.527836133 1.611811805 3.0536216杉木 1/21 3 1.5 1.144714 0.332515928 1.015377811 3.0536216桉树 1/3 1/31 0.

10、11110.48075 0.139647939 0.426431959 3.05362163.4425853.0536216方案层对技术要求判断技术要求 松树杉木桉树按行相乘 开N次方权重W1AW1AW1/W1松树1 1/23 1.5 1.144714 0.319618264 0.964702114 3.0182947杉木2 1 4 82 0.558424543 1.685489843 3.0182947桉树 1/3 1/41 0.08330.43679 0.1219571930.36810275 3.01829473.5815043.0182947层次总排序计算四准则ai 经济效益 社会效益

11、 生态效益 技术要求aibin三方案bin 0.48210.117 0.2178 0.1831松树0.14290.122 0.5278 0.3196 0.068865 0.014278334 0.114958278 0.0585349杉木0.4286 0.3325 0.3325 0.5584 0.206594 0.038909735 0.0724191770.10227桉树0.4286 0.6483 0.13960.122 0.206594 0.075864968 0.030414149 0.0223353层次总排序一致性检验CIi0 0.0018 0.0268 0.0091CI=aiCIiR

12、I=aiRIiCR=CI/RIRIi0.5149 0.5149 0.5149 0.51490.0077305760.5149 0.0150137CRi0 0.0036 0.0521 0.0178AWi/WiCI=（入-n）/（n-1）CR=CI/RI4.29530.07330.89310.08214.08634.30974.18864.2200一致性检验C.I.R.I.C.R.0.00000.51490.00000.0000CI=（入-n）/（n-1）CR=CI/RI0.00180.51490.0036CI=（入-n）/（n-1）CR=CI/RI0.0268110.51490.05206989

13、3CI=（入-n）/（n-1）CR=CI/RI0.0091470.51490.017765301总排序aibin0.4201930.4201930.335208判断矩阵A-B权重向量Wimax一致性检验C6D1D2aij 开N次方权重Wi（AW）i（AW）i/Wi C.I.R.I.D1 1 1/30.330.57740.2500#D2 3 1 3.001.73210.7500#2.3094max=#A AB1B1B2B2B3B3B1B11 1/3 1/3B2B211B3B31B1B1C1C1C2C2B2B2C1C111 C3C3C2C21 C4C4C1C1D1D1D2D2C2C2D1D115

14、D1D1D2D21 D2D2C4C4D1D1D2D2C5C5D1D117 D1D1D2D21 D2D2C.R.#A AB1B1B10.1429C10.5000B2B20.42860.4286C20.5000B3B30.42860.4286CR0.0000CR0C1C2D10.8333D10.7500D20.1667D20.2500CR0CR0.0000C3C3C4C4B3B3C5C5C6C613 C5C5131 C6C61C5C6D10.1667D10.2500D1D1D2D2C3C3D1D1D2D2D20.8333D20.750013 D1D11 1/5CR0CR0.00001 D2D21D

15、1D1D2D2C6C6D1D1D2D21 1/5 D1D11 1/31 D2D21B层次总排序（CR=0.0000）B1B2B30.14290.40.4286 C层次总排序（CR=0.0000）C1C2C3C40.07140.10.32140.1071 D层次总排序（CR=0.0000）D1D20.34080.7iWiWiWiWiWiW一致性检验B2B3C30.7500C50.75C40.2500C60.25CR0.0000CR0C3C4D10.1667D10.875D20.8333D20.125CR0.0000CR0C5C60.30.1071iWiWiWiWA AB1B1B2B2B3B3B1

16、B11 1/3 1/3B2B211B3B31B1B1C1C1C2C2B2B2C3C3C4C4C1C111 C3C313C2C21 C4C41C1C1D1D1D2D2C2C2D1D1D2D2D1D115 D1D113D2D21 D2D21C4C4D1D1D2D2C5C5D1D1D2D2D1D117 D1D11 1/5D2D21 D2D21计算判断矩阵权重向量WiAB1B2B3aij开N次方B11 1/3 1/30.110.4807B23 1 1 3.001.4422B33 1 1 3.001.44223.3652判断矩阵权重向量WiB1C1C2aij开N次方权重WiC11 1 1.001.00000.5000C21 1 1.001.00000.50002.0000判断矩阵权重向量WiB2C3C4aij开N次方权重WiC31 3 3.001.73210.7500C4 1/31 0.330.57740.25002.3094判断矩阵权重向量WiB3C5C6aij开N次方权重WiC51 3 3.001.73210.7500C6 1/31 0.330.57740.25002.3094判断矩阵权重向