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

1、使用Excel對多項選擇題自動評分并統(tǒng)計正確率【內(nèi)容摘要】   對選擇題的答題卷進(jìn)行批改和分析是教學(xué)中經(jīng)常要做的重要工作。利用Microsoft Excel電子表格的強(qiáng)大功能，用FREQUENCY函數(shù)和數(shù)組公式、COUNT函數(shù)對選各題各選項的人數(shù)及百分比進(jìn)行統(tǒng)計；用IF函數(shù)和SUM函數(shù)自動計算單項選擇題的總得分；用VLOOKUP函數(shù)和SUM函數(shù)自動計算不定項選擇題的總得分。【關(guān)鍵詞】Excel 選擇題 自動結(jié)分 統(tǒng)計分析   考試是教學(xué)中不可缺少的環(huán)節(jié)，是檢查教學(xué)質(zhì)量的重要手段?？荚嚳梢源龠M(jìn)、激勵學(xué)生的進(jìn)步發(fā)展，讓教師檢查、診斷學(xué)生的

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

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

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

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

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

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

8、、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ù)分別是：小于等于1.5數(shù)值的個數(shù)(即1的個數(shù)，也就是選A的個數(shù))、大于1.5小于等于2.5數(shù)值的個數(shù)(即2的個數(shù)，也就是選B的個數(shù))、大于2.5小于等于3.

9、5數(shù)值的個數(shù)(即3的個數(shù)，也就是選C的個數(shù))、大于3.5數(shù)值的個數(shù)(即4的個數(shù)，也就是選D的個數(shù))。如果公式未以數(shù)組公式的形式輸入，則返回的結(jié)果為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、

10、4來代替。1和前面相同，建立一個空白的Excel文件，輸入表格頭、題號、學(xué)號、姓名，C3:V62為數(shù)據(jù)輸入?yún)^(qū)。也可以在前面單選題表格的基礎(chǔ)上接著進(jìn)行操作。2在W3、W4、W16十四個單元格分別輸入數(shù)值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個數(shù)形成的15個區(qū)間分別對應(yīng)1、2、3、4、12、13、14、23、24、34、123、124、134、234、1234，也就是A、B、ABCD這十五種可能選項。在姓名的下方的適當(dāng)位置，如隔二行B65:B79十五行分別輸入“選A項人數(shù)、選B項人數(shù)、選C項

11、人數(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，光標(biāo)移到右下角變?yōu)閷嵭牡氖謺r，向右拖拉至V65:V79。4在B80輸入“總?cè)藬?shù)”，在C80中輸入公式“=COUNT(C3:C62)”，光標(biāo)移到右下角變?yōu)閷嵭牡氖謺r，向右

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

13、160;  只要把每個同學(xué)選擇題答卷中的答案字母用相應(yīng)的數(shù)字填入，就可以方便地統(tǒng)計出每題的答題情況。自動計算單項選擇題的總得分   哎！統(tǒng)計倒是不錯，可這些選擇題是批改了再來統(tǒng)計，還是統(tǒng)計了再來一遍批改，這不是干了二遍嗎？豈不是和當(dāng)初提高效率的初衷相違背了嗎！所以如果在得出統(tǒng)計結(jié)果的同時，每個同學(xué)的選擇題分?jǐn)?shù)也出來了多好。看下面操作：如果是單選題，在前面單選題表格中的題號下面插入一行成為第3 行，存放每小題的相應(yīng)的答案數(shù)字；在姓名后面插入一列成為C列，存放每個同學(xué)選擇題的總得分。在第一個同學(xué)的存放成績的單元格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”，然后向下拖拉到最后一個同學(xué)止(如圖4)。意思是比較D4單元格如果和D3單元格值相同，賦值為1；比較E4單元格如果和E3單元格值相同，賦值為1；直至最后一題；再把所有“1”求和(即正確的題數(shù))；再乘以每題的分值“$C$3”。   每次不同的選擇題要改動的只是答案行的正確答案的數(shù)字，和在C3單元格填入每小題

15、的題分值，每個學(xué)生選擇題的總得分在各人姓名后就顯示出來了，還是很方便的。   注意的是，如果選擇題只有10題，剩下其它幾題的答案要填入一個數(shù)如5，不能為空格，因為空格和空格比較表格也認(rèn)為是相同的，返回賦值1，這樣最后結(jié)果就不對了。這里用到求和函數(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可以是邏輯值，零，

16、也可以是其它公式。   而計算不定項選擇題的得分，也可用這個公式方法，只不過麻煩一點。比如第一題正確答案是ABC，答A或B或C，或答AB，或答AC，或答B(yǎng)C，均可得部分分?jǐn)?shù)，但一般如果有錯誤的答案選上就不得分了。首先答案行一行肯定不夠了，但如果不引用單元格而直接比較答案，答案行也可不用增加。函數(shù) 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函數(shù)將所有各題判斷加起來，如這樣：“=SUM(第1題判斷，第2題判斷，)”，式子很長。且每次不同的選擇題都要修改公式，很麻煩易出錯，必須另想辦法。自動計算不定項選擇題的總得分   如果把不定項選擇可能的答案的答題項很多行都列出來，數(shù)據(jù)很多，所以在上面工作表基礎(chǔ)上要另建一個工作表命名為“da”。在“da”工作表中建立如圖5所示表格，A列是所有可能的選

18、項，共15種可能，只不過答案是ABCD的情況一般不會出現(xiàn)，這一項等會另作他用。每一列的題號后面都插入一列，作為相應(yīng)選項的得分值，如只有一個選項“C”，則數(shù)字“3”后面填“1”，其它各項都填“0”；如正確選項是“ABC”，則“ABC”相應(yīng)數(shù)字后面“123”填“1”，而選AB、AC、BC都可得部分分?jǐn)?shù)，如得小題滿分的2/3分?jǐn)?shù)，則在相應(yīng)空格中都填上0.667(或2/3，單元格格式中數(shù)字項要設(shè)置一下) ，其它各項都填“0”；選A或B或C也可得分，如得小題滿分的1/3分?jǐn)?shù)，則在“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項，要有細(xì)心和耐心嗬！如果你確定以后肯定沒有20個選擇

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

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