1、使用Excel對多項選擇題自動評分并統計正確率【內容摘要】   對選擇題的答題卷進行批改和分析是教學中經常要做的重要工作。利用Microsoft Excel電子表格的強大功能，用FREQUENCY函數和數組公式、COUNT函數對選各題各選項的人數及百分比進行統計；用IF函數和SUM函數自動計算單項選擇題的總得分；用VLOOKUP函數和SUM函數自動計算不定項選擇題的總得分。【關鍵詞】Excel 選擇題 自動結分 統計分析   考試是教學中不可缺少的環節，是檢查教學質量的重要手段。考試可以促進、激勵學生的進步發展，讓教師檢查、診斷學生的

2、學習是否達到教學大綱規定的要求，同時給教師提供反饋信息，以便及時調整教學進度，改進教學方法，提高教學質量。而要達到以上目的，試卷分析是很重要的環節，只有對考試結果進行全面細致的分析，才能找出教和學雙方存在的問題，找出有針對性的解決方法，提高教學效果。而全面細致的分析必須要有詳細的數據，如及格率、優秀率、平均分、標準差、各分數段人數、各題難度等，特別是對每題的難度分析，對搞清每個知識點的落實掌握情況是很重要的。而選擇題答卷的統計分析，由于一般的平時測驗都沒有答題卡和讀卡機，老師要對選擇題進行手工逐題的批改記分，如要對各題的答題情況進行比較詳細的分析，許多老師用打五劃“正”的方法，來統計各題的做對

3、和做錯人數。本人也經常做這樣的事，感到效率很低，且容易出錯搞不準確。想到用Excel對考試結果數據進行及格率、優秀率、平均分、標準差、各分數段人數分析很容易，能不能也用Excel對選擇題進行比較詳細的分析，通過反復摸索，找到了以下方法。   下面以60人的班級，20題選擇題為例，多于60人或少于60人可用插入行或刪除行來調整，多于20題或少于20題用插入列或刪除列來調整，都不影響表格的性質。對單選題的分析1先新建一個空白的Excel工作簿，工作表Sheet1重命名為“1班”，輸入表格頭、題號、學號、姓名，C3:V62為數據輸入區(如圖1)。2在W3、W4、W5三個

4、單元格分別輸入數值1.5、2.5、3.5。在姓名的下方的適當位置，如隔二行B65:B68分別輸入“選A項人數、選D項人數”。3選中C65:C68四個單元格，單擊F2(編輯選定的單元格)，在公式編輯欄或直接在單元格輸入“=FREQUENCY(C3:C62,$W$3:$W$5)”(注意：輸入公式時字母、符號要用英文半角)，按Ctrl+Shift+Enter組合鍵輸入(如圖2)。選中C65:C68光標移到右下角變為實心的十字時，向右拖拉至V65:V68。4填入學生的選擇題答案，選A的填1，選B的填2，選C的填3，選D的填4。就會在65至68行中顯示各題選擇各選項的人數。至此主要任務完成。5為了使數據

5、看起來更方便，可在B69輸入“總人數”，在C69中輸入公式“=COUNT(C3:C62)”，光標移到右下角變為實心的十字時，向右拖拉至V69，則69行顯示答題總人數。6在B70:B73中分別輸入“選A百分比、選D百分比”，在C70中輸入“=C65/COUNT(C$3:C$62)”，光標移到右下角變為實心的十字時，向下拖拉至C73，向右拖拉至V73，就會在70至73行中顯示各題選擇各選項的人數的百分比。注意設置C70:V73單元格的格式，點“格式”中“單元格格式”，在“數字”中選“百分比”，“小數位數”設為1位，以免過多的小數位數顯示不出。7為方便使用，選中第3行，點“窗口”中的“凍結窗口”，凍

6、結表格頭，在輸入數據時讓題號不滾動；選中W列，點“格式”“列”“隱藏”可隱藏W列，防止誤操作這列數據；也可選中C3:V62數據輸入區，點“數據”、“有效性”、“設置”，在“有效性條件”中“允許”“整數”，“數據”“介于”最小值1，最大值4，防止輸入時誤輸入其它無效數字。說明：Excel中函數是對單元格中的數值進行處理，而選擇題的選項A、B、C、D這些文本形式很難處理，所以用數值“1、2、3、4”分別代替“A、B、C、D”。   這里用到函數“FREQUENCY”和數組公式。函數“FREQUENCY（data_array，bins_array）”是以一列垂直數組返回

7、某個區域中數據的頻率分布，Data_array為一數組或對一組數值的引用（即C3:C62），用來計算頻率；Bins_array 為間隔的數組或對間隔的引用（即$W$3:$W$5，$符號是指絕對引用，不隨拖拉而改變引用的單元格），該間隔用于對 data_array 中的數值進行分組，在這里1.5、2.5、3.5三個數形成四個間隔：小于等于1.5；大于1.5小于等于2.5；大于2.5小于等于3.5；大于3.5。而我們用1、2、3、4這四個數去對應這四個區間，來代替A、B、C、D這四個選項，所以只要小于等于1.5的數都可以代替A，大于1.5小于等于2.5的數都可以代替B，以此類推，只是由于方便只用1

8、、2、3、4來代替A、B、C、D。也就是函數 FREQUENCY返回的數組中的元素個數比 bins_array（W3: W5）中的元素個數多一。由于函數 FREQUENCY 返回一個數組，所以必須以數組公式的形式輸入，選中從公式單元格開始的單元格區域即C65:C68(四格，比W3: W5多一格)，按 F2編輯選中的單元格，在公式編輯欄或直接在單元格輸入公式，再按Ctrl+Shift+Enter以數組公式的形式輸入。則在C65:C68中的數據分別是：小于等于1.5數值的個數(即1的個數，也就是選A的個數)、大于1.5小于等于2.5數值的個數(即2的個數，也就是選B的個數)、大于2.5小于等于3.

9、5數值的個數(即3的個數，也就是選C的個數)、大于3.5數值的個數(即4的個數，也就是選D的個數)。如果公式未以數組公式的形式輸入，則返回的結果為1。和一般公式的區別是數組公式括于大括號 ( ) 中。按 Ctrl+Shift+Enter 可以輸入數組公式。對不定項選擇題的分析   不定項選擇題的選項有15種可能情況，即A、B、C、D、AB、AC、AD、BC、BD、CD、ABC、ABD、ACD、BCD、ABCD；分別用數值1、2、3、4、12、13、14、23、24、34、123、124、134、234、1234來代替，也就是字母A、B、C、D分別用數字1、2、3、

10、4來代替。1和前面相同，建立一個空白的Excel文件，輸入表格頭、題號、學號、姓名，C3:V62為數據輸入區。也可以在前面單選題表格的基礎上接著進行操作。2在W3、W4、W16十四個單元格分別輸入數值1.5、2.5、3.5、4.5、12.5、13.5、14.5、23.5、24.5、34.5、123.5、124.5、134.5、234.5；這14個數形成的15個區間分別對應1、2、3、4、12、13、14、23、24、34、123、124、134、234、1234，也就是A、B、ABCD這十五種可能選項。在姓名的下方的適當位置，如隔二行B65:B79十五行分別輸入“選A項人數、選B項人數、選C項

11、人數、選D項人數、選AB項人數、選AC項人數、選AD項人數、選BC項人數、選BD項人數、選CD項人數、選ABC項人數、選ABD項人數、選ACD項人數、選BCD項人數、選ABCD項人數”。3選中C65:C79十五個單元格，單擊F2(編輯選定的單元格)，在公式編輯欄或直接在單元格輸入“=FREQUENCY(C3:C62,$W$3:$W$16)”，按Ctrl+Shift+Enter組合鍵輸入數組公式(如圖3)。選中C65:C79，光標移到右下角變為實心的十字時，向右拖拉至V65:V79。4在B80輸入“總人數”，在C80中輸入公式“=COUNT(C3:C62)”，光標移到右下角變為實心的十字時，向右

12、拖拉至V80，則第80行顯示答題人數；5在B81:B95中分別輸入“選A百分比、選ABCD百分比”，在C81中輸入“=C65/COUNT(C$3:C$62)”，光標移到右下角變為實心的十字時，向下拖拉至C95，向右拖拉至V95，就會在81至95行中顯示各題選擇各選項的人數的百分比。6隱藏W列；凍結表格頭；有效性條件允許“整數”，數據介于最小值1，最大值1234。在C3:V62中輸入和A、B、C、D對應的1、2、3、4值就統計出各題的選項情況。但要注意的是，如：選項AB和BA等效，但在輸入數字時只能是12，不能輸入21，而ABC只能是123，不能是132或231，也就是數字只能從小到大排列。&#

13、160;  只要把每個同學選擇題答卷中的答案字母用相應的數字填入，就可以方便地統計出每題的答題情況。自動計算單項選擇題的總得分   哎！統計倒是不錯，可這些選擇題是批改了再來統計，還是統計了再來一遍批改，這不是干了二遍嗎？豈不是和當初提高效率的初衷相違背了嗎！所以如果在得出統計結果的同時，每個同學的選擇題分數也出來了多好。看下面操作：如果是單選題，在前面單選題表格中的題號下面插入一行成為第3 行，存放每小題的相應的答案數字；在姓名后面插入一列成為C列，存放每個同學選擇題的總得分。在第一個同學的存放成績的單元格C4里輸入：“=SUM(IF(D4=

14、$D$3,"1"),IF(E4=$E$3,"1"),IF(F4=$F$3,"1"),IF(G4=$G$3,"1"),IF(H4=$H$3,"1"),，)*$C$3”，然后向下拖拉到最后一個同學止(如圖4)。意思是比較D4單元格如果和D3單元格值相同，賦值為1；比較E4單元格如果和E3單元格值相同，賦值為1；直至最后一題；再把所有“1”求和(即正確的題數)；再乘以每題的分值“$C$3”。   每次不同的選擇題要改動的只是答案行的正確答案的數字，和在C3單元格填入每小題

15、的題分值，每個學生選擇題的總得分在各人姓名后就顯示出來了，還是很方便的。   注意的是，如果選擇題只有10題，剩下其它幾題的答案要填入一個數如5，不能為空格，因為空格和空格比較表格也認為是相同的，返回賦值1，這樣最后結果就不對了。這里用到求和函數SUM，和判斷函數IF，IF的語法是這樣的：IF(logical_test,value_if_true,value_if_false)，Logical_test 是判斷條件，如滿足返回Value_if_true，不滿足返回Value_if_false，Logical_test和Value_if_false可以是邏輯值，零，

16、也可以是其它公式。   而計算不定項選擇題的得分，也可用這個公式方法，只不過麻煩一點。比如第一題正確答案是ABC，答A或B或C，或答AB，或答AC，或答BC，均可得部分分數，但一般如果有錯誤的答案選上就不得分了。首先答案行一行肯定不夠了，但如果不引用單元格而直接比較答案，答案行也可不用增加。函數 IF最多可嵌套七層，也就是可比較七個條件，如第一題判斷可用：“IF(D4=123,"3",IF(D4=12,"2",IF(D4=13,"2",IF(D4=23,"2",IF(D4=1,&quo

17、t;1",IF(D4=2,"1",IF(D4=3,"1"，0)”，即如選ABC得3分，選AB或AC或BC得2分，選A或B或C得1分，否則為0分。而這只是一題，還要用SUM函數將所有各題判斷加起來，如這樣：“=SUM(第1題判斷，第2題判斷，)”，式子很長。且每次不同的選擇題都要修改公式，很麻煩易出錯，必須另想辦法。自動計算不定項選擇題的總得分   如果把不定項選擇可能的答案的答題項很多行都列出來，數據很多，所以在上面工作表基礎上要另建一個工作表命名為“da”。在“da”工作表中建立如圖5所示表格，A列是所有可能的選

18、項，共15種可能，只不過答案是ABCD的情況一般不會出現，這一項等會另作他用。每一列的題號后面都插入一列，作為相應選項的得分值，如只有一個選項“C”，則數字“3”后面填“1”，其它各項都填“0”；如正確選項是“ABC”，則“ABC”相應數字后面“123”填“1”，而選AB、AC、BC都可得部分分數，如得小題滿分的2/3分數，則在相應空格中都填上0.667(或2/3，單元格格式中數字項要設置一下) ，其它各項都填“0”；選A或B或C也可得分，如得小題滿分的1/3分數，則在“1”、“2”、“3”后面都填上0.333(或1/3)，其它各項都填“0”。為方便對照，在第3列列出正確的答案；在左上角空格中

19、選一格如C1，放置每小題的分值，每次改變題分只要修改這一格就可以了。   回到工作表“1班”，在“C4”單元格中輸入如下公式(如圖6)：“=SUM(VLOOKUP(D4,da!$B$4:$C$18,2,FALSE),VLOOKUP(E4,da!$D$4:$E$18,2,FALSE),VLOOKUP(F4,da!$F$4:$G$18,2,FALSE), ,VLOOKUP(W4,da!$AN$4:$AO$18,2,FALSE)*da!$C$1”   如選擇題有20題，則從“DW”有20項，要有細心和耐心嗬！如果你確定以后肯定沒有20個選擇

20、題，也可以少輸入幾個，如果不能保證的話，那只能為以后少點麻煩現在辛苦點了，因為用插入列增加題數在這里要改變公式。求和SUM函數里的VLOOKUP函數是查找和引用函數，作用是在表格或數值數組的首列查找指定的數值，并由此返回表格或數組當前行中指定列處的數值。其基本語法形式為：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)。Lookup_value表示要查找的值，它必須位于自定義查找區域的最左列，可以為數值、引用或文本字符串，這里是“D4”，學生選的第一題答案項；Table_array用于查找數據的區域，上面的查找值必須位于這

21、個區域的最左列，可以使用對區域或區域名稱的引用，這里是引用da工作表中的$B$4：$C$18，即第一題選擇題所有可能的答案項及相應分值，對不同工作表的引用和對單元格的引用之間用感嘆號“!”分開，絕對引用加“$”；邏輯值為 FALSE，table_array 不必進行排序，否則table_array 的第一列中的數值必須按升序排列；Col_index_num為table_array中待返回的匹配值的列序號，最左列為1，其右邊一列為2，依此類推，“2”也就是返回C列，即第一題選擇題“得分”情況這一列。range_lookup為一邏輯值，指明函數 VLOOKUP 返回時是精確匹配還是近似匹配，range_value為 FALSE，函數VLOOKUP 將返回精確匹配值。VLOOKUP(D4,da!$B$4:$C$18,2,FALSE)語法解釋：在da工作表的$B$4:$C$18范圍內（即選擇