淺析Excel在單選題閱卷中的應(yīng)用

摘要：本文結(jié)合考試單選題的閱卷，提出了使用Excel的自動(dòng)判分。闡述了數(shù)據(jù)的初始化和自動(dòng)判分相關(guān)的公式和函數(shù)，介紹了成績(jī)分段統(tǒng)計(jì)和差異程度分析的方法。

關(guān)鍵詞：Excel；自動(dòng)判分；單選題

1 初始化

在平時(shí)的課堂小測(cè)或者期末考試的閱卷環(huán)節(jié)中，如果班里人數(shù)較多或者選擇題的題量較大，人工判分將會(huì)變得非常繁瑣。本文介紹如何使用Excel 2010對(duì)單選題進(jìn)行自動(dòng)判分（Excel 2007和早期的版本也基本適用）。

假設(shè)有兩個(gè)班，每個(gè)班5名學(xué)生。一共有5道單選題，每題2分，共10分。在Excel中，輸入學(xué)生答案，全部輸入完畢后，間隔一行，在對(duì)應(yīng)答案的同一列輸入標(biāo)準(zhǔn)答案。形成如圖1所示的表格。

選中第一行，點(diǎn)擊“視圖”選項(xiàng)卡里的“凍結(jié)窗格”，選擇“凍結(jié)首行”。這樣使得無論有多少學(xué)生，不管表格向下有多長(zhǎng)，都可以顯示第一行的列標(biāo)題（也就是班級(jí)學(xué)號(hào)那一行）。

另外，在輸入學(xué)生選擇題答案時(shí)，可以是小寫字母abcd，也可以是大寫字母ABCD，甚至可以是數(shù)字1234，只要和標(biāo)準(zhǔn)答案的寫法統(tǒng)一即可。

2 單選題自動(dòng)判分

這個(gè)環(huán)節(jié)需要分為兩個(gè)階段完成，一是判斷每個(gè)學(xué)生的每一道題的對(duì)錯(cuò)，二是統(tǒng)計(jì)每個(gè)學(xué)生的得分。

（1）使用IF函數(shù)，判斷每個(gè)學(xué)生每道題的對(duì)錯(cuò)。在“J2”單元格判定一班1號(hào)學(xué)生第1題是否正確，如果E2=E13（“學(xué)生答案”=“標(biāo)準(zhǔn)答案”），那么在“J2”單元格顯示“對(duì)”，否則就什么都不顯示。判斷公式為“=IF（E2=E$13，“對(duì)”，“”）”。在數(shù)字13的前面加了“$”符號(hào)，意為對(duì)第13行絕對(duì)引用。也就是說，不管是E2還是E3、E4、E5都去和E13比較。否則后面對(duì)其他單元格進(jìn)行拖動(dòng)填充時(shí)，其他單元格的公式會(huì)發(fā)生錯(cuò)誤。

先對(duì)這一行進(jìn)行自動(dòng)填充（將鼠標(biāo)指針放置到單元格右下角的填充控制柄上，等鼠標(biāo)指針變成十字形，此時(shí)向右拖動(dòng)鼠標(biāo)），再對(duì)這5列向下填充?？梢钥吹揭呀?jīng)將做對(duì)的題標(biāo)注出來了。

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

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

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

3 成績(jī)分段統(tǒng)計(jì)

對(duì)成績(jī)進(jìn)行統(tǒng)計(jì)，計(jì)算各分?jǐn)?shù)段的人數(shù)，這是自動(dòng)判分后的下一個(gè)環(huán)節(jié)。在合理的劃分了分?jǐn)?shù)段后，可以使用FREQUENCY函數(shù)。其語法是：FREQUENCY （data_array，bins_array），兩個(gè)參數(shù)。第一個(gè)是一組數(shù)值，用來計(jì)算頻率；第二個(gè)是分段的間隔點(diǎn)，用來劃分分?jǐn)?shù)段。

如圖3中左圖所示，A列是學(xué)生的單選題成績(jī)，C2：C5是劃分好的各個(gè)分段間隔點(diǎn)，選擇D2：D5單元格，輸入“= FREQUENCY （A2：A11，C2：C5）”，按下“Ctrl+Shift+Enter”組合鍵（數(shù)組公式），這時(shí)在公式編輯欄上會(huì)自動(dòng)加上“{}”，和普通的公式加以區(qū)分。

使用FREQUENCY數(shù)組函數(shù)統(tǒng)計(jì)各個(gè)分?jǐn)?shù)段的人數(shù)時(shí)，只需先設(shè)置好分段間隔點(diǎn)，然后輸入數(shù)組函數(shù)即可完成人數(shù)統(tǒng)計(jì)[1]。

4 差異程度統(tǒng)計(jì)

成績(jī)分段統(tǒng)計(jì)或者班級(jí)平均分不能體現(xiàn)在同一個(gè)班級(jí)的學(xué)生掌握知識(shí)的差異程度。要檢查學(xué)生的差異水平，則需要使用方差，也就是得分的離散程度。其語法是：VAR.P（number1， [number2]， …） [2]。

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

5 結(jié)語

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

參考文獻(xiàn)：

[1]馮春楠，陳基容.Excel中的函數(shù)在數(shù)據(jù)統(tǒng)計(jì)中的應(yīng)用[J].電腦編程技巧與維護(hù)，2015（10）：8283.

[2]羅興賢，謝應(yīng)濤，高江錦.Excel統(tǒng)計(jì)功能在評(píng)卷分析中的應(yīng)用[J].福建電腦，2016（1）：9091.

作者簡(jiǎn)介：高國(guó)麗（1977），女，河北商貿(mào)學(xué)校講師。