用VBA开发基于Excel的数据统计
开篇：润墨网以专业的文秘视角，为您筛选了一篇用VBA开发基于Excel的数据统计范文，如需获取更多写作素材，在线客服老师一对一协助。欢迎您的阅读与分享！
摘要：Excel是广泛应用的电子数据表格程序,如在Excel中使用vba（Visual Basic for Application）编程将有很多机会开发出有价值的应用程序，本文介绍一种基于excel的VBA编程方法，来扩展Excel的功能，以实现学生评优和奖学金评选的数据统计的计算功能。
关键词：Excel；奖学金评选；宏
中图分类号：TP311文献标识码：A 文章编号：1009-3044(2007)17-31325-02
Use VBA to Develop Ddata Statistic Based on Excel
LI Hui
(Suzhou, Arts & DesignTechnology Institute ,Suzhou 215014,China)
Abstract: Excel is a electronic spreadsheet program which is widely used, we may have many opportunities to develop useful applications if we use VBA (Visual Basic for Application) in the Excel program. This paper introduces a method of VBA programming which is based on Excel, in order to expand the functions of Excel and help us to process statistical data of students and scholarships selection.
Key words: Excel; scholarship selection; Acer
1 前言
每到学年（或学期）结束时，学校都要根据学生的各门学习和资信等成绩来选出优秀者和评选出各等级奖学金获得者。我院已经和其它一些高校一样，采用了学分制的教学管理制度，但采用学分反应不出学习成绩之间的差异，对名额很有限的评优和奖学金评选工作也增加了一定难度。因此本人认为用学生学习绩效分来进行评选能起到公平、公正和效率高的的效果。
Excel是一个功能强大、使用方便的表格式数据综合管理和分析系统。它向用户提供了史无前例的、超强的计算功能及表格功能。它采用电子表格方式进行数据处理，提供了丰富的函数，可以进行各种数据处理、统计分析。但是Excel的强大功能还在于它提供的宏语言Visual Basic for Application（简称VBA），为广大用户提供了一个新的、更高层次的二次开发平台。为了提高学院评优和评选奖学金的质量和效率，本人用基于Excel的VBA编程方法编写了一段宏代码以实现学生的评优和奖学金评选。
2 学习绩效的定义
学习绩效分为单项绩效分（列为评选的每项科目绩效分）和平均绩效分（总科目绩效分的平均值）。
2.1 单项绩效分
单项绩效分是每项科目的学分与该门课程的绩效分的乘积来表示的，即：
单项绩效分＝该门课程的学分×该门课程的绩效分
表1
单科在70分或“中”以下者不得参与评选。
2.2 平均绩效分
平均绩效分＝各单项绩效分之和÷学年（或学期）规定的总学分数
平均绩效分是体现学生评优和划分奖学金等级的依据。如用一般的Excel表格编制，也要用许多语句，比较复杂，下面用基于Excel的VBA编程方法编写宏代码，并会使之简单有效。
2.3 Excel表格格式
工作表必须按照统一的格式进行组织以适应各种情况并使宏代码具有通用性，举学期统计例子如下：前两行是表头，用来填写科目及相应学分等信息，第3行开始填写学生姓名及成绩（学习成绩有70分或“中”以下成绩的学生不用填写，）A列和B列分别是学生的序号和姓名，从C列到Z列（Q-Z列表中隐藏了）共26列为各科成绩（列不够可以增加，后面内容往右移动），AA、AB和AC三列分别为单科绩效分之和、学年（或学期）总学分和平均绩效分。
表2
3 编制运行”宏代码”
3.1录制宏
在上述的Excel菜单栏中，打开“工具／宏(M)／宏(M)…”对话框（也可用快捷健Alt+F8打开），输入宏名（本文用的是“评比计算”），单击“创建”按钮后进入宏代码编辑窗口，输入以下程序：
Sub 评比计算()
Dim H%, L%, N%, Sum%, C%, d%, Ave!, Col$
N = Val(InputBox("请输入学生人数：", "苏州工艺美院"))
For H = 3 To N + 2 '计算所有人
Sum = 0
C = 0
在上述的Excel菜单栏中，打开“工具／宏(M)／宏(M)…”对话框（也可用快捷健Alt+F8打开），输入宏名（本文用的是“评比计算”），单击“创建”按钮后进入宏代码编辑窗口，输入以下程序：
Sub 评比计算()
Dim H%, L%, N%, Sum%, C%, d%, Ave!, Col$
N = Val(InputBox("请输入学生人数：", "苏州工艺美院"))
For H = 3 To N + 2 '计算所有人
Sum = 0
C = 0
For L = 3 To 26 '计算所有列的成绩
Select Case L'找列号名称
Case Is
Col = Chr(64 + L)
Case Is
Col = "A" & Chr(64 + L - 26)
End Select
If IsNumeric(Range(Col & H)) And Not IsEmpty(Range(Col & H)) Then'如果有成绩
d = Val(Range(Col & H)) \ 10 - 7 '算绩效分
If d > 3 Then d = 3
Sum = Sum + d * Val(Range(Col & 2)) '绩效分之和
C = C + Val(Range(Col & 2))'总学分
End If
Next L
Range("AA" & H) = Sum '填写绩效分之和
Range("AB" & H) = C '填写总学分
Range("AC" & H) = Sum / C '填写平均绩效分
Next H
End Sub
3.2 运行宏
在Excel窗口，再次打开“工具／宏(M)／录制新宏”对话框，在弹出的对话框中输
入快捷键字母（本例用m），单击“确定”即可。
按照上面的表格格式输入学生的成绩等信息，用快捷键Ctrl+Shift+m或Ctrl+m即可启动宏来完成有关计算。
如增加科目,要注意变量取值范围.防止编程出现“溢出”现象。
4 结束语
Excel一个工作薄的多个工作表可以共享一段宏,在运用过程中成绩输入有改动，需要重新启动宏得到新的计算结果,使用时若有评选要求的变化,只要将代码中的变化值按照要求相应改变。。另外还要说明2点：一是等级制的成绩要折算到百分制（定一个规定）录入表格，二是，免修或免考的成绩也要根据学校规定折合成绩来录入表格，做到计算正确了才能保证评选的合理性。
参考文献：
[1]谢乐军,李丽.中文EXCEL2000最佳实用教程[M].中国民航出版社,1999.11
[2]龚沛,陆慰民,杨志强.Visual Basic 程序设计教程（6.0版）[M]. 高等教育出版社,2000.7
[3]云舟工作室.《中文版Access2000VBA一册通南京[M].人民邮电出版社,2002.3
[4][美]汉森,著.李兆斌，等译 .Excel 2003与VBA编程从入门到精通[M].电子工业出版社,2004.9
注：本文中所涉及到的图表、注解、公式等内容请以PDF格式阅读原文。