用EXCEL中的VBA进行复杂的数据统计

用EXCEL中的VBA进行复杂的数据统计

王月兰

（东莞理工学校计算机科组，广东东莞，523000）

[摘要]Excel是常用的数据统计和分析软件，本文介绍一种基于Excel内核的VBA编程方法，来扩展Excel的功能，以实现高考成绩统计中复杂的数据统计功能。

关键词 Excel 高考成绩统计VBA编程

Excel是Microsoft Office 家族成员，是Windows环境下应用最广泛的软件之一。是一个功能强大、技术先进、使用方便的表格式数据综合管理和分析系统。它向用户提供了史无前例的、超强的计算功能及表格功能。它采用电子表格方式进行数据处理，操作直观方便；它提供了丰富的函数，可以进行各种数据处理、统计分析。此外，Excel的强大功能还在于它提供的宏语言Visual Basic for Application（简称VBA），为广大用户提供了一个新的、更高层次的二次开发平台。

一、 复杂数据统计问题和VBA

今年东莞市有近四十所中学七千多名考生参加高考，成绩总表（表1）中包括了准考证号、姓名、学校、语文、数学、英语、物理、化学、政治、历史、地理、生物、综合、外语复试、体育、音乐、美术成绩及各类总分。教育部门要根据此表进行统计，得出各个科目各校的报考人数，各批入围人数、比例、平均分、最高分等，即表2所示的报表若干份（每个科目一份）。由于高考是采用3+X形式，考生报考的科目不尽相同，即使是同一所学校，各科的报考人数都有所不同。因此统计过程既麻烦，又容易出错。如果采用数据库程序设计方法，编写一个独立、专门的统计程序来处理，则似有小题大作之嫌。本文介绍一种基于Excel的VBA编程方法，实现复杂的数据统计功能。

东莞市2001年高考成绩总表 ZKZH NAME SCHOOL K1 K2 K3 K4 K5 K6 K7 K8 K9 K10 K11 K12 K13 K14 K404 K405 K406 K407 K408 K409 K410 K504 K505 K506 K507 K508 K509 K511 190100001 曾俊杰 东莞中学 513 546 642 190100002 林正 东莞中学 540 448 433 190100005 罗燕青 东莞中学 461 323 545 190100006 李爱南 东莞中学 604 537 568 190100010 李晓薇 东莞中学 513 435 492 516 574 485 710 636 566 517 436 584 193 498 437 648 521 606 451 349 418 427 表1

东莞市2001年高考****科成绩统计表 报考学校 人数 人数 比例 人数 比例 人数 比例 人数 比例 人数 比例 平均 最高 平均 第一批 第二批 第三批 第四批 上线考生 全体考生 表2

Excel中的VBA，主要在VB中增加了关于Excel工作簿、工作表、区域、数据透视表等对象的属性、事件和方法。在Excel 中使用VBA，可以更方遍地操作、控制Excel，进一步深入发掘它的强大功能，全面提高使用Excel工作的自动化水平。使我们可以在熟练应用Excel的基础上，按需要设计出实用的信息管理系统，这种基于Excel内核的管理系统不仅可以与其它语言建立的管理系统相媲美，而且它开发容易，实用性更强。

开发信息管理系统，一般来说主要包括数据的输入、处理、输出三大组成部分。对上述问题，首先新建一个包含“设置”、“成绩总表”和“各科统计表”三个工作表的工作簿，将成绩导入“成绩总表”中（如图1），然后定制“各科统计表”（如图2），这两个步骤用Excel处理显得游刃有余。

图1

图2

二、高考成绩统计问题的解决

1、将代码--名称对照关系按图3所示放入“设置”工作表中，C列是“成绩总表”中各科成绩对应的列标，在E3：E6区域输入各批录取分数线。

2、在“各科统计表”中，增加下拉框窗体控

图3

件，设置数据源区域：设置!$B$3:!$B$12，单元格链接：设置!$B$2，这样能够方便地选择需要统计的科目。选定A2单元格，输入公式=\东莞市”&YEAR(NOW())&\年高考\设置!B3:B12,设置!B2)&\科成绩统计表\，其中函数YEAR（）与NOW（）可得到当年的年份，而函数INDEX(设置!B3:B12,设置!B2)则可以动态地得到下拉框所选中的科目名称。

3、选定“设置”的C2单元格，输入公式=INDEX(C3:C12,B2)，获得选中科目的列标。 4、在“各科统计表”中，增加“统计结果”命令按钮，使用VBA编制以下程序：

Private Sub CommandButton1_Click()

Dim n1, n2, n3, n4, np, nt As Integer '四批的入围人数、上线人数和考生总数

Dim score_pass, score_total, score_top As Integer '上线考生总分、全体考生总分、最高分 grade1 = Sheets(\设置\设置\各批分数线 grade3 = Sheets(\设置\设置\col_subject = Sheets(\设置\统计科目列号 school = Sheets(\成绩总表\第一个学校校名 j = 5 '各科统计表起始行

n1 = 0: n2 = 0: n3 = 0: n4 = 0: np = 0: nt = 0 score_pass = 0: score_total = 0: score_top = 0 ' ActiveSheet.Range(\清空 With ActiveSheet

For i = 3 To Sheets(3).UsedRange.Rows.Count + 1 '源数据行范围 If Sheets(\成绩总表\

score = Sheets(\成绩总表\读成绩 If score >= grade1 Then n1 = n1 + 1

If score >= grade2 And score < grade1 Then n2 = n2 + 1 If score >= grade3 And score < grade2 Then n3 = n3 + 1 If score >= grade4 And score < grade3 Then n4 = n4 + 1

If score >= grade4 Then score_pass = score_pass + score: np = np + 1 If score > 0 Then score_total = score_total + score: nt = nt + 1 If score > score_top Then score_top = score Else

.Range(\校名、报考人数 If nt > 0 Then

.Range(\第一批人数、比例 .Range(\第二批人数、比例 .Range(\第三批人数、比例 .Range(\第四批人数、比例 .Range(\上线总人数、比例 If np > 0 Then .Range(\上线考生平均分

.Range(\最高分、总平均分 End If

school = Sheets(\成绩总表\ j = j + 1: i = i - 1

n1 = 0: n2 = 0: n3 = 0: n4 = 0: np = 0: nt = 0 score_pass = 0: score_total = 0: score_top = 0 End If Next i End With

MsgBox \数据统计已经完成！\提示信息…\

End Sub

5、将“设置”中的第2行隐藏，并对工作表和VBA工程进行保护，以防止误操作，整个小型系统完成。

使用时，只需从下拉框中选择科目，单击“统计结果”按钮，结果就能够自动显示出来，随时可以进行打印，方便快捷。下一年使用时，只需将数据导入“成绩总表”中，再修改“设置”中的红色部分即可。

三、结束语

从本例中的几个步骤来看，我们仅设置了一个命令按钮(commandbutton)，用VBA编写一段小程序来完成Excel不方便直接解决的数据处理部分，其余的则交给Excel本身去处理。这样，所需工作量很小，但达到的效果却非同寻常。因此，把VBA与Excel有机地结合起来，或者说基于Excel内核的VBA编程，可以在充分发挥Excel直观快捷的数据输入、丰富的工作函数及强大的报表功能的基础上，利用VBA编程进一步拓展Excel的功能，做到用最短时间、花最少的精力去完成工作。换言之，简单、高效、经济、省时是VBA的特点，也正是使Excel更富魅力的关键所在。

此外，内嵌于Excel中的VBA不仅能提供标签(label)、文字框(textbox)、单选框(optionbutton)、复选框(checkbox)、列表框(listbox)、组合框(combobox)、命令按钮(commandbutton)、开关按钮(togglebutton)、数值调节按钮(spinbutton)、滚动条(scrollbar)等各种常用控件，还可以根据需要增加用户窗体(userform)；利用Application下的Inputbox()函数设立工作表的使用权限密码，以保护重要的文档；如果工作表的数量多，也可以在VBA中改变worksheets的visible属性，使指定工作表只在必要时才显示出来；而自定义工具条(toolbar)和自定义菜单(CommandBar)功能则可以使编制系统更典型，操作更方便……。总之，只要合理运用各种对象、方法、属性，就能够满足操作上、功能上、界面上的种种需要。

参考文献

1谢乐军，李丽.《中文EXCEL2000最佳实用教程》.中国民航出版社，1999.11 2云舟工作室.《中文版Access2000VBA一册通南京》人民邮电出版社

3龚沛曾，陆慰民，杨志强.《Visual Basic 程序设计教程（6.0版）. 高等教育出版社.2000.7

作者：王月兰,中学一级教师

地址：广东省东莞理工学校计算机科组

邮编：523000 E-mail: dgwyl1968@21cn.com