ExcelVBA在中小学成绩统计分析中的应用探索

ExcelVBA在中小学成绩统计分析中的应用探索

何沣燊摘要首先通过调查法了解中小学教师对Excel软件的使用情况及成绩统计的工作情况，发现大部分中学教师在使用Excel进行成绩统计时存在一定的操作困难，且由于数据量大，重复的手动移动数据的任务让他们感到疲累。ExcelVBA可以使Excel中的重复性任务自动化、个性化，能大大提高教师的成绩统计分析工作效率。通过实验法，向参与调查采访的教师提供利用ExcelVBA编写的成绩统计程序试用，并获得他们的反馈，据此在文中提出一些ExcelVBA在成绩统计中应用的可行性建议。KeyExcel；ExcelVBA；成绩统计：TP317.3：B：1671-489X（2016）04-0049-051前言学生的成绩统计分析是教师在每次考试后必须进行的一项工作。参加考试的班别、科目、题型众多，考试成绩的数据量也十分庞大。教师使用Excel统计分析成绩时，需进行以下工作：学生考试成绩的填写或移动；计算总分、平均分、标准差等数据；多次考试的成绩汇总；成绩数据的筛选呈现；绘制图表分析成绩。其中一些工作需要经过多个重复的步骤，还要调用一些Excel的函数功能。如果能够应用ExcelVBA，将成绩统计的功能自动化或部分自动化，将重复的工作步骤自动执行，可以大大提高教师的成绩统计工作效率。如果能设计出较为通用的ExcelVBA成绩统计数据，更是可以让一些原本不熟悉Excel统计功能和函数功能的教师轻松地在Excel这个平台上统计分析考试成绩。2Excel在中学成绩统计中的现状ExcelVBA使Excel的使用更自动化和个性化，在企业办公、财务统计等领域中已经得到很广泛的应用。但由于教师的工作重心更多放在课程教学中，使ExcelVBA在教学领域的应用有一定的难度。通过采访调查获悉，由于我国教育信息化工作的日渐推进，计算机已经成为大部分中学教师重要的工具。每次考试过后，几乎所有有设备条件的中学教师（以广州市铁一中学、广铁一小、明新中学等参与调查的中小学教师为样本，在其他学校中可能存在偏差）都会使用Excel来统计考试成绩。大部分教师主要利用Excel表格的格式设计、求和（总分）、求平均值、排序、制作打印成绩条等功能。而在一些重要考试后（如中高考模拟考、期中和期末考、调研水平测试等），一些教师还会利用Excel制作图表、计算名次、筛选分数段。在使用Excel的情况上，非信息类科目教师主要使用Excel中的格式设置、图表、条件格式和一些简单的函数功能；而一些复杂的任务如函数的嵌套、跨文件调用数据等，主要是求助于学校的信息教师来完成。信息教师都能熟练使用Excel，但大多数情况下帮助其他科目教师制作成绩统计的工作表都是一次性的。特别是数据调用、嵌套函数等任务，在每次有新的考试成绩后，都要对工作表中内容进行大量的更改。这样加大了原本就承担了学校中大量信息化工作任务的信息教师的工作压力。3ExcelVBA在中学成绩统计中的应用情况根据采访调查，只有极少数中小学教师在成绩统计中能用到简单的“录制宏”和相关的VBA代码。这样的应用能使某些Excel中不断重复的任务自动快速地完成，但对于使用ActiveX控件和一些较为复杂的VBA代码感到比较困难。而大部分非信息类教师在Excel的使用中不涉及VBA，不了解VBA强大的功能，在平时的工作中也很难抽出时间去进行系统的、细致的学习。现在各中学的信息教师大多有很好的编程基础，且VBA与中学信息课程中的编程内容所用语言VB一脉相承，信息教师能很快掌握并合理使用。若信息教师利用ExcelVBA为所在学校的教师编写出符合学校需求、能重复长期使用的成绩统计分析程序，对整个学校的教学工作极为有利。同时也免去了每次考试后其他科目的教师求助于信息教师用Excel统计成绩的工作压力。4ExcelVBA的使用方法ExcelVBA与Excel函数功能结合利用ExcelVBA可以对工作表中指定的区域进行赋值操作。赋值可以是数值、文本等。Excel中的函数功能主要是通过在单元格中输入函数语句实现的。单元格中存放的函数语句是文本形式的。通过VBA代码可以自动将函数语句输入指定单元格中。示例如下。在工作表中添加一个图1所示命令按钮（CommandButton）控件。在设计模式下双击该按钮，进入VBA代码编写界面。输入代码：保存代码后回到工作表，点击按钮，因按钮的caption属性仍为”CommandButton1”，则执行代码中的Else语句，向P列单元格中填入求平均值的函数式。按钮Caption属性更改为“总分”，即提示再次点击按钮将呈现总分。结果如图2所示。若再次点击按钮，则P列填充总分，按钮Caption属性更改为“平均分”。通过一个按钮和代码，就可以实现一列单元格中函数在求和求平均值之间切换。此例仅说明VBA代码和函数功能结合的使用方式，实际可编写逻辑结构更复杂的代码，填入的函数式在功能、嵌套结构上也可以更复杂，从而使实现的功能更灵活强大。录制宏与Excel功能结合作为一个功能丰富完善的办公统计软件，Excel中集成了大量数据统计分析、工作表格式设置的功能。通过录制宏的方式可以将在Excel中的操作步骤录制成代码，通过对这些代码的修改可以实现个性化的功能。示例如下。在Excel“开发工具”功能区中，点击“录制宏”开始录制代码。选择F列语文成绩，设置条件格式（图3）。完成后选择开发工具中的“停止录制”。停止录制宏后查看代码，在VBA工程的“模块”目录下，查看模块1中保存了录制的代码：这段代码可以作为一个模板，修改其中的参数，则可以使条件格式的显示形式更个性化。将这段代码与控件结合使用，通过控件就可以设置条件格式，在多次设置时大大减少了操作步骤。ExcelVBA代码与控件的使用通过编写代码，直接对工作表中数据进行分析和呈现。向工作表中添加一个名称为“CommandButton2”的命令按钮控件。在设计模式下双击按钮，进入代码的编写。输入代码：该段代码的功能是统计F列上的语文成绩，通过MsgBox对话框显示语文成绩中110分上下的学生分别有多少人。编写完成后点击按钮，效果如图4所示。通过编写代码，还可以对数据进行选定、移动、分析等多种操作，但需要较好的编程基础和算法思维。5制作并试用ExcelVBA成绩统计分析程序由于大部分教师不能自行设计编写一个系统的成绩统计分析程序，实验通过设计制作一个ExcelVBA成绩统计分析程序，交由教师试用，来获得教师的反馈。根据采访调查的结果，分析教师成绩分析统计的需求，确定程序的功能。基础的功能包括：1）花名册的输入和修改；2）成绩表中的考试科目、综合科目的设置；3）成绩表按每次考试的新建、修改、删除；4）成绩录入成绩表；5）成绩自动汇总排名；6）成绩分析的功能；7）多次考试成绩的汇总；8）筛选呈现特定的学科、班级的成绩；9）选择学生个人的多次成绩呈现；10）制作成绩打印条；11）年级成绩统计图表；12）个人成绩图表。在这些功能中，除了成绩和花名册的录入由教师按照Excel原来的数据输入方式输入相应的表中，其他的功能全部通过按钮、下拉列表控件或者程序自动运行代码完成。图5是程序运行后显示的界面，是程序的启动界面，基础的功能主要通过右边两个按钮启动。成绩分析功能设计在成绩汇总表中，由最左边的按钮打开进入。启动程序后点击“打开模文件”，如图6所示，可以进入花名册的输入和成绩表的设置。花名表中教师可以在特定的位置输入班级名称、学号、学生姓名等信息。关闭文件后会弹窗提示保存数据。这些花名册数据会被成绩表输入表和汇总表等功能自动调用。成绩输入表就是每次输入成绩的表格模板。在成绩输入表模板中，教师可以通过设置好的控件对考试的科目范围（即有哪些科目）、综合科（文综或理综）进行设置。成绩输入表模板设置好后，关闭这个工作表，程序会自动保存设置，回到开始的启动界面。点击“打开成绩输入表”后，弹出如图3所示的窗口，通过这个窗口的几个按钮，教师可以新建成绩表、打开某次考试的成绩表输入成绩、删除某次考试的成绩表或重命名某个成绩表。在完成花名册输入、成绩表的设置、成绩的输入后，关闭工作表回到启动界面。通过这些功能，建立的成绩表格式、功能都与成绩输入表模板一致。相较于不利用ExcelVBA统计汇总成绩，每次都要手动新建成绩表并设置成相同的格式；利用ExcelVBA制作的程序，大大减少了教师制作成绩表的工作步骤，且由于数据的存放方式一致，程序可以快速地调用数据，完成多班级多次多科目考试的成绩数据调用分析工作。建立成绩表并输入学生多次考试成绩数据后，从启动界面的“打开成绩汇总表”按钮进入汇总表。通过窗体和控件，可以对汇总表中的成绩数据进行筛选查看。部分效果如图7～图9所示。还可查看学生个人分数、学生分数图表，具备制作成绩条打印等多种功能。交由广州市铁一中学教师使用后获得反馈意见如下：“我使用得还不够熟练，就觉得提示界面还不够充分，查找、成绩条打印、分数、排位这些都比较好。还可以增加一些双上线的内容，比如说50%的学生总分达到一本，各学科是否达标，能够显示出来。”（根据广州市铁一中学高三黄老师原话记录。）整个程序包含了成绩统计用到的一般功能，其中的班级统计、学生个人统计、年级成绩分析等功能十分实用。但是成绩功能众多，也导致教师使用时存在一定的困难，需要有更充分明确的界面提示，帮助教师更好地使用该程序进行成绩统计分析工作。（根据广州市铁一中学及其他省市中学试用程序的教师填写的问卷整理得。）6ExcelVBA在中小学成绩统计分析中应用的建议根据ExcelVBA编写程序的特点，以及教师试用ExcelVBA编制的成绩统计分析程序反馈的意见，提出两点建议。1）非信息类教师大多数有使用Excel进行统计分析。录制宏、ActiveX控件等功能较易掌握，可以向非信息类教师推广录制宏、控件等功能的简单使用。学校在培训教师信息化能力时，应适当加入ExcelVBA的内容，可以大大提高教师在成绩统计分析上的工作效率。2）信息类教师大多具有良好的编程基础及Excel软件使用基础，在学校的信息化工作中，信息类教师应重视ExcelVBA的应用。无论是教务管理、成绩统计分析、学校财务工作信息化等方面，均应让ExcelVBA大展身手。协助非信息类教师制作成绩统计分析工作表时，可参考文中的成绩统计分析程序，设计编写适合所在学校使用的小程序，提高非信息类教师的工作效率。这样也减少每次考试后都要制作成绩表的工作压力。Reference[1]郗金甲，范进勇，李练，等.ExcelVBA实战技巧精粹（修订版）[M].北京：人民邮电出版社，2013.[2]马文宇.Excel中小学标准化测试中教育信息处理的应用[J].中国现代教育装备，2009（12）.[3]沈浩.Excel高级应用与数据分析[M].北京：电子工业出版社，20