Excel公式與函數(shù)作業(yè)的自動(dòng)檢測(cè)與統(tǒng)計(jì)

李碧云

摘 要：本文主要研究了在Excel公式與函數(shù)的課堂教學(xué)中，如何方便快捷的收集學(xué)生提交的作業(yè)答案，并對(duì)學(xué)生答案進(jìn)行自動(dòng)統(tǒng)計(jì)評(píng)價(jià)的方法，使教師能根據(jù)學(xué)生的作答情況來調(diào)整教學(xué)內(nèi)容。

關(guān)鍵詞：Excel公式;作業(yè)收集;VBA

在中小學(xué)信息技術(shù)課程標(biāo)準(zhǔn)中，表格信息的加工這一部分要求學(xué)生能夠使用表格處理工具的公式、函數(shù)、排序、篩選、分類匯總等功能處理數(shù)據(jù)[1]。使用Excel處理表格信息是教學(xué)的重點(diǎn)內(nèi)容，編寫公式和函數(shù)是一項(xiàng)重要的基本技能。在講解Excel公式和函數(shù)時(shí)老師一般會(huì)使用預(yù)設(shè)的習(xí)題讓學(xué)生練習(xí)作答，然后逐個(gè)查看學(xué)生的作答文件來了解學(xué)情，這樣做效率很低。

一、常規(guī)的檢測(cè)方式

常見FTP服務(wù)、B/S結(jié)構(gòu)和C/S結(jié)構(gòu)[2]三種應(yīng)用模式，但多數(shù)只能根據(jù)預(yù)設(shè)的答案進(jìn)行評(píng)判，在檢測(cè)自由編寫的主觀內(nèi)容時(shí)，往往無能為力，不具有靈活性。

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

2. B/S結(jié)構(gòu)模式。制作者能靈活地設(shè)計(jì)各項(xiàng)功能，根據(jù)需求進(jìn)行調(diào)整。但需要制作者具備較強(qiáng)的編程能力，需要搭建專用的服務(wù)器平臺(tái)和數(shù)據(jù)庫(kù)[2]。某些公共資源（如教育云平臺(tái)和在線考試系統(tǒng)）可能無法滿足使用者的特殊需求。

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

二、教學(xué)實(shí)踐中總結(jié)出的簡(jiǎn)便解決方案

如何快速便捷的檢測(cè)學(xué)生的Excel公式與函數(shù)作答情況并統(tǒng)計(jì)出每個(gè)知識(shí)點(diǎn)的作答正確率，經(jīng)過一段時(shí)間的研究，摸索了一套簡(jiǎn)便的解決方案。

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

2.使用VBA程序?qū)崿F(xiàn)答案的提交采集和自動(dòng)評(píng)判。在使用答案收集模板時(shí)，需要按照模板格式設(shè)置好學(xué)號(hào)和檢測(cè)知識(shí)點(diǎn)的答案保存區(qū)域。如有多個(gè)任務(wù)，建議使用不同的工作表來存儲(chǔ)任務(wù)答案。學(xué)生作答文件需要預(yù)設(shè)好答案保存的區(qū)域，并在VBA代碼中將對(duì)應(yīng)區(qū)域的代碼進(jìn)行更新。實(shí)現(xiàn)提交答案的VBA代碼（有刪減）見附錄。

3.使用Excel的統(tǒng)計(jì)函數(shù)和圖表自動(dòng)分析學(xué)生作答完成情況。在答案收集的文件中，用COUNTA函數(shù)來統(tǒng)計(jì)完成作答的人數(shù)，例如用“=COUNTA（主題任務(wù)作答信息！C5：C69） ”來統(tǒng)計(jì)作答人數(shù);用COUNTIF函數(shù)來統(tǒng)計(jì)正確作答人數(shù)，例如用“=COUNTIF（主題任務(wù)作答信息！E5：E69，"Rank（P3，P：P）"）+COUNTIF（主題任務(wù)作答信息！E5：E69，"Rank（P3，P3：P14）"）”來統(tǒng)計(jì)正確作答的人數(shù)，該問題有兩個(gè)正確答案，分別是“Rank（P3，P：P）”和“Rank（P3，P3：P14）”;用自動(dòng)統(tǒng)計(jì)的結(jié)果制作“數(shù)據(jù)點(diǎn)折線圖”或“數(shù)據(jù)點(diǎn)雷達(dá)圖”等圖表來直觀清晰的呈現(xiàn)作答情況。

三、方案可行性驗(yàn)證和使用注意事項(xiàng)

為了驗(yàn)證這個(gè)方案的可行性，使用該方案在兩所學(xué)校的5個(gè)機(jī)房進(jìn)行了多次現(xiàn)場(chǎng)教學(xué)測(cè)試。在使用過程中發(fā)現(xiàn)該方案在實(shí)施時(shí)需要具備一定的前提條件。

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

四、方案應(yīng)用后的效果

在實(shí)際教學(xué)中使用該方案來進(jìn)行學(xué)生作答情況的檢測(cè)，老師在匯總程序端查看統(tǒng)計(jì)分析的結(jié)果，就能有針對(duì)性地對(duì)學(xué)生作答正確率不高的內(nèi)容進(jìn)行講解，提高了教學(xué)的效率。

附錄：因篇幅限制僅列舉提交問題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完成，可在此處補(bǔ)充其它問題

If StuNum >= 1 And StuNum <= 65 Then

Workbooks.Open Filename：="＼＼教師機(jī)＼答案$＼答案.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

參考文獻(xiàn)

[1]2018年云南省普通高中學(xué)業(yè)水平標(biāo)準(zhǔn)與考試說明信息技術(shù)/云南省教育廳頒布.昆明：云南美術(shù)出版社，2017.11

[2]基于asp技術(shù)的作業(yè)評(píng)判與分析系統(tǒng)的設(shè)計(jì)與實(shí)現(xiàn)--潘莎.《電子科技大學(xué)》，2013

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