應用Excel實現TOPSIS法的計算

任禮 李健

【摘 要】目的：探討應用Excel完成TOPSIS法計算的途徑。方法：應用Excel的計算函數，編寫TOPSIS法的計算程序，結合實例評價計算結果。結果：Excel可以完成TOPSIS法中Di+、Di-和Ci及排序的計算。結論：Excel程序可以簡便、快速、正確地完成TOPSIS法的計算。

【關鍵詞】TOPSIS法；Excel；計算

【文章編號】1004-7484（2014）04-2053-01

TOPSIS法是technique for order preference by similarity to ideal solution的縮寫，是系統工程中有限方案多目標決策分析中用到的一種決策方法[1]。本文結合實例，應用OFFICE軟件中的Excel電子表格進行TOPSIS法計算。

1 原理與方法[2-4]

TOPSIS法是對同趨勢化后的原始數據進行歸一化處理，并建立數據矩陣，找出最優項量Z+和最劣項量Z-構成一個空間；將待評價的某個對象當作該空間上的一個點，據此獲得該點與最優項量Z+和最劣項量Z-間的距離，得出待評價對象與最優項量和最劣項量的相對接近程度 ，借此進行方案優劣的評價。

2 Excel計算程序 [5]

2.1 確定相關信息輸入區域

在單元格G4輸入[指標性質]，H4～AU4指標為高優指標輸入[高]、低優指標輸入[低]；G5輸入[權重]，H5～AU5采用加權TOPSIS法時輸入各指標權重，否則為1；G7～G106為對象名稱，H6～AU6為指標名稱；H7～AU106為數據輸入區域。

2.2 各評價指標值的同趨勢化計算

將低優指標轉變為高優指標：AW7=IF（H7=""，""，IF（H7=0，0，IF（H$4="高"，H7，IF（H$4="低"，1/H7，""）））），移動鼠標到AW7單元格右下角，當鼠標指針變為黑色+時按下左鍵下拉橫拉（向右，下同）至CJ106。

2.3 各指標值的歸一化及最優方案、最劣方案、Di+、Di-計算

在單元格CK4輸入最優方案[Z+]：CL4=IF（SUM（H7：H106）<>0，MAX（CL7： CL106），""），橫拉至DY4； CK5輸入最劣方案[Z-]：CL5=IF（SUM（H7：H106）<>0， MIN（CL7：CL106），""），橫拉至DY5。

歸一化：CL7=IF（H7<>""，AW7/SQRT（SUMSQ（AW$7：AW$106）），""），下拉橫拉至DY106。

Di+：EA7=SQRT（SUM（EB7：FO7）），下拉至EA106；Di-：FQ7=SQRT（SUM（FR7：HE7）），下拉至FQ106。

（Z+-Zij）2：EB7=IF（H7<>""，H$5*（CL$4-CL7）^2，""），下拉橫拉至FO106；

（Z--Zij）2：FR7=IF（H7<>""，H$5*（CL$5-CL7）^2，""），下拉橫拉至HE106。

2.5 的取值和 計算及排序

在單元格B6輸入[對象名稱]、C6輸入[Di+]、D6輸入[Di-]、E6輸入[Ci]、F6輸入[排序]。對象名稱：B7=G7；Di+取值：C7=ROUND（EA7，4）；Di-取值：D7=ROUND（FQ7，4）；計算Ci：E7=IF（H7<>""，ROUND（FQ7/（EA7+FQ7），4），""）；排序：F7=IF（E7<>""，RANK （E7，E$7：E$106，0），""），選中B7～F7，下拉至F106。

3 應用實例

評價資料來源于新都區2013年生活飲用水監測結果。2013年共監測生活飲用水1339份，檢測項目36項，。采用以上程序對新都區8類生活飲用水中合格率低于100%的14個項目進行衛生質量評價，評價指標共14個，均為高優指標。在Excel程序的指標性質（H4～U4）輸入[高]；權重（H5～U5）輸入1（權重相同）；指標名稱（H6～U6）輸入指標名稱；樣品類別（G7～G14）輸入樣品類別名；H7～U14輸入各指標值。Ci及排序結果見表1。

從表1可以看出：Ci值D>E>G>F>B>H>C>A，即市政水廠出廠水、末梢水、鄉鎮水廠末梢水衛生質量居前三位，單位自建供水和分散供水衛生質量最差，TOPSIS法評價結果與實際情況相符合。

*：單位類別：A、B、C、D、E、F、G、H分別代表單位自建供水、二次供水、分散供水、市政水廠出廠水、市政水廠末梢水、鄉鎮水廠出廠水、鄉鎮水廠末梢水、學校自建供水

4 討論

TOPSIS法使用靈活簡便，已廣泛應用于效益評價、衛生決策和衛生事業管理等多個領域。 該方法對數據分布類型、樣本均沒有特殊要求，既適用于小樣本資料又適用于多單元多指標的大樣本資料[6-7]。該方法的主要優點在是能充分利用指標參數的信息，靈敏地反映指標間的差異并對評價對象進行排序，而且結果量化準確直觀。它的局限性是當某個指標離散程度較大時，受異常值的影響，將會導致結果不穩定。由于TOPSIS法目前尚無商業軟件，本文建立的Excel應用程序用于TOPSIS法計算時，各種類型的指標直接輸入即可，省去了對指標值進行預處理的步驟，較為簡便，適宜在基層醫療衛生人員推廣。程序設置了指標40個、方案100個，使用中還可以根據具體情況做進一步擴充，能滿足各種不同分析的需要，具有一定的實用價值。

使用本程序應注意：①選用加權TOPSIS法時，在權重行輸入各指標權重，選用TOPSIS法時，必須在權重行輸入1。②為保護程序不被無意破壞，可選中H4～AU6和G7～AU106單元格單擊鼠標右鍵指向設置單元格式選擇保護取消鎖定上的勾；③選重AV～HE單元格單擊鼠標右鍵選擇隱藏；④單擊菜單欄上的工具指向保護單擊保護工作表只勾選保護工作及鎖定單元格內容和未鎖定的單元格確定，這樣程序就不容易被無意間破壞。

參考文獻

[1] 方積乾主編.衛生統計學[M].北京：人民衛生出版社，2011：428-429.

[2] 朱亞芹，宋金林，楊益國.TOPSIS法在公共場所衛生監督工作質量評價中的應用[J].中國衛生統計，2002，19（2）：37.

[3] 李鵬社，等.TOPSIS法在評價醫院工作效率中的應用[J].人民軍醫，2011，9（54）：831-832.

[4] 龔玲.TOPSIS法與綜合指數法在醫療質量綜合評價中的結合應用[J].中國醫院管理，2006，28（6）：26-28.

[5] 高永，等.加權TOPSIS法綜合評價在Excel中的實現[J].中國衛生統計，2007，8（24）：428-429.

[6] 李寧秀，柯雄.四川農村鄉鎮衛生院服務質量評價指標與TOPSIS分析[J].中國衛生事業管理，2011（10）：776-778.

[7] 馬亞楠，劉海波，金晶，等.TOPSIS法在沈陽市郊區社區衛生服務中心綜合評價中的應用[J].中國全科醫學，2011，14（7A）：2147-2149.