使用Excel實現測評得分統計

錢力濤

摘 要：在日常工作中，經常會有各類比賽或測評活動，而準確的統計和反饋，可以提高活動效率，并體現公開和公平原則。本文以Excel為工作環境，制作了一個高效的測評統計表。

關鍵詞：測評;統計;Excel

1 問題的提出

放假前，教代會召開會議，聽取學校領導班子做工作報告。在會議中表決通過了《學校章程》和《發展規劃》，并對中層干部進行了民主評議。會后，筆者參與了民主評議的統分工作，感覺輸入分值、計算平均分、排名，操作繁瑣，特制作了一個Excel文件來簡化操作。

選擇Excel作為開發環境，有三個原因。一是Excel作為微軟Office的辦公套件之一，在計算機上的普及率相當高，而且不同版本間的兼容性也很好。二是Excel呈現數據的方式簡單直觀，有較為豐富的函數，適合對數據的操作。三是在表格開發的后期，可以結合簡單的VBA技術（Visual Basic for Applications，一種宏語言），實現復雜的功能。[1]

2 步驟和制作

基本思路是，針對每一名被測評的中層，輸入每個代表的打分后，去掉三個最高分和三個最低分，對剩余分數取平均值作為這名中層的得分。打分數據輸入完畢后，也就是全體中層的每人平均分算出后，對得分進行總的排名。

根據上面的思路，得出Excel的制作流程：制作表格—設定區域的數據有效性——首行/首列固定——去掉最高分和最低分后計算平均分——最后得分的排名——表格美化。

2.1 制作表格

制作標題行，第一行合并單元格，輸入“教代會‘中層干部民主評議評分表”。第二行輸入序號、中層姓名、最后得分、排名和代表序號（如圖所示）。

2.2 數據有效性

對表格中的E3至BB52區域進行數據有效性設置，只能輸入0-100范圍內的整數，否則數據無效。另外，對此區域的數據設置條件格式，用特殊顏色標明0分和100分。

2.3 首行/首列固定

由于需要固定表格的前兩行和前三列，所以，鼠標選擇想要固定的行與列相交位置的單元格，也就是D3單元格，再接著點擊“凍結拆分窗格”，就可以實現同時固定前兩行和前三列。（固定的行和列是不包括被選擇的單元格，只是被選擇單元格的上側和左側）

2.4 去掉最高最低分后計算平均分

使用TRIMMEAN函數，TRIMMEAN 函數是先從數據集的頭部和尾部除去一定百分比的數據點，然后再求平均值。其語法格式是TRIMMEAN（array，percent）。所以，在表格中的C3單元格，輸入以下內容“=TRIMMEAN（E3：BB3，6 / COUNTA（E3：BB3））”（再按上述方法向下自動填充）。其含義是，從E3到BB3，一共50個數據點的集合中（全體代表對某一名中層的打分），去除6個數據點（去掉3個最高分，去掉3個最低分），剔除后，對剩余數據取平均值。

為了數字的簡潔和觀看的方便，對“最后得分”列（C列）的單元格格式設置為2位小數的數值類型。

2.5 排名

使用RANK函數實現排名，其語法形式是RANK（number，ref，[order]）。所以，在表格中的D3單元格，輸入以下內容“=RANK（C3，C$3：C$36，）”（再按上述方法向下自動填充）。其含義是，C3單元格的數據在從C3到C36的范圍中的排名（一共34名中層）。

2.6 表格美化

如果希望表格漂亮，字體是第一步。所以，將表格中的字體換成微軟雅黑。其次，取消網格線，并對標題行部分加上一點顏色。最后，隔行填充顏色，這樣可以使輸入數據或查看表格的人更清晰的觀看某行情況。

3 擴展和完善

每年年終時，學校都會舉辦“教師教學能力大賽”，為了使比賽成績更加透明，評委需要當眾打分，記分員也要迅速的計算出平均分和排名。同樣可以使用上述步驟和方法，制作Excel文件，提高工作效率。

此外，在排名環節中，RANK函數根據最終得分，按從大到小的順序排名。如果發生并列名次，則下一名次將按并列數順延。比如：有兩個人并列第3名，則沒有第4名，直接是第5名。

很顯然，這種排名方式不符合我們的日常習慣。在我們的日常生活中，無論有多少人并列第3名，之后的排名仍應該是第4名。所以，在上述步驟中，按習慣式排名則需要將D3單元格的公式改為“=SUMPRODUCT（（C$3：C$36>C3）*（1/（COUNTIF（C$3：C$36，C$3：C$36））））+1”。

該公式的后半部分用來計算重復排名的個數，不重復為1，重復一次為二分之一，重復兩次是三分之一，對應相乘累加后，就得到本次比賽中比該選手成績高的人數，再加一次就是該選手的排名。[2]

4 結語

通過對Excel的使用，制作了可用于測評打分、總分排名的統計工作表，借助信息化手段，實現公正、高效，提高了工作效率。

參考文獻：

[1]呂子燕.基于Excel的比賽計時評分程序的實現[J].中小學電教，2015（Z2）：135-137.

[2]柳淇.利用Excel制作競賽評分自動計算系統[J].中小學電教，2015（Z1）：142-143.