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