基于Excel應用的多指標評分系統設計與實現

摘 要：比賽評分、教材評比等多用單一指標衡量，評價結果并不盡合理，若從多個指標評價，將更準確。對于多指標評分，可以使用Excel的數據有效性、函數及保護功能實現，并設定各分數段數量及比率使評分工作更客觀、公正。

關鍵詞：多指標評分；Excel；數據的有效性；條件格式；保護

中圖分類號：TP391.1 文獻標識碼：A

1 引言

在日常工作和生活中，我們常會遇到各種評價問題，如果僅從單一指標上對被評價事物進行綜合評價不盡合理，因此往往需要將反映被評價事物的多項指標的信息加以匯集，得到一個綜合指標，以此來從整體上反映被評價事物的整體情況[1]。多指標評分，要求每位評委對各個指標打分，通過設定各指標權重，得到評分總和及最小分值、最大分值等統計指標。在多指標綜合評價中，指標的權重系數是指各指標對決策問題的相對重要程度[2]。評委打分時不可避免地帶有主觀色彩，為限制打分集中性地偏高或偏低，可用設定評分總和分數段比率的辦法來限定打分，這樣會使分值更客觀、公正。該評分系統能夠廣泛地應用于比賽評分、教材評比等，可用C#、Visual Basic等程序設計語言來實現，但EXCEL也能實現該系統功能，并且相比較程序語言開發工具來說更簡單易用。這里將以“教材評比”系統為例介紹實現辦法。

2 系統概述

制訂評價指標體系，應滿足指標的完備性，即要求制訂的教材質量評價指標體系不能遺漏關于教材的任何重要方面的情況，應全面地 系統地本質地反映再現和涵蓋教材的各方面情況[3]。“教材評比”系統具體地描述為：有10位評委為某出版社教材打分，共8個指標，每個指標可選分值為0—10分，其中“職業性”和“實踐性”這兩個指標的權重系數是2，其余的權重系數為1，總分最高為100分。系統能自動以不同顏色顯示每本教材的最大、最小得分，能統計“所有教材的最小分值”、“所有教材的最大分值”、“內容質量總成績”及“組內最低分”，并要求90—100分的比率不能高于10%，70—90分的比率不能高于30%，評分完畢，系統能夠統計每本教材的平均分值及位次。下面將詳細地介紹該系統的實現過程。

3 實現過程

3.1 根據教材和評價指標建立表格

在Excel中新建一個工作簿，并按照教材（以10本教材為例）評價指標及統計條目做出一個評分工作表，其中行標題為教材名，列標題為8個指標，并在右側添加“所有教材的最小分值”、“所有教材的最大分值”、“90—100分”數量統計等條目，具體如圖1所示，將C3至J12區域的單元格設為數字格式，以輸入各位評委對教材的打分。

3.2 凍結窗格

以C3為基準凍結窗格，當向下滑動滾動條，首行不動，確保評價每本教材都能隨時看到評價指標；向右滑動滾動條，教材名稱不變，確保能將每個指標移到離教材最近的地方，方便評價。

3.3 設定數據的有效性

一般在比賽規則中會明確評分的取值范圍，以此為依據，在系統中通過設定輸入框的取值來實現這一要求，否則系統報錯。在本系統中，選中C3：J12單元格區域，點擊“數據”菜單的“有效性”子菜單彈出“數據有效性”對話框，如圖2所示，在“允許”項目中選擇“序列”，在“來源”項目中輸入取值范圍，并以逗號隔開。

3.4 條件格式的設定

當輸入全部評分時，系統將以不同顏色來區分最高分及最低分，能夠讓人一目了然。這一點的實現方法是選擇數據區域C3：J3，點擊“格式”菜單的“條件格式”子菜單，打開“條件格式”對話框，如圖3所示。首先將單元格數值設置為“等于”“MAX（$C$3：$J$3）”，格式設為“紅色”，這樣將第一本教材的最高分設為了紅色。同理，將單元格數值設置為“等于”“MIN（$C$3：$J$3）”，格式設為“綠色”，將第一本教材的最低分設為了綠色。最后，選擇數據區域C3：J3，拖動填充柄至C12：J12即可。

3.5 最小、最大及和的計算

在本案例中，需要計算“每種教材的最小分值”、“每種教材的最大分值”、“總成績”及“組內最低分”。總成績的計算公式為：“K3=C3+D3+E3*2+F3*2+G3+H3+I3+J3”，選中K3單元格，拖動填充柄至K12單元格即可計算出所有教材的總成績。其余三項的計算公式，如表1所示。

3.6 各個分數段的教材數量及比率的計算

在這里主要用到COUNTIF及COUNTA函數，COUNTIF函數能夠計算某個區域內滿足給定條件的單元格數目；COUNTA函數能夠計算某個區域內所包含的數值個數，如表2所示。

3.7 鎖定及保護功能結合使用

為避免評委修改及刪除教材名、指標名和統計條目名，可將鎖定及保護功能結合使用對工作表進行設置。新建一個工作簿后，工作表內單元格默認為“鎖定”狀態，，首先選定整張工作表，選擇“單元格格式”中的“保護”選項卡，取消“鎖定”狀態。然后再選擇工作表內想要保護的內容，設定為“鎖定”狀態，然后選中“工具”菜單的“保護”子菜單中的“保護工作表”，打開“保護工作表”對話框，設定密碼即可，如圖4所示。

3.8 成績統計

為統計平均分值及排名，首先在原工作簿建一張新工作表，取名為“評選結果”，具體表設計如圖5所示。

“最終成績”的計算方式是由每位評委打分得到的“總成績”求平均值，具體地公式為“C3==（評委1！K3+評委2！K3+評委3！K3+評委4！K3+評委5！K3+評委6！K3+評委7！K3+評委8！K3+評委9！K3+評委10！K3）/10”， “名次”的計算需要用到RANK函數，具體地公式為“D3=RANK（C3，$C$3：$C$12）”，選中“C3：D3”單元格，拖動填充柄至“C12：D12”就可以把10本教材的最終成績及名次計算出來。

4 結束語

本文通過教材評比案例，介紹了使用Excel工具創建多指標評分系統的方法，利用Excel中countif及countna函數獲得各個分數段的數量及比率，通過限制各分數段的數量及比率，為評分工作帶來了公正及公平。該系統能夠廣泛地應用于各種評比活動中。在今后的研究中，將進一步在如何減少評比中的主觀因素，保持評分的公正中做研究。

參考文獻

[1] 虞曉芬，傅玳.多指標綜合評價方法綜述[J].知識叢林，2004（11）：119-121.

[2] 王明濤.多指標綜合評價中權系數確定[J].系統工程，1999（2）：56-61.

[3] 許新華.高職教材評價及其指標體系設計[J].長沙通信職業技術學院學報，2011（4）：52-56.

作者簡介：

趙華麗（1979-），女，碩士，講師.研究領域：計算機應用技術.