淺析Excel在單選題閱卷中的應用

摘要：本文結合考試單選題的閱卷，提出了使用Excel的自動判分。闡述了數據的初始化和自動判分相關的公式和函數，介紹了成績分段統計和差異程度分析的方法。

關鍵詞：Excel；自動判分；單選題

1 初始化

在平時的課堂小測或者期末考試的閱卷環節中，如果班里人數較多或者選擇題的題量較大，人工判分將會變得非常繁瑣。本文介紹如何使用Excel 2010對單選題進行自動判分（Excel 2007和早期的版本也基本適用）。

假設有兩個班，每個班5名學生。一共有5道單選題，每題2分，共10分。在Excel中，輸入學生答案，全部輸入完畢后，間隔一行，在對應答案的同一列輸入標準答案。形成如圖1所示的表格。

選中第一行，點擊“視圖”選項卡里的“凍結窗格”，選擇“凍結首行”。這樣使得無論有多少學生，不管表格向下有多長，都可以顯示第一行的列標題（也就是班級學號那一行）。

另外，在輸入學生選擇題答案時，可以是小寫字母abcd，也可以是大寫字母ABCD，甚至可以是數字1234，只要和標準答案的寫法統一即可。

2 單選題自動判分

這個環節需要分為兩個階段完成，一是判斷每個學生的每一道題的對錯，二是統計每個學生的得分。

（1）使用IF函數，判斷每個學生每道題的對錯。在“J2”單元格判定一班1號學生第1題是否正確，如果E2=E13（“學生答案”=“標準答案”），那么在“J2”單元格顯示“對”，否則就什么都不顯示。判斷公式為“=IF（E2=E$13，“對”，“”）”。在數字13的前面加了“$”符號，意為對第13行絕對引用。也就是說，不管是E2還是E3、E4、E5都去和E13比較。否則后面對其他單元格進行拖動填充時，其他單元格的公式會發生錯誤。

先對這一行進行自動填充（將鼠標指針放置到單元格右下角的填充控制柄上，等鼠標指針變成十字形，此時向右拖動鼠標），再對這5列向下填充。可以看到已經將做對的題標注出來了。

需要注意的是，判斷公式“=IF（E2=E$13，“對”，“”）”需要在“J2”單元格（一班1號學生的最后一個答案的右側單元格）中輸入，公式中的“對”也可以改為“T”、“1”或其他什么標志，只要和下一個階段中的標志相同即可。另外，輸入公式必須是英文輸入法狀態。

（2）使用COUNTIF函數，統計每人的“正確數”和“得分”。在D2單元格（“正確數”單元格）中使用COUNTIF函數。公式為“=COUNTIF （J2：N2，“對”）”，也就是從J2到N2中有多少個單元格是“對”。用上一階段中的自動填充對全部單元格填充。然后，在C2單元格（“得分”單元格）輸入“=D2*2”，然后對下面的單元格自動填充。這樣全部學生的單選題得分就計算出來了。如圖2所示。

需要注意的是，這種方法僅僅適用于每道題的分值均相同的單選題。如果有的題目分值不同，“得分”和“正確數”之間就不是一個簡單的線性關系，也就不能使用上述公式了。

3 成績分段統計

對成績進行統計，計算各分數段的人數，這是自動判分后的下一個環節。在合理的劃分了分數段后，可以使用FREQUENCY函數。其語法是：FREQUENCY （data_array，bins_array），兩個參數。第一個是一組數值，用來計算頻率；第二個是分段的間隔點，用來劃分分數段。

如圖3中左圖所示，A列是學生的單選題成績，C2：C5是劃分好的各個分段間隔點，選擇D2：D5單元格，輸入“= FREQUENCY （A2：A11，C2：C5）”，按下“Ctrl+Shift+Enter”組合鍵（數組公式），這時在公式編輯欄上會自動加上“{}”，和普通的公式加以區分。

使用FREQUENCY數組函數統計各個分數段的人數時，只需先設置好分段間隔點，然后輸入數組函數即可完成人數統計[1]。

4 差異程度統計

成績分段統計或者班級平均分不能體現在同一個班級的學生掌握知識的差異程度。要檢查學生的差異水平，則需要使用方差，也就是得分的離散程度。其語法是：VAR.P（number1， [number2]， …） [2]。

如圖3中右圖所示，在B8單元格輸入“=VAR.P（B2：B6）”，在B16單元格輸入“=VAR.P（B10：B14）”。分別計算出兩個班的方差，比較可知，二班學生得分差異小，即得分比較集中；一班學生得分差異大，即學生得分較為分散。

5 結語

學生的成績登統是每次考試后必做的一項工作。參加考試的班級、科目、題型不同，考試成績的數據量也十分龐大。通過使用Excel的相關公式和函數，可以方便的完成閱卷和成績的統計分析。此外，經過適當修改，此方法還可以用于投票系統的統計計算，減輕相關的工作量。

參考文獻：

[1]馮春楠，陳基容.Excel中的函數在數據統計中的應用[J].電腦編程技巧與維護，2015（10）：8283.

[2]羅興賢，謝應濤，高江錦.Excel統計功能在評卷分析中的應用[J].福建電腦，2016（1）：9091.

作者簡介：高國麗（1977），女，河北商貿學校講師。