使用Excel對多項選擇題自動評分并統(tǒng)計正確率

使用Excel對多項選擇題自動評分并統(tǒng)計正確率使用Excel對多項選擇題自動評分并統(tǒng)計正確率使用Excel對多項選擇題自動評分并統(tǒng)計正確率xxx公司使用Excel對多項選擇題自動評分并統(tǒng)計正確率文件編號：文件日期：修訂次數(shù)：第1.0次更改批準審核制定方案設計，管理制度使用Excel對多項選擇題自動評分并統(tǒng)計正確率

【內容摘要】

對選擇題的答題卷進行批改和分析是教學中經(jīng)常要做的重要工作。利用MicrosoftExcel電子表格的強大功能，用FREQUENCY函數(shù)和數(shù)組公式、COUNT函數(shù)對選各題各選項的人數(shù)及百分比進行統(tǒng)計；用IF函數(shù)和SUM函數(shù)自動計算單項選擇題的總得分；用VLOOKUP函數(shù)和SUM函數(shù)自動計算不定項選擇題的總得分?！娟P鍵詞】Excel選擇題自動結分統(tǒng)計分析

考試是教學中不可缺少的環(huán)節(jié)，是檢查教學質量的重要手段?？荚嚳梢源龠M、激勵學生的進步發(fā)展，讓教師檢查、診斷學生的學習是否達到教學大綱規(guī)定的要求，同時給教師提供反饋信息，以便及時調整教學進度，改進教學方法，提高教學質量。而要達到以上目的，試卷分析是很重要的環(huán)節(jié)，只有對考試結果進行全面細致的分析，才能找出教和學雙方存在的問題，找出有針對性的解決方法，提高教學效果。而全面細致的分析必須要有詳細的數(shù)據(jù)，如及格率、優(yōu)秀率、平均分、標準差、各分數(shù)段人數(shù)、各題難度等，特別是對每題的難度分析，對搞清每個知識點的落實掌握情況是很重要的。而選擇題答卷的統(tǒng)計分析，由于一般的平時測驗都沒有答題卡和讀卡機，老師要對選擇題進行手工逐題的批改記分，如要對各題的答題情況進行比較詳細的分析，許多老師用打五劃“正”的方法，來統(tǒng)計各題的做對和做錯人數(shù)。本人也經(jīng)常做這樣的事，感到效率很低，且容易出錯搞不準確。想到用Excel對考試結果數(shù)據(jù)進行及格率、優(yōu)秀率、平均分、標準差、各分數(shù)段人數(shù)分析很容易，能不能也用Excel對選擇題進行比較詳細的分析，通過反復摸索，找到了以下方法。

下面以60人的班級，20題選擇題為例，多于60人或少于60人可用插入行或刪除行來調整，多于20題或少于20題用插入列或刪除列來調整，都不影響表格的性質。

對單選題的分析

1．先新建一個空白的Excel工作簿，工作表Sheet1重命名為“1班”，輸入表格頭、題號、學號、姓名，C3:V62為數(shù)據(jù)輸入?yún)^(qū)(如圖1)。

2．在W3、W4、W5三個單元格分別輸入數(shù)值、、。在姓名的下方的適當位置，如隔二行B65:B68分別輸入“選A項人數(shù)、…、選D項人數(shù)”。

3．選中C65:C68四個單元格，單擊F2(編輯選定的單元格)，在公式編輯欄或直接在單元格輸入“=FREQUENCY(C3:C62,$W$3:$W$5)”(注意：輸入公式時字母、符號要用英文半角)，按Ctrl+Shift+Enter組合鍵輸入(如圖2)。選中C65:C68光標移到右下角變?yōu)閷嵭牡氖謺r，向右拖拉至V65:V68。

4．填入學生的選擇題答案，選A的填1，選B的填2，選C的填3，選D的填4。就會在65至68行中顯示各題選擇各選項的人數(shù)。至此主要任務完成。

5．為了使數(shù)據(jù)看起來更方便，可在B69輸入“總人數(shù)”，在C69中輸入公式“=COUNT(C3:C62)”，光標移到右下角變?yōu)閷嵭牡氖謺r，向右拖拉至V69，則69行顯示答題總人數(shù)。

6．在B70:B73中分別輸入“選A百分比、…、選D百分比”，在C70中輸入“=C65/COUNT(C$3:C$62)”，光標移到右下角變?yōu)閷嵭牡氖謺r，向下拖拉至C73，向右拖拉至V73，就會在70至73行中顯示各題選擇各選項的人數(shù)的百分比。注意設置C70:V73單元格的格式，點“格式”中“單元格格式”，在“數(shù)字”中選“百分比”，“小數(shù)位數(shù)”設為1位，以免過多的小數(shù)位數(shù)顯示不出。

7．為方便使用，選中第3行，點“窗口”中的“凍結窗口”，凍結表格頭，在輸入數(shù)據(jù)時讓題號不滾動；選中W列，點“格式”“列”“隱藏”可隱藏W列，防止誤操作這列數(shù)據(jù)；也可選中C3:V62數(shù)據(jù)輸入?yún)^(qū)，點“數(shù)據(jù)”、“有效性”、“設置”，在“有效性條件”中“允許”“整數(shù)”，“數(shù)據(jù)”“介于”最小值1，最大值4，防止輸入時誤輸入其它無效數(shù)字。

說明：Excel中函數(shù)是對單元格中的數(shù)值進行處理，而選擇題的選項A、B、C、D這些文本形式很難處理，所以用數(shù)值“1、2、3、4”分別代替“A、B、C、D”。

這里用到函數(shù)“FREQUENCY”和數(shù)組公式。函數(shù)“FREQUENCY（data_array，bins_array）”是以一列垂直數(shù)組返回某個區(qū)域中數(shù)據(jù)的頻率分布，Data_array為一數(shù)組或對一組數(shù)值的引用（即C3:C62），用來計算頻率；Bins_array為間隔的數(shù)組或對間隔的引用（即$W$3:$W$5，$符號是指絕對引用，不隨拖拉而改變引用的單元格），該間隔用于對data_array中的數(shù)值進行分組，在這里、、三個數(shù)形成四個間隔：小于等于；大于小于等于；大于小于等于；大于。而我們用1、2、3、4這四個數(shù)去對應這四個區(qū)間，來代替A、B、C、D這四個選項，所以只要小于等于的數(shù)都可以代替A，大于小于等于的數(shù)都可以代替B，以此類推，只是由于方便只用1、2、3、4來代替A、B、C、D。也就是函數(shù)FREQUENCY返回的數(shù)組中的元素個數(shù)比bins_array（W3:W5）中的元素個數(shù)多一。由于函數(shù)FREQUENCY返回一個數(shù)組，所以必須以數(shù)組公式的形式輸入，選中從公式單元格開始的單元格區(qū)域即C65:C68(四格，比W3:W5多一格)，按F2編輯選中的單元格，在公式編輯欄或直接在單元格輸入公式，再按Ctrl+Shift+Enter以數(shù)組公式的形式輸入。則在C65:C68中的數(shù)據(jù)分別是：小于等于數(shù)值的個數(shù)(即1的個數(shù)，也就是選A的個數(shù))、大于小于等于數(shù)值的個數(shù)(即2的個數(shù)，也就是選B的個數(shù))、大于小于等于數(shù)值的個數(shù)(即3的個數(shù)，也就是選C的個數(shù))、大于數(shù)值的個數(shù)(即4的個數(shù)，也就是選D的個數(shù))。如果公式未以數(shù)組公式的形式輸入，則返回的結果為1。和一般公式的區(qū)別是數(shù)組公式括于大括號({})中。按Ctrl+Shift+Enter可以輸入數(shù)組公式。

對不定項選擇題的分析

不定項選擇題的選項有15種可能情況，即A、B、C、D、AB、AC、AD、BC、BD、CD、ABC、ABD、ACD、BCD、ABCD；分別用數(shù)值1、2、3、4、12、13、14、23、24、34、123、124、134、234、1234來代替，也就是字母A、B、C、D分別用數(shù)字1、2、3、4來代替。

1．和前面相同，建立一個空白的Excel文件，輸入表格頭、題號、學號、姓名，C3:V62為數(shù)據(jù)輸入?yún)^(qū)。也可以在前面單選題表格的基礎上接著進行操作。

2．在W3、W4、…、W16十四個單元格分別輸入數(shù)值、、、、、、、、、、、、、；這14個數(shù)形成的15個區(qū)間分別對應1、2、3、4、12、13、14、23、24、34、123、124、134、234、1234，也就是A、B、…、ABCD這十五種可能選項。在姓名的下方的適當位置，如隔二行B65:B79十五行分別輸入“選A項人數(shù)、選B項人數(shù)、選C項人數(shù)、選D項人數(shù)、選AB項人數(shù)、選AC項人數(shù)、選AD項人數(shù)、選BC項人數(shù)、選BD項人數(shù)、選CD項人數(shù)、選ABC項人數(shù)、選ABD項人數(shù)、選ACD項人數(shù)、選BCD項人數(shù)、選ABCD項人數(shù)”。

3．選中C65:C79十五個單元格，單擊F2(編輯選定的單元格)，在公式編輯欄或直接在單元格輸入“=FREQUENCY(C3:C62,$W$3:$W$16)”，按Ctrl+Shift+Enter組合鍵輸入數(shù)組公式(如圖3)。選中C65:C79，光標移到右下角變?yōu)閷嵭牡氖謺r，向右拖拉至V65:V79。

4．在B80輸入“總人數(shù)”，在C80中輸入公式“=COUNT(C3:C62)”，光標移到右下角變?yōu)閷嵭牡氖謺r，向右拖拉至V80，則第80行顯示答題人數(shù)；

5．在B81:B95中分別輸入“選A百分比、…、選ABCD百分比”，在C81中輸入“=C65/COUNT(C$3:C$62)”，光標移到右下角變?yōu)閷嵭牡氖謺r，向下拖拉至C95，向右拖拉至V95，就會在81至95行中顯示各題選擇各選項的人數(shù)的百分比。

6．隱藏W列；凍結表格頭；有效性條件允許“整數(shù)”，數(shù)據(jù)介于最小值1，最大值1234。在C3:V62中輸入和A、B、C、D對應的1、2、3、4值就統(tǒng)計出各題的選項情況。但要注意的是，如：選項AB和BA等效，但在輸入數(shù)字時只能是12，不能輸入21，而ABC只能是123，不能是132或231，也就是數(shù)字只能從小到大排列。

只要把每個同學選擇題答卷中的答案字母用相應的數(shù)字填入，就可以方便地統(tǒng)計出每題的答題情況。

自動計算單項選擇題的總得分

哎！統(tǒng)計倒是不錯，可這些選擇題是批改了再來統(tǒng)計，還是統(tǒng)計了再來一遍批改，這不是干了二遍嗎豈不是和當初提高效率的初衷相違背了嗎！所以如果在得出統(tǒng)計結果的同時，每個同學的選擇題分數(shù)也出來了多好?？聪旅娌僮鳎?/p>

如果是單選題，在前面單選題表格中的題號下面插入一行成為第3行，存放每小題的相應的答案數(shù)字；在姓名后面插入一列成為C列，存放每個同學選擇題的總得分。在第一個同學的存放成績的單元格C4里輸入：

“=SUM(IF(D4=$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”求和(即正確的題數(shù))；再乘以每題的分值“$C$3”。

每次不同的選擇題要改動的只是答案行的正確答案的數(shù)字，和在C3單元格填入每小題的題分值，每個學生選擇題的總得分在各人姓名后就顯示出來了，還是很方便的。

注意的是，如果選擇題只有10題，剩下其它幾題的答案要填入一個數(shù)如5，不能為空格，因為空格和空格比較表格也認為是相同的，返回賦值1，這樣最后結果就不對了。

這里用到求和函數(shù)SUM，和判斷函數(shù)IF，IF的語法是這樣的：

IF(logical_test,value_if_true,value_if_false)，Logical_test是判斷條件，如滿足返回Value_if_true，不滿足返回Value_if_false，Logical_test和Value_if_false可以是邏輯值，零，也可以是其它公式。

而計算不定項選擇題的得分，也可用這個公式方法，只不過麻煩一點。比如第一題正確答案是ABC，答A或B或C，或答AB，或答AC，或答B(yǎng)C，均可得部分分數(shù)，但一般如果有錯誤的答案選上就不得分了。首先答案行一行肯定不夠了，但如果不引用單元格而直接比較答案，答案行也可不用增加。函數(shù)IF最多可嵌套七層，也就是可比較七個條件，如第一題判斷可用：

“IF(D4=123,"3",IF(D4=12,"2",IF(D4=13,"2",IF(D4=23,"2",IF(D4=1,"1",IF(D4=2,"1",IF(D4=3,"1"，0)))))))”，即如選ABC得3分，選AB或AC或BC得2分，選A或B或C得1分，否則為0分。而這只是一題，還要用SUM函數(shù)將所有各題判斷加起來，如這樣：“=SUM(第1題判斷，第2題判斷，…，…)”，式子很長。且每次不同的選擇題都要修改公式，很麻煩易出錯，必須另想辦法。

自動計算不定項選擇題的總得分

如果把不定項選擇可能的答案的答題項很多行都列出來，數(shù)據(jù)很多，所以在上面工作表基礎上要另建一個工作表命名為“da”。在“da”工作表中建立如圖5所示表格，A列是所有可能的選項，共15種可能，只不過答案是ABCD的情況一般不會出現(xiàn)，這一項等會另作他用。每一列的題號后面都插入一列，作為相應選項的得分值，如只有一個選項“C”，則數(shù)字“3”后面填“1”，其它各項都填“0”；如正確選項是“ABC”，則“ABC”相應數(shù)字后面“123”填“1”，而選AB、AC、BC都可得部分分數(shù)，如得小題滿分的2/3分數(shù)，則在相應空格中都填上(或2/3，單元格格式中數(shù)字項要設置一下)，其它各項都填“0”；選A或B或C也可得分，如得小題滿分的1/3分數(shù)，則在“1”、“2”、“3”后面都填上(或1/3)，其它各項都填“0”。為方便對照，在第3列列出正確的答案；在左上角空格中選一格如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題，則從“D－W”有20項，要有細心和耐心嗬！如果你確定以后肯定沒有20個選擇題，也可以少輸入幾個，如果不能保證的話，那只能為以后少點麻煩現(xiàn)在辛苦點了，因為用插入列增加題數(shù)在這里要改變公式。求和SUM函數(shù)里的VLOOKUP函數(shù)是查找和引用函數(shù)，作用是在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當前行中指定列處的數(shù)值。其基本語法形式為：

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)。

Lookup_value表示要查找的值，它必須位于自定義查找區(qū)域的最左列，可以為數(shù)值、引用或文本字符串，這里是“D4”，學生選的第一題答案項；Table_array用于查找數(shù)據(jù)的區(qū)域，上面的查找值必須位于這個區(qū)域的最左列，可以使用對區(qū)域或區(qū)域名稱的引用，這里是引用da工作表中的$B$4：$C$18，即第一題選擇題所有可能的答案項及相應分值，對不同工作表的引用和對單元格的引用之間用感嘆號“!”分開，絕對引用加“$”；邏輯值為FALSE，table_array不必進行排序，否則table_array的第一列中的數(shù)值必須按升序排列；Col_index_num為table_array中待返回的匹配值的列序號，最左列為1，其右邊一列為2，依此類推，“2”也就是返回C列，即第一題選擇題“得分”情況這一列。range_lookup為一邏輯值，指明函數(shù)VLOOKUP返回時是精確匹配還是近似匹配，range_value為FALSE，函數(shù)VLOOKUP將返回精確匹配值。

VLOOKUP(D4,da