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

用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年高考成绩总表ZKZHNAMESCHOOLK1K2K3K4K5K6K7K8K9K10K11K12K13K14K404K405K406K407K408K409K410K504K505K506K507K508K509K511190100001曾俊杰东莞中学513546642516710190100002林正东莞中学540448433574517193636498190100005罗燕青东莞中学461323545485436566437190100006李爱南东莞中学604537568648584606190100010李晓薇东莞中学513435492521349418451427表1东莞市2001年高考*科成绩统计表学校报考人数第一批第二批第三批第四批上线考生全体考生人数比例人数比例人数比例人数比例人数比例平均最高平均 表2Excel中的VBA，主要在VB中增加了关于Excel工作簿、工作表、区域、数据透视表等对象的属性、事件和方法。在Excel 中使用VBA，可以更方遍地操作、控制Excel，进一步深入发掘它的强大功能，全面提高使用Excel工作的自动化水平。使我们可以在熟练应用Excel的基础上，按需要设计出实用的信息管理系统，这种基于Excel内核的管理系统不仅可以与其它语言建立的管理系统相媲美，而且它开发容易，实用性更强。开发信息管理系统，一般来说主要包括数据的输入、处理、输出三大组成部分。对上述问题，首先新建一个包含“设置”、“成绩总表”和“各科统计表”三个工作表的工作簿，将成绩导入“成绩总表”中（如图1），然后定制“各科统计表”（如图2），这两个步骤用Excel处理显得游刃有余。图1图2二、高考成绩统计问题的解决图31、将代码-名称对照关系按图3所示放入“设置”工作表中，C列是“成绩总表”中各科成绩对应的列标，在E3：E6区域输入各批录取分数线。2、在“各科统计表”中，增加下拉框窗体控件，设置数据源区域：设置!$B$3:!$B$12，单元格链接：设置!$B$2，这样能够方便地选择需要统计的科目。选定A2单元格，输入公式=东莞市”&YEAR(NOW()&年高考&INDEX(设置!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(设置).Range(E3): grade2 = Sheets(设置).Range(E4) 各批分数线grade3 = Sheets(设置).Range(E5): grade4 = Sheets(设置).Range(E6)col_subject = Sheets(设置).Range(C2) 统计科目列号school = Sheets(成绩总表).Range(C3) 第一个学校校名j = 5 各科统计表起始行n1 = 0: n2 = 0: n3 = 0: n4 = 0: np = 0: nt = 0score_pass = 0: score_total = 0: score_top = 0 ActiveSheet.Range(A5:O50) = 清空With ActiveSheet For i = 3 To Sheets(3).UsedRange.Rows.Count + 1 源数据行范围 If Sheets(成绩总表).Range(C & CStr(i) = school Then score = Sheets(成绩总表).Range(col_subject & CStr(i) 读成绩 If score = grade1 Then n1 = n1 + 1 If score = grade2 And score = grade3 And score = grade4 And 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(A & CStr(j) = school: .Range(B & CStr(j) = nt 校名、报考人数 If nt 0 Then .Range(C & CStr(j) = n1: .Range(D & CStr(j) = n1 * 100 / nt 第一批人数、比例 .Range(E & CStr(j) = n2: .Range(F & CStr(j) = n2 * 100 / nt 第二批人数、比例 .Range(G & CStr(j) = n3: .Range(H & CStr(j) = n3 * 100 / nt 第三批人数、比例 .Range(I & CStr(j) = n4: .Range(J & CStr(j) = n4 * 100 / nt 第四批人数、比例 .Range(K & CStr(j) = np: .Range(L & CStr(j) = np * 100 / nt 上线总人数、比例 If np 0 Then .Range(M & CStr(j) = score_pass / np 上线考生平均分 .Range(N & CStr(j) = score_top: .Range(O & CStr(j) = score_total / nt 最高分、总平均分 End If school = Sheets(成绩总表).Range(C & CStr(i) 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 iEnd WithMsgBox 数据统计已经完成！, vbInformation, 提示信息End Sub5、将“设置”中的第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)功能则可以使编制系统更典型，操作更方便。总之，只要合理运用各种对象、方法、