使用Excel實(shí)現(xiàn)測評得分統(tǒng)計(jì)

錢力濤

摘 要：在日常工作中，經(jīng)常會(huì)有各類比賽或測評活動(dòng)，而準(zhǔn)確的統(tǒng)計(jì)和反饋，可以提高活動(dòng)效率，并體現(xiàn)公開和公平原則。本文以Excel為工作環(huán)境，制作了一個(gè)高效的測評統(tǒng)計(jì)表。

關(guān)鍵詞：測評;統(tǒng)計(jì);Excel

1 問題的提出

放假前，教代會(huì)召開會(huì)議，聽取學(xué)校領(lǐng)導(dǎo)班子做工作報(bào)告。在會(huì)議中表決通過了《學(xué)校章程》和《發(fā)展規(guī)劃》，并對中層干部進(jìn)行了民主評議。會(huì)后，筆者參與了民主評議的統(tǒng)分工作，感覺輸入分值、計(jì)算平均分、排名，操作繁瑣，特制作了一個(gè)Excel文件來簡化操作。

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

2 步驟和制作

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

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

2.1 制作表格

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

2.2 數(shù)據(jù)有效性

對表格中的E3至BB52區(qū)域進(jìn)行數(shù)據(jù)有效性設(shè)置，只能輸入0-100范圍內(nèi)的整數(shù)，否則數(shù)據(jù)無效。另外，對此區(qū)域的數(shù)據(jù)設(shè)置條件格式，用特殊顏色標(biāo)明0分和100分。

2.3 首行/首列固定

由于需要固定表格的前兩行和前三列，所以，鼠標(biāo)選擇想要固定的行與列相交位置的單元格，也就是D3單元格，再接著點(diǎn)擊“凍結(jié)拆分窗格”，就可以實(shí)現(xiàn)同時(shí)固定前兩行和前三列。（固定的行和列是不包括被選擇的單元格，只是被選擇單元格的上側(cè)和左側(cè)）

2.4 去掉最高最低分后計(jì)算平均分

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

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

2.5 排名

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

2.6 表格美化

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

3 擴(kuò)展和完善

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

此外，在排名環(huán)節(jié)中，RANK函數(shù)根據(jù)最終得分，按從大到小的順序排名。如果發(fā)生并列名次，則下一名次將按并列數(shù)順延。比如：有兩個(gè)人并列第3名，則沒有第4名，直接是第5名。

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

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

4 結(jié)語

通過對Excel的使用，制作了可用于測評打分、總分排名的統(tǒng)計(jì)工作表，借助信息化手段，實(shí)現(xiàn)公正、高效，提高了工作效率。

參考文獻(xiàn)：

[1]呂子燕.基于Excel的比賽計(jì)時(shí)評分程序的實(shí)現(xiàn)[J].中小學(xué)電教，2015（Z2）：135-137.

[2]柳淇.利用Excel制作競賽評分自動(dòng)計(jì)算系統(tǒng)[J].中小學(xué)電教，2015（Z1）：142-143.