Excel公式與函數作業的自動檢測與統計

李碧云

摘 要：本文主要研究了在Excel公式與函數的課堂教學中，如何方便快捷的收集學生提交的作業答案，并對學生答案進行自動統計評價的方法，使教師能根據學生的作答情況來調整教學內容。

關鍵詞：Excel公式;作業收集;VBA

在中小學信息技術課程標準中，表格信息的加工這一部分要求學生能夠使用表格處理工具的公式、函數、排序、篩選、分類匯總等功能處理數據[1]。使用Excel處理表格信息是教學的重點內容，編寫公式和函數是一項重要的基本技能。在講解Excel公式和函數時老師一般會使用預設的習題讓學生練習作答，然后逐個查看學生的作答文件來了解學情，這樣做效率很低。

一、常規的檢測方式

常見FTP服務、B/S結構和C/S結構[2]三種應用模式，但多數只能根據預設的答案進行評判，在檢測自由編寫的主觀內容時，往往無能為力，不具有靈活性。

1. FTP服務模式。建立在FTP上的作業提交和批閱方案，由教師布置作業，學生下載完成后上傳，教師再批閱。它只改變了傳輸作業文件的方式，并沒有減少教師的工作量，提高學生的學習效率。

2. B/S結構模式。制作者能靈活地設計各項功能，根據需求進行調整。但需要制作者具備較強的編程能力，需要搭建專用的服務器平臺和數據庫[2]。某些公共資源（如教育云平臺和在線考試系統）可能無法滿足使用者的特殊需求。

3.C/S結構模式。各種多媒體教室廣播程序（如極域多媒體教室、紅蜘蛛等）有課堂測試功能，測試結果只能在相應的軟件中編輯和使用，不具有通用性。

二、教學實踐中總結出的簡便解決方案

如何快速便捷的檢測學生的Excel公式與函數作答情況并統計出每個知識點的作答正確率，經過一段時間的研究，摸索了一套簡便的解決方案。

1.使用Windows的共享文件夾和Excel的共享工作簿功能收集匯總答案。在教師機將收集答案的文件夾設置為隱藏式共享文件夾，并將安全權限設置為“允許所有人修改”。設置隱藏共享是為了能夠避免被其他人隨意修改。將收集答案的工作簿設置為共享工作簿[3]，支持多人編輯同一個文件。

2.使用VBA程序實現答案的提交采集和自動評判。在使用答案收集模板時，需要按照模板格式設置好學號和檢測知識點的答案保存區域。如有多個任務，建議使用不同的工作表來存儲任務答案。學生作答文件需要預設好答案保存的區域，并在VBA代碼中將對應區域的代碼進行更新。實現提交答案的VBA代碼（有刪減）見附錄。

3.使用Excel的統計函數和圖表自動分析學生作答完成情況。在答案收集的文件中，用COUNTA函數來統計完成作答的人數，例如用“=COUNTA（主題任務作答信息！C5：C69） ”來統計作答人數;用COUNTIF函數來統計正確作答人數，例如用“=COUNTIF（主題任務作答信息！E5：E69，"Rank（P3，P：P）"）+COUNTIF（主題任務作答信息！E5：E69，"Rank（P3，P3：P14）"）”來統計正確作答的人數，該問題有兩個正確答案，分別是“Rank（P3，P：P）”和“Rank（P3，P3：P14）”;用自動統計的結果制作“數據點折線圖”或“數據點雷達圖”等圖表來直觀清晰的呈現作答情況。

三、方案可行性驗證和使用注意事項

為了驗證這個方案的可行性，使用該方案在兩所學校的5個機房進行了多次現場教學測試。在使用過程中發現該方案在實施時需要具備一定的前提條件。

1.共享工作簿的使用環境是局域網，要求教師機和學生機處于同一個網段，并且需要打開Windows網絡共享功能，能直接相互訪問。2.使用VBA程序來輔助采集答案，學生端在使用Excel以前需要在菜單“工具→宏→安全性”中進行宏安全性設置，將安全級設置為“中”或者“低”，允許運行宏程序。3.教師端的答案匯總程序和學生端的作答文件需要配合使用，如果檢測的內容有變化，就需要更新VBA代碼和匯總程序，需要具備一定的編程基礎才能修改。

四、方案應用后的效果

在實際教學中使用該方案來進行學生作答情況的檢測，老師在匯總程序端查看統計分析的結果，就能有針對性地對學生作答正確率不高的內容進行講解，提高了教學的效率。

附錄：因篇幅限制僅列舉提交問題Q1的答案的方法：

StuNum = Range（“B8”）.Value

If Range（“E10”）.HasFormula = True Then

Q1 = Right（Range（“E10”）.Formula， _

Len（Range（“E10”）.Formula） - 1）

Else

Q1 = Range（“E10”）.Text

End If'Q1完成，可在此處補充其它問題

If StuNum >= 1 And StuNum <= 65 Then

Workbooks.Open Filename：="＼＼教師機＼答案$＼答案.xls"

Sheets（"單元格引用"）.Columns（"A：A"）.Select

Selection.Find（What：=StuNum， After：=ActiveCell， _

LookIn：=xlValues， LookAt：=xlWhole）.Activate

Range（“C” & ActiveCell.Row）.Value = Q1

ActiveWorkbook.Close Savechanges：=True

ThisWorkbook.Activate

End If

參考文獻

[1]2018年云南省普通高中學業水平標準與考試說明信息技術/云南省教育廳頒布.昆明：云南美術出版社，2017.11

[2]基于asp技術的作業評判與分析系統的設計與實現--潘莎.《電子科技大學》，2013

[3]關于“共享工作簿”功能-微軟support.office.com