EXCEL VBA编制试卷分析软件之探索与实践
开篇：润墨网以专业的文秘视角，为您筛选了一篇EXCEL VBA编制试卷分析软件之探索与实践范文，如需获取更多写作素材，在线客服老师一对一协助。欢迎您的阅读与分享！
[中图分类号]R319[文献标识码]C [文章编号]1673-7210（2007）06（a）-118-02
考试是教学过程中的一个重要环节，而科学的试卷分析，对于提高命题水平和考试质量、优化教学效果、提高教学质量尤为关键。以往我校对试卷的分析基本是手工计算，或仅借助计算器、Excel软件的简单功能等来完成。由于试卷分析的计算量大，耗时多，计算方法又相当繁杂，计算的指标又较多，手工操作不可避免地出现许多错误，从而导致计算结果不准确，直接影响试卷分析的质量。鉴于此，我们对常用软件Microsoft Excel进行了开发，编制了适合我校进行学生考试成绩分析的试卷分析软件。
1 采用Excel解决问题的思路
Excel为目前最常用的电子表格软件，多数老师对其都较为熟悉，进行简单的操作不成问题，不需进行额外学习。Excel的主要特色是能够按预先编制的公式进行计算，并提供了对工程、财会、统计等多方面的众多函数以及良好的文字编排功能。
基于此，在最初的软件编制中，我们采用了Excel公式和函数。经过使用，普遍反应较好。但在使用中也出现了一些问题，最多的问题是数据的拖拽造成计算的错误。Excel计算的一个显著特点是需事先指定计算流程、单元格公式计算的一一对应，如将原始数据拖到不同的单元格将会破坏计算的流程，造成计算结果的错误，而且其计算是单向性的，如果想进行交互式计算，使用Excel提供的函数功能就很难实现。虽然Excel提供了单变量求解功能，但在很多场合无法获得正确结果。
然而，Excel却提供了良好的二次开发接口功能，如果要进行复杂计算，解决上述问题，借助Excel的Visual Basic Application（VBA）功能无疑是一个较好的方法。
鉴于此，我们对软件进行了全面改写，全部改用VBA语言编制。我们的思路是先用Excel做出所需要的各表，再以其为模板，应用VBA来实现其设计，即生成各表；然后用VBA进行各指标计算，最后再将结果传递至试卷分析表，其结果可供分析和打印。本版软件的设计也相应进行了其他一些方面的改进，如使程序和保存的文件脱离，使保存的文件体积大为减小，避免了把软件程序留存在文件中的弊端；取消了计算量的限制；明显提高了计算速度；在计算前对输入的数据进行核查等。
2 程序设计和功能实现
通过对试卷分析各计算过程的分析，结合Excel的计算功能，同时考虑到对其他一些相关功能的实现，将本软件共分为六大模块：工作表设计生成模块、移除Excel图标模块、替换Excel菜单栏和工具栏模块、计算模块、文件导入导出模块、打印模块。计算模块是程序的核心。
2.1 工作表设计生成模块
2.1.1 成绩录入表生成
主要是用于将学生成绩进行录入的。首先是试卷分析表中要求的一般项目（如考试科目、年级、专业等）、计算所需数据的输入等。因数据录入是采用在Excel表格中操作，所以本模块设计相对比较简单。主要设计好各项目所在的位置、大小、单元格的合并、题型、题数、各题满分值、总分及各题分数等所在位置。设计尽量简单适用，方便数据的输入。采用一些控制手段对输入值进行约束，并在用户输入数值错误时给予即时提示，指出应输入数值的范围。除用户输入数据的单元格外，所有单元格都进行了保护，以防用户随意更改。
2.1.2 分析计算表生成
分析计算表是用来存放计算数据的。为防止被用户修改数据，在工作簿中将该工作表的显示属性设置成隐藏。其实此举并非必须，因为我们在计算模块的设计时已经考虑到计算数据的重要性，在用VBA把数据填写到分析计算表之前和计算完毕之后把表中的数据全部清除，以防用户修改数据或原始数据改变后重新计算造成的结果不正确。
2.1.3 试卷分析表生成
Excel提供了优秀的表格排版功能，可以完全满足常规排版和输出的需要，Excel的VBA对版面各部分都提供了良好的控制功能，使得我们可以通过VBA程序来控制版面的内容和表现形式。我们的做法是先在工作薄中手动生成一个工作表作为“模板”，模板就是最终试卷分析表的“雏形”，模板中包含所有格式和用到的图表等，并对该工作表版面进行控制和打印排版设置，使得该工作表能直接打印输出形成按要求制作的试卷分析表。至于工作表中的数据，由VBA在计算过程即时填入。首先在每个单元格中自动填入“―”，然后实现动态修改，将每处有数据内容或文字内容的单元格把“―”置换出，填入相应的内容。生成的试卷分析表共分两个打印页面，第一页主要是各分析计算结果及判定，第二页是综合分析表，是用来手工填写分析报告的。此表须进行保护，以防页面的设置遭到破坏而不能正确打印和导出。
2.2 移除Excel图标模块
本软件对Excel工作簿和工作表窗口中的工作簿图标、工作表图标和最大化、最小化、关闭按钮等进行了移除。程序调用了Windows API函数，还使用了类模块。
2.3 替换Excel菜单栏和工具栏模块
Excel菜单栏的替换首先需删除Excel的菜单栏，然后创建一个新的菜单栏。同时还要编写一个恢复Excel的菜单栏及删除自定义菜单栏的程序来实现相反的过程。
工具栏的替换类似地还要移去Excel 97的工具栏，并在退出应用系统时恢复它。工具栏是一个系列和数目都不固定，有时可能还有不知名称的自定义工具栏，因而实现此过程较困难。为此我们使用了自定义集合来处理这一问题。
对Excel图标的移除和菜单栏、工具栏的替换，其主要目的是防止Excel原工具和菜单对本软件在使用时造成影响。程序编制完成后，还需要把它们进行连接，使得在打开工作簿时能自动连接为一个整体，完成各自的功能。同样，在每次关闭工作簿时能删除自定义菜单和工具栏，恢复Excel的原始环境。
2.4 计算模块
计算模块是本程序的核心，其编制较为复杂。其主要的工作是各指标计算的实现，最后把结果分别逐一填至分析计算表和试卷分析表。
2.4.1 一般项目
用VBA把一般项目填入相应的单元格。本过程的实现相对较易，只要把成绩录入表中的各项让VBA逐一给分析计算表和试卷分析表相应的单元格赋值即可。
2.4.2 各主要指标的计算及实现
试卷分析中的计算指标较多，计算方法也不同。现就本软件中选用的最主要的指标-难度、区分度、信度和效度（即“四度”）的计算和实现进行讨论。
2.4.2.1 难度（P）：难度是指试题或试卷的难易程度，是评估试卷质量的主要指标之一。设P为试卷的难度系数，x为试卷的平均分，W为试卷的满分，则试卷的难度为：P＝1－，用同样的方法可计算出各题的难度系数。
这一指标的计算主要是用Worksheet Function.Average来实现。
2.4.2.2 区分度（D）：区分度是指试题或试卷对学生实际水平的区分程度或鉴别能力。计算区分度时首先把考生试卷总分由高到低排序，取前27％的学生作为高分组，取后27％的学生作为低分组，分别计算高分组学生的平均分Xh和低分组学生的平均分Xl，设试卷满分为W，试卷区分度为D，则试卷区分度为：D＝ 。同理，可计算出各题的区分度。
区分度计算的实现难度较大，过程较为繁杂。主要步骤用到数据的转移、排序、高低段人数及平均分的计算等。主要用到.Sort、Worksheet Function.Count、Worksheet Function.Average等方法。
2.4.2.3 信度（B）
信度是衡量试题可靠性与稳定性的指标。我们采用的是克伦巴赫（Cronbach）的α-系数公式：B＝。其中B表示试卷的信度，n表示试题总数，Si表示第i题标准差，S2表示试卷方差。Si＝（m表示考生人数，Xij表示第j个考生第i题的成绩，Xi表示全部考生第i题的平均分）。S2＝ （m表示考生人数，Xj表示第j个考生试卷的总分，X表示全部考生试卷的平均分）。
主要用Worksheet Function.StDev来实现标准差、方差等的计算。此指标计算时用到总分、平均分等，但此处并非全部考生的，应该是用于计算各题指标的考生总分及平均分，并且是每个考生都要计算。
2.4.2.4 效度（R）：效度是衡量考试结果对考试目标实现程度的指标。试卷的效度可以通过试卷的平均区分度来衡量。R＝ 。其中R表示试卷的效度，Di表示第i题的区分度，n表示试题总数。
这一指标计算的实现较为容易，只涉及到Workbook Function.Average。
2.5 文件导入导出模块
文件的导出即保存文件。我们过去的做法是将工作表和整个程序一起保存在文件中，这一做法存在明显弊端。通过VBA的应用，很好地解决了这一问题。我们的思路和做法是仅把成绩录入表和试卷分析表两个工作表进行复制，然后设定保存的路径和文件名进行保存。保存路径是设置在与软件同一路径下，文件名系统给定。这一做法是否合理，有待使用者评判。我们也考虑过让用户指定路径和文件名，但这样做在每次保存文件时系统都要提示保存路径和文件名，使用不便，因而未采用。
文件导入是将导出文件中的成绩录入表导入到系统中。这主要是考虑到在输入量大时一次不能完成全部数据录入，需多次录入才能完成，或是多人同时录入时。这一过程的实现主要是用外部的成绩录入表替换系统的成绩录入表，这涉及到改名、删除、加入工作表等一系列步骤。
2.6 打印模块
这一功能是使用户在系统中能够进行打印。主要用Worksheets.Print Out Copies来实现。当然用户也可以在导出文件后调用Excel原有的打印程序进行打印。
上述各模块程序中用到了大量的Excel对象、方法和属性，处理对象用到了许多类型的语句和控制流，如顺序结构、循环结构、条件分支结构等。在各指标的计算中，我们尽量采用Worksheet Function，除非必须，尽量不使用自定义函数，这样可使程序的运行速度大为提高。在程序运行的各个环节还设置了大量用于进行控制和错误提示的语句，以对用户进行错误警示。
3 小结
通过本系统的开发，我们认识到，掌握VBA语言对于更好地应用Excel具有十分重要的意义。不仅可以使工作更高效，更方便，而且可以更加自动化，甚至智能化。即使是非计算机专业的人员，也可以利用EXCEL VBA方便、快捷地开发出结合自己专业的高水平的应用软件。而计算机专业人员开发一些应用于特定领域的系统，也不必非要使用专业的程序开发工具，而可以在Excel等软件基础上，利用相应软件的强大功能，再配合有关的VBA语言，可以快速、高效地开发出相应的应用软件。
（收稿日期：2007-04-07）
本文中所涉及到的图表、注解、公式等内容请以PDF格式阅读原文。